B4J Question [SOLVED] SQLite Query and display in a Tableview with Parameter from Combobox

[Peter Lewis]

Hi all

I have been trying now for a few hours to get this right and I do not understand WHAT I am doing wrong.

I copied the line of code from another area in my program and that works perfectly

This one gives me an error of

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such column: Family)

Where Family is the value of the ComboBox. I never told it that ComboGroups.value was a Column

DBUTILS.ExecuteTableView(sql1,"Select GroupName, Name, Loginname , Cellular from GroupsMembers WHERE GroupName = "&ComboGroups.Value ,Null,0,TableViewMembers)

Any comments would be appreciated

thank you

 

[roerGarcia]

Log("Select GroupName, Name, Loginname , Cellular from GroupsMembers WHERE GroupName = " & ComboGroups.Value) shows what?

and ExecuteTableView is the original code or was modified?

 

[OliverA]

You created a malformed SQL statement by not quoting the combo box value properly (according to the underlying database used). If you don’t want to worry about quoting and the possibility of SQL injection, try

DBUTILS.ExecuteTableView(sql1,"Select GroupName, Name, Loginname , Cellular from GroupsMembers WHERE GroupName = ?", Array as object (ComboGroups.Value ),0,TableViewMembers)

the array as object may also be array as string (not in front of computer at the moment)

 

[Peter Lewis]

You created a malformed SQL statement by not quoting the combo box value properly (according to the underlying database used). If you don’t want to worry about quoting and the possibility of SQL injection, try

DBUTILS.ExecuteTableView(sql1,"Select GroupName, Name, Loginname , Cellular from GroupsMembers WHERE GroupName = ?", Array as object (ComboGroups.Value ),0,TableViewMembers)

the array as object may also be array as string (not in front of computer at the moment)

Thank you for you reply but i got this error using that

java.lang.ClassCastException: class [Ljava.lang.Object; cannot be cast to class [Ljava.lang.String; ([Ljava.lang.Object; and [Ljava.lang.String; are in module java.base of loader 'bootstrap')

What do you mean by Malformed, I would like to learn.

Thank you

 

[roerGarcia]

Log("Select GroupName, Name, Loginname , Cellular from GroupsMembers WHERE GroupName = " & ComboGroups.Value) shows what? THIS IS malformed.

combogroups.value shows what?

 

[OliverA]

Thank you for you reply but i got this error using that

I did say:

the array as object may also be array as string (not in front of computer at the moment

What do you mean by Malformed, I would like to learn

Any unquoted text items in your sql statement are either seen as a table name or column name, depending on the context of the sql statement

 

[Peter Lewis]

Log("Select GroupName, Name, Loginname , Cellular from GroupsMembers WHERE GroupName = " & ComboGroups.Value) shows what? THIS IS malformed.

combogroups.value shows what?

The answer from that is

Select GroupName, Name, Loginname , Cellular from GroupsMembers WHERE GroupName = Family

Which is correct, Family is the Filter that i am looking for

 

[OliverA]

The answer from that is

Which is not what you intended. Your flavor of sql sees that as comparing the value in the column GroupName to the value of the column of Family, a column that does not exist and therefore the error message in your first post. Even though, this shows that if possible you should not build your own sql statements. Use ExecuteTableViews ability to work with parameterized queries to let jdbc handle the creation of the proper sql for you. For my example to work, just replace array as object with array as string

 

[Peter Lewis]

Which is not what you intended. Your flavor of sql sees that as comparing the value in the column GroupName to the value of the column of Family, a column that does not exist and therefore the error message in your first post. Even though, this shows that if possible you should not build your own sql statements. Use ExecuteTableViews ability to work with parameterized queries to let jdbc handle the creation of the proper sql for you. For my example to work, just replace array as object with array as string

That is perfect, Thank you. I was going to write out the whole procedure and not use DBUtils but you have saved me some time.