Android Question [SOLVED] Strange behaviour in SQLite in different SDKs - (No such column in JOIN query)

[hatzisn]

Hi everyone,

take a look at the following query:

SELECT [10_Departments].[DepID] AS DepID, [70_Types].[TypeName] AS TypeName,
         [10_Departments].[DepName] AS DepName, [10_Departments].[Description] AS Description,
         [10_Departments].[HTML] AS HTML,[70_Pictures].[PictureFilename] AS PictureFilename,
         [40_AvailableDeps].[DepID] AS AvailDepID
         FROM (([70_Types] INNER JOIN (([80_Deps_Pictures] INNER JOIN [10_Departments] ON
         [80_Deps_Pictures].[DepID] = [10_Departments].[DepID]) INNER JOIN [70_Pictures] ON
         [80_Deps_Pictures].[PicID] = [70_Pictures].[PicID])
         ON [70_Types].[TypeID] = [10_Departments].[Type])
         LEFT JOIN [40_AvailableDeps] ON [10_Departments].[DepID] = [40_AvailableDeps].[DepID])
         WHERE [10_Departments].[Type] = 2 ORDER BY [10_Departments].[DepID]

The previous query with the same database installed both in Android 4.2.2 and Android 7.0 in the second works perfect but in the first it reports the error that the [10_Departments].[DepID] column was not found. I was selecting before [10_Departments].* and it was reporting that the table [10_Departments] was not found. The fact is that in both SDK cases it is there as it is the same database installed with the b4a bridge...

Any insights anyone?

Thanks


Edit - Both in DB Browser for SQLLite and SQLite Expert Personal 5 the query executes against the database with no problem.
Edit#2 - SQL library connects to the database as in another activity I read data from it with no problem (plain query - no join query - not the same table).
Edit#3 - I tried to read the records from the table with a plain query (f.e. "SELECT * FROM [10_Departments]") and it executes with no problem (the table is there).

 

[RB Smissaert]

Hi everyone,

take a look at the following query:

SELECT [10_Departments].[DepID] AS DepID, [70_Types].[TypeName] AS TypeName,
         [10_Departments].[DepName] AS DepName, [10_Departments].[Description] AS Description,
         [10_Departments].[HTML] AS HTML,[70_Pictures].[PictureFilename] AS PictureFilename,
         [40_AvailableDeps].[DepID] AS AvailDepID
         FROM (([70_Types] INNER JOIN (([80_Deps_Pictures] INNER JOIN [10_Departments] ON
         [80_Deps_Pictures].[DepID] = [10_Departments].[DepID]) INNER JOIN [70_Pictures] ON
         [80_Deps_Pictures].[PicID] = [70_Pictures].[PicID])
         ON [70_Types].[TypeID] = [10_Departments].[Type])
         LEFT JOIN [40_AvailableDeps] ON [10_Departments].[DepID] = [40_AvailableDeps].[DepID])
         WHERE [10_Departments].[Type] = 2 ORDER BY [10_Departments].[DepID]

The previous query with the same database installed both in Android 4.2.2 and Android 7.0 in the second works perfect but in the first it reports the error that the [10_Departments].[DepID] column was not found. I was selecting before [10_Departments].* and it was reporting that the table [10_Departments] was not found. The fact is that in both SDK cases it is there as it is the same database installed with the b4a bridge...

Any insights anyone?

Thanks


Edit - Both in DB Browser for SQLLite and SQLite Expert Personal 5 the query executes against the database with no problem.
Edit#2 - SQL library connects to the database as in another activity I read data from it with no problem (plain query - no join query - not the same table).
Edit#3 - I tried to read the records from the table with a plain query (f.e. "SELECT * FROM [10_Departments]") and it executes with no problem (the table is there).

As shown the SQL wouldn't run due to the non-matching brackets.
What are the SQLite versions of the different Android versions?
Are you sure you are connecting to the same database?

RBS

 

[OliverA]

Where is the dB located on the phone? Assets directory? Somewhere else?

 

[hatzisn]

As shown the SQL wouldn't run due to the non-matching brackets.
What are the SQLite versions of the different Android versions?
Are you sure you are connecting to the same database?

RBS

Hi, thanks for answering. I am not sure what you mean by saying non-matching brackets... All the brackets as well as the parentheses in my query have matches. After all it works in Android 7.0. I do not know how to tell the versions of the Android SQLite... How is this possible? I can tell you though that I created the database in Microsoft access and converted it to a SQLite database with a tool and manipulated a little the DB in SQLite expert personal 5 and also at last I added/changed data in it from .Net with the Devart.SQLite provider. The database works in 7.0 and also in 4.2.2. In the second case as I wrote in edit#3 I can select the data from the table with a plain query (f.e. SELECT * FROM [10_Depatments]). As far as it has to do with the last question B4A bridge installs the same database in both cases as it is included in the files folder. I believe it has something to do with the join query (maybe because of different handling in 4.2.2?). Thanks a lot...

 

[hatzisn]

Where is the dB located on the phone? Assets directory? Somewhere else?

Hi, the database is coppied from assets in a directory called dbs which is created and accessed using runtime permissions implementation. I have no problem accessing the DB. I believe the problem is in the query itself as mentioned in edits #2 and #3. Thanks for answering.

 

[RB Smissaert]

Hi, thanks for answering. I am not sure what you mean by saying non-matching brackets... All the brackets as well as the parentheses in my query have matches. After all it works in Android 7.0. I do not know how to tell the versions of the Android SQLite... How is this possible? I can tell you though that I created the database in Microsoft access and converted it to a SQLite database with a tool and manipulated a little the DB in SQLite expert personal 5 and also at last I added/changed data in it from .Net with the Devart.SQLite provider. The database works in 7.0 and also in 4.2.2. In the second case as I wrote in edit#3 I can select the data from the table with a plain query (f.e. SELECT * FROM [10_Depatments]). As far as it has to do with the last question B4A bridge installs the same database in both cases as it is included in the files folder. I believe it has something to do with the join query (maybe because of different handling in 4.2.2?). Thanks a lot...

