OPENQUERY / MS SQL Server Linked Server Issue

Classic Servoy
1

Hi,
I am trying to send data directly between my Servoy back end running on MS SQL to MySQL which is running my client’s web site.
I worked out how to do this by creating something called a ‘Linked Server’, which is using an ODBC connection to MySQL, and then a SQL statement called ‘OPENQUERY’, that uses the Linked Server.
I can do INSERTs without a problem, but I wanted to be able to do UPDATEs based on common keys.
I am trying to do this the correct way, using an arguments array etc., but it isn’t parsing correctly. I have done this directly in the back end tools, obviously using values directly, but wanted to get it working from Servoy.
Here is my code

// globals.ec_update_min_stock_flag(product_id , value);

var lv_serial_no  = arguments[0] ;
var lv_flag_value = arguments[1] ;

var args = new Array();
args[0] = lv_serial_no ;
args[1] = lv_flag_value ;

//"UPDATE OPENQUERY(ABRSMPUB, 'SELECT * FROM fm_production WHERE serial_no = \"' + lv_serial_no + '\"' ) SET minStockForWeb = \'" + lv_flag_value + "\' ;") ;
	
var done = plugins.rawSQL.executeSQL("abrsm",
						"products",
						"UPDATE OPENQUERY(ABRSMPUB, 'SELECT * FROM fm_production WHERE serial_no = \'\'' + lv_serial_no + '\'\'' ) SET minStockForWeb = \'" + lv_flag_value + "\' ;") ;

if (!done)
{
	var msg = plugins.rawSQL.getException().getMessage();
	plugins.dialogs.showErrorDialog('Error',  'SQL exception: ' + msg , 'Ok' ) ;
}

I think my string formation isn’t quite correct, as the UPDATE line should be passed as

UPDATE OPENQUERY(ABRSMPUB, 'SELECT * FROM fm_production WHERE serial_no = ''1422''' ) SET minStockForWeb = 'Y' ;

with there being 2 single quotes/apostrophes before the 1422 and 3 after it, and just one before and after the Y

when I send it as above, I get the first error attached and if I add another \' after ```
lv_serial_no +


This code was just to do a test for a single record, but I intend to do it for a foundset of records.

Any help would be greatly appreciated <img src="{SMILIES_PATH}/icon_biggrin.gif" alt=":D" title="Very Happy" /> 

Thanks,

Rafi

[Screen shot 2009-12-15 at 23.44.05.pdf|attachment](upload://2PfpF0ZCmdz4oOjNs6WDRxePAxB.pdf) (10.7 KB)


[Screen shot 2009-12-15 at 23.43.16.pdf|attachment](upload://bcDdQLoM9C2ktz6UBF3fBVJqkqG.pdf) (12.4 KB)
2

You have 3 and 4 ’ chars instead of 2 and 3 in there. Try …'' + lv_serial_no + '''…
You can also use ’ directly instead of ' because your outer-most quotes are ". But it should work both ways.

3

In fact, I think what you really wanted there is ''" + lv_serial_no + "'''. That would be double quotes before and after + lv_serial_no +:

"UPDATE OPENQUERY(ABRSMPUB, 'SELECT * FROM fm_production WHERE serial_no = \'\'" + lv_serial_no + "\'\'\' ) SET minStockForWeb = \'" + lv_flag_value + "\' ;"

or

"UPDATE OPENQUERY(ABRSMPUB, 'SELECT * FROM fm_production WHERE serial_no = ''" + lv_serial_no + "''' ) SET minStockForWeb = \'" + lv_flag_value + "\' ;"
4

Thanks Andrei.

In the end, I managed to get it working with the ‘?’ arg passing using

var args = new Array();
args[0] = lv_serial_no ;
args[1] = lv_flag_value ;

var done = plugins.rawSQL.executeSQL("abrsm",
									"products",
									"UPDATE OPENQUERY(ABRSMPUB, 'SELECT * FROM abrsmPubTest.fm_production WHERE serial_no = ''?''' ) SET minStockForWeb = '?' ;") ;

I was getting some other errors due to some back-end NULL settings!
I’m working on the rest of my code now :D
Thanks
Rafi

5

Rafi, your are directly connected to the mySQL database right?
why do that via odbc & linked tables? why not use jdbc directly to mysql?

should be much easier…

just a thought.

6

Hi Harjo,

Harjo:
Rafi, your are directly connected to the mySQL database right?
why do that via odbc & linked tables? why not use jdbc directly to mysql?
should be much easier…
just a thought.

sorry for not replying sooner…
Unfortunately I am not able to connect directly to the MySQL database as it requires an SSL connection for which I have been provided the Certificates & Keys (which I can configure the MySQL ODBC driver with…).
If you know of a way I can get Servoy to work with MySQL using SSL certificates etc., I would be very interested to know :)

I also think it is probably quicker to get the data to transfer directly via SQL statements through the back end.

Thanks.

7

rafig:
If you know of a way I can get Servoy to work with MySQL using SSL certificates etc., I would be very interested to know :)

First, try and see if you can bypass certificate validation with the following JDBC connection URL:

jdbc:mysql://127.0.0.1:3306/<databasename>?verifyServerCertificate=false&useSSL=true&requireSSL=true

If that doesn’t work, setup the certificates on the Servoy Server machine:

  1. Import the certificates into a java truststore and keystore as also described in the MySQL Reference Manual, 21.4.4.5. Connecting Securely Using SSL.

  2. Add the following lines to the servoy_server.sh (or servoy.properties):

-Djavax.net.ssl.keyStore=<keystore-path>/<keystore-name> 
-Djavax.net.ssl.keyStorePassword=<keystore-password> 
-Djavax.net.ssl.trustStore=<truststore-path>/<truststore-name> 
-Djavax.net.ssl.trustStorePassword=<truststore-password>
  1. Add the SSL parameters to the JDBC connection URL:
jdbc:mysql://127.0.0.1:3306/<databasename>?verifyServerCertificate=true&useSSL=true&requireSSL=true
  1. Server assumptions: this assumes mysqld is setup to support OpenSSL with the following four lines added to /etc/my.cnf:
ssl 
ssl-ca=<certs-dir>/ca-cert.pem 
ssl-cert=<certs-dir>/server-cert.pem 
ssl-key=<certs-dir>/server-key.pem
8

Hi David,
wow! thanks for all that stuff on MySQL with SSL!
I might give that a go when I am at client next week.
In the mean time, I think I am just managing to get it working without Servoy talking to it directly…

Thanks though.

Rafi