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
On Monday, January 20, 2014 11:22:54 PM UTC+5:30, Lukas Eder wrote:
>
> 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 <[email protected] <javascript:>>
>
>> 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
>>
>>
>>
>>
>>
>> On Saturday, January 18, 2014 1:36:05 AM UTC+5:30, Lukas Eder wrote:
>>
>>> 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 <[email protected]>
>>>
>>>> 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
>>>>
>>>>
>>>>>
>>>> --
>>>> 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.
>>>>
>>>
>>> --
>> 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/groups/opt_out.
>>
>
>
--
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.
