More problems with revExecuteSQL

[Len Morgan]

I believe that even with variable substitution, you have to put ' around 
any non-numeric parameter.  Your SQL statement doesn't have that.  The 
13T01 is the time.  It appears that the expression for the date is 
getting evaluated as a math function.

I think your template SQL statement should be something like:

... VALUES (':1',':2',...

Hope that helps

len morgan


David Burgun wrote:
> 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,TrackSkippedDate,UnplayedFlag)  
> VALUES 
> (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21) 
>
>
>
> 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,:21 
>
>
> 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***
>
>
> _______________________________________________
> use-revolution mailing list
> use-revolution at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your 
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-revolution
>
>