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]>: > 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]. > 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.
