SQLite Syntax Error Only in Livecode

[manofarms]

Hi all,
I've searched the forum and haven't found a relevant thread. I feel the question is important. I've got an SQLite statement that will execute in multiple database editors but it won't execute in livecode.
This is the code that generates the SQLite Statment

Code: Select all

on updateDatabase
   local queryFields
   local queryValues
   databaseConnect
   put getDatabaseID() into dbID
   put the keys of aEvent into tkeys 
   sort tKeys numeric
   repeat for each line tlineNum in tKeys
      put the keys of aEvent[line tlineNum of tKeys] into sKeys
      repeat for each line slineNum in sKeys
         put  "'"&slineNum&"'"&comma after queryFields
         put "'"&aEvent[line tlineNum of tKeys][slineNum]&"'"&comma after queryValues
      end repeat
      delete the last character of queryFields
      delete the last character of queryValues
      put "INSERT OR ROLLBACK INTO events ("&queryFields&") VALUES ("&queryValues&")" into tSQL
      put tSQL    
      revExecuteSQL(dbID, tSQL)
      put empty into queryFields 
      put empty into queryValues
      put empty into tSQL
      
   end repeat
   databaseCloseConnect
end updateDatabase

The tSQL variable contains this bit of information for example:

Code: Select all

INSERT INTO events ('creator','etag','guestsCanInviteOthers','originalStartTime','description','location','sequence','htmlLink','start','kind','guestsCanSeeOtherGuests','recurringEventId','timeZone','id','end','updated','email','created','organizer','iCalUID','status','summary') VALUES ('{','"auapDmnea4KYDCjlbHK2Hbx6X-Q/Q09EUG9zRFBKaEVBQUFBQUFBQUFBQT09"','true','2012-04-05T19:00:00-06:00','The Vineyard is a program under the direction of the First Presidency of the Church which gives people the opportunity to put their foreign language skills to use. Volunteers are needed to assist with church translation materials, media, technology, and tutoring. Vineyard meets every Tuesday at 6:00pm and Thursday at 7:00pm in 3223 Wilkinson Student Center. Volunteers are not required to attend both. \n\nFor more information about Vineyard go to webreference or send them an email at webreference. \n','3223 WSC','1','webreference','2012-04-05T19:00:00-06:00','calendar#event','true','r6jbjuc9arj1jjk3av18tlv39s','America/Denver','r6jbjuc9arj1jjk3av18tlv39s_20120406T010000Z','2012-04-05T20:00:00-06:00','2012-01-19T22:14:04.000Z','webreference','2012-01-19T22:14:04.000Z','{','r6jbjuc9arj1jjk3av18tlv39s','confirmed','Vineyard')

I have run this statement successfully in two other database managers, but livecode gets stuck on the revExecuteSQL(dbID, tSQL) and says "revdberr,syntax error". Any insight would be extremely helpful.

[shaosean]

Perhaps Rev does not like the quotes in here:

Code: Select all

'"auapDmnea4KYDCjlbHK2Hbx6X-Q/Q09EUG9zRFBKaEVBQUFBQUFBQUFBQT09"'

[manofarms]

Perhaps Rev does not like the quotes in here:

Code: Select all

'"auapDmnea4KYDCjlbHK2Hbx6X-Q/Q09EUG9zRFBKaEVBQUFBQUFBQUFBQT09"'

I looked into that and got rid of the quotes, but it still gives the same error. I honestly have no idea what to do now. The syntax is fine according to SQL. Why does livecode hate me so?

[manofarms]

I figured it out.

I replaced

Code: Select all

revExecuteSQL(dbID, tSQL)

with the command

Code: Select all

revdb_execute(dbID, tSQL)

and it works now. Strange, but the older syntax works better.

[mwieder]

Not strange at all. revExecuteSQL is a command, revdb_execute is a function. The syntax for using them is different.

Code: Select all

revExecuteSQL dbID, tSQL
put the result into tHowManyRows

Code: Select all

put revdb_execute(dbID, tSQL) into tHowManyRows

Your original invocation of

Code: Select all

revExecuteSQL(dbID,tSQL)

was invalid and the compiler should have given you the syntax error.