Primary Key Constraint Error in Multi-User environment Inserting Data

Using IB XE3, and have migrated from BDE -> SQLDirect -> FireDAC

These triggers and procedures have been in the system for over 10 years with not issue until we moved to FireDAC.  I am thinking that it has to be some kind of setting that we are missing.  I am wondering if there is some way that a transaction can still be active when it's looping to insert the next record.  As you can see the PK is set from a generator through a StoredProc, so it should always be unique.

Is there some way to Force an immediate Commit from the FDConnection component for each Query/Exec that happens?  We are using the automatic transaction handling within FireDAC.

Any suggestions welcome.

-------------------------

Mesage text: attempt to store duplicate value (visible to active transactions) in unique index "RDB$PRIMARY667"
null segment of UNIQUE KEY
Error code: 335544349
Error kind: Other
Server object: 
Command text offset: 
-------------------------------------------
Command text: INSERT INTO PATRPT
(PATIENT_ID, REPORT_ID, DESCRIPTION, REPORT)
VALUES (:PATIENT_ID, :REPORT_ID, :DESCRIPTION, :REPORT)
 
Command parameters:
  PATIENT_ID=125747
  REPORT_ID=2
  DESCRIPTION=Patient Statement
  REPORT=SPISLZHŠ 

There are 2 triggers that execute:

CREATE TRIGGER SET_PATRPT_ID FOR PATRPT BEFORE INSERT POSITION 0 AS
BEGIN
  IF (NEW.PATRPT_ID IS NULL) THEN
    EXECUTE PROCEDURE GET_NEW_PATRPT_ID 
      RETURNING_VALUES NEW.PATRPT_ID;
END

CREATE TRIGGER PATRPT_INSERT FOR PATRPT BEFORE INSERT POSITION 0 AS
BEGIN
  NEW.CREATION_DATETIME = 'NOW';
  EXECUTE PROCEDURE GET_STAFF_ID
    RETURNING_VALUES NEW.CREATION_STAFF_ID;
END

The triggers call these procedures:

CREATE PROCEDURE GET_NEW_PATRPT_ID  RETURNS (
  NEW_ID INTEGER
) AS 
BEGIN
  NEW_ID = GEN_ID(CREATE_INSURANCE_ID, 1);
  SUSPEND; 
END

CREATE PROCEDURE GET_STAFF_ID  RETURNS (
  STAFF_ID INTEGER
) AS  
BEGIN
  SELECT STAFF_ID FROM STAFF
     WHERE UPPER(STAFF_USERNAME) = UPPER(USER) INTO
      :STAFF_ID;

  SUSPEND;
END
0
Bryan
7/21/2015 2:21:17 PM
embarcadero.delphi.firedac 822 articles. 2 followers. Follow

4 Replies
1014 Views

Similar Articles

[PageSpeed] 8

Your question as presented is answered here: 
http://docwiki.embarcadero.com/RADStudio/XE7/en/Managing_Transactions_%28FireDAC%29 


Autocommit is the terminology for forcing an immediate commit and you 
should notice the above document says FireDac has autocommit on by 
default. You can also explicitly control transactions by issuing your 
own commit statements, just as the above document explains

However, I don't think transactions are your problem here. Look to see 
what the primary key to table PATRPT is. Your code suggests it is 
PATRPT_ID but I am guessing it is not. If it is, then PATRPT_ID is being 
populated via a generator and generator values are not based on 
transactions. You get a new one each time you call GEN_ID.

If you are still confused, I think either altering trigger SET_PATRPT_ID 
or creating a new before insert trigger with a position after 0 which 
does some sort of logging to reveal what is getting insert is needed. It 
might look like this:

