Bug #1869 for DBD-ODBC: cannot update/insert smalldatetime and money fields

Wed Dec 11 20:13:32 2002 Guest - Ticket created

Subject:

cannot update/insert smalldatetime and money fields

hi we have problems with this DBD::ODBC driver. i have tested with this problem only the 0.43 and 0.48_? - but i haven't seen any fixes in the changes - therefor i think it is inside 1.01, too. here a example code - not working 1. $timestamp is a Unix Timestamp! 2. DAYSTAMP is a smalldatetime field my ($day,$mon,$year) = (localtime($timestamp))[3,4,5]; my $onlydate = sprintf("%02d.%02d.%04d",$day,$mon+1,$year+1900); my $sth=$dbh->prepare("UPDATE $dbtable_stats SET ORDERS_NEW_EUR=ORDERS_NEW_EUR+? WHERE (PARTNER=?) AND (DAYSTAMP=?)"); my $howmany=$sth->execute($diffvalue,$partner,$onlydate); if ($howmany eq '0E0') { $sth=$dbh->prepare("INSERT INTO $dbtable_affilian_stats (ORDERS_NEW,ORDERS_NEW_EUR,PARTNER,DAYSTAMP) VALUES (?,?,?,?)"); $sth->execute(1,$new_value,$partner,$onlydate); } $sth->finish(); the following will work - but i do not realy like this... my $sth=$dbh->prepare("UPDATE $dbtable_stats SET ORDERS_NEW_EUR=ORDERS_NEW_EUR+? WHERE (PARTNER=?) AND (DAYSTAMP=CONVERT(varchar(10), ?, 104))"); my $howmany=$sth->execute($diffvalue,$partner,$onlydate); if ($howmany eq '0E0') { $sth=$dbh->prepare("INSERT INTO $dbtable_affilian_stats (ORDERS_NEW,ORDERS_NEW_EUR,PARTNER,DAYSTAMP) VALUES (?,?,?,CONVERT(varchar(10), ?, 104))"); $sth->execute(1,$new_value,$partner,$onlydate); } $sth->finish(); a convert to smalldatetime is working too... additional to this - maybe the same source of problem... i have set up a MONEY column and tryed to update... this isn't working too! for more details about i need to review my tests - done some months ago. i have worked around by changing the field to float... but this is unpossible in future. i need to integrate the perl into a existing DB and there are very very often money fields... this will break my app :-( i hope you are able to help me about this... all this updates/inserts works with sql query and sql2k enterprisemanager... Kind Regards Marc Bauer

Wed Dec 11 22:22:17 2002 Guest - Correspondence added

[guest - Wed Dec 11 20:13:32 2002]: Show quoted text

> hi > > we have problems with this DBD::ODBC driver. i have tested with this > problem only the 0.43 and 0.48_? - but i haven't seen any fixes in > the changes - therefor i think it is inside 1.01, too. > > here a example code - not working > 1. $timestamp is a Unix Timestamp! > 2. DAYSTAMP is a smalldatetime field > > my ($day,$mon,$year) = (localtime($timestamp))[3,4,5]; > my $onlydate = sprintf("%02d.%02d.%04d",$day,$mon+1,$year+1900); > > my $sth=$dbh->prepare("UPDATE $dbtable_stats SET > ORDERS_NEW_EUR=ORDERS_NEW_EUR+? WHERE (PARTNER=?) AND > (DAYSTAMP=?)"); > my $howmany=$sth->execute($diffvalue,$partner,$onlydate); > if ($howmany eq '0E0') { > $sth=$dbh->prepare("INSERT INTO $dbtable_affilian_stats > (ORDERS_NEW,ORDERS_NEW_EUR,PARTNER,DAYSTAMP) VALUES (?,?,?,?)"); > $sth->execute(1,$new_value,$partner,$onlydate); > } > $sth->finish(); > > > the following will work - but i do not realy like this... > > my $sth=$dbh->prepare("UPDATE $dbtable_stats SET > ORDERS_NEW_EUR=ORDERS_NEW_EUR+? WHERE (PARTNER=?) AND > (DAYSTAMP=CONVERT(varchar(10), ?, 104))"); > my $howmany=$sth->execute($diffvalue,$partner,$onlydate); > if ($howmany eq '0E0') { > $sth=$dbh->prepare("INSERT INTO $dbtable_affilian_stats > (ORDERS_NEW,ORDERS_NEW_EUR,PARTNER,DAYSTAMP) VALUES > (?,?,?,CONVERT(varchar(10), ?, 104))"); > $sth->execute(1,$new_value,$partner,$onlydate); > } > $sth->finish(); > > > a convert to smalldatetime is working too... > > additional to this - maybe the same source of problem... i have set

up Show quoted text

> a MONEY column and tryed to update... this isn't working too! for > more details about i need to review my tests - done some months > ago. i have worked around by changing the field to float... but > this is unpossible in future. i need to integrate the perl into a > existing DB and there are very very often money fields... this

will Show quoted text

> break my app :-( > > i hope you are able to help me about this... all this updates/inserts > works with sql query and sql2k enterprisemanager... > > > Kind Regards > Marc Bauer

Marc, A small example would be best, however, I have two comments: 1) SQL_NUMERIC, and other "floating point" value inserts/updates were recently fixed for bind variables in version 1.0 2) You probably need to format your date in the ODBC specific format: {d 'YYYY-MM-DD'} instead of DD.MM.YYYY If either of these do not work for you, please create a sample script along the following lines: #!perl -w use DBI qw(:sql_types); use strict; my $dbh = DBI->connect(); # use DBI env variables for connection info $dbh->{RaiseError} = 1; $dbh->{PrintError} = 0; eval { $dbh->do("drop table PERL_DBD_TEST1"); }; $dbh->do("create table PERL_DBD_TEST1 (your-columns-here)"); my $sth = $dbh->prepare("your insert here"); # can put loop here.... $sth->execute(your params here); my $sth2 = $dbh->prepare("Your verify query here"); $sth2->execute; my @row; while (@row = $sth2->fetchrow_array) { print join(', ', @row), "\n"; } $dbh->disconnect; Thanks, Jeff

