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.

Record numbering and deleted records

7 posts in Linux Last posting was on 2007-01-14 03:32:11.0Z
Chris Chiesa Posted on 2006-12-22 11:26:58.0Z
From: Chris Chiesa <xet001@yahoo.com>
Subject: Record numbering and deleted records
Newsgroups: advantage.Linux
Reply-To: xet001@yahoo.com
Mail-Copies-To: cchiesa1@rochester.rr.com
Date: Fri, 22 Dec 2006 06:26:58 -0500
Lines: 67
Organization: Spherion for IEC
User-Agent: KNode/0.7.7
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
NNTP-Posting-Host: 72.226.213.15
Message-ID: <458b502f@solutions.advantagedatabase.com>
X-Trace: 21 Dec 2006 20:25:35 -0700, 72.226.213.15
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!72.226.213.15
Xref: solutions.advantagedatabase.com Advantage.Linux:996
Article PK: 1130538

Hi all,

I want to execute a SELECT statement and then be able to randomly access
records in the resulting recordset. The only ways I've found to "move
around in" a recordset are AdsSeek() and AdsGotoRecord(). (If there are
others, please clue me in.) These worked just fine in my initial
SELECT-only tests, but after I got to working with DELETE statements I ran
into trouble.

My desired access paradigm requires the ability to position to "the K'th
NON-DELETED record" in the recordset, but it appears that AdsGotoRecord()
positions to "the K'th 'absolute' record." That is, I want the recordset
returned from SELECT to _OMIT_ deleted records, at least as far as moving
around with AdsGotoRecord() is concerned:

Rec # Content
1 Jim
2 Joe
John (DELETED)
Bill (DELETED)
3 Paul
4 Sam

so that my API presents only the NON-DELETED records AS THOUGH they were a
contiguous set.

ACE, on the other hand, evidently includes deleted records in the recordset
and numbers them like this:

Rec # Content
1 Jim
2 Joe
3 John (DELETED)
4 Bill (DELETED)
5 Paul
6 Sam

So what I want to think of as the "fourth" record is now at position 6, with
no good way to "know that in advance." It seems that instead of
positioning right to the record I want, I must always go back to Record 1
and use AdsSeek() to move, one record at a time, through the recordset,
counting records as I go and explicitly skipping those that, upon arrival,
turn out to have been deleted. Is this really the only way to do it? If
so, what's the point of ACE's even bothering to provide AdsGotoRecord()?

One alternative I'm sure someone is about to suggest, would be to "read all
the records from the database into memory, omitting the deleted records as
you go, and then delivering records from the in-memory copy." That's fine
except for a couple of things. First, it's wasteful of memory -- to the
point where, with a couple of million records in a table it might fail
altogether. Second, it decouples the recordset from the original ACE
cursor, so that it is now very difficult to UPDATE records in-place.

Another solution would be to go through the entire recordset right after the
initial SELECT, and construct a map from "my" record-numbering scheme to
ACE's -- but again, with millions of records in a table that's not going to
be very efficient.

This can't be an unusual thing for a person to want to do, so I have to hope
that there's some setting or option in the ACE API that I'm overlooking
that makes this happen automatically, down inside ACE itself. Anybody...?

Thanks,

Chris


Edgar Sherman Posted on 2006-12-22 18:29:42.0Z
Date: Fri, 22 Dec 2006 11:29:42 -0700
From: Edgar Sherman <no@email.com>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
Newsgroups: advantage.Linux
Subject: Re: Record numbering and deleted records
References: <458b502f@solutions.advantagedatabase.com>
In-Reply-To: <458b502f@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 10.24.34.110
Message-ID: <458c231a@solutions.advantagedatabase.com>
X-Trace: 22 Dec 2006 11:25:30 -0700, 10.24.34.110
Lines: 79
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.34.110
Xref: solutions.advantagedatabase.com Advantage.Linux:997
Article PK: 1130540

One option is to use the ACE function AdsShowDeleted and pass the
parameter of FALSE. This will filter out the deleted records, and then
you can use AdsSeek().

Note, AdsGotoRecord() does not respect this setting.

Help File for AdsShowDeleted
http://devzone.advantagedatabase.com/dz/webhelp/Advantage8.1/mergedProjects/ace/ace.htm#api1/adsshowdeleted.htm

