🚀 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 !!

"Isaac" <no@no.com> wrote in message news:484b16e4@newsgroups.borland.com...
>>    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


Is this a multiuser system? If so, this method of getting a new key for a 
new record is very dangerous. Use a generaotr for new keys.

-- 
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca) 

Wayne Niddery (TeamB) wrote:
> "Isaac" <no@no.com> wrote in message 
> news:484b16e4@newsgroups.borland.com...
>>>    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
>
>
> Is this a multiuser system? If so, this method of getting a new key 
> for a new record is very dangerous. Use a generaotr for new keys.
>
ok I will do it
thanks

What I should do in case I already have data in this table also this 
table has two primary key one of them get the value from the master 
table and other one should count from 1 for each different value from 
the other field

example
f1(key from master)  f2(count)
1                                 1
1                                 2
2                                 1
.
.
.


Thanks

Isaac wrote:

> What I should do in case I already have data in this table also this
> table has two primary key one of them get the value from the master
> table and other one should count from 1 for each different value from
> the other field
> 
> example
> f1(key from master)  f2(count)
> 1                                 1
> 1                                 2
> 2                                 1
> .
> .
> .
> 
> 
> Thanks

FYI, a table can only have one primary key but the primary key can
consist of more than one field. I assume the purpose of the COUNT field
is to preserve the order in which the records were added to the table.
If so, the COUNT field does not have to be 1,2,3. It can be any
ascending sequence of integer numbers which means that you can use a
generator. It is extremely difficult to number a group of records 1
through N in a multi-user environment. If you think you must have 1,2,3
etc. tell us why. Maybe the design can be changed to avoid that
requirement.

-- 
Bill Todd (TeamB)

Bill Todd [TeamB] wrote:
> FYI, a table can only have one primary key but the primary key can
> consist of more than one field. I assume the purpose of the COUNT field
> is to preserve the order in which the records were added to the table.
> If so, the COUNT field does not have to be 1,2,3. It can be any
> ascending sequence of integer numbers which means that you can use a
> generator. It is extremely difficult to number a group of records 1
> through N in a multi-user environment. If you think you must have 1,2,3
> etc. tell us why. Maybe the design can be changed to avoid that
> requirement.
>
>   

Hi Bill,
This table is a detail table hold items for each inventory (master 
table) the primary key consist of "inventory code" and "item code" for 
some reason customers need the item code to repeat for each inventory 
not to be unique all over the table also I already made all my logic 
based on those two fields , so I hope if there is a way to get unique 
value for field "item code" on multi user environment

Thanks

BTW: Is there a problem in the news group server many times I get post 
message refused and I dont know whom I should report this to

please also look at my procedure I got a weird problem the first update 
is executed normally but the second one never executed !! although I did 
the debug work with live numbers

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),
    p_to_item_code varchar(25))
