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