Updating FM via ODBC not working

[djlane]

Hi guys,

My problem is with updating a FM database via ODBC.

I’m using a Scanning program called “SimpleIndex” which can scan documents and send OCR info to a database file via ODBC. I can get it to export to ACCESS, to MYSQL, to an XLS file or to a CSV file… but I cannot get it to update a Filemaker database directly. I can export to MYSQL and connect via ODBC to the FM database (tested and working), but this introduces an extra layer that I would rather not have.

I created a File DSN and a system DSN using the DataDirect 32-Bit SequeLink 5.5. Both report that the connection to the host file is OK.

When I try to export the data from the scanning software, it always tells me that “The destination table / query is not updatable. Be sure that the user has write access to database files and tables”. I get the same error when using either the File or System DSN.

The FM database has ODBC sharing enabled, the user has full (Admin) access rights. Windows firewall is off. The Filemaker database name does not contain spaces, and there is only one table in the file. None of the fields in the database use any of the reserved keywords mentioned in the FM ODBC JDBC guide. The fields being updated are all text fields. My database is being hosted by FM Pro Advanced 10.0v3 and I’m running windows XP SP3.

Any ideas ?

[mr_vodka]

Have you tried an import from FileMaker of your SimpleIndex data via ODBC?

[djlane]

Well... yes. I can import from a CSV file, from Excel, ACCESS, or MYSQL. My problem is getting SimpleIndex to export the data directly to Filemaker. I suspect that I either need a connection string (the one at http://www.connectionstrings.com/filemaker seems to be very old), or that there is a problem with the Sequelink Driver.

[djlane]

Ok, I got this partially working now. I found that the ODBC Data Source Admin utility has a logging function. That log told me a completely different error than the one being generated by the exporting application. The correct error was “Table not found”. It turned out to be a tickbox in the exporting application which was checked, and was adding [brackets] to the table name. Lesson : Don't trust the error messages generated from your application - look in the ODBC log.

But all is not well yet. I now find that I am unable to insert data into the text fields in my FM table. No problem with inserting data into number or date fields, but trying to update text fields throws up the error (from the ODBC log) “Binding to SQL_LONGVARCHAR/SQL_WLONGVARCHAR is not supported”. The problem has something to do with FM text fields being able to store 1,000,000 characters. I found that by changing the text field properties in FM and making the max length 255 (or whatever), the error in the log goes away – but the exporting application application (simpleindex.exe) still throws the same spurious error that the user may not have write access to the database, and the export operation aborts.

I tried using “Limit Cursor Column Size” (LCCS) in the connection string as recommended in the Sequelink Developers reference, but I’m not sure what number I should set it to, or what number I should set the size of the text field to. I have tried various combinations without success So I still cannot get my application to export text data directly to FM. For now, I have to export to MYSQL and use the much nicer MYSQL ODBC driver to connect to FM. That works but it would be soo much nicer if I could export directly to FM.

Another possibility is “SLKStaticCursorLongColBuffLen”. This “turns on a workaround that allows you to specify the amount of data (in KB) that is buffered for SQL_LONGVARCHAR and SQL_LONGVARBINARY columns with a static cursor. Because the driver caches Unicode characters (UTF-16-LE on Windows, UTF-8 on UNIX), the number of characters that can be cached is smaller for the long-character, unicode columns. The initial default is 4.” But again, the developers reference is not clear on how to use this in a connection string.

I have seen forums in which the Sequelink driver for FM is described as “quirky” and even “retarded” ! Certainly it should not be so difficult to do something as simple as insert data into a text field. The MYSQL ODBC driver configuration panel has an advanced tab with 24 options you can set when you create a DSN. The Sequelink DataDirect 5.5 driver does not even have an advanced tab.

The Sequelink DataDirect 5.5 Developers reference manual can be found at http://www.baobabsoftware.com.au/sequelink.htm

It's dated July 2005, so this driver is now 4 years old !

Help !

Edited August 3, 2009 by Guest

[djlane]

OK, update to the above. It turns out I got my manuals mixed up. “Limit Cursor Column Size” (LCCS) is actually in Sequelink 6.0, it’s not in v5.5. I downloaded the trial version of 6.0, set LCCS = 1000 in the connection string, and now it works – I can output text data directly to FM.

In the Sequelink manual it is specifically mentioned that this connection string parameter is for Filemaker databases.

So, it seems that DataDirect have a new sequelink driver 6.0, (released in April 2008) which includes something required to insert data into Filemaker text fields, but the version that ships with Filemaker is version 5.5, which is 4 years old, and does not support the LCCS connection string attribute needed to send data into FM text fields. Why does Filemaker not ship with the latest Sequelink Driver, or even better, why does filemaker not have its own ODBC Driver?? Should I really have to BUY the latest ODBC driver from DataDirect, instead of it being shipped with Filemaker??

Does anyone know how to get the Sequelink 5.5 driver to update text fields in FM??