Android Question Why SQL "WHERE" cannot detect column / no such column ?

[NLok]

Hi everyone, why I put the column name 'CustomModule', but I can't get this column.


Dim cursor1 As Cursor
cursor1 = SQL1.ExecQuery("SELECT * FROM MenuCategory WHERE CustomModule = '' ORDER BY SeqNo")

If cursor1.RowCount > 0 Then
cursor1.Position = 0
lblCategory1.Text = cursor1.GetString("Name")
btnCategory1.Tag = cursor1.GetString("ID")
Log("Name1 : " & cursor1.GetString("Name"))
End If

Error occurred on line: 326 (Menu)
android.database.sqlite.SQLiteException: no such column: CustomModule (code 1): , while compiling: SELECT * FROM MenuCategory WHERE CustomModule = '' ORDER BY SeqNo
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:952)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:506)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:223)
at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:211)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:348)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:197)
at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:262)
at b4a.example.menu._refreshcategory(menu.java:437)
at b4a.example.menu._activity_create(menu.java:426)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:351)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
at b4a.example.menu.afterFirstLayout(menu.java:105)
at b4a.example.menu.access$000(menu.java:17)
at b4a.example.menu$WaitForLayout.run(menu.java:83)
at android.os.Handler.handleCallback(Handler.java:739)
at android.os.Handler.dispatchMessage(Handler.java:95)
at android.os.Looper.loop(Looper.java:148)
at android.app.ActivityThread.main(ActivityThread.java:5541)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:853)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:737)

 

[jahswant]

If the application was already installed before you added that column , then try uninstalling the app then recompile.

 

[NLok]

If the application was already installed before you added that column , then try uninstalling the app then recompile.

Hi Jahswant ,I need to delete the (B4A Example)application of B4A Bridge and install it again, right?

If right, I tried it and can't

 

[jahswant]

B4A Example App

 

[NLok]

B4A Example App

Ya, I have reinstalled it and still cannot

 

[aeric]

It’s recommended to use Resultset instead of Cursor.

 

[NLok]

It’s recommended to use Resultset instead of Cursor.

Hi aeric, I tried to use the Resultset , but there are still no such columns.

 

[jahswant]

Hi aeric, I tried to use the Resultset , but there are still no such columns.

Please tell us where is your database located ?

 

[KMatle]

SELECT * FROM MenuCategory WHERE CustomModule = '' ORDER BY SeqNo

The SQL statement looks incorrect. There's nothing behind the first "

 

[aeric]

Hi aeric, I tried to use the Resultset , but there are still no such columns.

Sorry actually it is not solving the problem but just a recommended way to write cross platform app in B4X.

 

[jahswant]

he SQL statement looks incorrect. There's nothing behind the first "

The query is correct. The error seems to come from the database location. I think he add that column after the first compile and did not update the database file.

 

[aeric]

What do you get when you query the table to show all columns?

SELECT * FROM MenuCategory

 

[alwaysbusy]

The SQL statement looks incorrect.

That was my first reaction too, but I then realized those '' must be two single quotes, else the IDE would've have complained.

 

[aeric]

We can see there are a few “code smell” here:
1. Not using resultset
2. Not using prepared statement

I guess the code come from a quite old sample project. I recommend the OP please go through the SQL tutorial.

However, to help you solve this question if you insist with the existing code, I advice you to copy out that section and upload as a small project. It is difficult to guess which part you made a mistake.

 

[udg]

I read somewhere that if you use quotes for column names when you CREATE a table than some sqlite implementations needs you to use quotes with SELECT.
If that applies, the following query should works:

SELECT * FROM MenuCategory WHERE "CustomModule" = '' ORDER BY "SeqNo"

Something similar could be done with square brackets, I presume.
Anyway, using smart string literals, IMHO, is always better than a string between double quotes when building a query or a DB statement.

 

[LucaMs]

Always use the 2nd "variant" of sql commands, parameterized queries (see ExecQuery2)

 

