Insert time into Oracle SQL

SCYing - shadow · ‎05-01-2017

I am currently using "Integration - JDBC" plugin to insert records into Oracle SQL. Everything works just fine except the date field. We can successfully insert date, but it can't have the time.

Below is the sample code I have in our business rules:

var j = new JDBCProbe(midServerName);

j.agent_correlator=j.addParameter('agent_correlator',gs.generateGUID());

j.setDriver("oracle.jdbc.OracleDriver");

j.setConnectionString('jdbc:oracle:thin:'+uname+'/'+pw+'@//'+host+':'+port+'/'+serviceName);

j.setTable("snowdb.CMDB_CI");

var sys_created_on = current.sys_created_on.nil() ? null : current.sys_created_on.getByFormat("dd-MMM-yyyy").toString();

j.addField("SYS_CREATED_ON", sys_created_on);

j.setFunction('insert');

j.create();

This works just fine. Oracle will accept format "dd-MMM-yyyy", but the time will be "00:00:00" in Oracle SQL.

What we want to have is something like:

sys_created_on = "TO_DATE('2003/05/03 21:02:44', 'yyyy/MM/dd hh24:mi:ss')";

sys_created_on = "03-MAY-2003 21:02:44";

None of above codes work so far. The XML will be in ready state for JDBCProbe and not get processed.

Any help will be welcomed.

SCYing - shadow · ‎05-12-2017

I raised an HI ticket and the support provided the solution. Just want to post it here in case other people face this issue as well.

The mid server will create the Oracle SQL base on your input. "addField" will cause the TO_DATE function wrapped with single quotes and looks like below:

insert into snowdb.cmdb_ci SYS_CREATED_ON values 'TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss')'

and Oracle expect:

insert into snowdb.cmdb_ci SYS_CREATED_ON values TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss')

By using "addNumberField" instead of "addField" will resolve the issue, because the value will not be wrapped around with single quotes.

var j = new JDBCProbe(midServerName);

j.agent_correlator=j.addParameter('agent_correlator',gs.generateGUID());

j.setDriver("oracle.jdbc.OracleDriver");

j.setConnectionString('jdbc:oracle:thin:'+uname+'/'+pw+'@//'+host+':'+port+'/'+serviceName);

j.setTable("snowdb.CMDB_CI");

var sys_created_on = "TO_DATE('2003/06/03 12:05:22', 'yyyy/MM/dd hh24:mi:ss')"

j.addNumberField("SYS_CREATED_ON", sys_created_on);

j.setFunction('insert');

j.create();

View solution in original post

Brent L_3 · ‎05-31-2019

So it appears that the addNumberField logic no longer works for adding a date time field. Instead you need to use the following line instead.

.addTimestampField('PLANNED_START_DATE',start_date,'yyyy-MM-dd HH:mm:ss');

So the values in the function (<field name>,<value>, <format>). It took a while to get a format that worked.

Thanks to Archana and Orlando from the HI team for helping point me in the right direction.

AJ5 · ‎06-03-2019

Hello Guys,

Sorry, this is off topic but I am posting this question here for some inputs on JDBC Probe since you all are already inserting records to external database.

I have been able to make connection to the external database and do the import. I am trying to find a way to insert records from ServiceNow tables to external database (Oracle, SQL, MYSQL). I am missing an important step not sure where to start with, where to write the script. I would really appreciate if you can shed some light with a some examples.

Thanks in advance

AJ

Service Manager · ‎03-13-2020

Hi AJ,

Did you find the solution to insert and update records from ServiceNow to external sql server database yet?

Thnaks