RE: errors reading linked table when row contains Timestamp

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hello Bill,

Based on my scope I think the only workaround here is what John mentioned
in his reply that you can round the DB2 values to the nearest millisecond
the resolution of the double is usually good enough.

Best Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.




--------------------
| Thread-Topic: errors reading linked table when row contains Timestamp
| thread-index: AcVAYHTiZyMgi2awR6aJmt1nFbDSWg==
| X-WBNR-Posting-Host: 12.106.254.66
| From: "=?Utf-8?B?bXIuYmlsbA==?=" <HedoSandwich@xxxxxxxxxxxxxxxx>
| References: <8CE05694-1A83-4D04-9501-00DD9D5E16A5@xxxxxxxxxxxxx>
<nBOPFw#PFHA.3736@xxxxxxxxxxxxxxxxxxxxx>
| Subject: RE: errors reading linked table when row contains Timestamp
| Date: Wed, 13 Apr 2005 12:39:07 -0700
| Lines: 171
| Message-ID: <06DE016A-4418-46E4-BA00-E136719A2A3B@xxxxxxxxxxxxx>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.access.conversion
| Path: TK2MSFTNGXA01.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.conversion:3112
| NNTP-Posting-Host: tk2msftngxa03.phx.gbl 10.40.2.157
| X-Tomcat-NG: microsoft.public.access.conversion
|
| Thank you for the information. I was all over IBM's website yesterday
before
| I resorted to posting here, and read all about the CLI Configuration
Keywords
| available thru the configuration panel of their driver software. l had
| played around with several without success.
|
| I didn't see the newer ones that you pointed out though - namely
| MapTimeStampDescribe so I gave it a try. Unfortunately, the net result
was
| not any better. I could see that the data type had changed in Access
from
| Date/Time to char(26) and in other tables where the timestamp field was
not a
| part of the key, the records displayed properly with strings in the
timestamp
| columns. However, in the one table that uses timestamp as part of the
key,
| none of the records could be displayed now -- the cells all contained
#Name?,
| instead of #Deleted. In addition, an ODBC error messagebox popped open.
|
| I examined the ODBC trace and saw the error being returned from IBMs
driver
| is "SQL0180N The syntax of the string representation of a datetime value
is
| incorrect." I find that particularly ironic given that the KB article
you
| located at IBM's site stated that the workaround using
MapTimeStampDescribe
| would cure this exact error for an Access97 client. I'm doubtful that
| downgrading to Access 97 would help in this situation as I am still
incluned
| to believe that the timestamp column being a part of the natural primary
key
| is what is making this extra difficult.
|
| The basic thrust of the article at IBM's site stated that this was not
their
| problem as their database and drivers were abiding by the ODBC standards
| published at Microsoft's site. Their opening statement is, "The problem
is
| that Microsoft Access is using SQL_C_DEFAULT instead of providing an
actual
| data type during the SQLBindParameter() call." And I think the other
| gentleman that replied here did an excellent job of explaining the
| implications of what IBM meant when they said, "However, the buffers
| Microsoft Access gives DB2 contain SQL_C_CHAR data, i.e. ASCII data."
|
| So, of course now we're reduced to fingerpointing.
|
| Unless you have something else up your sleeve I'm afraid I will have to
find
| another client-server capable database tool to get this job done.
|
| Thanks,
| mr.bill
|
| "Peter Yang [MSFT]" wrote:
|
| > Hello,
| >
| > From IBM's website, I located a number of articles which discussed the
| > issue of Microsoft products having trouble with DB2 Timestamp data, and
| > provides recommended configuration switches that may help out. You may
| > wish to talk with your DB2 representatives to ensure these have already
| > been taken into account in your situation. Take a look at the
following:
| > vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
| >
| >
http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=%22Microsof
| > t+Access%22+%22Timestamp%22&uid=swg21153006&loc=en_US&cs=utf-8&lang=en
| >
| > That site describes the issue and recommends the following
configuration
| > parameters on the DB2 side. :
| >
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
| > vvvvvvvvvvvvvvvv
| > Solution The following CLI Configuration Keywords were added to DB2
v8.1
| > fixpack 4 as a workaround (depending on the data type of concern):
| >
| > MapTimeDescribe=1
| > MapTimeStampDescribe=1
| > MapDateDescribe=1
| > vvvvvvvvvvvvvvvvvvvvvvvv
| >
| > Also found a section of CLI Configuration options for the driver. See
| >
| >
http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&q1=%22Microsof
| > t+Access%22+%22Timestamp%22&uid=swg21179697&loc=en_US&cs=utf-8&lang=en
| >
| > http://www-1.ibm.com/support/docview.wss?uid=swg21164225
| >
| > One of the options is as follows:
| > ----------------------
| > 24 Reports TIME data as SQL_CHAR data. This patch value is used as a
| > workaround for Microsoft Access applications.
| >
| > Hope this helps.
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| >
| > =====================================================
| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| >
| >
| > --------------------
| > | Thread-Topic: errors reading linked table when row contains Timestamp
| > | thread-index: AcU/qAoJ6pGcdh/HRMS5/uKKKNUgqw==
| > | X-WBNR-Posting-Host: 12.106.254.66
| > | From: "=?Utf-8?B?bXIuYmlsbA==?=" <HedoSandwich@xxxxxxxxxxxxxxxx>
| > | Subject: errors reading linked table when row contains Timestamp
| > | Date: Tue, 12 Apr 2005 14:39:00 -0700
| > | Lines: 34
| > | Message-ID: <8CE05694-1A83-4D04-9501-00DD9D5E16A5@xxxxxxxxxxxxx>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | charset="Utf-8"
| > | Content-Transfer-Encoding: 7bit
| > | X-Newsreader: Microsoft CDO for Windows 2000
| > | Content-Class: urn:content-classes:message
| > | Importance: normal
| > | Priority: normal
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| > | Newsgroups: microsoft.public.access.conversion
| > | Path: TK2MSFTNGXA01.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.access.conversion:3107
| > | NNTP-Posting-Host: tk2msftngxa03.phx.gbl 10.40.2.157
| > | X-Tomcat-NG: microsoft.public.access.conversion
| > |
| > | My environment is DB2 database v8.2 on AIX as server, Windows XP with
| > MDAC2.8
| > | and Access 2002 (Office XP w/SP3) as client.
| > |
| > | I have a table in the database whose key is [ field1(decimal,16,0) +
| > | field2(char16) + field3(timestamp) ]
| > |
| > | In DB2, timestamps go to six places to the right of the nearest
second
| > (e.g.
| > | 2004-06-02-13.07.53.123456)
| > |
| > | Evidently the degree of precision is messing up Access' ability to
| > display
| > | recordsets from rows in this table if the fractional seconds portion
| > contains
| > | a number other than Zero in the fourth, fifth, and sixth position.
| > That's
| > | the ten-thousandths, hundred-thousandths, and millisecond positions
if
| > I'm
| > | not mistaken. For instance, the above example would fail to be
| > displayed,
| > | but (e.g. 2004-06-02-13.07.53.123000) will be displayed OK.
| > |
| > | Ill-affected rows display as #Deleted in all columns if using
Data***
| > | view, and the resultset of a query cannot be manipulated either as in
an
| > | Update or Delete.
| > |
| > | Is there no way to solve this issue? Would upgrading to Access 2003
| > help?
| > | I don't have this problem in code using ADO, but of course the task I
| > have
| > | before me requires I build some temporary tables in Access to update
the
| > data
| > | in DB2 with. I shouldn't be spending this much time doing something
so
| > | simple.
| > |
| > | About the only other alternative I can see is to create a temporary
table
| > in
| > | DB2 (requires much red tape) and upload my data from Access into it.
| > Then I
| > | would have to use some other ODBC tool to run the SQL bypassing
Access
| > | entirely. That doesn't make our ROI look very favorable. Any other
| > | suggestions would be very much appreciated.
| > |
| > | TIA,
| > | ~mr.bill
| > |
| >
| >
|

.

Quantcast