B4J Question Sql Tableview filter problem

D

dgoss

Member
Licensed User
Longtime User
hi all

I I have a database with a couple of Fields in a Table that are two words ie. Phone No, First Name, I get the field name from a combobox(cmbSearch.value).mySQL is ref to SQL
i'm using the following code to retrieve what I need, it works when the Field name is one word ie. DoB, but not two words.

B4X: 
		DBUtils.ExecuteTableView(mySql,"SELECT rowid, [First Name], [Middle Name], [Surname] From Employee Where " & _
		 cmbSearch.Value & " = " & _
		  txtSearchFor.Text ,Null,0, TableView1)

I use the following code to load Tableview on App Start and it does the job

B4X: 
	DBUtils.ExecuteTableView(mySql,"SELECT rowid, [First Name], [Middle Name], [Surname] FROM Employee", Null, 0, TableView1)
when I try to filter entries I get the following
the bit(near "No": syntax error) refers to the Phone No field in database
error log:-
Error occurred on line: 232 (DBUtils)
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near "No": syntax error)
at org.sqlite.DB.newSQLException(DB.java:383)
at org.sqlite.DB.newSQLException(DB.java:387)
at org.sqlite.DB.throwex(DB.java:374)
at org.sqlite.NativeDB.prepare(Native Method)
at org.sqlite.DB.prepare(DB.java:123)
at org.sqlite.PrepStmt.<init>(PrepStmt.java:42)
at org.sqlite.Conn.prepareStatement(Conn.java:404)
at org.sqlite.Conn.prepareStatement(Conn.java:399)
at org.sqlite.Conn.prepareStatement(Conn.java:383)
at anywheresoftware.b4j.objects.SQL.ExecQuery2(SQL.java:283)
at b4j.example.dbutils._executetableview(dbutils.java:496)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:612)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:226)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:90)
at anywheresoftware.b4a.BA$2.run(BA.java:165)
at com.sun.javafx.application.PlatformImpl.lambda$null$170(PlatformImpl.java:295)
at com.sun.javafx.application.PlatformImpl$$Lambda$48/1960829338.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl.lambda$runLater$171(PlatformImpl.java:294)
at com.sun.javafx.application.PlatformImpl$$Lambda$47/410424423.run(Unknown Source)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$145(WinApplication.java:101)
at com.sun.glass.ui.win.WinApplication$$Lambda$36/186276003.run(Unknown Source)
at java.lang.Thread.run(Thread.java:745)

any help to point me the right direction is very much appreciated
 
Last edited:
Sort by date Sort by votes

jmon

Well-Known Member
Licensed User
Longtime User
Have you tried replacing the brackets [ ] with back ticks ` ` (not single quotes)?
If it doesnt work, consider changing your columns names with underscores instead of spaces. I also recommend lowercases for simplicity.

[Edit] i read that the recommended way for sqlite is using double quotes " " for column names.
 
Upvote 0
I

inakigarm

Well-Known Member
Licensed User
Longtime User
You have to add the ' before and after the txtSearchFor.Text string variable:

B4X: 
DBUtils.ExecuteTableView(mySql,"SELECT rowid, [First Name], [Middle Name], [Surname] From Employee Where " & _
         cmbSearch.Value & " ='" & _
          txtSearchFor.Text& "'" ,Null,0, TableView1)
What I usually do is build the query as a String variable, Log(the variable) and after, append to DBUtils command:
B4X: 
[CODE]
SQLQuery="SELECT rowid, [First Name], [Middle Name], [Surname] From Employee Where " & _
 cmbSearch.Value & " ='" &  txtSearchFor.Text & "'"
Log(SQLQuery)
DBUtils.ExecuteTableView(mySql,SQLQUery ,Null,0, TableView1)
[/CODE]

This way is more easier to detect syntax errors when building Queries.

(don't know if [column] works)
 
Upvote 0

Erel

B4X founder
Staff member
Licensed User
Longtime User
inakigarm said:
What I usually do is build the query as a String variable, Log(the variable) and after, append to DBUtils command:
Click to expand...
Why not use parameterized queries?

They are simpler and safer.

Specifically your code will fail if the user text includes an apostrophe and it is also vulnerable to sql injections.
 
Upvote 0
I

inakigarm

Well-Known Member
Licensed User
Longtime User
Sure you're right but I like to have more control when building the queries and the parametrized queries (for me) seem more prone to error coding queries (not as easy code reading)

Maybe it's time to modify bad code habits...
 
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
inakigarm said:
the parametrized queries (for me) seem more prone to error coding queries
Click to expand...
I like to build my queries like this:
B4X: 
Sub test(location As String)
    Dim args As List
    args.Initialize
   
    Dim Query As StringBuilder
    Query.Initialize
   
    Query.Append("SELECT * FROM users")
    Query.Append(" WHERE")
   
    Query.Append(" name = ?")
    args.Add("john")
       
    Query.Append(" AND")
   
    Query.Append(" age = ?")
    args.Add(25)   

    If location <> "" Then
        Query.Append(" AND")
       
        Query.Append(" location = ?")
        args.Add(location)   

    End If

    Query.Append(";")
   
    sqlite.ExecQueryAsync("sqlite", Query, args)       
End Select

This way it's very customisable and it would be difficult to have coding errors. As you see I can even have a If..then condition.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Question
B4J Question Unusual SQLite error. Works in editor and not in B4J.
2
Replies
26
Views
3K
MichalK73
P
  • Question
B4J Question SQL Delete
Replies
7
Views
1K
Pelky
P
  • Question
B4J Question [B4X Table] java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (near ")"
Replies
6
Views
2K
Waldemar Lima
  • Question
B4J Question SQL with b4j problem
Replies
7
Views
965
EnriqueGonzalez
P
  • Question
B4J Question SQLite error or missing database
Replies
1
Views
1K
EnriqueGonzalez
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…