vexing MySQL query problem

Sieg Lindstrom 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,

Sieg Lindstrom




More information about the Use-livecode mailing list