Watch, Follow, &
Connect with Us

Welcome, Guest
Guest Settings
Help

Thread: RecNo Not in Numerical Order


This question is answered.


Permlink Replies: 6 - Last Post: Jul 21, 2017 12:00 PM Last Post By: Earl Staley Threads: [ Previous | Next ]
Earl Staley

Posts: 86
Registered: 4/9/07
RecNo Not in Numerical Order  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 21, 2017 8:22 AM
I was troubleshooting a program and found that the RecNo was not advancing normally. So I made a short test program to see if the problem was in my code or elsewhere. The database is an SQLite database and the table was generated as follows:

CREATE TABLE Header(IDLine1 VarChar(80) UNIQUE, IDLine2 VarChar(80), URL VarChar(1000), UserID VarChar(80) NOT NULL, Creditor VarChar(80) NOT NULL, TableName VarChar(80) UNIQUE NOT NULL, MonthsPerCycle Integer NOT NULL, GracePeriod Integer NOT NULL, BillArrivalDate Integer NOT NULL, FinType Integer NOT NULL, Active Boolean Default 1);


Here is the total code in the test program:

__fastcall TForm2::TForm2(TComponent* Owner) : TForm(Owner) {
	FDTable1->Open();
	Memo1->Lines->Clear();
	FDTable1->First();
	while (!FDTable1->Eof) {
		Memo1->Lines->Add(FDTable1->RecNo);
		FDTable1->Next();
	}
	leRecCnt->Text = FDTable1->RecordCount;
}
 
// ---------------------------------------------------------------------------
void __fastcall TForm2::FDTable1AfterScroll(TDataSet *DataSet) {
	leRecNo->Text = FDTable1->RecNo;
 
}
// ---------------------------------------------------------------------------
void __fastcall TForm2::FormClose(TObject *Sender, TCloseAction &Action)
{
 Memo1->Lines->SaveToFile("RecNo.txt");
}
//---------------------------------------------------------------------------


Here is the output from "RecNo.txt":

1
2
5
6
7
3
4
8
9
10
11
12
14
15
13
16
17
18
19
20
21
22
23
24
25
26
27
28
31
32
29
33
30
34
35
36

All of the records are there, but they are not in correct order. How can this happen? I thought the Next() function advanced one record at a time in numerical order.

If some of the entries were made using Insert() rather than Append(), would that affect the order of RecNo?

Thank you...
Earl Staley
Jeff Overcash (...

Posts: 1,375
Registered: 9/23/99
Re: RecNo Not in Numerical Order
Correct
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 21, 2017 9:48 AM   in response to: Earl Staley in response to: Earl Staley
Earl Staley wrote:
I was troubleshooting a program and found that the RecNo was not advancing normally. So I made a short test program to see if the problem was in my code or elsewhere. The database is an SQLite database and the table was generated as follows:

CREATE TABLE Header(IDLine1 VarChar(80) UNIQUE, IDLine2 VarChar(80), URL VarChar(1000), UserID VarChar(80) NOT NULL, Creditor VarChar(80) NOT NULL, TableName VarChar(80) UNIQUE NOT NULL, MonthsPerCycle Integer NOT NULL, GracePeriod Integer NOT NULL, BillArrivalDate Integer NOT NULL, FinType Integer NOT NULL, Active Boolean Default 1);


Here is the total code in the test program:

__fastcall TForm2::TForm2(TComponent* Owner) : TForm(Owner) {
	FDTable1->Open();
	Memo1->Lines->Clear();
	FDTable1->First();
	while (!FDTable1->Eof) {
		Memo1->Lines->Add(FDTable1->RecNo);
		FDTable1->Next();
	}
	leRecCnt->Text = FDTable1->RecordCount;
}
 
// ---------------------------------------------------------------------------
void __fastcall TForm2::FDTable1AfterScroll(TDataSet *DataSet) {
	leRecNo->Text = FDTable1->RecNo;
 
}
// ---------------------------------------------------------------------------
void __fastcall TForm2::FormClose(TObject *Sender, TCloseAction &Action)
{
 Memo1->Lines->SaveToFile("RecNo.txt");
}
//---------------------------------------------------------------------------


Here is the output from "RecNo.txt":

1
2
5
6
7
3
4
8
9
10
11
12
14
15
13
16
17
18
19
20
21
22
23
24
25
26
27
28
31
32
29
33
30
34
35
36

All of the records are there, but they are not in correct order. How can this happen? I thought the Next() function advanced one record at a time in numerical order.

If some of the entries were made using Insert() rather than Append(), would that affect the order of RecNo?

Yes this will. Append always puts it as the last item, insert in place. RecNo
only represents the number of the record as it was put into the buffer, either
from reading it from the DB, inserting it, or appending it. Deletes will put
gaps in that number. That number is not going to represent the same record from
one execution to another necessarily either.


Thank you...
Earl Staley


--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Earl Staley

Posts: 86
Registered: 4/9/07
Re: RecNo Not in Numerical Order  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 21, 2017 10:16 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...

Yes this will. Append always puts it as the last item, insert in place. RecNo
only represents the number of the record as it was put into the buffer, either
from reading it from the DB, inserting it, or appending it. Deletes will put
gaps in that number. That number is not going to represent the same record from
one execution to another necessarily either.
Jeff,

Thank you for the reply.

I can understand that the database might know the RecNo while the program is open that inserted or appended the data. But after the records are posted and the database is closed, how would the database know whether records were inserted or appended? Does the database retain that info and, if so, why would the database retain that info? Why wouldn't the RecNo just follow the records numerically after the records are posted and the database closed?

Thank You...
Earl Staley
Jeff Overcash (...

Posts: 1,375
Registered: 9/23/99
Re: RecNo Not in Numerical Order  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 21, 2017 10:49 AM   in response to: Earl Staley in response to: Earl Staley
Earl Staley wrote:
Yes this will. Append always puts it as the last item, insert in place. RecNo
only represents the number of the record as it was put into the buffer, either
from reading it from the DB, inserting it, or appending it. Deletes will put
gaps in that number. That number is not going to represent the same record from
one execution to another necessarily either.
Jeff,

Thank you for the reply.

I can understand that the database might know the RecNo while the program is open that inserted or appended the data. But after the records are posted and the database is closed, how would the database know whether records were inserted or appended? Does the database retain that info and, if so, why would the database retain that info? Why wouldn't the RecNo just follow the records numerically after the records are posted and the database closed?

Thank You...
Earl Staley

RecNo is in this context is 100% a client thing. Append in SQL backends is
meaningless. In file based DB's like pDox or dBase it does mean put it at the
end of the file.

SQL tables are mathematical Sets. IOW they are unordered. You have no control
on the actual physical location that the record will be stored in the server.
So if you do not apply an ORDER BY on your SQL the result order will change over
time as most backends will just read data pages at a time and return everything
on that data page before moving to the next data page.

Most DB's have some form of RecNo concept (Oracle it is RecNo, InterBase
RDB$DB_KEY etc), but it is a pseudo column that has no physical storage and has
no relation to the order that things were inserted into the DB.

Primary keys should never change (if they are changing that usually means they
are tied to business logic and should be a secondary key with a real primary key
added) and if using an autoincrement mechanism (sequences, generators etc) will
actually when ordered be the order in which items entered the DB (with gaps from
deletes or rolled back items after the number was generated).

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Earl Staley

Posts: 86
Registered: 4/9/07
Re: RecNo Not in Numerical Order  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 21, 2017 11:16 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...

RecNo is in this context is 100% a client thing. Append in SQL backends is
meaningless. In file based DB's like pDox or dBase it does mean put it at the
end of the file.

SQL tables are mathematical Sets. IOW they are unordered. You have no control
on the actual physical location that the record will be stored in the server.
So if you do not apply an ORDER BY on your SQL the result order will change over
time as most backends will just read data pages at a time and return everything
on that data page before moving to the next data page.

Most DB's have some form of RecNo concept (Oracle it is RecNo, InterBase
RDB$DB_KEY etc), but it is a pseudo column that has no physical storage and has
no relation to the order that things were inserted into the DB.

