Skip to main content
P
Priyatosh1-Visitor
1-Visitor
September 25, 2022
Question

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

  • September 25, 2022
  • 3 replies
  • 4689 views

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.

3 replies

N
nmilleson17-Peridot
17-Peridot
September 26, 2022

@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 

P
Priyatosh1-VisitorAuthor
1-Visitor
September 26, 2022

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.

N
nmilleson17-Peridot
17-Peridot
September 26, 2022

@Priyatosh ,

 

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

 

Nick

P
PaiChung22-Sapphire I
22-Sapphire I
September 26, 2022

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

P
Priyatosh1-VisitorAuthor
1-Visitor
September 26, 2022

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

P
PaiChung22-Sapphire I
22-Sapphire I
September 27, 2022

What sort of error messages are you receiving?

Thingworx side / postgres side

T
TL_922087012-Amethyst
12-Amethyst
September 30, 2022

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.

Terms & ConditionsCookie settingsAccessibility statement