Searching Multiple Columns.

Classic Servoy
1

HI,
I would like to search multiple columns in a table with one search string and return a dataset. Is that possible ?

2

Hi Phil,

Yep, it’s possible :)
You can do that with SQL and the databaseManagager object:

var sQuery	= "SELECT * FROM myTable WHERE myColumn1=? AND myColumn2=? AND myColumn3=?",
	 ds 		= databaseManagager("myServerName", sQuery, [1, 2, "three"], -1 );

if ( ds.getExceptionMsg() ) {

	// There was a database exception
	application.output("Error: " + ds.getExceptionMsg());

} else if ( ds.getMaxRowIndex() > 0 ) {

	// We have a dataset with data

}

You could also use OR instead of AND in your SQL or mix and match those.

Hope this helps

3

Thanks Robert,
I will give that a go, what if I would like to perform the same search across different tables, eg find a customer name in the quotes,orders and invoice tables ??

4

Hi Phil,

That depends on how you want to show the resultset.
You could fire 3 seperate queries and show the 3 different datasets or you fire 1 query that uses a UNION to fire all 3 queries and returns a single dataset.
You need to be more specific to give you a better answer on this.
But in any case I suggest you find a good SQL resource and read up on the SQL syntax.
A quick google search on ‘SQL manual’ gave me http://www.sql.org/ .Perhaps you can find even better ones.

Hope this helps.

5

OK, thanks Robert I will do more reading.