Hi Helen

Thanks again and Hope you finally got some sleep !

I wasn't sure where to post my final solution, but as I used 2 IB_Stored Pocedures in the end I thought I would post it here.
As you suggested and I suspected using a TIB_Cursor was not the correct thing to do.
It all fell neatly into place using stored procedures.


I decided to use 2 stored procedures, 1 to reserve the pin from the barcode, 1 to convert it's status to 4 - sold.

The reserve Pin function is

{!! Given a Barcode and Clek ID try to find an available Pin then reserve it!!}
Function TOdin_Server.ReservePinFromBarcode(Barcode: String; Operator : Integer): Integer;
begin
with BarcodeReserve_IB_StoredProc do
begin
If prepared then UnPrepare;

ParamByName('BARCODE').AsString := Barcode;
ParamByName('OPERATOR').AsInteger := Operator;
Prepare;
ExecSQL;

Result := FieldByName('PIN_ID').AsInteger; // was Param
close;

end;
end;

------- The basic sql for this is
/*
Procedure:

Author : PJC
Date : 15/08/2003
Purpose : to find an available pin based upon product barcode and reserve it
Params
------
barcode : barcode string
*/
begin
/* code */
select product_id from product where BARCODE = :BARCODE
INTO :pin_id;
if (pin_id <> 0) then execute procedure P_FIND_PIN_AND_RESERVE :pin_id, :operator returning_values :pin_id;
end




The Make pin sold is.

Function TServer.SellPinFromID(ID : Integer) : Integer;
begin
with Sell_From_ID_IB_StoredProc do
begin
If prepared then UnPrepare;

Result := 0;

ParamByName('INDEX_ID').AsInteger := ID;
Prepare;
ExecSQL;
Memo1.Lines.add(' Result from sold is ' + IntToStr(FieldByName('RETURN_VAL').asInteger));
Result := FieldByName('RETURN_VAL').asInteger;
close;

end;
end;


----- The sql being

/*
Procedure:

Author : PJC
Date : 14/08/2003
Purpose : alter status of pin to sold
Params
------
<param> : INDEX_ID unique key-id to pin
*/
begin
/* code */
RETURN_VAL = 0;

IF (EXISTS ( SELECT ID FROM PINS WHERE ID = :INDEX_ID and STATUS <> 4 )) THEN
BEGIN
UPDATE PINS SET
STATUS = 4,
STATUS_CHANGED = 'NOW'

WHERE ID = :INDEX_ID;
RETURN_VAL = :INDEX_ID;

END




end


That's it It's a wrap !.

Rgds
Peter

----- Original Message -----
From: Helen Borrie
To: IBObjects@yahoogroups.com
Sent: Thursday, August 14, 2003 2:45 PM
Subject: Re: [IBO] TIB_Cursor edits


At 02:06 PM 14/08/2003 +0100, you wrote:
>Thanks ever so much Helen for the Help
>This is a very valuable learning experience for me and I do appreciate the
>help.
>
>I would like to see a server side solution, I have been looking at using a
>procedure.
>
>My SQL is pretty weak, but I think I can write a procedure to do the
>search and update etc, but I am not sure how to limit it to one pin,
>IBO_Cursor allows me to work with the first record, even though multiple
>records may have been fetched.

Thats about the worst reason I can think of - ESPECIALLY in a POS system,
where every bit of wire traffic is a customer drumming her fingers on the
counter, her kids bellowing in the trolley.

There are better ways to do what you want but - for future education - if
you are using Firebird, SELECT FIRST 1 * FROM...WHERE.. is the way to get
the first and only the first.

From here on we are pretty much off-topic for IBO so, if you want to pad
it out, take the requirements across to ib-support and ask there.

To wrap this one up here ---


>There are no parent tables or links.
>
>Application Description
>
>What actually happens is a whole load of tills in a store connect to my
>own applications server socket.
>
>These till can ask for information based on a scanned bar code about the
>product, such as value, pin number , receipt messages etc.
>
>If the till operator decides to go ahead with the transaction then

at this point you pass the barcode across to the server as input argument
to any one of a number of stored procedures. SP's can return a line of
output variables or a multi-line set.

In IBO, you wouldn't use a dataset component for this. You would use
either a TIB_DSQL or a TIB_Cursor whose SQL property would be 'EXECUTE
PROCEDURE DO_YOUR_STUFF(ThisBarcode)'. The IBO method is Execute(), not
Open() or First().

>the pin type associated with the barcode is retrieved from a product
>description table, this type is then used to scan a table of pin stock, as
>a separate operation, no links.

Calls another SP - still server-side. Client doesn't need anything yet.


>Each pin has a unique Key_ID which has a generator, the product_id is they
>type of pin, status 0 means it is available.
>The product_id is not unique in the pin stock table.

Another reason why your query in the initial question is not great as a
basis for update, as a general rule. The WHERE clause for updatable
searched queries should be able to be positioned to the exact record you
want to update. Your UpdateSQL would have actually updated multiple
records, potentially. I think the IBO update would be OK, as long as your
KeyLinks were absolutely primary.


>If a suitable pin in stock is available then the KeyID is returned, and
>that pin marked as reserved, status 1.
>Other fields such as till operator etc are updated at the same time.

Still on the server. Still nothing needed by the client.


>At this point the receipt with the pin is printed out, to complete the
>transaction the till sends back a sale complete message with the ID
>previously supplied

Actually, the ID will have been supplied to the SP via a server-based query
or procedure call.

>, the pin is then marked as sold.

This is out of sequence. The pin should be marked as sold before the
top-level procedure finally returns the output - in this case, the sale
complete message? The client might also receive other outputs from the
procedure - a returned set that it uses to print a till docket?

Do you see why this is wire-friendly? The client prepares (once only, not
every time) the execute procedure statement and sends the "filled"
statement across the wire. There is no more wire traffic until the
initially-called procedure returns a one-record output. Just a few bytes.
No datasets. All very quick. No time for Junior to pull the cap off that
stolen pot of Smarties.

Helen



Yahoo! Groups Sponsor
ADVERTISEMENT




___________________________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
___________________________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.


[Non-text portions of this message have been removed]