🚀 go-pugleaf

Isaac wrote:
> Hello,
> I use the following insert into in a stored procedure and the problem 
> is that no record is inserted at all !!
>
> I debugged the statement with different ways and it work but inside 
> the stored proc did not work
>
> insert into inventory_items (INV_CODE, ITEM_CODE, ITEM_NAME, CUR_QTY,
>      MIN_QTY, OB_QTY, OB_TOTAL_COST, UNIT_PRICE, UNIT_COST)
>    values (:p_to_inv_code, :v_max_item_code,
>      :v_item_name, :v_cur_qty, 0, 0, 0, 0, 0);
>
> here is the complete stored procedure
> =====================================================
> CREATE PROCEDURE PROC_TRANS_ITEMS (
>    p_old_qty float,
>    p_new_qty float,
>    p_from_inv_code integer,
>    p_to_inv_code integer,
>    p_item_code varchar(25))
> as
> declare variable v_cur_qty float;
> declare variable v_item_name varchar(150) character set win1252;
> declare variable v_max_item_code integer;
> begin
>  /* only qty can be changed */
>  /*if (:p_operation = 'update') then begin*/
>  /*if (:p_old_qty <> :p_new_qty) then begin*/
>
>  /* ---------- >>>>>>>> check old onventory first */
>  select cur_qty, item_name from inventory_items
>  where (inventory_items.inv_code = :p_from_inv_code)
>  and (inventory_items.item_code = :p_item_code)
>  into :v_cur_qty, :v_item_name;
>
>  if (v_cur_qty is null) then v_cur_qty = 0;
>
>  v_cur_qty = v_cur_qty - p_new_qty + p_old_qty;
>  if (v_cur_qty < 0) then exception EXCP_BAL_IS_UNDER_ZERO;
>
>  update inventory_items set CUR_QTY = :v_cur_qty
>  where (inventory_items.inv_code = :p_from_inv_code)
>  and (inventory_items.item_code = :p_item_code);
>
>  /* -------------->>>>>>>>> check new inventory */
>  v_cur_qty = -1;
>  select cur_qty from inventory_items
>  where (inventory_items.inv_code = :p_to_inv_code)
>  and (inventory_items.item_code = :p_item_code)
>  into :v_cur_qty;
>
>  if (v_cur_qty = -1) then begin
>    v_cur_qty = 0;
>    /* insert item */
>    select max(item_code) from inventory_items
>    where (inventory_items.inv_code = :p_to_inv_code)
>    and (inventory_items.item_code = :p_item_code)
>    into :v_max_item_code;
>
>    if (v_max_item_code is null) then
>      v_max_item_code = 1;
>    else
>      v_max_item_code = v_max_item_code + 1;
>
>    insert into inventory_items (INV_CODE, ITEM_CODE, ITEM_NAME, CUR_QTY,
>      MIN_QTY, OB_QTY, OB_TOTAL_COST, UNIT_PRICE, UNIT_COST)
>    values (:p_to_inv_code, :v_max_item_code,
>      :v_item_name, :v_cur_qty, 0, 0, 0, 0, 0);
>  end
>
>  v_cur_qty = v_cur_qty + p_new_qty - p_old_qty;
>  if (v_cur_qty < 0) then exception EXCP_BAL_IS_UNDER_ZERO;
>
>  update inventory_items set CUR_QTY = :v_cur_qty
>  where (inventory_items.inv_code = :p_to_inv_code)
>  and (inventory_items.item_code = :p_item_code);
>
>  /*end  old_qty <> new_qty */
>  /*end  (:p_operation = 'update') */
>
> end
> ===================================================
> Thanks
after further testing it seems a permission problem !!