0 Replies Latest reply on May 16, 2013 8:12 PM by barefootguru

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

    barefootguru

      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