Java exception when inserting into postgres db

Ignition
1

When i execute the code below, I get the following error
B_CONNECTION = ‘data_collector_db_connection’
level = ‘WARNING’
message=‘MyModule:: my message’
query = “INSERT into my_schema.logs (timestamp, module, level, message) VALUES(now(),?,?,?)”
results = system.db.runPrepUpdate(query,[module, level, message], DB_CONNECTION)

<type ‘java.lang.Exception’> : java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT into equipments.logs (timestamp, module, level, message) VALUES(now(),?,?,?), [MyModule, WARNING, MyModule:: my message], data_collector_db_connection, , false, false)")

‘data_collector_db_connection’ is the name of the database connection (not db name) and its status is ‘Valid’. The table and column names are all correct.

Are there some other configuration i need to make in order to allow the script to write to the db? I can read/query the db fine, and the db userid has select/insert/update/delete privileges. I made sure the table and column names are all correct.

TIA

2

Welcome!

Try:

query = “INSERT into my_schema.logs (timestamp, module, level, message) VALUES(?,?,?,?)”
results = system.db.runPrepUpdate(query,[now(),module, level, message], DB_CONNECTION)

Also your first line says "B_CONNECTION" not "DB_CONNECTION", assume this is copy and paste error but if not, fix that too.

3

@Matrix_Engineering is on the right path, but it still won’t work with now().
EDIT: or rather, now() would have to be a defined function in the db in the first instance, or a defined function in the second instance.

EDIT2: I also noticed that MyModule is not defined in the script you posted. As a tip, when you look at the error messages, there should be a ‘Caused by’ line. That should give an extra hint as to what is going on.

DB_CONNECTION = 'data_collector_db_connection'
level = 'WARNING'
message='MyModule:: my message'
now = system.date.now()

query = "INSERT into my_schema.logs (timestamp, module, level, message) VALUES(?,?,?,?)"
results = system.db.runPrepUpdate(query,[now, MyModule, level, message], DB_CONNECTION)