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