More problems with revExecuteSQL

[David Burgun]

Hi,

> I believe that even with variable substitution, you have to put '  
> around any non-numeric parameter.

Do you mean the data in the array? The reason I moved from just  
sending the data in the SQL Statement directly was to get around the  
problem that there may be quotes in the data to be inserted.

> 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.

But why? And how do I stop it! There may well be single quotes in the  
data. The data can be any UTF8 character.

> I think your template SQL statement should be something like:
>
> ... VALUES (':1',':2',...

Not according to the documentation:

revExecuteSQL myID,"insert into emp() values(:1,:2,:1)",\
      "valueX","valueY"

The content of the variable valueX is substituted for the ":1" in the  
SQLQuery (in both places where ":1" appears), and the content of  
valueY is substituted for ":2".

If you specify an arrayName rather than a list of ordinary variables,  
the revExecuteSQL command substitutes the corresponding element of  
the array for each of the placeholders in the query:

   revExecuteSQL myID,"insert into emp() values(:1,:2,:1)","myArray"

The content of the element myArray[1] is substituted for the ":1" in  
the SQLQuery (in both places where ":1" appears), and the content of  
myArray[2] is substituted for ":2".


Anyone have any idea how to do this simple thing?

All the Best
Dave


On 1 Nov 2007, at 12:53, Len Morgan wrote:

> 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,DateAdd 
>> ed,Duration,FileLocation,FileSizeBytes,Genre,ModDate,PlayedCount,Play 
>> edDate,SampleRate,TrackName,TrackRating,TrackRatingKind,TrackSkippedC 
>> ount,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
>>
>>
>
> _______________________________________________
> 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