SQL Syntax question

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

SQL Syntax question

Postby 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 OSX 10.7.5, Xcode 4.6.3, LC 6.6
Targets: Mac, Windows, iOS
marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
 
Posts: 371
Joined: Thu Nov 13, 2008 6:48 am
Location: Winnipeg, Manitoba

Re: SQL Syntax question

Postby 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 OSX 10.7.5, Xcode 4.6.3, LC 6.6
Targets: Mac, Windows, iOS
marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
 
Posts: 371
Joined: Thu Nov 13, 2008 6:48 am
Location: Winnipeg, Manitoba

Re: SQL Syntax question

Postby 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.
Bernard
 
Posts: 182
Joined: Sat Apr 08, 2006 10:14 pm
Location: London, England

Re: SQL Syntax question

Postby 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 OSX 10.7.5, Xcode 4.6.3, LC 6.6
Targets: Mac, Windows, iOS
marksmithhfx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
 
Posts: 371
Joined: Thu Nov 13, 2008 6:48 am
Location: Winnipeg, Manitoba

Re: SQL Syntax question

Postby 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
Jason1234
 
Posts: 42
Joined: Sat Jun 18, 2011 9:20 am


Return to Databases

Who is online

Users browsing this forum: Yahoo [Bot] and 2 guests