Prepared Statement

Classic Servoy
1

I’m looking over some queries i have written and updating them using prepared statements. I have come across a few scenarios I’m having trouble getting to work. For instance, I have a form variable, search, that users can enter search criteria. Lets say the user inputs the letter “t”. Here is my query:

var found = "'" + search + "%'";
query = "Select medication_sample_id from medication_samples where medication_name like ? order by medication_name";
dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[found],-1);

My dataset is empty. But if I do it this way:

var found = "'" + search + "%'";
query = "Select medication_sample_id from medication_samples where medication_name like " + found + " order by medication_name";
dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,null,-1);

I get the desired results. Can someone show me how to properly structure this prepared statement using LIKE and the % wildcard?

Thanks!
Nicholas Dunn
E-Automation Systems

2

Hi Nicholas,

When you pass a parameter to a prepared statement you don’t pass any quoted strings because the prepared statement will take care of this for you.
So the following code would do fine:

var found = search + "%"; // no extra quotes
query = "Select medication_sample_id from medication_samples where medication_name like ? order by medication_name";
dataset = databaseManager.getDataSetByQuery(controller.getServerName(),query,[found],-1);

Hope this helps.

3

Awesome Robert! Thanks for the help!