Help with SQL syntax on SQLite

Hi there!

I would appreciate a little help with an SQL Select I´m attempting to run against an SQLITE DB. Any ideas why this Select won´t run on?

SELECT B.ID AS Bale_ID, P.Code AS P_Code, B.Permanent_Bale_ID, b.Bale_Storage_Location, S.Contract_Number, S.Contract_Date AS S_Contract_Date, S.Date_Ship FROM BALES AS B INNER JOIN SHIPMENTS AS S ON S.ID = B.Shipment_ID INNER JOIN Partners AS P ON P.ID = S.Partner_ID ORDER BY S.Date_Ship

The Select runs fine on “DB Browser for SQLite” but returns a DB error when try the statement using a DB.SQLSelect(SQL).

Any help is much appreciated!

remove the “AS” keywords

FROM BALES  B INNER JOIN SHIPMENTS  S 

AS should be syntactically fine in both the selected column list an table aliases and the join clauses
https://sqlite.org/lang_select.html

what error are you getting in Xojo ?

I´m getting a
1 - near “?SELECT”: syntax error

[quote=455124:@Dave S]remove the “AS” keywords

FROM BALES B INNER JOIN SHIPMENTS S [/quote]

Thanks for your reply, this looks like the cause of my problem. I will read the link that Norman posted.

I tried this but it didn´t work either

ELECT B.ID AS Bale_ID, P.Code AS P_Code, B.Permanent_Bale_ID, b.Bale_Storage_Location, S.Contract_Number, S.Contract_Date AS S_Contract_Date, S.Date_Ship FROM BALES B INNER JOIN SHIPMENTS S ON S.ID = B.Shipment_ID INNER JOIN Partners P ON P.ID = S.Partner_ID ORDER BY S.Date_Ship

Got some reading to do!

Thanks Dave and Norman

Still can´t get this to work. I removed all the Aliases from the columns and tables list, here is the Xojo code I´m attempting to run. I know… I know… this is just a test, I will code it better as soon as I get the SQL syntax to work. This too works on a

Here is my xojo code:

[code]SQL = "?SELECT B.ID, P.Code, B.Permanent_Bale_ID, "
SQL = SQL + "B.Bale_Storage_Location, "
SQL = SQL + "S.Contract_Number, S.Contract_Date, "
SQL = SQL + "S.Date_Ship "
SQL = SQL + "FROM BALES B "
SQL = SQL + "INNER JOIN SHIPMENTS S "
SQL = SQL + "ON S.ID = B.Shipment_ID "
SQL = SQL + "INNER JOIN Partners P "
SQL = SQL + "ON P.ID = S.Partner_ID "
SQL = SQL + "ORDER BY S.Date_Ship DESC "

// I know I should not use this for debbuging :slight_smile:
MsgBox(“Load Bales sql” + EndOfLine + SQL)

Dim RS As RecordSet
RS = App.DB.SQLSelect(SQL)

If App.DB.Error Then
MsgBox(“Error reading the Bales list” + EndOfLine + _
App.DB.ErrorCode.ToText + " - " + App.DB.ErrorMessage + EndOfLine + _
“Bales_Container - Load_Bales”)
Exit
Else[/code]

Help? :wink:

It might help to get an SQLite Database manager to test your query
Personally I suggest Tadpole but full disclosure… I wrote it :slight_smile:

One of my current apps has a query that is well over 100 lines long (lots of UNION)… and Tadpole saved me hours as I could test sections to find the error…

Once each section worked… I put it into Xojo like you did above…

Suggestion… you might find this format easier to read

SQL = _
"?SELECT B.ID, P.Code, B.Permanent_Bale_ID, " +_
"              B.Bale_Storage_Location, " +_
 "             S.Contract_Number, S.Contract_Date, " +_
              "S.Date_Ship " +_
 "FROM BALES B " +_
 "INNER JOIN SHIPMENTS S " +_
 "    ON S.ID = B.Shipment_ID " +_
 "INNER JOIN Partners P " +_
 "     ON P.ID = S.Partner_ID " +_
 "ORDER BY S.Date_Ship DESC "

[quote=455141:@Dave S]It might help to get an SQLite Database manager to test your query
Personally I suggest Tadpole but full disclosure… I wrote it :slight_smile:

One of my current apps has a query that is well over 100 lines long (lots of UNION)… and Tadpole saved me hours as I could test sections to find the error…

Once each section worked… I put it into Xojo like you did above…

Suggestion… you might find this format easier to read

SQL = _ "?SELECT B.ID, P.Code, B.Permanent_Bale_ID, " +_ " B.Bale_Storage_Location, " +_ " S.Contract_Number, S.Contract_Date, " +_ "S.Date_Ship " +_ "FROM BALES B " +_ "INNER JOIN SHIPMENTS S " +_ " ON S.ID = B.Shipment_ID " +_ "INNER JOIN Partners P " +_ " ON P.ID = S.Partner_ID " +_ "ORDER BY S.Date_Ship DESC " [/quote]

I´ll get Tadpole! I have tested the SQL code using DB Browser for SQLite Database manager and it runs OK in it. I´ll disect the SQL syntax bit by bit. Your formatting suggestion looks good, will try!

[quote=455137:@Hector Marroquin]I´m getting a
1 - near “?SELECT”: syntax error[/quote]
Error Code 1 is a “SQL logic error or missing database”. Since the SQL looks fine, you should make sure you’re connecting to the correct db file.

Also try a simple “Select * from BALES” to see if you get an error, and each of the other tables.

Hector, you have “invisible” characters on your code. I guess you copied the SELECT statement from some app and pasted directly to Xojo.

I think if you use the option to “clean invisible ascii characters”, then your code will work.

Thanks for your reply. The SELECT * FROM Bales was the first thing I tested and it works fine, right now I´m rebuilding the SQL statement bit by bit, so far this simple join is working:

SELECT B.ID AS BALE_ID, B.Permanent_Bale_ID  FROM Bales AS B INNER JOIN Shipments AS S ON B.SHIPMENT_ID = S.ID 

I´ll test a multi table join next.

Thanks again for your response

[quote=455147:@Alberto DePoo]Hector, you have “invisible” characters on your code. I guess you copied the SELECT statement from some app and pasted directly to Xojo.

I think if you use the option to “clean invisible ascii characters”, then your code will work.[/quote]

Ohhhhh this never crossed my mind! Will check right now!

[quote=455147:@Alberto DePoo]Hector, you have “invisible” characters on your code. I guess you copied the SELECT statement from some app and pasted directly to Xojo.

I think if you use the option to “clean invisible ascii characters”, then your code will work.[/quote]

Thanks Alberto! You saved me a night of no sleep. I indeed copied the code from a SQLite manager where I wrote it, never thought I was getting invisible characters.

wow… no SQLite Managment program should even allow such a thing to occur :frowning:

From my tests, several do that, don’t know why.

Glad my time wasted some time ago could help Hector today :wink:

Usually, Xojo remove gremlin characters…

I think you need to manually do it, I don’t know if there is an automatic setting:

Nice !