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
lukeMHAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick UpsonPrincipal Operations EngineerCommented:
please post details of versions (interbase, delphi, etc) and the details of how the generator value is acquired
lukeMHAuthor Commented:
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 ; ||
Nick UpsonPrincipal Operations EngineerCommented:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

lukeMHAuthor Commented:
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
Nick UpsonPrincipal Operations EngineerCommented:
behind the TIBtable there is still a query, I don't know Delphi enough to point you at the problem but there should be a way
of using it to get the table records in order. The database stores the records in any order it feels like, it's your query that decides on the order to get them
kacorretiredCommented:
Hi lukeMH,

the SQL-based databases differ from the old record type databases because they use sets instead of the serial inserted records therefore is not important where there are placed in the set. If I understood right you get an empty row but the ticksheetindex is inserted.

To find the row having the highest ticksheetindex would be not lucky because in the case when more row are inserted the same time you will not know which was your row you need.

Try to use in your procedure a variable which takes over this ticksheetindex, search this value, then you can process this row.

I hope this helps

wbr

Janos

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lukeMHAuthor Commented:
Doesn't seem to be any option to do so in the table component

Kind Regards

Luke
lukeMHAuthor Commented:
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
Nick UpsonPrincipal Operations EngineerCommented:
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.


Nick UpsonPrincipal Operations EngineerCommented:
"generate a unique ID that I am sure is unique " select  GEN_ID(gen_ticksheet, 1) from rdb$database
kacorretiredCommented:
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
kacorretiredCommented:
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.
EugeneK-biruzaCommented:
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

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.