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