format Long SQL query

[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

[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

[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

[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

[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

[FourthWorld]

The merge function can be very handy for long strings like SQL queries in which you need to insert a few values.

[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

[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

[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

[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

[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

[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