The main reason, and maybe it is not a good one, is that I have an inheritence structure for my Java objects that uses a different TABLE for each subclass. Each object has a list of column names that map to field names. An object may have methods like this:


class Persistent {
void writeColumns( ColumnValues values )
{
values.setInt( "id", this.getIdentity();
values.setString( "classtype", this.getClassType() );
}

void readColumns( ColumnValues values )
{
this.setIdentity( values.getInt( "id" ) );
this.setClassType( values.getString( "classtype" ) );
}

...

class NamedPersistent extends Persistent {
void writeColumns( ColumnValues values )
{
super.writeColumns( values );

values.setInt( "persistentId", this.getIdentity();
values.setString( "name", this.getName() );
}

void readColumns( ColumnValues values )
{
super.readColumns( values );
this.setName( values.getString( "name" ) );
}


When the column names are registered, they are given position indices. This is a pretty fast light weight mapping. Queries are easy, since all the column names will be in the appropriate order that the query asked for.

"SELECT " + cols.asCommaSeparatedList() + " FROM " + froms.asCommaSeparatedList() + whereCriteria + ";";

But I couldn't figure out how to do it with INSERT and UPDATE without making a stored procedure and listing the column names as the stored procedure's parameters.

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.