Solved

PreparedStatement question

Posted on 2006-07-03
30
517 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 450 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 50 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Impossible to extract MSI from new JAVA releases 2 48
Java Jpanels and Jframe 8 28
Java - Why doesn't this JFrame work 3 42
even odd program using while loop 3 28
For customizing the look of your lightweight component and making it look lucid like it was made of glass. Or: how to make your component more Apple-ish ;) This tip assumes your component to be of rectangular shape and completely opaque. (COD…
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

910 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

21 Experts available now in Live!

Get 1:1 Help Now