code-perl-data-type-conversion.md

#!/usr/bin/perl
use strict;
use DBI;
# Create a hash reference that holds a hash of parameters for the
# connection.
 my $attr = {AutoCommit => 0, # Turn off autocommit
             PrintError => 0   # Turn off print error. Manually handled
             };
# Open a connection using a DSN. Supply the username and password.
my $dbh = DBI->connect("dbi:ODBC:VerticaDSN","ExampleUser","password123",
    $attr);
if (defined DBI::err) {
    # Conection failed.
    die "Failed to connect: $DBI::errstr";
}
print "Connection AutoCommit state is: " . $dbh->{AutoCommit} . "\n";
# Create table to hold inserted data
$dbh->do("DROP TABLE IF EXISTS TEST CASCADE;");
$dbh->do("CREATE TABLE TEST( \
               C_ID  INT, \
               C_FP  FLOAT,\
               C_VARCHAR VARCHAR(100),\
               C_DATE DATE, C_TIME TIME,\
               C_TS TIMESTAMP,\
               C_BOOL BOOL)");
# Populate an array of arrays with values.
my @data = (
                # Start with matching data types
                [1,1.111,'Matching datatypes','2001-01-01','01:01:01'
                    ,'2001-01-01 01:01:01','t'],
                # Force floats -> int and int -> float.
                [2.0,2,"Ints <-> floats",'2002-02-02','02:02:02'
                    ,'2002-02-02 02:02:02',1],
                # Float -> int *only* works when there is no loss of precision.
                # this row will fail to insert:
                [3.1,3,"float -> int with trunc?",'2003-03-03','03:03:03'
                    ,'2003-03-03 03:03:03',1],
                # String values are converted into numbers
                ["4","4.4","Strings -> numbers", '2004-04-04','04:04:04',
                    ,'2004-04-04 04:04:04',0],
                # String -> numbers only works if the entire string can be
                # converted into a number
                ["5 and a half","5.5","Strings -> numbers", '2005-05-05',
                    '05:05:05', ,'2005-05-05 05:05:05',0],
                # Number are converted into string values automatically,
                # assuming they fit into the column width.
                [6,6.6,3.14159, '2006-06-06','06:06:06',
                    ,'2006-06-06 06:06:06',0],
                # There are some variations in the accepted date strings
                [7,7.7,'Date/time formats', '07/07/2007','07:07:07',
                    ,'07-07-2007 07:07:07',1],
             );
# Create a prepared statement to use parameters for inserting values.
my $sth = $dbh->prepare_cached("INSERT into test values(?,?,?,?,?,?,?)");
my $rowcount = 0; # Count # of rows
# Loop through the arrays to insert values
foreach my $tuple (@data) {
    $rowcount++;
    # Insert the row
    my $retval = $sth->execute(@$tuple);

    # See if the row was successfully inserted.
    if ($retval == 1) {
        # Value of 1 means the row was inserted (1 row was affected by insert)
        print "Row $rowcount successfully inserted\n";
    } else {
        print "Inserting row $rowcount failed with error " .
                $sth->state . " " . $sth->errstr . "\n";
    }
}
# Commit the data
$dbh->commit();
# Prepare a query to get the content of the table
$sth = $dbh->prepare("SELECT * FROM TEST ORDER BY C_ID ASC");
$sth->execute() or die "Error: " . $dbh->errstr;
my @row; # Need to pre-declare to use in the format statement.
# Use Perl formats to pretty print the output.
format STDOUT_TOP =
Int  Float          VarChar        Date      Time      Timestamp     Bool
===  =====  ==================  ========== ======== ================ ====
.
format STDOUT =
@>>  @<<<<  @<<<<<<<<<<<<<<<<<  @<<<<<<<<< @<<<<<<< @<<<<<<<<<<<<<<< @<<<<
@row
.
# Loop through result rows while we have them
while (@row = $sth->fetchrow_array()) {
         write; # Format command does the work of extracting the columns from
                 # the array.
}
# Commit to stop Perl complaining about in-progress transactions.
$dbh->commit();
$dbh->disconnect();