as
declare variable v_cur_qty float;
declare variable v_item_name varchar(150) character set win1252;
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 = null;
  select cur_qty from inventory_items
  where (inventory_items.inv_code = :p_to_inv_code)
  and (inventory_items.item_code = :p_to_item_code)
  into :v_cur_qty;

  if (v_cur_qty is null) then exception EXCP_NO_ITEM_FOUND;

  /*if (v_cur_qty = -1) then begin
    v_cur_qty = 0;

    select max(item_code) from inventory_items
    where (inventory_items.inv_code = :p_to_inv_code)
    and (inventory_items.item_code = :p_to_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_to_item_code);

end

Isaac wrote:
> please also look at my procedure I got a weird problem the first 
> update is executed normally but the second one never executed !! 
> although I did the debug work with live numbers
>
> 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),
>    p_to_item_code varchar(25))
> as
> declare variable v_cur_qty float;
> declare variable v_item_name varchar(150) character set win1252;
> 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 = null;
>  select cur_qty from inventory_items
>  where (inventory_items.inv_code = :p_to_inv_code)
>  and (inventory_items.item_code = :p_to_item_code)
>  into :v_cur_qty;
>
>  if (v_cur_qty is null) then exception EXCP_NO_ITEM_FOUND;
>
>  /*if (v_cur_qty = -1) then begin
>    v_cur_qty = 0;
>
>    select max(item_code) from inventory_items
>    where (inventory_items.inv_code = :p_to_inv_code)
>    and (inventory_items.item_code = :p_to_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_to_item_code);
>
> end
Is this possible !!!!!!!!!!! I discovered that the problem because I did 
not made a proper foreign key and after I made one the second update 
worked !! which make me wonder does the update statement inside a stored 
procedure need a foreign key to work !!

another problem in another stored procedure start to give me wrong 
values I then start to give permission with grant option to every field 
inside every table and now the procedure give me correct values !!! is 
it a bug , this is very serious and will lead all my application to miss 
calculations please advice

Most developers have their apps log in as the database owner. The owner
has rights to everything. If you are using multi-ple InterBase  users
and roles you need to read the IB manuals. Users, privileges and roles
is much too large a topic to explain in a newsgroup message.


-- 
Bill Todd (TeamB)

An INSERT statement in a stored procedure works exactly like an INSERT
statement outside a stored procedure.


-- 
Bill Todd (TeamB)

Isaac wrote:

> Hi Bill,
> This table is a detail table hold items for each inventory (master
> table) the primary key consist of "inventory code" and "item code"
> for some reason customers need the item code to repeat for each
> inventory not to be unique all over the table also I already made all
> my logic based on those two fields , so I hope if there is a way to
> get unique value for field "item code" on multi user environment

For one inventory code  suppose you have records with the following
item codes.

1
2
3
4
5

I assume that if you insert a new record it would get item code 6.

If you delete the record with item code 3. What are the item codes for
the remaining records?

Once you answer this question I can tell you how to get the row number.

> 
> Thanks
> 
> BTW: Is there a problem in the news group server many times I get
> post message refused and I dont know whom I should report this to

AFAIK the newsgroups are working properly. I have not had any problem
posting and I have not seen compaints from anyone else. Are you
connecting to newsgroups.borland.com?



-- 
Bill Todd (TeamB)

Bill Todd [TeamB] wrote:
> For one inventory code  suppose you have records with the following
> item codes.
>
> 1
> 2
> 3
> 4
> 5
>
> I assume that if you insert a new record it would get item code 6.
>
> If you delete the record with item code 3. What are the item codes for
> the remaining records?
>
> Once you answer this question I can tell you how to get the row number.
>
>   
if I delete item code 3 the remaining will be as they are no change 
there will be gaps but item code count based on its inventory code not a 
unique number all over the table

> AFAIK the newsgroups are working properly. I have not had any problem
> posting and I have not seen compaints from anyone else. Are you
> connecting to newsgroups.borland.com?
>
>   

Yes , last week I did not able to post for 2 days , is the 
newsgroups.borland.com will remain the same after *Embarcadero acquired 
CodeGear ? *

Bill Todd [TeamB] wrote:
> Most developers have their apps log in as the database owner. The owner
> has rights to everything. If you are using multi-ple InterBase  users
> and roles you need to read the IB manuals. Users, privileges and roles
> is much too large a topic to explain in a newsgroup message.
>
>   
I use only one user as an admin and I created every thing with that user 
so I suppose that any thing created with that user will have permission 
to select, update, etc...

The question do I have to set permission for every field to be able to 
update that table ?

Thanks

Isaac wrote:

> if I delete item code 3 the remaining will be as they are no change
> there will be gaps but item code count based on its inventory code
> not a unique number all over the table

The the problem is getting the next number for the group when you
insert a new record in a way that ensures that two users cannot get the
same number. I suggest you lock the master record by setting one of its
fields to itself then get the last used number, add one to it, insert
the new detail record then commit the transaction. This will prevent
two users from inserting detail records at the same time.

> 
> 
> Yes , last week I did not able to post for 2 days , is the
> newsgroups.borland.com will remain the same after *Embarcadero
> acquired CodeGear ? *

The newsgroup domain will probably change in the future but it has not
changed yet.

-- 
Bill Todd (TeamB)

Isaac wrote:

> I use only one user as an admin and I created every thing with that
> user so I suppose that any thing created with that user will have
> permission to select, update, etc...

Is that user the database owner?

> 
> The question do I have to set permission for every field to be able
> to update that table ?

If the user is the database owner then the answer is no.

-- 
Bill Todd (TeamB)

Bill Todd [TeamB] wrote:
> The the problem is getting the next number for the group when you
> insert a new record in a way that ensures that two users cannot get the
> same number. I suggest you lock the master record by setting one of its
> fields to itself then get the last used number, add one to it, insert
> the new detail record then commit the transaction. This will prevent
> two users from inserting detail records at the same time.
>   
Ok, can you please tell me what you mean by setting one of the fields to 
itself

Thanks

I remember that I created the database with that user , however how I 
can make sure ?

"Isaac" <no@no.com> wrote in message 
news:484d14e3$1@newsgroups.borland.com...
>
> Yes , last week I did not able to post for 2 days , is the 
> newsgroups.borland.com will remain the same after *Embarcadero acquired 
> CodeGear ? *

Last week the groups were indeed down for the weekend, no one could connect 
again until Monday morning (PDT). They've been fine before and since.

-- 
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca) 

"Isaac" <no@no.com> wrote in message news:484d2e33@newsgroups.borland.com...
>>
> Ok, can you please tell me what you mean by setting one of the fields to 
> itself


update mytable set myid = myid where myid = x

Locking as suggested by Bill requires the application to do two things:
1. Proper use of transactions
2. Handling the exception when the above SQL fails due to another user 
having it locked

Pseudocode would be:

    start transaction // must be read committed
    try
        update mytable set myid = myid where myid = x
        select max(itemnum) from mytable where myid = x
        newitemnum := fields[itemnum] + 1
        insert into mytable (myid, itemnum, ...) values (x, newitemnum, ...)
        commit transaction
    except
        // decide how to handle other user having this record locked already
        // could just go back around and try again since it should not be 
long
        // or can rollback transaction and inform the user to try again 
later
    end

-- 
Wayne Niddery - TeamB (www.teamb.com)
Winwright, Inc. (www.winwright.ca) 

Isaac wrote:

> Ok, can you please tell me what you mean by setting one of the fields
> to itself

UPDATE ATABLE SET AFIELD = AFIELD

This does not change the value of the field but it counts as an update
so the record is locked until the transaction commits. By locking the
master record before you insert a detail record you make sure that no
other user can insert a detail record while you are inserting one
because no other user will be able to lock the master record.

Obviously you have to deal with the case where the UPDATE to the master
record fails because some other user has the record locked.

-- 
Bill Todd (TeamB)

Isaac wrote:

> I remember that I created the database with that user , however how I
> can make sure ?

select rdb$owner_name from rdb$relations where rdb$relation_name =
'RDB$DATABASE'; 

-- 
Bill Todd (TeamB)

Bill Todd [TeamB] wrote:
> select rdb$owner_name from rdb$relations where rdb$relation_name =
> 'RDB$DATABASE'; 
>
>   
Thanks, yes the owner is me.

Bill Todd [TeamB] wrote:
> UPDATE ATABLE SET AFIELD = AFIELD
> This does not change the value of the field but it counts as an update
> so the record is locked until the transaction commits. By locking the
> master record before you insert a detail record you make sure that no
> other user can insert a detail record while you are inserting one
> because no other user will be able to lock the master record.
>
> Obviously you have to deal with the case where the UPDATE to the master
> record fails because some other user has the record locked.
>
>   
Maaaaaaany Thanks very good hack

Wayne Niddery (TeamB) wrote:
> update mytable set myid = myid where myid = x
>
> Locking as suggested by Bill requires the application to do two things:
> 1. Proper use of transactions
> 2. Handling the exception when the above SQL fails due to another user 
> having it locked
>
> Pseudocode would be:
>
>    start transaction // must be read committed
>    try
>        update mytable set myid = myid where myid = x
>        select max(itemnum) from mytable where myid = x
>        newitemnum := fields[itemnum] + 1
>        insert into mytable (myid, itemnum, ...) values (x, newitemnum, 
> ...)
>        commit transaction
>    except
>        // decide how to handle other user having this record locked 
> already
>        // could just go back around and try again since it should not 
> be long
>        // or can rollback transaction and inform the user to try again 
> later
>    end
>
Thanks alot, appreciate your concern

Bill Todd [TeamB] wrote:

> Isaac wrote:
> 
> > Ok, can you please tell me what you mean by setting one of the
> > fields to itself
> 
> UPDATE ATABLE SET AFIELD = AFIELD

	Isaac, don't forget the WHERE clause here.

-- 
Craig Stuntz [TeamB] � Vertex Systems Corp. � Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Please read and follow Borland's rules for the user of their 
  server: http://support.borland.com/entry.jspa?externalID=293