Putting Variable into SQL

[sandybassett]

I'm building my first app, and practicing by putting hard-coded words into the different SQL fields, i.e.
put "INSERT into apts VALUES ('Lakepoint','1821 Dora Ave');" into tSQL
But I really need to insert the contents of an entry field into each, so I put the contents of each field into a local variable, but when I use that variable instead of the text, ie. CoName instead of Lakepoint, or text of CoName instead of Lakepoint it doesn't work. If I leave the single quotes in, it puts the word CoName or text of CoName in the SQL, and if I take out the single quotes it doesn't put any new record into the SQL. So what gives? How are you supposed to refer to a variable putting it into SQL? Thanks for the help.

[makeshyft]

do not include the ; at the end of your query

Also... please look at the SQL example stack available from runrev, and make sure you use some sort of SQL cleaning function.

Also remember you can combine strings together, which might make it easier to constuct them. use the & to put strings together. and also when constructing sql statements use the "after" to add new strings to the END of the one you are constructing.

[makeshyft]

Reading the livecode SQL primer will help alot.

http://lessons.runrev.com/s/lessons/fil ... n_id=30516

this might help too

http://forums.runrev.com/viewtopic.php? ... 4&start=30

[sandybassett]

Tom: Appreciate your help. I have read the links you said, but I went back and re-read them, looked at the video, and I do remember when using SQL with another language system that you have to be very exact when using the single quotes, double quotes and ampersand. So I went back and tried several different ways. The latest is:
put "INSERT into apts VALUES ('&tCoName&')" into tSQL
--put "INSERT into apts VALUES ('&tCoName&','1821 Dora Ave','32778','Tavares','FL','352-391-8459','sandyb@reagan.com','750.00','None')" into tSQL
As you can see, I commented out the longer line to try just one field. Neither one worked. When I switched the single quotes and the double quotes, it didn't work. When I put a space between the var name and the &s, it didn't work. But using double quotes everywhere, it showed the value of CoName var in the answer line just before it, but still either put in nothing or gave a dberr message. Don't know what's wrong. Can you copy me a line from a program that does work so I can start there? Thanks for the help.

[makeshyft]

Here you go bro...this works perfect... notice that I "clean" as I write the sql statement, and I don't clean every field. and I don't use the t g varaible naming convention....I make a global array where I store and access my data so my variable names are long but also easy to understand.

replace functions cleans the sql before it becomes sql.

"ConnectToUMPDatabase is a function that gives me a database connection and writes it into a key in my array.

------------------------------------------------------------------------------------------------

put empty into AddUserSQL // CLEAR SQL Query

ConnectToUMPDatabase "Local Users"

// Construct SQL Statement - Columns

put "INSERT into local_users (" into AddUserSQL
put "local_user_id" & "," after AddUserSQL
put "local_user_first_name" & "," after AddUserSQL
put "local_user_last_name" & "," after AddUserSQL
put "local_user_default_email" & "," after AddUserSQL
put "local_user_folder" & "," after AddUserSQL
put "local_user_default_color" & "," after AddUserSQL
put "local_user_avatar_type" & "," after AddUserSQL
put "local_user_avatar_link" & "," after AddUserSQL
put "local_user_avatar_meta" & "," after AddUserSQL
put "local_user_code_encrypt" & "," after AddUserSQL
put "option_fullscreen" & "," after AddUserSQL
put "option_usevoice" & ")" after AddUserSQL

// Construct SQL Statement - Values from INTERNAL NewUserArray

put " VALUES (" after AddUserSQL
put "'" & UMPInternalArray["New User"][1]["Local User ID"] & "'" & "," after AddUserSQL
replace "'" with "''" in UMPInternalArray["New User"][1]["First Name"]
put "'" & UMPInternalArray["New User"][1]["First Name"] & "'" & "," after AddUserSQL
replace "'" with "''" in UMPInternalArray["New User"][1]["Last Name"]
put "'" & UMPInternalArray["New User"][1]["Last Name"] & "'" & "," after AddUserSQL
put "'" & UMPInternalArray["New User"][1]["Default Email"] & "'" & "," after AddUserSQL
replace "'" with "''" in UMPInternalArray["New User"][1]["User Folder"]
put "'" & UMPInternalArray["New User"][1]["User Folder"] & "'" & "," after AddUserSQL
put "'" & UMPInternalArray["New User"][1]["Default Color"] & "'" & "," after AddUserSQL
put "'" & UMPInternalArray["New User"][1]["Default Avatar Type"] & "'" & "," after AddUserSQL
replace "'" with "''" in UMPInternalArray["New User"][1]["Default Avatar Link"]
put "'" & UMPInternalArray["New User"][1]["Default Avatar Link"] & "'" & "," after AddUserSQL
put "'" & UMPInternalArray["New User"][1]["Default Avatar Meta"] & "'" & "," after AddUserSQL
put "'" & UMPInternalArray["New User"][1]["Code Hash"] & "'" & "," after AddUserSQL
put "'" & UMPInternalArray["New User"][1]["Settings"]["Full Screen"] & "'" & "," after AddUserSQL
put "'" & UMPInternalArray["New User"][1]["Settings"]["Use Voice"] & "'" & ")" after AddUserSQL

// Execute Add User

local DBResponse
revExecuteSQL UMPInternalArray["Databases"]["Local Users Database ID"],AddUserSQL
put the result into DBResponse
if DBResponse is a number then
revCommitDatabase UMPInternalArray["Databases"]["Local Users Database ID"]
else
IT DIDN'T WORK! Show Error.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

[dave.kilroy]

Hi Sandy

I obviously don't have as much patience as @makeshyft and try to use placeholders where I can (see the dictionary entry for "revExecuteSQL") - thanks to Eleanor and others at RunRevLive 2013 who put me on to this method.

This is how I update data to a SQLite database which uses lists, variables and placeholders to create the SQL command - and as long as I set up the variables from when I do the 'SELECT' call (plus some checks) I can use this code to update any database table from any GUI in my app...

Code: Select all

    connectAppdb --calls a handler to connect to current SQLite database
    put gConnAppdbID into tDatabaseID --puts connectionID into local parameter 
    
    --populate arrays & build SQL string
    set the itemdelimiter to tab 
    put "UPDATE " & gCurrentTable & " SET " into tSQL --gCurrentTable holds name of current database table
    put 1 into xi
    repeat for each item i in sControlGroups --sControlGroups is a list of groups used in the GUI
        put item xi of gColumnNames after tCol[xi] --start to build array the tCol containing the list of field names
        put field 2 of group i of group "grpDetailControls" of card "cdMain" into tArray[xi] --get data from the GUI
        if xi >1 then put item xi of gColumnNames & "=:" & xi & ", " after tSQL --use all except the ID column...
        add 1 to xi
    end repeat
    delete char (len(tSQL) -1) to len(tSQL) of tSQL --remove trailing ":=:
    put " WHERE " & (item 1 of gColumnNames) & " =:1" after tSQL --now use the first item in gColumnNames 
    
    --do the update
    revExecuteSQL tDatabaseID,tSQL,"tArray" --the revExecuteSQL statement making use of placeholders
    put the result into tReturn --should return a number, used later to check for success of operation
    closeAppdb --calls a handler to close the database

Dave