RE: [dbi] DBD::ODBC, FreeTDS, SQL Server and 'money' column type
Daniel, According to SQLGetTypeInfo SQL Server type money is a SQL_DECIMAL. When DBD::ODBC sees a SQL_DECIMAL is binds it as a SQL_VARCHAR - there is no quoting involved because you are using bound parameters. A quick test with isql from unixODBC showed: SQL create table mjemny (a integer, b money) SQLRowCount returns 0 SQL insert into mjemny values (1, 1) SQLRowCount returns 1 SQL insert into mjemny values (1, '23') [37000][unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. [37000][unixODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'test.dbo.mjemny', column 'b'. Use the CONVERT function to run this query. [ISQL]ERROR: Could not SQLExecute which reproduces your problem I think (and is probably what you are referring to when you found this happens when using quotes). Using a convert would be your easiest solution (or change the column type). Other than that, DBD::ODBC would have to bind it as a SQL_C_FLOAT or SQL_C_DOUBLE (which would not be exact and therefore no good for a money column) or a SQL_C_NUMERIC with a scale of 4 (since that's what sql server says money has). Martin -- Martin J. Evans Easysoft Ltd, UK Development On 12-Jul-2005 Daniel Kasak wrote: Hi all. I'm using the process: my $sth = $dbh-prepare ( update xxx set a=?, b=?, c=? where d=? ); $sth-execute( $a, $b, $c, $d ); to update data in a SQL Server database. It works fine most of the time, but if I have a 'money' column type in SQL Server, I get the following error: [unixODBC][FreeTDS][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'NUSsql.dbo.EAPosting', column 'EAPTotal'. Use the CONVERT function to run this query. (SQL-) I've googled around and found that this error occurs if you enclose a money value in single quotes. There are a couple of problems with this. Firstly, I'm not quoting the values ... I assume something further up the chain is. Possibly DBD::ODBC or FreeTDS? Secondly, I've got a couple of layers of abstraction between getting the data, processing it, and updating the database, and I really can't ( without some *major* work ) insert convert() functions around things - particularly since a lot of the stuff I'm doing is supposed to be db-neutral. I suppose I can convert the data in SQL Server to something else. I didn't set the table up - I wouldn't have used 'money' anyway, but I'm not sure what effect changing it now will have. Anyone encountered this before? Looks like a bug to me. -- 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: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
Re: DBD::ODBC, FreeTDS, SQL Server and 'money' column type
Have you tried putting the appropriate money symbol in the front of the string ? Such as $ for US currency. HTH, Chuck [EMAIL PROTECTED] wrote: Hi all. I'm using the process: my $sth = $dbh-prepare ( update xxx set a=?, b=?, c=? where d=? ); $sth-execute( $a, $b, $c, $d ); to update data in a SQL Server database. It works fine most of the time, but if I have a 'money' column type in SQL Server, I get the following error: [unixODBC][FreeTDS][SQL Server]Disallowed implicit conversion from data type varchar to data type money, table 'NUSsql.dbo.EAPosting', column 'EAPTotal'. Use the CONVERT function to run this query. (SQL-) I've googled around and found that this error occurs if you enclose a money value in single quotes. There are a couple of problems with this. Firstly, I'm not quoting the values ... I assume something further up the chain is. Possibly DBD::ODBC or FreeTDS? Secondly, I've got a couple of layers of abstraction between getting the data, processing it, and updating the database, and I really can't ( without some *major* work ) insert convert() functions around things - particularly since a lot of the stuff I'm doing is supposed to be db-neutral. I suppose I can convert the data in SQL Server to something else. I didn't set the table up - I wouldn't have used 'money' anyway, but I'm not sure what effect changing it now will have. Anyone encountered this before? Looks like a bug to me.
DBD::mysql 3.0002 released
Dear developers, Version 3.0002 DBD::mysql, the perl DBI interface to MySQL,has just been released! Version 3.0002 Fixes the problem with the package had problems being built with MySQL 4.1, as was fixed on 3.0001_3. Many of you informed me about this issue and I'm very thankful for your help! Thanks to: Tom Parkison, Anup Singh, Sergey Skvortsov, and other users! This should stabilise the 3.0002 tree for some time, and the production releases won't be as frequent. I will be working on adding features to the 3.0002 Dev tree, which will include server side prepare being turned on by default, multiple result set support (for stored procedures in 5.0), as well as not having to emulate LIMIT placeholders (fixed in 5.0.x), plus many other enhancements. As always, you are welcome to send me patches - I enjoy working with the community in improving and enhancing DBD::mysql! Please, if anyone has any problems or questions with the driver, please feel free to email me, or especially post to dbi-users@perl.org (if you are subscribed of course!), and if you find bugs, please report them to http://bugs.mysql.com These versions for this module can be found at CPAN: http://search.cpan.org/dist/DBD-mysql/ The files: file: $CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-3.0002.tar.gz size: 130077 bytes md5: df70ba084c97f5f7c2a997c3de2f0ad0 Again, as always, thank you for using DBD::mysql and MySQL! Patrick Galbraith Senior Software Developer [EMAIL PROTECTED] http://www.mysql.com Whatever action a great man performs, common men follow. Whatever standards he sets by exemplary acts, all the world pursues -- Bhagavad Gita
Re: DBD::ODBC, FreeTDS, SQL Server and 'money' column type
Chuck Fox wrote: Have you tried putting the appropriate money symbol in the front of the string ? Such as $ for US currency. HTH, Chuck Thanks for the reply. No I haven't tried that. The layer of software that does the database interaction has no idea about the field type and it's special requirements - nor do I want it to. Instead I'm just going to change the field type to a decimal - which I basically decided to do yesterday. I was mainly posting here in case someone with some spare time felt motivated to fix it ... as I indicated, I believe this is a bug. I would consider doing it myself, but I don't have *any* spare time at the moment, and I'm not overly interested in SQL Server or FreeTDS. I'll just make a note in my project that this particular module is not compatible with 'money' types in SQL Server and leave it at that. Thanks again for the reply. Dan -- 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: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au
