unable to insert data into two tables at same time

unable to insert data into two tables at same time

Post by Yuan Che » Mon, 17 Jun 2002 12:48:52



Hi, there:

I try to write a perl code that takes form data from
html page and inserts or updates database tables. I
have successfully insert data into one table, but fail
to insert data into another table.  My sample as
follows:
***************************************************
 my $dbh = DBI->connect('dbi:mysql:ordering','yxc',
'9cvpt')
          ||die "unable to connect: $DBI::errstr";

my $sql = "INSERT INTO order_record (reqID,
product_name,catalog_No,supplier,order_date,status,who_ordered,PO_Num,local_req_No,
item_type, description, unit_price, unit_size,
num_of_unit, measure_type) VALUES
('','$product_name','$catalog_no','$supplier','$order_date',
'$status','$username','$PO_Num','$local_Req_No','$item_type','$req_note','$unit_price','$unit_size','$no_of_unit','$meas_type')";

my $sth = $dbh->prepare($sql)
         ||die "unable to prepare $sql: $DBI::errstr";

$sth->execute();
$sth->finish();
$dbh->disconnect();

my $dbh2 = DBI->connect('dbi:mysql:ordering','yxc',
'9cvpt')
          ||die "unable to connect: $DBI::errstr";
my $sql2 = "INSERT INTO company-profile (company_name,
contact_name,address,city,postal_code,phone,fax,e-mail,website)
VALUES
('$supplier','','$address','','','$tel_num','$fax_num','$email','')";
my $sth2 = $dbh->prepare($sql2)
         ||die "unable to prepare $sql: $DBI::errstr";
$sth2->execute();
$sth2->finish();
$dbh2->disconnect();
******************************************************

I have tried to use one connection to execute two sql
statement, it didn't work also.  So, I seperated two
process, it still doesn't work.

Anyone has the idea.  Thanks

yc

__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com

 
 
 

unable to insert data into two tables at same time

Post by Shaw » Mon, 17 Jun 2002 13:42:53


What is the returned error from DBI?  Also, turn warnings on (-w behind the shebang) and see what it says from the command line...  You should be able to do as many inserts as you want with a single db handle.

Also, on the second insert, are you trying to insert null values?  I am curious...  If the columns allow null and are defaulted to null, you should not have to mention them at all since you are just inserting null's...

Shawn

----- Original Message -----


Sent: Saturday, June 15, 2002 10:48 PM
Subject: unable to insert data into two tables at same time

> Hi, there:

> I try to write a perl code that takes form data from
> html page and inserts or updates database tables. I
> have successfully insert data into one table, but fail
> to insert data into another table.  My sample as
> follows:
> ***************************************************
>  my $dbh = DBI->connect('dbi:mysql:ordering','yxc',
> '9cvpt')
>           ||die "unable to connect: $DBI::errstr";

> my $sql = "INSERT INTO order_record (reqID,
> product_name,catalog_No,supplier,order_date,status,who_ordered,PO_Num,local_req_No,
> item_type, description, unit_price, unit_size,
> num_of_unit, measure_type) VALUES
> ('','$product_name','$catalog_no','$supplier','$order_date',
> '$status','$username','$PO_Num','$local_Req_No','$item_type','$req_note','$unit_price','$unit_size','$no_of_unit','$meas_type')";

> my $sth = $dbh->prepare($sql)
>          ||die "unable to prepare $sql: $DBI::errstr";

> $sth->execute();
> $sth->finish();
> $dbh->disconnect();

> my $dbh2 = DBI->connect('dbi:mysql:ordering','yxc',
> '9cvpt')
>           ||die "unable to connect: $DBI::errstr";
> my $sql2 = "INSERT INTO company-profile (company_name,
> contact_name,address,city,postal_code,phone,fax,e-mail,website)
> VALUES
> ('$supplier','','$address','','','$tel_num','$fax_num','$email','')";
> my $sth2 = $dbh->prepare($sql2)
>          ||die "unable to prepare $sql: $DBI::errstr";
> $sth2->execute();
> $sth2->finish();
> $dbh2->disconnect();
> ******************************************************

> I have tried to use one connection to execute two sql
> statement, it didn't work also.  So, I seperated two
> process, it still doesn't work.

> Anyone has the idea.  Thanks

> yc

> __________________________________________________
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com

> --




 
 
 

unable to insert data into two tables at same time

Post by Joe Rau » Mon, 17 Jun 2002 22:17:00


Add

 || die etc, etc

to your $sth->execute() statement -- possibly the error messages will
help you debug this better.


Quote:> Hi, there:

> I try to write a perl code that takes form data from
> html page and inserts or updates database tables. I
> have successfully insert data into one table, but fail
> to insert data into another table.  My sample as
> follows:
> ***************************************************
>  my $dbh = DBI->connect('dbi:mysql:ordering','yxc',
> '9cvpt')
>           ||die "unable to connect: $DBI::errstr";

> my $sql = "INSERT INTO order_record (reqID,

product_name,catalog_No,supplier,order_date,status,who_ordered,PO_Num,local_req_No,
Quote:> item_type, description, unit_price, unit_size,
> num_of_unit, measure_type) VALUES
> ('','$product_name','$catalog_no','$supplier','$order_date',

'$status','$username','$PO_Num','$local_Req_No','$item_type','$req_note','$unit_price','$unit_size','$no_of_unit','$meas_type')";
Quote:

> my $sth = $dbh->prepare($sql)
>          ||die "unable to prepare $sql: $DBI::errstr";

> $sth->execute();
> $sth->finish();
> $dbh->disconnect();

> my $dbh2 = DBI->connect('dbi:mysql:ordering','yxc',
> '9cvpt')
>           ||die "unable to connect: $DBI::errstr";
> my $sql2 = "INSERT INTO company-profile (company_name,
> contact_name,address,city,postal_code,phone,fax,e-mail,website)
> VALUES

('$supplier','','$address','','','$tel_num','$fax_num','$email','')";

> my $sth2 = $dbh->prepare($sql2)
>          ||die "unable to prepare $sql: $DBI::errstr";
> $sth2->execute();
> $sth2->finish();
> $dbh2->disconnect();
> ******************************************************

> I have tried to use one connection to execute two sql
> statement, it didn't work also.  So, I seperated two
> process, it still doesn't work.

> Anyone has the idea.  Thanks

> yc

> __________________________________________________
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com

> --



__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
 
 
 

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

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

2. ACTION! assistance...

3. Table corrupted and data lost (second time in one

4. Drop Down Menu w/multiple choices not working

5. Table corrupted and data lost (second time in one month!!)

6. Seeking TUXEDO references and information

7. Unable to insert data if no primary key, WHY?

8. MSChart control Question from Newbie

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

10. Insert into two tables

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

12. best way to insert date/time into oracle table