prb using Date/time field with DBI , odbc connected with Access 2000

Hi,

I have some problems using date/time field with DBI and a MS Access Database
over ODBC.

I try to insert a record containig a date :
$res = $dbh->do("INSERT INTO SIT_PREVISIONNEL_HISTO (......., DATEMAJ,
......) values (......,$rec->{majdate},.....)

$rec->{majdate} come from another table and i got it without a problem with
a SELECT query.

DBI trace shows that :

dbd_preparse scanned 2 distinct placeholders
dbd_st_prepare'd sql f61546664
 INSERT INTO SIT_PREVISIONNEL_HISTO
                                (...,...,...,DATEMAJ,...)
                                values (...,...,...2002-02-05 15??,...);
2   <- prepare= DBI::st=HASH(0x38b54d0) at DBI.pm line 930.
    -> execute for DBD::ODBC::st (DBI::st=HASH(0x38b54d0)~0x38b54f4)
    dbd_st_execute (for sql f61546664 after)...
st_execute/SQLExecute error -1 recorded: [Microsoft][Pilote ODBC Microsoft
Access]Champ COUNT incorrect  (SQL-07001)(DBD: st_execute/SQLExecute err=-1)
    !! ERROR: -1 '[Microsoft][Pilote ODBC Microsoft Access]Champ COUNT
incorrect  (SQL-07001)(DBD: st_execute/SQLExecute err=-1)'

i don't know why perl found 2 placeholders where none existed...
I'm relatively new to perl and i have certainly missed something in the
syntax.

TIA
Emmanuel


0
emmanuel
2/5/2002 3:36:27 PM
perl.dbi.users 11099 articles. 1 followers. Follow

5 Replies
618 Views

Similar Articles

[PageSpeed] 31

On 05-Feb-2002 Emmanuel Jaeckert wrote:
> Hi,
> 
> I have some problems using date/time field with DBI and a MS Access Database
> over ODBC.
> 
> I try to insert a record containig a date :
> $res = $dbh->do("INSERT INTO SIT_PREVISIONNEL_HISTO (......., DATEMAJ,
> .....) values (......,$rec->{majdate},.....)
> 
> $rec->{majdate} come from another table and i got it without a problem with
> a SELECT query.
> 
> DBI trace shows that :
> 
> dbd_preparse scanned 2 distinct placeholders
> dbd_st_prepare'd sql f61546664
>  INSERT INTO SIT_PREVISIONNEL_HISTO
>                                 (...,...,...,DATEMAJ,...)
>                                 values (...,...,...2002-02-05 15??,...);
> 2   <- prepare= DBI::st=HASH(0x38b54d0) at DBI.pm line 930.
>     -> execute for DBD::ODBC::st (DBI::st=HASH(0x38b54d0)~0x38b54f4)
>     dbd_st_execute (for sql f61546664 after)...
> st_execute/SQLExecute error -1 recorded: [Microsoft][Pilote ODBC Microsoft
> Access]Champ COUNT incorrect  (SQL-07001)(DBD: st_execute/SQLExecute err=-1)
>     !! ERROR: -1 '[Microsoft][Pilote ODBC Microsoft Access]Champ COUNT
> incorrect  (SQL-07001)(DBD: st_execute/SQLExecute err=-1)'
> 
> i don't know why perl found 2 placeholders where none existed...
> I'm relatively new to perl and i have certainly missed something in the
> syntax.

You need to quote the timestamp with ' to stop the preparse method looking for
placeholders.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development

0
martin
2/5/2002 3:59:18 PM
Emmanuel,

There are two ways to fix that.  It was a bug in DBD::ODBC.  I'm VERY
surprised I didn't catch it earlier.

	1) Upgrade to the Latest DBD::ODBC (0.33_3)
	or
	2) use bindings/placeholders for the dates.

> $res = $dbh->do("INSERT INTO SIT_PREVISIONNEL_HISTO (......., DATEMAJ,
> .....) values (......,$rec->{majdate},.....)

