Subject Jaybird + iBATIS + stored procedures
Author Fidel Viegas
Hello folks,

I am trying to call a stored procedure that inserts something into the
database and returns the primary key. Does anyone have an experience
setting this up?

I have tried everything and I am not getting any successful results.
Here is my procedure statement:

<procedure id="insert" parameterClass="warehouse" resultClass="java.lang.Long">
{call insertwarehouse(#warehouseName,jdbcType=VARCHAR,mode=IN#)}

When I do:

Warehouse warehouse = new Warehouse();
warehouse.setWarehouseName("Warehouse 2");
Object key = sqlMapper.insert("insert", warehouse);

this will insert the item, but will return null as object key.

If, on the other hand, I call the stored procedure as follows:

Object key = sqlMapper.insert("insert", warehouse);

I manage to get the object key, but the object is not saved into the
database. My stored procedure can be called as a selectable procedure
or as an executable procedure.

Here is the code:

set term ! ;
create or alter procedure insertwarehouse(warehousename varchar(50))
returns (warehouseid bigint) as
insert into warehouse (warehouseid, warehousename) values (next
value for warehouse_seq, :warehousename) returning warehouseid into
end !
set term ; !

Can someone give me a hint on how to return the value from iBATIS?

Thanks in advance,