lukeMH
asked on
Delphi / Interbase table inserts seem to be random
Hi,
I have a web application that uses delphi / interbase
When you press the 'NEW' button,
The table is opened
A new record is inserted (blank)
The table is posted
The table is closed
A tranasaction commits the data
After this an interbase generator gives the record a unique ID
The application sends the table to the end (so that it can read the unique ID that was assigned to the new record you just created- as it needs to know this)
Now this was working fine, but now something has gone wrong.
When you insert a new record it sticks it in a completly random place in the database.
For example, imagine we have a table with records numbered from 1 to 501.
Press new, a blank record is inseted etc etc (which has the uniqueID 501)
When the table then seeks to the end so that it can read back the value of 501, it instead finds 500 at the end.
Eventually I spotted that interbase was sticking 501 in a completely random place in the database, and it now does this every time
eg, the records may run like this
1
2
3
4
5
6
501
7
8
etc
Why on earth is it doiing this? Any idea how to stop it? It is strange as it worked fine for a while now it always does this. Also I have recreated the same fault on 2 computers (i.e. it works fine for ages then suddenly start inserting randomly)
If there is no way to gaurantee that the new data will be inserted at the end, then is there a way that I can create a new record, number it, and then tell delphi what the number of the record is without having to do what I am doing?
Thanks in advance for your help
Kind Regards
Luke
I have a web application that uses delphi / interbase
When you press the 'NEW' button,
The table is opened
A new record is inserted (blank)
The table is posted
The table is closed
A tranasaction commits the data
After this an interbase generator gives the record a unique ID
The application sends the table to the end (so that it can read the unique ID that was assigned to the new record you just created- as it needs to know this)
Now this was working fine, but now something has gone wrong.
When you insert a new record it sticks it in a completly random place in the database.
For example, imagine we have a table with records numbered from 1 to 501.
Press new, a blank record is inseted etc etc (which has the uniqueID 501)
When the table then seeks to the end so that it can read back the value of 501, it instead finds 500 at the end.
Eventually I spotted that interbase was sticking 501 in a completely random place in the database, and it now does this every time
eg, the records may run like this
1
2
3
4
5
6
501
7
8
etc
Why on earth is it doiing this? Any idea how to stop it? It is strange as it worked fine for a while now it always does this. Also I have recreated the same fault on 2 computers (i.e. it works fine for ages then suddenly start inserting randomly)
If there is no way to gaurantee that the new data will be inserted at the end, then is there a way that I can create a new record, number it, and then tell delphi what the number of the record is without having to do what I am doing?
Thanks in advance for your help
Kind Regards
Luke
Nick Upson
please post details of versions (interbase, delphi, etc) and the details of how the generator value is acquired
ASKER
Hi,
Delphi 7
Interbase 6.0
Using standard Delphi interbase components
Here is my generator code
CREATE GENERATOR gen_ticksheet;
SET GENERATOR gen_ticksheet TO 1008919;
SET TERM || ;
CREATE TRIGGER ticksheet_autoinc FOR ticksheet
BEFORE INSERT AS
BEGIN
IF (NEW.ticksheetindex is NULL) THEN
NEW.ticksheetindex = GEN_ID(gen_ticksheet, 1);
END||
SET TERM ; ||
Delphi 7
Interbase 6.0
Using standard Delphi interbase components
Here is my generator code
CREATE GENERATOR gen_ticksheet;
SET GENERATOR gen_ticksheet TO 1008919;
SET TERM || ;
CREATE TRIGGER ticksheet_autoinc FOR ticksheet
BEFORE INSERT AS
BEGIN
IF (NEW.ticksheetindex is NULL) THEN
NEW.ticksheetindex = GEN_ID(gen_ticksheet, 1);
END||
SET TERM ; ||
ok, I read it again "When the table then seeks to the end so that it can read back the value of 501, it instead finds 500 at the end.
Eventually I spotted that interbase was sticking 501 in a completely random place in the database, and it now does this every time "
if you do select * from table, you will get the records in whatever order he database feels like, this is as per SQL standards.
if the order is significant you must add an order by clause, so you get:
select ticksheetindex from ticksheet order by ticksheetindex
or
select ticksheetindex from ticksheet order by 1
Eventually I spotted that interbase was sticking 501 in a completely random place in the database, and it now does this every time "
if you do select * from table, you will get the records in whatever order he database feels like, this is as per SQL standards.
if the order is significant you must add an order by clause, so you get:
select ticksheetindex from ticksheet order by ticksheetindex
or
select ticksheetindex from ticksheet order by 1
ASKER
Hi,
I am not using a query, I am using an Interbase table (TIBTable)
Previously, Table.insert would insert at the end. And Table.Last would seek to the end.
Now it does not anymore.
Kind Regards
Luke
I am not using a query, I am using an Interbase table (TIBTable)
Previously, Table.insert would insert at the end. And Table.Last would seek to the end.
Now it does not anymore.
Kind Regards
Luke
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Doesn't seem to be any option to do so in the table component
Kind Regards
Luke
Kind Regards
Luke
ASKER
kacor, Would that not create the same problem? How would I generate a unique ID that I am sure is unique and specific to a client?
Kind Regards
Luke
Kind Regards
Luke
from another support forum
> I have taken on another programmer (Delphi) on a consultancy
> basis. On a small project he did for us, I was looking
> through the sources and noticed that he had used an IBX
> IBTable component to access a table that held employee
> timeclock records and filtered the records locally using the
> OnFilter event. As you can imagine, this table can hold
> quite a lot of records.
You seem to have hit the nail on the head, less network traffic = increased
speed, also you would typically have a much more powerful server for
processing the requests so you should see increased speed over local
filtering
Where circumstances permit I would also use TIBSQL component as it has a lot
less overhead than TIBQuery.
> I have taken on another programmer (Delphi) on a consultancy
> basis. On a small project he did for us, I was looking
> through the sources and noticed that he had used an IBX
> IBTable component to access a table that held employee
> timeclock records and filtered the records locally using the
> OnFilter event. As you can imagine, this table can hold
> quite a lot of records.
You seem to have hit the nail on the head, less network traffic = increased
speed, also you would typically have a much more powerful server for
processing the requests so you should see increased speed over local
filtering
Where circumstances permit I would also use TIBSQL component as it has a lot
less overhead than TIBQuery.
"generate a unique ID that I am sure is unique " select GEN_ID(gen_ticksheet, 1) from rdb$database
As NickUpson said the unique ID is generated by the above command. that's sure.
If you'd like to use the IBX components and til now you didn't use them, click on this link:
Introduction To InterBase Express (IBX) By Bill Todd, Borland Developers Conference San Diego 2000
http://ibphoenix.com/main.nfs?a=ibphoenix&s=1119091248:273978&page=ibp_ibx
If you'd like to use the IBX components and til now you didn't use them, click on this link:
Introduction To InterBase Express (IBX) By Bill Todd, Borland Developers Conference San Diego 2000
http://ibphoenix.com/main.nfs?a=ibphoenix&s=1119091248:273978&page=ibp_ibx
I'd like to add the followings only:
In SQL-based databases are missing the commands BeginOfFile (BOF) and EndOfFile (EOF). They are not interpretable because of the sets.
In SQL-based databases are missing the commands BeginOfFile (BOF) and EndOfFile (EOF). They are not interpretable because of the sets.
Just in addition: here is a very good book about Firebrid:
The Firebird Book: A Reference for Database Developers
http://www.devarchive.com/bb231.html
The Firebird Book: A Reference for Database Developers
http://www.devarchive.com/bb231.html