I've been investigating an issue reported by the user that is probably due
to the limitations of an outdated JDBC driver (snowflake) but might be due
to our interaction with the DEFAULT SQLDialect type. We only use a very
limited subset of what jOOQ offers and haven't seen many problems reported
by users but they may quietly be using workarounds that we're not aware of.
The original problem involves a MERGE statement and ctx.execute(). Our plan
is to start by greatly simplifying the query and added pieces until it
breaks.
Enough preamble. For a while
private static final String SRC_TABLE_NAME = "swat1239_1";
private static final String DEST_TABLE_NAME = "swat1239_2";
private static final Table<Record> SRC_TABLE =
DSL.table(SRC_TABLE_NAME);
private static final Table<Record> DEST_TABLE =
DSL.table(DEST_TABLE_NAME);
private static final Field<String> NAME = DSL.field("NAME",
String.class);
private static final Field<Integer> NUM = DSL.field("NUM",
Integer.class);
void prepareData(DSLContext ctx) {
ctx.truncate(DEST_TABLE);
ctx.insertInto(DEST_TABLE, NAME, NUM)
.values("test data2", 2)
.values("test data3", 3);
}
worked. I would see two records when I did a traditional "select * from
swat1239_2". Then, without warning, I only saw one record. I thought it
might be because of caching or something - I was using a different
connection. So I rewrote the code to reuse the verification method to use
jOOQ
void showData(DSLContext ctx) throws SQLException {
Result<Record> result = ctx.selectQuery(DEST_TABLE).fetch();
System.out.println(result);
}
and was told that the object didn't exist. Maybe it's capitalization or
something so I changed the prepare data to create the table
void prepareData(DSLContext ctx) {
ctx.dropTableIfExists(DEST_TABLE);
ctx.createTable(DEST_TABLE).columns(NAME, NUM);
ctx.insertInto(DEST_TABLE, NAME, NUM)
.values("test data2", 2)
.values("test data3", 3);
}
but I still see the error. Finally, in frustration, I tried going back to
the original JDBC code (but continuing to reuse the conneciton)
void showData(DSLContext ctx) throws SQLException {
ctx.connection(new ConnectionRunnable() {
public void run(Connection conn) throws SQLException {
try (Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(String.format(
"select * from \"%s\"",
DEST_TABLE.getName()))) {
Result<Record> result = ctx.fetch(rs);
System.out.println(result);
}
}
});
}
but I still get the error with and without the quote marks. (Snowflake uses
double quotes).
At this point I'm half-convinced that I've never seen what I though I saw,
that the jOOQ code (to insert the data) wasn't writing to the same place as
what I got with the new ConnectionManager.getConnection() call. I don't
know if it's a commit issue, but if it is wouldn't this all be the same
transaction (once you get past the DDL in prepareData)?
Any ideas? I can always do all of the prep work using traditional JDBC but
I would prefer to stay within jOOQ since we do use it to insert the data.
We just never do everything in a single place like this.
Thanks,
Bear
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
