More problems with revExecuteSQL

[David Burgun]

Hi,

After finding out that the data I was sending with revExecuteSQL  
needed to be escaped if it contains double-quotes or colon etc. I  
decided it would be better to put the data into an array and use the  
parameter substitution feature. When I run it now, I get  the  
following error: unrecognized token: "13T01"

And I can't figure out could be wrong! Please see of the function  
where this occurs followed by the contents of the variables.

Anyone got any ideas or what could be wrong? Not sure what to do now!  
I need to be able to write arbitrary UTF8 string to a SQLite  
database. Doesn't seem a lot to ask! lol

All the Best
Dave

--------------------------------------------------------------
--
--  UtilDBInsertRecord
--
---------------------------------------------------------------
function UtilDBInsertRecord theDatabaseID,theTableName,theRecordKeyArray
   local mySQLCode
   local myKeyList
   local myKeyName
   local myKeyText
   local myResult
   local myTempKeyList
   local myValueList
   local myParameterList
   local myParameterArray
   local myParameterNumber

   put the keys of theRecordKeyArray into myKeyList
   if myKeyList <> empty then
     sort lines of myKeyList
     put replaceText(myKeyList,cr,",") into myTempKeyList
     --delete char -1 of myTempKeyList

     put empty into myParameterArray
     put empty into myParameterList
     put empty into myValueList
     put 1 into myParameterNumber
     repeat for each line myKeyName in myKeyList
       put ":" &  myParameterNumber & "," after myParameterList
       put theRecordKeyArray[myKeyName] into myParameterArray 
[myParameterNumber]
       add 1 to myParameterNumber
     end repeat

     delete char -1 of myParameterList
     put "INSERT INTO " & theTableName & " (" & myTempKeyList & ") " & \
         " VALUES (" & myParameterList & ") " into mySQLCode

     revExecuteSQL theDatabaseID,mySQLCode,"myParameterArray"
     put the result into myResult

     if myResult <> empty then
       if myResult is not an integer then
         answer error "Error in UtilDBInsertRecord, revExecuteSQL:"  
&& myResult
         breakpoint
       end if
     end if
   end if

   return myResult
end UtilDBInsertRecord

------------------------------------------------------------------------ 
--------------------------------
Variable dump when the error occurs:


mySQLCode:

INSERT INTO MusicBase  
(AlbumName,AlbumRating,AlbumRatingKind,ArtistName,BitRate,BPM,DateAdded, 
Duration,FileLocation,FileSizeBytes,Genre,ModDate,PlayedCount,PlayedDate 
,SampleRate,TrackName,TrackRating,TrackRatingKind,TrackSkippedCount,Trac 
kSkippedDate,UnplayedFlag)  VALUES (: 
1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:2 
1)


myResult			unrecognized token: "13T01"

myParameterList		: 
1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:2 
1

myParameterArray:

[1]	The Best Of Bob Dylan I
[2]	0
[3]	computed
[4]	Bob Dylan
[5]	192
[6]	0
[7]	2007-10-29T10:25:04Z
[8]	168463
[9]	file://localhost/Users/Dave/Music/iTunes/iTunes Music/Bob Dylan/ 
The Best Of Bob Dylan I/01 Blowin' In The Wind.mp3
[10]	4045324
[11]	Rock
[12]	2003-07-13T01:58:33Z
[13]	0
[14]	missing value
[15]	44100
[16]	Blowin' In The Wind
[17]	0
[18] computed
[19]	0
[20]	missing value
[21]	***NULL***