SQL Syntax question

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 461
Joined: Thu Nov 13, 2008 6:48 am
Location: Winnipeg, Manitoba

SQL Syntax question

Post by marksmithhfx » Thu Dec 15, 2011 7:09 am

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
Mac OS 10.12.1, Xcode 8.0, LC 8.1.1, iOS 9.3.5
Targets: Mac, iOS

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 461
Joined: Thu Nov 13, 2008 6:48 am
Location: Winnipeg, Manitoba

Re: SQL Syntax question

Post by marksmithhfx » Thu Dec 15, 2011 7:29 am

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:
Mac OS 10.12.1, Xcode 8.0, LC 8.1.1, iOS 9.3.5
Targets: Mac, iOS

Bernard
Posts: 184
Joined: Sat Apr 08, 2006 10:14 pm
Location: London, England

Re: SQL Syntax question

Post by Bernard » Thu Dec 15, 2011 3:25 pm

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.

marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 461
Joined: Thu Nov 13, 2008 6:48 am
Location: Winnipeg, Manitoba

Re: SQL Syntax question

Post by marksmithhfx » Fri Dec 16, 2011 4:11 am

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
Mac OS 10.12.1, Xcode 8.0, LC 8.1.1, iOS 9.3.5
Targets: Mac, iOS

Jason1234
Posts: 42
Joined: Sat Jun 18, 2011 9:20 am

Re: SQL Syntax question

Post by Jason1234 » Thu Dec 29, 2011 12:21 am

Bernard...

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

Saved me loads of trouble. :)

Regards

Jason
Windows / MAC / IOS / Android - Deployment
Build 5.5.4 / & Community Version 6.1

Post Reply

Return to “Databases”