Solved

insert data into two tables

Posted on 2006-07-08
44
1,222 Views
Last Modified: 2007-12-19

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
   ) ;
0
Comment
Question by:zolf
  • 17
  • 15
  • 12
44 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 17064060
How is table 'attachments' defined?
0
 

Author Comment

by:zolf
ID: 17064072
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 17064106
There are actually no foreign key constraints defined between the two
0
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 17064125
in the second table use attach_id_seq.currval
0
 

Author Comment

by:zolf
ID: 17064127

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
 

Author Comment

by:zolf
ID: 17064128

mukundha can you explain little more what you mean
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 17064136
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
 

Author Comment

by:zolf
ID: 17064152

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
 
LVL 86

Expert Comment

by:CEHJ
ID: 17064157
>>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
 
LVL 10

Accepted Solution

by:
mukundha_expert earned 290 total points
ID: 17064158
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
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 210 total points
ID: 17064163
Sorry - misread it. You *can* do that
0
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 17064165
>> 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
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 17064169
>>you mean like this

yes
0
 
LVL 86

Expert Comment

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

Author Comment

by:zolf
ID: 17064205
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
 

Author Comment

by:zolf
ID: 17064207

cehj according to you what should i do.
0
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 17064216
>> 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
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 17064217
>> 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
 
LVL 86

Expert Comment

by:CEHJ
ID: 17064220
mukundha_expert, what are the transactional/concurrency implications of nextval/currval?
0
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 17064235
>> 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
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 17064243
>>   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
 

Author Comment

by:zolf
ID: 17064245

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 86

Expert Comment

by:CEHJ
ID: 17064247
>>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
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 17064248
>> " values ( ' "+attachid + " ' ) where
0
 

Author Comment

by:zolf
ID: 17064249

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
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 17064250
attachID will be in single quotes... other wise its k i think
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 17064255
>>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
 

Author Comment

by:zolf
ID: 17064256

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
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 17064257
auto-increment feature will work and its lot better when everything goes fine,

if anything goes wrong you will also face problems..
0
 

Author Comment

by:zolf
ID: 17064260

so what is the way out of this??????????
0
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 17064267
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
 

Author Comment

by:zolf
ID: 17064280

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

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

Expert Comment

by:mukundha_expert
ID: 17064290
try,
   ps = connect.prepareStatement("insert into city (attach_id) values (attach_id_seq.currval ) where city_id= ' "+pk + " ' " ) ;
0
 

Author Comment

by:zolf
ID: 17064294
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
 

Author Comment

by:zolf
ID: 17064296

no, i still that that error
0
 

Author Comment

by:zolf
ID: 17064303

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

Expert Comment

by:mukundha_expert
ID: 17064305
where you are getting the error, in the first update or the second one
0
 

Author Comment

by:zolf
ID: 17064306

second
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 17064307
Can't think why getGeneratedKeys caused an exception
0
 

Author Comment

by:zolf
ID: 17064311

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

Expert Comment

by:CEHJ
ID: 17064332
Use two separate PreparedStatements and close the first one first
0
 

Author Comment

by:zolf
ID: 17064334


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
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 17064339
ya it should be update

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

sorry did t notice that
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 17064349
:-)
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
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:
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

708 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

13 Experts available now in Live!

Get 1:1 Help Now