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

michael_baker · ‎05-02-2017

Hi Paul,

If you convert the "current.sys_created_on" value to a GlideDateTime record you will have functions that should provide you what you need.

I would recommend replacing the below line:

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

With these new lines:

var gdt = new GlideDateTime(current.sys_created_on);

var sys_created_on = current.sys_created_on.nil() ? null : (gdt.getDate().getByFormat('dd-MMM-yyyy') + ' ' + gdt.getTime().getByFormat('HH:mm:ss'));

Hope this helps!

Regards,

Mike Baker

SCYing - shadow · ‎05-04-2017

Thanks for the help Michael. Unfortunately, It did not work.

The idea is that the date will be converted to below XML. With your script, the date format will be like below in ECC Queue.

With that XML sent out to MID server, MID server tried to process the XML and gave below error.

This is caused by incorrect date format from Oracle SQL. That is why I tried below line because this is how you write query to insert record in Oracle SQL. Again, this won't work either.