Page 1 of 2
Basic SQL question
Posted: Sat Dec 06, 2008 9:55 pm
by Ron Zellner
How do I put the contents of a field into an SQL INSERT statement?
Code: Select all
put field "StartTime" of card "MainData1" into Entry1
put "INSERT INTO InitialData VALUES (Entry1, Entry2, .............
I tried this
Code: Select all
put "'" & field "StartTime" of card "MainData1" &"'" into Entry1
but that didn't help
I get error:
Unknown column 'Entry1' in 'field list'
Posted: Sat Dec 06, 2008 10:06 pm
by SparkOut
So far as I can see you're putting the correct thing into the variable Entry1, but you're not using the variable in the INSERT INTO statement - Entry1 in that statement is part of the literal text.
You should probably need to say:
Code: Select all
put "INSERT INTO InitialData VALUES (" & Entry1 & comma & Entry2 & comma & Entry3 & ")"
solution
Posted: Sun Dec 07, 2008 12:47 am
by Ron Zellner
Don't know how elegant it is, but this works:
Code: Select all
put "INSERT INTO InitialData VALUES ('" & field "Subject" of card "MainData1" & "'," into dbSQL
put "'" & field "SubmitTime" of card "MainData1" & "'," after dbSQL
and so on for all of the 31 variables- this being the last:
Code: Select all
put "'" & field "RatingsF" of card "Data1" & "')" after dbSQL
and then this to submit to the DB:
Alternate suggestions are welcome.
Posted: Sun Dec 07, 2008 7:47 am
by Janschenkel
Or you can rely on the template query mechanism, and use an array to get the data safely to the database.
Code: Select all
put "INSERT INTO InitialData VALUES(:1,:2,:3)" into dbSQL
put field "Subject" of card "MainData1" into dbData[1]
put field "SubmitTime" of card "MainData1" into dbData[2]
put field "RatingsF" of card "MainData1" into dbData[3]
revExecuteSQL dbID, dbSQL, "dbData"
The database driver will then make sure to replace the ":1", ":2", ":3" parts in the SQL query with the correctly encoded data from the first/second/third element of the dbData array - putting quotes when needed and escaping quotes inside your data.
Jan Schenkel.
Can I include a graphic object?
Posted: Sun Dec 07, 2008 3:12 pm
by Ron Zellner
Thanks, that looks good- I'll try it.
I also have a graphic (image "ImageDrawing") where the person makes a drawing with the pencil tool. How could I include that graphic object in the SQL upload process? Can it's content be included in the array?
Posted: Sun Dec 07, 2008 6:40 pm
by Janschenkel
Binary data is handled slightly differently, as you have to tell Revolution which variable is binary data by prepending its element key in the array with "*b"
Code: Select all
put "INSERT INTO InitialData VALUES(:1,:2,:3,:4)" into dbSQL
put field "Subject" of card "MainData1" into dbData[1]
put field "SubmitTime" of card "MainData1" into dbData[2]
put field "RatingsF" of card "MainData1" into dbData[3]
put image "ImageDrawing" of card "MainData1" into dbData["*b4"]
revExecuteSQL dbID, dbSQL, "dbData"
Of course, if you apply 'base64Encode' to the image content, you have a string that you can safely put into a text column, and all you have to remember is to 'base64Decode' it after you've retrieved it from the database.
Code: Select all
put "INSERT INTO InitialData VALUES(:1,:2,:3,:4)" into dbSQL
put field "Subject" of card "MainData1" into dbData[1]
put field "SubmitTime" of card "MainData1" into dbData[2]
put field "RatingsF" of card "MainData1" into dbData[3]
put base64Encode(image "ImageDrawing" of card "MainData1") into dbData[4]
revExecuteSQL dbID, dbSQL, "dbData"
HTH,
Jan Schenkel.
I'm anxious to try that, but..
Posted: Sun Dec 07, 2008 7:35 pm
by Ron Zellner
I have this line (line 20), but it produces an error:
Code: Select all
put "INSERT INTO InitialData VALUES(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32)" into dbSQL
I can't see a problem in the line, I've retyped most of it in case there were hidden characters or something.
I'm using phpMyAdmin-2.5.4
and then.....
Posted: Mon Dec 08, 2008 12:51 am
by Ron Zellner
I tried adding spaces between the commas and Colons
Code: Select all
put "INSERT INTO InitialData VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32)" into dbSQL
That changes the error statement a little:
That is the point in the array where the put command changes a little in relation to the content of the fields.
Code: Select all
put field "Ratings" of card "Data1" into dbData[17]
put field "RatingsF" of card "Data1" into dbData[18]
set the itemDelimiter to tab
put item 2 of line 1 of field "RatingsF" of card "Data1" into dbData[19]
put item 2 of line 2 of field "RatingsF" of card "Data1" into dbData[20]
Posted: Mon Dec 08, 2008 6:53 am
by Janschenkel
Can you give us the complete script? It looks like the database driver fails to replace the ":i" placeholders with the actual data when you call the 'revExecuteSQL' command.
Jan Schenkel.
full script
Posted: Mon Dec 08, 2008 3:02 pm
by Ron Zellner
Here is the full script. I had emptied all of the fields in case there might be data there causing the problem. However, the other script above does work to submit all of these fields.
Code: Select all
on mouseUp
Answer "Connected to the Internet and ready to submit?" with "Yes" or "No"
if it ="Yes" then
Open DB code into dbresult
if dbresult is a number then
put dbresult into dbID
else
answer dbResult
end if
--put "INSERT INTO InitialData VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32)" into dbSQL --no spaces
put "INSERT INTO InitialData VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32)" into dbSQL --spaces added
put field "Subject" of card "MainData1" into dbData[1]
put field "SubmitTime" of card "MainData1" into dbData[2]
put field "SubmitDate" of card "MainData1" into dbData[3]
put field "Summary" of card "MainData1" into dbData[4]
put field "NumberOfEraser" of card "MainData1" into dbData[5]
put field "NumberOfPencil" of card "MainData1" into dbData[6]
put field "ToolChoice" of card "MainData1" into dbData[7]
put field "PencilSpent" of card "MainData1" into dbData[8]
put field "ToolSwitch" of card "MainData1" into dbData[9]
put field "EraserSpent" of card "MainData1" into dbData[10]
put field "StartTime" of card "MainData1" into dbData[11]
put field "TimeSeq" of card "MainData1" into dbData[12]
put field "NumbersOfTitleCorrection" of card "MainData1" into dbData[13]
put field "Titles" of card "MainData1" into dbData[14]
put field "CompleteDraw" of card "MainData1" into dbData[15]
put field "Age" of card "MainData1" into dbData[16]
put field "Ratings" of card "Data1" into dbData[17]
put field "RatingsF" of card "Data1" into dbData[18]
set the itemDelimiter to tab
put item 2 of line 1 of field "RatingsF" of card "Data1" into dbData[19]
put item 2 of line 2 of field "RatingsF" of card "Data1" into dbData[20]
put item 2 of line 3 of field "RatingsF" of card "Data1" into dbData[21]
put item 2 of line 4 of field "RatingsF" of card "Data1" into dbData[22]
put item 2 of line 5 of field "RatingsF" of card "Data1" into dbData[23]
put item 2 of line 6 of field "RatingsF" of card "Data1" into dbData[24]
put item 2 of line 7 of field "RatingsF" of card "Data1" into dbData[25]
put item 2 of line 8 of field "RatingsF" of card "Data1" into dbData[26]
put item 2 of line 9 of field "RatingsF" of card "Data1" into dbData[27]
put item 2 of line 10 of field "RatingsF" of card "Data1" into dbData[28]
put item 2 of line 11 of field "RatingsF" of card "Data1" into dbData[29]
put item 2 of line 12 of field "RatingsF" of card "Data1" into dbData[30]
put item 2 of line 13 of field "RatingsF" of card "Data1" into dbData[31]
put item 2 of line 14 of field "RatingsF" of card "Data1" into dbData[32]
put image "ImageDrawing" of card "Activity1" into dbData["*b33"]
revExecuteSQL dbID, dbSQL, "dbData"
if last char of dbSQL = ";" then delete last char of dbSQL
revExecuteSQL dbID, dbSQL
put the result into sqlResult
if sqlResult is a number then
answer "Query OK: " & sqlResult & " record(s) altered." with "OK"
else
answer sqlResult with "OK"
end if
end if
end mouseUp
Posted: Mon Dec 08, 2008 3:09 pm
by SparkOut
You have "sanitised" your db username and password in the connection string there, haven't you Ron?
Posted: Mon Dec 08, 2008 3:15 pm
by bangkok
SparkOut wrote:You have "sanitised" your db username and password in the connection string there, haven't you Ron?
Oh my.... I mean "oh"(mine)"God".
Posted: Mon Dec 08, 2008 3:32 pm
by SparkOut
Nice pun bangkok, but can I suggest that you alter it so as not to even give a hint as to the previous post? Thanks.
Fix alternate to handle an image
Posted: Mon Dec 08, 2008 3:40 pm
by Ron Zellner
This format works, can I alter the last item so that it will be able to handle the image rather than another field?
Code: Select all
on mouseUp
Answer "Connected to the Internet and ready to submit?" with "Yes" or "No"
if it ="Yes" then
open DB code into dbresult
if dbresult is a number then
put dbresult into dbID
else
answer dbResult
end if
set the itemDelimiter to tab
put "INSERT INTO InitialData VALUES ('" & field "Subject" of card "MainData1" & "'," into dbSQL
put "'" & field "SubmitTime" of card "MainData1" & "'," after dbSQL
put "'" & field "SubmitDate" of card "MainData1" & "'," after dbSQL
put "'" & field "Summary" of card "MainData1" & "'," after dbSQL
put "'" & field "NumberOfEraser" of card "MainData1" & "'," after dbSQL
put "'" & field "NumberOfPencil" of card "MainData1" & "'," after dbSQL
put "'" & field "ToolChoice" of card "MainData1" & "'," after dbSQL
put "'" & field "PencilSpent" of card "MainData1" & "'," after dbSQL
put "'" & field "ToolChoice" of card "MainData1" & "'," after dbSQL
put "'" & field "EraserSpent" of card "MainData1" & "'," after dbSQL
put "'" & field "StartTime" of card "MainData1" & "'," after dbSQL
put "'" & field "TimeSeq" of card "MainData1" & "'," after dbSQL
put "'" & field "NumbersOfTitleCorrection" of card "MainData1" & "'," after dbSQL
put "'" & field "Titles" of card "MainData1" & "'," after dbSQL
put "'" & field "CompleteDraw" of card "MainData1" & "'," after dbSQL
put "'" & field "Age" of card "MainData1" & "'," after dbSQL
put "'" & field "Ratings" of card "Data1" & "'," after dbSQL
put "'" & field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 1 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 2 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 3 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 4 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 5 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 6 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 7 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 8 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 9 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 10 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 11 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 12 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 13 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & item 2 of line 14 of field "RatingsF" of card "Data1" & "'," after dbSQL
put "'" & image "ImageDrawing" of card "Activity1" & "')" after dbSQL
put dbSQL into field "Review" of card "MainData1"
if last char of dbSQL = ";" then delete last char of dbSQL
revExecuteSQL dbID, dbSQL
put the result into sqlResult
if sqlResult is a number then
answer "Query OK: " & sqlResult & " record(s) altered." with "OK"
else
answer sqlResult with "OK"
end if
end if
end mouseUp
Posted: Mon Dec 08, 2008 3:51 pm
by SparkOut
OK, now trying to answer the question!
In the version with placeholders, you have 32 placeholders and the image data is the 33rd element of the array so it will not get inserted into the table.
Then you seemed to be inserting twice:
Code: Select all
revExecuteSQL dbID, dbSQL, "dbData"
if last char of dbSQL = ";" then delete last char of dbSQL
revExecuteSQL dbID, dbSQL
put the result into sqlResult
The first time with the dbData array to be passed to revExecuteSQL, and then again without the array - which I would think is the problem, as the dbSQL string still contained the placeholders, which would then be treated as literal text, and obviously fail.