Edgar Sherman
Advantage Support

Chris Chiesa wrote:
> Hi all,
>
> I want to execute a SELECT statement and then be able to randomly access
> records in the resulting recordset. The only ways I've found to "move
> around in" a recordset are AdsSeek() and AdsGotoRecord(). (If there are
> others, please clue me in.) These worked just fine in my initial
> SELECT-only tests, but after I got to working with DELETE statements I ran
> into trouble.
>
> My desired access paradigm requires the ability to position to "the K'th
> NON-DELETED record" in the recordset, but it appears that AdsGotoRecord()
> positions to "the K'th 'absolute' record." That is, I want the recordset
> returned from SELECT to _OMIT_ deleted records, at least as far as moving
> around with AdsGotoRecord() is concerned:
>
> Rec # Content
> 1 Jim
> 2 Joe
> John (DELETED)
> Bill (DELETED)
> 3 Paul
> 4 Sam
>
> so that my API presents only the NON-DELETED records AS THOUGH they were a
> contiguous set.
>
> ACE, on the other hand, evidently includes deleted records in the recordset
> and numbers them like this:
>
> Rec # Content
> 1 Jim
> 2 Joe
> 3 John (DELETED)
> 4 Bill (DELETED)
> 5 Paul
> 6 Sam
>
> So what I want to think of as the "fourth" record is now at position 6, with
> no good way to "know that in advance." It seems that instead of
> positioning right to the record I want, I must always go back to Record 1
> and use AdsSeek() to move, one record at a time, through the recordset,
> counting records as I go and explicitly skipping those that, upon arrival,
> turn out to have been deleted. Is this really the only way to do it? If
> so, what's the point of ACE's even bothering to provide AdsGotoRecord()?
>
> One alternative I'm sure someone is about to suggest, would be to "read all
> the records from the database into memory, omitting the deleted records as
> you go, and then delivering records from the in-memory copy." That's fine
> except for a couple of things. First, it's wasteful of memory -- to the
> point where, with a couple of million records in a table it might fail
> altogether. Second, it decouples the recordset from the original ACE
> cursor, so that it is now very difficult to UPDATE records in-place.
>
> Another solution would be to go through the entire recordset right after the
> initial SELECT, and construct a map from "my" record-numbering scheme to
> ACE's -- but again, with millions of records in a table that's not going to
> be very efficient.
>
> This can't be an unusual thing for a person to want to do, so I have to hope
> that there's some setting or option in the ACE API that I'm overlooking
> that makes this happen automatically, down inside ACE itself. Anybody...?
>
> Thanks,
>
> Chris
>


Chris Chiesa Posted on 2006-12-23 14:13:50.0Z
From: Chris Chiesa <xet001@yahoo.com>
Subject: Re: Record numbering and deleted records
Newsgroups: advantage.Linux
Reply-To: xet001@yahoo.com
Mail-Copies-To: cchiesa1@rochester.rr.com
Date: Sat, 23 Dec 2006 09:13:50 -0500
References: <458b502f@solutions.advantagedatabase.com> <458c231a@solutions.advantagedatabase.com>
Lines: 21
Organization: Spherion for IEC
User-Agent: KNode/0.7.7
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
NNTP-Posting-Host: 72.226.213.15
Message-ID: <458ca9dc@solutions.advantagedatabase.com>
X-Trace: 22 Dec 2006 21:00:28 -0700, 72.226.213.15
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!72.226.213.15
Xref: solutions.advantagedatabase.com Advantage.Linux:1000
Article PK: 1130543


Edgar Sherman wrote:

> One option is to use the ACE function AdsShowDeleted and pass the
> parameter of FALSE. This will filter out the deleted records, and then
> you can use AdsSeek().

Thanks Ed.

> Note, AdsGotoRecord() does not respect this setting.

How unfortunate. That was precisely the API I was hoping to make work.
Darn.

> Help File for AdsShowDeleted
>
http://devzone.advantagedatabase.com/dz/webhelp/Advantage8.1/mergedProjects/ace/ace.htm#api1/adsshowdeleted.htm

That doesn't actually work for me. Takes me to the Advantage Client Engine
page.

Chris


