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)

 

[MicroDrie]

The Sqlite DB.DB database column CustomModule is defined as an INTEGER, not as a TEXT value. If it has no number (which is here the case), the content is NULL, not an empty TEXT with two quotes.

The table MenuCategory column Price value is INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. and the column CustomModule value is an empty text value ''.

The INTEGER value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. If there is no value I think that the NULL value must be used not the '' for an empty TEXT value.

 

[NLok]

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

Thank you very much for your example guidance and explanations, now my project is smoother.?

 

[NLok]

The Sqlite DB.DB database column CustomModule is defined as an INTEGER, not as a TEXT value. If it has no number (which is here the case), the content is NULL, not an empty TEXT with two quotes.
View attachment 114516
The table MenuCategory column Price value is INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. and the column CustomModule value is an empty text value ''.
View attachment 114517
The INTEGER value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value. If there is no value I think that the NULL value must be used not the '' for an empty TEXT value.

My goodness, I was too careless to notice its value.? Thank you for your careful explanation, let me understand how to distinguish the usage of values. Next time I will pay more attention to these small details.?

 

[MicroDrie]

Next time I will pay more attention to these small details.?

It's kind of old school wisdom that my then colleague had in the second half of the eighties. He always checked very strictly for empty text and handling of 0 values (=0, <>0, <0, >0, NULL and "") values. The best choice is to create a text and numeric variable with a value. That's useful if you're going to test on that variable later. Especially with a database, searches become very complex if you have to take into account all the possibilities that a variable can have. It is not for nothing that you can specify the initial value when creating a database to avoid a lot of misery!