How to pass a String variable into sqlite query?

demiguel...

How to pass a String variable into sqlite query?

in Contributed Library Questions • 1 year ago

Hi, sorry for asking so much but its hard to find doc on the sqlite library. I have another problem,

I want to make a query based on the name of the street, but i have to pass it from a variable, like using "+mystreetString+"

But it doesn't work. It does work however if my variable is an int and not a String, i guess because String is a class and int is a primitive / datatype? If i just type the name of the street in the query command it works fine too, but it doesnt help me much. Here's the code, i've commented out the lines that work in case u want to test:

Thanks for the help, by the way, queries are really slow... is this normal? my database is just 27 mb, i actually transfered all my files to a database cuz i thought it would process data much faster but it seems to be even slower.

import de.bezier.data.sql.*;
SQLite db;
void setup()
{
size( 400, 400);
db = new SQLite( this, "data/sevilla_price_map.db" );
}
void draw(){
String street="Calle Regina";
int daytoday=day()-2;// u see, my database is not up to date...
int input_price=2700;
if ( db.connect() && frameCount==20) {
db.query("SELECT * FROM variable_data WHERE \"idname\"="+street+" AND \"day\"="+daytoday+" AND \"month\"="+month()+" AND \"year\"="+year()+"");
//db.query("SELECT * FROM variable_data WHERE \"idname\"=\"Calle Regina\" AND \"day\"="+daytoday+" AND \"month\"="+month()+" AND \"year\"="+year()+"");
//db.query("SELECT * FROM variable_data WHERE \"stPrice\"="+input_price+" AND \"day\"="+daytoday+" AND \"month\"="+month()+" AND \"year\"="+year()+"");
while (db.next ()) {
String name=db.getString("idname");
int price=db.getInt("stPrice");
println(price);
println(name);
}
}
println(frameCount);
}

Replies(3)

PhiLho

Re: How to pass a String variable into sqlite query?

1 year ago

Strings in SQL statements are quoted with single quotes.
And you must not quote the column names.

27 MB fits in memory, it is probably much faster (and perhaps simpler) to load the data in classes in memory...

demiguel...

Re: Re: How to pass a String variable into sqlite query?

1 year ago

EDIT:

//////////////////////////////////////////////////////////////////////////////////////////////////

ahhh now i get it! i was trying WHERE idname=" '+street+' " and also WHERE idname=" \'+street+\' " .... was trying everyth except the right way :).. with the single quotes outside the double quotes:

db.query("SELECT * FROM variable_data WHERE idname = '"+street+"' AND day="+daytoday+" AND month="+month()+" AND year="+year()+"");

//////////////////////////////////////////////////////////////////////////////////////////////////

Thanks, i will put the database in memory from the setup().

But i cant make a single quoted string in processing... it gives an error:

String street='Calle Regina'; ==> processing.app.SketchException: Badly formed character constant

I´ve removed the quotes from the columns, now the query looks like this:

db.query("SELECT * FROM variable_data WHERE idname="+street+" AND day="+daytoday+" AND month="+month()+" AND year="+year()+"");

If i define street as String street="Calle Regina"; then i get this error ==> java.sql.SQLException: near "Regina": syntax error

But, if i define street as String street=str(345); then i dont get any error, though of course it doesnt find any match...

Is this normal? i´ve looked through java examples, they use stmt.setString(int, String) where stmt is an instance of PreparedStatement class. I could try that way but it would be more complicated.

PhiLho

Re: How to pass a String variable into sqlite query?

1 year ago

String street='Calle Regina';
You mix up Java syntax and SQL syntax. They are different worlds...

Try:
db.query("SELECT * FROM variable_data WHERE idname='"+street+"' AND day='"+daytoday+"' AND month='"+month()+"' AND year='"+year()+"'");
Leave the single quotes in the SQL world, ie. inside the string.
Note: I treated the date element as strings, but I don't know how they are defined in your table.

Top Reply