Solved

Interbase problem. Record/Key deleted.

Posted on 1998-11-19
12
534 Views
Last Modified: 2012-06-22
I have Delphi4 installed with Interbase 5.0. Here is the probelm:
I have a database and a table in it. This table is created this way
CREATE TABLE STOREMAN
(
ID_STOCKGROUP         INT              NOT NULL,
Name                  char(96),
Code                  char(32),
MarkUpValue           float            DEFAULT 0,
MarkUpIsPercent       FT               DEFAULT "T",
MinAmount             float            DEFAULT 0,
MaxAmount             float            DEFAULT 0,
MinMaxWanted          FT               DEFAULT "T",
CassGroup             int              DEFAULT 1,
ID_UserCreate         int,
ID_UserEdit           int
)
The first field has to be Autoincrement field. But Interbase does not have native support for autoincrement fields. So i made a generator and a triger. The triger fires before post in the database. When i try to post a NEW record i get the BDE error "Record/Key Deleted". After this when i press the refresh button, i see that the record has been posted. But i don't want to get this error. What might be the problem, what does this error meen, and what is the solution?
0
Comment
Question by:pripkam
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 1

Expert Comment

by:zac
ID: 1347291
Pripkam,

I can't give you an exact answer as I don't use Interbase, but I can give you some good pointers.  However the problem is a standard SQL problem when using auto increment fields in the primary key.  

The problem is caused by the fact that you post a record with out a primary key, the DB then gives it a primary key - fine.  However delphi then tries to reposition itself onto that row - probably using the primary key (which as far as delphi is concerned is NULL) and hence cannot find the row. EXCEPTION!

As for a work around - Are you using TTables?  - If so don't.... Use TQuery's.  Also do not request a live result set from the TQuery - use TUpdateQuery to update records.

This should remove the error from the post.  However if you try and edit that row you will probably get the same error.  This may or may not be important.

Should you need to be able to edit a newly inserted record, you have 2 choices - firstly you can refresh the dataset (you may have to close & reopen the TQuery to achive this) - this is fine if the dataset is small.  Alternativly if there is some way of getting the last value of the generator back from interbase then you can put this into the dataset by something like:

Query1.FieldByName('ID_STOCKGROUP').Value := myIdentity;

In MS SQL server after you have issued an insert statement that inserts a row with an identity field you can refer to the value of the variable @@identity which contains the value inserted.

ie you can issue a SELECT @@IDENTITY

You can then put this value into the identity field in your recordset and avoid all complications....

Unfortunately there is no cure all because of the way SQL was defined.

If you want any further assistance, just let me know.

Good Luck


Zac

0
 

Author Comment

by:pripkam
ID: 1347292
Sorry, I have forgotten to say that ID_STOCKGROUP has a Primary key, so it seems that this is not the problem.
I use only TTables (not TQuery-s).
About "Query1.FieldByName('ID_STOCKGROUP').Value := myIdentity":
I have already done something like this, and it works fine, but the problem is that i want to move the bussiness ligin in the server. I capture the event lokaly (in the "OnNewRecord" of the TTable), then i call a StoredProcedure which in turn calls the generator, and the proc returns value. That is fine but i want to make this from the server. That is how i will become application-independedable. Do you ged it?

Regards
Pripkam
0
 
LVL 1

Expert Comment

by:zac
ID: 1347293
Pripkam,

Yes, I assumed it was the primary key as I stated in my answer....  You wouldn't get the error otherwise....

What I suggest is that do everything server side, but you then need to get the value that got inserted into the primary key.  As I said under MS SQL you can use SELECT @@IDENTITY - I assume you can get in effect generator - 1 - or perhaps return the value of the primary key from your insert stored proc (my prefered route).  

You then need to put this value into your local recordset manually as delphi doesn't know about this because it happened server side.  Hence the code sample above.

I think you misunderstood my answer - Basically you need to insert the value for the primary key into the delphi field *AFTER POST* so that the delphi cached recordset reflects that of the live data.  Otherwise you get the dreaded record deleted error which is cause by delphi realising that its cached record and the one on the server are different (not surprising - the server added a field...)

Unfortunately there is no *nice* work around - this is as good as it gets.  You either refresh the dataset or you pick up the identity value and "kludge" it back into the cached dataset.

It isn't ideal but it is the main problem with the SQL standard.  As the changes are heppening remotely you have *NO* way of monitoring them - so you have to deal with it manually at the client or refresh the entire dataset (how can you refresh the row if you don't know its primary key?).

The solution I use under SQL server is to use a stored proc to do the insert and return a result set from the proc that contains the value of the indentity field I have just inserted.  I then force this back into the dataset.

Good luck,

Zac
0
 

Author Comment

by:pripkam
ID: 1347294
It seems that i am missing something. PLEASE post some code here so that i can understand you. I  THINk i did what you said, but i am not shure.
Sorry about this INCONVENIENCE

0
 

Author Comment

by:pripkam
ID: 1347295
It seems that i am missing something. PLEASE post some code here so that i can understand you. I  THINk i did what you said, but i am not shure.
Sorry about this INCONVENIENCE

0
 
LVL 1

Expert Comment

by:zac
ID: 1347296
Pripkam,

Unfortunately code might be a bit dificult - I don't use interbase - in fact I don't even use the BDE I use some TQuery descendants for direct MS SQL access however the same problem applies.

Okay here goes.....  This is a kludge but it should work.....

1) Declare a boolean variable (called insert in this example).
In you AfterInsert event set insert := True (obviously in  oncreate set it to false).

In the AfterPost event write something like this...

  if insert then
  begin
    insert := false;
    SQLQuery1.Edit;
    SQLQuery1['p_campaign'] := GetIdentityValue;
    SQLQuery1.Post;
  end;

This code works here with SQL....  However it does assume that your update SQL objects update query does not touch the identity field.

The GetIdentityValue function returns the value that was inserted into the identity field from the DB.

What the above code does it force delphi into putting the correct value of the identity field into the cached copy of the identity field.  

You will find that you can also now edit a newly inserted field etc.

If you want any further information just let me know

Good luck


Zac


0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:pripkam
ID: 1347297
Sorry for the delay. (problems with the ISP)
So here is what happens. I desided to reorganise everything. First i started using TTables. From the "Client/server programming with Delphi3" i took a table that is absolutly the same as mine table(if you want i can mail the tables to you). It has triger like mine, generator like mine, primary keys and so on.... Here comes the intereting part: It works with their table but doesn't with mine, that is when i try to post a new record, the record is been posted and updated in the database, but i get the error "Record/key deleted" again. I don't get this error with thier table. I  comapred the two tables field, by field, property by property...
Absolutly strange.
Sorry about the reeling out :)
0
 
LVL 1

Expert Comment

by:zac
ID: 1347298
Pripkam,

Well lets put it this way the SQL standard states that this should *NOT* work.  I have given you the answers above which is the accepted method of getting the identity for a table after insertion.  Unless borlands example performs a refresh (or uses some cunning interbase feature) it should not work.  SQL servers will not work in this context as explained earlier.  According to the SQL standard when you perform an insert statement the server does not tell you the value of the identity inserted therefore your program does not know what the identity value is - it has *NO* way of knowing what it will be.  Consequently when you access the table you get an error due to inconsistancies between the cached record set and the one on the server.....

As a note, if you are using a database server you should be using TQuery objects, not TTable objects.

The last answer I gave is a valid work around, and is the only portable method I know of.  I have had it working with differing databases without a problem.  If you are looking for a miracle solution that involves no coding then unfortunately I cannot help you.

Yours,

Zac

0
 
LVL 1

Accepted Solution

by:
EmmDieh earned 200 total points
ID: 1347299
The Record-Deleted-Problem is a well known when using the BDE with an SQL-Server. It happens when the SQL-Server modifies the primary key within the before insert trigger but the BDE cannot know the new value so it thinks the record has been deleted.
also see: http://www.mers.com/interbasereckeydel.html
---
AFAIK, no SQL Sever has a datatype like Autoincrement, because it is not needed. They have triggers and generators instead.
---
So your first approach was totally correct. Use TQueries with SQL-Servers.
0
 

Author Comment

by:pripkam
ID: 1347300
Here go the points...
But why should i use TQuery instead of TTable?
Is seem that my database was currupted, because when i rebuild it(entirely new one, but the same as first one) it is working ok with TTable.
Thx
0
 
LVL 1

Expert Comment

by:EmmDieh
ID: 1347301
tTable's are not designed to work with SQL-Servers. They hide operation from the user (like select * and other nasty things).
If you go from local to remote you'll see that you'll have to minimize network traffic.
0
 

Expert Comment

by:JonathanElkins
ID: 32707689
I have had this problem for years with no solution with Interbase/Delphi/BDE. What Zac and EmmDieh make sense if you are modifying the primary key. But what if you are not?

I have an interbase table where I'm changing a non PK/non indexed value on an exisitng record (i.e. *NOT* a record that I recently inserted). For many years this record was modifyable now: RECORD/KEY DELETED.

Anybody got any ideas?
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now