How To Format MySQL Query In Xojo?

Databases
1

Okay, so my query is returning the total number of lunches ordered in a given week. It works perfectly in Workbench but Xojo is telling me “I have an error in my SQL syntax” Here’s what I have:

Var inrs As RowSet
Var insql As String = "SELECT (COUNT(CASE WHEN Monday = 'Eating In' THEN 1 END)" + _
"COUNT(CASE WHEN Tuesday = 'Eating In' THEN 1 END)" + _
"COUNT(CASE WHEN Wednesday = 'Eating In' THEN 1 END)" + _ 
"COUNT(CASE WHEN Thursday = 'Eating In' THEN 1 END)" + _
"COUNT(CASE WHEN Friday = 'Eating In' THEN 1 END)) AS TotalEatingInCount FROM lunch;"

inrs = db.SelectSQL(insql)

Thanks!

2

I don’t see commas separating columns, also pay attention to keep proper spaces, because things like:

SELECT *
FROM TABLE;

When translated without care as

Var sql As String = _
"SELECT *" +_
"FROM TABLE;"

ends as this “SELECT *FROM TABLE;”

And needs extra spaces as

Var sql As String = _
"SELECT * " +_
"FROM TABLE;"

You wrote
SELECT ... COUNT()COUNT() …
instead of
SELECT ... COUNT(), COUNT() …

1 Like
3

You could break execution at the statement:

inrs = db.SelectSQL(insql)

and then examine inrs in the debugger to see what you have actually created.

4

Hidden Xojo feature, you can also paste the exact code from workbench with a backslash before the string, like this:


Var inrs As RowSet
Var insql As String = \"SELECT (COUNT(CASE WHEN Monday = 'Eating In' THEN 1 END),
COUNT(CASE WHEN Tuesday = 'Eating In' THEN 1 END),
COUNT(CASE WHEN Wednesday = 'Eating In' THEN 1 END),
COUNT(CASE WHEN Thursday = 'Eating In' THEN 1 END),
COUNT(CASE WHEN Friday = 'Eating In' THEN 1 END)) AS TotalEatingInCount FROM lunch;"

inrs = db.SelectSQL(insql)

Not sure your SQL is valid, but you’ll get the general idea. A backslash before the string will allow to have multiple lines and paste directly from workbench.

Note, syntax highlighting might not work correctly, but the compiler should parse the string correctly.

2 Likes
5

Oh wow, great tip. Thanks!

6

As Rick said, as a good habit, I always add a space to my strings when I build complex SQL :slight_smile:

Jean-maurice