Re: DBI SQL adding array of values

Ugly, Ugly code.

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.");
}
[download]

If still get a "26 values when 25 are expected" error, try using

$sth->execute( undef,@data )
[download]

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

Comment on Re: DBI SQL adding array of values Select or Download Code

Replies are listed 'Best First'.
Re^2: DBI SQL adding array of values by jZed (Prior) on Oct 27, 2004 at 15:42 UTC
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]
Re^3: DBI SQL adding array of values by radiantmatrix (Parson) on Oct 27, 2004 at 18:16 UTC
That is exactly the point. Adding the `undef` is a debugging step the OP was trying to use, but using the wrong function to do it. It allows you to get an idea of what data is actually being passed to the DBI call without resorting to tracing. It's a good quick debug, and I (and the OP) was perfectly aware of the outcome. 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]
Re^2: DBI SQL adding array of values by bart (Canon) on Oct 28, 2004 at 00:17 UTC
`if (scalar @data = scalar @table) {` [download] Oops. Wrong operator. You want `==`. And use of scalar is unnecessary — though it doesn't hurt. `if (@data == @table) {` [download]	[reply] [d/l] [select]
Re^3: DBI SQL adding array of values by radiantmatrix (Parson) on Oct 29, 2004 at 17:09 UTC
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] [d/l]
Re^4: DBI SQL adding array of values by diotalevi (Canon) on Oct 29, 2004 at 17:29 UTC
Your concern is without foundation. The behaviour of the == operator applying a scalar / numeric context to both of its operands is not going to change in perl 5 and will not in perl 6 either. Feel safe in knowing `@data == @table` is unambiguous and is not going to change on you. Also, the question of comparing two arrays for equality is more complex than you appear to think. If you'd like to hear more about equality, speak up. In a new SoPW about the different meanings of equality in comparing two arrays. Its a bigger subject than a single response to an unrelated thread merits.	[reply] [d/l]
Re^5: DBI SQL adding array of values by radiantmatrix (Parson) on Oct 29, 2004 at 20:30 UTC