Page 1 of 1

SQL Syntax question

Posted: Thu Dec 15, 2011 7:09 am
by marksmithhfx
I wonder if someone could help me out with the following. What I want to do is query the database to pull all records where the deliver_date field in the database contains the same value as the date contained in my global variable called globalDate. I'm just not sql savvy enough to know how to write this in LC. Here's what I have (which I know if I try will fail)...

Code: Select all

global globaldate, gConnectID

on preopencard
   put "SELECT eventid,mothers_name,fsum,delivery_date FROM master where delivery_date = globaldate" into tQuery
   put revDataFromQuery(,,gConnectID,tQuery) into tRecords
 etc
Thanks a bunch
-- Mark

Re: SQL Syntax question

Posted: Thu Dec 15, 2011 7:29 am
by marksmithhfx
Ah, somewhat embarrassed, but while waiting for an answer I fooled around a bit and stumbled on the solution

global globaldate, gConnectID

on preopencard
put "SELECT eventid,mothers_name,fsum,delivery_date FROM master where delivery_date =" & quote & globaldate & quote into tQuery
put revDataFromQuery(,,gConnectID,tQuery) into tRecords

Thanks anyway if you were thinking of answering.... :oops:

Re: SQL Syntax question

Posted: Thu Dec 15, 2011 3:25 pm
by Bernard
Here is how I deal with the construction of SQL statements or even commands to shell out to the operating system (or URL construction, for that matter...)

Your original was this:
put "SELECT eventid,mothers_name,fsum,delivery_date FROM master where delivery_date = globaldate" into tQuery

I would do this:

put "SELECT eventid,mothers_name,fsum,delivery_date FROM master where delivery_date = '[[globaldate]]' " into tQueryTemplate
put merge(tQueryTemplate) into tQuery

The merge() function will substitute variables in your literal string, for the values in those variables.

I find all the fiddling about with & and quote too fiddly. This way, I can see right off in the IDE that I have got a well-formed statement (or command or URL). Then I can check that the subsequent substitution works, knowing that at least the text was well-formed before any subsequent substitution of values.

Re: SQL Syntax question

Posted: Fri Dec 16, 2011 4:11 am
by marksmithhfx
Bernard wrote:I would do this:

put "SELECT eventid,mothers_name,fsum,delivery_date FROM master where delivery_date = '[[globaldate]]' " into tQueryTemplate
put merge(tQueryTemplate) into tQuery

The merge() function will substitute variables in your literal string, for the values in those variables.

I find all the fiddling about with & and quote too fiddly. This way, I can see right off in the IDE that I have got a well-formed statement (or command or URL). Then I can check that the subsequent substitution works, knowing that at least the text was well-formed before any subsequent substitution of values.
Awesome, thanks Bernard... I really appreciate the tip.

-- Mark

Re: SQL Syntax question

Posted: Thu Dec 29, 2011 12:21 am
by Jason1234
Bernard...

That is a truly great tip!.... really makes building a query from variables easy.

Saved me loads of trouble. :)

Regards

Jason