I have a function with a SQL guery, but I realy don’t know how to set the function variables correct into the SQL statement string .
The simple statement is working well in the Sybase InteractiveSQL, but not in my function like this:
var v_company = company;
var v_str = street;
var v_zip = zip;
if(v_company && v_str && v_zip){
var vSQLquery = "SELECT * FROM t_customer WHERE company = '+v_company+' AND street = '+v_str+' AND zip = '+v_zip'";
var vDataSet = databaseManager.getDataSetByQuery("customer_db", vSQLquery, null, 5);
var vResult = vDataSet.getMaxRowIndex();
}
In the function I always get “vResult == 0” but there are definitely 3 identical records in the db. In InteractiveSQL I get three rows and I think something is wrong in my function. Can you help me please?
We always use it like (from my head , not tested) :
var query = 'select kol1,kol4 from table where kol1 = ? and kol2 = ?'
var args = new Array();
args[0] = _var1;
args[1] = _var2
var dataset = databaseManager.getDataSetByQuery('server_name', query, args, -1);
The the first ? wil be replaced by args[0], the second by args[1] , …
Like Hans already said you could/should be using a prepared statement (i.e. using arguments).
But lets take a look at your code.
var vSQLquery = "SELECT * FROM t_customer WHERE company = '+v_company+' AND street = '+v_str+' AND zip = '+v_zip'";
Here you set vSQLquery to hold the following string:
SELECT * FROM t_customer WHERE company = '+v_company+' AND street = '+v_str+' AND zip = '+v_zip'
This is literally what you are sending to the backend database.
You see what’s wrong with it ?
The variablenames, instead of their values are in the query. You forgot to put them outside the double quotes of the main SQL string.
So your code should have been the following:
var vSQLquery = "SELECT * FROM t_customer WHERE company = '"+v_company+"' AND street = '"+v_str+"' AND zip = '"+v_zip +"'";
But like said before, use a prepared statement like so:
var v_company = company;
var v_str = street;
var v_zip = zip;
if(v_company && v_str && v_zip){
var vSQLquery = "SELECT * FROM t_customer WHERE company = ? AND street = ? AND zip = ?";
var vDataSet = databaseManager.getDataSetByQuery("customer_db", vSQLquery, [v_company, v_str, v_zip], 5);
var vResult = vDataSet.getMaxRowIndex();
}
As you can see now you also don’t have to deal with single quotes. The prepared statement takes care of that for you.
thank you also for your great explanation! It’s nice to see that I was not on a completely wrong way… but I agree with you and take the solution from Hans.
Hi all,
I’m having problems with a sql query too.
I would like to use the LIKE condition in an sql query.
I’ve tried using LIKE ’ % ? % ’ . But it launches an exception.
Indice de parametro fuera de rango
That means Parameter index out of range
I’ve tried using the same variable with other conditions but keeping the same structure of the query and it works perfectly.
Therefore I’m guessing the problem is in the way I’ve been using LIKE.
Does anyone know how it should be used??
I’m going to reuse this post as it was very helpful when I had an sql statement problem.
Now I have a very similar one.
I’m trying to use the BETWEEN sentence.
I’ve tried with ‘between (? and ?)’, obviously this is wrong or else I wouldn’t be asking you guys.
Should I try ‘between (?)’ ???
And in this case what format should have the argument/s??
The rest of the sentence is not important as it is very normal.