Consider this:
my $sth = $dbh->prepare("INSERT INTO [DATATEST] ( [billingterms], [carrier],
[class (1)], [class (2)], [class (3)], [class (4)], [client], [consignee],
[cube (1)], [cube (2)], [cube (3)], [cube (4)], [description (1)], [description
(2)], [description (3)], [description (4)], [eta], [insured], [order date],
[pallets (1)], [pallets (2)], [pallets (3)], [pallets (4)], [percentinvoice],
[pieces (1)], [pieces (2)], [pieces (3)], [pieces (4)], [po #1], [po #2], [po
#3], [po #4], [po #5], [productinvoice], [quoted?], [residential?], [ship
date], [shipper], [special instructions], [weight (1)], [weight (2)], [weight
(3)], [weight (4)]) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?
);SELECT SCOPE_IDENTITY()");
my $rv = $sth->execute('DRIVER COLLECT', undef, undef, undef, undef, undef,
undef, undef, undef, undef, undef, undef, undef, undef, undef, undef,
'07/22/2009', 0, '2009-07-22 00:00:00', undef, undef, undef, undef, 0, undef,
undef, undef, undef, undef, undef, undef, undef, undef, 20.20, -1, 0,
'2009-07-22 00:00:00', undef, 'this is fun.. not.', undef, undef, undef, undef,
undef, undef);
'Productinvoice' is of a data type "money". So:
perl test_odbc.pl
DBD::ODBC::st execute failed: [FreeTDS][SQL Server]Disallowed implicit
conversion from data type varchar to data type money, table
'TREX.dbo.DATATEST', column 'ProductInvoice'. Use the CONVERT function to run
this query. (SQL-42000)
[FreeTDS][SQL Server]Statement(s) could not be prepared. (SQL-42000) at
test_odbc.pl line 14.
Is it failing to prepare, or to execute... or what? Removing that column,
place-holder and value -- it works just fine. So, 'money' data type is the only
problem.
Why is this happening at all? From Google it seems that the problem has been
around for years, but all fingers point to the "application" not casting the
data type correctly for preparing, or something.
So, is there a data type definition that still needs to be coded somewhere?
In this case, even with 'vanilla' DBI, it looks like what should happen to keep
the odbc library happy, doesn't. So I can't blame DBIx-Class or related... it
really seems the fix should be in DBD::ODBC, no? I mean, they were very helpful
and encouraging that this can be fixed in their code, but I'm doubting that as
it exists even in a direct DBI call as above.
So how and where *does* this get fixed in the 'application' (as compared to the
driver/library/server) side? Consider this, as well, from someone I assume far
more skilled a perl programmer than myself:
"The only problem I've ( recently ) encountered with SQL Server is with the
'money' column type. Avoid using this type, and you should have flawless SQL
Server action." -- http://search.cpan.org/~dkasak/
So, that's a great endorsement but with a serious caveat. Would be much nicer
to have a whole-hearted endorsement, I think. How can this problem be fixed,
finally?
Cheers,
--
|\ /| | | ~ ~
| \/ | |---| `|` ?
| |ichael | |iggins \^ /
michael.higgins[at]evolone[dot]org