to
use DBI qw(:sql_types);		# important to change your use DBI at the top!

	my $sth = $dbh->prepare("INSERT INTO SIT_PREVISIONNEL_HISTO (.......,
DATEMAJ,
 .....) values (......,?,.....)
      $sth->bind_param(1, $rec->{majdate}, { TYPE => SQL_TIMESTAMP });
	$sth->execute;

Jeff

> Hi,
>
> I have some problems using date/time field with DBI and a MS
> Access Database
> over ODBC.
>
> I try to insert a record containig a date :
> $res = $dbh->do("INSERT INTO SIT_PREVISIONNEL_HISTO (......., DATEMAJ,
> .....) values (......,$rec->{majdate},.....)
>
> $rec->{majdate} come from another table and i got it without a
> problem with
> a SELECT query.
>
> DBI trace shows that :
>
> dbd_preparse scanned 2 distinct placeholders
> dbd_st_prepare'd sql f61546664
>  INSERT INTO SIT_PREVISIONNEL_HISTO
>                                 (...,...,...,DATEMAJ,...)
>                                 values (...,...,...2002-02-05 15??,...);
> 2   <- prepare= DBI::st=HASH(0x38b54d0) at DBI.pm line 930.
>     -> execute for DBD::ODBC::st (DBI::st=HASH(0x38b54d0)~0x38b54f4)
>     dbd_st_execute (for sql f61546664 after)...
> st_execute/SQLExecute error -1 recorded: [Microsoft][Pilote ODBC Microsoft
> Access]Champ COUNT incorrect  (SQL-07001)(DBD:
> st_execute/SQLExecute err=-1)
>     !! ERROR: -1 '[Microsoft][Pilote ODBC Microsoft Access]Champ COUNT
> incorrect  (SQL-07001)(DBD: st_execute/SQLExecute err=-1)'
>
> i don't know why perl found 2 placeholders where none existed...
> I'm relatively new to perl and i have certainly missed something in the
> syntax.
>
> TIA
> Emmanuel

0
jurlwin
2/5/2002 4:13:41 PM
Martin -- oops I forgot to mention that, too.  But the upgrade will be
important, too...

Jeff
>
> You need to quote the timestamp with ' to stop the preparse
> method looking for
> placeholders.
>
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> Development
>
>

0
jurlwin
2/5/2002 4:16:18 PM
Thank you for your answers, i can't test it now but for now i tried to
update DBD-ODBC from 0.28 to 0.33 from ppm but it didn't work (i use perl
from Active State).
I think i can test it tomorrow.

Bye
Emmanuel

"Jeff Urlwin" <jurlwin@bellatlantic.net> a �crit dans le message news:
OEEMJFLOJPABMFADAPIKMEAMDAAA.jurlwin@bellatlantic.net...
> Emmanuel,
>
> There are two ways to fix that.  It was a bug in DBD::ODBC.  I'm VERY
> surprised I didn't catch it earlier.
>
> 1) Upgrade to the Latest DBD::ODBC (0.33_3)
> or
> 2) use bindings/placeholders for the dates.
>
> > $res = $dbh->do("INSERT INTO SIT_PREVISIONNEL_HISTO (......., DATEMAJ,
> > .....) values (......,$rec->{majdate},.....)
>
> to
> use DBI qw(:sql_types); # important to change your use DBI at the top!
>
> my $sth = $dbh->prepare("INSERT INTO SIT_PREVISIONNEL_HISTO (.......,
> DATEMAJ,
>  .....) values (......,?,.....)
>       $sth->bind_param(1, $rec->{majdate}, { TYPE => SQL_TIMESTAMP });
> $sth->execute;
>
> Jeff
>
> > Hi,
> >
> > I have some problems using date/time field with DBI and a MS
> > Access Database
> > over ODBC.
> >
> > I try to insert a record containig a date :
> > $res = $dbh->do("INSERT INTO SIT_PREVISIONNEL_HISTO (......., DATEMAJ,
> > .....) values (......,$rec->{majdate},.....)
> >
> > $rec->{majdate} come from another table and i got it without a
> > problem with
> > a SELECT query.
> >
> > DBI trace shows that :
> >
> > dbd_preparse scanned 2 distinct placeholders
> > dbd_st_prepare'd sql f61546664
> >  INSERT INTO SIT_PREVISIONNEL_HISTO
> >                                 (...,...,...,DATEMAJ,...)
> >                                 values (...,...,...2002-02-05 15??,...);
> > 2   <- prepare= DBI::st=HASH(0x38b54d0) at DBI.pm line 930.
> >     -> execute for DBD::ODBC::st (DBI::st=HASH(0x38b54d0)~0x38b54f4)
> >     dbd_st_execute (for sql f61546664 after)...
> > st_execute/SQLExecute error -1 recorded: [Microsoft][Pilote ODBC
Microsoft
> > Access]Champ COUNT incorrect  (SQL-07001)(DBD:
> > st_execute/SQLExecute err=-1)
> >     !! ERROR: -1 '[Microsoft][Pilote ODBC Microsoft Access]Champ COUNT
> > incorrect  (SQL-07001)(DBD: st_execute/SQLExecute err=-1)'
> >
> > i don't know why perl found 2 placeholders where none existed...
> > I'm relatively new to perl and i have certainly missed something in the
> > syntax.
> >
> > TIA
> > Emmanuel
>


