Insert value in related tables

Hello,

I have two related tables : Reports and Lines that are linked (a report may
be several line long). So Lines table contains a field called KeyReport, and
Reports contains an auto generated key called KeyReport also.

Now I want to insert a new report. I insert an entry in the reports table,
and then I want to insert values in the Lines table. Is there a standard SQL
way to insert the Lines with the good KeyReport ?

I'm using the odbc_ functions, but may have to change the database (so I may
have to use the my sql functions, or any other I dont know ?) so that's why
I would like something as portable as possible...

--
Vincent

On Sat, 6 Nov 2004 15:54:59 -0000, "Vincent Lascaux" <nospam@nospam.org> wro
te:

>I have two related tables : Reports and Lines that are linked (a report may
>be several line long). So Lines table contains a field called KeyReport, an
d
>Reports contains an auto generated key called KeyReport also.
>
>Now I want to insert a new report. I insert an entry in the reports table,
>and then I want to insert values in the Lines table. Is there a standard SQ
L
>way to insert the Lines with the good KeyReport ?
>
>I'm using the odbc_ functions, but may have to change the database (so I ma
y
>have to use the my sql functions, or any other I dont know ?) so that's why
>I would like something as portable as possible...

If you're after the autogenerated key value, that's could be tricky to do
portably since automatically generated keys work in widely different ways
between databases, and I can't spot anything in the ODBC extension that
abstracts that into a common call. This is probably a place where you will h
ave
to put some database-specific code in.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

> If you're after the autogenerated key value, that's could be tricky to do
> portably since automatically generated keys work in widely different ways
> between databases, and I can't spot anything in the ODBC extension that
> abstracts that into a common call. This is probably a place where you will
> have
> to put some database-specific code in.

Could you point me to some document on how to do this with the standard
databases (SQL Server, Oracle, MySQL...) ?

Thank you

--
Vincent

Vincent Lascaux wrote:
> Hello,
>
> I have two related tables : Reports and Lines that are linked (a report ma
y
> be several line long). So Lines table contains a field called KeyReport, a
nd
> Reports contains an auto generated key called KeyReport also.
>
> Now I want to insert a new report. I insert an entry in the reports table,
> and then I want to insert values in the Lines table. Is there a standard S
QL
> way to insert the Lines with the good KeyReport ?
>
> I'm using the odbc_ functions, but may have to change the database (so I m
ay
> have to use the my sql functions, or any other I dont know ?) so that's wh
y
> I would like something as portable as possible...
>

Its going to have to be database specific.  I believe you can retrieve
the ID from a newly insert MySQL row as a variable and then use that in
your remaining insert statements.  Oracle on the other hand has a
sequence that you can either select from to get a key value and then
apply it to all appropriate records however if you are using a trigger
to apply the key value to inserted records automatically then your going
to have to work a little harder.

J

JAS wrote:
>
> Its going to have to be database specific.  I believe you can retrieve
> the ID from a newly insert MySQL row as a variable and then use that in
> your remaining insert statements.  Oracle on the other hand has a
> sequence that you can either select from to get a key value and then
> apply it to all appropriate records however if you are using a trigger
> to apply the key value to inserted records automatically then your going
> to have to work a little harder.
>
> J

To add to this, in Oracle it is possible to insert via a view and
populate multiple tables in one go.  It might not lend itself to you
one-to-many records but hey - it could come in useful sometime.

J

> Its going to have to be database specific.  I believe you can retrieve the
> ID from a newly insert MySQL row as a variable and then use that in your
> remaining insert statements.  Oracle on the other hand has a sequence that
> you can either select from to get a key value and then apply it to all
> appropriate records however if you are using a trigger to apply the key
> value to inserted records automatically then your going to have to work a
> little harder.

Thank you for your detailed answer. I'm surprised that inserting values in
two related tables (something that shold be very basic for a relational
database) is so complex !

--
Vincent

Vincent Lascaux wrote: 
>
>
> Thank you for your detailed answer. I'm surprised that inserting values in
> two related tables (something that shold be very basic for a relational
> database) is so complex !
>

It isn't complex. It is just different from DB to DB :)

If you use MySQL you can insert report like this (PHP example):

// ====

mysql_query('INSERT INTO reports (...) VALUES (...)');

// Return last generated insert ID, works only for auto_increment pkeys
$rep_id = mysql_insert_id();

for($i = 0; $i < count($lines); $i++) {
mysql_query('INSERT INTO lines (key, ...) VALUES ('$rep_id', ...)');
}

// ===

As you can see it is not so complex.

> Could you point me to some document on how to do this with the standard da
tabases (SQL Server, Oracle, MySQL...) ?

In SQL Server there are @@IDENTITY, SCOPE_IDENTITY( ) and
IDENT_CURRENT('table_name') to retrieve last generated autoincrement
(identity) value respectively for whole last statement generated operations
(including triggered operations), only direct statement operations
(not including triggered operations), and for given table.
Most useful is SCOPE_IDENTITY() (you have to call it in the same
batch as the INSERT statement).

In Oracle there's RETURNING clause, which is best for retrieving
any values set by DB (not only sequence values).

I do not use MySQL, but there's PHP MySQL function 'mysql_insert_id', which
gives last autoincrement value generated in given connection.

Hilarion

> I'm surprised that inserting values in two related tables (something that shold be very b
asic for a relational database) is so
> complex !

Inserting is not complexed. The problem is that there's nothing about
autoincrement / identity columns etc. in SQL standard, so each DB
engin has it's own solution for this.
If you do not use this autonumbering features, then it's almost same
in all DBs:

INSERT INTO main_table ( some_key_id, some_data )
VALUES ( 1234, 'my data to key 1234, which I created myself' );

INSERT INTO slave_table ( some_slave_id, master_id, another_data )
VALUES ( 15, 1234, 'my "slave" data with myself generated slave key 15, for 
master key 1234' );


Hilarion

> If you do not use this autonumbering features, then it's almost same
> in all DBs:
>
> INSERT INTO main_table ( some_key_id, some_data )
> VALUES ( 1234, 'my data to key 1234, which I created myself' );
>
> INSERT INTO slave_table ( some_slave_id, master_id, another_data )
> VALUES ( 15, 1234, 'my "slave" data with myself generated slave key 15,
> for master key 1234' );

I agree, but how can you not use autonumbering feature ? unless the key has
some meaning...
I find it strange that SQL doesn't define a standard auto numbering
feature... I think its really a lack in the language

--
Vincent