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

NLok

Member
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

1622436337025.png
1622436337025.png

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)
 
Last edited:

jahswant

Well-Known Member
Licensed User
Longtime User
If the application was already installed before you added that column , then try uninstalling the app then recompile.
 
Upvote 0

NLok

Member
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
 
Last edited:
Upvote 0

jahswant

Well-Known Member
Licensed User
Longtime User
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.
 
Upvote 0

aeric

Expert
Licensed User
Longtime User
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.
 
Upvote 0

udg

Expert
Licensed User
Longtime User
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:
B4X:
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.
 
Upvote 0

Mahares

Expert
Licensed User
Longtime User
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.
 
Upvote 0

NLok

Member
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? 😅

B4X:
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.
 

Attachments

  • SmallProject.zip
    16.9 KB · Views: 118
Upvote 0

Lucas Siqueira

Active Member
Licensed User
Longtime User
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? 😅

B4X:
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...
 

Attachments

  • SmallProjectOK.zip
    85.7 KB · Views: 124
Upvote 0
Top