SQL queries in Servoy

Classic Servoy
1

Hi,

Is it possible to use SQL queries in Servoy such as:

//----
IF EXISTS (SELECT pk_id, fk_ctr FROM chargeback_status WHERE fk_ctr = ‘KR8GH5F4’)
BEGIN
UPDATE chargeback_status SET checking_status = ‘0’ WHERE fk_ctr = ‘KR8GH5F4’
END
ELSE
BEGIN
SET IDENTITY_INSERT chargeback_status OFF
INSERT INTO chargeback_status (“fk_ctr”, “checking_status”) VALUES(‘KR8GH5F4’, ‘0’)
END
//----

For example, I’m trying to use the above query in:

var dataset = databaseManager.getDataSetByQuery(‘railoffers’, query, null, 1)

Without any luck. What are the constraints where Servoy and SQL server are concerned? Is there a set of SQL command Servoy will only accept?

Thanks

2

Pat,

databaseManager.getDataSetByQuery() is for running select queries, not updates.
You can use the rawsql plugin, but note that performing updates directly in the database will not be seen by Servoy clients, you will need to fire notifications.

Rob

3

Hi,

Why not use stored procedure/function here ?

Regards,

Hans

4

Thanks guys, I think I’ve cracked it ;o)

5

pat:
Thanks guys, I think I’ve cracked it ;o)

For the benefit of others Pat (me in particular :lol: ) how did you make your SQL work?

6

Why not using Servoy Javascript for this situation?

Retrieve foundset
if getSize() == 0
NewRecord()
else
foundset.status = 0

saveData()

I don’t see why you should do this with SQL script