Hello Shyam,
2014/1/21 Sha <[email protected]>
> Thanks a lot for your quick reply Lukas,
> Sorry if it appears me asking do my work.Thats not my intension. To
> explain the scenario well i posted that proc.
>
> My POC is reading if is it possible to write the complex stored proc(SP) @
> java side using JOOQ API. So that we can have a better control of debugging
> of the SPs.
>
> Yes I need the following specific items help from this group. I checked
> User Guide but not able to find these solutions.
>
> 1) Is there a way in JOOQ API to assign local variables in the select
> statement , if not what is the alternative?
> Ex : select @lclPeriodId = period_id from employ_calendar_period
> how to assign local variable @lclPeriodId directly ?
>
Local T-SQL variables make sense in a T-SQL context. When operating with
SQL Server through JDBC, there might not be much use in using local
variables. Of course, you could "map" the notion of such variables to Java
local variables, if that makes any sense in the context of your POC. In
that case, you would just fetch a value from a SELECT statement for later
reuse:
int lclPeriodId = ctx
.select(EMPLOY_CALENDAR_PERIOD.PERIOD_ID)
.from(EMPLOY_CALENDAR_PERIOD)
.where(...)
.fetchOne(EMPLOY_CALENDAR_PERIOD.PERIOD_ID);
You can then, of course, reuse that Java variable as a bind value in
subsequent SQL statements. Maybe you can also resort to using actual T-SQL
variables by using jOOQ's plain SQL capabilities:
http://www.jooq.org/doc/3.2/manual/sql-building/plain-sql/
Example:
ctx.select(DSL.field("@lclPeriodId = {0}",
EMPLOY_CALENDAR_PERIOD.PERIOD_ID.getDataType(),
EMPLOY_CALENDAR_PERIOD.PERIOD_ID))
.from(...)
.fetch(...)
2) As you know generally we use temporary tables in SPs, how to create a
> temporary tables in a SP using JOOQ API ?
> Can you give some example.
>
Again, in my opinion, a temporary table is a T-SQL object that makes sense
mostly in a T-SQL context. I'm not sure if T-SQL temporary tables can be
shared among subsequent JDBC statements. If so, then you might get lucky
again by using jOOQ's plain SQL capabilities:
http://www.jooq.org/doc/3.2/manual/sql-building/plain-sql/
Another option is to define CustomTables:
http://www.jooq.org/doc/3.2/manual/sql-building/queryparts/custom-queryparts/
This might allow you to select from such temporary tables without
surrendering the use of jOOQ's typesafety. Again, this would only make
sense if temporary tables can be shared among subsequent T-SQL statements
called through JDBC.
> 3) How to check the the value of field from table if it equals to some
> variable value.
> Ex : I have something like below
> int inEmpId = 5;
> ctx.select().from(RULE_EMP)
> .join(EMP_TYPE)
> .on(RULE_EMP.RULE_ID.equal(EMP_TYPE.RULE_PARAMETER_ID)
> .where(EMP_TYPE.RULE_ID = inEmpId ).and(EMP_TYPE.ORDER.equal(2));
>
> a) In the above snippet there is a compilation error in
> "*where(EMP_TYPE.RULE_ID
> = inEmpId )*"
> Error--* The method where(TableField<EmpTypeRecord,Integer>) is
> undefined for the type Condition*
> How to handle these type of conditions ?
>
You got it right in the other predicates, why not in this one? The SQL
equality comparison operator cannot be mapped to Java's assignment operator
by jOOQ. As any internal domain-specific language, jOOQ's syntax is limited
by the host language - Java.
So the SQL equality comparison operator maps to either Field.equal(), or
Field.eq(), whatever you prefer.
> b) And in the "*and(EMP_TYPE.ORDER.equal(2))*; "
> It is not a straight comparison and gives an error.
> Error-- *The method equal(Long) in the type Field<Long> is not
> applicable for the arguments *
> How to handle these kind of straight forward condition checks in
> JOOQ.
>
As the error message indicates, the column EMP_TYPE.ORDER is of type Long
(SQL BIGINT). You cannot compare a Long with an Integer/int value through
the jOOQ API. You will have to compre it with a Long value. Examples:
EMP_TYPE.ORDER.equal(2L)
EMP_TYPE.ORDER.equal((long) 2)
EMP_TYPE.ORDER.equal(Long.valueOf(2));
What you tried didn't work because since Java 5, autoboxing is applied
before generic type-checking. This means that your int literal cannot be
promoted to type long, *before* auto-boxing it to a wrapper type.
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/groups/opt_out.
