DBI Insert

Win has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: DBI Insert by edan (Curate) on Nov 24, 2003 at 16:45 UTC
Yes. Oh wait, did you want to know what the problem is? Read the DBI docs. You'll find there that the method `do()` is used on a database handle - in this case, that's your `$dbh`. Thus you need to do this: `my $sql = "INSERT into 1985_onwards_slim_I (Year_of_death, Ageyears, Sex, Undcause, OAcode) SELECT Col003, Col020, Col023, Col029, Col051 from 1985_onwards_full_data_set"; $dbh->do($sql);` [download] If you want to prepare and execute, the sequence is like this: `my $sth = $dbh->prepare($sql); $sth->execute();` [download] Also, I recommend some Error-checking - always a plus. -- 3dan	[reply] [d/l] [select]
Re: DBI Insert by jZed (Prior) on Nov 24, 2003 at 16:33 UTC
1. 1985_onwards_slim_I is not a valid SQL table name because it begins with a number although some RDBMSs will accept it 2. The syntax is $sth->execute(), not $dbh->do($sth)	[reply]
Re: DBI Insert by sgifford (Prior) on Nov 24, 2003 at 16:49 UTC
One problem is that you're not doing any error checking on `$dbh->prepare` or `$dbh->do`. Probably if you put in some error checking, whatever problem you're having will become more clear. The other problem is that `$dbh->do` takes a string to execute, not a statement handle. If you have an already-prepared statement, like the one in `$sth`, you want to use `$sth->execute`. `my $sth = $dbh->prepare("INSERT into 1985_onwards_slim_I (Year_of_deat +h, Ageyears, Sex, Undcause, OAcode) SELECT Col003, Col020, Col023, Col029, Col051 from 1985_onwards_f +ull_data_set") or die "Couldn't prepare query: ".$dbh->errstr; $sth->execute() or die "Couldn't execute query: ".$sth->errstr;` [download] Update: I've fixed a typo in the above. Thanks to 3dan for the correction.	[reply] [d/l] [select]
Re: Re: DBI Insert by edan (Curate) on Nov 24, 2003 at 16:57 UTC
`$sth->execute($sth)` You have a typo here. `execute()` takes either no arguments, or a list of bind values if you are using placeholders - you do not pass the statement handle. -- 3dan	[reply] [d/l]
Re: Re: DBI Insert by jeffa (Bishop) on Nov 24, 2003 at 20:22 UTC
"you're not doing any error checking on `$dbh->prepare` or `$dbh->do` ..." But if the OP had set `RaiseError` to true in the `connect()` method, then this would be handled transparently and automatically. :) `my $dbh = DBI->connect( $datasource,$user,$pass, {RaiseError => 1}, );` [download] jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l]
Re: DBI Insert by Anonymous Monk on Nov 24, 2003 at 16:37 UTC
Yes, $dbh is not defined	[reply]