Mark Wilkins Posted on 2006-12-22 18:38:15.0Z
From: "Mark Wilkins" <mark@no.email>
Newsgroups: advantage.Linux
References: <458b502f@solutions.advantagedatabase.com>
Subject: Re: Record numbering and deleted records
Date: Fri, 22 Dec 2006 11:38:15 -0700
Lines: 77
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
NNTP-Posting-Host: 10.24.38.228
Message-ID: <458c2518@solutions.advantagedatabase.com>
X-Trace: 22 Dec 2006 11:34:00 -0700, 10.24.38.228
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.228
Xref: solutions.advantagedatabase.com Advantage.Linux:998
Article PK: 1130541

Hi Chris,

What table type are you using? ADT or DBF? There are some differences in
deleted record handling between those formats. In both cases, deleted
records have a physical position in the file. But with DBFs, the visibility
of the deleted records is affected by the AdsShowDeleted() API. With ADTs,
deleted records are not visible, and the deleted records are reused when new
records are appended.

Warning: Bad newsgroup etiquette with redesign comments coming next.

In general, it is not a good idea to base application logic on physical
record position. If you can avoid doing that (maybe use a unique ID
instead?), it will probably make some tasks in the future simpler. It is
true that the file structure supports the concept of physical record numbers
and the AdsGotoRecord allows very quick access to those records, but
conceptually it is better to think of that API as being similar to
AdsGetBookmark/AdsGotoBookmark. You can save a position and return to it.

If the records can be deleted and appended "often" in the table, then I am
not aware of any data structure can keep the type of information necessary
for this type of operation. Basically you are needing an index of the
logical record numbers of non-deleted records. I might be missing something
obvious, but if such a structure exists, I suspect it is rather complex.
The idea of an index of logical record numbers is simple enough, but
maintaining it would be very expensive. For example, if I delete the first
record in a million record table, then the index keys of all the remaining
999,999 records would have to be updated (extremely expensive).
Alternatively, some kind of structure that maintains information about
deleted records and their positions might help, but it would have to answer
complex questions. To go to logical record 100, I need to know how many
deleted records are in the first 100 physical records, offset by that
amount, and repeat until it converges. For example, if there are 30 deleted
records in the first 100, then the new physical target is record 130, so
then it is necessary to know the number of deleted records in the first 130,
etc.

Having said that ... Another common movement API is AdsSkip(). That one
might be useful for you since it will skip over deleted records
automatically (depending on the AdsShowDeleted setting and/or table type).
For example, if you are on record 1 in your example below and call
AdsSkip( hTable, 2), it will end up on the third non-deleted record.
However, this is not the perfect solution for the million record table. It
might be fast enough if you are using Advantage Database Server (all the
processing for that would happen on the server), but it still must read
every record between the current position and the target. For example, if
the table is positioned on record 1 and you call AdsSkip( hTable, 1000000 ),
the server will have to read at least 999,999 records (more if there are
deleted ones).

Probably more words and less information than you were hoping for :)

Mark Wilkins
Advantage R&D

"Chris Chiesa" <xet001@yahoo.com> wrote in message
news:458b502f@solutions.advantagedatabase.com...
>
> Hi all,
>
> I want to execute a SELECT statement and then be able to randomly access
> records in the resulting recordset. The only ways I've found to "move
> around in" a recordset are AdsSeek() and AdsGotoRecord(). (If there are
> others, please clue me in.) These worked just fine in my initial
> SELECT-only tests, but after I got to working with DELETE statements I ran
> into trouble.
>
> My desired access paradigm requires the ability to position to "the K'th
> NON-DELETED record" in the recordset, but it appears that AdsGotoRecord()
> positions to "the K'th 'absolute' record." That is, I want the recordset
> returned from SELECT to _OMIT_ deleted records, at least as far as moving
> around with AdsGotoRecord() is concerned:
>


Chris Chiesa Posted on 2006-12-23 14:12:17.0Z
From: Chris Chiesa <xet001@yahoo.com>
Subject: Re: Record numbering and deleted records
Newsgroups: advantage.Linux
Reply-To: xet001@yahoo.com
Mail-Copies-To: cchiesa1@rochester.rr.com
Date: Sat, 23 Dec 2006 09:12:17 -0500
References: <458b502f@solutions.advantagedatabase.com> <458c2518@solutions.advantagedatabase.com>
Lines: 156
Organization: Spherion for IEC
User-Agent: KNode/0.7.7
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
NNTP-Posting-Host: 72.226.213.15
Message-ID: <458ca982@solutions.advantagedatabase.com>
X-Trace: 22 Dec 2006 20:58:58 -0700, 72.226.213.15
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!72.226.213.15
Xref: solutions.advantagedatabase.com Advantage.Linux:999
Article PK: 1130542