> I am not sure what you mean by saying non-matching brackets...

Sorry, ignore that, just overlooked one.

> I do not know how to tell the versions of the Android SQLite... How is this possible?

A simple search in the forum will tell you. It is a simple select SQL. From the SQLite website:

sqlite_version()

The sqlite_version() function returns the version string for the SQLite library that is running. This function is an SQL wrapper around the sqlite3_libversion() C-interface.

RBS

 

[npsonic]

Hi everyone,

take a look at the following query:

SELECT [10_Departments].[DepID] AS DepID, [70_Types].[TypeName] AS TypeName,
         [10_Departments].[DepName] AS DepName, [10_Departments].[Description] AS Description,
         [10_Departments].[HTML] AS HTML,[70_Pictures].[PictureFilename] AS PictureFilename,
         [40_AvailableDeps].[DepID] AS AvailDepID
         FROM (([70_Types] INNER JOIN (([80_Deps_Pictures] INNER JOIN [10_Departments] ON
         [80_Deps_Pictures].[DepID] = [10_Departments].[DepID]) INNER JOIN [70_Pictures] ON
         [80_Deps_Pictures].[PicID] = [70_Pictures].[PicID])
         ON [70_Types].[TypeID] = [10_Departments].[Type])
         LEFT JOIN [40_AvailableDeps] ON [10_Departments].[DepID] = [40_AvailableDeps].[DepID])
         WHERE [10_Departments].[Type] = 2 ORDER BY [10_Departments].[DepID]

The previous query with the same database installed both in Android 4.2.2 and Android 7.0 in the second works perfect but in the first it reports the error that the [10_Departments].[DepID] column was not found. I was selecting before [10_Departments].* and it was reporting that the table [10_Departments] was not found. The fact is that in both SDK cases it is there as it is the same database installed with the b4a bridge...

Any insights anyone?

Thanks


Edit - Both in DB Browser for SQLLite and SQLite Expert Personal 5 the query executes against the database with no problem.
Edit#2 - SQL library connects to the database as in another activity I read data from it with no problem (plain query - no join query - not the same table).
Edit#3 - I tried to read the records from the table with a plain query (f.e. "SELECT * FROM [10_Departments]") and it executes with no problem (the table is there).

Do not use square brackets. Sqlite supports them only for the MSSql compability reasons. It is most likely cause of your problem.

 

[hatzisn]

I have done a check and selected all the distinct values (not with *) seen in my query above that are related to the [10_Departments] table with a plain query and the [DepID] is there and all the other values are there. This is getting straight to the twilight zone...

 

[npsonic]

Did you try to do query without square brackets? Change
[10_Departments].[DepID] to
10_Departments.DepID and so on.

 

[hatzisn]

Do not use square brackets. Sqlite supports them only for the MSSql compability reasons. It is most likely cause of your problem.

Hi npsonic,

I added the brackets because the problem was there before I add them... Just to check with no luck.
My answer mentioned in the previous post is done with brackets... And I tested what is mentioned in my previous
answer both in Android 4.2.2 and Android 7.0 and it strangely works but in Android 4.2.2 in the immediate next line where the query in the first post is executed I get the "No such column: [10_Departments].[DepID]".

Thanks for answering

 

[RB Smissaert]

Do not use square brackets. Sqlite supports them only for the MSSql compability reasons. It is most likely cause of your problem.

I would be surprised if that was the problem.
Been using square brackets for years with no problem.
This discusses quoting of keywords and identifiers in SQLite:
https://www.sqlite.org/lang_keywords.html

RBS

 

[npsonic]

I would be surprised if that was the problem.
Been using square brackets for years with no problem.
This discusses quoting of keywords and identifiers in SQLite:
https://www.sqlite.org/lang_keywords.html

RBS

Recently I had exactly same kind of problem and it was gone when I removed square brackets.
Square brackets are also not needed with sqlite so why would you use them?

 

[hatzisn]

When the table names start with numbers I believe you have to escape them.

 

[Mahares]

Square brackets are also not needed with sqlite so why would you use them?

If a table name starts with a number, you must use brackets:
CREATE TABLE [2_Table] will work, CREATE TABLE 2_Table will give you an error.
@npsonic why are you confusing @hatzisn?
@hatzisn: Sometimes the best way to have members help you is to have a small project that reproduces the problem.

 

[hatzisn]

I found a solution. The joins of the query have to be rearranged and remove the parentheses. It has to be converted to this:

SELECT [10_Departments].[DepID] AS DepID, [70_Types].[TypeName] AS TypeName,
         [10_Departments].[DepName] AS DepName, [10_Departments].[Description] AS Description,
         [10_Departments].[HTML] AS HTML,[70_Pictures].[PictureFilename] AS PictureFilename,
         [40_AvailableDeps].[DepID] AS AvailDepID
         FROM [80_Deps_Pictures] INNER JOIN [10_Departments] ON [80_Deps_Pictures].[DepID] = [10_Departments].[DepID]
         INNER JOIN [70_Pictures] ON [80_Deps_Pictures].[PicID] = [70_Pictures].[PicID]
         INNER JOIN [70_Types] ON [70_Types].[TypeID] = [10_Departments].[Type]
         LEFT JOIN [40_AvailableDeps] ON [10_Departments].[DepID] = [40_AvailableDeps].[DepID]
         WHERE [10_Departments].[Type] = 2 ORDER BY [10_Departments].[DepID]

Thanks anyone for answering...