0
emmanuel
2/5/2002 5:39:00 PM
I'd suggest, given that ActiveState is behind and I'm patching like mad
lately, that you use bind parameters to work around it.  It's not a bad
thing and, at least for Oracle, the server can cache the parsing and
execution plan for the query, the more you use place holders and have the
same "exact" query every time (the bind values can change, but if the query
is character-for-character the same, Oracle (and probably others) are
smarter about executing them (or at least execute them a little faster
avoiding the overhead of repreparing them).

Jeff

> -----Original Message-----
> From: Emmanuel Jaeckert [mailto:emmanuel@jaeckert.org]
> Sent: Tuesday, February 05, 2002 12:39 PM
> To: dbi-users@perl.org
> Subject: Re: prb using Date/time field with DBI , odbc connected with
> Access 2000
>
>
> Thank you for your answers, i can't test it now but for now i tried to
> update DBD-ODBC from 0.28 to 0.33 from ppm but it didn't work (i use perl
> from Active State).
> I think i can test it tomorrow.
>
> Bye
> Emmanuel
>
> "Jeff Urlwin" <jurlwin@bellatlantic.net> a �crit dans le message news:
> OEEMJFLOJPABMFADAPIKMEAMDAAA.jurlwin@bellatlantic.net...
> > Emmanuel,
> >
> > There are two ways to fix that.  It was a bug in DBD::ODBC.  I'm VERY
> > surprised I didn't catch it earlier.
> >
> > 1) Upgrade to the Latest DBD::ODBC (0.33_3)
> > or
> > 2) use bindings/placeholders for the dates.
> >
> > > $res = $dbh->do("INSERT INTO SIT_PREVISIONNEL_HISTO (......., DATEMAJ,
> > > .....) values (......,$rec->{majdate},.....)
> >
> > to
> > use DBI qw(:sql_types); # important to change your use DBI at the top!
> >
> > my $sth = $dbh->prepare("INSERT INTO SIT_PREVISIONNEL_HISTO (.......,
> > DATEMAJ,
> >  .....) values (......,?,.....)
> >       $sth->bind_param(1, $rec->{majdate}, { TYPE => SQL_TIMESTAMP });
> > $sth->execute;
> >
> > Jeff
> >
> > > Hi,
> > >
> > > I have some problems using date/time field with DBI and a MS
> > > Access Database
> > > over ODBC.
> > >
> > > I try to insert a record containig a date :
> > > $res = $dbh->do("INSERT INTO SIT_PREVISIONNEL_HISTO (......., DATEMAJ,
> > > .....) values (......,$rec->{majdate},.....)
> > >
> > > $rec->{majdate} come from another table and i got it without a
> > > problem with
> > > a SELECT query.
> > >
> > > DBI trace shows that :
> > >
> > > dbd_preparse scanned 2 distinct placeholders
> > > dbd_st_prepare'd sql f61546664
> > >  INSERT INTO SIT_PREVISIONNEL_HISTO
> > >                                 (...,...,...,DATEMAJ,...)
> > >                                 values (...,...,...2002-02-05
> 15??,...);
> > > 2   <- prepare= DBI::st=HASH(0x38b54d0) at DBI.pm line 930.
> > >     -> execute for DBD::ODBC::st (DBI::st=HASH(0x38b54d0)~0x38b54f4)
> > >     dbd_st_execute (for sql f61546664 after)...
> > > st_execute/SQLExecute error -1 recorded: [Microsoft][Pilote ODBC
> Microsoft
> > > Access]Champ COUNT incorrect  (SQL-07001)(DBD:
> > > st_execute/SQLExecute err=-1)
> > >     !! ERROR: -1 '[Microsoft][Pilote ODBC Microsoft Access]Champ COUNT
> > > incorrect  (SQL-07001)(DBD: st_execute/SQLExecute err=-1)'
> > >
> > > i don't know why perl found 2 placeholders where none existed...
> > > I'm relatively new to perl and i have certainly missed
> something in the
> > > syntax.
> > >
> > > TIA
> > > Emmanuel
> >
>
>
>

