Problem with duplicated keys while inserting in database

acq

• March 25, 2008 00:00

• Answered

Follow

Hi,
I found some problems when inserting data in DB_OUTPUT_TABLE.
I post here my graph and my test files.
When inserting say 3 records, with first record with duplicated primary key, I got a DB error for all 3 records (they are rejected as duplicated) but I expect only error on wrong record.

I encountered this problem only with MS SQLServer2005, while on Oracle 10g it works properly.
Is this a possible fault or configuration problem?
Any answer would be really appreciated...
Thanks.


Sample Graph:
<?xml version="1.0" encoding="UTF-8"?>
<Graph created="Fri Mar 21 16:56:02 CET 2008" guiVersion="1.8" licenseType="Evaluation license." modified="Fri Mar 21 17:52:36 CET 2008" name="Test" revision="1.10">
<Global>
<Metadata id="Metadata0">
<Record name="defaultRecordName" recordSize="-1" type="delimited">
<Field delimiter=";" name="Field0" nullable="true" shift="0" type="string"/>
<Field delimiter=";" name="Field1" nullable="true" shift="0" type="string"/>
<Field delimiter="\r\n" name="Field2" nullable="true" shift="0" type="string"/>
</Record>
</Metadata>
<Metadata id="Metadata1">
<Record name="defaultRecordNameErr" recordSize="-1" type="delimited">
<Field delimiter=";" name="Field0" nullable="true" shift="0" type="string"/>
<Field delimiter=";" name="Field1" nullable="true" shift="0" type="string"/>
<Field delimiter=";" name="Field2" nullable="true" shift="0" type="string"/>
<Field delimiter="\r\n" name="Field4" nullable="true" shift="0" type="string"/>
</Record>
</Metadata>
<Connection dbDriver="${dbDriver}" dbURL="${dbURL}" user="${user}" password="${password}" id="DBConnection0" type="JDBC"/>
</Global>
<Phase number="0">
<Node batchMode="true" batchSize="1" commit="1" dbConnection="Connection0" dbTable="test" enabled="enabled" fieldMap="Field0=A;Field1=B;Field2=C" guiHeight="0" guiName="DB Output Table" guiWidth="0" guiX="502" guiY="229" id="DB_OUTPUT_TABLE0" maxErrors="-1" type="DB_OUTPUT_TABLE"/>
<Node enabled="enabled" fileURL="C:/Test.txt" guiHeight="0" guiName="Delimited Data Reader" guiWidth="0" guiX="210" guiY="241" id="DELIMITED_DATA_READER0" type="DELIMITED_DATA_READER"/>
<Node debugFilename="C:/TestERROR.txt" debugPrint="true" enabled="enabled" guiHeight="0" guiName="Trash" guiWidth="0" guiX="752" guiY="286" id="TRASH0" type="TRASH"/>
<Edge fromNode="DB_OUTPUT_TABLE0:0" guiBendpoints="" id="Edge1" inPort="Port 0 (in)" metadata="Metadata1" outPort="Port 0 (rejected)" toNode="TRASH0:0"/>
<Edge fromNode="DELIMITED_DATA_READER0:0" guiBendpoints="" id="Edge0" inPort="Port 0 (in)" metadata="Metadata0" outPort="Port 0 (out)" toNode="DB_OUTPUT_TABLE0:0"/>
</Phase>
</Graph>

Sample Data:
(Run 1, correct)
00000;zxc;qa1

(Run 2, all 3 line rejected instead of first)
00000;zxc;qa1
11111;abc;qwe
22222;zxc;qa1

• 

  avackova

  • March 25, 2008 13:26

  0

  Comment actions Permalink

  What is in error message?
• 

  acq

  • March 25, 2008 14:58

  0

  Comment actions Permalink

  The error message is the standard database error for duplicated keys:
  "Violation of PRIMARY KEY constraint 'PK_test'. Cannot insert duplicate key in object 'dbo.test'."
  The matter is that I got the same message also for correct records, which are not inserted in database.
  It seems that after first error, every record looks like duplicated.
  Thanks
• 

  avackova

  • March 26, 2008 12:27

  0

  Comment actions Permalink

  Pls check you database table definition. I can't replicate your problem (I have only one record rejected).

Please sign in to leave a comment.