3 Replies Latest reply on Aug 6, 2014 10:30 PM by PH

    FileMaker ODBC Driver Adds Escape Characters Inserting Records on Windows 7

    dsimpson

      Summary

      FileMaker ODBC Driver Adds Escape Characters Inserting Records on Windows 7

      Product

      FileMaker Pro

      Version

      FileMaker Pro Advanced 12, FileMaker Pro Advanced 11

      Operating system version

      Windows 7 - (Windows 6.1.7601 (i386) - as listed in fmodbc log file)

      Description of the issue

      Inserting data into FileMaker Pro Advanced 11 or FileMaker Pro Advanced 12 via ODBC inserts an escape character "\" before each single quote character. These escape characters are entered into the fields of the FileMaker database. This problem only seems to occur on Windows 7. This problem does not occur when using the same data, the same drivers, the same versions of FileMaker Pro on Windows XP. The problem also does not occur when using the DataDirect 5.5 ODBC driver with FileMaker Pro Advanced 10.

      The same issue would also be expected to occur with FileMaker Server Advanced.

      Steps to reproduce the problem

      1) Create an ODBC DSN using the FileMaker ODBC driver. On Windows 7 64bit, use the 32bit ODBC Manager to create this ODBC DSN.
      2) Create a table in the FileMaker database.
      3) Use a tool which can insert records into FileMaker via using parameters (not a command line tool). If you use a command line tool, you would have to escape the single quote characters - which would defeat the test. I am using FmPro Migrator for my testing. Other tools like RazorSQL could potentially also be used to perform a test like this.
      4) In my testing, I have Microsoft Access database files which contain single quote characters within the fields, this is my source data. Transfer the data into FileMaker via the ODBC DSN.
      5) Observe the results by reviewing the records inserted into the FileMaker database.

      Expected result

      The expected result is that the data should be inserted accurately into the database, without having the escape characters inserted.
      In the fmodbc.log file the correct data looks like this:
      // finalized query: INSERT INTO birdcodes (speciescode,species) VALUES (?,?)
      // changing size from 0 to 0
      // command ID: 1
      // octet len: 4
      // parm 1: AMAV
      // octet len: 17
      // parm 2: American's Avocet

      Actual result

      The actual result is that that escape characters get written into the FileMaker database fields. Notice the escape character before the single quote character for param2.
      In the log file fmodbc_Win7_FM12db_FM12driver_04302012_1235pm.log, the data looks like this:
      // finalized query: INSERT INTO birdcodes (speciescode,species) VALUES (?,?)
      // command ID: 1
      // octet len: 4
      // parm 1: AMAV
      // octet len: 17
      // parm 2: American\'s Avocet
      // EXIT: 2012-4-30 12:33:9.271484  rc: SQL_SUCCESS

      Note: I have detailed log files available, but cannot load them into this forum post.

      Exact text of any error message(s) that appear

      None

      Configuration information

      This testing has been done on:
      Windows 7 - 64bit (using 32bit ODBC Administrator) - (Windows 6.1.7601 (i386) - listed in fmodbc.log file)
      .Net Framework 4.5 - included with Visual Studio 11
      Windows 7 - 32bit
      .Net Framework 4
      Windows XP (Windows 5.1.2600 - listed in log file)

      Additional info is included in the fmodbc.log files.

      Workaround

      I have found that one workaround for this issue is to only use Windows XP for inserting records into the FileMaker database via ODBC.

      An alternate workaround on Windows 7 is to install FileMaker Pro Advanced 10 and the included DataDirect 5.5 ODBC driver. Transfer the data into FileMaker Pro Advanced 10, then quit and work with the file in FileMaker Pro Advanced 11. Or then convert the file into FileMaker 12 file format to use it with FileMaker Pro Advanced 12.