pass sql statement in JDBC Probe

[efe1]

Iam trying to trigger   data source via JDBC probe   using :

var j = new JDBCProbe("MID_B1");

j.setDataSource(datasource_sys_id);

j.create()

In that data source I have selected specific SQL statement ,and using sql query not all rows from table ,so I have join of 3 tables ..

Now after running the above script Iam getting an error in ecc_queue

"<?xml version="1.0" encoding="UTF-8"?><parameters><parameter name="work"><insert table="undefined"/></parameter></parameters>"

Can anyone please tell me how to correct this.or how to pass the sql query into

var j = new JDBCProbe('MID_B1');

j.setDriver('com.mysql.jdbc.Driver');

j.setConnectionString(cnstr);

j.setTable("incident");

j.setFunction("select");

j.setWhereClause("priority='1'");

j.create();

rather than :

j.setTable("incident");

j.setFunction("select");

j.setWhereClause("priority='1'");

I want to pass sql statement directly .

Thanks

[vab_13]

Yes you can.

Parameter: "query"

"Optional. Type of query. Possible choices are "All Rows from Table" or "Specific SQL". If "Specific SQL", the sql_statement will be required to specify the SQL statement."

Parameter: "sql_statement"

"Optional. Use a specific SQL query. The presence of this element executes a direct query specified in the value attribute."

yourObject.addParameter("query", "Specific SQL");   
yourObject.addParameter("sql_statement", YoursqlQuery);   

Reference:

https://docs.servicenow.com/bundle/jakarta-servicenow-platform/page/integrate/inbound-other-web-serv...

[Mike225]

So I used this command and JDBC driver would return the error:

 "error="net.snowflake.client.jdbc.SnowflakeSQLException: SQL compilation error:&#10;syntax error line 1 at position 23 unexpected ')'.&#10;syntax error line 1 at position 25 unexpected 'VALUES'"

I noticed the script include was still passing the parameter "work" to the ECC queue entry causing the invalid SQL statement error.

To fix this I updated the JDBCProbe script include on line 30 from:

	addParameter : function(name, value) {

		var el = this.payloadDoc.createElement("parameter");

to

	addParameter : function(name, value) {
		if (name == 'query' && value == 'Specific SQL') //OOB Code Bug Fix
			this.functionName = "";

		var el = this.payloadDoc.createElement("parameter");

This stops the script include from passing the work parameter, and gets the custom SQL statement to execute with out an error.

Here is the example code to execute the JDBC query incase some one needs it

var j = new JDBCProbe('MIDServer1');
j.setDriver('net.snowflake.client.jdbc.SnowflakeDriver');
j.setDriverJar('E:\\snowflake-jdbc.jar');
j.setConnectionString('jdbc:snowflake://someserver.snowflakecomputing.com/?user=USERNAME&password=PASSWORD');
j.addParameter('CLIENT_SESSION_KEEP_ALIVE', 'true');
j.addParameter('query', 'Specific SQL');
j.addParameter('sql_statement', 'SHOW Roles');
j.create();

Hopefully this will help anyone having the same issue as me.

I attached the XML for the updated JDBCProbe script include to fix the issue.

[Rachana Baranwa]

Hi Mike,

Were you able to fetch data from SnowFlake using this script ? We have a requirement to fetch data from Snowflake to Servicenow but Mid Server is not responding to the data source

[Mike225]

Hi Rachana, I am pulling live data from snowflake with this code, modified where needed. Do you have your output and input logs from your ECC queue for the request, it would help me trouble shoot it.