Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

LastAutoIncValue in Bidirectional Select not working

5 posts in Delphi Last posting was on 2003-09-23 21:27:06.0Z
Scott Lynn Posted on 2003-09-08 04:50:32.0Z
From: Scott Lynn <scott@lynn.net>
Newsgroups: Advantage.Delphi
Subject: LastAutoIncValue in Bidirectional Select not working
Date: Mon, 8 Sep 2003 00:50:32 -0400
Message-ID: <MPG.19c5d4a2b7ddec18989680@DevZone.AdvantageDatabase.com>
Organization: Online Solutions, Inc.
X-Newsreader: MicroPlanet Gravity v2.60
NNTP-Posting-Host: 68.41.64.76
X-Trace: 7 Sep 2003 22:52:30 -0700, 68.41.64.76
Lines: 86
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!68.41.64.76
Xref: solutions.advantagedatabase.com Advantage.Delphi:13906
Article PK: 1107344

Greetings,
I have really enjoyed using the bidirectional select
for retrieving and editting record sets, but it doesn't
work well for inserts where the new record fails to
meet the search criteria in the Where clause of the Select,
and so the record disappears on Post. This part is logical,
but I would like to be able to detect the new record's
id from the autoinc field to display it instead if desired.

I discovered LastAutoIncVal through this newsgroup,
but it does not work in this case. When inside a SELECT,
it returns '0' no matter when I the query.Post method.
I created a simple project to verify this, and below
are the only three methods I used. 'mm' is a memo
field for outputting debugging information.

I have tried this with a local and remote connection,
and both return '0' no matter whether I call LastAutoIncVal
before or after the Post.

I have used this same function on SQL INSERT's and it
did work then. Why does it not work on SELECT's? This would
seem a significant limitation on the usefulness of
bidirectional SELECT's.

Any help is appreciated. I can post the entire
sample project if desired, but all the procedural
code is listed below.

Thanks,
Scott Lynn


-----------------------------------------------------

// This project just has a grid, dbnav, and an
// edit box on it as well as a query and insert button.
// There is a memo box on the bottom for outputing
// debugging information.

procedure TForm1.btQueryClick(Sender: TObject);
var
s : string;
begin
s := Uppercase(Trim(txtSearch.Text));
if Length(s) > 0 then begin
tbCustQuery.SQL.Text :=
'SELECT * FROM customers WHERE (lastname = :plastname)';
tbCustQuery.ParamByName('plastname').Value := s;
if tbCustQuery.Active then tbCustQuery.Close;
tbCustQuery.Prepare;
tbCustQuery.Open;
end;
end;

procedure TForm1.btInsertClick(Sender: TObject);
var
s : string;
begin
s := Uppercase(Trim(txtSearch.Text));
if Length(s) > 0 then begin
mm.Lines.Add(
'About to insert Lastname=' + s +
' on Lastname query of ' + LastQueryName);
tbCustQuery.Insert;
mm.Lines.Add(
'tbCustQuery.Insert Called, Custid is ' +
IntToStr(tbCustQuerycustid.AsInteger) +
', LastAutoIncVal is ' +
IntToStr(tbCustQuery.LastAutoIncVal));
tbCustQueryLastName.AsString := s;
tbCustQuery.Post;
mm.Lines.Add(
'tbCustQuery.Post Called, Custid is ' +
IntToStr(tbCustQuerycustid.AsInteger) + ',
LastAutoIncVal is ' +
IntToStr(tbCustQuery.LastAutoIncVal));
end;
end;

procedure TForm1.FormShow(Sender: TObject);
begin
txtSearch.Text := 'LYNN';
AdsConnection.Connect;
btQuery.Click;
end;


Scott Lynn Posted on 2003-09-08 04:57:27.0Z
From: Scott Lynn <scott@lynn.net>
Newsgroups: Advantage.Delphi
Subject: Re: LastAutoIncValue in Bidirectional Select not working
Date: Mon, 8 Sep 2003 00:57:27 -0400
Message-ID: <MPG.19c5d6412815fdb989681@DevZone.AdvantageDatabase.com>
References: <MPG.19c5d4a2b7ddec18989680@DevZone.AdvantageDatabase.com>
Organization: Online Solutions, Inc.
X-Newsreader: MicroPlanet Gravity v2.60
NNTP-Posting-Host: 68.41.64.76
X-Trace: 7 Sep 2003 22:59:24 -0700, 68.41.64.76
Lines: 25
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!68.41.64.76
Xref: solutions.advantagedatabase.com Advantage.Delphi:13907
Article PK: 1107343

Some additional notes that may be helpful:

- I am using Delphi 6 Pro with Update Pack 2
- I am using Advantage Server 6.2
- Here is the debugging output from the memo box:
(custid 10128 is the last record of the query,
and not the id of the new inserted record which
is correctly missing)

About to insert Lastname=LYNN2 on Lastname query of LYNN
tbCustQuery.Insert Called, Custid is 0, LastAutoIncVal is 0
tbCustQuery.Post Called, Custid is 10128, LastAutoIncVal is 0