Primary keys should never change (if they are changing that usually means they
are tied to business logic and should be a secondary key with a real primary key
added) and if using an autoincrement mechanism (sequences, generators etc) will
actually when ordered be the order in which items entered the DB (with gaps from
deletes or rolled back items after the number was generated).
Jeff,

Thank you your extensive reply.

I am using an SQLite database with an FDTable so I am not reordering the table in anyway. So shouldn't the RecNos be consecutive since "it is a pseudo column that has no physical storage"? The RecNos remain in the same non-numerical order every time I run the test program in the original post, so SQLite must be saving RecNos someplace.

All of the data in the table was loaded with Append() and Post().

Thank You...
Earl Staley
Jeff Overcash (...

Posts: 1,375
Registered: 9/23/99
Re: RecNo Not in Numerical Order  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 21, 2017 11:47 AM   in response to: Earl Staley in response to: Earl Staley
Earl Staley wrote:
RecNo is in this context is 100% a client thing. Append in SQL backends is
meaningless. In file based DB's like pDox or dBase it does mean put it at the
end of the file.

SQL tables are mathematical Sets. IOW they are unordered. You have no control
on the actual physical location that the record will be stored in the server.
So if you do not apply an ORDER BY on your SQL the result order will change over
time as most backends will just read data pages at a time and return everything
on that data page before moving to the next data page.

Most DB's have some form of RecNo concept (Oracle it is RecNo, InterBase
RDB$DB_KEY etc), but it is a pseudo column that has no physical storage and has
no relation to the order that things were inserted into the DB.

Primary keys should never change (if they are changing that usually means they
are tied to business logic and should be a secondary key with a real primary key
added) and if using an autoincrement mechanism (sequences, generators etc) will
actually when ordered be the order in which items entered the DB (with gaps from
deletes or rolled back items after the number was generated).
Jeff,

Thank you your extensive reply.

I am using an SQLite database with an FDTable so I am not reordering the table in anyway. So shouldn't the RecNos be consecutive since "it is a pseudo column that has no physical storage"? The RecNos remain in the same non-numerical order every time I run the test program in the original post, so SQLite must be saving RecNos someplace.

No RecNo is 100% totally a TDataset thing. It has absolutely nothing to do with
the backend.

All of the data in the table was loaded with Append() and Post().

Append only tells the FDTable where you want the data to be put in the local
buffer. There is no way to tell SQLite where to store the data in the database.
The storage of the data and how to optimize that storage for space and access
speed is left totally up to SQLite.


Thank You...
Earl Staley

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Earl Staley

Posts: 86
Registered: 4/9/07
Re: RecNo Not in Numerical Order  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 21, 2017 12:00 PM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Thank you Jeff, for your extensive help...

Earl
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02