Start Free Trial

asked on

insert data into two tables

hello there,

i have a table into which i insert new data like this
PreparedStatement ps = connect.prepareStatement("INSERT INTO attachments (attach_id,file_name,file_path,file_type_id,data) VALUES(attach_id_seq.nextval,?,?,?,?)");

and also want to insert the pk of this table i.e attach_id which is a seq in oracle 10g into another table as foreign key.the foreign is attach_id in this table.How can i get acheive it.please help.

CREATE TABLE "DSL"."CITY"
(      "CITY_ID" NUMBER(10,0) NOT NULL ENABLE,
      "CITY_ENG_NAME" VARCHAR2(50 BYTE),
      "CITY_FARSI_NAME" VARCHAR2(50 BYTE),
      "PREFIX" VARCHAR2(10 BYTE),
      "ATTACH_ID" NUMBER(10,0),
       CONSTRAINT "CITY_ID_PK" PRIMARY KEY ("CITY_ID") ENABLE
) ;

How is table 'attachments' defined?

ASKER

TABLE ATTACHMENTS
(      "ATTACH_ID" NUMBER(10,0),
      "FILE_NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,
      "FILE_PATH" VARCHAR2(100 BYTE),
      "FILE_TYPE" VARCHAR2(5 BYTE),
      "DATA" BLOB,
       PRIMARY KEY ("ATTACH_ID") ENABLE
) ;

There are actually no foreign key constraints defined between the two

mukundha_expert

in the second table use attach_id_seq.currval

ASKER

let me explain again,when i insert new data into attachments,the attach_id gets a new seq value,this value i want to insert into city table,so they have a relationship

ASKER

mukundha can you explain little more what you mean

You can use

http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html#getGeneratedKeys()

after insert into attachments. If there are foreign key relationships, they should be defined though

ASKER

you mean like this

Connection connect = frame.getDBConnection();
                  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(filePath);
int fileLength = (int)fileIn.length();
InputStream streamedJpg = new FileInputStream(fileIn);
ps.setString(1, fileName);
ps.setString(2, fileParent);
ps.setString(3, fileExt);
ps.setBinaryStream(4,streamedJpg,fileLength);
ps.executeUpdate();

String query = "INSERT INTO city (attach_id) VALUES (attach_id_seq.currval)";

                  ps.executeUpdate(query);

>>String query = "INSERT INTO city (attach_id) VALUES (attach_id_seq.currval)";

No. That will simply generate a new id. Use the method to which i posted a link

ASKER CERTIFIED SOLUTION

mukundha_expert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mukundha_expert

>> attach_id_seq.currval

it will return the current value of the sequence,

>> attach_id_seq.nextval

this only will generate a new ID

mukundha_expert

>>you mean like this

yes

... although personally i'd feel safer knowing the value that's now actually sitting in attachments because of transactional/concurrency issues

ASKER

this is what i have done,will this work. for the second insert i dont need to use setInt am i right
Connection connect = frame.getDBConnection();
                  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(filePath);
int fileLength = (int)fileIn.length();
InputStream streamedJpg = new FileInputStream(fileIn);
ps.setString(1, fileName);
ps.setString(2, fileParent);
ps.setString(3, fileExt);
ps.setBinaryStream(4,streamedJpg,fileLength);
ps.executeUpdate();
//second insert
ps = connect.prepareStatement("insert into city (attach_id) values (attach_id_seq.currval ) where city_id="+pk) ;
ps.executeUpdate();

ASKER

cehj according to you what should i do.

mukundha_expert

>> cehj according to you what should i do.

ps.executeUpdate();

Resultset rs = ps.getGeneratedKeys() ;

if ( rs.next () )
String attachid = rs.get("attach_id_seq");
"insert into city ( attach ) values (' "+attachid + '" ' )"

mukundha_expert

>> this is what i have done,will this work. for the second insert i dont need to use setInt am i right

ya it will work, it might fail in some case where a insert into the attchaments takes place before u insert into the city table.

CEHJ is safer

mukundha_expert, what are the transactional/concurrency implications of nextval/currval?

mukundha_expert

>> what are the transactional/concurrency implications of nextval/currval?

i am not getting what you really mean by that..

anyways nextval/currval might fail if there are two threads executing this method,

the first thread inserts the value in the attachments table
now attachID.currval --> 1 ( say ),

before the first thread could insert into city table, if the second thread tries to insert to the attachments table,

now attchID.currval --> 2,

so later when both these thread insert into table city, the attachID for both the entries will be 2

mukundha_expert