In article <MPG.19c5d4a2b7ddec18989680@DevZone.AdvantageDatabase.com>,
scott@lynn.net says...

> Greetings,
> I have really enjoyed using the bidirectional select
> for retrieving and editting record sets, but it doesn't
> work well for inserts where the new record fails to
> meet the search criteria in the Where clause of the Select,
> and so the record disappears on Post. This part is logical,
> but I would like to be able to detect the new record's
> id from the autoinc field to display it instead if desired.
>


Scott Lynn Posted on 2003-09-23 14:59:03.0Z
From: Scott Lynn <scott@lynn.net>
Newsgroups: Advantage.Delphi
Subject: Re: LastAutoIncValue in Bidirectional Select not working
Date: Tue, 23 Sep 2003 10:59:03 -0400
Message-ID: <MPG.19da29c3e8973008989682@DevZone.AdvantageDatabase.com>
References: <MPG.19c5d4a2b7ddec18989680@DevZone.AdvantageDatabase.com> <MPG.19c5d6412815fdb989681@DevZone.AdvantageDatabase.com>
Organization: Online Solutions, Inc.
X-Newsreader: MicroPlanet Gravity v2.60
NNTP-Posting-Host: 192.168.192.21
X-Trace: 23 Sep 2003 09:00:56 -0700, 192.168.192.21
Lines: 76
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!192.168.192.21
Xref: solutions.advantagedatabase.com Advantage.Delphi:13994
Article PK: 1107430

I am sitting at the Advantage Training right now, which is excellent by
the way, and I now have a fix for this.

J.D. from Advantage saw my post and asked me about it. He checked
the source code and this is now fixed in version 7 of Advantage.
He also gave me the following code change for 6.2 to fix this:

Inside the TAdsQuery unit which for me is adstable.pas,
you need to goto the GetLastAutoInc routine.

Change it to something like this :

{*********************************************************************
* Module: TAdsQuery.GetLastAutoinc
* Input:
* Output: return the last autoinc value inserted with sql INSERT
statement
* Description:
*********************************************************************}
function TAdsQuery.GetLastAutoinc: Integer;
var
ulRetCode : UNSIGNED32 ;
ulLastAutoincVal : UNSIGNED32;
begin
{
// Original code before change to fix error
// on inserts in bidirectional SELECTS.
ulLastAutoincVal := 0;
ulRetCode := Ace.AdsGetLastAutoinc( mhStmt, @ulLastAutoincVal );
ACECheck( self, ulRetCode );
Result := ulLastAutoincVal;
}
// If there is a cursor, then get last autoinc from it, else
// use the statement handle to get the last autoinc.
ulLastAutoincVal := 0;

if (ActiveHandle <> INVALID_ACE_HANDLE) then
ulRetCode :=
Ace.AdsGetLastAutoinc( ActiveHandle, @ulLastAutoincVal )
else
ulRetCode :=
Ace.AdsGetLastAutoinc( mhStmt, @ulLastAutoincVal );
ACECheck( self, ulRetCode );
Result := ulLastAutoincVal;
end;

I have tested this, and it works.

In article <MPG.19c5d6412815fdb989681@DevZone.AdvantageDatabase.com>,
scott@lynn.net says...

> Some additional notes that may be helpful:
>
> - I am using Delphi 6 Pro with Update Pack 2
> - I am using Advantage Server 6.2
> - Here is the debugging output from the memo box:
> (custid 10128 is the last record of the query,
> and not the id of the new inserted record which
> is correctly missing)
>
> About to insert Lastname=LYNN2 on Lastname query of LYNN
> tbCustQuery.Insert Called, Custid is 0, LastAutoIncVal is 0
> tbCustQuery.Post Called, Custid is 10128, LastAutoIncVal is 0
>
>
> In article <MPG.19c5d4a2b7ddec18989680@DevZone.AdvantageDatabase.com>,
> scott@lynn.net says...
> > Greetings,
> > I have really enjoyed using the bidirectional select
> > for retrieving and editting record sets, but it doesn't
> > work well for inserts where the new record fails to
> > meet the search criteria in the Where clause of the Select,
> > and so the record disappears on Post. This part is logical,
> > but I would like to be able to detect the new record's
> > id from the autoinc field to display it instead if desired.
> >
>


Jeremy D. Mullin Posted on 2003-09-23 19:16:37.0Z
From: Jeremy D. Mullin <IHateSpamjeremym@extendsys.com>
Newsgroups: Advantage.Delphi
Subject: Re: LastAutoIncValue in Bidirectional Select not working
Date: Tue, 23 Sep 2003 13:16:37 -0600
Message-ID: <MPG.19da4a015391c664989b9a@solutions.advantagedatabase.com>
References: <MPG.19c5d4a2b7ddec18989680@DevZone.AdvantageDatabase.com> <MPG.19c5d6412815fdb989681@DevZone.AdvantageDatabase.com> <MPG.19da29c3e8973008989682@DevZone.AdvantageDatabase.com>
Organization: ESI
X-Newsreader: MicroPlanet Gravity v2.30
NNTP-Posting-Host: 198.60.232.45
X-Trace: 23 Sep 2003 13:15:23 -0700, 198.60.232.45
Lines: 60
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.60.232.45
Xref: solutions.advantagedatabase.com Advantage.Delphi:13996
Article PK: 1107431

