DBD::ODBC and binding to a money column

thor has asked for the wisdom of the Perl Monks concerning the following question:

Greetings monks,

I was helping someone write a tool at work yesterday when I hit a snag in using the DBI that has me a bit confused. We were searching a table on an MS-SQL database using DBD::ODBC as the connecting module. Let's say for the sake of argument that the query was something like

$sth=$dbh->prepare(q(select * from table_name where money_column=?));
[download]

When we tried to run that query by using $sth->execute(7), we got an error saying that you couldn't bind a varchar to a money column. FUBAR! I found a reference to this problem here, but I find it hard to believe that it's been over 4 years and this problem still exists. I'd rather not go through the bind_column business mentioned in that node, but if that's what it, so be it.

Thanks in advance,

thor

Feel the white light, the light within Be your own disciple, fan the sparks of will For all of us waiting, your kingdom will come

Comment on DBD::ODBC and binding to a money column Select or Download Code

Replies are listed 'Best First'.
Re: DBD::ODBC and binding to a money column by mpeppler (Vicar) on Dec 16, 2004 at 15:24 UTC
The whole problem comes down to this: does the client (DBD::ODBC in this case) get a description of the datatypes for each of the placeholders? If it does, then it can "know" that the data that you pass in should be converted to MONEY (or NUMERIC, or whatever). If it doesn't, then you have to supply that information - hence the call to `$sth->bind_param(1, 7, SQL_NUMERIC)` to let the driver know that the data needs to be passed as numeric data. Michael	[reply] [d/l]
Re: DBD::ODBC and binding to a money column by r34d0nl1 (Pilgrim) on Dec 16, 2004 at 18:30 UTC
When forced to deal with MS-SQL we used freetds and the sybase module (DBD::Sybase) to connect and retrieve the needed information from the 'database'. http://www.freetds.org/ has more detailed information about it. It's easy to install and configure. It is working fine and the new version is now able to deal with binds. I hope it helps.	[reply]
Re: DBD::ODBC and binding to a money column by redhotpenguin (Deacon) on Dec 16, 2004 at 14:31 UTC
I've always been frustrated when having to deal with ODBC connections myself. It looks like if you are going to continue using MS-SQL you're going to have to deal with workarounds such as this. <open source pitch> I would highly recomment switching to PostgreSQL, or one of the other excellent open source databases, but it's understandable if that option isn't feasible for various reasons. Although if you had this same problem with an open source database, you can just go in the source and fix it instead of having to wait 4 years and continue work arounds. </open source pitch>	[reply]
Re^2: DBD::ODBC and binding to a money column by jfroebe (Parson) on Dec 16, 2004 at 16:11 UTC
It's not actually a bug in the odbc driver or in the DBD::ODBC module. The money datatype is in truth a float. It is not unusual to explicitly state the datatype as Michael Peppler wrote in another reply. This 'issue' is a non-issue ... it is probably preferrable to state the datatype when binding a column if the datatype is known. Switching DBMSs (opensource or commercial) is overkill. I won't get into the whole opensource vs commercial dbms's as it really isn't relevant here. Jason L. Froebe Team Sybase member No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1	[reply]
Re: DBD::ODBC and binding to a money column by kgraff (Monk) on Dec 16, 2004 at 15:32 UTC
It has been a couple years since I used DBD::ODBC but remember that it can be picky, like connect strings being case sensitive. Sounds like your database doesn't like the way Perl is free and easy with strings and numbers. Have you tried something like: `$sth->execute(7.00);` Kathy A. Graff http://kgraff.net/	[reply] [d/l]