Sqlite valid join statements return errors

[Benjamin_Brannen]

If I run a simple sql statement it works ok.

sql = "select * from table1;" // -- works

If I add some type of join to the sql statement, it fails with error:

sql = "select t1.col1, t1.col2, t2.col2 " + _
" From table1 t1 " + _
" inner join table2 t2 on " + _
" t1.col1 = t2.col1 " + _
" Where t1.col2 <> t2.col2; "

An exception of class DatabaseException was not handled. The application must shut down.

Exception Message: near “select”: syntax error
Exception Error Number: 1

Typically when I run into this error I figure out a different way to solve the problem. In this particular case I’d like to find - if possible - the proper solution to letting sql answer the query instead of me coding around the error message.

I’m open to suggestions.

[Kem_Tekinay]

What happens if you highlight your text, right-click, then choose “Clean invisible ascii characters”?

[Benjamin_Brannen]

No invisible characters. I’m able to take the code that xojo generates and directly run it inside of SQLiteSutdio with no issues - but it fails inside xojo.

[DerkJ]

This can happen if a comma “,” or a dot “.” is wrong or missing or many other ways.

I’m able to take the code that xojo generates and directly run it inside of SQLiteSutdio with no issues - but it fails inside xojo.

Since you say the query works here but not there, it sounds like there are some characters in the string that should not be there (as @Kem_Tekinay suggests).

Otherwise i suggest you post the actual query here, including your code that runs the query

[TimStreater]

You should run this SQL statement through the sqlite3 CLI. The SqliteStudio thingy may be fixing up your SQL.

[Eugene_Dakin]

@Benjamin_Brannen

sql = "select t1.col1, t1.col2, t2.col2 " + _
" From table1 t1 " + _
" inner join table2 t2 on " + _
" t1.col1 = t2.col1 " + _
" Where t1.col2 <> t2.col2; "

Hello Benjamin,

I am taking a wild guess, and the Select statement might see two different tables: table1 and t1. Would the following SQL command work?

sql = "select t1.col1, t1.col2, t2.col2 " + _
" From table1 " + _
" inner join table2 t2 on " + _
" t1.col1 = t2.col1 " + _
" Where t1.col2 <> t2.col2; "

I removed t1 in the line that has From table1 t1.

Another part of the command that might be causing an issue is the semicolon. Maybe try this also:

sql = "select t1.col1, t1.col2, t2.col2 " + _
" From table1 t1 " + _
" inner join table2 t2 on " + _
" t1.col1 = t2.col1 " + _
" Where t1.col2 <> t2.col2 "

I hope one of these works for you.

[Tim_Hare]

You do have 2 columns in the select portion with the same name. You could try to disambiguate them using aliases.

select t1.col1, t1.col2, t2.col2 t2_col2 from table1 t1 ...

[Benjamin_Brannen]

I submitted a bug report. Let’s see what happens.
Thanks for the feedback.

[AlbertoD]

@Benjamin_Brannen

sql = "select t1.col1, t1.col2, t2.col2 " + _
" From table1 t1 " + _
" inner join table2 t2 on " + _
" t1.col1 = t2.col1 " + _
" Where t1.col2 <> t2.col2; "

Your code has invisible characters that Xojo can’t process. Some SQLite apps add that invisible character so when you copy/paste from those apps you will have those in your code.

[TimStreater]

@TimStreater

You should run this SQL statement through the sqlite3 CLI. The SqliteStudio thingy may be fixing up your SQL.

Did you try this?

[Benjamin_Brannen]

I did try running the code through different sqlite interfaces and it worked each time. Only when I put that same code in XOJO did it fail. I re-hand coded it this morning line for line and it worked.

That’s a bit embarrassing.

In the windows environment since the '80’s I’ve used a tool called vedit to show me invisible characters. Written in assembler, it’s very fast, and has a great feature that allows me to toggle between six different display modes using ‘Alt-D’. The modes I use most are ASCII, ASCII-Exended, HEX. In my mac I haven’t been able to find such a tool. I would love any suggestions.

or perhaps I could write my own - I keep saying that for like ten years but never get to it.

[AlbertoD]

@Benjamin_Brannen

I would love any suggestions.

Use Xojo:

[Kem_Tekinay]

Gee, I wish somebody had suggested that earlier…

[Benjamin_Brannen]

Yes sorry. I did see that suggestion and I did highlight and clean the characters but obviously I did not do that correctly. I’ll use that feature more heavy handedly now. Thanks.

[Kem_Tekinay]

Glad you figured it out.

Remember to mark this topic “solved”.

[brian_franco]

Kem,
how come you never mentioned this before?

[AlbertoD]

If you didn’t select all code and you started with select, then the invisible character was not selected. You need to select from the quote " at least, but is better to use Select All and then clean invisible ascii characters.

[Kem_Tekinay]

Honestly, it would be better if invisible characters were rendered in the first place.

[Kem_Tekinay]

feedback://showreport?report_id=63812

[Benjamin_Brannen]

I need to really thank everyone. Kem said the answer but I was in such a hurry I missed a lot. With AlbertoD’s step by step demonstration I could clearly see what I was missing. I agree whole-heartedly that gremlins should display by default. Thanks again.