More problems with revExecuteSQL
Len Morgan
len-morgan at crcom.net
Thu Nov 1 09:25:49 EDT 2007
David Burgun wrote:
> 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.
>
You might try creating a dbQuote function that specifically looks for
the "no-no" characters and replaces them with something else. In my
code, I replace ' with ` when I'm writing and do the reverse before I
use the contents. Yes, I have sacrificed one possible character (the `)
but in all the code I've written over the last 10 years, that's never
been a problem. You can also use the "legal" escape sequence for your
database. For example, I could have replaced every ' with '' (two
apostrophes in a row) and accomplished the same thing.
> 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".
>
>
I've never actually used the variable substitution technique (and
therefore not read the documentation either). From what you (the
documentation) wrote, it appears that it is possible to do what you want
the way you want to do it. Might I suggest that try a simple example
and try to insert 1 string value into a table using (:1) and see if it
throws and error. If it doesn't, try storing 'test' in the variable
(i.e., you add the single quotes to the variable) and try again and see
what happens.
Unfortunately, you can't see the generated sql that is being sent to
sqlLite. Is there anyway to tell sqlLite to echo it's queries to the
console? That way you would be able to see what Revolution is sending.
The error your getting appears to be from the database and not from Rev.
len
> 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,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
>>>
>>>
>>
>> _______________________________________________
>> 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
>
>
More information about the use-livecode
mailing list