Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 1869
Status: resolved
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: marc.bau [...] gmx.net
Cc:
AdminCc:

Bug Information
Severity: Critical
Broken in: 0.43
Fixed in: (no value)



Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0
X-Mailer: MIME-tools 5.405 (Entity 5.404)
Subject: cannot update/insert smalldatetime and money fields
X-RT-Original-Encoding: iso-8859-1
Content-Length: 1934
Download (untitled) / with headers
text/plain 1.8k
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
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0
X-Mailer: MIME-tools 5.405 (Entity 5.404)
X-RT-Original-Encoding: iso-8859-1
Content-Length: 3103
[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
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0
X-Mailer: MIME-tools 5.405 (Entity 5.404)
From: marc.bau [...] gmx.net
X-RT-Original-Encoding: iso-8859-1
Content-Length: 726
Download (untitled) / with headers
text/plain 726b
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
Return-Path: <jurlwin [...] bellatlantic.net>
Delivered-To: cpan-bug+dbd-odbc [...] pallas.eruditorum.org
Received: from pop018.verizon.net (pop018pub.verizon.net [206.46.170.212]) by pallas.eruditorum.org (Postfix) with ESMTP id 7740E11199 for <bug-DBD-ODBC [...] rt.cpan.org>; Thu, 12 Dec 2002 08:56:27 -0500 (EST)
Received: from URLWINLT ([63.76.230.60]) by pop018.verizon.net (InterMail vM.5.01.05.09 201-253-122-126-109-20020611) with ESMTP id <20021212135626.XBKK4558.pop018.verizon.net [...] URLWINLT> for <bug-DBD-ODBC [...] rt.cpan.org>; Thu, 12 Dec 2002 07:56:26 -0600
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
Message-Id: <000b01c2a1e6$3863a680$9a01010a [...] softmatic.esoftmatic.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-Msmail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.4024
In-Reply-To: <rt-1869-4518.5.88855779659994 [...] cpan.org>
Importance: Normal
X-Mimeole: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Authentication-Info: Submitted using SMTP AUTH LOGIN at pop018.verizon.net from [63.76.230.60] at Thu, 12 Dec 2002 07:56:16 -0600
RT-Send-Cc:
X-RT-Original-Encoding: us-ascii
Content-Length: 1273
Download (untitled) / with headers
text/plain 1.2k
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 >
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0
X-Mailer: MIME-tools 5.405 (Entity 5.404)
From: marc.bau [...] gmx.net
X-RT-Original-Encoding: iso-8859-1
Content-Length: 633
Download (untitled) / with headers
text/plain 633b
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
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0
X-Mailer: MIME-tools 5.405 (Entity 5.404)
X-RT-Original-Encoding: iso-8859-1
Content-Length: 355
Download (untitled) / with headers
text/plain 355b
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
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0
X-Mailer: MIME-tools 5.405 (Entity 5.404)
X-RT-Original-Encoding: iso-8859-1
Content-Length: 584
Download (untitled) / with headers
text/plain 584b
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
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0
X-Mailer: MIME-tools 5.405 (Entity 5.404)
X-RT-Original-Encoding: iso-8859-1
Content-Length: 1506
Download (untitled) / with headers
text/plain 1.4k
[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
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0
X-Mailer: MIME-tools 5.405 (Entity 5.404)
X-RT-Original-Encoding: iso-8859-1
Content-Length: 470
Download (untitled) / with headers
text/plain 470b
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
Content-Type: text/plain
Content-Disposition: inline
Content-Transfer-Encoding: binary
MIME-Version: 1.0
X-Mailer: MIME-tools 5.405 (Entity 5.404)
From: marc.bau [...] gmx.net
X-RT-Original-Encoding: iso-8859-1
Content-Length: 841
Download (untitled) / with headers
text/plain 841b
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


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.