Mark Wilkins wrote:

> What table type are you using? ADT or DBF?

In my local tests, I'm pretty sure it's ADT. I created a table the second I
was able to get even the barest Advantage Data Architect functionality
working... (I'll get to my handful of Data Architect issues in another
post sometime...)

> In both cases, deleted
> records have a physical position in the file.

I realize that in terms of underlying implementation this is probably the
simplest thing to do, but--can you tell me what practical purpose is served
by having deleted records show up in the recordset returned by a SELECT?
(I haven't managed to notice whether those records' CONTENT is still
present for perusal; I would have expected them to be blanked/nulled out at
deletion time, but given that they can apparently be "recalled," I'm
guessing that "it ain't necessarily so." Interesting.

> But with DBFs, the visibility
> of the deleted records is affected by the AdsShowDeleted() API.

Thanks. I had managed to overlook that function, what with the lack of a
coordinated overview of How Things Work. Looks like that has to come from
trial-and-error over time, turning into "long experience." It'll be one
week tomorrow since I first heard of Advantage Database.

> In general, it is not a good idea to base application logic on physical
> record position. If you can avoid doing that (maybe use a unique ID
> instead?),

In this case I am working with already-extant tables which do not contain
unique IDs, and am not in a position to add them. I am also trying to
implement/reproduce a set of C++ classes giving a general-purpose set of
select/insert/update/delete abstractions, to which I became accustomed on a
previous job. Since first posting, however, I have recalled that THAT
implementation does precisely what I spoke against in my previous post
here: buffers the recordset in memory and "retrieves" records from there
when asked. I have no idea whether the underlying database in that
environment returns deleted records or not, because they could be getting
filtered out somewhere "under the hood" for all I know. (Even the use of a
vendor-supplied SQL command-line utility doesn't answer the question,
because THAT TOO could be filtering out deleted records below the level of
user awareness.) The STRONG IMPRESSION, however, is that "deleted records
go away immediately and never appear again," which is exactly the way I
feel it ought to work, no offense intended.

Bottom line, I'm probably overthinking the issue and can just go ahead and
buffer records in memory; my working recordsets are PROBABLY going to be
"small," I'm probably going to process them sequentially rather than
needing random access, etc.--but it would sure be nice to be able to leave
behind for my client a general-purpose access facility that were a bit more
bulletproof against "commonplace" situations. Guess I'll just have to
refresh my recollection of what exceptions C++ throws when one runs out of
memory, and try to code around all this as best I can.

> It is
> true that the file structure supports the concept of physical record
> numbers and the AdsGotoRecord allows very quick access to those records,
> but conceptually it is better to think of that API as being similar to
> AdsGetBookmark/AdsGotoBookmark. You can save a position and return to it.

First, I don't know a thing about AdsGetBookmark and AdsGotoBookmark. I've
seen them listed in the ACE API Reference index, but they haven't caught my
eye because, well, I haven't yet found an overview that answers the
fundamental prerequisite question of "what the heck IS a 'bookmark,' in the
first place?" Maybe I'm just really poor at reading documentation, but it
seems that one must "derive" the nature of fundamental Ace concepts by
trial-and-error and eventually develop working knowledge through long
experience. Am I missing something obvious?

Second, "saving a position" and being able to "return to it" requires that I
first VISIT every position in order to save it. Forgive me, but that
sounds even more expensive than either building my own index for
AdsGotoRecord OR using AdsSeek!

> If the records can be deleted and appended "often" in the table, then I am
> not aware of any data structure can keep the type of information necessary
> for this type of operation.

I've been hoping to foist off most of the complexity onto the SQL engine:

SELECT * FROM tablename WHERE [...criteria...];

(process data, figure out what needs updated and/or deleted)

UPDATE tablename SET fieldname = value WHERE [...criteria...];
or
DELETE FROM tablename WHERE [...critiera...];

(repeat)

> I might be missing
> something obvious, but if such a structure exists, I suspect it is rather
> complex.

I wouldn't think it would be too bad. If the recordset came back from Ace
looking like this:

Rec # Firstname
1 Joe
2 John
3 Bill
4 <deleted>
5 <deleted>
6 Mark
7 Chris
8 <deleted>
9 Paul
10 Sam

and I wanted to access just the non-deleted records (1-3, 6, 7, 9, and 10)
as a "logically contiguous" pseudo-recordset, I would probably just
populate an STL map like so:

map[1] = 1
map[2] = 2
map[3] = 3
map[4] = 6
map[5] = 7
map[6] = 9
map[7] = 10

and instead of coding e.g.

AdsGotoRecord(recnum)

I would code

AdsGotoRecord(map[recnum]);

Not complicated at all--unless _I_ am now missing something.

> The idea of an index of logical record numbers is simple enough, but
> maintaining it would be very expensive. [etc...]

Algorithms would be pretty straightforward but would scale linearly with the
total number of records and become inefficient for "large" recordsets.

> Having said that ... Another common movement API is AdsSkip(). That one
> might be useful for you since it will skip over deleted records
> automatically [ but ] it still must read every record between the current
> position and the target.

The only question remaining seems to be that of where the "read,
test-for-deletedness, ignore-if-deleted" code resides: in AdsSkip or in my
application code.

> Probably more words and less information than you were hoping for :)

