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

Peter Lewis · Apr 20, 2020

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

B4X:

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 · Apr 20, 2020

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

and ExecuteTableView is the original code or was modified?

OliverA · Apr 20, 2020

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

B4X:

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 · Apr 20, 2020

OliverA said:
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

B4X:

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 · Apr 20, 2020

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

combogroups.value shows what?

OliverA · Apr 20, 2020

Peter Lewis said:
Thank you for you reply but i got this error using that

I did say:

OliverA said:
B4X:

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

Peter Lewis said:
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 · Apr 20, 2020

roerGarcia said:
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 · Apr 20, 2020

Peter Lewis said:
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 · Apr 20, 2020

OliverA said:
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.

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

Peter Lewis

Active Member

roerGarcia

Active Member

OliverA

Expert

Peter Lewis

Active Member

roerGarcia

Active Member

OliverA

Expert

Peter Lewis

Active Member

OliverA

Expert

Peter Lewis

Active Member

Similar Threads

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

Active Member

Active Member

Expert

Active Member

Active Member

Expert

Active Member

Expert

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency