Hi Steve, Thanks for reporting these things
2014-04-07 19:19 GMT+02:00 <[email protected]>: > 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 -- 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.
