JDBC Probe to insert/update records into sql database table from ServiceNow

Service Manager · ‎03-13-2020

Hi,

I was trying to insert/update the record via JDBC probe into SQL database table from ServiceNow. Below script was working as expected

var mid = new GlideRecord("ecc_agent");

mid.addQuery('name','MID_Serverxxx');

mid.query();

if(mid.next()){

}

var data = new GlideRecord("sys_data_source");

data.addQuery('name','xxname of datasourcexx');

data.query();

if(data.next()){

}

var r1 = new JDBCProbe(mid.name);

r1.setDataSource(ds.sys_id);

jr1.setFunction("INSERT");

r1.setTable("xxdatabase tablexx");

r1.addParameter("skip_sensor","true");

r1.addField("Number", "INC7845129");

r1.addField("Contact", "david");

r1.create();

But when I updated to below script to insert the records from target table "Incident", tickets assigned to Service desk group. It throws me an error

r1.addField("Number", current.number);

r1.addField("Contact", current.caller);

r1.addField("opened",current.opened_at);

r1.create();

Thanks

Tanaji Patil · ‎03-13-2020

Your old script was working because you were not using current object there. You had static values (INC7845129 and david).

Now it's not working because current object is not available on scheduled jobs or background scripts.

current object is a GlideRecord object representing records in table and is available in server side scripts directly acting on tables (business rule, ACL, workflows).
For more information check-
https://community.servicenow.com/community?id=community_question&sys_id=a6d62986db7d1b005322f4621f9619dd#:~:text=Current%20object%20refers%20to%20current%20table.,refers%20to%20an%20incident%20table.

So you need to do a GlideRecord on incident table and then use that gliderecord object to dot walk to incidents fields.

For e.g. following script will give you all active incidents. gr is the gliderecord object-
var gr = new GlideRecord("incident");
gr.addQuery("active","true");
gr.query();
while(gr.next()) {
// Here you have gr as GlideRecord object representing each record
// So gr.caller_id.name will give you callers name on incident
}

For more information of GlideRecord check-
https://www.servicenowguru.com/scripting/gliderecord-query-cheat-sheet/

-Tanaji
Please mark reply correct/helpful if applicable

View solution in original post

Tanaji Patil · ‎03-13-2020

Isn't the field name of Caller field caller_id? And don't you need name of the user?
So your second line should be like+
r1.addField("Caller",current.caller_id.name);

I hope opened is a column available in your database.

-Tanaji
Please mark response correct/helpful if applicable

Tanaji Patil · ‎03-13-2020

Or if you need only the first name of user then this-
r1.addField("Caller",current.caller_id.first_name);

-Tanaji
Please mark response correct/helpful if applicable

Service Manager · ‎03-13-2020

Thanks for your quick response!

I believe we are suppose to define the property over here because when I tried, I got below error

and r1.addField("contact",current.caller_id.name)

here contact is a name of the field on sql table, so I think we cannot change it.

I also wanted to add filter such as

insert/update only incidents assigned to service desk group.

Thanks

Tanaji Patil · ‎03-13-2020

Sorry my bad. That was a typo with Contact.

Where are you writing this script? Is it not in a BR on incident table? If it is on incident table then check if Caller field is having value in it.

If it's not a BR on incident table then you need to have a GlideRecord on incident table and then dot walk through that GlideRecord object to get to the caller_id.

-Tanaji
Please mark response correct/helpful if applicable