Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Java > PreparedStatement Insert statement MS SQL Server problem

Reply
Thread Tools

PreparedStatement Insert statement MS SQL Server problem

 
 
ddog
Guest
Posts: n/a
 
      02-07-2007
I'm trying to execute the following insert statement on MS SQL Server
v.8 using their JDBC drivers. I'm unable to insert the last field in
the table for some reason that I don't understand. The code follows:

Connection connection =null;
PreparedStatement pstmt = null;

Integer jobId = value.getJobTypeId();
Integer sequenceNumber = value.getSequenceNumber();
String fieldName = value.getFieldName();
String fieldDescription = value.getFieldDescription();
String fieldIdentifier = value.getFieldIdentifier();
String recordLocator = value.getRecordLocator();
java.sql.Date entryDttm = value.getEntryDttm();
java.sql.Date lastUpdatedDttm = value.getLastUpdatedDttm();


try {
connection = ConnectionManager.getConnection();
String sql = "INSERT INTO RECON_FIELD_IDENTIFIER
VALUES(?,?,?,?,?,?,?,?,?)";

pstmt = connection.prepareStatement(sql);

pstmt.setInt(1, jobId);
pstmt.setInt(2, sequenceNumber);
pstmt.setString(3, fieldName);
pstmt.setString(4, fieldDescription);
pstmt.setString(5, fieldIdentifier);
pstmt.setString(6, recordLocator);
pstmt.setDate(7, entryDttm);
pstmt.setDate(8, lastUpdatedDttm);
pstmt.setNull(9, java.sql.Types.INTEGER); // set value to null -
experiment
// OR pstmt.setNull(9, 1); // valid value for foreign key, fails also

// fails here....
pstmt.executeUpdate();
Error Message:
ReconFieldIndentifierDAO.insertReconFieldIdentifie r() =
[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK__RECON_FIE__FK_RE__239E4DCF'.
The conflict occurred in database 'RECON', table 'RECON_JOB_TYPE',
column 'JOB_TYPE_ID'.
java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]
[SQLServer]
INSERT statement conflicted with
COLUMN FOREIGN KEY constraint
'FK__RECON_FIE__FK_RE__239E4DCF'.
The conflict occurred in database 'RECON', table
'RECON_JOB_TYPE', column 'JOB_TYPE_ID'.

Both of the conflicting fiels are defined as ints.
The FK field in RECON_JOB_TYPE table named FK_RECON_FIELD_IDENTIFIER_1
is defined as (int, null) and accepts null values using straight
INSERT
statements via SQL Query Analyzer. When I try to insert a valid value
for
the foreign key or set it to null as above for an insert, I get the
same error
message.

Any ideas?

 
Reply With Quote
 
 
 
 
Thomas Kellerer
Guest
Posts: n/a
 
      02-07-2007
On 07.02.2007 18:06 ddog wrote:
> Error Message:
> ReconFieldIndentifierDAO.insertReconFieldIdentifie r() =
> [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]
> INSERT statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK__RECON_FIE__FK_RE__239E4DCF'.
> The conflict occurred in database 'RECON', table 'RECON_JOB_TYPE',
> column 'JOB_TYPE_ID'.
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]


This has noting to do with Java.

You are trying to insert a value into the column job_type_id which
obviously does not reference a valid primary key for the related foreign
table.

This is what the message

> INSERT statement conflicted with COLUMN FOREIGN KEY constraint
> 'FK__RECON_FIE__FK_RE__239E4DCF'.


tells you.

Check the values of your insert statement and make sure the referenced
rows exist.

Thomas
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mismatch in Statement and PreparedStatement execution in Oracle DB. Alex Kizub Java 10 02-11-2010 09:20 PM
Difference between Statement and preparedStatement (for SQL databases) ? Ken Philips Java 4 11-05-2008 01:04 AM
PreparedStatement and prewiev SQL query tomas Java 3 09-16-2006 04:14 PM
get SQL statement from PreparedStatement JY Java 11 02-24-2006 05:23 PM
Execution time problem with Insert Query using PreparedStatement Manoj S. P. Java 2 07-02-2003 03:52 PM



Advertisments