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.

R.

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


Hi,

> 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))
AS BEGIN
INSERT INTO Persistent(id, classtype) VALUES (:id, :classtype);
INSERT INTO Named(persistentId, name) VALUES
(:persistentId, :name);

END

and then you can use following:

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

cs.setInt(1, ...);
...
cs.execute();

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
ADVERTISEMENT




To unsubscribe from this group, send an email to:
Firebird-Java-unsubscribe@yahoogroups.com



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