Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 525
  • Last Modified:

PreparedStatement question

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
zolf
Asked:
zolf
  • 10
  • 9
  • 7
  • +1
2 Solutions
 
CEHJCommented:
Leave out the id from the statement
0
 
CEHJCommented:
PreparedStatement ps = connect.prepareStatement("insert into attachments (file_name,file_path,file_type_id,data) values(attach_id_seq.nextval,?,?,?,?)");
0
 
CEHJCommented:
Sorry typo:

PreparedStatement ps = connect.prepareStatement("insert into attachments (file_name,file_path,file_type_id,data) values(?,?,?,?)");
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
CEHJCommented:
And you need to renumber the parameters
0
 
objectsCommented:
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
 
zolfAuthor Commented:

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
 
objectsCommented:
>                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
 
objectsCommented:
you need to include the id for oracle, try the code I posted above
0
 
zolfAuthor Commented:

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
 
zolfAuthor Commented:

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
 
girionisCommented:
This should work, oracle shoudl autoincrement the field once you insert something in there. Are you still getting the same error?
0
 
objectsCommented:
u still need:

               ps.executeUpdate();

sorry, i just posted the bits that needed to be changed
0
 
girionisCommented:
YOu should leave out the auto increment field, as CEHJ suggested, oracle should do it for you.
0
 
girionisCommented:
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
 
zolfAuthor Commented:

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
 
zolfAuthor Commented:

i get the above error when i run objects code
0
 
girionisCommented:
Try to run my code
0
 
objectsCommented:
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
 
girionisCommented:
> 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
 
zolfAuthor Commented:

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
 
zolfAuthor Commented:

objects i did not understand what you meant.
0
 
zolfAuthor Commented:

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
 
girionisCommented:
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
 
objectsCommented:
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
 
girionisCommented:
Try and insert that number (123) into the attach_file_types first.
0
 
zolfAuthor Commented:

do you mean i need to add some data first into table attach_file_types before inserting into attachments.
0
 
girionisCommented:
Try this manually

INSERT INTO attach_file_types value(123, "a test field");
0
 
objectsCommented:
correct, you can only insert values in your image tanble with types that exist in other table
0
 
girionisCommented:
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
 
zolfAuthor Commented:

you are geneuis,thanks mate.

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 10
  • 9
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now