Hi Jeremy,
Yes, I had an issue that I could not use sequel API to get return value
from an oracle procedure. So resorted to going manual by using a jdbc
connection object directly.
After adding the explicit close for the preparecall, have no issues with
leaking db resources. Was my bad for not closing it directly.
Thanks again for your help as always
Dylan
On Sun, Jan 30, 2022, 18:39 Jeremy Evans <jeremyeva...@gmail.com> wrote:
>
> On Sun, Jan 30, 2022 at 1:47 AM Dylan Camilleri <dcam...@gmail.com> wrote:
>
>> Hi,
>>
>> Want to add some notes. Did some further reading on the issue. If a jdbc
>> callable statement is started on a jdbc connection object.
>>
>> Say: cs = Conn.prepareCall()
>>
>> Then it must be put into a begin / end block and under ensure part do
>> "cs.close"
>>
>> Begin
>>
>> cs = Conn.prepareCall("some procedure");
>>
>> cs.executeQuery
>>
>> Ensure
>>
>> cs.close
>>
>> End
>>
>> This makes sure that the cursor is released on oracle side.
>>
>> Tested this and have reduced the open_cursors parameter in oracle from
>> 1000 to 100 and it works fine.
>>
>
> Sequel only uses prepareCall in one place, and it does use a begin/ensure
> to try to ensure it gets closed, but maybe the logic can be improved:
>
> diff --git a/lib/sequel/adapters/jdbc.rb b/lib/sequel/adapters/jdbc.rb
> index adf474994..138990dfc 100644
> --- a/lib/sequel/adapters/jdbc.rb
> +++ b/lib/sequel/adapters/jdbc.rb
> @@ -188,12 +188,12 @@ module Sequel
> args = opts[:args] || []
> sql = "{call #{name}(#{args.map{'?'}.join(',')})}"
> synchronize(opts[:server]) do |conn|
> - cps = conn.prepareCall(sql)
> + begin
> + cps = conn.prepareCall(sql)
>
> - i = 0
> - args.each{|arg| set_ps_arg(cps, arg, i+=1)}
> + i = 0
> + args.each{|arg| set_ps_arg(cps, arg, i+=1)}
>
> - begin
> if defined?(yield)
> yield log_connection_yield(sql, conn){cps.executeQuery}
> else
> @@ -205,7 +205,7 @@ module Sequel
> rescue *DATABASE_ERROR_CLASSES => e
> raise_error(e)
> ensure
> - cps.close
> + cps.close if cps
> end
> end
> end
>
> Are you calling prepareCall manually?
>
> Thanks,
> Jeremy
>
> --
> You received this message because you are subscribed to the Google Groups
> "sequel-talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sequel-talk+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sequel-talk/CADGZSSe3k%2Bqq0W-GTivtOnCNCt7VqJ39MhY%2BZyj-Z1kJRHYDsg%40mail.gmail.com
> <https://groups.google.com/d/msgid/sequel-talk/CADGZSSe3k%2Bqq0W-GTivtOnCNCt7VqJ39MhY%2BZyj-Z1kJRHYDsg%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sequel-talk+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CAFQwEfuUVABUWqQRKDdjPhCHGa1r_R_Vb1CQS%2B0Q6Ug0CmTKZg%40mail.gmail.com.
