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

Fellow monasterians,

I'm returning an array (yes, I know it's really a list) from an subroutine by reference. So far, so good. However, I'm not able to INSERT those returned dereferenced values into my database. This first bit of code returned the bizarre error: unknown column 'actual_value_I'm_trying_to_place' in 'field list' (which is discussed in this node). Much simplified (hope I didn't lose anything):

   my $ref = &subroutine( $fee, $fie, $foo, $fum );
        
   $sth = $dbh->prepare ( "INSERT INTO mytable VALUES(?,?,?,?)" );
   $sth->execute ( @$ref[0], @$ref[1], @$ref[2], @$ref[3] ) ;
   
   sub subroutine {
      my @ref = @_;
      ...
      return (\@ref);
   }

[download]

Data::Dumper is our friend:

   print Dumper(@$ref[0], @$ref[1], @$ref[2], @$ref[3]);
   
      $VAR1='aaaaaaa';
      $VAR2='bbbbbbb';
      $VAR3='ccccccc';
      $VAR4='ddddddd';

[download]

If I hard code it, all is fine:

   my @ref = qw( aaaaaaa bbbbbbb cccccccc ddddddd );
   $sth->execute ( $ref[0], $ref[1], $ref[2], $ref[3] );

[download]

I even tried, unsuccessfully, re-assigning the references to a regular array (desperation):

   my @array = ( @$ref[0], @$ref[1], @$ref[2], @$ref[3] );
   $sth->execute ($array[0], $array[1], $array[2], $array[3]);

[download]

So, my questions: 1) is my original subroutine referenced return okay? 2) is it true that you can't INSERT derefenced values into MySQL? 3) how to I do this without just declaring a big fat global and avoiding the references? As always, thanks in advance.


—Brad
"A little yeast leavens the whole dough."

Replies are listed 'Best First'.
Re: Unable to INSERT dereferenced values into db
by Stevie-O (Friar) on May 25, 2004 at 03:18 UTC
    First: Actually, you ARE returning an array. Lists are only transient things that exist in expressions. If you can take a reference to it, it's an array.

    With that out of the way: That's not how you access array elements, and I think that'll give you a warning about single-element array slicing. There are two ways you can do your array: 

    $sth->execute( $ref->[0], $ref->[1], $ref->[2], $ref->[3]) ;

    [download]
    or slightly easier 
    $sth->execute( @$ref );

    [download]
    Try those.
    --Stevie-O 
    $"=$,,$_=q>|\p4<6 8p<M/_|<('=>
.q>.<4-KI<l|2$<6%s!<qn#F<>;$,
.=pack'N*',"@{[unpack'C*',$_]
}"for split/</;$_=$,,y[A-Z a-z]
         {}cd;print lc

    [download]
[reply]
[d/l]
[select]

      TMTTWTDI (There's More Than Two Ways To Do It), this is Perl after all.

      <pedantic>You forgot about $sth->execute($$ref[0], $$ref[1], $$ref[2], $$ref[3]); and what about ...</pedantic>

[reply]
[d/l]
Re: Unable to INSERT dereferenced values into db
by Somni (Friar) on May 25, 2004 at 03:27 UTC

    What version of DBD::mysql are you using? You may be encountering a bug in an older versions of DBD::mysql. The driver looks at the scalars you pass in and tries to determine what type they are, and older versions would occasionally get this wrong. The resulting SQL would leave the value unquoted, which the database then misparses.

    As for the question you actually asked, it's irrelevant where the data comes from, as long as you dereference correctly it doesn't matter that your data is contained in an arrayref.

    Having said that, your dereferences are slightly wrong. @$ref[0], while it will work here, is akin to saying @array[0]; in some contexts you will not get a result you expect. Consider: $array[0] = `ls` vs. @array[0] = `ls`. See perldoc -q '@array' for a little more discussion.

    Your dereferences are better written as $ref->[0], $$ref[0], or even @$ref[0..3].

[reply]
[d/l]
[select]
      Thanks Somni for that clarification. I checked on the version of DBD::mysql they have on my host: 2.1011. I checked CPAN for the DBD::mysql changelog and found that in version 2.9002 they...
      Fixed bug where strings that were used in numeric context were not getting quoted on execute(). Now all parameters are bound as varchar by default.
      Bingo!

      —Brad
      "A little yeast leavens the whole dough."
[reply]
      Thank Somni for the "clue." In my production code I was trying to insert hex values for web colors, e.g., c023ab, which was producing the "unknown column" error. When I substituted simple strings, e.g., "aaaaaaaa" for those values, I was fine. So, it must be the bug of which you and this node spoke of. My web host is running MySQL 3.23.58, but can't find a convenient listing of all the fixes since then (like you can with CPAN modules). So, I can't say positively that is the problem.

      And interestingly, I can't replicate the error in a stripped-down version, I can only get it in my larger program. Anyway, for now, I must forego the monkish ways of placeholders and place my quoted values directly into my INSERT statement, which, of course, fixes the immediate problem.

      If I find out more, I will update this node.

      —Brad
      "A little yeast leavens the whole dough."
[reply]

        It is the driver that does the placeholder substitution, and that determines (correctly or incorrectly) the data types of the parameters and how to quote them. MySQL 3.23.58 is the database version; I am speaking of the driver, DBD::mysql, which can be found on CPAN, changelogs included.

        You can upgrade the driver yourself, see perldoc -q 'my own module'.

[reply]
      Or could it be a bug or just an old version of DBI (they have an ancient 1.21 version running)? Thanks.

      —Brad
      "A little yeast leavens the whole dough."
[reply]

Back to Seekers of Perl Wisdom