create table log (the_time default current_timestamp, what_happened 
varchar(1024);

CREATE TRIGGER logger  FOR PATRPT BEFORE INSERT POSITION 1 AS
BEGIN
   insert into log ((what_happened) values ('patient_id = ' || patient_id));
   insert into log ((what_happened) values ('NEW.PATRPT_ID = ' || 
NEW.PATRPT_ID));
....
END

Bryan Thorell wrote:
> Is there some way to Force an immediate Commit from the FDConnection component for each Query/Exec that happens?  We are using the automatic transaction handling within FireDAC.
0
quinn
7/21/2015 4:30:38 PM
Your question as presented is answered here: 
http://docwiki.embarcadero.com/RADStudio/XE7/en/Managing_Transactions_%28FireDAC%29 


Autocommit is the terminology for forcing an immediate commit and you 
should notice the above document says FireDac has autocommit on by 
default. You can also explicitly control transactions by issuing your 
own commit statements, just as the above document explains

However, I don't think transactions are your problem here. Look to see 
what the primary key to table PATRPT is. Your code suggests it is 
PATRPT_ID but I am guessing it is not. If it is, then PATRPT_ID is being 
populated via a generator and generator values are not based on 
transactions. You get a new one each time you call GEN_ID.

If you are still confused, I think either altering trigger SET_PATRPT_ID 
or creating a new before insert trigger with a position after 0 which 
does some sort of logging to reveal what is getting insert is needed. It 
might look like this:

create table log (the_time default current_timestamp, what_happened 
varchar(1024);

CREATE TRIGGER logger  FOR PATRPT BEFORE INSERT POSITION 1 AS
BEGIN
   insert into log ((what_happened) values ('patient_id = ' || patient_id));
   insert into log ((what_happened) values ('NEW.PATRPT_ID = ' || 
NEW.PATRPT_ID));
....
END

Bryan Thorell wrote:
> Is there some way to Force an immediate Commit from the FDConnection component for each Query/Exec that happens?  We are using the automatic transaction handling within FireDAC.
0
quinn
7/21/2015 4:33:57 PM
> {quote:title=quinn wildman wrote:}{quote}
> Your question as presented is answered here: 
> http://docwiki.embarcadero.com/RADStudio/XE7/en/Managing_Transactions_%28FireDAC%29 
> 
> 
> Autocommit is the terminology for forcing an immediate commit and you 
> should notice the above document says FireDac has autocommit on by 
> default. You can also explicitly control transactions by issuing your 
> own commit statements, just as the above document explains
> 
> However, I don't think transactions are your problem here. Look to see 
> what the primary key to table PATRPT is. Your code suggests it is 
> PATRPT_ID but I am guessing it is not. If it is, then PATRPT_ID is being 
> populated via a generator and generator values are not based on 
> transactions. You get a new one each time you call GEN_ID.
> 
> If you are still confused, I think either altering trigger SET_PATRPT_ID 
> or creating a new before insert trigger with a position after 0 which 
> does some sort of logging to reveal what is getting insert is needed. It 
> might look like this:
> 
> create table log (the_time default current_timestamp, what_happened 
> varchar(1024);
> 
> CREATE TRIGGER logger  FOR PATRPT BEFORE INSERT POSITION 1 AS
> BEGIN
>    insert into log ((what_happened) values ('patient_id = ' || patient_id));
>    insert into log ((what_happened) values ('NEW.PATRPT_ID = ' || 
> NEW.PATRPT_ID));
> ...
> END
> 
> Bryan Thorell wrote:
> > Is there some way to Force an immediate Commit from the FDConnection component for each Query/Exec that happens?  We are using the automatic transaction handling within FireDAC.


Thanks, AutoCommit is turned on (as is the default), and PATRPT_ID is the Primary Key defined as follows:

/* Table: PATRPT, Owner: SYSDBA */
CREATE TABLE PATRPT (PATIENT_ID INTEGER NOT NULL,
        CREATION_DATETIME TIMESTAMP DEFAULT "NOW",
        REPORT_ID INTEGER,
        REPORT BLOB SUB_TYPE 0 SEGMENT SIZE 80,
        DESCRIPTION VARCHAR(80),
        PATRPT_ID INTEGER NOT NULL,
        FILENAME VARCHAR(255),
        CREATION_STAFF_ID INTEGER,
PRIMARY KEY (PATRPT_ID));

So as the generator is setting the PK through the BEFORE INSERT using a IB generator, how can you get a PK violation that the ID already exists?

So let's talk about Isolation, right now it is set to xiReadCommitted (which is the default).  It seems that this should be right, however which Isolation level is the most compatible to what the BDE used by default?

Also, note that this is straight inserts and reads, this table isn't ever edited, the hard thing with this is it doesn't happen if only a single user is inserting into the table, but if there are many users simultaniously inserting data into this table, this is when it "might" happen (it does not always happen).
0
Bryan
7/21/2015 8:06:38 PM
The only way I see that your problem would be happening is if an insert 
is happening that you don't anticipate which is supplying the key. To 
start, I suggest you remove the if from trigger SET_PATRPT_ID and always 
set NEW.PATRPT_ID, regardless of whether it is null not.

If this works as I think it will, then use the FireDac monitor to 
determine where the insert is coming from.

Bryan Thorell wrote:
> So as the generator is setting the PK through the BEFORE INSERT using a IB generator, how can you get a PK violation that the ID already exists?
0
quinn
7/21/2015 9:39:32 PM
Reply:

Similar Artilces:

Cannot INSERT data to 3 tables linked with relationship (INSERT statement conflicted with the FOREIGN KEY constraint error)
Hello  I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake... Here's the sql management studio diagram :  and here goes the  code1 DataSet ds = new DataSet(); 2 3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection); 4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1); 5 myCommand1.FillSchema(ds, SchemaType.Source); 6 DataTable pTable = ds.Tables["Table"]; 7 pTable.TableName = "SurveyTemplate"; 8 m...

Code builders INSERT Data Method
PLEASE HELP I would like to insert the 'primary key' value into a foreign key table each time a record is insert using the: Web Matrix-Code builders-INSERT Data Method All help greatly appreciated......

SP causes the error Violation of UNIQUE KEY constraint Cannot insert duplicate key
The following SP causes the error "Violation of UNIQUE KEY constraint 'AlumniID'. Cannot insert duplicate key in object [table name]. The statement has been terminated." AlumniID is the table's PK and is set to autoincrement. I'd appreciate any help or suggestions. 1 ALTER PROCEDURE dbo.sp_CreateUser 2 3 @UserID uniqueidentifier, 4 @UserName nvarchar(128), 5 @Email nvarchar(50), 6 @FirstName nvarchar(25), 7 @LastName nvarchar(50), 8 @Teacher nvarchar(25), 9 @GradYr int 10 11 AS 12 SET NOCOUNT ON; 13 ...

"Violation of PRIMARY KEY constraint Pk_table. Cannot insert duplicate key in object (tbltable)
this is my error and I was hoping tha someone else has seen this error and could tell me where to lok to solve this problem. It only happens when i go to the update procedure then go backt he populating procedure of my gridview. please if help Thank You in advance I think you forgot to set your primary key to be an identity column. This will auto increment it and avoid this error. thanks for that i check thatThank You in advance can u please post your code... While updating make sure that ur not updating the primary key... but just updating the other columns in that row.  &...

Violation of PRIMARY KEY constraint 'PK_tblType'. Cannot insert duplicate key in object 'dbo.tblType'.
the point here that i have a small table with two fileds, ID (guid) as primerykey RAF(char) and the table is empty when i add a new row i recieve this exception, Violation of PRIMARY KEY constraint 'PK_tblType'. Cannot insert duplicate key in object 'dbo.tblType'. i found no way to solve the problem. thanks in advans  Muhanad YOUNISMCSD.NETMy Blog || My Photos || LinkedIn Apparently you are a value that already exists. How are you generating the GUID?***********************Dinakar NethiLife is short. Enjoy it.*********************** Yes, i think the problem is...

multiple fields as primary key and primary key error message
Hello, I have a two-fold question, one of which is very basic, but I am very interested in feedback as I am fairly new to dbase design. I have a UsageData table with the following fields: Facility, UsageDate, ProductID, BegInventory, PurchaseAmount, EndInventory.  The user can only insert one entry per Facility per month (UsageDate gets set to mm/1/yyyy programatically) per ProductID - therefore the combination of these three fields is what makes that record unique, and together they serve as the primary key.  I have seen and heard that using multiple fie...

Catching Primary Key Violation on insert error
I've read a few different articticles wrt how the handle this error gracefully. I am thinking of wrapping my sql insert statement in a try catch  and have the catch be something likeIF ( e.ToString() LIKE '% System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_PS_HR_Hrs'. Cannot insert duplicate key in object %'){lable1.text = "Sorry, you already have reported hours for that day, please select anothe rdate" } Is there a better way?TIA Dan  Find out the error number that your Exception is throwing, then trap f...

Validate primary key constraint on item insert.
I have a few fields on a rather large form which act as primary keys. This application will be deployed over an intranet. Auto incrementing numbers are not particularly well suited to this application, so for the most part, are excluded.  I would like to validate that an inserted value is unique.   I have thought about creating a hidden formview, filtering the form for the table in question by the value the user wants to insert.  After that, check that the datacount is 0 then proceed to insert the field, otherwise produce an error message for the user.  &nb...

DetailsView, New Data Inserting and Primary Key
I use a detailsview and a sqldatasource I can insert new datas to the DB table with detailsview (in insert mode) My DB table has a Identity Key which is generated by Sql server I need this key after the data inserted. I implement the DataInserted event but I don't know to reach the new added data's identity key I want to show the created data's primary key to the user. what must I do? please help maybe the easiest way to get that is to add an ouput parameter to the insert statement. By doing that you'll be able to get it from the inserted event of the sqldatasource control.-- R...

Insert data into Access DB then grab the Primary Key
Hello I am trying to insert a row into a table which is in Access database. Once I insert the row, I am trying to grab the Primary Key of that row (Scope Identity). How can I do this? Since I cannot use stored procedures, is there a work around for this in Access database? I am currently using a ASP.NET FormView control to insert the data. Thanks for any help I can get public void AddGebruiker(Gebruiker gebruiker)        {            SqlConnection oConn = new SqlConnection(_connectionString);&...

retrieving value of primary key field after inserting data
Hi guys I have a problem in sql. I'm using asp.net/vb.net to write my web application. I have a table in sql server which has some fields and of course a primary key. my question is that when I add a record to the table how can I get the value of the primary key field which has been set to identity (auto number). >>>>I'm not using stored procedures. thanks You need to use a stored procedure. In that you can say RETURN @@IDENTITY which gives you the primary key of last inserted record. good wayto solve it thanks but it gives you the last records value maybe no...

INSERT data into table that maybe have that primary key already
Hi, I'm not user to inserting data into databases, usually I just read the data.  So I think my problem might be pretty common.I have a table of longitudes, latitudes, city names, and country names.  I set the primary key to be the columns longitude and latitude.   I have a method that generates the user's location and the mentioned data.  So I want to only insert the new data into the database if it is new and unique.  currently if the same user goes to my site, it inserts the data fine the first time and then throws and error the second time because it is inse...

How to insert data into 2 tables (with primary and foreign keys)
I have two tables linked together:- Table 1 has columns Member_No, Name, Age, Hobbies (Member_No is the primary key)- Table 2 has columns  FK_Member_No, FK_Hobbies  (both columns are keys - composite key) Member_No in Table 1 is automatically incremented and i have a web form that captures Name, Age and Hobbies. Hobbies is a listbox that the user can select more than 1 options. When i want to do a save (ie insert), how can it be done.  I suppose, i will need to insert the data into both tables at the same time.  Also, how to handle the Hobbies field if t...

JDBC Oracle-Table Not Synchable Primary Key Constraint Error
I have a JDBC driver (IDM 3.5.1) connecting to an Oracle database (10g). The driver tries to synch data from eDir directly to a table in the Oracle database. The driver starts fine and events are processed fine right up to the point that it tries to actually insert the record into the database. The <add> event fails and the driver shuts down. The log contains the following error: "Table 'XXX.YYYYYY' is not syncable. It must have a primary key constraint." The table has a primary key field and there is a trigger in the table that creates the primary key v...

Web resources about - Primary Key Constraint Error in Multi-User environment Inserting Data - embarcadero.delphi.firedac

Environment - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...


Ian Robert Turnbull pleads not guilty to murdering environment officer
The farmer accused of killing environmental&nbsp;officer Glen Turner has pleaded not guilty to murder.&nbsp;


Planned Parenthood: ‘Extremists Creating a Poisonous Environment That Feeds Domestic Terrorism in This ...
Planned Parenthood of the Rocky Mountain (PPRM) said about the recent shooting, “extremists are creating a poisonous environment..."

Riverbed Survey: App Performance a Challenge in Hybrid IT Environments
Cloud computing brings its share of benefits and complexities, driving the need for greater visibility into app performance for executives and ...

SungardASVoice: Six Critical Risks That Reside In Healthcare IT Environments
... ability to deliver patient care, ensure resiliency, and protect your reputation. View Identifying the Risks that Reside in Healthcare IT Environments ...

“We’re living in an age when humans have modified just about all aspects of our environment, deliber
“We’re living in an age when humans have modified just about all aspects of our environment, deliberately or accidentally,” said developmental ...

After Shooting, Planned Parenthood Blames GOP Rhetoric for 'Toxic Environment'
... Planned Parenthood clinic, Planned Parenthood today accused some of those same GOP candidates of contributing to the creation of a "toxic environment" ...

New SIDS Study Reveals That There’s More To It Than Just The Baby’s Sleeping Environment
A new SIDS study reveals that there’s more to the poorly-understood phenomenon than just the baby’s sleep environment, The Philadelphia Inquirer ...

Resources last updated: 12/10/2015 11:31:54 AM