Write a chunk of sql into a variable

[jalz]

Hi Guys,

I've got the following sql statement in live code, which I would like to write and execute as elegantly as possible. My connection ID is stored in a field called gConnID. I've got the code below broken down, but its not working, can someone help me to write it broken down.

Code: Select all

put "CREATE TEMPORARY TABLE tmp AS SELECT customerID, title, first_name, last_name, position, telephone, mobile, email FROM customer_contact WHERE customerID=" && var_customerID && ";" into tTheSQLQuery1
   put "UPDATE tmp SET customerID=" && new_customer_id &&";" into tTheSQLQuery1
   put "INSERT INTO customer_contact(customerID, title, first_name, last_name, position, telephone, mobile, email) SELECT * FROM tmp; " into tTheSQLQuery1
   put "DROP TABLE tmp;" into tTheSQLQuery1
   
   revExecuteSQL gConnID, tTheSQLQuery1

[bangkok]

You put 4 SQL queries.... into only one var, without executing it ! Use "after"

[Simon]

That's what I saw, just the OP's use of "broken down" had me confused.
I thought it meant "shortened code".

Simon

[edgore]

One more thing that pops out - you should not be using ; at the end of SQL queries in livecode. Pretty sure you need single quotes around you values too.

Also, is there a reason you have the connection id in a field, rather than using a global variable? Or, even better, closing the connection when it's not needed and reconnecting when you need to in order to reduce overhead on the database?

Lastly, look up the merge function. It will change your life.