Using Global Variables within SQL Query

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
Jamie37
Posts: 53
Joined: Sat Dec 19, 2015 1:45 pm

Using Global Variables within SQL Query

Post by Jamie37 » Tue Feb 23, 2016 6:01 pm

Hello,

I followed the Livecode tutorial for basic connection and record adding and deleting in a SQL database. The one question I have is I have two input field for testing (Username and Password). The add record button puts these field names into a global variable and add the records to the database. The part I cannot do is the delete button. I want the delete button to take these variable and if there is matching records in the database, I want the record to be deleted. Hopefully I am explaining this well but hard to explain. Below is the code for the Delete button. I think I have made the global variables correct but cannot work out how to add them to the query.

Code: Select all

global tUsername
global tPassword

on mouseUp
global gConnectionID
if gConnectionID is not a number then
answer error "Please connect to the database first"
exit to top
end if

-- since you don't need a where clause, its really straight forward. No Where means all rows removed.
put "DELETE FROM Logininfo WHERE Username= 'tUsername' AND Password= 'tPassword'" into tSQL 

revExecuteSQL gConnectionID, tSQL

 if the result is not number then
    answer "All Records Deleted"
 else answer error "There was a problem clearing your table" & cr & the result
end mouseUp
Thanks
Jamie

Klaus
Posts: 11497
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: Using Global Variables within SQL Query

Post by Klaus » Tue Feb 23, 2016 7:03 pm

Hi Jamie,

you are supplying the STRINGS tUsername and tPassword and not the content of these variables!
...
## put "DELETE FROM Logininfo WHERE Username= 'tUsername' AND Password= 'tPassword'" into tSQL
put "DELETE FROM Logininfo WHERE Username='" & tUsername & "' AND Password= '" & tPassword & "'" into tSQL
...

Best

Klaus

Jamie37
Posts: 53
Joined: Sat Dec 19, 2015 1:45 pm

Re: Using Global Variables within SQL Query

Post by Jamie37 » Sat Feb 27, 2016 10:36 pm

HI Klaus,

That seems to delete all the records whatever I have in the fields?

SparkOut
Posts: 2067
Joined: Sun Sep 23, 2007 4:58 pm

Re: Using Global Variables within SQL Query

Post by SparkOut » Sat Feb 27, 2016 11:06 pm

While debugging, "answer tSQL" after the statement constructing the query and before executing, to check it matches your expectations.
Your code comments seem to imply you want to delete records with no where clause, but if you do, are the columns correctly stated, along with the correct variable contents? Klaus showed the correct way to build your query. Now you need to make sure that your query does what you expect.

Post Reply

Return to “Databases”