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
   ) ;
zolfAsked:
Who is Participating?
 
mukundha_expertConnect With a Mentor Commented:
in the first table you insert the value like this,

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


in the second table,

PreparedStatement ps = connect.prepareStatement("insert into city values ( ?, ? ,? , ? ,attach_id_seq.currval ) ") ;

0
 
CEHJCommented:
How is table 'attachments' defined?
0
 
zolfAuthor Commented:
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
   ) ;
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
CEHJCommented:
There are actually no foreign key constraints defined between the two
0
 
mukundha_expertCommented:
in the second table use attach_id_seq.currval
0
 
zolfAuthor Commented:

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

mukundha can you explain little more what you mean
0
 
CEHJCommented:
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
0
 
zolfAuthor Commented:

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);
0
 
CEHJCommented:
>>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
0
 
CEHJConnect With a Mentor Commented:
Sorry - misread it. You *can* do that
0
 
mukundha_expertCommented:
>> attach_id_seq.currval

it will return the current value of the sequence,

>> attach_id_seq.nextval

this only will generate a new ID
0
 
mukundha_expertCommented:
>>you mean like this

yes
0
 
CEHJCommented:
... although personally i'd feel safer knowing the value that's now actually sitting in attachments because of transactional/concurrency issues
0
 
zolfAuthor Commented:
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();
0
 
zolfAuthor Commented:

cehj according to you what should i do.
0
 
mukundha_expertCommented:
>> 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 + '" ' )"
0
 
mukundha_expertCommented:
>> 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
0
 
CEHJCommented:
mukundha_expert, what are the transactional/concurrency implications of nextval/currval?
0
 
mukundha_expertCommented:
>> 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
0
 
mukundha_expertCommented:
>>   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
0
 
zolfAuthor Commented:

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);
            }
0
 
CEHJCommented:
>>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*
0
 
mukundha_expertCommented:
>> " values ( ' "+attachid + " ' ) where
0
 
zolfAuthor Commented:

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
0
 
mukundha_expertCommented:
attachID will be in single quotes... other wise its k i think
0
 
CEHJCommented:
>>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
0
 
zolfAuthor Commented:

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)
0
 
mukundha_expertCommented:
auto-increment feature will work and its lot better when everything goes fine,

if anything goes wrong you will also face problems..
0
 
zolfAuthor Commented:

so what is the way out of this??????????
0
 
mukundha_expertCommented:
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
0
 
zolfAuthor Commented:

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

java.sql.SQLException: ORA-00933: SQL command not properly ended
0
 
mukundha_expertCommented:
try,
   ps = connect.prepareStatement("insert into city (attach_id) values (attach_id_seq.currval ) where city_id= ' "+pk + " ' " ) ;
0
 
zolfAuthor Commented:
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();
0
 
zolfAuthor Commented:

no, i still that that error
0
 
zolfAuthor Commented:

i think that is because i am trying to use WHERE with INSERT,should'nt it be UPDATE instead of INSERT????????
0
 
mukundha_expertCommented:
where you are getting the error, in the first update or the second one
0
 
zolfAuthor Commented:

second
0
 
CEHJCommented:
Can't think why getGeneratedKeys caused an exception
0
 
zolfAuthor Commented:

it is getting fustrating to get this thing to work,please help
0
 
CEHJCommented:
Use two separate PreparedStatements and close the first one first
0
 
zolfAuthor Commented:


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
0
 
mukundha_expertCommented:
ya it should be update

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

sorry did t notice that
0
 
CEHJCommented:
:-)
0
All Courses

From novice to tech pro — start learning today.