Perl – DBI string incorrectly gets double quoted

I’m using Text::CSV to read in a csv file, parse the fields and then write the record to SQL Server via DBI. All of the fields are golden except for the date fields that I have to manipulate.

The format of the date field in the CSV is, for example, ‘Fri Dec 20 14:56:54 2013’. Of course, SQLServer doesn’t like that format. Ergo, in the script I’m using this block:

  • Can file log deletion raise any problem?
  • Count number of occurrences of keyword in comma separated column?
  • C# bcp to SQL Server
  • Sorting varchar field with mixed alphanumeric data
  • How to rename something in SQL Server that has square brackets in the name?
  • Pass multiple parameters in a stored procedure to a QueryString Parameter
  •     if ( $i == 12 || $i == 13 || $i == 22 || $i == 23 || $i == 24 || $i == 25)
        {
            if ( defined $fields[$i] )
            {
                $dummy = UnixDate($fields[$i],'%m/%d/%Y %H:%M:%S');
                $fields[$i] = $dummy;
            }
        }
    

    (The defined check is because if a field from the CSV contains the word ‘None’, then I’m doing an undef on that element so that the field will be NULL when inserted into the DB. In this sample row, all of the columns/fields except for elements 24 and 25 are ‘None’.)

    Turning on DBI tracing shows me:

    <- bind_param(19, '1')= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(20, undef)= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(21, undef)= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(22, undef)= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(23, undef)= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(24, undef)= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(25, "12/20/2013 14:56:54")= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(26, "12/20/2013 14:55:37")= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(27, 'Unknown')= ( 1 ) [1 items] at upload_merged_host.pl line 71
    

    So you can see that other fields/columns from the CSV are bound correctly (the ‘1’ and ‘Unknown’ above), but the date field/column that I manipulated now has double quotes, and SQL just isn’t happy with that.

    For further clarity, if I do not manipulate the date field, it correctly has the single quotes, but is no longer in a SQLServer-acceptable format:

    <- bind_param(19, '1')= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(20, undef)= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(21, undef)= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(22, undef)= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(23, undef)= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(24, undef)= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(25, 'Fri Dec 20 14:56:54 2013')= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(26, 'Fri Dec 20 14:55:37 2013')= ( 1 ) [1 items] at upload_merged_host.pl line 71
    <- bind_param(27, 'Unknown')= ( 1 ) [1 items] at upload_merged_host.pl line 71
    

    Would love any pointers/suggestions!

    Update: @Vinbot’s comment got me to try something. Instead of using manipulating the date string with UnixDate, why not just chop of the day-of-the-week at the beginning. So it now looks like this:

    $fields[$i] = substr($fields[$i],4);

    And what I’m seeing in the trace should now be good to go:

    ...
    <- bind_param(25, 'Dec 20 15:33:42 2013')= ( 1 ) [1 items] at upload_merged_host.pl line 93
    ...
    

    But I’m still getting the casting error, same as before. There are no problems using this same format directly to sql (e.g. via sql server management studio):

    INSERT INTO <schema>.dbo.host(hostname,interface_ipaddress,interface_name,host_modified_date) VALUES ('some_host','10.1.1.1','Local Area Connection','Dec 20 15:33:42 2013')
    

    Works just fine.

    Update2
    I was able use a different date manipulation routine and the problem is now resolved (though I still need to optimize the changes, it’s at least functional).

    The resolution was to use DateTime and DateTime::Format::DBI. As of now, I’m having to manually manipulate the date string as it comes in from the CSV to pass to DateTime->new() and then pass that value into DateTime::Format::DBI->format_datetime().

    Hopefully after some sleep I can cut out some of the brute force that I just did! 🙂

    One Solution collect form web for “Perl – DBI string incorrectly gets double quoted”

    I think you might have diagnosed your problem incorrectly and you’ve not given us the full information. It would have been nice to know a) what DBD you are using to connect to MS SQL Server, b) the column type for the date and c) the exact error message you were getting.

    I’m going to assume you are using DBD::ODBC to talk to MS SQL Server but much of that does not matter and the column was a datetime.

    The first thing is that I believe the trace output for bind_param puts quotes round all parameters so I don’t think your datetime is getting mysteriously quoted. The second thing is I don’t think that is a valid datetime especially when using ODBC. If you error was something like “Numeric value out of range: invalid character in date/time string” or “invalid value for cast” I think it would demonstrate the datetime was just the wrong format.

    In ODBC the correct way to input a datetime/timestamp is {ts ‘yyyy-mm-dd hh:mm:ss’} and the driver should ensure the correct thing is sent to the database.

    use 5.016;
    use strict;
    use warnings;
    use DBI;
    
    my $h=DBI->connect("dbi:ODBC:abc","xx","yy", {RaiseError => 1, PrintError => 0});
    eval {
        $h->do(q/drop table mje/);
    };
    $h->do(q/create table mje (a varchar(50), b datetime)/);
    
    my $s = $h->prepare(q/insert into mje values(?,?)/);
    $s->bind_param(1, 'fred');
    $s->bind_param(2, "{ts '2013-12-20 14:55:37'}");
    $s->execute;
    # or $s->execute("fred", "{ts '2013-12-20 14:55:37'}");
    

    Bear in mind inputting a datetime into sql server management studio successfully is not guarantee of it being right behind a driver which is not the database engine as the driver does not pass the string datetime, it parses it into something else (unless of course your datetime is in your sql itself). Also sessions can have different datetime input formats.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.