Subject RE: [Firebird-Java] Re: INSERT INTO Multiple Tables
Author Robert DiFalco
Also, I should probably send this to ibsupport, but while I have you, is it still true in Firebird that one cannot have a parameter to a stored procedure that is a blob? Ugh.


-----Original Message-----
From: Roman Rokytskyy [mailto:rrokytskyy@...]
Sent: Monday, March 24, 2003 1:44 PM
Subject: [Firebird-Java] Re: INSERT INTO Multiple Tables


> Sorry for the naïve question, but is it possible to insert into
> multiple tables using a single statement?
> Say I want to do the following:
> PreparedStatement ps = con.prepareStatement(
> "INSERT INTO Persistent(id,classtype) VALUES (?,?); " +
> "INSERT INTO Named(persistentId,name) VALUES (?,?)" );
> ps.setInt( 1, id );
> ps.setString( 2, classtype );
> ps.setInt( 3, id );
> ps.setString( 4, name );
> Note that I want the parameters to be positioned 1,2,3, and 4.

Not with INSERT statement. However you can create stored procedure,
and use it:

CREATE PROCEDURE my_insert_procedure(id INTEGER, classtype VARCHAR
(20), persistentId INTEGER, name VARCHAR(20))
INSERT INTO Persistent(id, classtype) VALUES (:id, :classtype);
INSERT INTO Named(persistentId, name) VALUES
(:persistentId, :name);


and then you can use following:

CallableStatement cs =
connection.prepareCall("{call my_insert_procedure(?, ?, ?, ?)}");

cs.setInt(1, ...);

But why do you want to use one statement to insert into two tables?
The only reason I can imagine is having something similar to nested
transation where data are inserted either into two tables or into
none. If this is the case, stored procedure is what you need.

Best regards,
Roman Rokytskyy

Yahoo! Groups Sponsor

To unsubscribe from this group, send an email to:

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.