There is a lesser known JDBC integration interface to ServiceNow than is generally published to the outside world, but it can make JDBC integrations go much smoother that traditionally supported in the ServiceNow product.
A typical JDBC integration in ServiceNow is usually a data pull from a third party into ServiceNow through a one-time or scheduled JDBC pull using a Data Source record. This works great for bringing bulk data from an external source into ServiceNow.
What if, however, you want a more transactional data pull…or even to set data in the external database using JDBC? There is a way to do this by creating XML payloads on the ECC queue. There is some pretty good ECC Queue – JDBC Probe documentation on the wiki. This can be cumbersome, however to build ECC Queue records with XML documents for the queries you wish to make. There is a much easier way!
Unfortunately, what I am about to demonstrate does not come with a vanilla instance, but requires the “Integration – JDBC” plugin. Just request the plugin for your instance via HI, or through your account representative. It is a small plugin that can be enabled in a matter of seconds by ServiceNow.
The plugin will create, among other things, a Script Include called “JDBCProbe”. This script include creates an API for generating the XML payloads on the ECC Queue so that you don’t have to worry about how to represent SQL statements in XML.
JDBCProbe API Basics
Every JDBCProbe API call will require some initial parameters and values.
MID Server: The JDBCProbe requires a MID Server for execution. Specify the name of the MID Server as it it shown in the MID Server list.
Here is what my MID Server list looks like:
I am going to use my MID Server, “DEB1” for these examples.
Driver: You need to specific the JDBC Driver that will be used to make the JDBC call. This is specific to the type of Database you are calling into. For this example, I am using a MySQL database. Thus, the driver string is: “com.mysql.jdbc.Driver”. Other valid OOB driver classes are:
Oracle: oracle.jdbc.OracleDriver
Microsoft SQL Server: com.microsoft.sqlserver.jdbc.SQLServerDriver
MySQL: com.mysql.jdbc.Driver
Sybase: com.sybase.jdbc3.jdbc.SybDriver
IBM DB2 Universal: com.ibm.db2.jcc.DB2Driver
Connection String: The JDBC protocol uses a connection string to establish the authentication and other parameters to establish a connection between the client and the Database. Each Database has its own connection string format. Here is a list taken from the ServiceNow JDBCProbe wiki for setting up your connection string:
Oracle: [jdbc:oracle:thin:
@ ]
Microsoft SQL Server: [jdbc:sqlserver://localhost;user=MyUserName;password=*****;]
MySQL: [jdbc:mysql://localhost/database?user=username%26password=passwd]
Sybase: [jdbc:sybase:Tds:10.10.11.162:4100/NCOMS?user=yyy%26password=xxx]
DB2 Universal: [jdbc:db2://sysmvs1.stl.ibm.com:5021/san_jose:user=db2adm;password=db2adm]
Function: This tells the JDBCProbe what type of query you intend to do with the database. Supported functions are: “insert”, “select”, “update”, “delete”.
JDBCProbe API Functions
The following is a list of the functions available by the JDBCProbe Script Include:
JDBCProbe( string MIDServer ) //Constructor
Constructor for the JDBCProbe class. The MIDServer parameter is the name of the MID Server which will be issuing the database commands.
1 | var j = new JDBCProbe("DEB1"); |
setDriver( string Driver )
Sets the driver class corresponding to the brand of database with which you are connecting.
1 2 | var j = new JDBCProbe("DEB1"); j.setDriver("com.mysql.jdbc.Driver"); |
setConnectionString( string connectionString )
Sets the connection string appropriate for the brand of database with which you are connecting.
1 2 | var j = new JDBCProbe("DEB1"); j.setConnectionString("jdbc:mysql://mysqlserver.org/snctestdb?user=MyUsername&password=MyPassword"); |
setTable( string tablename )
Indicates which table we will query against.
1 2 | var j = new JDBCProbe("DEB1"); j.setTable("incident"); |
setFunction( string function )
Sets the query function being used. Supported functions are “select”, “insert”, “update”, “delete”.
1 2 | var j = new JDBCProbe("DEB1"); j.setFunction("insert"); |
addField( string fieldName, string fieldValue )
Adds a field on the insert query. You should provide both the fieldName as well as the value.
1 2 3 4 5 6 7 | var j = new JDBCProbe("DEB1"); j.setDriver("com.mysql.jdbc.Driver"); j.setConnectionString("jdbc:mysql://mysqlserver.org/snctestdb?user=MyUsername&password=MyPassword"); j.setTable("incident"); j.setFunction("insert"); j.addField("priority", "1"); j.create(); |
addNumberField( string fieldName, string fieldValue )
Adds a field formatted for numbers on the insert query. You should provide both the fieldName as well as the value.
1 2 3 4 5 6 7 | var j = new JDBCProbe("DEB1"); j.setDriver("com.mysql.jdbc.Driver"); j.setConnectionString("jdbc:mysql://mysqlserver.org/snctestdb?user=MyUsername&password=MyPassword"); j.setTable("incident"); j.setFunction("insert"); j.addNumberField("priority", 1); j.create(); |
create( )
Creates the JDBCProbe entry on the ECC Queue table for processing.
1 2 3 4 5 6 7 | var j = new JDBCProbe("DEB1"); j.setDriver("com.mysql.jdbc.Driver"); j.setConnectionString("jdbc:mysql://mysqlserver.org/snctestdb?user=MyUsername&password=MyPassword"); j.setTable("incident"); j.setFunction("insert"); j.addField("priority", "1"); j.create(); |
setWhereClause( string whereClause)
Allows you to set a string representing the “Where” clause for a select, update, or delete query.
1 2 3 4 5 6 7 | var j = new JDBCProbe("DEB1"); j.setDriver("com.mysql.jdbc.Driver"); j.setConnectionString("jdbc:mysql://mysqlserver.org/snctestdb?user=MyUsername&password=MyPassword"); j.setTable("incident"); j.setFunction("select"); j.setWhereClause("priority='1'"); j.create(); |
setDataSource( string dataSourceSysId )
This allows you to have all JDBC settings and query information come from a datasource rather than your script. You will need to pass in the sys_id to the data source record containing this information.
1 2 3 | var j = new JDBCProbe("DEB1"); j.setDataSource("11e091bf0a258102005ba7a71b145a8a"); j.create(); |
ECC Queue Records
Once you execute your script and end it with the create() function, an “output” record will be placed on the ECC Queue. This output record will contain all of the data set through the script.
Once the MID Server grabs the output record, it will execute the query and then return the JDBC response message back via an “input” record on the ECC Queue corresponding to the original ECC Queue entry.
INSERT example
The following code will insert a row in a table called “incident” on a MySQL server that I have:
1 2 3 4 5 6 7 8 9 10 | var j = new JDBCProbe('DEB1'); j.setDriver('com.mysql.jdbc.Driver'); j.setConnectionString('jdbc:mysql://mySqlServer.org/snctest?user=MyUser&password=MyPassword'); j.setTable("incident"); j.setFunction("insert"); j.addField("summary", "My chair will not roll smoothly"); j.addNumberField("priority", 1); j.addField("urgency", "2"); j.addField("impact", "1"); j.create(); |
This code creates an output record on the ECC Queue that has the following Payload:
1 2 3 4 5 6 7 8 9 10 11 12 | <parameters> <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/> <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&password=sncrocks"/> <parameter name="work"> <insert table="incident"> <summary>My chair will not roll smoothly</summary> <impact>1</impact> <urgency>2</urgency> <priority quoted="false">1</priority> </insert> </parameter> </parameters> |
The result is created by the MID Server on the ECC Queue. Here is the Payload of the result:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | <?xml version="1.0" encoding="UTF-8"?> <results probe_time="6449"> <result> <insertResult>1 rows inserted</insertResult> </result> <result/> <parameters> <parameter name="topic" value="JDBCProbe"/> <parameter name="work" value="<?xml version="1.0" encoding="UTF-8"?><work><insert table="incident"><summary>My chair will not roll smoothly</summary><impact>1</impact><urgency>2</urgency><priority quoted="false">1</priority></insert></work>"/> <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&password=sncrocks"/> <parameter name="queue" value="output"/> <parameter name="error" value=""/> <parameter name="from_sys_id" value=""/> <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/> <parameter name="sys_id" value="80d4367b507da0006d49b2e21aafd57a"/> <parameter name="state" value="ready"/> <parameter name="from_host" value=""/> <parameter name="agent" value="mid.server.DEB1"/> <parameter name="processed" value=""/> <parameter name="ecc_queue" value="80d4367b507da0006d49b2e21aafd57a"/> <parameter name="response_to" value=""/> <parameter name="source" value=""/> <parameter name="sequence" value="1373db366fe0000001"/> <parameter name="name" value="JDBCProbe"/> <parameter name="table_name" value="ecc_queue"/> <parameter name="agent_correlator" value=""/> </parameters> </results> |
UPDATE example
The following code will update all incidents in my database that have a priority of 1. It will set the urgency field to 4.
1 2 3 4 5 6 7 8 | var j = new JDBCProbe('DEB1'); j.setDriver('com.mysql.jdbc.Driver'); j.setConnectionString('jdbc:mysql://mySqlServer.org/snctest?user=MyUser&password=MyPassword'); j.setTable("incident"); j.setFunction("update"); j.addField("urgency", "4"); j.setWhereClause("priority='1'"); j.create(); |
It creates an output record in the ECC queue with the following Payload:
1 2 3 4 5 6 7 8 9 10 | <?xml version="1.0" encoding="UTF-8"?> <parameters> <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/> <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&password=sncrocks"/> <parameter name="work"> <update table="incident" where="priority='1'"> <urgency>4</urgency> </update> </parameter> </parameters> |
The result is created by the MID Server on the ECC Queue. Here is the Payload of the result:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | <?xml version="1.0" encoding="UTF-8"?> <?xml version="1.0" encoding="UTF-8"?> <results probe_time="102"> <result> <updateResult>4 rows updated</updateResult> </result> <result/> <parameters> <parameter name="topic" value="JDBCProbe"/> <parameter name="work" value="<?xml version="1.0" encoding="UTF-8"?><work><update table="incident" where="priority='1'"><urgency>4</urgency></update></work>"/> <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&password=sncrocks"/> <parameter name="queue" value="output"/> <parameter name="error" value=""/> <parameter name="from_sys_id" value=""/> <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/> <parameter name="sys_id" value="d2d6367b507da0006d49b2e21aafd5ad"/> <parameter name="state" value="ready"/> <parameter name="from_host" value=""/> <parameter name="agent" value="mid.server.DEB1"/> <parameter name="processed" value=""/> <parameter name="ecc_queue" value="d2d6367b507da0006d49b2e21aafd5ad"/> <parameter name="response_to" value=""/> <parameter name="source" value=""/> <parameter name="sequence" value="1373db889dd0000001"/> <parameter name="name" value="JDBCProbe"/> <parameter name="table_name" value="ecc_queue"/> <parameter name="agent_correlator" value=""/> </parameters> </results> |
SELECT example
The following code will select all incidents in my database that have a priority of 1.
1 2 3 4 5 6 7 | var j = new JDBCProbe('DEB1'); j.setDriver('com.mysql.jdbc.Driver'); j.setConnectionString('jdbc:mysql://mySqlServer.org/snctest?user=MyUser&password=MyPassword'); j.setTable("incident"); j.setFunction("select"); j.setWhereClause("priority='1'"); j.create(); |
It creates an output record in the ECC queue with the following Payload:
1 2 3 4 5 6 7 8 | <?xml version="1.0" encoding="UTF-8"?> <parameters> <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/> <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&password=sncrocks"/> <parameter name="work"> <select table="incident" where="priority='1'"/> </parameter> </parameters> |
The result is created by the MID Server on the ECC Queue. The “Name” field contains the number of rows returned. Here is the Payload of the result:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | <?xml version="1.0" encoding="UTF-8"?> <results probe_time="236"> <result query="SELECT * FROM incident WHERE priority='1'"> <row id="1"> <summary class="java.lang.String" length="255" type="12">My chair will not roll smoothly</summary> <id class="java.lang.Long" length="20" type="-5">1</id> <external_id class="java.lang.String" length="255" type="12"/> <priority class="java.lang.Integer" length="11" type="4">1</priority> <external_system class="java.lang.String" length="255" type="12"/> <impact class="java.lang.Integer" length="11" type="4">1</impact> <urgency class="java.lang.Integer" length="11" type="4">4</urgency> </row> <row id="2"> <summary class="java.lang.String" length="255" type="12">My chair will not roll smoothly</summary> <id class="java.lang.Long" length="20" type="-5">2</id> <external_id class="java.lang.String" length="255" type="12"/> <priority class="java.lang.Integer" length="11" type="4">1</priority> <external_system class="java.lang.String" length="255" type="12"/> <impact class="java.lang.Integer" length="11" type="4">1</impact> <urgency class="java.lang.Integer" length="11" type="4">4</urgency> </row> <row id="3"> <summary class="java.lang.String" length="255" type="12">My chair will not roll smoothly</summary> <id class="java.lang.Long" length="20" type="-5">3</id> <external_id class="java.lang.String" length="255" type="12"/> <priority class="java.lang.Integer" length="11" type="4">1</priority> <external_system class="java.lang.String" length="255" type="12"/> <impact class="java.lang.Integer" length="11" type="4">1</impact> <urgency class="java.lang.Integer" length="11" type="4">4</urgency> </row> <row id="4"> <summary class="java.lang.String" length="255" type="12">My chair will not roll smoothly</summary> <id class="java.lang.Long" length="20" type="-5">4</id> <external_id class="java.lang.String" length="255" type="12"/> <priority class="java.lang.Integer" length="11" type="4">1</priority> <external_system class="java.lang.String" length="255" type="12"/> <impact class="java.lang.Integer" length="11" type="4">1</impact> <urgency class="java.lang.Integer" length="11" type="4">4</urgency> </row> </result> <parameters> <parameter name="topic" value="JDBCProbe"/> <parameter name="work" value="<?xml version="1.0" encoding="UTF-8"?><work><select table="incident" where="priority='1'"/></work>"/> <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&password=sncrocks"/> <parameter name="queue" value="output"/> <parameter name="error" value=""/> <parameter name="from_sys_id" value=""/> <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/> <parameter name="sys_id" value="23d7367b507da0006d49b2e21aafd5ae"/> <parameter name="state" value="ready"/> <parameter name="from_host" value=""/> <parameter name="agent" value="mid.server.DEB1"/> <parameter name="processed" value=""/> <parameter name="ecc_queue" value="23d7367b507da0006d49b2e21aafd5ae"/> <parameter name="response_to" value=""/> <parameter name="source" value=""/> <parameter name="sequence" value="1373dbca0140000001"/> <parameter name="name" value="JDBCProbe"/> <parameter name="table_name" value="ecc_queue"/> <parameter name="agent_correlator" value=""/> </parameters> </results> |
DELETE example
The following code will delete all incidents in my database that have a priority of 1.
1 2 3 4 5 6 7 | var j = new JDBCProbe('DEB1'); j.setDriver('com.mysql.jdbc.Driver'); j.setConnectionString('jdbc:mysql://mySqlServer.org/snctest?user=MyUser&password=MyPassword'); j.setTable("incident"); j.setFunction("delete"); j.setWhereClause("priority='1'"); j.create(); |
It creates an output record in the ECC queue with the following Payload:
1 2 3 4 5 6 7 8 | <?xml version="1.0" encoding="UTF-8"?> <parameters> <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/> <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&password=sncrocks"/> <parameter name="work"> <delete table="incident" where="priority='1'"/> </parameter> </parameters> |
The result is created by the MID Server on the ECC Queue. The “Name” field contains the number of rows returned. Here is the Payload of the result:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | <?xml version="1.0" encoding="UTF-8"?> <results probe_time="67"> <result> <deleteResult>4 rows deleted</deleteResult> </result> <result/> <parameters> <parameter name="topic" value="JDBCProbe"/> <parameter name="work" value="<?xml version="1.0" encoding="UTF-8"?><work><delete table="incident" where="priority='1'"/></work>"/> <parameter name="connection_string" value="jdbc:mysql://mysql.ht4.org/snctest?user=snctest&password=sncrocks"/> <parameter name="queue" value="output"/> <parameter name="error" value=""/> <parameter name="from_sys_id" value=""/> <parameter name="jdbc_driver" value="com.mysql.jdbc.Driver"/> <parameter name="sys_id" value="31e8b6bb507da0006d49b2e21aafd57f"/> <parameter name="state" value="ready"/> <parameter name="from_host" value=""/> <parameter name="agent" value="mid.server.DEB1"/> <parameter name="processed" value=""/> <parameter name="ecc_queue" value="31e8b6bb507da0006d49b2e21aafd57f"/> <parameter name="response_to" value=""/> <parameter name="source" value=""/> <parameter name="sequence" value="1373dc001800000001"/> <parameter name="name" value="JDBCProbe"/> <parameter name="table_name" value="ecc_queue"/> <parameter name="agent_correlator" value=""/> </parameters> </results> |
Hi John,
Nice article. Is there a way to select specific columns from the table using JDBCProbe?
@Amit, I don’t think you can from the JDBCProbe script. I have utilized raw SQL queries for the JDBCProbe, but I had to build the ECC Queue XML record myself. You would use code similar to the following block of javascript to form the ecc queue payload for such as request:
var xml = ‘<?xml version=”1.0″ encoding=”UTF-8″?>’;
xml = ‘<parameters>’;
xml += ‘<parameter name=”jdbc_driver” value=”‘+driver+'”/>’;
xml += ‘<parameter name=”connection_string” value=”‘+connectionString+'”/>’;
xml += ‘<parameter name=”query” value=”Specific SQL”/>’;
xml += ‘<parameter name=”sql_statement” value=”‘+sqlQuery+'”/>’;
xml += ‘</parameters>’;
Hi John,
is there any way to call oracle function through JDBC probe apart from doing normal database operation.
Regards,
Ashvin
@Ashvin. Unfortunately, I don’t believe you can. I have never tried it though. If you get a chance, let me know if it worked for you.
John,
Thanks for the write up. I already have some uses for this, but I have a tough scenario.
Say I have field in the catalog that I need to have reference an external source (either DB or possible webservice call) on a ui action.
I would want it to make the call and bring the info back to the form for the user to make a choice from.
Is this possible without going into the ECC queue since that would take far to long for a use to wait for?
@Ryan, The instance typically can execute JDBC calls without a MID Server as long as the Database is accessible to the instance – either over the internet or via a VPN tunnel. Typically, I recommend only using MID Servers for JDBC calls due to offloading from the instance, etc. However, there are some cases like yours where it may be OK. In order to test it, just leave the MID Server field blank and see what you get. However, if your Database is not accessible, it is not going to work.
For some similar reading, I recommend you check out:
Live JDBC Queries displayed in ServiceNow
Thanks John, awesome write up – exactly what I needed!
Hi,
I tried this and it works fine for me. But how to insert date field in to the table?
x2 on how to insert date fields into the db table.
I have tried everything (from initially formatting as GlideDateTime, etc… but cannot get that to save to db.) Example would be great!
Hi John,
Great article. We used your examples to get our JDBCProbe implementation off the ground. That was back in the Fall.
We are using JDBCProbe to write to an oracle database. Our oracle admins have advised us that we need to set the sqlnet.ora file on the client to use SQLNET.ENCRYPTION_CLIENT = required
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256, AES192, AES128)
The problem is, there is no sqlnet.ora file in our MID Server. We have the ojdbc-jdk14.jar file that comes with the latest MID server download from SNC (part of their midserver zip package).
Do you have any experience with setting encryption for the jdbcprobe? Are these properties set using some jdbcprope script parameters maybe?
If I go to the Oracle JDBC FAQ page (http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html), and search for encryption i find a short section that talks about setting the properties and it made me wonder if I need to be settingsome jdbcprobe parameters in my script.
Thanks in advance for any thoughts on this matter.
Great comments Ty. I have never tried the encryption with oracle, but I did notice a similar enhancement note for Dublin that might be referencing that capability. Check out the following url: https://wiki.servicenow.com/index.php?title=Importing_Data_Using_Import_Sets#Dublin
Hi John,
Great Work!..I have one issue though, I am packaging a probe in a function that will be calling various sources so of course the user details are never the same… the password field in SN is encrypted so calling its in a Concatenated connection string does not give the correct password leading to the probe failing…have you come across a similar problem?
I am try to fetch limited columns of a table on a oracle database using a direct JDBCProbe but it is returning me all the columns of the table. As per wiki article, https://wiki.servicenow.com/index.php?title=JDBCProbe; we can get specific fields. Below is the work parameter of the xml structure :
You can find the link for this question on community – https://community.servicenow.com/thread/164321
Please share your thoughts why it is not working.
@Joe – You could write a function that generates your connection string on the fly…have the function take in a username and password as input parameters and use those parameters as you build out the connection string. That should do the trick for you.
@Kshitij – I don’t have Oracle to test that on…but maybe if you post your XML payload or your code (please remove any sensitive data), maybe I could be of help. You are correct though, you should be able to do that. in a select.
Hi John,
I have done the inbound integration of IBM Netcool with service-now. In this when a critical alarm comes to netcool, it sends a WSDL request to service-now to create an incident and in reply receives the incident number.This is working fine…
Now i want that if that incident will get resolve in service-now, then the s-now will update the netcool “alert.status” table “Severity” column to Resolved(i.e. 0). I have heared that this is possible by outbound integration using a jdbc probe….need you suggestion on this.
Hi John,
Great article. I have few questions:
(1) Is it possible to provide the query directly instead of providing it in parts?
(2) How can I specify multiple where clause i.e., where ssn == ‘XXXX’ and Type == ‘hire’?
(3) Is there is any way to use the credential table in this case? I don’t want to expose the password in code.
Waiting for response.
Thanks in advance
Also how can I update multiple column in one query. I guess I can trigger this from script include function.
Have you used the JDBCProbe with the sql_statement parameter defined on the wiki. I have tried and I cannot get it to work for the life of me. Here is my code:
var ecc = new JDBCProbe(‘cfdlsnmid01’);
ecc.setDataSource(‘dc05dadabcdfce008e40cb330af65cf8’);
ecc.setTable(‘was_change_deploy_records’);
ecc.setFunction(“select”);
ecc.addParameter(‘query’,’Specific SQL’);
ecc.addParameter(‘sql_statement’,”SELECT * FROM was_change_deploy_records WHERE change_id = ‘”+chg+”‘”);
var ecc_rec = ecc.create();
return ecc_rec;
It basically ignores the sql_statement parameter and returns every record in the table.
Any ideas?
Regarding date fields, I handled this by extending the JDBCProbe class, though you can also just push a sql date function in place of a number. (addNumberField does the same as addField, but without the quotes)
for(nextKey in this.fDarray) {
strDate = “to_date(‘”+ this.fDarray[nextKey] +”‘, ‘yyyy-mm-dd hh24:mi:ss’)”;
el = this.payloadDoc.createElement(nextKey, strDate);
el.setAttribute(“quoted”, “false”);
}
For selecting any record run time …use below “Select” query using BR
var SR=current.serial_number;
wc = “(SerialNumber=”+”‘”+SR+”‘)”;
j.setWhereClause(wc);
Is there a command to close the connection, or does the connection close once the command is run? Or is there a commit statement? Looking at using this for writing to Oracle Database but I need to make sure it commits and closes connection
Hi, Very useful info. I actually used it for a project connecting to Oracle database. I do have one issue where I am sort of stuck. If I had to update a field to NULL, the update doesn’t go through.
@Shahid S: I’m able to set (null) value. Pass empty string in the addField method value.
var j = new JDBCProbe(‘midservername’);
j.setDriver(‘oracle.jdbc.OracleDriver’);
j.setConnectionString(‘jdbc:oracle:thin:/@//:/’);
j.setTable();
j.setFunction(‘update’);
j.addField(“username”,””);
j.setWhereClause(“id=’123′”);
j.create();
Hi John,
Your posts are really helpful, I have checked execute JAR file, call midserver script etc.. they are very detailed, thanks a lot for sharing.
regarding this JDBC probe, i’m experiencing weird error, when i pass the table name in setTable method, output queue of ecc xml payload appends some special character (red dot) \u00b. I’m running it in script background so no chance of copy paste issue i think.
Anyway i have overcome this issue by passing table name in all small letter instead of all caps.
Thanks again.
I’m also having the same issue with updating a field to NULL for a Decimal data type. Sending an empty string (“”) does not go through correctly.
Hi John,
How to automate this jdbc probe scripts
Hi John,
I see that under connections for Oracle you are not defining any authentication “detailsOracle: [jdbc:oracle:thin:@]”
So we don’t need to use any authentication here
Hi,
Great article! Has anyone successfully performed an Insert of Multiple rows using the JDBC probe?
@Ayman, trying to do the same thing. Going by the name I suspect in the very same project 😀
Hello, this is a great article thank you John for always writing greta topics.
I got it to work with an MS SQL DB to update a record in the DB. however, what if the system failed, example record not found does not exist.
How can I check the logs if it failed to update /create in the DB ?