Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

PreparedStatement question

Posted on 2006-07-03
30
Medium Priority
?
524 Views
Last Modified: 2008-02-20
i get this error,my problem is with auto increment in oracle.
java.sql.SQLException: Invalid column index
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
      at oracle.jdbc.driver.OraclePreparedStatement.setBinaryStreamInternal(OraclePreparedStatement.java:8295)
      at oracle.jdbc.driver.OraclePreparedStatement.setBinaryStream(OraclePreparedStatement.java:8286)
      at src.com.victa.DBImage.dbInsertImage(DBImage.java:59)
      at src.com.victa.DBImage.main(DBImage.java:121)


PreparedStatement ps = connect.prepareStatement("insert into attachments (attach_id,file_name,file_path,file_type_id,data) values(attach_id_seq.nextval,?,?,?,?)");
                  File fileIn = new File("C:/Documents and Settings/My Documents/My Pictures/6804MPC.jpg");
                  int fileLength = (int)fileIn.length();
                  InputStream streamedJpg = new FileInputStream(fileIn);
                  //ps.setInt(1, attach_id.nextval);
                  ps.setString(2, "6804MPC.jpg");
                  ps.setString(3, "C:/Documents and Settings/zulf/My Documents/My Pictures/");
                  ps.setInt(4, 123);
                  ps.setBinaryStream(5,streamedJpg,fileLength);
                  ps.executeUpdate();

this is my table structure
create table attachments (
            attach_id number(10) primary key,
            file_name varchar2(50) not null,
            file_path varchar2(100),
            file_type_id number(10) not null,
            data blob,
            CONSTRAINT file_type_id_fk FOREIGN KEY (file_type_id) REFERENCES attach_file_types (attach_ft_id)
);
0
Comment
Question by:zolf
  • 10
  • 9
  • 7
  • +1
30 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 17029655
Leave out the id from the statement
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 17029657
PreparedStatement ps = connect.prepareStatement("insert into attachments (file_name,file_path,file_type_id,data) values(attach_id_seq.nextval,?,?,?,?)");
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 17029660
Sorry typo:

PreparedStatement ps = connect.prepareStatement("insert into attachments (file_name,file_path,file_type_id,data) values(?,?,?,?)");
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 86

Expert Comment

by:CEHJ
ID: 17029670
And you need to renumber the parameters
0
 
LVL 92

Expert Comment

by:objects
ID: 17029690
As I mentioned in your previous question, you don't need to specify an ootuoincrement filed in your insert.
the database looks after it for you.
0
 

Author Comment

by:zolf
ID: 17029694

when i leave out the primary key it gives me error.this works fine with mysql,but not in case of oracle.please help

PreparedStatement ps = connect.prepareStatement("insert into attachments (file_name,file_path,file_type_id,data) values(?,?,?,?)");
                  File fileIn = new File("C:/Documents and Settings/zulf/My Documents/My Pictures/6804MPC.jpg");
                  int fileLength = (int)fileIn.length();
                  InputStream streamedJpg = new FileInputStream(fileIn);
                  //ps.setInt(1, attach_id.nextval);
                  ps.setString(1, "6804MPC.jpg");
                  ps.setString(2, "C:/Documents and Settings/zulf/My Documents/My Pictures/");
                  ps.setInt(3, 123);
                  ps.setBinaryStream(4,streamedJpg,fileLength);
                  ps.executeUpdate();

java.sql.SQLException: ORA-01400: cannot insert NULL into ("DSL"."ATTACHMENTS"."ATTACH_ID")

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
      at src.com.victa.DBImage.dbInsertImage(DBImage.java:59)
      at src.com.victa.DBImage.main(DBImage.java:120)
0
 
LVL 92

Expert Comment

by:objects
ID: 17029700
>                ps.setBinaryStream(5,streamedJpg,fileLength);

you're getting the error because there are only 4 placeholders defined in your statement
should be more like:

PreparedStatement ps = connect.prepareStatement("insert into attachments (attach_id,file_name,file_path,file_type_id,data) values(attach_id_seq.nextval,?,?,?,?)");
               File fileIn = new File("C:/Documents and Settings/My Documents/My Pictures/6804MPC.jpg");
               int fileLength = (int)fileIn.length();
               InputStream streamedJpg = new FileInputStream(fileIn);
               ps.setString(1, "6804MPC.jpg");
               ps.setString(2, "C:/Documents and Settings/zulf/My Documents/My Pictures/");
               ps.setInt(3, 123);
               ps.setBinaryStream(4,streamedJpg,fileLength);
0
 
LVL 92

Expert Comment

by:objects
ID: 17029708
you need to include the id for oracle, try the code I posted above
0
 

Author Comment

by:zolf
ID: 17029716

i am sorry, typing error,it is like this

PreparedStatement ps = connect.prepareStatement("insert into attachments (file_name,file_path,file_type_id,data) values(?,?,?,?)");
                  File fileIn = new File("C:/Documents and Settings/zulf/My Documents/My Pictures/6804MPC.jpg");
                  int fileLength = (int)fileIn.length();
                  InputStream streamedJpg = new FileInputStream(fileIn);
                  //ps.setInt(1, attach_id.nextval);
                  ps.setString(1, "6804MPC.jpg");
                  ps.setString(2, "C:/Documents and Settings/zulf/My Documents/My Pictures/");
                  ps.setInt(3, 123);
                  ps.setBinaryStream(4,streamedJpg,fileLength);
                  ps.executeUpdate();
0
 

Author Comment

by:zolf
ID: 17029753

When i run your code nothing happens,i mean data is not inserted in db nor do i get any error

