AnsweredAssumed Answered

ODBC driver incorrectly returns length of -1 for the max length of character fields

Question asked by barefootguru on May 16, 2013

Summary

ODBC driver incorrectly returns length of -1 for the max length of character fields

Product

FileMaker Server

Version

12.0v3, 12.0v4

Operating system version

Windows Server 2008 R2 Enterprise SP1

Description of the issue

The ODBC driver is returning a length of -1 for the max length of the character fields, causing an insert from SAS to fail.

(SAS track 7610978627)

Steps to reproduce the problem

9     libname test12 odbc dsn='Test12' user='admin' password=XXXXXXXXXXX;
NOTE: Libref TEST12 was successfully assigned as follows:
      Engine:        ODBC
      Physical Name: Test12

10
11    data work.i;
12      f1='aaa'; f2=222;
13    run;

NOTE: The data set WORK.I has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.13 seconds
      cpu time            0.03 seconds


14
15    proc sql;
16      insert into test12.test12(field1,field2)
17      select      f1,f2
18      from        work.i;
ERROR: The character variable field1 has too long a value for the TEST12 library.
ERROR: CLI close cursor error: [FileMaker][FileMaker] No cursor open for statement
19    quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.09 seconds
      cpu time            0.03 seconds

Expected result

Row is inserted into FileMaker table by the ODBC driver.

Actual result

ODBC driver returns incorrect value to SAS, causing it to abort the insert.

(Note the 'CLI close cursor error' is caused by a different driver bug, ignore it here.)

Exact text of any error message(s) that appear

SAS Institute have been kind enough to trace yet another FileMaker ODBC bug.  Their response:

Development ran a test and found the following:

"....
Enter the DSN : Fm12_32bit
Enter the UID : xxx
Enter the PWD : xxx
Connected to FM12_32bit
GetInfo Data Source Name: FM12_32bit
GetInfo Data Source read only: N
GetInfo DBMS Name: FileMaker
GetInfo DBMS Version: 12.0.3
GetInfo Driver Name: fmodbc32.dll
GetInfo Driver Version: 12.3.103.0
GetInfo Driver ODBC Version: 03.00
SQLGetTypeInfo(hstmt, SQL_ALL_TYPES)
SQLGetTypeInfo was successful! Now bind its columns.
SQLBindCol for the columns was successful!  Now SQLFetch GetTypeInfo.

TYPE_NAME, DATA_TYPE, PRECISION, LITERAL_PREFIX, LITERAL_SUFFIX, CREATE_PARMS, MONEY, MAX_SCALE
VARCHAR, 12, -1, ', ', length, 0, 0
CHARACTER VARYING, 12, -1, ', ', length, 0, 0
DECIMAL, 3, 10, , , precision,scale, 0, 15
......"

The driver is returning a length of -1 for the max length of the character fields. A description of the ODBC call can be found here http://msdn.microsoft.com/en-us/library/ms714632(v=vs.85).aspx


Running the same test against SQL Server for instance produces:

"......
SQLBindCol for the columns was successful!  Now SQLFetch GetTypeInfo.

TYPE_NAME, DATA_TYPE, PRECISION, LITERAL_PREFIX, LITERAL_SUFFIX, CREATE_PARMS, MONEY, MAX_SCALE
sql_variant, -150, 8000, , , , 0, 0
uniqueidentifier, -11, 36, ', ', , 0, 0
ntext, -10, 1073741823, N', ', , 0, 0
xml, -10, 1073741823, N', ', , 0, 0
nvarchar, -9, 4000, N', ', max length, 0, 0
date, -9, 10, ', ', , 0, 0
time, -9, 16, ', ', , 0, 0
datetime2, -9, 27, ', ', , 0, 0
datetimeoffset, -9, 34, ', ', , 0, 0
sysname, -9, 128, N', ', , 0, 0
nchar, -8, 4000, N', ', length, 0, 0
bit, -7, 1, , , , 0, 0
tinyint, -6, 3, , , , 0, 0
....."

Configuration information

Workstation used to pull ODBC data is:

Windows 7 64-bit with 32-bit SAS 9.3 TS1M2 (WLATIN encoding) and 32-bit FileMaker Pro ODBC driver 12.03.103.00

Workaround

Insert text directly:


22    proc sql;
23      insert into test12.test12(field1,field2)
24      values('aaa',222);
NOTE: 1 row was inserted into TEST12.test12.

ERROR: CLI close cursor error: [FileMaker][FileMaker] No cursor open for statement
25    quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.32 seconds
      cpu time            0.01 seconds

Outcomes