On 3/29/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
Thanks for your reply.
I'm writing my application in Perl using SQLite as file based database.
Multiple users would be accessing the database simultaneously.But I'll
lock the file prior to any updations to the database. I'm planning to
use a third table to generate ids for the master table. But I don't know
how to select id INTO a variable which can be used while inserting data
into both the tables. Is it possible?
Maybe you are not explaining your problem completely, else, I don't
see what the problem is. Here is some Perl code to do the above
without requiring a third table. You can adjust it accordingly if you
insist on a third table.
# get your values to insert somehow
my $field1 = "my field one";
my $name = "my name";
# enable transactions, if possible
$dbh->{AutoCommit => 0, RaiseError => 1};
eval {
my $sth_master = $dbh->prepare(qq{
INSERT INTO Master ( field1 ) VALUES ( ? )
});
$sth_master->execute( $field1 );
my $master_id = $dbh->func('last_insert_rowid');
my $sth_child = $dbh->prepare(qq{
INSERT INTO Child ( id, name ) VALUES ( ?, ? )
});
$sth_master->execute( $master_id, $name );
# commit the changes if we get this far
$dbh->commit;
};
if ($@) {
warn "Transaction aborted because $@";
# now rollback to undo the incomplete changes
# but do it in an eval{} as it may also fail
eval { $dbh->rollback };
# add other application on-error-clean-up code here
}
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P
Kishor
Sent: 28 March 2007 17:42
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] RE: Maintaining Master-Child relationships
On 3/28/07, Arora, Ajay <[EMAIL PROTECTED]> wrote:
> Can anyone please look into my query,
>
> I've tables
>
> Master ( id integer primary key,
> field1 text)
>
> And
>
> Child (id integer, name text)
>
> My application receive values for field1 and name. I need to populate
> master and child with incoming values using the same id.
>
1. Insert "field1" in Master;
2. Get id of record inserted from #1 above;
3. Insert id from #2 and "name" in Child.
That is about all the help I can provide given that you have not
provide any further context... what language you are using, how you
are developing the application, etc.
SQLite does provide a 'last_insert_rowid' capability. Look on the
sqlite.org website.
if you are 100% sure that no one else will be fiddling with your
database, you can probably use something like
INSERT INTO Child( id, name)
VALUES ( (SELECT Max(id) FROM Master), <name>)
> A quick reply will be highly appreciated.
>
> Regards
> Ajay
>
> -----Original Message-----
> From: Arora, Ajay
> Sent: 28 March 2007 15:04
> To: 'sqlite-users@sqlite.org'
> Subject: Maintaining Master-Child relationships
>
>
> Hi,
>
> I've two tables in my database, a master and a child with ID as a
> common key.
>
> I've created a table with one column to generate the sequence
number.How
> can I insert related records into both the tables using same ID?
>
> Thanks
> Ajay
>
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
---------------------------------------------------------------------
collaborate, communicate, compete
=====================================================================
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
