Conversions between Perl and Vertica data types
Perl is a loosely-typed programming language that does not assign specific data types to values. It converts between string and numeric values based on the operations being performed on the values. For this reason, Perl has little problem extracting most string and numeric data types from Vertica. All interval data types (DATE, TIMESTAMP, etc.) are converted to strings. You can use several different date and time handling Perl modules to manipulate these values in your scripts.
Vertica NULL values translate to Perl's undefined (undef) value. When reading data from columns that can contain NULL values, you should always test whether a value is defined before using it.
When inserting data into Vertica, Perl's DBI module attempts to coerce the data into the correct format. By default, it assumes column values are VARCHAR unless it can determine that they are some other data type. If given a string value to insert into a column that has an integer or numeric data type, DBI attempts to convert the string's contents to the correct data type. If the entire string can be converted to a value of the appropriate data type, it inserts the value into the column. If not, inserting the row of data fails.
DBI transparently converts integer values into numeric or float values when inserting into column of FLOAT, NUMERIC, or similar data types. It converts numeric or floating values to integers only when there would be no loss of precision (the value to the right of the decimal point is 0). For example, it can insert the value 3.0 into an INTEGER column since there is no loss of precision when converting the value to an integer. It cannot insert 3.1 into an INTEGER column, since that would result in a loss of precision. It returns an error instead of truncating the value to 3.
The following example demonstrates some of the conversions that the DBI module performs when inserting data into Vertica.
#!/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();
The example produces the following output when run:
Connection AutoCommit state is: 0
Row 1 successfully inserted
Row 2 successfully inserted
Inserting row 3 failed with error 01000 [Vertica][VerticaDSII] (20) An error
occurred during query execution: Row rejected by server; see server log for
details (SQL-01000)
Row 4 successfully inserted
Inserting row 5 failed with error 01000 [Vertica][VerticaDSII] (20) An error
occurred during query execution: Row rejected by server; see server log for
details (SQL-01000)
Row 6 successfully inserted
Row 7 successfully inserted
Int Float VarChar Date Time Timestamp Bool
=== ===== ================== ========== ======== ================ ====
1 1.111 Matching datatypes 2001-01-01 01:01:01 2001-01-01 01:01 1
2 2 Ints <-> floats 2002-02-02 02:02:02 2002-02-02 02:02 1
4 4.4 Strings -> numbers 2004-04-04 04:04:04 2004-04-04 04:04 0
6 6.6 3.14159 2006-06-06 06:06:06 2006-06-06 06:06 0
7 7.7 Date/time formats 2007-07-07 07:07:07 2007-07-07 07:07 1