INSERT values alias problem

[stev...@gmail.com]

Hello-

I'm actually a commercial user, but have a low-priority support question.  I have generated bindings and I have code, following the examples, like:

  AmlTestResults tr = AML_TEST_RESULTS.as("tr");
        create.insertInto(tr, tr.ID, tr.BANK_ID, tr.RUN_ID, tr.TEST_NAME, tr.TEST_SCORE)
                .values(34L, 123, "T1", "ThisIsATest", -1000000).execute();

However, I see that this generates sql incorrectly.  The table name is AML_TEST_RESULTS but it creates the insert statement with the alias instead of the table name!

DEBUG Executing query          : insert into [tr] ([id], [bank_id], [run_id], [test_name], [test_score]) values (?, ?, ?, ?, ?)
DEBUG -> with bind values      : insert into [tr] ([id], [bank_id], [run_id], [test_name], [test_score]) values (34, 123, 'T1', 'ThisIsATest', -1000000)
DEBUG Exception                : Total: 81.936ms
DEBUG Finishing                : Total: 83.077ms, +1.14ms

org.springframework.jdbc.UncategorizedSQLException: jOOQ; uncategorized SQLException for SQL [insert into [tr] ([id], [bank_id], [run_id], [test_name], [test_score]) values (?, ?, ?, ?, ?)]; SQL state [S0002]; error code [208]; Invalid object name 'tr'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'tr'.
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at com.argodata.fraud.commons.database.mapping.JooqExceptionTranslator.exception(JooqExceptionTranslator.java:21)
    at org.jooq.impl.ExecuteListeners.exception(ExecuteListeners.java:232)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:328)
    at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:140)


The workaround of doing create.insertInto(AML_TEST_RESULTS, tr.ID, ..) works fine, but it seems like I should be able to do the other as well, no?

Also, as a minor nit: in all of the documentation examples here http://www.jooq.org/doc/3.3/manual/sql-building/sql-statements/insert-statement/ it never shows that you need to call .execute() at the end.  This was pretty obvious to debug, but still a minor annoyance for someone new to jooq.

Thanks!
Steve Ash

[Lukas Eder]

Hi Steve,

Thanks for reporting these things

2014-04-07 19:19 GMT+02:00 <stev...@gmail.com>:

Hello-

I'm actually a commercial user, but have a low-priority support question.  I have generated bindings and I have code, following the examples, like:

  AmlTestResults tr = AML_TEST_RESULTS.as("tr");
        create.insertInto(tr, tr.ID, tr.BANK_ID, tr.RUN_ID, tr.TEST_NAME, tr.TEST_SCORE)
                .values(34L, 123, "T1", "ThisIsATest", -1000000).execute();

However, I see that this generates sql incorrectly.  The table name is AML_TEST_RESULTS but it creates the insert statement with the alias instead of the table name!

DEBUG Executing query          : insert into [tr] ([id], [bank_id], [run_id], [test_name], [test_score]) values (?, ?, ?, ?, ?)
DEBUG -> with bind values      : insert into [tr] ([id], [bank_id], [run_id], [test_name], [test_score]) values (34, 123, 'T1', 'ThisIsATest', -1000000)
DEBUG Exception                : Total: 81.936ms
DEBUG Finishing                : Total: 83.077ms, +1.14ms

To me, this looks like the "correct" SQL in a way that this is "probably" your intent when you alias / rename a table that you provide to an INSERT statement.

Note that T-SQL (and most other dialects) does not allow any table aliasing in INSERT statements:

http://technet.microsoft.com/library/ms174335.aspx

In other words, optimally, you would not pass an aliased table to INSERT statements. May I ask why you were attempting to do this? Is it to have shorter Java table references? If so, you are not required to actually alias those reference in order to have shorter Java variable names. You could:

- Just create a local reference like this: AmlTestResults tr = AML_TEST_RESULTS;

- Use advanced code generation options, to change the naming patterns of the code generator. This is explained here: http://www.jooq.org/doc/latest/manual/code-generation/codegen-matcherstrategy/

Note that truly aliased tables with INSERT statements might make sense in the near future, when we provide support for common table expressions, where you could write:

WITH [tr](...) AS (

    SELECT * FROM AML_TEST_RESULTS

)

INSERT INTO [tr] (...)

VALUES (...)

org.springframework.jdbc.UncategorizedSQLException: jOOQ; uncategorized SQLException for SQL [insert into [tr] ([id], [bank_id], [run_id], [test_name], [test_score]) values (?, ?, ?, ?, ?)]; SQL state [S0002]; error code [208]; Invalid object name 'tr'.; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'tr'.
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at com.argodata.fraud.commons.database.mapping.JooqExceptionTranslator.exception(JooqExceptionTranslator.java:21)
    at org.jooq.impl.ExecuteListeners.exception(ExecuteListeners.java:232)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:328)
    at org.jooq.impl.AbstractDelegatingQuery.execute(AbstractDelegatingQuery.java:140)


The workaround of doing create.insertInto(AML_TEST_RESULTS, tr.ID, ..) works fine, but it seems like I should be able to do the other as well, no?

Columns must not be qualified in the INSERT statement's column list in almost all SQL dialects. Hence, jOOQ only renders the column's name, not the full qualification. That is why this works, coincidentally.

Also, as a minor nit: in all of the documentation examples here http://www.jooq.org/doc/3.3/manual/sql-building/sql-statements/insert-statement/ it never shows that you need to call .execute() at the end.  This was pretty obvious to debug, but still a minor annoyance for someone new to jooq.

You're right, thanks for pointing this out. In principle, all examples should have either fetch() or execute() in them to prevent this sort of confusion. In some examples, this was forgotten. I have registered an issue for this:

https://github.com/jOOQ/jOOQ/issues/3170

I hope this helps,

Lukas

[Rob Sargent]

Have you tried

AmlTestResults tr = AML_TEST_RESULTS.as("tr");

create.insertInto(AML_TETS_RESULTS, tr.ID, tr.BANK_ID, tr.RUN_ID, tr.TEST_NAME, tr.TEST_SCORE)

                .values(34L, 123, "T1", "ThisIsATest", -1000000).execute();

--
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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[stev...@gmail.com]

Rob-

Yes this is what I did.

Lukas-

Yes I was just being stupid.  I should've (and have now) just done AmlTestResults tr = AML_TEST_RESULTS; as you recommend.

[Lukas Eder]

Hi Steve,

Glad to hear that this has sorted out your issue!

Best Regards,

Lukas