Not too bad. You basically just went into a lot of detail that I omitted
from my original posting for the sake of brevity, and added a few valuable
bits-and-pieces that I hadn't had before. I'm glad you did.

Chris


Mark Wilkins Posted on 2007-01-02 17:43:23.0Z
From: "Mark Wilkins" <mark@no.email>
Newsgroups: advantage.Linux
References: <458b502f@solutions.advantagedatabase.com> <458c2518@solutions.advantagedatabase.com> <458ca982@solutions.advantagedatabase.com>
Subject: Re: Record numbering and deleted records
Date: Tue, 2 Jan 2007 10:43:23 -0700
Lines: 148
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3028
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3028
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: 10.24.38.228
Message-ID: <459a98ba@solutions.advantagedatabase.com>
X-Trace: 2 Jan 2007 10:39:06 -0700, 10.24.38.228
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.228
Xref: solutions.advantagedatabase.com Advantage.Linux:1003
Article PK: 1130546


"Chris Chiesa" <xet001@yahoo.com> wrote in message
news:458ca982@solutions.advantagedatabase.com...
> Mark Wilkins wrote:
>
>
> I realize that in terms of underlying implementation this is probably the
> simplest thing to do, but--can you tell me what practical purpose is
> served
> by having deleted records show up in the recordset returned by a SELECT?
> (I haven't managed to notice whether those records' CONTENT is still
> present for perusal; I would have expected them to be blanked/nulled out
> at
> deletion time, but given that they can apparently be "recalled," I'm
> guessing that "it ain't necessarily so." Interesting.

If you are using ADTs, the deleted records should not be showing up in the
result set. The ability to recall a deleted record only applies to DBF
table types. That is the way DBFs are "defined".

It is true that if the result is a live cursor, then you can use
AdsGotoRecord to position on a deleted record. This is because a live
cursor is simply a filtered view of the actual physical table (for
efficiency/speed reasons). For example, if you run something like "SELECT *
from sometable where ID=1", then it is possible to use AdsGotoRecord to go
to records that are not in the result set. That is simply the way a "goto"
works. In reality, AdsGotoRecord is not a good fit with SQL concepts. An
SQL result is a relation, which is an unordered set of rows. In the pure
SQL sense, it is legal for "SELECT * FROM sometable" to produce the same
relation in two different orders. In fact, the following contrived example
does just this if you are using ADTs:

User 1 runs this:
create table test (i integer);
insert into test values (1);
insert into test values (2);
select * from test;

User 2 runs this:
delete from test;
insert into test values (1);
insert into test values (2);

User 1 runs this:
select * from test;

The second SELECT result produces the same relation, but the rows are
reversed in order.

If it is absolutely necessary to get a result set with which it is
impossible to have deleted records, you can force a static result set with a
query such as the following:
SELECT {static} * from sometable;

That will produce a result set that is a copy of the original data and will
not have any deleted records (or any records that were filtered out due to a
WHERE clause if provided).

>
>> It is
>> true that the file structure supports the concept of physical record
>> numbers and the AdsGotoRecord allows very quick access to those records,
>> but conceptually it is better to think of that API as being similar to
>> AdsGetBookmark/AdsGotoBookmark. You can save a position and return to
>> it.
>
> First, I don't know a thing about AdsGetBookmark and AdsGotoBookmark.
> I've
> seen them listed in the ACE API Reference index, but they haven't caught
> my
> eye because, well, I haven't yet found an overview that answers the
> fundamental prerequisite question of "what the heck IS a 'bookmark,' in
> the
> first place?" Maybe I'm just really poor at reading documentation, but it
> seems that one must "derive" the nature of fundamental Ace concepts by
> trial-and-error and eventually develop working knowledge through long
> experience. Am I missing something obvious?

We probably should have included more background information on this topic
in the documentation rather than assuming an understanding of the concept
(this probably applies to quite a few topics in our help file ... we try to
make it a little better each release). A bookmark is simply a placeholder
analogous to the real-world placeholder for books. It lets you efficiently
return to a position (row) that you have previously visited. I think the
concept is supported by most database vendors.


>
> Second, "saving a position" and being able to "return to it" requires that
> I
> first VISIT every position in order to save it. Forgive me, but that
> sounds even more expensive than either building my own index for
> AdsGotoRecord OR using AdsSeek!

You are correct; this would not be an efficient use of bookmarks.

>
> I've been hoping to foist off most of the complexity onto the SQL engine:
>
> SELECT * FROM tablename WHERE [...criteria...];
>
> (process data, figure out what needs updated and/or deleted)
>
> UPDATE tablename SET fieldname = value WHERE [...criteria...];
> or
> DELETE FROM tablename WHERE [...critiera...];
>
> (repeat)

This seems like a good plan.


> and I wanted to access just the non-deleted records (1-3, 6, 7, 9, and 10)
> as a "logically contiguous" pseudo-recordset, I would probably just
> populate an STL map like so:
>
> map[1] = 1
> map[2] = 2
> map[3] = 3
> map[4] = 6
> map[5] = 7
> map[6] = 9
> map[7] = 10
>

Yes, this would be the index of logical record numbers I mentioned.

>
> Algorithms would be pretty straightforward but would scale linearly with
> the
> total number of records and become inefficient for "large" recordsets.

Yes. It would be impracticle for large tables.

>
> The only question remaining seems to be that of where the "read,
> test-for-deletedness, ignore-if-deleted" code resides: in AdsSkip or in my
> application code.

AdsSkip efficiently handles the deleted records. I think AdsGotoRecord is
the only API that provides the capability to visit records that are not
"visible" based on filters, WHERE clauses, etc.

HTH,
Mark Wilkins
Advantage R&D


Chris Chiesa Posted on 2007-01-14 03:32:11.0Z
From: Chris Chiesa <cchiesa1@rochester.rr.com>
Subject: Re: Record numbering and deleted records
Newsgroups: advantage.Linux
Reply-To: cchiesa1@rochester.rr.com
Mail-Copies-To: cchiesa1@rochester.rr.com
Date: Sat, 13 Jan 2007 22:32:11 -0500
References: <458b502f@solutions.advantagedatabase.com> <458c2518@solutions.advantagedatabase.com> <458ca982@solutions.advantagedatabase.com> <459a98ba@solutions.advantagedatabase.com>
Lines: 220
Organization: IEC
User-Agent: KNode/0.7.7
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7Bit
NNTP-Posting-Host: 72.226.213.15
Message-ID: <45a83126@solutions.advantagedatabase.com>
X-Trace: 12 Jan 2007 18:08:54 -0700, 72.226.213.15
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!72.226.213.15
Xref: solutions.advantagedatabase.com Advantage.Linux:1005
Article PK: 1130548


Mark Wilkins wrote:

>
> "Chris Chiesa" <xet001@yahoo.com> wrote in message
> news:458ca982@solutions.advantagedatabase.com...
>> Mark Wilkins wrote:
>
> If you are using ADTs, the deleted records should not be showing up in the
> result set. The ability to recall a deleted record only applies to DBF
> table types. That is the way DBFs are "defined".

When doing my local-database-file tests, I believe I am using an "ADT." I
used Advantage Data Architect to create a database and a table within same,
which caused a .adt file to appear, having the name of my database. I
"connect to" a Data Dictionary (.add file; don't remember how I created it.
By hand, perhaps?) that successfully references the created database / .adt
file, and I do get deleted records in my SELECT'ed recordsets.

I didn't know I had the option of creating a DBF table/database/whatever
(.dbf file though -- I see one such in my directory, that I didn't
explicitly create), though perhaps 'arc' gave me the choice and I didn't
realize its significance so early in my experience.

In any case, this doesn't necessarily help with my work for my client, as I
connect to a Data Dictionary about whose contents and underpinnings I know
nothing -- ADT, DBF, or what-have-you. I haven't been able to complete
execution of my own code to see if I get deleted records in my recordset --
it takes so long to retrieve millions of records that I get impatient,
interrupt my program -- and then can't ever again get a database connection
from my own code. Arc can get a connection and let me SELECT from the
table in question, but then I have to wade through a million-plus records
BY HAND looking for some that "might" be deleted. The only circumstantial
evidence I have is that a SELECT MAX(FIELD_NAME) FROM TABLE_NAME returns a
value that is demonstrably LESS than the value of that same field in the
last record returned. I tentatively suspect that this might mean that that
last record is a deleted record, and that I'm using an ADT, but... I just
don't know enough about how things fit together to know what conclusions to
draw from the rather screwy results of my experiments.

> It is true that if the result is a live cursor, then you can use
> AdsGotoRecord to position on a deleted record. This is because a live
> cursor is simply a filtered view of the actual physical table (for
> efficiency/speed reasons).

Since I will ultimately want to SELECT a recordset, do some fiddling around,
and "UPDATE" the records in place, back to the "actual physical table" if I
take your meaning. (That is, if I _can_ UPDATE records in place. My
client apparently found it necessary in _his_ code to DELETE all the
records and reINSERT (or AdsAppend() ) them from scratch. Ugh.)

> For example, if you run something like "SELECT
> * from sometable where ID=1", then it is possible to use AdsGotoRecord to
> go to records that are not in the result set. That is simply the way a
> "goto" works.

With all due respect, that would seem to invalidate the very concept of a
"result set." Shouldn't a result set be composed entirely of records that
meet the criteria specified to the database engine for record-selection?
Shouldn't the engine then deliver only records that meet the criteria you
asked for? Shouldn't the delivered recordset then be identical to the
result set of the request, i.e. be subject to the criteria specified as
part of the request? Shouldn't it therefore be impossible, while wandering
around in the returned recordset, to stumble across records that in any way
don't meet the specfied criteria, whether because the record is 'deleted'
or because you can "go to records that are not in the result set?" It
almost sounds as if the database engine ignores the criteria specification,
delivers ALL records from the physical table (or rather, makes them all
visible through the live cursor), and requires the application to
subsequently ignore / filter out those records that don't meet the original
criteria. If that's the case, it seems very wasteful of bandwidth to
deliver, say, a million-line entire table so that I can post-filter it down
to maybe ten records. I am strongly of the opinion that all such filtering
should take place "as close to the data as possible," i.e. in the engine at
the remote end before ever delivering a single record to me -- and that the
records delivered should all be in actual existence and should all meet the
criteria I _already gave to the engine_. If it doesn't work that way, SQL
support is an illusion -- but I have to admit that this would explain the
tremendous wait time for my programs' SELECT execution. Please, please,
tell me that's NOT how it works!

> In reality, AdsGotoRecord is not a good fit with SQL concepts.

I agree, and more, but I'll refrain from detailed comment. ;-)

> An
> SQL result is a relation, which is an unordered set of rows. In the pure
> SQL sense, it is legal for "SELECT * FROM sometable" to produce the same
> relation in two different orders.

I have no particular interest in "order." I just want all the records
available to me to match the criteria I specified in my retrieval operation
-- whether through SQL or through explicit Ads____ function calls. That
appears difficult-or-impossible to achieve, and at the cost of enormous
performance problems besides.

> If it is absolutely necessary to get a result set with which it is
> impossible to have deleted records, you can force a static result set with
> a query such as the following:
> SELECT {static} * from sometable;

I submit that it is next-to-impossible to do straightforward work on, or
navigation within, a recordset / result set that _does_ contain deleted
records -- or, new tonight, records "outside the result set." At least, I
am having a hard time wrapping my head around any useful way to do it under
the circumstances that appear to apply here. All of this is expanding my
project time estimates enormously, too.

> That will produce a result set that is a copy of the original data and
> will not have any deleted records (or any records that were filtered out
> due to a WHERE clause if provided).

I have a sneaking unhappy suspicion that will not then be possible to update
records "in place" back to the actual physical table. Am I right?

>>> It is
>>> true that the file structure supports the concept of physical record
>>> numbers and the AdsGotoRecord allows very quick access to those records,
>>> but conceptually it is better to think of that API as being similar to
>>> AdsGetBookmark/AdsGotoBookmark. You can save a position and return to
>>> it.
>>
>> First, I don't know a thing about AdsGetBookmark and AdsGotoBookmark.
>> [...]
>
> We probably should have included more background information on this topic
> in the documentation rather than assuming an understanding of the concept
> (this probably applies to quite a few topics in our help file ... we try
> to make it a little better each release).

If I had time, I would be able to supply you with a large list of "things to
add." :-)

> A bookmark is simply a placeholder analogous to the real-world placeholder
> for books. It lets you efficiently return to a position (row) that you
> have previously visited. I think the concept is supported by most
> database vendors.

I guess I'm not accustomed to an environment in which such low-level access
to the raw stuff of which tables and records are made, is necessary. I've
never had a need to create or use a bookmark using you-know-whose
you-know-what.

>> I've been hoping to foist off most of the complexity onto the SQL engine:
>>
>> SELECT * FROM tablename WHERE [...criteria...];
>>
>> (process data, figure out what needs updated and/or deleted)
>>
>> UPDATE tablename SET fieldname = value WHERE [...criteria...];
>> or
>> DELETE FROM tablename WHERE [...critiera...];
>>
>> (repeat)
>
> This seems like a good plan.

Except that it gets enormously complicated, apparently, by the need for the
"process data" step to painstakingly validate each record in the so-called
"result set" for existence and adherence to my selection criteria. My
client was hoping I could recast his code into SQL, but I'm becoming
convinced that to do so would be a great loss rather than a great gain.
I'm better off porting his "nitpickingly peck through all records" code and
forget about doing anything easier-to-read.

>> and I wanted to access just the non-deleted records (1-3, 6, 7, 9, and
>> 10) as a "logically contiguous" pseudo-recordset, I would probably just
>> populate an STL map like so:
>>
>> map[1] = 1
>> map[2] = 2
>> map[3] = 3
>> map[4] = 6
>> map[5] = 7
>> map[6] = 9
>> map[7] = 10
>>
>
> Yes, this would be the index of logical record numbers I mentioned.

Yes, it would.

>> Algorithms would be pretty straightforward but would scale linearly with
>> the total number of records and become inefficient for "large"
>> recordsets.
>
> Yes. It would be impracticle for large tables.

At the time I originally wrote, I was hoping my client's result sets would
be "small," but they're not. In fact, they begin to approach "large," by
my feeble standards. I'm just glad I'm handling "only" millions of
records, and not tens of millions!

>> The only question remaining seems to be that of where the "read,
>> test-for-deletedness, ignore-if-deleted" code resides: in AdsSkip or in
>> my application code.
>
> AdsSkip efficiently handles the deleted records. I think AdsGotoRecord is
> the only API that provides the capability to visit records that are not
> "visible" based on filters, WHERE clauses, etc.

I guess as long as I only ever want to go monotonically forward through the
result set -- and in this application, at least, that seems to be the case,
thank goodness -- I can probably get away with AdsSkip().

The whole darned shame of this is that my client's existing code was written
using the -- excuse me if I misname this -- TTableDescendant (?) et al
classes in Borland C++, whereas I am porting it to Linux where AT BEST I
have to understand the semantics of those classes, the semantics of the
underlying Ads____() functions, and then re-implement my own minimal set of
TTableDescendant-like functionality so as to port his code with minimal
disaster. I don't suppose you have TTableDescendant-equivalent classes
already available for Linux, to save me this heartache?

> HTH,

I'm afraid that mostly it discourages, but greater information is always a
good thing. Thanks for taking the time.

Chris