What am I doing wrong with ExecuteSQL

Classic Servoy
1

My method is very simple:

databaseManager.saveData()
var sql_query = 'UPDATE contacts SET contacts.partner_id_fk = ' + contacts_id + ' WHERE contacts.contacts_id = ' + partner_id_fk;
databaseManager.plugins.rawSQL.executeSQL(controller.getServerName(), sql_query, 1);

My error is:
The undefined value has no properties calling rawSQL.
Callstack:
contacts.set_foreign_keys_foreign_key
ConversionError: The undefined value has no properties calling rawSQL. (set_foreign_keys_foreign_key; line 8)
contacts.set_foreign_keys_foreign_key

I don’t know what I am doing wrong. I have a contacts_id and a partner_id_fk in all the test records that I am trying. I am calling this method ondatachange on the partner_id_fk field.

I’m using servoy 3.5.10 on an intel Mac with Sybase 10.

Thanks for any help

Bevil

2

Hi bevil,

I see 2 errors in your last line of code.

databaseManager.saveData()
var sql_query = 'UPDATE contacts SET contacts.partner_id_fk = ' + contacts_id + ' WHERE contacts.contacts_id = ' + partner_id_fk;
databaseManager.plugins.rawSQL.executeSQL(controller.getServerName(), sql_query, 1);

Should be :

databaseManager.saveData()
var sql_query = 'UPDATE contacts SET contacts.partner_id_fk = ' + contacts_id + ' WHERE contacts.contacts_id = ' + partner_id_fk;
plugins.rawSQL.executeSQL(controller.getServerName(), sql_query, [1]);

Spot the difference ;)

3

Hey Robert

Aha (duh)… I didn’t see that, dunno how a databaseManager snuck in there.

I couple of questions though. Why the around the 1? I’ve only ever used that for arguments (where I put ? in the query instead of values, my 1 is to return 1 value (which there should only be)). Perhaps I should have done it like this :?

plugins.rawSQL.executeSQL(controller.getServerName(), sql_query, null, 1);

In any case, now I no longer have the error, but it is not setting my partner_id_fk, i.e. nothing happens…

Still stuck.

Bevil

4

Just have a close look at the parameters of the method (either the tooltip or the sample code). This is what I see:

plugins.rawSQL.executeSQL( serverName, tableName, SQL, [arguments])

So you are missing tableName and your last parameter (1) is good for nothing if not disturbing. So in your case it should be

var sql_query = 'UPDATE contacts SET contacts.partner_id_fk = ' + contacts_id + ' WHERE contacts.contacts_id = ' + partner_id_fk;
databaseManager.plugins.rawSQL.executeSQL(controller.getServerName(), 'contacts', sql_query);
5

Additionally I’d recommend using prepared statements like in the documentation:

var country = ‘NL’
var done = plugins.rawSQL.executeSQL(“example_data”,“employees”,“update employees set country = ?”, [country])

In your example if any of the variables is user modifiable it is prone to sql injection. eg if a user could modify contacts_id he could put inside that field:

21 ; drop table contacts