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.

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 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.

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.

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.

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 , the pin is then marked as sold.


The pin table is thus

CREATE TABLE PINS
(
ID INTEGER NOT NULL,
REMOVED INTEGER NOT NULL,
STATUS INTEGER NOT NULL,
STATUS_CHANGED TIMESTAMP DEFAULT 'NOW' NOT NULL,
EXPIRATION_DATE DATE NOT NULL,
RESERVATION_ID INTEGER NOT NULL,
CLERK_ID INTEGER,
PRODUCT_ID BIGINT NOT NULL,
VOUCHER_ID BIGINT,
PIN_LENGTH INTEGER,
PIN_SERIAL VARCHAR( 21) NOT NULL COLLATE NONE,
BUNDLE_NO VARCHAR( 33) COLLATE NONE,
PIN VARCHAR( 33) NOT NULL COLLATE NONE,
TRANSACTION_NO VARCHAR( 41) COLLATE NONE,
KEY_ID INTEGER NOT NULL
);
update rdb$relations set rdb$description = 'Pin Storage Table' where rdb$relation_name = 'PINS';
update rdb$relation_fields set rdb$description = 'Auto incrementing id' where rdb$relation_name = 'PINS' and rdb$field_name = 'ID';
update rdb$relation_fields set rdb$description = 'Pin Status , 1 = removed' where rdb$relation_name = 'PINS' and rdb$field_name = 'REMOVED';
update rdb$relation_fields set rdb$description = 'Pin Status, references table PIN_STATUS for textual descriptions' where rdb$relation_name = 'PINS' and rdb$field_name = 'STATUS';
update rdb$relation_fields set rdb$description = 'Time Date Status Last Changed' where rdb$relation_name = 'PINS' and rdb$field_name = 'STATUS_CHANGED';
update rdb$relation_fields set rdb$description = 'Pins may not be sold beyond expiration date' where rdb$relation_name = 'PINS' and rdb$field_name = 'PIN_LENGTH';
update rdb$relation_fields set rdb$description = 'Pin Number encyrypted' where rdb$relation_name = 'PINS' and rdb$field_name = 'PIN';
update rdb$relation_fields set rdb$description = 'This colun refeernces the decryption key used' where rdb$relation_name = 'PINS' and rdb$field_name = 'KEY_ID';

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



We have Pin_IB_Cursor, with the following SQL spec:

select * from PINS where PRODUCT_ID = :PRODUCT_ID AND STATUS = 0

Unless Product_ID is the primary key of PINS, Pin_IB_Cursor will potentially return multiple rows. (If it potentially returns multiple rows, we might have a structural problem... )

So let's be optimistic and assume Product_ID is the PK or, at least, a unique key; or, alternatively, that the first available row where PRODUCT_ID = :PRODUCT_ID AND STATUS = 0 will do just fine for the reservation. Your (unneeded) function appears to indicate so.

The function is not needed. As long as the Product_ID argument finds the correct PINS record, the (presumably) singleton row returned to the row buffer of Pin_IB_Cursor contains everything needed to perform the update directly from the row buffer (no UpdateSQL).

Make sure that the RequestLive property is true and the Keylinks is properly set to the PK of the PINS table. The following is all you need:

procedure Server.UpdatePinRecord(ProductID: integer);
begin
With Pin_IB_Cursor do
begin
Close;
// Prepare; No. IBO will prepare if needed - only once!!
ParamByName('PRODUCT_ID').AsInteger := ProductID;
First; // run query
// end of Pin_IB_Cursor - BUT only if record is singleton
if EOF then
memo1.Lines.add('Debug - zero result')
else
begin
Memo1.Lines.add('Attempting Edit :' + IntToStr(Result));
Edit;
FieldByName('CLERK_ID').AsInteger := 11;
FieldByName('STATUS').AsInteger := 1;
FieldByName('STATUS_CHANGED').AsDateTime := Now; // not safe
try
Post;
except
Memo1.Lines.add('Debug - Reservation Error');
// of course, you will call a proper exception handler here, won't you?
end;
end;
end;
end;

That's it.

But even that isn't the best way to do this particular operation, which doesn't appear to involve the user at all. If you want, in another post, I'll show you the client/server way to do this.

Now a bit of comment about what you attempted to before and why it wasn't going to work.

Here is the statement that you used to search for that one row in the PINS table:

select * from PINS where PRODUCT_ID = :PRODUCT_ID AND STATUS = 0

You wrongly thought that you needed an UpdateSQL for this. You didn't, because this is a simple SQL spec, no joins. In these conditions, all you need is RequestLive true and correct KeyLinks. IBO does the rest for you by magic.

*IF* this had been a joined set, you would have needed UpdateSQL, so it's worth pointing out why yours wouldn't have worked.

It was:

UPDATE PINS
SET
STATUS = 1 ,
STATUS_CHANGED = 'NOW' ,
CLERK_ID = :CLERK_ID
WHERE ID = :ID

You got an EIB_StatementError saying that the field CLERK_ID is not found. That's because you tried to assign parameters to the Pin_IB_Cursor statement that did not exist in the Pin_IB_Cursor statement. This UpdateSQL would have *nearly* worked if you had just left it alone, put the Pin_IB_Cursor into Edit mode, assigned the FieldByName values to the three fields in Pin_IB_Cursor and then called Post. IBO would then pick up the parameter values for the UpdateSQL query and create the required update statement (as indeed it does whenever it posts after an Edit). It's all done with smoke and mirrors.

But there was one more devilishly subtle way in which this updateSQL would have fallen over if you are in dialect 3. If you use the context variable 'NOW' in d3, you have to cast it - otherwise it is taken to be a string and you get a type error.

SET
...
STATUS_CHANGED = CAST('NOW' AS TIMESTAMP); (or use CURRENT_TIMESTAMP)

In the workthrough above, because we're using ParamByName, I've assigned Delphi's Now to the STATUS_CHANGED field. This is NBG, as it gets client-local time, not what you want.

The proper way to get an accurate timestamp, regardless of what updates the PINS record, is via a BeforeUpdate trigger.

create trigger bu_pins for PINS
active before update position 0
as
begin
new.STATUS_CHANGED = CAST('NOW' AS TIMESTAMP);
end

'Ways, that'll do for now.

cheers,
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]