>> PreparedStatement ps = connect.prepareStatement("INSERT INTO attachments
>> (attach_id,file_name,file_path,file_type_id,data) VALUES(attach_id_seq.nextval,?,?,?,?)");

one problem with the sequence is even if the query fails to execute, the sequence will get its next value.

rollback will not work here, we have to drop the seqeunce and recreate it starting with its previous value

ASKER

so i have changed my code to this,please check if it is correct to run.

try
{
                  Connection connect = frame.getDBConnection();
                  PreparedStatement ps = connect.prepareStatement("INSERT INTO attachments (attach_id,file_name,file_path,file_type,data) VALUES(attach_id_seq.nextval,?,?,?,?)");

File fileIn = new File(filePath);
int fileLength = (int)fileIn.length();
InputStream streamedJpg = new FileInputStream(fileIn);
ps.setString(1, fileName);
ps.setString(2, fileParent);
ps.setString(3, fileExt);
ps.setBinaryStream(4,streamedJpg,fileLength);
ps.executeUpdate();

ResultSet rs = ps.getGeneratedKeys() ;
Statement st = connect.createStatement();

if ( rs.next () )
{
int attachid = rs.getInt("attach_id_seq");
String query = "insert into city ( attach_id ) values ("+attachid + " ) where city_id="+pk ;
st.executeUpdate(query);
}

>>anyways nextval/currval might fail if there are two threads executing this method

That's the sort of thing i meant, as well as other *processes*

mukundha_expert

>> " values ( ' "+attachid + " ' ) where

ASKER

so i there not auto-increment feature in oracle like we have in mysql which is so easy to work with.i tried searhing a lot ablout it but could not get any

mukundha_expert

attachID will be in single quotes... other wise its k i think

>>so i there not auto-increment feature in oracle like we have in mysql which is so easy to work with.i

You'd still have the same problem with an auto-increment field - in fact it would be worse since the possibility of currval doesn't even exist

ASKER

i get this error on like 101 which is ResultSet rs = ps.getGeneratedKeys() ;

java.sql.SQLException: operation not allowed
      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.OracleStatement.getGeneratedKeys(OracleStatement.java:4559)
      at src.com.victa.DBImage.dbInsertImage(DBImage.java:101)
      at src.com.victa.CityFileChooser.actionPerformed(CityFileChooser.java:120)
      at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1786)
      at javax.swing.AbstractButton$ForwardActionEvents.actionPerformed(AbstractButton.java:1839)
      at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:420)
      at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:258)
      at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:245)
      at java.awt.Component.processMouseEvent(Component.java:5100)
      at java.awt.Component.processEvent(Component.java:4897)
      at java.awt.Container.processEvent(Container.java:1569)
      at java.awt.Component.dispatchEventImpl(Component.java:3615)
      at java.awt.Container.dispatchEventImpl(Container.java:1627)
      at java.awt.Component.dispatchEvent(Component.java:3477)
      at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:3483)
      at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3198)
      at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3128)
      at java.awt.Container.dispatchEventImpl(Container.java:1613)
      at java.awt.Window.dispatchEventImpl(Window.java:1606)
      at java.awt.Component.dispatchEvent(Component.java:3477)
      at java.awt.EventQueue.dispatchEvent(EventQueue.java:456)
      at java.awt.EventDispatchThread.pumpOneEventForHierarchy(EventDispatchThread.java:201)
      at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:151)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:145)
      at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:137)
      at java.awt.EventDispatchThread.run(EventDispatchThread.java:100)

mukundha_expert

auto-increment feature will work and its lot better when everything goes fine,

if anything goes wrong you will also face problems..

ASKER

so what is the way out of this??????????

mukundha_expert

might be problem with permissions or lock,

try using currval,

File fileIn = new File(filePath);
int fileLength = (int)fileIn.length();
InputStream streamedJpg = new FileInputStream(fileIn);
ps.setString(1, fileName);
ps.setString(2, fileParent);
ps.setString(3, fileExt);
ps.setBinaryStream(4,streamedJpg,fileLength);
ps.executeUpdate();
//second insert
ps = connect.prepareStatement("insert into city (attach_id) values (attach_id_seq.currval ) where city_id="+pk) ;
ps.executeUpdate();

it will work, but will fail in the scenorios i said before

ASKER

now i get this error on this line ps.executeUpdate();

java.sql.SQLException: ORA-00933: SQL command not properly ended

mukundha_expert

try,
ps = connect.prepareStatement("insert into city (attach_id) values (attach_id_seq.currval ) where city_id= ' "+pk + " ' " ) ;

ASKER

this is the code i ran

try
{
                  Connection connect = frame.getDBConnection();
                  PreparedStatement ps = connect.prepareStatement("INSERT INTO attachments (attach_id,file_name,file_path,file_type,data) VALUES(attach_id_seq.nextval,?,?,?,?)");

File fileIn = new File(filePath);
int fileLength = (int)fileIn.length();
InputStream streamedJpg = new FileInputStream(fileIn);
ps.setString(1, fileName);
ps.setString(2, fileParent);
ps.setString(3, fileExt);
ps.setBinaryStream(4,streamedJpg,fileLength);
ps.executeUpdate();

ps = connect.prepareStatement("insert into city (attach_id) values (attach_id_seq.currval ) where city_id="+pk) ;
ps.executeUpdate();

ASKER

no, i still that that error

ASKER

i think that is because i am trying to use WHERE with INSERT,should'nt it be UPDATE instead of INSERT????????

mukundha_expert

where you are getting the error, in the first update or the second one

ASKER

second

Can't think why getGeneratedKeys caused an exception

ASKER

it is getting fustrating to get this thing to work,please help

Use two separate PreparedStatements and close the first one first

ASKER

thankyou everybody,it was that insert and update thingy which i mentioned about.it is working.hope i dont get any bugs later in my application.

thanks again.

cheers
zolf

mukundha_expert

ya it should be update

update city
attach = attachod.currval where cityid = pid,

sorry did t notice that

:-)