DBD::CSV quote() oddity

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

Hi all, I've recently been using DBD::CSV in my ongoing job of cleaning up horrible public record data. Well, this is the first time i've tried to write csv tables (files) using DBD::CSV, and i've found quite a strange (to me) situation that may have something to do with the quote() method, and may have something to do with the actual writing of the record. In any case, heres my original approach which leads to strange results, and my workaround:

original approach - strange result
I issue this query, which i generated using a csv db handle quote method on all values being inserted:

INSERT INTO snames VALUES ( '797000','140740413000','KNAACK, RICHARD I
+','GRANTEE','5403','','W','','SUNNYSIDE','DR','112N','','','MEQUON','
+WI','53092' )
[download]

And the result in the data file is:

797000,140740413000,"HAGEN, RONALD",GRANTOR,5403,'?5?W?6?SUNNYSIDE?7?D
+R?8?112N?9?MEQUON?10?WI?11?53092'
[download]

I get no error of any sort, and i do have PrintError and RaiseError set, and am executing the query in an eval block. Note that things go fine until that first empty field (empty string). Then i get that strange last column, the numbers correspond to numbering the columns, and the data is there too, with the question marks in between. I have the csv_sep_char as a comma, and the csv_quote_char as double quote.

Fixed approach, but why:
If i change all the empty string values to a single space and then DBD::CSV quote() them, then everything behaves as i'd expect, and the rows get written as they should. For the insert as above, heres what ends up in the file:

797000,140740413000,"HAGEN, RONALD",GRANTOR,5403," ",W," ",SUNNYSIDE,D
+R,112N," "," ",MEQUON,WI,53092
[download]

Is this a problem with a version of some support lib, such as SQL::Statement, or am i just expecting something other than what is supposed to happen?

Comment on DBD::CSV quote() oddity Select or Download Code

Replies are listed 'Best First'.
Re: DBD::CSV quote() oddity by jZed (Prior) on Apr 22, 2005 at 18:02 UTC
Yes, it's the version of SQL::Statement. I recently fixed the empty string parsing. In general it's better to use placeholders or to use NULL instead of empty string, but your problem should be fixed in the most recent SQL::Statement. I'm debugging some errors in the make test for the latest statement but you can safely ignore the test failure and install it anyway, it's a test problem, not a module problem. update please let me know if this fixes your problem.	[reply]
Re^2: DBD::CSV quote() oddity by shemp (Deacon) on Apr 22, 2005 at 18:17 UTC
Thanks much! Yep, i upgraded to SQL::Statement 1.13 (from cpan) and things now work as expected. Regarding using NULL, etc, im taking results directly from reads from another csv table (file) and normalizing the data. At this stage all im doing is re-arranging the table structure, actually modifying field contents will come later.	[reply]