Insert in 2 tables - Second insert fails then first, then second, then first...

Insert in 2 tables - Second insert fails then first, then second, then first...

Post by Vincent O' Keef » Sun, 02 Mar 2003 01:14:23



Hi there,

I'm currently trying to run two Insert queries, one after the other
into a MySQL database via DBI. The first inserts into table poheader,
the second into table pocontent

What's happening, though, is that I'm getting consecutive successes
and failures with each attempt.

As in, if I get a successful insert into POHeader, POContent remains
empty, and vice versa. Even though both queries return a rowsaffected
value of 1.

Initially, I had them set up as simply

$query1 = QueryCode1;
$query2 = QueryCode2;

with consecutive do statements,

$dbh->do($query1);
$dbh->do($query2);

However, I thought perhaps there was something in the concurrent
connection causing the problem so I split it up into two separate
connections and prepare and execute methods but, still to no avail.

The two tables in question are in a one to many relationship with a
code from one being inserted in the other but I'm inserting the record
for the 1 side of the relationship first, so that shouldn't be an
issue, and, anyway, the second query is executing without a relevant
key being in the parent table.

Some code for those who wish to get a clearer picture

my $insertheader = qq{INSERT INTO poheader (AutoID, ...) VALUES
(NULL,$...)};

my $insertcontent = qq{INSERT INTO pocontent (AutoID, ...) VALUES
(NULL, ...)};

my $poheadrows = $dbh->prepare ($insertheader);
$poheadrows->execute() or warn "Warning: Could not insert data into PO
Header table. \n\n";

# Now close the connection

$dbh->disconnect;

my $dbh2 = DBI->connect ($dsn, $user_name, $password);

my $sth = $dbh2->prepare($insertcontent);

$sth->execute() or warn "Warning: Could not insert data into PO
Content table. \n\n";

$dbh2->disconnect;

Anyone have any ideas why this might be happening? Is there some cache
I have to flush - it's as if it's only ever inserting the last one
that didn't get inserted. Would really appreciate any pointers.

Thanks,
Vincent

 
 
 

1. Returning PK of first insert for second insert use.

All,

I have two tables t_proj, t_task see below:

CREATE TABLE t_proj (
proj_id SERIAL NOT NULL,
PRIMARY KEY (proj_id),
task_id integer(12),
user_id integer(6),
title varchar(35),
description varchar(80)
);

CREATE TABLE t_task (
task_id SERIAL NOT NULL,
PRIMARY KEY (task_id),
title varchar(35),
description varchar(80)
);

When I insert into t_task I need to return the task_id (PK) for that insert
to be used for the insert into the t_proj table.

I tried using RESULT_OID but I have no idea how to obtain the true PK using
this opague id. Below is the procedure I tried to use.

CREATE OR REPLACE FUNCTION insertTask (varchar, varchar)
        RETURNS INTEGER AS '

        DECLARE
                -- local variables
                oid1 INTEGER;
                retval INTEGER;

        BEGIN
                INSERT INTO t_task (title, description) VALUES ($1, $2);

                -- Get the oid of the row just inserted.
                GET DIAGNOSTICS oid1 = RESULT_OID;

                retval := oid1;

                -- Everything has passed, return id as pk
                RETURN retval;
        END;
' LANGUAGE 'plpgsql';

Any help would be great!

Thanks Again,
-p

---------------------------(end of broadcast)---------------------------

2. PowerC on Irix6.0 DESPERATELY SEEKING HELP

3. Insert values from one existing table into a new table.

4. Can't hotsync my Palm (usb) to my Sony Vaio

5. Skins for VB.NET

6. Enumerating list of files under NSE fails second time after NSE device removed/inserted back

7. cant add appointments

8. Comparing two tables, update some then insert the rest

9. Insert into two tables

10. unable to insert data into two tables at same time

11. How to spawn two target server for two different boards in one machine?