Page 1 of 1
Putting Variable into SQL
Posted: Thu Jul 25, 2013 3:10 am
by 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.
Re: Putting Variable into SQL
Posted: Thu Jul 25, 2013 3:46 am
by 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.
Re: Putting Variable into SQL
Posted: Thu Jul 25, 2013 3:52 am
by makeshyft
Re: Putting Variable into SQL
Posted: Thu Jul 25, 2013 8:15 pm
by 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.
Re: Putting Variable into SQL
Posted: Fri Jul 26, 2013 4:55 am
by 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.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Re: Putting Variable into SQL
Posted: Mon Aug 05, 2013 10:01 am
by 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