Solved

insert data into two tables

Posted on 2006-07-08
44
1,248 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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.

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

623 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