Thu Dec 12 03:58:36 2002 Guest - Correspondence added

From:

marc.bau [...] gmx.net

Dear Jeff, Show quoted text

> A small example would be best, however, I have two comments: > 1) SQL_NUMERIC, and other "floating point" value inserts/updates

were Show quoted text

> recently fixed for bind variables in version 1.0

ok... i will update. Show quoted text

> 2) You probably need to format your date in the ODBC specific

format: Show quoted text

> {d 'YYYY-MM-DD'} instead of DD.MM.YYYY

ok. good to know :-). i will change this... i have never used rt.cpan.org, too. but it seems to work... :-))) no problem with this mails... you so very are fast! cool! i need a week or more to review this fixes... my develop machine is on 5.8 and production is on 5.6... :-(. therefor i need to downgrade for recompileing the driver and testing will takes some additional time. Marc

Thu Dec 12 08:56:31 2002 jurlwin [...] bellatlantic.net - Correspondence added

From:	"Jeff Urlwin" <jurlwin [...] bellatlantic.net>
To:	<bug-DBD-ODBC [...] rt.cpan.org>, <"AdminCc ofcpanTicket#1869:" [...] fsck.com>
Subject:	RE: [cpan #1869] cannot update/insert smalldatetime and money fields
Date:	Thu, 12 Dec 2002 08:56:02 -0500
RT-Send-Cc:

Marc, Thanks Keep me informed. Regards, Jeff Show quoted text

> -----Original Message----- > From: Guest via RT [mailto:bug-DBD-ODBC@rt.cpan.org] > Sent: Thursday, December 12, 2002 3:59 AM > To: AdminCc of cpan Ticket #1869 : > Subject: [cpan #1869] cannot update/insert smalldatetime and > money fields > > > > This message about DBD-ODBC was sent to you by guest via rt.cpan.org > > Full context and any attached attachments can be found at: > <URL: https://rt.cpan.org/Ticket/Display.html?id=1869 > > > Dear Jeff, >

> > A small example would be best, however, I have two comments: > > 1) SQL_NUMERIC, and other "floating point" value inserts/updates

> were

> > recently fixed for bind variables in version 1.0

> ok... i will update. >

> > 2) You probably need to format your date in the ODBC specific

> format:

> > {d 'YYYY-MM-DD'} instead of DD.MM.YYYY

