Page 1 of 1
format Long SQL query
Posted: Sun Feb 21, 2016 11:47 pm
by parsec
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
Re: format Long SQL query
Posted: Mon Feb 22, 2016 6:34 pm
by Klaus
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.
Best
Klaus
Re: format Long SQL query
Posted: Mon Feb 22, 2016 7:09 pm
by parsec
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
Re: format Long SQL query
Posted: Mon Feb 22, 2016 7:15 pm
by Klaus
Hi John,
&& is the same as & but also adds a SPACE -> put ("I" &&"am" && "Klaus") = I am Klaus
\ will let LC know that the script continues on the next line.
Best
Klaus
Re: format Long SQL query
Posted: Mon Feb 22, 2016 7:33 pm
by phaworth
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
Re: format Long SQL query
Posted: Mon Feb 22, 2016 9:16 pm
by FourthWorld
The merge function can be very handy for long strings like SQL queries in which you need to insert a few values.
Re: format Long SQL query
Posted: Thu Feb 25, 2016 7:27 pm
by Adrian
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
Re: format Long SQL query
Posted: Fri Feb 26, 2016 12:52 pm
by parsec
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
Re: format Long SQL query
Posted: Fri Feb 26, 2016 12:58 pm
by Adrian
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
Re: format Long SQL query
Posted: Fri Feb 26, 2016 1:51 pm
by parsec
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
Re: format Long SQL query
Posted: Fri Feb 26, 2016 8:03 pm
by phaworth
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
Re: format Long SQL query
Posted: Sat Feb 27, 2016 3:51 pm
by parsec
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