vexing MySQL query problem
sl at trackandfieldnews.com
Fri Jan 25 16:20:12 EST 2013
I am working on an app to update a MySQL database table. Unfortunately, one of the fields in the table is named "Text" and I can't change that because it's a long-extant table which other non-LC scripts query. I am able to update the table except when the "Text" field is part of the query.
Hence this query won't work.
do "revExecuteSQL myDB," & quote & "INSERT INTO RelevantTable (Text) " & "VALUES ('" & thisText & "')" & quote
But this query does work.
do "revExecuteSQL myDB," & quote & "INSERT INTO RelevantTable (URL) " & "VALUES ('" & thisURL & "')" & quote
It works even though URL is a LiveCode keyword. "Text" is a keyword but it's also a property. I guess that is the rub?
Of course, with MySQL I have the option of inserting values into the table without identifying the fields by name. The catch seems to be that using that approach I have to insert values into all the fields, including the nullable and auto-incrementing fields. As I understand it, the value null should be inserted into the auto-incrementing fields. Hence I've tried the following query.
do "revExecuteSQL headlinesDB," & quote & "INSERT INTO RelevantTable VALUES (null, '" & thisURL & "', null, '" & thisText & "', null)" & quote
This doesn't work either. Is this because "null" is a LC constant? If so, is there some way I can escape it in the query? Or some other, better way to phrase the query?
Thanks in advance,
More information about the Use-livecode