search

gflewis / sndml3

ServiceNow Data Mart Loader: an application to load SQL databases from ServiceNow
MIT License
30 stars 19 forks source link

DatabaseUpdateStatement bindField time_in_seconds java.lang.NumberFormatException #36

Closed jonathangull closed 2 years ago

jonathangull commented 2 years ago

Getting this , job aborts from the app. On UI it stays in running status, is there a way, the record is skipped and job continues?

03:58:50 ERROR DatabaseUpdateStatement [DPRUN0001004] DPRUN0001004 PROCESS: bindField time_in_seconds="17763811500" Exception in thread "DPRUN0001004" java.lang.NumberFormatException: For input string: "17763811500" at java.base/java.lang.NumberFormatException.forInputString(NumberFormatException.java:67) at java.base/java.lang.Integer.parseInt(Integer.java:668) at java.base/java.lang.Integer.parseInt(Integer.java:784) at sndml.datamart.DatabaseStatement.bindField(DatabaseStatement.java:236) at sndml.datamart.DatabaseStatement.bindField(DatabaseStatement.java:69) at sndml.datamart.DatabaseUpdateStatement.update(DatabaseUpdateStatement.java:34) at sndml.datamart.DatabaseUpdateWriter.writeRecord(DatabaseUpdateWriter.java:34) at sndml.datamart.DatabaseTableWriter.processRecords(DatabaseTableWriter.java:76) at sndml.servicenow.RestTableReader.call(RestTableReader.java:80) at sndml.datamart.JobRunner.runLoad(JobRunner.java:223) at sndml.datamart.JobRunner.call(JobRunner.java:99) at sndml.daemon.AppJobRunner.call(AppJobRunner.java:85) at sndml.daemon.AppJobRunner.run(AppJobRunner.java:60) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833) 04:00:45 INFO AgentScanner [scanner] main INIT: Nothing ready 04:02:45 INFO AgentScanner [scanner] main INIT: Nothing ready 04:14:43 INFO AgentScanner [scanner] main INIT: Nothing ready

gflewis commented 2 years ago

This is a really low level error. It appears that the code is trying to put the value 17763811500 into a 32-bit integer, and it will not fit. Can you tell me more about the type of database you are using (Oracle? SQL Server? MySQL?) and the field time_in_seconds? What is the data type in ServiceNow? What is the data type in the SQL database? It is possible that the app is not generating the correct SQL data types, and you may need to modify the file sqltemplates.xml (See https://github.com/gflewis/sndml3/wiki/Templates).

17763811500 seconds translates to approximately 562.9 years. Is this a valid data value? If the column is not being used, then the easiest solution would probably be to drop the column from the SQL table. If the column is being used, then the easiest solution might be to drop the INTEGER column from the database table and replace it with a DOUBLE.

jonathangull commented 2 years ago

I found a record with this time worked ( I am trying to copy the task_time_worked table), obviously, it's not the right value however since it was possible to record this value against a task, it's possible it happens again. For now, I have edited the value to a realistic value on task.

I am dealing with mssql db. I looked at sqltemplates.xml and I see numeric values are mapped to Int. Since I cant specify table wise mapping, it might be too much to change all numeric fields to big int in my opinion.

Any suggestions how can poor data values can be stopped from ending the jobs like this?

gflewis commented 2 years ago

I think the mapping of "numeric" to "int" is simply incorrect. I looked at the mapping for Oracle, and it maps "numeric" to NUMBER(10).

BigInt would be a Java Long, but there does not appear to be any code in the current version of DatabaseStatement.java to support BigInt or Long. Maybe it can be added in a future version. I think your best bet for now would be to try mapping all "numeric" fields to either Decimal or Numeric.

jonathangull commented 2 years ago

Thanks @gflewis fixed the bad entry in the record to take care of this.