CODEWITHSUNDEEP
javajdbcprepared-statementinformixbatch-insert
Janhavi Gadkari
Janhavi Gadkari

Reputation: 133

Batch Insert for Timeseries table of Informix

I am trying to do Batch Insert for a table in Informix. I tried following code to perform Batch Insert for a normal table.

PreparedStatement ps = conn.prepareStatement("insert into tableName (a,b,c,d) values(?,?,?,?)"); 
ps.addBatch();
int[] n =  ps.executeBatch();
System.out.println("Array size : " + n.length); // Output : Array size : 1

And the above code works successfully for normal table. But when I try to do the same batch insert on time-series table, its not working, neither there is any Exception caught, also the return type int[] of executeBatch() gives me count as zero.

int[] n = ps.executeBatch();
System.out.println("Array size : " + n.length); // Output : Array size : 0

Any idea what am I missing or doing wrong?

Upvotes: 0

Views: 648

Answers (1)

Brian Hughes
Brian Hughes

Reputation: 683

One thing to remember is that for Informix, Timeseries looks a lot like a table inside of a table. So when you insert into your base table, you have a column of type "timeseries". The actual timeseries data then goes into that column. This leads to some strange looking SQL as you have to run "UPDATE" statements to "insert" timeseries data since we are basically manipulating that one column in that one row in our base table.

Here is a full example that uses JDBC to setup a basic timeseries table and do batched inserts on a timeseries column. This example presumes you enter the timeseries data every minute and simulates that.

try(Connection c = DriverManager.getConnection("jdbc:informix-sqli://HOST:PORT/DATABASENAME", "username", "password") {
        try(Statement s = c.createStatement()) {
            //Auto registers timeseries if it does not exist (12.10 or higher versions of the server I believe)
            s.execute("INSERT INTO CalendarPatterns VALUES ('patt_1min', '{1 on , 59 off}, second')");
            s.execute("INSERT INTO CalendarTable (c_name, c_calendar)" + 
                    "     VALUES ('cal_1min', 'startdate(2018-01-01 00:00:00), " + 
                    "     pattstart(2018-01-01 00:00:00), pattname(patt_1min)')");
            s.execute("CREATE ROW TYPE ts_basic_row(entry_time DATETIME YEAR TO FRACTION(5), value float NOT NULL)");
            s.execute("CREATE TABLE tstab1( id integer, sensor timeseries(ts_basic_row))");
            s.execute("EXECUTE PROCEDURE TSContainerCreate ('test_container', 'rootdbs','ts_basic_row', 0, 0)");
        }
        //Insert a row with a timeseries column
        //Note the origin date matches the calendar pattern and calendar from above (explaining those is another exercise)
        try(PreparedStatement p = c.prepareStatement("INSERT INTO tstab1 VALUES(?, ?)")) {
            p.setInt(1, 1);
            p.setString(2, "origin(2018-01-01 12:00:00.00000), calendar(cal_1min), container(test_container), threshold(0), irregular, []");
            p.execute();    
        }

        //Now we can bulk insert into our timeseries
        //There are other mechanisms (setting IFX_USEPUT, using a bulk loader, etc) which could be faster, but this is a good start
        Calendar cal = Calendar.getInstance();
        Random r = new Random();
        try(PreparedStatement p = c.prepareStatement("UPDATE tstab1 SET sensor = PutElem(sensor, ROW(?, ?)::ts_basic_row) WHERE id=?")) {
            for(int i = 0; i < 1000; i++) {
                p.setDate(1, new java.sql.Date(cal.getTimeInMillis()));
                //add a minute to the calendar
                cal.add(Calendar.MINUTE, 1);
                p.setDouble(2, r.nextDouble()); //your sensor/timeseries value
                p.setInt(3, 1);  //The row in your base table (matching the id column)
                p.addBatch();
            }
            int [] results = p.executeBatch();
            System.out.println(Arrays.toString(results)); // a bunch of '1' values as expected
        }
    }

Upvotes: 3

Related Questions