PreparedStatement ps = connect.prepareStatement("insert into attachments (attach_id,file_name,file_path,file_type_id,data) values(attach_id_seq.nextval,?,?,?,?)");
            File fileIn = new File("C:/Documents and Settings/zulf/My Documents/My Pictures/6804MPC.jpg");
            int fileLength = (int)fileIn.length();
            InputStream streamedJpg = new FileInputStream(fileIn);
            ps.setString(1, "6804MPC.jpg");
            ps.setString(2, "C:/Documents and Settings/zulf/My Documents/My Pictures/");
            ps.setInt(3, 123);
            ps.setBinaryStream(4,streamedJpg,fileLength);
0
 
LVL 35

Expert Comment

by:girionis
ID: 17029757
This should work, oracle shoudl autoincrement the field once you insert something in there. Are you still getting the same error?
0
 
LVL 92

Expert Comment

by:objects
ID: 17029760
u still need:

               ps.executeUpdate();

sorry, i just posted the bits that needed to be changed
0
 
LVL 35

Expert Comment

by:girionis
ID: 17029762
YOu should leave out the auto increment field, as CEHJ suggested, oracle should do it for you.
0
 
LVL 35

Expert Comment

by:girionis
ID: 17029776
PreparedStatement ps = connect.prepareStatement("insert into attachments (file_name,file_path,file_type_id,data) values(?,?,?,?)");
            File fileIn = new File("C:/Documents and Settings/zulf/My Documents/My Pictures/6804MPC.jpg");
            int fileLength = (int)fileIn.length();
            InputStream streamedJpg = new FileInputStream(fileIn);
            ps.setString(1, "6804MPC.jpg");
            ps.setString(2, "C:/Documents and Settings/zulf/My Documents/My Pictures/");
            ps.setInt(3, 123);
            ps.setBinaryStream(4,streamedJpg,fileLength);
ps.executeUpdate();

should work
0
 

Author Comment

by:zolf
ID: 17029779

java.sql.SQLException: ORA-02291: integrity constraint (DSL.FILE_TYPE_ID_FK) violated - parent key not found

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
      at src.com.victa.DBImage.dbInsertImage(DBImage.java:69)
      at src.com.victa.DBImage.main(DBImage.java:132)
0
 

Author Comment

by:zolf
ID: 17029786

i get the above error when i run objects code
0
 
LVL 35

Expert Comment

by:girionis
ID: 17029792
Try to run my code
0
 
LVL 92

Accepted Solution

by:
objects earned 1800 total points
ID: 17029794
your breaking your constraint

          CONSTRAINT file_type_id_fk FOREIGN KEY (file_type_id) REFERENCES attach_file_types (attach_ft_id)

the attach_file_types would appear to not contain 3
0
 
LVL 35

Expert Comment

by:girionis
ID: 17029797
> ORA-02291: integrity constraint (DSL.FILE_TYPE_ID_FK) violated

This means that you have inserted an inappropriate value in the file_type_id.
0
 

Author Comment

by:zolf
ID: 17029799

girionis

java.sql.SQLException: ORA-01400: cannot insert NULL into ("DSL"."ATTACHMENTS"."ATTACH_ID")

      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
      at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
      at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
      at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:213)
      at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:952)
      at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1160)
      at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3285)
      at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3368)
      at src.com.victa.DBImage.dbInsertImage(DBImage.java:79)
      at src.com.victa.DBImage.main(DBImage.java:143)
0
 

Author Comment

by:zolf
ID: 17029802

objects i did not understand what you meant.
0
 

Author Comment

by:zolf
ID: 17029812

create table attach_file_types (
            attach_ft_id number(10) primary key,
            attach_ft_name varchar2(50) not null
);


create table attachments (
            attach_id number(10) primary key,
            file_name varchar2(50) not null,
            file_path varchar2(100),
            file_type_id number(10) not null,
            data blob,
            CONSTRAINT file_type_id_fk FOREIGN KEY (file_type_id) REFERENCES attach_file_types (attach_ft_id)
);


create sequence attach_ft_id_seq;
create sequence attach_id_seq;
0
 
LVL 35

Expert Comment

by:girionis
ID: 17029813
The file_type_id_fk references the attach_ft_id field and it contains a value (file_type_id_fk) that does not exist in the attach_ft_id. Try to put a value in the third parameter that exists in the attach_ft_id.
0
 
LVL 92

Expert Comment

by:objects
ID: 17029814
In your table definition you define a contraint on the file_type_id that it must contain a matching value in the attach_file_types table. If you don't need this constraint then remove it from table definition.
Otherwise add a row to that table for the type you are using (3), or change the type to one that exists in that table.
0
 
LVL 35

Assisted Solution

by:girionis
girionis earned 200 total points
ID: 17029816
Try and insert that number (123) into the attach_file_types first.
0
 

Author Comment

by:zolf
ID: 17029820

do you mean i need to add some data first into table attach_file_types before inserting into attachments.
0
 
LVL 35

Expert Comment

by:girionis
ID: 17029822
Try this manually

INSERT INTO attach_file_types value(123, "a test field");
0
 
LVL 92

Expert Comment

by:objects
ID: 17029824
correct, you can only insert values in your image tanble with types that exist in other table
0
 
LVL 35

Expert Comment

by:girionis
ID: 17029827
zolf,
> do you mean i need to add some data first into table attach_file_types
> before inserting into attachments.

yes

Sorry typo:

INSERT INTO attach_file_types VALUES(123, "a test field");
0
 

Author Comment

by:zolf
ID: 17029831

you are geneuis,thanks mate.

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This video teaches viewers about errors in exception handling.
Suggested Courses

972 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