How to insert integer data to PostgreSQL database table from datatable?

[Priyatosh]

Hi,

I have created a services in database for updating the data of database table,  from data table, but it is not updating the integer data in database table, following is the service details,

let table;
let variable;
let DataTableData = Things["pp_44270_SPC_DataTable"].GetDataTableEntries({
maxItems: 1000 /* NUMBER {"defaultValue":500} */
});

for (var i=0; i <=DataTableData.rows.length; i++)
{
var row=DataTableData.rows[i];

table=me.Table_Update1({
DataSource: row.datasource /* STRING [Required] */,
Workstation: row.workstation /* STRING [Required] */,
Model: row.model /* STRING [Required] */,
Variable: row.variable /* STRING [Required] */,
Value: row.value /* INTEGER [Required] {"minimumValue":1,"maximumValue":100} */,
Key: row.key
});


var result=table;
}

I will be thankful, if some one provide me the solution.

Thanks in advance.

[nmilleson]

@Priyatosh ,

Is your service throwing an error or is it updating all of the other columns?  One thing I usually do before I insert integer values into a database using Thingworx is explicitly cast it to integer:

parseInt(row.value, 10)

I also do some error checking around it to ensure it's not null.  If you need to be able to insert null values as well, you'll need to use  << >> brackets around your SQL parameters rather than [[ ]] brackets. 

Hope that helps,

Nick 

[Priyatosh]

HI @nmilleson ,

As per you, I have given the data as-

Value: parseInt( row.value,10) /* INTEGER [Required] {"minimumValue":1,"maximumValue":100} */,
Key: parseInt(row.key, 10)

and the postgreSQL command is I am using as following-

INSERT INTO SPC_Table (DataSource, Workstation, Model, Variable, Value, Key ) VALUES ([[dataSource]], [[workstation]], [[model]], [[variable]], [[value]], [[key]] );

but still not working.

I tried with <<>>,  instead of [[ ]] ,it is not working.

Thanks for the reply.

[nmilleson]

@Priyatosh ,

Is it giving an error?  If so, can you share it?  

Nick

[Priyatosh]

Hi @nmilleson ,,

Using parseInt, it is not giving any error, but using Math.ceil/trunc/floor, it is showing following error-

 Error executing service Table_Update. Message :: Wrapped java.lang.NullPointerException - See Script Error Log for more details.

[nmilleson]

I'm guessing there are null values coming through that are breaking your query.  Maybe a test for that would be to do something like this for your integer values:

!isNaN(parseInt(row.value,10)) ? parseInt(row.value,10) : -1

[Priyatosh]

Hi @nmilleson ,

It is also not working, but why it's happen as the  datashape and the table of database ,I have mentioned as integer type only. Only at the time of inserting the value it is not inserting.

[nmilleson]

@Priyatosh ,

I would suggest adding some logging into your service to ensure that the integer values are indeed being passed to your SQL service.  Perhaps something is missing.

-Nick

[Priyatosh]

HI @nmilleson ,

For your kind information, I am using PostgreSQL database.

and now I have implemented it, using try catch block with debug information. Following is the code-

let table;
let variable;
try {
let DataTableData = Things["pp_44270_SPC_DataTable"].GetDataTableEntries({
maxItems: undefined /* NUMBER {"defaultValue":500} */
});

for (var i = 0; i <= DataTableData.rows.length; i++) {
var row = DataTableData.rows[i];

table = me.Table_Update1({
dataSource: row.datasource /* STRING [Required] */ ,
workstation: row.workstation /* STRING [Required] */ ,
model: row.model /* STRING [Required] */ ,
variable: row.variable /* STRING [Required] */ ,
value: row.value /* INTEGER [Required] {"minimumValue":1,"maximumValue":100} */ ,
key: row.key
});

var result = table;
}
logger.debug("DatabaseController.JavaScriptInsert_SPC_Table(): Query - " + command);
} catch (error) {
logger.error("DatabaseController.JavaScriptInsert_SPC_Table(): Error - " + error.message);
}

but data is not inserting in the table. Could provide me some solution?

Thanks in advance

[nmilleson]

@Priyatosh ,

I would suggest adding this after this row:

var row = DataTableData.rows[i];

logger.debug("Row: " + i + ", Value: " + row.value);

This should tell us if there are indeed integer values there. 

[PaiChung]

if it is required to be an integer and there isn't an automatic conversion happening anywhere, you could try using math.round (floor/ceil/trunc) or parseInt

[Priyatosh]

Hi @PaiChung ,

Thank you for you kind reply. 

I tried with Math.floor/ceil/trunc, neither is working, givingh following error-

 Error executing service Table_Update. Message :: Wrapped java.lang.NullPointerException - See Script Error Log for more details.

and tried with parseInt also as @nmilleson  said, not get the result.

Thank you

[PaiChung]

What sort of error messages are you receiving?

Thingworx side / postgres side

[Priyatosh]

Hi @PaiChung , No  error, simply after inserting the value, integer type field is empty, while string is inserting properly.

[TL_9220870]

My approach is to check if row.value really exists as such in your original table. If the name of the column is misspelled, this doesn't result in an error, it just doesn't find the value.
Ain't no mistake here...
What happens in me.Table_Update1? here you pass the variables. Have a look here to see if all the designations are correctly declared and this is not where the error devil is hiding.