Multiline sql

What is the best way to make a long sql string that expands on multiple lines? How is a string broken from line to line?

Short example
sql = “CREATE TABLE Medico0001Patients (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,”_
“signupdate DATE NOT NULL;”

Is this correct?

sql = "some string " + _
  "more string " + _
  "even more"

I sometimes do like :
sql = “…”
sql=sql+"…"

For even greater control, consider placing your SQL into a constant. Or you can do something like this:

dim sql as string = _
  "SELECT " + &uA + _
  "  * " + &uA + _
  "FROM " + &uA + _
  "  table1, " + &uA + _
  "  table2 " + &uA + _
  "WHERE " + &uA + _
  "  someCondition"

If you view that in the debugger, you will see:

SELECT
  *
FROM
  table1, 
  table2
WHERE
  someCondition

But a constant is easier. :slight_smile:

Jean-Yves, using line continuation is faster because the compiler will turn that into one static for you rather than concatenating at runtime.

Thanks a lot for the input.

When you come to selects have a look at http://bkeeney.com/smartsql/ . Wayyyy easier to use than those ugly multiline statements.

We prefer an array + Join for complex sql statements:

[code]dim cSQL, aSQL() as string

aSQL.Append “CREATE TABLE Medico0001Patients (”
aSQL.Append "id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, "
if MyCondition then aSQL.Append "my_optional_field Text, " // easy for using conditions
aSQL.Append “signupdate DATE NOT NULL”
aSQL.Append “);”

cSQL = Join(aSQL, EndOfLine) // cSQL is helpfull for debugging purposes

// execute sql…
[/code]

I prefer using constants as per http://blog.xojo.com/tip-formatted-text/strings-in-xojo this blog.

and I prefer storing PREPARED STATEMENTS in a separate file and take that into a lookup dictionary right at the start of the application. Since my statements also get some additional parameters it’s easier to setup multi database connections.