OHDSI Home | Forums | Wiki | Github

SQL issues in JDBC

Developers
#1

@alfranke and I are making good progress toward getting Heracles jobs running. There is one problem we’ve had though. After we call SqlRender, we get a block of SQL that includes some PL/SQL blocks and some inserts, pretty much everything Patrick setup in the Heracles script, but we run a snag when we try to run with JDBC.

We’ve had a little more luck (just testing) using a CallableStatement and wrapping in a DECLARE BEGIN … END, but it’s still not working.

@jon_duke says this has been done before, but the example is we see is using R. Any luck running these types of scripts from Java?

#2

So, I think we got it working using SqlSplit from SqlRender, if anyone else sees this issue.

       String[] sql = SqlSplit.splitSql(sql);

…

        try {
            final int[] ret = this.jdbcTemplate.batchUpdate(sql);
            log.debug("Update count: " + ret);
        } catch (final Exception e) {
            log.error(e.getMessage(), e);
        }
#3

Sorry I couldn’t help sooner, Charity. We actually up to this point haven’t run any achilles-like batch scripts from anything except R. The sqlTranslate you might have seen already in WebAPI is just simple scripts.

Interestingly, I’m wondering if I just haven’t seen the issue yet in Circe becuase I haven’t executed a cohort construction job yet (next on the list!)

Good to know you have a workaround.

@schuemie, Any thoughts on this? I remember you had to split up batch SQL statements for specific reasons…

-Chris

#4

That is exactly what the SqlSplit.splitSql() function is for, so I think @cahilton got it exactly right.

In R nobody had to deal with this because the executeSql() function in the DatabaseConnector package calls the splitSql() function under the hood. (It is actually what drives the progress bar in case you were wondering)

t