Instert rows into a database with a timestamp

Module Development
1

Hello all!

I want to insert previously calculated values into a database. Ideally it would have just two columns, the timestamp and the value. I want the first column to be the timestamp so it’s easier to query afterwards periods of time (if there is a better way to accomplish this, please let me know).

The problem is that if I select DataType.DateTime as the column type, it won’t accept java.util.Date . To what type should I transform my timestamp?

This is what I have so far:
In a runnable:

                SimpleDateFormat format = new SimpleDateFormat("yyyy_MM");
                String date = format.format(Date.from(Instant.now()));
                String tableName = date + params.getTagDataTableName();

                checkTableStructure(tableName + "_A");
                checkTableStructure(tableName + "_B");
                checkTableStructure(tableName + "_C");
                
                BasicHistoricalRecord aRecord = new BasicHistoricalRecord(tableName + "_A", "Historization");
                BasicHistoricalRecord bRecord = new BasicHistoricalRecord(tableName + "_B", "Historization");
                BasicHistoricalRecord cRecord = new BasicHistoricalRecord(tableName + "_C", "Historization");

                addData(aRecord, aMean);
                addData(bRecord, bMean);
                addData(cRecord, cMean);

                GatewayHook.getGatewayContext().getHistoryManager().storeHistory(params.getDataSource(), aRecord);
                GatewayHook.getGatewayContext().getHistoryManager().storeHistory(params.getDataSource(), bRecord);
                GatewayHook.getGatewayContext().getHistoryManager().storeHistory(params.getDataSource(), cRecord);

To check the table structure/create it:

private void checkTableStructure(String tableName) {
        SRConnection con = null; 
        
        try{
            con = GatewayHook.getGatewayContext().getDatasourceManager().getConnection(params.getDataSource());
            DBTableSchema table = new DBTableSchema(tableName,con.getParentDatasource().getTranslator());
            table.addRequiredColumn("Timestamp", DataType.DateTime, null);
            table.addRequiredColumn("Value", DataType.Float4, null);
            table.verifyAndUpdate(con); 
            logger.info("Successs");
        }catch(Exception e){
            logger.info("Exception checking table structure");
        }finally{
            DBUtilities.close(con);
        }
    }

And to add the values to the records:

private void addData(BasicHistoricalRecord record, float value){
        record.addColumn("Timestamp");
        record.addColumn("Value");
        List<Object> timeValue= new ArrayList<>();
        timeValue.add(Date.from(GatewayHook.lastExecution));
        timeValue.add(value);
        try {
            record.addRow(timeValue);
        } catch (Exception e) {
            logger.info(e.toString());
        }

    }

As it is right now, it is getting this exception:

java.lang.RuntimeException: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO 2021_04data_table_temp("Timestamp","Value") VALUES('2021-04-14 09:51:14.384+02',321.11627) was aborted: ERROR: syntax error at or near "2021" Position: 13 Call getNextException to see other errors in the batch

If you think there is a better way to do this please let me know :slight_smile: . Keeping in mind that this same module would query through this database that I’m creating (ideally all the values between two dates), would I be able to use any methods from Ignition’s libraries? Which ones?

Thank you in advance for your help!

2

That’s pretty odd; it should be using parameters correctly when inserting your values into the DB…
If you put the gateway.database.updates on TRACE, is the query being executed correctly using SQL placeholders?

3

Apparently table names shouldn’t start with a number!

1 Like