Thanks Scott. I just had a slight correction. The fix is not in 7
(because we didn't find the bug in time), but it will be in 7.1. I'll
also create a knowledge base item on how to change the source to avoid
the problem in whatever version of Advantage people are using.

J.D. Mullin
Advantage R&D

In article <MPG.19da29c3e8973008989682@DevZone.AdvantageDatabase.com>,
scott@lynn.net says...

> I am sitting at the Advantage Training right now, which is excellent by
> the way, and I now have a fix for this.
>
> J.D. from Advantage saw my post and asked me about it. He checked
> the source code and this is now fixed in version 7 of Advantage.
> He also gave me the following code change for 6.2 to fix this:
>
> Inside the TAdsQuery unit which for me is adstable.pas,
> you need to goto the GetLastAutoInc routine.
>
> Change it to something like this :
>
> {*********************************************************************
> * Module: TAdsQuery.GetLastAutoinc
> * Input:
> * Output: return the last autoinc value inserted with sql INSERT
> statement
> * Description:
> *********************************************************************}
> function TAdsQuery.GetLastAutoinc: Integer;
> var
> ulRetCode : UNSIGNED32 ;
> ulLastAutoincVal : UNSIGNED32;
> begin
> {
> // Original code before change to fix error
> // on inserts in bidirectional SELECTS.
> ulLastAutoincVal := 0;
> ulRetCode := Ace.AdsGetLastAutoinc( mhStmt, @ulLastAutoincVal );
> ACECheck( self, ulRetCode );
> Result := ulLastAutoincVal;
> }
> // If there is a cursor, then get last autoinc from it, else
> // use the statement handle to get the last autoinc.
> ulLastAutoincVal := 0;
>
> if (ActiveHandle <> INVALID_ACE_HANDLE) then
> ulRetCode :=
> Ace.AdsGetLastAutoinc( ActiveHandle, @ulLastAutoincVal )
> else
> ulRetCode :=
> Ace.AdsGetLastAutoinc( mhStmt, @ulLastAutoincVal );
> ACECheck( self, ulRetCode );
> Result := ulLastAutoincVal;
> end;
>
> I have tested this, and it works.
>
> In article <MPG.19c5d6412815fdb989681@DevZone.AdvantageDatabase.com>,
> scott@lynn.net says...


Jeremy D. Mullin Posted on 2003-09-23 21:27:06.0Z
From: Jeremy D. Mullin <IHateSpamjeremym@extendsys.com>
Newsgroups: Advantage.Delphi
Subject: Re: LastAutoIncValue in Bidirectional Select not working
Date: Tue, 23 Sep 2003 15:27:06 -0600
Message-ID: <MPG.19da6895925dc535989b9c@solutions.advantagedatabase.com>
References: <MPG.19c5d4a2b7ddec18989680@DevZone.AdvantageDatabase.com> <MPG.19c5d6412815fdb989681@DevZone.AdvantageDatabase.com> <MPG.19da29c3e8973008989682@DevZone.AdvantageDatabase.com>
Organization: ESI
X-Newsreader: MicroPlanet Gravity v2.30
NNTP-Posting-Host: 198.60.232.45
X-Trace: 23 Sep 2003 15:25:56 -0700, 198.60.232.45
Lines: 34
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.60.232.45
Xref: solutions.advantagedatabase.com Advantage.Delphi:13999
Article PK: 1107437

I've made on slight change to the code posted above. This is the code
that will be incorporated into the product (the only change is using
Handle instead of ActiveHandle, so this will still work even if the
TAdsQuery component has an active index set).

J.D. Mullin
Advantage R&D

function TAdsQuery.GetLastAutoinc: Integer;
var
ulRetCode : UNSIGNED32 ;
ulLastAutoincVal : UNSIGNED32;
begin
ulLastAutoincVal := 0;

{*
* If there's a cursor, get the last autoinc from it, otherwise use
the
* statment handle. The cursor handle is necessary so they can get
the
* last autoinc value if the TAdsQuery.Insert method was used to add
* a record (because no INSERT statement was executed, just a table
* append).
*}
if ( Handle <> INVALID_ACE_HANDLE ) then
ulRetCode := Ace.AdsGetLastAutoinc( Handle, @ulLastAutoincVal )
else
ulRetCode := Ace.AdsGetLastAutoinc( mhStmt, @ulLastAutoincVal );

ACECheck( self, ulRetCode );

Result := ulLastAutoincVal;
end;