vexing MySQL query problem

[Robert Sneidar]

delimit text with sql quotes, or single quotes perhaps? Also, I think it is better (and easier to debug) to build your sql query as a single string, then execute it. I wouldn't use DO for this. No point. Why is this way better? Because in the debugger you can view and even copy the completed SQL query, paste it into your favorite SQL manager, and see what is wrong with it. 

Bob


On Jan 25, 2013, at 1:20 PM, Sieg Lindstrom wrote:

> 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
> 
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode