*Default*: understood. We're never going to be doing anything fancy, I'm
just pushing us to use jooq in place of jdbc for a lot of basic stuff. If
we're using the library then we should actually be using it, not just
cherry picking one or two features. In most cases we can use the
appropriate dialect but for the exceptions we won't need anything fancy, or
could just drop back to jdbc.
This actually came up in my one-on-one with my boss yesterday. I'm slower
than some of my coworkers but it's because I insist on taking the time to
start to get an idea of how the author intended a library to be used,
several coworkers just find something that looks like it will do what they
want and then they bang on it until it does. That might work for a one-off
task but something central, like, oh, database access, is something that
you're going to come back to again and again. We've undoubtedly wasted more
time figuring out how to work around our prior solutions than we "saved" by
focusing on getting something, anything, that worked.
</soapbox>
Anyway pushing for more jooq on the basic stuff will hopefully make people
more aware of how it should be used and the necessary refactoring will be
easier to defend.
*Execute*: brain-fart. I was seeing them more as a self-executing
description than a 'builder pattern' that needs the final
build()/execute(). I don't know why. Phase of the moon?
*Quotes*: I've also been using Names, I was trying explicit strings since I
was getting object not found... because I forgot to call execute() earlier.
Duh. Thanks for pointing out the proper Setting. I had looked at the
javadoc but couldn't figure out where it was set.
On Tuesday, June 5, 2018 at 7:52:16 PM UTC-6, The bear in Boulder wrote:
>
> 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.
