[sql]

[rajesh kalluri <ra...@yahoo.com>]

 
 rajesh kalluri <ra...@yahoo.com> wrote:
1) Hi, we are trying to use dyna-beans for domain objects and are looking for any utils to easily store them into a database (Commons- SQL seemed to be the closest with its support for dyna beans)

2)I am trying to think how commons-sql can be used to insert a dynabean into a table and return the identity column inserted in one statement so that it can be used as a foreign key reference to insert several other rows.

3) Taking the example provided with commons- sql for these two tables

create table author

(

author_id INTEGER NOT NULL ,

name VARCHAR (50) NOT NULL ,

organisation VARCHAR (50) NULL ,

PRIMARY KEY (author_id)

);



create table book

(

book_id INTEGER NOT NULL IDENTITY,

isbn VARCHAR (15) NOT NULL ,

author_id INTEGER NOT NULL ,

title VARCHAR (255) DEFAULT 'N/A' NOT NULL ,

FOREIGN KEY (author_id)

REFERENCES author (author_id)

);

The code to insert dyna-beans to this table

- insert author

author = dynaSql.newInstance("author");

author.set("author_id", new Integer(2)); 

-- is there a way to return this column from the db on insert of the author dyna-bean so that it can used in book inserts.

author.set("name", "Ian Rankin");





dynaSql.insert(author);



-- insert a book



DynaBean book = dynaSql.newInstance("book");



book.set("author_id", new Integer(1));

book.set("isbn", "ISBN-ABCDEF");

book.set("title", "The Importance of being Earnest");

dynaSql.insert(book);

-----



book = dynaSql.newInstance("book");

book.set("author_id", new Integer(2));

book.set("isbn", "ISBN-XYZ");

book.set("title", "The Hanging Garden");

dynaSql.insert(book);

Questions:

Is there a way to return this column from the db on insert of the author dyna-bean so that it can used in book inserts.

Something similato this in SQL : 

INSERT INTO author (col1, col2, col3) VALUES (?, ?, ?)
CALL IDENTITY()

Also is there a way to insert a collection of Dyna-Beans into a db like a batch insert with common-sql.

Thanks again for your time and energy.





---------------------------------
Do you Yahoo!?
Yahoo! Web Hosting - establish your business online


---------------------------------
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!