Re: Unable to INSERT dereferenced values into db

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

Comment on Re: Unable to INSERT dereferenced values into db Select or Download Code

Replies are listed 'Best First'.
Re: Re: Unable to INSERT dereferenced values into db by bradcathey (Prior) on May 26, 2004 at 02:18 UTC
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]
Re: Re: Unable to INSERT dereferenced values into db by bradcathey (Prior) on May 25, 2004 at 13:42 UTC
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]
Re: Re: Re: Unable to INSERT dereferenced values into db by Somni (Friar) on May 26, 2004 at 01:55 UTC
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]
Re: Re: Unable to INSERT dereferenced values into db by bradcathey (Prior) on May 25, 2004 at 13:51 UTC
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]