DB2 UDB and Microsoft Access
Rich Smrcina
I am using DB2 UDB V5, the server is on Windows NT Server and the client is
on Windows NT Workstation. Both have the latest CSD applied (9024).
When accessing a table that contains a date or time value as a primary key,
all of the cells in the Microsoft Access result set appear as '#Deleted'.
Is there anyone else running in to this problem and/or what is the
solution?
Rich Smrcina
Grede Foundries
rsmr...@grede.com
p...@acm.org
I found that MSAccess can't handle DB2 date/time as a primary key. The DB2
Timestamp data type causes same problem. I had to create a text data type
column and build my own date/time key in order to get it to work. If you just
need a unique key, there is a new function, Generate_Unique, that will give
you a character format timestamp with the partition id attached which is
suppose to be unique. Pop In article <6i71u1$a...@newsops.execpc.com>, "Rich
Smrcina" <rsmr...@execpc.com> wrote:
-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/ Now offering spam-free web-based newsreading
David Marvin
Please read the Db2 CLI Programmers Guide and search for PATCH1 or
PATCH2, etc. This should describe what patches are available and how to
activate them.
Rich Smrcina wrote:
> I am using DB2 UDB V5, the server is on Windows NT Server and the client is
> on Windows NT Workstation. Both have the latest CSD applied (9024).
>
> When accessing a table that contains a date or time value as a primary key,
> all of the cells in the Microsoft Access result set appear as '#Deleted'.
>
> Is there anyone else running in to this problem and/or what is the
> solution?
>
> Rich Smrcina
> Grede Foundries
> rsmr...@grede.com
--
David Marvin
DRDA & DUOW Development
DB2 Universal Database & DB2 Connect
IBM Toronto Laboratory
DB2 product information at http://www.software.ibm.com/data/db2/
DB2 product support at http://www.software.ibm.com/data/db2/library/
DB2 produc publications at http://www.software.ibm.com/data/db2/library/ and
follow link
DB2 performance reports at http://www.software.ibm.com/data/db2/performance/
Shawn Espenlaub
Rich,
We are also using the same configuration and running into exactly the same
problem. Unfortunately we haven't found a solution either!
What we have noticed is that ALL of our tables display the same behavior,
regardless of whether a date and/or time form part of the key - even the
simplest of tables which only contain a couple of CHAR's gets the same
problem.
I've had a look at PATCH1, can't find much on PATCH2 - but nothing seems to
work.
Some other behaviour we've noticed is:
1. It appears as though the "#deleted" only appears if there is already one
or more rows in the table. If the table is empty it appears in MS Access as
though it is empty, adding a record seems to work OK (ie. the "insert"
works) but reading it back out again (in order to refresh the record
contents on the screen) results in a garbled record being shown (but the
data makes it to the database OK). Once you close the table and open it up
again to view it you will get "#deleted".
2. You can try viewing a table three times and then you will get an error
reporting that you have been disconnected - in which case you must exit MS
Access and re-enter.
Regards,
Shawn Espenlaub
Beacon Technology Pty Ltd
sh...@beacon.com.au
Rich Smrcina wrote in message <6i71u1$a...@newsops.execpc.com>...
Richard Cotterill
HI,
i had trouble with dates using DELPHI and HOLOS - it turned out that
when binding the package you need to leave the date in ISO format - we
normally use EUR.
probably not your problem, but who knows.
Richard
Richard Cotterill
rcot...@pcug.org.au
David Marvin
2.2.10.4 CLI/ODBC Settings Notebook GUI
The following CLI/ODBC configuration keywords cannot be set using the
CLI/ODBC Settings Notebook. The db2cli.ini file must be modified directly to
make use of these keywords.
o CURRENTPACKAGESET
o GATEWAYVERSION
o PATCH2
The documentation describes these keywords. See either the Installing and
Configuring DB2 Clients manual, or the CLI Guide and Reference.
For information about the latest settings for the PATCH1 and PATCH2
configuration keywords, see the Installing and Configuring DB2 Clients
section in these Release Notes.
2.2.13.3 CLI/ODBC Settings Notebook GUI
The following CLI/ODBC configuration keywords cannot be set using the
CLI/ODBC Settings Notebook. The db2cli.ini file must be modified directly to
make use of these keywords.
o CURRENTPACKAGESET
o GATEWAYVERSION
o PATCH2
The documentation describes these keywords. See either the Installing and
Configuring DB2 Clients manual, or the CLI Guide and Reference.
The DB2 CLI/ODBC driver default behavior can be modified by specifying values
for both the PATCH1 and PATCH2 keyword through either the db2cli.ini file or
through the SQLDriverConnect() or SQLBrowseConnect() CLI API.
The PATCH1 keyword is specified by adding together all keywords that the user
wants to set. For example, if patch 1, 2, and 8 were specified, then PATCH1
would have a value of 11. Following is a description of each keyword value
and its effect on the driver:
1 - This makes the driver search for "count(exp)" and replace it with
"count(distinct exp)". This is needed because some versions of
DB2
support the "count(exp)" syntax, and that syntax is generated by
some
ODBC applications. Needed by Microsoft applications when the
server
does not support the "count(exp)" syntax.
2 - Some ODBC applications are trapped when SQL_NULL_DATA is returned
in
the SQLGetTypeInfo() function for either the LITERAL_PREFIX or
LITERAL_SUFFIX
column. This forces the driver to return an empty string instead.
Needed
by Impromptu 2.0.
4 - This forces the driver to treat the input time stamp data as date
data
if the time and the fraction part of the time stamp are zero.
Needed by
Microsoft Access.
8 - This forces the driver to treat the input time stamp data as time
data
if the date part of the time stamp is 1899-12-30. Needed by
Microsoft Access.
16 - Not used.
32 - This forces the driver to not return information about
SQL_LONGVARCHAR,
SQL_LONGVARBINARY, and SQL_LONGVARGRAPHIC columns. To the
application
it appears as though long fields are not supported. Needed by
Lotus 123.
64 - This forces the driver to NULL terminate graphic output strings.
Needed
by Microsoft Access in a double byte environment.
128 - This forces the driver to let the query "SELECT Config, nValue
FROM MSysConf"
go to the server. Currently the driver returns an error with
associated
SQLSTATE value of S0002 (table not found). Needed if the user has
created
this configuration table in the database and wants the
application to access it.
256 - This forces the driver to return the primary key columns first in
the SQLStatistics() call. Currently, the driver returns the
indexes sorted
by index name, which is standard ODBC behavior.
512 - This forces the driver to return FALSE in SQLGetFunctions() for
both
SQL_API_SQLTABLEPRIVILEGES and SQL_API_SQLCOLUMNPRIVILEGES.
1024 - This forces the driver to return SQL_SUCCESS instead of
SQL_NO_DATA_FOUND
in SQLExecute() or SQLExecDirect() if the executed UPDATE or
DELETE statement
affects no rows. Needed by Visual Basic applications.
2048 - Not used.
4096 - This forces the driver to not issue a COMMIT after closing a
cursor
when in autocommit mode.
8192 - This forces the driver to return an extra result set after
invoking
a stored procedure. This result set is a one row result set
consisting
of the output values of the stored procedure. Can be accessed by
Powerbuild applications.
32768 - This forces the driver to make Microsoft Query applications work
with DB2 MVS synonyms.
65536 - This forces the driver to manually insert a "G" in front of
character
literals which are in fact graphic literals. This patch should
always
be supplied when working in an double byte environment.
131072 - This forces the driver to describe a time stamp column as a
CHAR(26)
column instead, when it is part of an unique index. Needed by
Microsoft applications.
262144 - This forces the driver to use the pseudo-catalog table
db2cli.procedures instead of the SYSCAT.PROCEDURES and
SYSCAT.PROCPARMS tables.
524288 - This forces the driver to use SYSTEM_TABLE_SCHEMA instead of
TABLE_SCHEMA
when doing a system table query to a DB2/400 Version 3.x system.
This
results in better performance.
1048576 - This forces the driver to treat a zero length string through
SQLPutData() as SQL_NULL_DATA.
The PATCH2 keyword differs from the PATCH1 keyword. In this case, multiple
patches are specified using comma separators. For example, if patch 1, 4, and
5 were specified, then PATCH2 would have a value of "1,4,5". Following is a
description of each keyword value and its effect on the driver:
1 - This forces the driver to convert the name of the stored procedure
in a CALL statement to uppercase.
2 - Not used.
3 - This forces the driver to convert all arguments to schema calls to
uppercase.
4 - This forces the driver to return the Version 2.1.2 like result set for
schema calls (that is, SQLColumns(), SQLProcedureColumns(), and so on),
instead of the
Version 5 like result set.
5 - This forces the driver to not optimize the processing of input VARCHAR
columns, where the pointer to the data and the pointer to the length
are consecutive in memory.
6 - This forces the driver to return a message that scrollable cursors are
not supported.
This is needed by Visual Basic programs if the DB2 client is Version 5
and the server is DB2 UDB Version 5.
7 - This forces the driver to map all GRAPHIC column data types to the CHAR
column data type. This is needed in a double byte environment.
8 - This forces the driver to ignore catalog search arguments in schema
calls.
Rich Smrcina wrote:
> I am using DB2 UDB V5, the server is on Windows NT Server and the client is
> on Windows NT Workstation. Both have the latest CSD applied (9024).
>
> When accessing a table that contains a date or time value as a primary key,
> all of the cells in the Microsoft Access result set appear as '#Deleted'.
>
> Is there anyone else running in to this problem and/or what is the
> solution?
>
> Rich Smrcina
> Grede Foundries
> rsmr...@grede.com
--
David Marvin
David Marvin wrote:
> ------------------------------------------------------------------------
>
> David Marvin <mar...@ca.ibm.com>
> DUOW & DRDA Development DB2 UDB
> IBM Toronto Laboratory
>
> David Marvin
> DUOW & DRDA Development DB2 UDB <mar...@ca.ibm.com>
> IBM Toronto Laboratory HTML Mail
> Netscape Conference Address
> Netscape Conference DLS Server
> Additional Information:
> Last Name Marvin
> First Name David
> Version 2.1
Rich Smrcina
David,
Thanks for your email and newsgroup reply.
I set PATCH1 = 12, restarted Microsoft Access and all rows and columns in
the result set still appear as #Deleted.
Here is the table definition, feel free to try it.
create table grede00.test_t \
(Test_Date Date NOT NULL, \
Test_Time Time NOT NULL, \
Test_Stuff Char(20), \
Primary Key(Test_Date, Test_Time))
Rich Smrcina
Grede Foundries
rsmr...@grede.com
David Marvin <mar...@ca.ibm.com> wrote in article
<3548E956...@ca.ibm.com>...
> Ooops...try PATCH1=4+8=21 for starters
>
Shawn Espenlaub
Hi again Rich,
We got around our problems - we did not read the installation instructions
for patch #2 (WR9014) and therefore did not install the client portion - we
had only installed the server portion. We installed the CAE patch version
on each client and it worked like a dream. It also got rid of another
problem regarding the Control Centre "looping/locking" when used to remotely
administer databases from the client.
Regards,
Shawn Espenlaub
Beacon Technology Pty Ltd
sh...@beacon.com.au
Rich Smrcina wrote in message <6iccga$7...@newsops.execpc.com>...
>David,
>
>Thanks for your email and newsgroup reply.
>
>I set PATCH1 = 12, restarted Microsoft Access and all rows and columns in
>the result set still appear as #Deleted.
>
>Here is the table definition, feel free to try it.
>
>create table grede00.test_t \
> (Test_Date Date NOT NULL, \
> Test_Time Time NOT NULL, \
> Test_Stuff Char(20), \
> Primary Key(Test_Date, Test_Time))
>
>Rich Smrcina
>Grede Foundries
>rsmr...@grede.com
>