DBI Removing Leading Zeroes 0's

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

Good Morning. I was wondering if anyone had run into an issue with DBI, where, when you have mixed data types coming back, it would return a number such as 0301 as 301. I was wondering if anyone had found an easy fix for it so that it provides the entire number? I just posted the sub that I am using. It's an Oracle connection. Please let me know if anyone has any questions. thanks in advance! -theleftsock

UPDATE:

Thank you for the responses. After I retested several of my responses, it appears it is not an issue.

To Corion's point, They are VARCHAR2 in the database, and are coming back correct. The columns are of mixed data types, with this type of sub used in a couple different places. It's hard to tell the data type up front. I believe I had done some post processing on the files, and when I evaluated these in post-processing that is where it is evaluating 0301 as 301. I am updating my post processing to be more aware of leading 0's on integer values. I am using printf, sprintf in most cases there, I just need to be more cautious when I slurp the files.

Very much thanks for the responses!

sub run_select { #takes a dbh, a string of SQL
    my ($dbh_, $sql_) = @_;
    my ($result, $sth, $col_name_aref);
    print "in run_select sql_: $sql_\n";    
    if ($sql_ =~ /;/) {
        $sql_ =~ s/;//;
    }
    $sth = $dbh_->prepare($sql_);
    eval {$sth->execute;};
    
    if ($@) {
        write_log($log_file, "error with execute in run_select in $0\n
+");
        write_log($log_file, "error: $@");
    } 
    else {
        $col_name_aref = $sth->{NAME};
        $result = $sth->fetchall_arrayref;
    }
    return ($result, $col_name_aref); #returns an aref of the values a
+nd an aref of the column names
}
[download]

Comment on DBI Removing Leading Zeroes 0's Download Code

Replies are listed 'Best First'.
Re: DBI Removing Leading Zeroes 0's by Corion (Patriarch) on Apr 08, 2013 at 16:16 UTC
What is the data type of the column? If it is (for example) `INTEGER` or `DECIMAL(18,2)`, it won't have leading zeroes even in the database.	[reply] [d/l] [select]
Re: DBI Removing Leading Zeroes 0's by kennethk (Abbot) on Apr 08, 2013 at 16:25 UTC
To expound upon Corion's wisdom, you are conflating numerical values and formatting. As far as your database (as well as Perl) is concerned, `0301` and `301` are the same number, but are different strings. Oracle will also truncate trailing zeroes in decimals, because those contain no numerical information. So the question is do you need to store all four digits, which would suggest you should use perhaps a `char(4)` type instead, or do you just need to output four digits? Any time you need to control formatting on a numerical output, you should probably turn to printf/sprintf, e.g. `~$ perl -e 'printf "%04d\n", 301' 0301` [download] #11929 First ask yourself `How would I do this without a computer?' Then have the computer do it the same way.	[reply] [d/l] [select]