Re: DBI SQL adding array of values
by duff (Parson) on Oct 27, 2004 at 14:52 UTC
|
This really isn't pertinent to your question, but everytime I see code like
for(0..$size-1){
$tstr=$tstr."?,";
$tv=$tv.@table[$_].",";
}
chop($tstr);
chop($tv);
it triggers my gag reflex :) Also, you're using @table[$_] when you clearly want $table[$_]
Such things are better written thusly:
my $tstr = join ',', ("?") x @table;
my $tv = join ',', @table;
But, back to your question ... are you sure that @data has the exact same number of elements as @table?
| [reply] [Watch: Dir/Any] [d/l] [select] |
Re: DBI SQL adding array of values
by samtregar (Abbot) on Oct 27, 2004 at 15:10 UTC
|
First, let me help you. You're working much too hard. That first bit of code should go like this:
my $tstr = join(',', ('?') x @table);
my $tv = join(',', @table);
Now your error comes when you try to call execute using @data:
$sth->execute( @data) || die("Error: Can't execute: $DBI::errstr
+");
I'm pretty sure this is happening because @data has one less item than @tables. Try putting this before the execute:
die "Hey, \@data should have " .
scalar(@table) .
" values but it only has " . scalar(@data) . "!"
if @data != @table;
The error message generated should explain the problem to you. If not, post more code and maybe the real problem will be more clear.
-sam
| [reply] [Watch: Dir/Any] [d/l] [select] |
Re: DBI SQL adding array of values
by bart (Canon) on Oct 28, 2004 at 00:04 UTC
|
(Reply to the revised node — people, don't do that, don't delete the original contents. This is more some temporary medium for communication, solving your technical problems; this is supposed to be an archive, too. Editing nodes this way destroys their value. Anyway, beck to your regular programme.)
If your data comes from Excel, you shouldn't worry about it not including empty cells to the left of any nonempty cells. So all you'd still have to do is make sure @data holds the same number of columns as the header array does. The easiest to achieve that is:
$#data = $#table;
If this creates extra columns, they'll be undef. Any undefined values in the data will be replaced with NULL in the database, by DBI.
If, however, people have put data in the wrong cells, all bets are off.
| [reply] [Watch: Dir/Any] [d/l] |
|
Sorry about the question disappearing I though it would add an update underneath, and thanks for all of your help it is much appreciated.
| [reply] [Watch: Dir/Any] |
Re: DBI SQL adding array of values
by rdfield (Priest) on Oct 27, 2004 at 14:16 UTC
|
What's in $event_sql? Have you checked what's in $fields?
| [reply] [Watch: Dir/Any] [d/l] [select] |
Re: DBI SQL adding array of values
by edan (Curate) on Oct 27, 2004 at 15:03 UTC
|
die "number of data items doesn't match column count"
if @data != @table;
local $" = q/,/;
my @place_holders = ('?') x @data;
my $sql = "insert into member_info (@table) VALUES (@place_holders)";
my $sth = $dbh->prepare($sql)
or die "DBI->prepare failed: $DBI::errstr";
$sth->execute(@data)
or die "DBI->execute failed: $DBI::errstr";
To me, that reads a lot better than your snippet...
| [reply] [Watch: Dir/Any] [d/l] |
Re: DBI SQL adding array of values
by radiantmatrix (Parson) on Oct 27, 2004 at 15:19 UTC
|
my $tstr = '?';
my $tv = join (', ', @table);
for (1..$#table) {
$tstr.=', ?';
}
my $sth = $ebh->prepare("INSERT INTO member_info ($tv) VALUES ($tstr)"
+)
or die "Unable to prepare statement: ($DBI::err) $DBI::errstr";
if (scalar @data = scalar @table) {
$sth->execute(@data) or die "No exec: ($DBI::err) $DBI::errstr";
} else {
warn("\@data is not the same size as \@table; tried to put ".scalar
+ @data." items into ".scalar @table." columns.");
}
If still get a "26 values when 25 are expected" error, try using
$sth->execute( undef,@data )
Update: I forgot to note that the above should only be used as a test, as it will insert a NULL into the first column of your tables. Thanks, jZed, for reminding me.
execute_array does something different.
radiantmatrix
require General::Disclaimer;
"Users are evil. All users are evil. Do not trust them. Perl specifically offers the -T switch because it knows users are evil." - japhy
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
If still get a "26 values when 25 are expected" error, try using
$sth->execute( undef,@data )
Um, no, don't do that. It will almost guarantee that the data will get placed in the wrong columns. Undef should be the first value in $dbh->do() because it is an attribute, not part of the data, but execute() doesn't currently take an attribute so it will become a value and shift all the other values to the right.
| [reply] [Watch: Dir/Any] |
|
| [reply] [Watch: Dir/Any] |
|
if (@data == @table) {
| [reply] [Watch: Dir/Any] [d/l] [select] |
|
Yeah, @data == @table works today, but I'm always worried that someday it will mean "if the contents of @data match the contents of @table. Better to explicitly compare the number of elements, as scalar's behavoir is less likely to change.
radiantmatrix
require General::Disclaimer;
"Users are evil. All users are evil. Do not trust them. Perl specifically offers the -T switch because it knows users are evil." - japhy
| [reply] [Watch: Dir/Any] [d/l] |
|
|