SQL: Single quote embedded in string?

[vermeerp]

I'm using FMPro JDBC to communicate between my Filemaker databases and my Java program. I am trying to INSERT into the database, and some of my strings have single quotes in them. I wrote a method to change all single quotes in any string to two single quotes, which is suposed to be interpreted as an embedded single quote, rather than as the quote ending the string. This worked fine when I used the jdbc-odbc driver from Sun. However, now I can't get it to work. I get the following error message:

java.sql.SQLException: [5] Invalid Command - INSERT INTO "ProblemLists1.fp5" ("UniqueID", "ListPosition", "Type", "Name", "CoreName", "LabTestName", "TestSetting", "Notes", "ShowNotes", "Indent", "Checked") VALUES ('test18', 1, 'D', 'port wine urine', 'null3', 'COLOR', 'Abnormal', 'I don''t know if this''ll work or not.', 'true', 2, 'false')

I tried surrounding all of my strings in the VALUES list with double quotes, and only having a single quote inside the string, but that didn't work either. In fact, doing it that way, I got an error message even when there was no single quote in any of the strings:

java.sql.SQLException: [5] Invalid Command - INSERT INTO "ProblemLists1.fp5" ("UniqueID", "ListPosition", "Type", "Name", "CoreName", "LabTestName", "TestSetting", "Notes", "ShowNotes", "Indent", "Checked") VALUES ("test18", 0, "M", "New Mechanism", "null0", "", "", "", "false", 0, "false")

All is fine if I use single quotes to surround the string, and no single quotes inside them. Any suggestions how I get the embedded single quote to work? I sure don't want to disallow my users from using them!

Thanks,

Pam

[Bohu]

Hi Pam,

Sorry, I don't have an answer for your question but another question for you. You seem to be the first developer I could find that is actually using JDBC with Filemaker. We are trying to develop a JAVA application with access to Filemaker but no one seems to know even what we would need (developer edition? FM 6 unlimited?)

We tried to access via ODBC and the FM 6 Demo version and a simple SELECT statement takes about 1 minute per record (!)

So does the JDBC work? Is there documentation? What do we need to buy? Should we give up??? We don't want to!

Thanks,

Markus