B4J Question Sql Tableview filter problem

[dgoss]

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.

		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

	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

 

[jmon]

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.

 

[Erel]

Use parameterized queries:

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

 

[inakigarm]

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.

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

You have to add the ' before and after the txtSearchFor.Text string variable:

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:

[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)

 

[Erel]

What I usually do is build the query as a String variable, Log(the variable) and after, append to DBUtils command:

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.

 

[LucaMs]

Why not use parameterized queries?

I love them (almost how much I love Maps )

 

[inakigarm]

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

 

[jmon]

the parametrized queries (for me) seem more prone to error coding queries

I like to build my queries like this:

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.