0
jurlwin
2/6/2002 12:58:05 AM
Reply:

Similar Artilces:

Inserting Row into Access 2000 Table Using DBI:ODBC
Hello, I'm trying to write a program that will update a simple Access 2000 table. Whenever I try to run the insert statement, though, I get the following error back: " DBD::ODBC::st execute failed: [Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query. (SQL-S1000)(DBD:st_execute/SQLExecute err=-1) at dbi_ins_test.pl line 56. " This is what the prepared statement looks like: " my $sth5 = $dbh->prepare("insert into dbi_test ( district_cd ) values (?)") or die "Cant prepare jcdb insert" . $dbh->errst...

DBI, MS Access, inserting/updating a Access Date/Time value
Hi, Does anyone have any idea what is up with Microsoft Access and its ridiculous Date/Time formatting options? It doesn't seem to matter what format I attempt to insert with. The directory and db file permissions are all Full Control. I'm using the "#" around the Date/Time format as required. The database has nothing as the Format in Design view on the General tab. The insert below has never worked once. On the up side if I run an insert that has no mention of the Date/Time field then it will just insert the record using the =Now() function as the default va...

user count using nds odbc connection in access
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C73FAE.6D14D650 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Has anyone had luck linking to an odbc nds source from access to query = things from edirectory? I need to get a user object count and would like = the list so I can make sure there are no ex-employees on the list. I = have the odbc link set up and my query runs but locks up and access = takes a dive after processing a little over 200 users. Here is my sql = statement. Also, the nds_name fiel...

MS SQL through MS Access using DBI:ODBC
Hi When connecting an MS SQL database from MS Access it is necessary to use the option dbSeeChanges with db.openrecordset. This is ok when using Visual Basic but is it possible to access the data using DBI:ODBC. Win2000 MS SQL version 7 MS Access 97 perl 5.005 (build 509) W32ODBC.pm,v 10.1 1998/08/14 Thanks. Ian ...

Problems moving date fields between systems using DBI
Hello, I am attempting to move data from one database to another using perl. Things work fine until I run into date fields. I have checked the fine manual as well as Tim's book and I haven't found much of help in either. There is some dicussion of how an insert works, where you hand craft the insert and have a date. I have created a small test script that shows the problem. #!/usr/bin/perl use DBI qw( neat_list ); $ora_uid = 'XXXX'; $ora_pw = 'YYYY"; $ora_dbase = 'DDDD"; $ora_DSN = join ':','dbi','Oracle',$or...