> ok. good to know :-). i will change this... > > i have never used rt.cpan.org, too. but it seems to work... :-))) no > problem with this mails... you so very are fast! cool! i need a week > or more to review this fixes... my develop machine is on 5.8 and > production is on 5.6... :-(. therefor i need to downgrade for > recompileing the driver and testing will takes some additional time. > > > Marc >

Thu Dec 12 11:03:41 2002 Guest - Correspondence added

From:

marc.bau [...] gmx.net

hi i found some time to test the date problem. this will all work - but behind the scenes is a convert :-). the documentation told me and EP/MDAC ODBC Driver is changing this code on a execute. {d 'YYYY-MM-DD'} is the same as CONVERT(DATETIME, '2002-11- 14', 102) another possible way is (DAYSTAMP = CONVERT(DATETIME, '14.11.2002', 104) for german date formats... but additional to this EP allows to use '14.11.2002' direcly without changing anything... and DBD-ODBC not. therefor it seems to be bedder or faster for me to use this CONVERT statement directly or will this break the portability to other DBI driver? Marc

Thu Dec 12 11:22:40 2002 Guest - Correspondence added

oh sorry... i haven't tested the last posting with perl and odbc driver.. this was from documentation only... UPDATE $db SET ORDERS_NEW_EUR=ORDERS_NEW_EUR+? WHERE (PARTNER=?) AND (DAYSTAMP={d ?}) will NOT work! the following will work... UPDATE $db SET ORDERS_NEW_EUR=ORDERS_NEW_EUR+? WHERE (PARTNER=?) AND (DAYSTAMP=CONVERT(DATETIME,?, 102)) Marc

Thu Dec 12 13:22:14 2002 JURL [...] cpan.org - Correspondence added

Mark, Actually I'd recommend using ... DAYSTAMP=? ... and bind in {d 'YYYY- MM-DD'} (with the single quote). See t/ODBCTEST.pm for formats (in the arrays). Jeff [guest - Thu Dec 12 11:22:40 2002]: Show quoted text

> oh sorry... i haven't tested the last posting with perl and odbc > driver.. this was from documentation only... > > UPDATE $db SET ORDERS_NEW_EUR=ORDERS_NEW_EUR+? WHERE (PARTNER=?) AND > (DAYSTAMP={d ?}) > > will NOT work! the following will work... > > UPDATE $db SET ORDERS_NEW_EUR=ORDERS_NEW_EUR+? WHERE (PARTNER=?) AND > (DAYSTAMP=CONVERT(DATETIME,?, 102)) > > > Marc

Thu Dec 12 13:28:14 2002 JURL [...] cpan.org - Correspondence added

[guest - Thu Dec 12 11:03:41 2002]: Show quoted text

> hi > > i found some time to test the date problem. > > this will all work - but behind the scenes is a convert :-). the > documentation told me and EP/MDAC ODBC Driver is changing this code

on Show quoted text

> a execute. {d 'YYYY-MM-DD'} is the same as CONVERT(DATETIME, '2002-

11- Show quoted text

> 14', 102) > > another possible way is (DAYSTAMP = CONVERT(DATETIME, '14.11.2002', > 104) for german date formats... but additional to this EP allows to > use '14.11.2002' direcly without changing anything... and DBD-ODBC > not. > > therefor it seems to be bedder or faster for me to use this CONVERT > statement directly or will this break the portability to other DBI > driver?

That is going to be driver specific, however, it's going to get converted anyway, unless DBD::ODBC starts parsing the date and plugging it into the structure. Since 99% of the drivers do this well, I've decided to leave it alone and let the driver handle the conversion(s) necessary. Well, If anything at all, DBI will probably move to allow the ODBC standard for date time format escapes (i.e. the {d } syntax), so you'd be OK in the long run. The reality is that each database handles dates in their own ways, so I'd expect that date time handling will need to be crafted carefully -- and is unlikely to be completely portable on the insert side (although, with Oracle for example, you can alter the session to do whatever you want and hide it from the rest of the program...). Regards, Jeff

Thu Dec 12 16:55:54 2002 Guest - Correspondence added

Hi Jeff Show quoted text

> Actually I'd recommend using ... DAYSTAMP=? ... and bind in {d 'YYYY- > MM-DD'} (with the single quote). See t/ODBCTEST.pm for formats (in > the arrays).

sorry for my stupid question... i'm new to DBI - not a beginner but on the other side no DBI freak :-). what do you mean with "bind in {d 'YYYY-MM-DD'}"? what should i change in the skript? set the "$onlydate={d '2002-11-14'}"? hm... yes i'm new to this but i cannot realy see a difference... Marc

Thu Dec 12 17:08:45 2002 Guest - Correspondence added

From:

marc.bau [...] gmx.net

hi Show quoted text

> That is going to be driver specific, however, it's going to get > converted anyway, unless DBD::ODBC starts parsing the date and > plugging it into the structure. Since 99% of the drivers do this > well, I've decided to leave it alone and let the driver handle the > conversion(s) necessary.

yes - that is a good idea to leave it alone... Show quoted text

> Well, If anything at all, DBI will probably move to allow the ODBC > standard for date time format escapes (i.e. the {d } syntax), so

you'd Show quoted text

> be OK in the long run. The reality is that each database handles > dates in their own ways, so I'd expect that date time handling will > need to be crafted carefully -- and is unlikely to be completely

yes... but how schould i know what is the best and portable? what is the best now? use {d 'YYYY-MM-DD'} for best portability? Marc

Sat Feb 15 05:44:15 2003 JURL [...] cpan.org - Status changed from 'new' to 'resolved'