MSSQL Server driver seems to require the "CONVERT(MONEY, )" syntax.

Hello --

"Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query."

I'm trying to nail down this issue. Does anyone know if there is a way to get around this at the DBI level when using DBD::ODBC/Sybase?

Some (nearly related) references:
http://www.mail-archive.com/dbi-users@perl.org/msg16398.html
http://lists.ibiblio.org/pipermail/freetds/2006q3/020422.htm l

It appears that others have determined it is a MsSQL SQL Server problem -- not in the freeTDS driver, or unixODBC, for example.

I just need to know, if possible, if this is a dead issue, or if anyone has any notion that somebody might be putting a workaround into the code, or if there is a standard fix.

The problem, for me, is that I like using DBIx::Class, but any SQL prepared with a placeholder ('?') will need Convert(money, ?) in the appropriate place. Of course the SQL is magically generated, but not the conversion.

Thread here: http://lists.scsys.co.uk/pipermail/dbix-class/2007-August/00 4781.html

So, if I could tell DBI to fix this at some different level. Suggestions to use bind* calls seem to have zero effect, afaict.

Any clue appreciated, including 'forget about it', if that's the general consensus and I'll just code up my hackaround as best I can.

Cheers,

--
Michael Higgins

Re: MSSQL Server driver seems to require the "CONVERT(MONEY, )"

On Mon, 2007-08-13 at 11:43 -0700, Michael Higgins wrote:

> Hello --
>
> "Implicit conversion from data type varchar to money is not allowed.
> Use the CONVERT function to run this query."

I've certainly had that problem before ( I was one of the posters in the
links you gave ). The consensus at the time ( in the FreeTDS list ) was
that this was a SQL Server bug ( perhaps an intentional one ), and that
no-one was particularly interested in implementing work-arounds. I had
hit the bug in developing Gtk2::Ex::DBI and Gtk2::Ex::Datasheet::DBI,
where I generate SQL code to insert / update records. At this point, I
considered implementing my own work-around, ( basically adding the
'convert' function around MONEY fields ), but then I decided that I
wasn't interested in pandering to SQL Server's ridiculous requirements
either, so I converted all my MONEY fields ( what the hell is a MONEY
field anyway ) to DECIMAL fields, and I haven't had any problems since
( in the Perl side of things anyway - MS Access now treats the DECIMAL
fields as VARCHAR, but we're moving everything to from SQL Server and
Access to MySQL and Perl, so this doesn't matter for us ). Be careful
that you don't mangle your data when you do the conversion - back things
up first.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au