Loading ...
Sorry, an error occurred while loading the content.

Jaybird + iBATIS + stored procedures

Expand Messages
  • 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
    Message 1 of 1 , Mar 1, 2008
    • 0 Attachment
      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#)}
      </procedure>

      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
      begin
      insert into warehouse (warehouseid, warehousename) values (next
      value for warehouse_seq, :warehousename) returning warehouseid into
      :warehouseid;
      suspend;
      end !
      set term ; !

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

      Thanks in advance,

      Fidel.
    Your message has been successfully submitted and would be delivered to recipients shortly.