Writing to MS Access using DBI and DBD::ODBC
--=====================_26212221==_ Content-Type: multipart/alternative; boundary="=====================_26212231==.ALT" --=====================_26212231==.ALT Content-Type: text/plain; charset="us-ascii"; format=flowed I can read my MS Access DB using DBI but I cannot write, delete, or update it. I have seen a couple relevant discussions posted but none (I've seen) offered a solution. The web site is using: Windows 2000 Server Perl v 5.8.0 build 805 DBI v 1.37 from ActiveState DBD-ODBC v 1.06 from ActiveState ...

Accessing MS Access through the DBI ODBC
Hi all, I am trying to access an MS Access database via perl DBI and I am unable = to. I checked the security and there is only one 'Admin' user with no = password and I use that in my connection string. However, I cannot = connect. Please help. Regards, Javier Moreno Hi Javier, > I am trying to access an MS Access database via perl DBI and I am unable to. I checked the security and there is only one 'Admin' user with no password and I use that in my connection string. However, I cannot connect. Please help. > 1. Create an ODBC connection via %Sys...

[perl5-dbi/DBI-Test] 9016ab: Replace %$DBD_DBM_SETTINGS our var with use of DBI...
----==_mimepart_52cd4b65d99a9_473dd75d481011f8 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Branch: refs/heads/master Home: https://github.com/perl5-dbi/DBI-Test Commit: 9016ab6b16ca17737ba583c93ae0248be4649a6d https://github.com/perl5-dbi/DBI-Test/commit/9016ab6b16ca17737ba583c93ae0248be4649a6d Author: Tim Bunce <Tim.Bunce@pobox.com> Date: 2014-01-08 (Wed, 08 Jan 2014) Changed paths: M sandbox/tim/lib/Context.pm M sandbox/tim/tumbler.pl Log Message: ----------- Replace %$DBD_DBM_SETTINGS our var with us...

Need help connecting to Teradata using DBI and DBD::ODBC packages
--bcaec5015d3940ad0504ae15d2e4 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Jeff, The following perl script is used to connect to SQL server database using DBI and DBD::ODBC modules. *my *$dbh =3D DBI->*connect*("DBI:ODBC:Driver=3D{SQL Server};Server=3D$db_instance;Database=3D$db_name;UID=3D$db_user;PWD=3D$db_= pass") or *die*("\n\nCONNECT ERROR:\n\n$DBI::errstr"); I used the same syntax to connect to Teradata but I couldn't connect. I have Installed the cli, teragss, and tdicu Teradata software pac...

Inserting Date into Access Date/Time Field
Everytime i capture a date from an asp:dropdownlist and try to insert it into an access date/time field i keep getting a Data type mismatch in criteria expression error (0x80040e07). is there a way i need to format the data before inserting? Any help would be ace!1 using System; 2 using System.Data; 3 using System.Data.OleDb; 4 using System.Configuration; 5 using System.Web; 6 using System.Web.Security; 7 using System.Web.UI; 8 using System.Web.UI.WebControls; 9 using System.Web.UI.WebControls.WebParts; 10 using System.Web.UI.HtmlControls; 11 12 ...

How to use DBI to connect to MS =?iso-8859-1?q?Access=3F?=
Hello, I want to use DBI to connect to MS Access database. In my code, I have: #!c:/perl/bin/perl.exe use CGI qw(:standard :html); use DBI; $DBase="C:\db1.mdb"; my $dbh=DBI->connect('dbi:ODBC:$DBase') or die "Can`t connect to database: " . DBI->errstr; ...... But I get this error: install_driver(ODBC) failed: Can't locate DBD/ODBC.pm in @INC(@INC contains: c:/Perl/lib c:/Perl/site/lib. Perhaps the DBD::ODBC perl module hasn't been fully installed, or perhaps the capitalisation of 'ODBC' isn't right. Available dri...

AppConfig::DBI
--Apple-Mail-3-257921081 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed [ this is pre-CPAN until Monday ] --Apple-Mail-3-257921081 Content-Disposition: attachment; filename=AppConfig-DBI-0.01.tar.gz Content-Transfer-Encoding: base64 Content-Type: application/x-gzip; x-unix-mode=0644; name="AppConfig-DBI-0.01.tar.gz" H4sICDvGgDwCA0FwcENvbmZpZy1EQkktMC4wMS50YXIA7Vp7U+PItedf+lOc9bBluGUL29iQawZq POCZcYVXMLO7qc3GkaW2rSBLXqmFx2H57vmdbknYBpJ7U7tMaks9Q1lqdZ/u8350d2azkzAYeePq 6ftetWbV6rsbv3ar1Zq1g1YLv7Xawb75rTWa5te0jdpBs1mrN...

How to insert date & time into access data field using data adapters?
If I use regular INSERT SQL query I succeed to put both date & time in Access date time field. I must use data adapters for million reasons, but for some strange reason, time in database is ignored. I saw in this forum that there is strange solution to convert date time variable to string, but that didn't work in my case. This is how .NET framework declared parameter: Me.OleDbInsertCommand17.Parameters.Add(New System.Data.OleDb.OleDbParameter("SubmissionDate", System.Data.OleDb.OleDbType.DBDate, 0, "SubmissionDate")) I tried to change parameter to DBTimeStamp, but that didn...

[perl5-dbi/dbi] d353b6: Add release date to DBI 1.632 heading in Changes
----==_mimepart_545a0ef45875a_65bd3fe14ad7d2bc35fd Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Branch: refs/heads/master Home: https://github.com/perl5-dbi/dbi Commit: d353b6a93f38d1a72e09e6b620e0ec3fa33c2cfe https://github.com/perl5-dbi/dbi/commit/d353b6a93f38d1a72e09e6b620e0ec3fa33c2cfe Author: Tim Bunce <Tim.Bunce@pobox.com> Date: 2014-11-05 (Wed, 05 Nov 2014) Changed paths: M Changes Log Message: ----------- Add release date to DBI 1.632 heading in Changes ----==_mimepart_545a0ef45875a_65bd3fe14ad...

Web resources about - prb using Date/time field with DBI , odbc connected with Access 2000 - perl.dbi.users

Connected sum - Wikipedia, the free encyclopedia
In mathematics , specifically in topology , the operation of connected sum is a geometric modification on manifolds . Its effect is to join two ...

‘The Washington Post’ Cranks Up Facebook-Connected Political News App The Issue Engine
The Washington Post enhanced its coverage of the 2012 presidential election with its release of news application The Issue Engine , which allows ...

The Value of a Facebook-connected Mobile User
... back more often, and are more likely to become paying customers. Many top game developers, for example, have also found that Facebook-connected ...

Facebook mobile game users play longer, spend more than users not connected with the social network
Mobile game players logged in via Facebook generate more revenue for developers, Facebook Games Strategic Partner Manager Sara Brooks said today ...

CMS-Connected (@cmsconnected) on Twitter
Webinar on Saving your Intranet from the Boneyard (and more) with @sethearley @RHarbridge @JiveSoftware Here => ow.

Better connected: Twitter for BlackBerry
A new version of Twitter for BlackBerry introduces new features that make it easier to connect to the people and things you care about. Twitter ...

Global Trade Network-Exporters,Importers,Manufacturers,Wholesalers,Suppliers,Buyers Connected
Find, Connect & Engage with new Buyers, Suppliers, Manufacturers, Wholesalers, Exporters and Importers all-over the world.

Republicans Connected for iPhone, iPod touch, and iPad on the iTunes App Store
Get Republicans Connected on the App Store. See screenshots and ratings, and read customer reviews.

AT&T Kicks Off Major Campaign Encompassing Smart Cars, Connected Homes
Last week at the consumer electronics show, AT&T announced a host of new products and services including a Siri-like assistant, a partnership ...

CES 2016: Cooler Master’s MasterWatt Connected Digital PSU Almost Ready
... also had Bluetooth. The idea was that the PSU could be monitored on the system when powered on, or a user could log in to a home server connected ...

Resources last updated: 1/16/2016 6:05:27 PM