Knowledge Base

MAS 200 - Providex ODBC SQL

admin — Fri, 18 Sep 2009 00:11:20 GMT

When linking to MAS 200, if you link directly you use the Providex ODBC driver.

This has its own SQL syntax. For example, to use a function in a SQL SELECT , you need to use:

{fn current_date()}

like this:

select field1, {fn current_date()} from table

See the attached document for more information.

Linking to MAS 200

admin — Thu, 17 Sep 2009 23:25:46 GMT

MAS 200 uses the Pervasive database system, and pulling data from it can be a bit difficult.

Belinda Zenk of Argenta Sytems has contributed the following notes on how to use SQL Server to create a linked server to the MAS 200 database. This means that Inaport can read from the linked tables in SQL Server, which removes many issues with the specialised SQL that the Providex ODBC driver requires.

If you are linking directly the MAS 200 using the Proxidex ODBC driver, see the seperate post on SQL.

MAS 200 Linked Server

Create Linked Server:

EXEC sp_addlinkedserver
      @server='MAS', -- Name for Link Server
      @provider='MSDASQL',
      @srvproduct = 'ProvideX',
      @datasrc='Best Dynalink' -- Name of ODBC DSN
GO

EXEC master.dbo.sp_addlinkedsrvlogin
      @rmtsrvname=N'MAS',

      @useself=N'False',
      @locallogin=NULL,
      @rmtuser=N'XXXXXX', --MAS 200 Username
      @rmtpassword='XXXXXX' --MAS 200 Password
Go

Drop Linked Server:

EXEC sp_dropserver MAS, droplogins
GO

Example Query1: Basic

SELECT * FROM OPENQUERY([MAS], 'SELECT * FROM AR_CustomerSalesHistory')

Example Query2: Use SQL syntax

Select ARDivisionNo, CustomerNo, sum(DollarsSold) as LYTD
From(
          SELECT * FROM OPENQUERY([MAS], 'SELECT * FROM AR_CustomerSalesHistory')
      ) as AR
where FiscalYear = cast(year(getdate())-1 as varchar(4))
     and FiscalPeriod < right('00' + cast(month(getdate())-1 as varchar(2)),2)
Group by ARDivisionNo, CustomerNo

GO

Note: Cannot Query ‘directly' ie this does not work:

Select ARDivisionNo, CustomerNo, sum(DollarsSold) as LYTD
From MAS...AR_CustomerSalesHistory
where FiscalYear = cast(year(getdate())-1 as varchar(4))
and FiscalPeriod < right('00' + cast(month(getdate())-1 as varchar(2)),2)
Group by ARDivisionNo, CustomerNo

Data typing in Excel

admin — Wed, 25 Mar 2009 21:53:23 GMT

A problem sometimes arises when trying to read data from an Excel spreadsheet. There may be data in cells, but when you look at it in the preview pane in Inaport, the cells look empty.

Fundamentally, Excel is NOT a database and the columns in a spreadsheet do not have a data type associated with them in the same way that that a database does. The Excel ODBC driver has to make an assumption about the data type for a given column. The driver does this by reading ahead 8 records and looking at the data found. It then makes a decision about the data type based upon
the majority type of the first 8 rows.
Problems occur when the assumption about data type is proven wrong by subsequent records. Consider two scenarios….

An Excel spreadsheet has a column which contains text data. The first 5 records contain text in this column, but rows 6, 7, 8 contain numbers. The Excel ODBC driver reads this data and assumes that a text data type will be appropriate for this column. Rows 6, 7, 8 will show as blank, because the cells hold numbers and not text.

Conversely, suppose the first 5 rows hold numbers; if rows 6, 7 and 8 hold text, they will appear blank when you look at them in the preview pane.

A possible work around is to force the contents of a column to be text.

You CANNOT do this by changing the format of the column - this just change show the cell is displayed, NOT how the data is actually stored and processed.

If you have some numbers in a text column, you can preface the numbers with a single quote to make them text: '123

Another technique is to add a column in Excel, and use the text() function. This converts the contents of a cell to text. if the source cell is a number, it gets converted to text; if the source is already text, it does not get changed. For example:

Cell A1 holds the number 123; cell B1 has the formula =text(a1, "0"). When read by Inaport, Inaport sees text "123"
Cell A2 holds the text "abc"; cell B2 has the formula =text(a2, "0"). When read by Inaport, Inaport sees text "abc"

Removing NULLs and other illegal characters from text

admin — Sat, 08 Nov 2008 18:39:53 GMT

A frequent problem with data migrations is text data with illegal characters in it. For example, the GoldMine notes fields sometimes contains NULL characters (binary 0), which can be problematic when inserting the data into SQL Server.

Inaport and Sage Migrator provide the snip() function, which can be used to remove characters or strings from a field. For example:

snip("aa111bb", "1")

will remove the "1" characters from the string, returning "aabb".

The characters to be snipped can be built from a regular expression. In particular, \xNN allows you to specify the binary value of any character. Examples:

\x00 - NULL
\x0A - Carriage Return
\x0D - Line Feed

So to snip the NULL character from a field, do:

snip(#myField, "\x00")

If you need to remove a range of characters, use the regular expression "[]" construct, which allows you to specify a set or range of characters:

[\x00\x02] - specifes the characters \x00 and \x02
[\x00-\x08] - specifies all characters between \x00 and \x08

So to snip a range of characters fro the field, do:

snip(#myField, "[\x00-\x08]+")

Updating source database during an import

admin — Fri, 31 Oct 2008 19:06:59 GMT

Occassionally, it is necessary to update the source or target database during an import operation. For example, it may be necessary to flag a source record as processed. Inaport supports this using the "dbexecupdate()" function, that allows you to perform an arbitrary SQL UPDATE statement against either the source or the target database.

The general format of the function is:

dbexecupdate("S" or "T", "UPDATE table SET field = 'value' WHERE key = 'keyvalue'")

The "S" or "T" specifies the source or target database respectively.

The second parameter is a string that needs to resolve to a legal SQL UPDATE statement for the database. The string can be built up using stanard inaport expressions. For example, if the time is 10:22:34 and the field keyfield has the value "aa123", then the string:

dbexecupdate("S", "UPDATE table SET field = '" & nowstr("HH:mm:ss") & "' WHERE key = '" & #keyfield & "'")

will produce a SQL UPDATE that looks like this:

"UPDATE table SET field = '10:22:34' WHERE key = 'aa123'"

NOTE: It is important to note the sequence of single and double quotes. There are two things going on:

The single quotes need to be positioned to produce legal SQL
The double quotes are marking the boundaries of strings that are joined together by the expression evaluator to produce the SQL.

So the piece of text: key = '" & #keyfield & "'"
is really: key = SQ DQ & #keyfield & DQ SQ DQ

NOTE FOR EXCEL: Excel requires that the worksheet name end in "$", and be surrounded by the "`" character - this is NOT a single quote. So the above example for Excel would be:

dbexecupdate("S", "UPDATE `table$` SET field = '" & nowstr("HH:mm:ss") & "' WHERE key = '" & #keyfield & "'")

Renaming text files after processing - include date and time in name

admin — Fri, 24 Oct 2008 23:51:41 GMT

A common requirement when using Inaport to import text files is to rename the files after processing. This allows an external process (e.g. mainframe) to continuously drop in text files, for Inaport to process on a scheduled basis.

The Inaport text connector provides two options for handling a file after it has been processed:

Change the extension of the file
Move the file to a new directory

These options may be combined, so it is possible to change the extension to ".done" and move the file to a directory "Processed", for example.

Inaport also supports using expressions in the "Change extension" text box. This allows you to include a time stamp in the new name. For example, the following expression can be put in the text box:

"." & nowstr("yyyyMMddTHH_mm_ss") & ".done"

If the current date and time is 24 October 2008 @ 16:46:00, this will change the extension of the file to:

".20081024T16_47_00.done"

The expression used can be any valid Inaport expression that produces a string that is legal to include in a file name.

Excel numbers as text

admin — Wed, 07 May 2008 09:21:13 GMT

A common problem when reading data from Excel is that numbers may be formatted as text, but are still really numbers and are treated as numbers when read by Inaport.

The most common example is US zip codes. In Excel, they look like "12345" or "00123", but when read by Inaport they come through as "12345.0" or "123.0".

In Excel, even if a number is formatted as text it is still a number. You have two options:

1. In Excel, add another column and put in the formula "=text(a1, "00000")" (without the surrounding double quotes)

This will convert the contents of cell a1 to true text, which Inaport can then read as text. Obviously change the cell reference to fit your spreadsheet.

2. In Inaport, use the n2s() function to convert the number to a string in the correct format:

n2s(#myZipCode, "00000")

This will convert the number 123.0 to text "00123"

Matching against Numbers

admin — Wed, 19 Mar 2008 14:46:14 GMT

When using Standard or Fuzzy matching, Inaport will convert all target matching fields to strings BEFORE matching the incoming field against it.

This is because Inaport needs to be able to match across many different database types, including products such as ACT! or SageCRM.com which do not support SQL.

This can present problems when matching against numbers, because how a number is converted to a string can be dependent on locale. For example, the number 1234.567 may be "1,234.567" or "1.234,567". Starting with build 7.0.7303, Inaport standardised the way numbers are converted to strings when reading from the target database to use a CULTURE INVARIANT format.

This means that the number 1234.567 will always be represented as "1234.567", whatever the local culture.

To match against a number field, therefore, you need to convert your source number to a string in the same format.

The recommended way to do this is to use the n2s() function, with the invariant culture specifier:

n2s(#mySourceNumber, "G")

then use this value to match against the target field.

For more information, see the topic "Matching Numbers and Dates" in the Help.

Excel ODBC error - "The field is too small ..."

admin — Sun, 16 Mar 2008 19:04:35 GMT

While importing from an Excel spread sheet you see the following error message "[Microsoft][ODBC Excel Driver] The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data". This is problem with the Excel ODBC driver which can often be worked around by reordering the records in the spreadsheet..

Fundamentally, Excel is NOT a database and the columns in a spreadsheet do not have a data type associated with them in the same way that that a database does. The Excel ODBC driver has to make an assumption about the data type for a given column. The driver does this by reading ahead 8 records and looking at the data found. It then makes a decision about the data type based upon what it has read. Problems occur when the assumption about data type is proven wrong by subsequent records. Consider two scenarios….

A column in an Excel spreadsheet has a column in it which contains string data. The first 8 records contain short strings, (let's say 20-30 characters). The Excel ODBC driver reads this data and assumes that a short string data type will be appropriate for this column. If a subsequent record contains a longer string, (let's say 300 characters). The data type may prove inappropriate and unable to store the longer string and the error above is raised by the Excel ODBC driver. Moving the record with the long string to the beginning of the dataset will allow the Excel ODBC to select a more appropriate data type for the column which will apply to all records in the spreadsheet.

A column in a spreadsheet has numeric strings for the first 8 records, For example "123", "456" etc. Excel decides that this column has numeric data. A subsequent record contains a string which is not numeric data, for example "Hello World". The assumption made by the Excel ODBC driver will prove incorrect and the above error message will be raised by the ODBC driver. The problem can be worked around by reconsidering the order of the records. If the record containing "hello world" is placed within the first 8 records. The Excel ODBC driver will determine that this column contains string data and hopefully a string data type will be selected which will be appropriate for all data records.

In all cases the technique is to arrange the order of the records such that the Excel ODBC driver is allowed to make the correct selection of data type.

GoldMine 5.x, 6.x - BDE Failed to load

admin — Sun, 16 Mar 2008 18:11:10 GMT

This problem is related to the configuration of the Borland Database engine, (BDE). The problems are most frequently seen when a user has upgraded from v5 to v6 of Goldmine. When this is not the case, the problem is more frequently seen with GoldMine v6 than v5.

Typically, the problem is related to one of two configuration settings for the BDE:-

Share Memory Settings
Configuration file settings

Suggested settings for the BDE SHAREDMEMLOCATION are:

6BDE
5BDE
4BDE
7F00
7BDE

From the tools menu in GoldMine select the BDE administrator option. This will launch the BDE administrator. Click on the configuration tab and expand the "System" part of the tree. Click on the INIT branch and change the settings for SHAREDMEMLOCATION. NOTE YOU MUST RESTART ALL APPLICATIONS, GOLDMINE, INAPORT BEFORE THE CHANGES TAKE EFFECT.

GoldMine V7.x: Fail appending to CONTACT2

admin — Fri, 25 Jan 2008 18:25:16 GMT

Do you have a numeric UDEF field in contact2? If so, you are running into a known issue with GM7.

PROBLEM

When an application (such as Inaport) creates a C2 record using the API, GoldMine issues an insert on the database, which succeeds. It then accumulates all the field values, and issues a single update to the record when the changes are commited by the application. For reasons best known to the developers, the WHERE clause to the UPDATE checks that each field being updated is currently null.

Problem with this is that with GM7, the schema definition for UDEF numeric fields was changed to have them default to 0. That means any numeric fields are set to 0 when the record is first inserted, which in turn means that the where clause fails.

WORKAROUND

In Inaport, you can work around this problem by having two instances of the _contact2 table in your table map.

On the first instance, set the operation to "Create not exist", and accept the default primary key/foreign key settings.
Change the action for "If there are multiple matches" to be: "Continue processing this source row"
Do not specify any match criteria.
Do NOT map any values to the fields - have an empty "Map for Create" grid

On the second instance, set the operation to "Update single existing"
Do not specify any match crieria
Now map all your fields to the required fields in C2

----------

In the medium term, we are working on having Inaport handle this silently for you, but at the moment this is the only work around.

Connecting to GoldMine V7, V8, PE

admin — Thu, 27 Dec 2007 13:09:15 GMT

Inaport needs to log into the GoldMine API to enable it to write to GoldMine. To do so, you must set some login parameters. GoldMine 7.x and up are very particular about how these parameters are set. Please note the following:

Information about the correct GoldMine directory and alias can be found in GoldMine - Help - About GoldMine - System
The path to the GoldMine system directory must end with a '\'
The alias names for the GoldMine System and Contact Set databases must end in ':'
The user name specified must have Admin permissions, and must NOT have SQL login set in User Properties - Access.

See screen shots below.

Upgraded GoldMine Installations

If your GoldMine installation is an upgrade of a 6.x install, rather than a clean install, you may find you still get issues. This seems to be caused by the upgrade process not correctly registering all new GoldMine dll's.

A work around is to copy the GM7S32.dll file to the Inaport install directory. Occassionally, you may need to copy more GoldMine dll's.

Screen Shots

GoldMine - Help - About GoldMine - System screen

Inaport GoldMine 7.x, 8.x connector