At 11:40 PM 26/04/2004 +0100, you wrote:

>Basically I am trying to get a grip of how IBObjects work so I can decide
>if its the tool for me.
>
>I am probably being stupid but I am trying to extract a field from a
>tib_cursor row and having problems getting the correct data returned.
>
>This is a snippet of code
>
>Var
> MyObjid as Int64;
>
>With tib_cursor1 do
> begin
> Sql.Clear;
> Sql.Add('Select objid from table_accounts where account_name = :AccName');

Do this ahead of time so you always have it there. Once it has been
prepared, it can stay prepared, which will make it much faster.

> Prepare;

if not prepared then prepare;

> {Params.}ParamByName('account_name').AsString:='MYACCOUNT';

Params is the default property so you don't need to name it.

> Execute;

No: execute is not used for selects. For ib_cursor selects use

First;

> If Active then { Also tried Rowselected>0 which works fine as a test }

Active isn't appropriate for ib_cursor since it is not a buffered dataset.

> begin
> FetchFirst; {or FetchAll or BufferFirst - Get data into the buffer }

Omit this. the call to First (see above) fetches the first row.

> MyObjid:=FieldByName('OBJID').AsInt64;

If AsInt64 doesn't work, use AsInteger or Value.

> { The objid returned is always zero which is incorrect }

Yup. Execute doesn't return anything.

>Basically all I am trying to do is read fields from the current row of the
>TIB_CURSOR. In this instance I know its a singleton but its essentially
>the active row I am after. I have looked though the examples for a demo of
>how this might be done but there are no examples I can find that show this
>and the help gives no examples either. It looks from the help as though
>this should work but I am obviously missing something. Probably blindingly
>obvious once someone points it out.

Yup. The essential thing about SQL is that Execute does stuff only on the
server, it doesn't return a result set. (The exception to this rule is the
"executable stored procedure", which will return an array if output
arguments were defined).

then, when you use ib_cursor with a select, remember that it's unbuffered,
so you get "one shot" at reading a row. First gets you the first
row; then Next gets you each row in turn until all are fetched. You can
buffer the rows yourself if you want to.

>In this instance I am just doing an existence check so I can warn the user
>if the account exists before I overwrite it. There are no doubt other ways
>to achieve the same ends but I need to know how to read data from fields
>to variables.

Yes, this isn't a very cool way to do this thing in a client/server
application. If you *must* do this existence check before posting, then
use something with less overhead: typically, an executable procedure that
runs in the same transaction context.

create procedure check_account (inputstring varchar(35))
returns (YesNo char)
as
begin
if (exists(select 1 from Account where AccountName = :inputstring))
then
YesNo = 'T';
else
YesNo = 'F';
end

Then, in the client, have a tib_dsql, e.g. sqCheckAccount, with the SQL
property
EXECUTE PROCEDURE CHECK_ACCOUNT(:INPUTSTRING)

function MyDM.AccountExists(inputstring: string): string;
with sqCheckAccount do
begin
if not prepared then prepare;
Params[0].AsString := MyVariable;
Execute;
Result := Fields[0].AsBoolean;
end;

at run-time:

if AccountExists(MyVariable) then
blah

cheers,
Helen