col wrote:
> 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,
>
I fail to see (again) how you come to your conclusions e.g., "fix should
be in DBD::ODBC". The error is reported by MS SQL Server (the "[SQL
Server]" at the end of the list of modules your SQL passed through). The
error even tells you how to fix this - "Use the CONVERT function to run
this query".
DBD::ODBC does not touch your SQL at all although in this case it does
bind your parameters. When binding parameters DBD::ODBC needs to know
something about those parameters and ODBC provides the SQLDescribeParam
API to do this. FreeTDS does NOT support SQLDescribeParam so DBD::ODBC
has little choice but to bind the parameters as VARCHARs. You can
override the default in DBD::ODBC using bind_param(parameter_number,
\$param, TYPE).
As example with an ODBC driver which does support SQLDescribeParam:
isql -v sqlserver user pass
SQL> create table mje (a money);
SQLRowCount returns -1
SQL> insert into mje values(20.20);
SQLRowCount returns 1
SQL> insert into mje values('20.20');
SQLRowCount returns 1
SQL> select * from mje;
+----------------------+
| a |
+----------------------+
| 20.20 |
| 20.20 |
+----------------------+
SQLRowCount returns 2
2 rows fetched
Now with Perl:
perl -e 'use DBI;$h = DBI->connect("dbi:ODBC:baugi","sa","easysoft");$s
= $h->prepare(q{insert into mje values(?)});
$s->execute(20.20);$s->execute("20.20");$r = $h->prepare(q{select * from
mje});$r->execute;print DBI::dump_results($r)'
'20.2000'
'20.2000'
'20.2000'
'20.2000'
4 rows
Having said all that it appears I can get this to work with the
latest freeTDS and SQL Server Express anyway but you should be aware not
all ODBC drivers are created equal, freeTDS does not support
SQLDescribeParam and this may impact how you bind parameters.
If you continue to have problems I suggest you follow the MS advice and
use convert (convert(money, ?)) or specifically set the bind type.
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
