Re: More problems with revExecuteSQL
Thanks Trevor, I found it by experimenting and from what others had said. I then looked at your library stack and say the (very nicely formatted and well written code). I forgot I could use your library as an abstraction layer. All the Best Dave On 1 Nov 2007, at 14:07, Trevor DeVore wrote: Dave, Variable substitution doesn't escape characters for in version of Rev prior to 2.9. Just run all values you want to insert into the database through a function such as the following. Note that the PostGreSQL switch statement is based on what I read in the docs. I haven't used PostGreSQL myself so I haven't tested it. -- Trevor DeVore Blue Mango Learning Systems www.bluemangolearning.com-www.screensteps.com on _escapeStringByRef pDBType, @pString switch word 1 of pDBType case "valentina" replace numtochar(92) with numtochar(92) & numtochar (92) in pString --> \ to \\ replace numtochar(39) with numtochar(39) & numtochar(39) in pString --> ' to '' break case "mysql" replace numtochar(92) with numtochar(92) & numtochar (92) in pString --> \ to \\ replace numtochar(39) with numtochar(92) & numtochar(39) in pString --> ' to \' replace numtochar(34) with numtochar(92) & numtochar (34) in pString --> " to \" replace numtochar(0) with numtochar(92) & numtochar(48) in pString --> NULL to \0 replace numtochar(26) with numtochar(92) & numtochar (90) in pString --> Control-Z to \Z replace numtochar(10) with numtochar(92) & numtochar (110) in pString --> newline to \n replace numtochar(13) with numtochar(92) & numtochar (114) in pString --> carriage return to \r replace numtochar(9) with numtochar(92) & numtochar (116) in pString --> tab to \t replace numtochar(8) with numtochar(92) & numtochar(98) in pString --> backspace to \b break case "postgresql" replace numtochar(92) with numtochar(92) & numtochar (92) in pString --> \ to \\ replace numtochar(39) with numtochar(39) & numtochar(39) in pString --> ' to '' replace numtochar(12) with numtochar(92) & numtochar (102) in pString --> formfeed to \f replace numtochar(10) with numtochar(92) & numtochar (110) in pString --> newline to \n replace numtochar(13) with numtochar(92) & numtochar (114) in pString --> carriage return to \r replace numtochar(9) with numtochar(92) & numtochar (116) in pString --> tab to \t replace numtochar(8) with numtochar(92) & numtochar(98) in pString --> backspace to \b break case "sqlite" default replace numtochar(39) with numtochar(39) & numtochar (39) in pString --> ' to '' break end SWITCH end _escapeStringByRef ___ use-revolution mailing list use-revolution@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: More problems with revExecuteSQL
Dave, Variable substitution doesn't escape characters for in version of Rev prior to 2.9. Just run all values you want to insert into the database through a function such as the following. Note that the PostGreSQL switch statement is based on what I read in the docs. I haven't used PostGreSQL myself so I haven't tested it. -- Trevor DeVore Blue Mango Learning Systems www.bluemangolearning.com-www.screensteps.com on _escapeStringByRef pDBType, @pString switch word 1 of pDBType case "valentina" replace numtochar(92) with numtochar(92) & numtochar(92) in pString --> \ to \\ replace numtochar(39) with numtochar(39) & numtochar(39) in pString --> ' to '' break case "mysql" replace numtochar(92) with numtochar(92) & numtochar(92) in pString --> \ to \\ replace numtochar(39) with numtochar(92) & numtochar(39) in pString --> ' to \' replace numtochar(34) with numtochar(92) & numtochar(34) in pString --> " to \" replace numtochar(0) with numtochar(92) & numtochar(48) in pString --> NULL to \0 replace numtochar(26) with numtochar(92) & numtochar(90) in pString --> Control-Z to \Z replace numtochar(10) with numtochar(92) & numtochar (110) in pString --> newline to \n replace numtochar(13) with numtochar(92) & numtochar (114) in pString --> carriage return to \r replace numtochar(9) with numtochar(92) & numtochar(116) in pString --> tab to \t replace numtochar(8) with numtochar(92) & numtochar(98) in pString --> backspace to \b break case "postgresql" replace numtochar(92) with numtochar(92) & numtochar(92) in pString --> \ to \\ replace numtochar(39) with numtochar(39) & numtochar(39) in pString --> ' to '' replace numtochar(12) with numtochar(92) & numtochar (102) in pString --> formfeed to \f replace numtochar(10) with numtochar(92) & numtochar (110) in pString --> newline to \n replace numtochar(13) with numtochar(92) & numtochar (114) in pString --> carriage return to \r replace numtochar(9) with numtochar(92) & numtochar(116) in pString --> tab to \t replace numtochar(8) with numtochar(92) & numtochar(98) in pString --> backspace to \b break case "sqlite" default replace numtochar(39) with numtochar(39) & numtochar(39) in pString --> ' to '' break end SWITCH end _escapeStringByRef ___ use-revolution mailing list use-revolution@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
Re: More problems with revExecuteSQL
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 (" & myParamete
Re: More problems with revExecuteSQL
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)
myResultunrecognized 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 Mu
Re: More problems with revExecuteSQL
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)
myResultunrecognized 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@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@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
More problems with revExecuteSQL
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)
myResultunrecognized 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***
___
use-revolution mailing list
use-revolution@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-revolution
