Hi Bear,
Some different observations:
*Using the DEFAULT dialect:*
The point of this dialect is to allow for generating *some* SQL (mostly
standard SQL) in the absence of an actual dialect. This usually corresponds
to generating the most straightforward SQL string given the jOOQ API input,
not some emulation.
As for all dialects, the behaviour might change in the future. In other
SQLDialects, the change will produce more optimal SQL that is still
supported by the respective database. Since DEFAULT doesn't have any
corresponding database to integration test against, the change might be
backwards incompatible.
This is just a word of caution put here for the record. I'm sure you're
aware of this risk.
*Execute:*
I'm assuming this was just a problem in your email, not in your actual
code. Statements don't execute themselves, you always have to append an
.execute() call.
*Double quoting identifiers:*
If you need to have jOOQ add quotation marks around your tables, you have
two options:
1. Continue using the plain SQL API as you're doing, and add the quotation
marks around the identifiers yourself
2. Use the more appropriate DSL.name() API to create an identifier, which
will then be quoted according to the Settings.renderNameStyle
For instance:
private static final Name SRC_TABLE_NAME = DSL.name("swat1239_1");
private static final Name DEST_TABLE_NAME = DSL.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(DSL.name("NAME"),
String.class);
private static final Field<Integer> NUM = DSL.field(DSL.name("NUM"),
Integer.class);
*Transaction related things:*
Since you seem to be able to reproduce the missing data issue with jOOQ and
with JDBC, I'm not sure how I can help given the code you've provided. I
guess I'd need more information...
Thanks,
Lukas
2018-06-06 3:51 GMT+02:00 Bear Giles <[email protected]>:
> 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.
>
--
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.
