Hello,
The upcoming jOOQ 3.5 will now support retrieving OUT parameters as well as
result sets from stored procedures that produce such result sets. This has
been implemented as part of https://github.com/jOOQ/jOOQ/issues/3681. An
example can be seen here:
SQL:
CREATE PROCEDURE p_results_and_out_parameters(
@p_result_sets INT,
@p_count INT OUT
)ASBEGIN
IF @p_result_sets = 1 BEGIN
SELECT 1 a;
END
ELSE IF @p_result_sets = 2 BEGIN
SELECT 1 a;
SELECT 1 b UNION SELECT 2 b;
END
ELSE IF @p_result_sets = 3 BEGIN
SELECT 1 a;
SELECT 1 b UNION SELECT 2 b;
SELECT 1 c UNION SELECT 2 c UNION SELECT 3 c;
END;
SET @p_count = @p_result_sets;
END;
Java call:
PResultsAndOutParameters routine = new PResultsAndOutParameters();
routine.setPResultSets(2); // IN parameter
routine.execute(configuration);Integer pCount = routine.getPCount(); // OUT
parameterList<Result<Record>> results = routine.getResults(); // This will
return two results
Best Regards,
Lukas
Am Mittwoch, 19. März 2014 11:55:32 UTC+1 schrieb Lukas Eder:
>
> Hello,
>
> I'm sorry for the delay. Stored procedures with IN and OUT parameters can
> be called easily as documented here:
> http://www.jooq.org/doc/latest/manual/sql-execution/stored-procedures/
>
> However, I'm not sure what you mean by "getting a result set". Is your
> procedure supposed to return a cursor (in addition to the OUT parameters)?
> Can you post the generated GetAddress class?
>
> Best Regards,
> Lukas
>
>
> 2014-03-13 22:05 GMT+01:00 dsr <[email protected]>:
>
>> Hi,
>>
>> Can you please let me know how do I make a call from Java to fetch
>> records from a stored procedure that may be similar to the following:
>> Jooq Codegen does not generate pojos to fetch resultSet. also, generated
>> routines does not give resultset - routine.execute gives an int type and
>> not result set
>>
>> CREATE PROCEDURE [dbo].[GetAddress](
>> @orgId int,
>> @errorCode int OUTPUT
>> @numberOFemployees int OUTPUT
>> ) AS
>> Begin try
>> DECLARE @empId int, @msg nvarchar(1000), @error_message nvarchar(1000),
>> @error_line int, @error_severity int, @error_state int
>> SET @numberOFEmployees = select employeeCount from organization where
>> id=@orgId
>>
>> if @numberOFEmployees = 0
>> begin
>> ...
>> ...
>> .../do some updates to some other tables and then
>> set @errorCode=4014 // some org specific code end
>> else begin
>> ...
>> ...
>> more updates to another set of tables
>> ....
>> ...
>>
>> select emp.id, emp.name, address.number,address.street, state.name,
>> country.name
>> from organization join emp on emp.orgId=@orgId
>> join address on emp.id = address.empId
>> join state on address.stateId=state.id
>> join country on country.id=state.countryId
>> where organization.id =@orgId and emp.status ='active' and
>> address.type='permanent'
>>
>> end
>> END TRY
>> BEGIN CATCH
>> SELECT @msg = 'Exiting GetAddress on error: '
>> SELECT @error_message = ERROR_MESSAGE(), @error_line = ERROR_LINE(),
>> @error_severity = ERROR_SEVERITY(), @error_state = ERROR_STATE()
>> SET @msg += @error_message
>> print @msg
>>
>> --IF @@Trancount > 0
>> --ROLLBACK
>> INSERT INTO Log(OrgId,Severity,LogDetail,LogSource,LineNumber)
>> VALUES(@OrgId,@error_severity,@error_message,@msg,@error_line);
>>
>> RAISERROR(@error_message,@error_severity,@error_state) WITH NOWAIT
>> set @errorCode=5999
>> END CATCH
>>
>> --
>> 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.
