Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

DBI SQL adding array of values

by pmtolk (Acolyte)
on Oct 27, 2004 at 13:55 UTC ( [id://403005]=perlquestion: print w/replies, xml ) Need Help??

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

They keep changing when I grab the fields from excel
foreach my $sheet ($xls->sheets()) { while ($sheet->has_data) { my @data = $sheet->next_row;
there are empty fields and I would like to change the empty fields to "void" or "NULL" so that I can match up my call to $sth->execute_array(undef, @data) with the proper values for the prepared sql statement which has 25 ?'s (?,?..?);

Replies are listed 'Best First'.
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?

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

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.

      Sorry about the question disappearing I though it would add an update underneath, and thanks for all of your help it is much appreciated.
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?

    rdfield

Re: DBI SQL adding array of values
by edan (Curate) on Oct 27, 2004 at 15:03 UTC

    I would try something like this untested code:

    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...

    --
    edan

Re: DBI SQL adding array of values
by radiantmatrix (Parson) on Oct 27, 2004 at 15:19 UTC

    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."); }
    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
      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.
        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
      if (scalar @data = scalar @table) {
      Oops. Wrong operator. You want ==.

      And use of scalar is unnecessary — though it doesn't hurt.

      if (@data == @table) {

        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

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://403005]
Approved by claree0
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others wandering the Monastery: (8)
As of 2024-03-28 12:35 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found