Basic SQL question

[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'

[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 & ")"

[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:

Code: Select all

revExecuteSQL dbID, dbSQL

Alternate suggestions are welcome.

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

[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?

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

[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

[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]

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

[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

[SparkOut]

You have "sanitised" your db username and password in the connection string there, haven't you Ron?

[bangkok]

You have "sanitised" your db username and password in the connection string there, haven't you Ron?

Oh my.... I mean "oh"(mine)"God".

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

[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

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