Suddenly having an odd issue with wxSqlite3...

Talk here about issues with one of the components hosted at wxCode, or suggest features for it.
Post Reply
ouch67
Earned some good credits
Earned some good credits
Posts: 135
Joined: Sun Mar 23, 2008 12:09 am

Suddenly having an odd issue with wxSqlite3...

Post by ouch67 »

So I have this SQL String I store in a variable:

Code: Select all

select distinct PartNumber from WEBSITE where PartNumber in (select PartNumber from WEBSITE where PartNumber != \"\" group by PartNumber having (COUNT(PartNumber) > 1)) order by PartNumber;
This looks for duplicate item numbers in our DB and list any it finds. (There should be none Ideally)
And this has always worked for me over the years. However after updating wxWidgets (f1425dad131cd44d49ce5182378738b685fcac66) and wxSqlite (957d0c5c129bc5d1a98236a3f135a9ff626b8b43) to their current Master trunk versions. This now gives me an error saying "No such column"

What's odd is that simpler statements work fine. So "select PartNumber from WEBSITE;" works. It seems like whenever there is nested queries it fails with that error. But yet if I use the command line utility programs for SQLite3 and paste in:

Code: Select all

select distinct PartNumber from WEBSITE where PartNumber in (select PartNumber from WEBSITE where PartNumber != "" group by PartNumber having (COUNT(PartNumber) > 1)) order by PartNumber;
It works fine, I also send the variable to a text box just before sending it to gettable so I know that the statement is making it to wxSqlite3 un-corrupted.

I've attached an sqlite3 db file that just has the table name WEBSITE with a column called PartNumber so you can try this out yourself.

Any help would be appreciated. Thanks!

Oh, this on windows 7 home, and using mingw64 configured as: i686-8.1.0-posix-sjlj-rt_v6-rev0
Attachments
Sample.zip
(310 Bytes) Downloaded 249 times
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: Suddenly having an odd issue with wxSqlite3...

Post by utelle »

ouch67 wrote: Tue May 26, 2020 9:53 pm So I have this SQL String I store in a variable:

Code: Select all

select distinct PartNumber from WEBSITE where PartNumber in (select PartNumber from WEBSITE where PartNumber != \"\" group by PartNumber having (COUNT(PartNumber) > 1)) order by PartNumber;
This looks for duplicate item numbers in our DB and list any it finds. (There should be none Ideally)
And this has always worked for me over the years. However after updating wxWidgets (f1425dad131cd44d49ce5182378738b685fcac66) and wxSqlite (957d0c5c129bc5d1a98236a3f135a9ff626b8b43) to their current Master trunk versions. This now gives me an error saying "No such column"
Which version of wxSQLite3 did you use before upgrading to the latest version?
ouch67 wrote: Tue May 26, 2020 9:53 pm What's odd is that simpler statements work fine. So "select PartNumber from WEBSITE;" works. It seems like whenever there is nested queries it fails with that error. But yet if I use the command line utility programs for SQLite3 and paste in:

Code: Select all

select distinct PartNumber from WEBSITE where PartNumber in (select PartNumber from WEBSITE where PartNumber != "" group by PartNumber having (COUNT(PartNumber) > 1)) order by PartNumber;
It works fine, I also send the variable to a text box just before sending it to gettable so I know that the statement is making it to wxSqlite3 un-corrupted.
Which SQLite command line utility did you use? I doubt that you used the SQLite command shell coming with wxSQLite3 releases, because it shows the same behaviour.

Looking closer at your select statements, I see that you used double quotes to delimit an empty string literal. This in not conforming to the SQL standard. According to the SQL standard single quotes are used to delimit string literals, while double quotes are used to delimit names like column or table names.

Since wxSQLite3 version 4.4.4 the SQLite library is compiled with the option SQLITE_DQS=0. The effect is that SQLite is more strict. That is, delimiting string literals with double quotes is no longer allowed (as it was in prior versions). If you use double quotes SQLite will now look for an identifier (here a column name) - and "" is not a valid column name.

To solve the problem you can either replace the double quotes by single quotes in your query, or compile wxSQLite3 with the preprocessor option SQLITE_DQS=1.

Regards,

Ulrich
Post Reply