format Long SQL query

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
parsec
Posts: 27
Joined: Fri Jan 09, 2015 5:24 pm

format Long SQL query

Post by parsec » Sun Feb 21, 2016 11:47 pm

is any way that I format a long query like this one

Code: Select all

"Select Inventory.PLU_Number,Inventory.Plu_Description,Brands. Brand_Name,Department_Name,Inventory_Prices.Price From Inventory Inner Join Brands On Inventory.Brand_Number = Brands.Brand_Number Inner Join Departments On Inventory.Department_Number = Departments.Department_Number Inner Join Inventory_Prices On Inventory.PLU_Number = Inventory_Prices.PLU_Number" into tQuery
from a single line line as it apears the LC does not allow breaks?

I was looking to use something like this

Code: Select all

Select
  Inventory.PLU_Number,
  Inventory.Plu_Description,
  Brands.Brand_Name,
 Department_Name,
 Inventory_Prices.Price
From
  Inventory Inner Join
  Brands
    On Inventory.Brand_Number = Brands.Brand_Number Inner Join
  Departments
    On Inventory.Department_Number =
    Departments.Department_Number Inner Join
 Inventory_Prices
    On Inventory.PLU_Number = Inventory_Prices.PLU_Number
any thought how to deal with long statesmen
Thank you
J

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

Re: format Long SQL query

Post by Klaus » Mon Feb 22, 2016 6:34 pm

Hi J,

do like this:

Code: Select all

...
   put "Select Inventory.PLU_Number,Inventory.Plu_Description,Brands. Brand_Name,Department_Name,Inventory_Prices.Price From Inventory" && \
        "Inner Join Brands on Inventory.Brand_Number = Brands.Brand_Number Inner Join Departments On Inventory.Department_Number =" && \
        "Departments.Department_Number Inner Join Inventory_Prices On Inventory.PLU_Number = Inventory_Prices.PLU_Number" into tQuery
...
no idea how to deal with long statesmen however. :D

Best

Klaus

parsec
Posts: 27
Joined: Fri Jan 09, 2015 5:24 pm

Re: format Long SQL query

Post by parsec » Mon Feb 22, 2016 7:09 pm

Hi Klaus
Got it. Can you let me know what && and \ indicate to LC
Thank you for the help.

Funny I had a long night
" deal with long statesmen"
thank you again for all the help
John

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

Re: format Long SQL query

Post by Klaus » Mon Feb 22, 2016 7:15 pm

Hi John,

&& is the same as & but also adds a SPACE -> put ("I" &&"am" && "Klaus") = I am Klaus :D
\ will let LC know that the script continues on the next line.


Best

Klaus

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: format Long SQL query

Post by phaworth » Mon Feb 22, 2016 7:33 pm

Don;t think you can format it in exactly the way you want it but one way to make the code more readable is to break thew SQL statement into a series of constants and string them together to create the complete statement. For example;

Code: Select all

constant kSelect="Select Inventory.PLU_Number,Inventory.Plu_Description,Brands. Brand_Name,Department_Name,Inventory_Prices.Price From Inventory"
constant kJoin1="Inner Join Brands On Inventory.Brand_Number = Brands.Brand_Number"
constant kJoin2="Inner Join Departments On Inventory.Department_Number = Departments.Department_Number"
constant kJoin3="Inner Join Inventory_Prices On Inventory.PLU_Number = Inventory_Prices.PLU_Number"" 

put kSelect && kJoin1 && kJoin2 && kJoin3 into tQuery

FourthWorld
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 6720
Joined: Sat Apr 08, 2006 7:05 am
Location: Los Angeles
Contact:

Re: format Long SQL query

Post by FourthWorld » Mon Feb 22, 2016 9:16 pm

The merge function can be very handy for long strings like SQL queries in which you need to insert a few values.
Richard Gaskin
Community volunteer LiveCode Community Liaison

LiveCode development, training, and consulting services: Fourth World Systems: http://FourthWorld.com
LiveCode User Group on Facebook : http://FaceBook.com/groups/LiveCodeUsers/

Adrian
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 79
Joined: Wed Jul 11, 2012 5:03 pm

Re: format Long SQL query

Post by Adrian » Thu Feb 25, 2016 7:27 pm

Another way to make the code a bit more readable is to use "after", such as:

put "SELECT something.one,something.two" into tSQL
put " FROM sometable" after tSQL
put " WHERE this=that" after tSQL

Cheers,

Adrian
Last edited by Adrian on Fri Feb 26, 2016 12:53 pm, edited 1 time in total.

parsec
Posts: 27
Joined: Fri Jan 09, 2015 5:24 pm

Re: format Long SQL query

Post by parsec » Fri Feb 26, 2016 12:52 pm

Thank you for the Lessons I get the idea. I have several questions?
1 will any of the recommendation impact the sql query execution performance +/=
2 is it good idea or not to call queries that reside on the server side?
thank you again
John

Adrian
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 79
Joined: Wed Jul 11, 2012 5:03 pm

Re: format Long SQL query

Post by Adrian » Fri Feb 26, 2016 12:58 pm

I can't see any of the suggestions impacting the query processing. They are all simply ways of building up a string in a variable, which you then use in your query. There likely is some impact on the time to build up the string, but I would imagine it would be imperceptible.

As to whether using server-side queries are "a good idea", it really depends on what you are doing in any specific situation. The question is usually really more about where the database is (local, or server) and how the application is to work overall.

Cheers,

Adrian

parsec
Posts: 27
Joined: Fri Jan 09, 2015 5:24 pm

Re: format Long SQL query

Post by parsec » Fri Feb 26, 2016 1:51 pm

Thank you
lets say I like to have all queries on the server side. How can I possible pass the criteria row from LC field to the SQL querie on the server side
for ex:

Code: Select all

SELECT* FROM Projects
WHERE StartDate =   [Enter the start date] /code]
thank you

phaworth
Posts: 592
Joined: Thu Jun 11, 2009 9:51 pm

Re: format Long SQL query

Post by phaworth » Fri Feb 26, 2016 8:03 pm

As Adrian noted, the answer to whether to execute the queries on the server depends on a lot of things. How sensitive is your data? Do you care if your database gets hacked via SQL injection? And many others.

The simplest thing you can do to protect your security is to to use the variables list parameter to revDataFromQuery, etc. The dictionary entry explains that but here's an example. Insetad of "SELECT * FROM Projects Where StartDate='2016-02-26'", you would use "SELECT * FROM Projects WHERE StartDate=:1". You would then have a variable named, for example, tStartDate which would contain the date. Your call to revDataFromQuery would look like this:

put revDataFromQuery(,,gDBID,"SELECT * FROM Projects WHERE StartDate=:1","tStartDate") into tData

The result of this is that the SELECT statement and the start date value are sent to your server as separate communications. That protects you from SQL injection attack and also ahs the side benefit that you don;t have to write code to escape quote characters, etc in and data strings.

There are solutions with various levels of complexity after that. The most complex system I have implemented involves php scripts on a server that have all the SQL statements contained within them. I use the Livecode post command to send messages to my php script with a code that identifies the SQL statement to be executed and any data values it needs. That way, no SQL statements are ever transmitted over the web.

Pete

parsec
Posts: 27
Joined: Fri Jan 09, 2015 5:24 pm

Re: format Long SQL query

Post by parsec » Sat Feb 27, 2016 3:51 pm

Thank you
I will read about the post command. If any one has any pointers were to start it will be very much apreciated
thank you again
John

Post Reply

Return to “Databases”