Zolf
asked on
PreparedStatement question
i get this error,my problem is with auto increment in oracle.
java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.Databas eError.thr owSqlExcep tion(Datab aseError.j ava:112)
at oracle.jdbc.driver.Databas eError.thr owSqlExcep tion(Datab aseError.j ava:146)
at oracle.jdbc.driver.Databas eError.thr owSqlExcep tion(Datab aseError.j ava:208)
at oracle.jdbc.driver.OracleP reparedSta tement.set BinaryStre amInternal (OraclePre paredState ment.java: 8295)
at oracle.jdbc.driver.OracleP reparedSta tement.set BinaryStre am(OracleP reparedSta tement.jav a:8286)
at src.com.victa.DBImage.dbIn sertImage( DBImage.ja va:59)
at src.com.victa.DBImage.main (DBImage.j ava:121)
PreparedStatement ps = connect.prepareStatement(" insert into attachments (attach_id,file_name,file_ path,file_ type_id,da ta) values(attach_id_seq.nextv al,?,?,?,? )");
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,strea medJpg,fil eLength);
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)
);
java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.Databas
at oracle.jdbc.driver.Databas
at oracle.jdbc.driver.Databas
at oracle.jdbc.driver.OracleP
at oracle.jdbc.driver.OracleP
at src.com.victa.DBImage.dbIn
at src.com.victa.DBImage.main
PreparedStatement ps = connect.prepareStatement("
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,strea
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)
);
Leave out the id from the statement
PreparedStatement ps = connect.prepareStatement(" insert into attachments (file_name,file_path,file_ type_id,da ta) values(attach_id_seq.nextv al,?,?,?,? )");
Sorry typo:
PreparedStatement ps = connect.prepareStatement(" insert into attachments (file_name,file_path,file_ type_id,da ta) values(?,?,?,?)");
PreparedStatement ps = connect.prepareStatement("
And you need to renumber the parameters
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.
the database looks after it for you.
ASKER
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("
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,strea
ps.executeUpdate();
java.sql.SQLException: ORA-01400: cannot insert NULL into ("DSL"."ATTACHMENTS"."ATTA
at oracle.jdbc.driver.Databas
at oracle.jdbc.driver.T4CTTIo
at oracle.jdbc.driver.T4CTTIo
at oracle.jdbc.driver.T4C8Oal
at oracle.jdbc.driver.T4CPrep
at oracle.jdbc.driver.T4CPrep
at oracle.jdbc.driver.OracleS
at oracle.jdbc.driver.OracleP
at oracle.jdbc.driver.OracleP
at src.com.victa.DBImage.dbIn
at src.com.victa.DBImage.main
> ps.setBinaryStream(5,strea medJpg,fil eLength);
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,da ta) values(attach_id_seq.nextv al,?,?,?,? )");
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,strea medJpg,fil eLength);
you're getting the error because there are only 4 placeholders defined in your statement
should be more like:
PreparedStatement ps = connect.prepareStatement("
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,strea
you need to include the id for oracle, try the code I posted above
ASKER
i am sorry, typing error,it is like this
PreparedStatement ps = connect.prepareStatement("
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,strea
ps.executeUpdate();
ASKER
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("
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,strea
This should work, oracle shoudl autoincrement the field once you insert something in there. Are you still getting the same error?
u still need:
ps.executeUpdate();
sorry, i just posted the bits that needed to be changed
ps.executeUpdate();
sorry, i just posted the bits that needed to be changed
YOu should leave out the auto increment field, as CEHJ suggested, oracle should do it for you.
PreparedStatement ps = connect.prepareStatement(" insert into attachments (file_name,file_path,file_ type_id,da ta) 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,strea medJpg,fil eLength);
ps.executeUpdate();
should work
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,strea
ps.executeUpdate();
should work
ASKER
java.sql.SQLException: ORA-02291: integrity constraint (DSL.FILE_TYPE_ID_FK) violated - parent key not found
at oracle.jdbc.driver.Databas
at oracle.jdbc.driver.T4CTTIo
at oracle.jdbc.driver.T4CTTIo
at oracle.jdbc.driver.T4C8Oal
at oracle.jdbc.driver.T4CPrep
at oracle.jdbc.driver.T4CPrep
at oracle.jdbc.driver.OracleS
at oracle.jdbc.driver.OracleP
at oracle.jdbc.driver.OracleP
at src.com.victa.DBImage.dbIn
at src.com.victa.DBImage.main
ASKER
i get the above error when i run objects code
Try to run my code
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
> ORA-02291: integrity constraint (DSL.FILE_TYPE_ID_FK) violated
This means that you have inserted an inappropriate value in the file_type_id.
This means that you have inserted an inappropriate value in the file_type_id.
ASKER
girionis
java.sql.SQLException: ORA-01400: cannot insert NULL into ("DSL"."ATTACHMENTS"."ATTA
at oracle.jdbc.driver.Databas
at oracle.jdbc.driver.T4CTTIo
at oracle.jdbc.driver.T4CTTIo
at oracle.jdbc.driver.T4C8Oal
at oracle.jdbc.driver.T4CPrep
at oracle.jdbc.driver.T4CPrep
at oracle.jdbc.driver.OracleS
at oracle.jdbc.driver.OracleP
at oracle.jdbc.driver.OracleP
at src.com.victa.DBImage.dbIn
at src.com.victa.DBImage.main
ASKER
objects i did not understand what you meant.
ASKER
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;
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.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
do you mean i need to add some data first into table attach_file_types before inserting into attachments.
Try this manually
INSERT INTO attach_file_types value(123, "a test field");
INSERT INTO attach_file_types value(123, "a test field");
correct, you can only insert values in your image tanble with types that exist in other table
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");
> 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");
ASKER
you are geneuis,thanks mate.