Regarding executing a stored proc using JOOQ

[Sha]

Hi Lukas,

  I am doing a simple POC using JOOQ for my project.

I have a scenario like below.

created a student table in SQLServer DB

inserted records 

write a simple stored proc , given a student_Id , which will return Student name.

Now using a Code Generator of JOOQ , i generated a code.

I wrote a simple Java class where me trying to execute , providing the in-paramenter.

as below

public static void main(String[] args) {

Getstudentnameinoutputvariable procedure = new Getstudentnameinoutputvariable();

// All IN and IN OUT parameters generate setters

procedure.setStudentid(1);

procedure.execute();

System.out.println("Result :" + procedure.getStudentname())

}

but when it executes procedure.execute(); throws a null pointer exception.

I dont know how to execute a Stored Proc (SP) from JOOQ java class.

I checked JOOQ user guide.

the sample reads as below

procedure.execute(configuration);

i dont understand where the instance of "configuration" came from ?

I my scenarios where can i get one?

Please provide me a complete sample to execute a stored proc from Java class using JOOQ.

Thank you.

~Sha

[Lukas Eder]

Hi Sha,

Configuration is a very central type in jOOQ. Much of it is explained in this section of the manual:

http://www.jooq.org/doc/3.2/manual/sql-building/dsl-context/

You can either rely on extracting the Configuration from a DSLContext object through

http://www.jooq.org/javadoc/latest/org/jooq/DSLContext.html#configuration()

Or, you create your own org.jooq.impl.DefaultConfiguration:

http://www.jooq.org/javadoc/latest/org/jooq/impl/DefaultConfiguration.html

Hope this helps,

Lukas

2014/1/16 Sha <shate...@gmail.com>

--
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/groups/opt_out.

[Sha]

Thanks a lot Lukas, Its working now.

I need one more help.

How to write a stored proc in a java code (class) using JOOQ and which can be compiled and run against a DB....i.e. executed aganist a DB.

Could you help me please!

thank you.

~Shyam

 

[Lukas Eder]

Hello Shyam,

I'm sorry, I don't understand this question.

1. If you want to write a stored procedure in Java, is this about H2? HSQLDB? PostgreSQL? Oracle?

2. Can you give an example of what you want to achieve?

3. Can you show where you're currently struggling?

Cheers

Lukas

2014/1/17 Sha <shate...@gmail.com>

--

[Sha]

Sorry for delay Lukas,

    I know its a tough task to do , but let me explain in it with a sample.

I have a stroed procedure as below. in SQL Servier2008 DB.

  

CREATE PROCEDURE emp_details_info

        (   

         @inMarker numeric(7,0),  

         @inRuleId numeric(7,0),  

         @inPeriodId numeric(7,0),  

         @inPK   varchar(50),  

 @inDebug varchar(12) = null  

        )  

AS  

    set nocount on  

 --------------------------------------------------- Variable Declaration --  

declare @lclPeriodId numeric(7,0)  

declare @id numeric(7,0), @typeId numeric(7,0)  

declare @lclCmd    varchar(255)  

declare @lclErrorNum     integer  

    set @lclErrorNum = 0  

declare @lclStatus    integer  

    set @lclStatus = 0  

 ------------------------------------------------------------------- Body --  

begin           

      

    select @lclPeriodId = period_id from employ_calendar_period  

    where calendar_name = 'MONTHLY'  

    and emp_num = (select min(emp_num) from employ_calendar_period where calendar_name = 'MONTHLY')  

  

    if (@inDebug is not null)  

    print 'Executing emp_details_info'   

  

    select @id = perspective_id, @typeId = type_id  

    from attendance_rules_for_times rpt  

    join emp_attendance_type rpty   

    on rpty.emp_attendance_type_id = rpt.attendance_rules_id  

    where   

    rpty.rule_id = @inRuleId and rpty.employee_order = 2  

      

 

      

    update employee_job_tree   

           set computed_salary = case when ( isnull(view1.emp_type,0) ) = 1.0 then  isnull(cdd3.value,0) else  isnull(fdd2.value,0) end  

    from employee_job_tree ejt  

          join emp_vwcatalog_detail empvwd1 on  

ejt.detail_id = empvwd1.detail_id  

join VWEMPLOYEE_LEAF view1 on  

vwcd1.emp_id = view1.emp_id  

and view1.emp_period_id = view1.emp_current_period_id  

and view1.emp_effective_period_id = view1.emp_current_period_id  

        left outer join employee_detail_data_b edd2 on  

ejt.emp_detail_id = edd2.detail_id   

and edd3.type_id = @typeId

        where ejt.employee_rule_marker = @inMarker  

  

     set @lclErrorNum = @@error    

     if ((@lclStatus != 0) or (@lclErrorNum != 0))    

     begin    

       set @lclCmd = 'ERROR: Failed to exec emp_details_info {' + dbo.Emp_Render(@empId) + ':' +'}'    

       exec Emp_Log_Message @lclErrorNum, 'ERROR', '__ENGINE', 'STEP1', @lclCmd  

       goto exception    

     end    

     

    return 0  

  

exception:  

    return 1  

end 

-- emp_details_info  

Me trying to write the above stored procedure in java code using JOOQ API, so that i can execute it using java compiler and interpreter.

But i have a tough time to write it in JOOQ API, getting the input parameters, joins , assigning local variable values in select statement.

Can you provide me help in order to conver it entirely to java code.

Thank you.

~Shyam

[Sha]

Any suggestion Lukas ?

~Shyam

[Lukas Eder]

Hi Shyam,

Well, that is certainly quite a bit of work, but you will have to start somewhere. I cannot do the work for you, but I and the user group can maybe help you with some specific problem you're having. What particular aspect of this SQL to jOOQ code transformation is troubling you?

Could you maybe also elaborate a little bit, why you want to move all of that code out of the database? I.e. what is the goal of your POC?

Best Regards,
Lukas

2014/1/20 Sha <shate...@gmail.com>

[Sha]

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 ?

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.

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 ? 

       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.

These errors halted my POC in JOOQ.

Need groups help here  in solving these. kindly help me.

Thank you.

~Shyam

[Lukas Eder]

Hello Shyam,

2014/1/21 Sha <shate...@gmail.com>

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

[Sha]

Thanks a lot Lukas,

   Its been very helpful. thank you for your support and time.

Let me try the temporary table creation stuff in SP.

If I need any advice/suggestion I would consult you.

All I am trying to do in POC , unit testing of stored procs is possible in JOOQ?

~Shyam

[Lukas Eder]

Hi Shyam,

2014/1/22 Sha <shate...@gmail.com>

Thanks a lot Lukas,

   Its been very helpful. thank you for your support and time.

Let me try the temporary table creation stuff in SP.

If I need any advice/suggestion I would consult you.

Sure, that's what we're here for.

 

All I am trying to do in POC , unit testing of stored procs is possible in JOOQ?

We tend to run extensive integration tests to check jOOQ for SQL generation and execution correctness. For this, we create a "standard" database on all supported databases and run thousands of queries against it. You should probably do something similar. If you google for "java database integration testing", you'll find a couple of useful resources.

If you really want to "unit test" your code, there are some ideas around jOOQ's MockDatabase here:

http://blog.jooq.org/2013/02/20/easy-mocking-of-your-database/

Cheers

Lukas

[Sha]

Sure Lukas,

 We need unit testing stored procedure from application(java) code. 

Let me go through the link which you sent and i would get back to you if any suggestion needed.

Thank you.

~Shyam