Matt,
In the DBD::ODBC test suite (t/* files), you will find examples of how to do
such things in a very cross-database way. In the 'non-standard' tests,
which are not guaranteed to run anywhere but here :), I have some things
that I run to ensure things still work for me. Look at testfunc.pl to see
some examples of getting information from the driver.
The test suite actually detects which types of values the driver will
support and tries to use those when it creates columns and binds values for
tests.
That said, however, that's a bit of work.
There's a new feature in DBD::ODBC as of 0.34 which you can try.
I'm presuming you set the value of $keys based upon the value type and that
you have a varchar column for the text data and a numeric column for the
numeric data, right? The key is NOT the type of the data as much as the
type of the column you are trying to insert into.
Right after the connect, try:
$dbh->{odbc_default_bind_type} = 0;
Then, build your query as follows:
Instead of:
> if ($isnumber[$isnumbercounter]) {
> $query .= $item . ",";
> } else
>
> $query .= $dbh->quote($item) . ",";
> }
Try:
$query .= "?, "
....etc...
$dbh->prepare($query);
$dbh->execute(@values);
Jeff
>
>
>
> 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;
>
>
>
