# need help by SQL query

**URL:** <https://forum.servoy.com/t/need-help-by-sql-query/11727>\
**Category:** Classic Servoy\
**Created:** [February 6, 2010, 8:58am UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727 "2010-02-06T08:58:32Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![tgs](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tgs](https://forum.servoy.com/u/tgs)\
**Post date:** [February 6, 2010, 8:58am UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/1 "2010-02-06T08:58:32Z")

</div>

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 ![:cry:]( "Crying or Very sad") .  
The simple statement is working well in the Sybase InteractiveSQL, but not in my function like this:

```auto
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?

Servoy 5.0.1  
Sybase 11

---

<div class="post-metadata">

**Author:** ![Hans\_Nieuwenhuis](https://avatars.discourse-cdn.com/v4/letter/h/87869e/32.png) [@Hans\_Nieuwenhuis](https://forum.servoy.com/u/Hans_Nieuwenhuis)\
**Post date:** [February 6, 2010, 9:42am UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/2 "2010-02-06T09:42:39Z")

</div>

Hi Thomas,

We always use it like (from my head , not tested) :

```auto
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] , …

regards,

---

<div class="post-metadata">

**Author:** ![tgs](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tgs](https://forum.servoy.com/u/tgs)\
**Post date:** [February 6, 2010, 9:59am UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/3 "2010-02-06T09:59:06Z")

</div>

Thank you Hans!

You could help me. My function is now working as it should be.

Nice weekend

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [February 6, 2010, 10:01am UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/4 "2010-02-06T10:01:02Z")

</div>

Hi Thomas,

Like Hans already said you could/should be using a prepared statement (i.e. using arguments).  
But lets take a look at your code.

```auto
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:

```auto
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:

```auto
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:

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

Hope this helps.

---

<div class="post-metadata">

**Author:** ![Hans\_Nieuwenhuis](https://avatars.discourse-cdn.com/v4/letter/h/87869e/32.png) [@Hans\_Nieuwenhuis](https://forum.servoy.com/u/Hans_Nieuwenhuis)\
**Post date:** [February 6, 2010, 10:05am UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/5 "2010-02-06T10:05:22Z")

</div>

gut, dass ich helfen konnte.  
Auch ein schönes Wochenende.

---

<div class="post-metadata">

**Author:** ![tgs](https://avatars.discourse-cdn.com/v4/letter/t/f19dbf/32.png) [@tgs](https://forum.servoy.com/u/tgs)\
**Post date:** [February 6, 2010, 10:14am UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/6 "2010-02-06T10:14:11Z")

</div>

Hi Robert,

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.

For you too a nice weekend

---

<div class="post-metadata">

**Author:** ![nromeou](https://avatars.discourse-cdn.com/v4/letter/n/779978/32.png) [@nromeou](https://forum.servoy.com/u/nromeou)\
**Post date:** [March 3, 2010, 1:00pm UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/7 "2010-03-03T13:00:43Z")

</div>

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. ![:?]( "Confused")

Indice de parametro fuera de rango

That means Parameter index out of range ![:wink:]( "Wink")

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

Thanks for the help ![:D]( "Very Happy")

PD: I’m using Servoy 5.1

---

<div class="post-metadata">

**Author:** ![Hans\_Nieuwenhuis](https://avatars.discourse-cdn.com/v4/letter/h/87869e/32.png) [@Hans\_Nieuwenhuis](https://forum.servoy.com/u/Hans_Nieuwenhuis)\
**Post date:** [March 3, 2010, 1:11pm UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/8 "2010-03-03T13:11:59Z")

</div>

Hi,

What database and version are You using ?

Can You show the complete sql statement ?

Regards,

---

<div class="post-metadata">

**Author:** ![Joas](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/joas/32/5095_2.png) [@Joas](https://forum.servoy.com/u/Joas)\
**Post date:** [March 3, 2010, 1:44pm UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/9 "2010-03-03T13:44:04Z")

</div>

> nromeou:  
> I’ve tried using LIKE ’ % ? % ’ . But it launches an exception. ![:?]( "Confused")

The %'s are a part of the parameter, so you should use it like this:

```auto
var _query = "SELECT pk FROM table_x WHERE column_y LIKE ?";
var _args = ["%value%"];

foundset.loadRecords(_query, _args);

```

---

<div class="post-metadata">

**Author:** ![gldni](https://avatars.discourse-cdn.com/v4/letter/g/7ea924/32.png) [@gldni](https://forum.servoy.com/u/gldni)\
**Post date:** [March 3, 2010, 4:51pm UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/10 "2010-03-03T16:51:00Z")

</div>

I was having a similar problem. Here is a link to the topic: [http://www.servoy.com/forum/viewtopic.php?f=22&t=13180](http://www.servoy.com/forum/viewtopic.php?f=22&t=13180)

---

<div class="post-metadata">

**Author:** ![nromeou](https://avatars.discourse-cdn.com/v4/letter/n/779978/32.png) [@nromeou](https://forum.servoy.com/u/nromeou)\
**Post date:** [March 3, 2010, 5:46pm UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/11 "2010-03-03T17:46:06Z")

</div>

Thanks a lot Joas, it worked perfectly!

---

<div class="post-metadata">

**Author:** ![nromeou](https://avatars.discourse-cdn.com/v4/letter/n/779978/32.png) [@nromeou](https://forum.servoy.com/u/nromeou)\
**Post date:** [March 16, 2010, 1:50pm UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/12 "2010-03-16T13:50:00Z")

</div>

Hi again,

I’m going to reuse this post as it was very helpful when I had an sql statement problem. ![:wink:]( "Wink")

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. ![:lol:]( "Laughing")

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.

Does anyone now how should the syntax be??

Thanks ![:D]( "Very Happy")

---

<div class="post-metadata">

**Author:** ![mboegem](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/mboegem/32/4399_2.png) [@mboegem](https://forum.servoy.com/u/mboegem)\
**Post date:** [March 16, 2010, 2:20pm UTC](https://forum.servoy.com/t/need-help-by-sql-query/11727/13 "2010-03-16T14:20:07Z")

</div>

did you try:

```auto
select * from TABLE where COL between ? and ?

```

pass in the arguments for the variables and it should work…

Hope this helps!

[EDIT] in this post on a webinar about SQL, you might find some interesting PDF to read as well:  
[http://forum.servoy.com/viewtopic.php?f=8&p=60294&start=0&st=0&sk=t&sd=a](http://forum.servoy.com/viewtopic.php?f=8&p=60294&start=0&st=0&sk=t&sd=a)

pdf: [http://forum.servoy.com/download/file.php?id=1769](http://forum.servoy.com/download/file.php?id=1769)