Thanks Stanisias and Lukas, your answer was really fast! You are totally right. I checked the code that is working and it is configured to use STATIC_STATEMENT as statment type.
As we must use jOOQ 3.6.x (because ToroDB still supports Java 7), I think I will continue using the trick I said: To renderize the SQL and then use DSLContext#execute. El martes, 15 de diciembre de 2015, 13:00:13 (UTC+1), Lukas Eder escribió: > > Hi Gonzalo, > > Huh, interesting. In addition to the workaround that Stan already > mentioned (inlining the parameter, or using a table-valued function), you > can also set the Settings.statementType to StatementType.STATIC_STATEMENT > for this particular query. This will force *ALL* bind variables to be > inlined. See also the documentation around bind values for jOOQ: > http://www.jooq.org/doc/latest/manual/sql-building/bind-values > > However, jOOQ should do this for every CREATE VIEW statements, where bind > values don't really make any sense. > > I've created an issue for this. We'll fix it ASAP: > https://github.com/jOOQ/jOOQ/issues/4806 > > Thanks a lot for reporting this! > Lukas > > 2015-12-15 12:46 GMT+01:00 Stanislas Nanchen <[email protected] > <javascript:>>: > >> Hi Gonzalo! >> >> It seems that your query uses a parameter : where ("test"."root"."sid" = >> ? ......) >> It is not possible to directly use a parameter in a view. >> You have 2 possibities: >> 1. inlining the parameter : but then the view is fixed >> 2. using a function: see this stackoverflow: >> http://stackoverflow.com/questions/11401749/pass-in-where-parameters-to-postgresql-view >> >> Does this help you? >> Cheers. stan. >> >> >> >> On Tuesday, December 15, 2015 at 12:38:27 PM UTC+1, Gonzalo Ortiz >> Jaureguizar wrote: >>> >>> Hi there, >>> >>> Here at 8kdata we widely use jOOQ on ToroDB to dinamically generate SQL >>> staments. I am developing a new feature that will automatically generate >>> some views, but I am blocked with a strage error from my backend database. >>> I am using jOOQ 3.6.4 and PostgreSQL as backend and dialect. The generated >>> statment is something like: >>> >>> create view "test"."whatever1"("did", "whatever2", "whatever3") >>> as select "test"."t_3"."did", "test"."t_3"."whatever2", >>> "test"."t_3"."whatever3" from "test"."t_3" >>> join "test"."root" >>> on "test"."t_3"."did" = "test"."root"."did" >>> where ("test"."root"."sid" = ? and "test"."t_3"."index" is null) >>> >>> The error I recive from postgres is the following >>> >>> *ERROR: there is no parameter $1*And when I turn on the jooq logging >>> this is printed: >>> >>> DEBUG org.jooq.tools.LoggerListener - Executing query : create view >>> "test"."whatever1"("did", "whatever2", "whatever3") as select >>> "test"."t_3"."did", "test"."t_3"."whatever2", "test"."t_3"."whatever3" from >>> "test"."t_3" join "test"."root" on "test"."t_3"."did" = "test"."root"."did" >>> where ("test"."root"."sid" = ? and "test"."t_3"."index" is null) >>> DEBUG org.jooq.tools.LoggerListener - -> with bind values : create view >>> "test"."whatever1"("did", "whatever2", "whatever3") as select >>> "test"."t_3"."did", "test"."t_3"."whatever2", "test"."t_3"."whatever3" from >>> "test"."t_3" join "test"."root" on "test"."t_3"."did" = "test"."root"."did" >>> where ("test"."root"."sid" = 1 and "test"."t_3"."index" is null) >>> >>> If I do not execute the query calling org.jooq.Query#execute but instead >>> I render the sql with org.jooq.Query#toSQL(org.jooq.conf.ParamType) and >>> then I execute it with org.jooq.DSLContext#execute, I have no problems. >>> >>> The funny thing is that there are some other views that are being >>> generated in a very simmilar way, using similar code and they are >>> rendereized in the same way (both on logs and with toSQL method) but they >>> can be executed without problems. >>> >>> I am sure this problem is originated on my code, I may need to specify >>> something to jOOQ to change the way it speaks with the driver or something >>> like that. >>> >>> Do you have any ideas of what is happening? >>> >>> Bests, >>> >>> Gonzalo >>> >> -- >> 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] <javascript:>. >> 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.
