Folks,
After reading searching around Usenet and "Programming the PERL DBI" I
still can't find any concrete examples to help me do what I have to do. All
I am doing is inserting data into a database. The data comes from a few
different place but ends up in two synced arrays (the keys are in one array,
the values are in the other).
The data is going to a MS-SQL server. The DSN and connection works
correctly and the inserts are fine if all data types on the database are set
to VARCHAR. If it is anything else (like numeric) the insert fails. I
decided to try to check explicitly if the value is numeric and generate an
appropriate SQL string (below). It generates a nice string but the inserts
still fail if the data type is changed on the database side to anything
other than VarChar.
So far I've been able to figure out how to iterate through the data types
that my database supports but have no idea how to specifically bind a data
type to values in my synced arrays (what happens when it has to be some
other funky data type?). Does anyone know how to do this or can anyone offer
another strategy?
-Matt
#### This code generates and prints some SQL based on to synced arrays, it
attempts to determine if the value is a number and #### if it is, it doesn't
quote it
my $query = "INSERT INTO $tablename (";
foreach my $item (@$referencetokeys) {
$query .= $item . ",";
}
chop($query);
$query .= ') VALUES (';
my $isnumbercounter = 0;
my @values = @$referencetovalues;
my @isnumber = DBI::looks_like_number(@values);
my $item;
foreach $item (@$referencetovalues) {
if ($isnumber[$isnumbercounter]) {
$query .= $item . ",";
} else
$query .= $dbh->quote($item) . ",";
}
$isnumbercounter++;
}
chop($query);
$query .= ')';
my $sql = $query; #"INSERT INTO $tablename ($keys) VALUES ($values)";
my $printme = $sql;