[Mahares]

cursor1 = SQL1.ExecQuery("SELECT * FROM MenuCategory WHERE CustomModule = '' ORDER BY SeqNo")

Now that you had a chance to digest all the posts responding to your thread, what did you do to solve it. The code you posted in post #1 looks correct, although you would have been better off with a parameterized query and Resultset instead of Cursor. An unresolved thread is a wasted thread.

 

[NLok]

Sorry and thanks to everyone for your help, I am busy with other things these days and need time to read your comments and research. But when I copied it to a small project, I used File.DirInternal to run it. Although it ran successfully, no errors (no such column) were displayed anymore. But I used this "SELECT * FROM MenuCategory ORDER BY SeqNo" to display the data, but added WHERE CustomModule ='' but it did not display the data, but it can still be run. Therefore, I deleted the WHERE in the original project, but ORDER BY is also wrong (no column), so I checked whether the code is the same, so I recreated the DatabaseFile, and it worked. But there is no data display after adding WHERE CustomModule ='' .

I think half of it is because of the DataBaseFile problem

But when I use this(code below), it has shown that sql has no Users table on the first page. Then it took a long time to solve it, just reinstall B4A-Bridge to get it done. I don't know why, is there any reason? Or am I using the wrong kvs? ?

shared = rp.GetSafeDirDefaultExternal("")
kvs.Initialize(shared, "Data")
If FirstTime = True Then
    If File.Exists(shared, "DB.db") = False Then
      File.Copy(File.DirAssets, "DB.db", shared, "DB.db")
      SQL1.Initialize(shared, "DB.db", True)
    Else
      SQL1.Initialize(shared, "DB.db", True)
    End If
End If

Thanks again to everyone who discussed this issue.

 

[aeric]

I think you are confused with KVS and SQL.

 

[Lucas Siqueira]

Sorry and thanks to everyone for your help, I am busy with other things these days and need time to read your comments and research. But when I copied it to a small project, I used File.DirInternal to run it. Although it ran successfully, no errors (no such column) were displayed anymore. But I used this "SELECT * FROM MenuCategory ORDER BY SeqNo" to display the data, but added WHERE CustomModule ='' but it did not display the data, but it can still be run. Therefore, I deleted the WHERE in the original project, but ORDER BY is also wrong (no column), so I checked whether the code is the same, so I recreated the DatabaseFile, and it worked. But there is no data display after adding WHERE CustomModule ='' .

I think half of it is because of the DataBaseFile problem

But when I use this(code below), it has shown that sql has no Users table on the first page. Then it took a long time to solve it, just reinstall B4A-Bridge to get it done. I don't know why, is there any reason? Or am I using the wrong kvs? ?

shared = rp.GetSafeDirDefaultExternal("")
kvs.Initialize(shared, "Data")
If FirstTime = True Then
    If File.Exists(shared, "DB.db") = False Then
      File.Copy(File.DirAssets, "DB.db", shared, "DB.db")
      SQL1.Initialize(shared, "DB.db", True)
    Else
      SQL1.Initialize(shared, "DB.db", True)
    End If
End If

Thanks again to everyone who discussed this issue.

there are some flaws in your project...

1) use whenever possible File.DirInternal instead of rp.GetSafeDirDefaultExternal(""), also don't use File.DirRootExternal, it will give you a headache with permissions... File.DirInternal doesn't need write or permission reading

2) centralize the database and kvs variables and permissions in the starter module, this will help you start these variables only once and use them in other modules, you need to leave the public variables to be accessed from other modules. .
Ex:
Public SQL1 As SQL
Public rp As RuntimePermissions
Public shared As String
Public kvs As KeyValueStore

3) in the starter's create, put it to check if the database file exists, if it doesn't, copy it...
check if sql1 is started, if not start it...
check if kvs is started, if not start it...

In short, your database could not find the columns, as they did not exist in the database, because when you created the screen in the secondActivity module the variable sql1 had not been started and ended up creating a new database, but this time...