Hi Jeremy,
As per your suggestion. This works...
............
java_import "java.sql.Types"
java_import "oracle.jdbc.OracleTypes"
...............
@@db.synchronize do |conn|
cs = conn.prepareCall('{call SpInsertCustomer(?, ?,?,?,?,?)}')
cs.registerOutParameter(1, Types::VARCHAR)
cs.setString(2, 'Test Customer')
cs.setInt(3, 99)
cs.setInt(4, 18)
cs.setInt(5, 2)
cs.setString(6, 'XYZ10000')
cs.executeQuery
newCustomerID = cs.getString(1)
puts newCustomerID
end
On Wednesday, October 27, 2021 at 5:06:25 PM UTC+2 Dylan Camilleri wrote:
> Thank you Jeremy,
>
> I am getting this if I switch to :select
>
> Sequel::DatabaseError: Java::JavaSql::SQLException: Cannot perform fetch
> on a PLSQL statement: next
> raise_error at
> C:/app/dylanca/jruby-9.3.1.0/lib/ruby/gems/shared/gems/sequel-5.49.0/lib/sequel/database/misc.rb:443
> call_sproc at
> C:/app/dylanca/jruby-9.3.1.0/lib/ruby/gems/shared/gems/sequel-5.49.0/lib/sequel/adapters/jdbc.rb:208
>
> reference:
>
> result =
> @@db.dataset.call_sproc(:select,"SpInsertCustomer",out_param,"Test
> Customer",99,18,2,"123456",nil)
>
> Thanks,
>
> Dylan
>
> On Wednesday, October 27, 2021 at 4:28:36 PM UTC+2 Jeremy Evans wrote:
>
>> On Wed, Oct 27, 2021 at 5:43 AM Dylan Camilleri <[email protected]> wrote:
>>
>>> An update:
>>>
>>> This works and executes the procedure and "creates the customer"
>>>
>>> out_param = 0
>>> result =
>>> @@db.dataset.call_sproc(:update,"SpInsertCustomer",out_param,"Test
>>> Customer",99,18,2,"123456",nil)
>>>
>>> puts out_param >> 0 (no change)
>>> puts result >> nil (no return value)
>>>
>>> Basically the issue is I am not getting back the value from the executed
>>> procedure result:
>>>
>>> Oracle definition:
>>>
>>> PROCEDURE SpInsertCustomer(
>>> *pCustomerID* *OUT* NUMBER,
>>> pTitle VARCHAR2 ,
>>> pProduct IN NUMBER ,
>>> pType NUMBER ,
>>> pStatus NUMBER ,
>>> pCustomerNo VARCHAR2 ,
>>> pPassword VARCHAR2 DEFAULT NULL)
>>>
>>
>> out_param will always be 0. Integers are immutable in Ruby, and a method
>> call cannot change the value of a local variable of the caller.
>>
>> You could try switching update to select and see if the return value
>> includes the OUT column. If that doesn't work, you'll need to use the JDBC
>> API directly (see the JDBC documentation for how to do that):
>>
>> @@db.synchronize do |conn|
>> # conn is the JDBC connection object
>> end
>>
>> 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 [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/5e2b7bd7-7bdf-449a-a077-3223f13d7579n%40googlegroups.com.
