MySQL Forums
Forum List  »  Connector/ODBC

Re: Microsoft Access with ODBC-Connector 8
Posted by: Bogdan Degtyariov
Date: July 19, 2023 03:10AM

After a thorough investigation if was determined that MS Access is not
interpreting the data fetched data correctly.
The same behavior was observed with MariaDB and Microsoft SQL Server linked
tables in MS Access.

The data fetch process does not produce any errors, but it shows #Deleted in
the rows.
However, if click "Refresh all" button on the toolbar the data is shown, but
only for the first row.
Changing the sorting or filtering data allows for seeing every row correctly,
but other rows are marked as #Deleted.

The ODBC Trace shows the data being fetched correctly and the data itself is
correct.

Here is the test, which clearly demonstrates how MS Access uses the same
buffer result to produce correct and incorrect strings.

NOTE: The steps below use Japanese characters, but they can be other
characters (such as ä, Ä, ö, Ö, ü, Ü, ß) that take more than 1
byte to encode in UTF8MB4.

1. CREATE TABLE t_c_default(ida varchar(45) PRIMARY KEY, col1 int)
CHARACTER SET utf8mb4;
2. INSERT INTO t_c_default VALUES('ほげ', 1);
3. Open MS Access, create a linked table, read table, observe #Deleted
instead of the acctual data.
4. Click "Refresh all" button. The #Deleted content disappears,
the correct data appears.
5. Try updating the value for `ida` column by adding the character
'A' at the end.
6. The record update fails with the error "This record has been
changed by another user..."
7. At the update stage MS Access generates two Prepare/Execute statements
as can be seen in the server query log:
2023-07-13T12:27:56.259731Z 48 Prepare SELECT `ida`,`col1` FROM
`t_c_default` WHERE `ida` = ?
2023-07-13T12:27:56.259916Z 48 Execute SELECT `ida`,`col1` FROM
`t_c_default` WHERE `ida` = 'ほげ'
2023-07-13T12:27:57.157683Z 48 Query SET AUTOCOMMIT=0
2023-07-13T12:27:57.158028Z 48 Query SHOW SESSION VARIABLES LIKE
'MAX_EXECUTION_TIME'
2023-07-13T12:27:57.159449Z 48 Prepare UPDATE `t_c_default` SET
`ida`=? WHERE `ida` = ? AND `col1` = ?
2023-07-13T12:27:57.159601Z 48 Execute UPDATE `t_c_default` SET
`ida`='ほげA' WHERE `ida` = '㼿' AND `col1` = '1'

SELECT runs when I start editing the table cell.
UPDATE runs when I save the updated information. As can be seen in the
timing, there is less than 1s apart between two queries, so they
could not be from other query sequence (such as refresh in step 4).
NOTE: "Refresh all" produces a similar SELECT query as well, but we are
interested in the update process.
We can see that SELECT contains the correct parameter value inside
WHERE clause. The ODBC driver returns the result to MS Access, which
composes a new value for SET by adding 'A' to the string. The resulting
value is correct as well (... SET `ida`='ほげA' ...). However, out of the
same data it creates WHERE `ida` = '㼿' with garbage value.

This has absolutely no sense. The first SELECT and even SET query inside
the same UPDATE query contain the correct data, but WHERE has garbage.

In order to understand how the garbage output was produced we did a similar
experiment where the PK value was a latin string "AB".

When updating it to "ABC" we observed how the client (MS Access)
bound parameters. Here is a fragment from ODBC trace:

MSACCESS 304c-89c ENTER SQLBindParameter
HSTMT 0x000001A05C6F97E0
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD -8 <SQL_C_WCHAR>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 16
SWORD 0
PTR 0x000001A079BD35F0
SQLLEN 0
SQLLEN * 0x000001A079BD35E8

MSACCESS 304c-89c EXIT SQLBindParameter with return code 0
(SQL_SUCCESS)
HSTMT 0x000001A05C6F97E0
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD -8 <SQL_C_WCHAR>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 16
SWORD 0
PTR 0x000001A079BD35F0
SQLLEN 0
SQLLEN * 0x000001A079BD35E8 (6)

MSACCESS 304c-89c ENTER SQLBindParameter
HSTMT 0x000001A05C6F97E0
UWORD 2
SWORD 1 <SQL_PARAM_INPUT>
SWORD 99 <SQL_C_DEFAULT>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 16
SWORD 0
PTR 0x0000001D975E3E38
SQLLEN 0
SQLLEN * 0x0000001D975E3E30

MSACCESS 304c-89c EXIT SQLBindParameter with return code 0
(SQL_SUCCESS)
HSTMT 0x000001A05C6F97E0
UWORD 2
SWORD 1 <SQL_PARAM_INPUT>
SWORD 99 <SQL_C_DEFAULT>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 16
SWORD 0
PTR 0x0000001D975E3E38
SQLLEN 0
SQLLEN * 0x0000001D975E3E30 (2)

MSACCESS 304c-89c ENTER SQLBindParameter
HSTMT 0x000001A05C6F97E0
UWORD 3
SWORD 1 <SQL_PARAM_INPUT>
SWORD 4 <SQL_C_LONG>
SWORD 4 <SQL_INTEGER>
SQLULEN 10
SWORD 0
PTR 0x000001A079BD3600
SQLLEN 0
SQLLEN * 0x000001A079BD35F8

MSACCESS 304c-89c EXIT SQLBindParameter with return code 0
(SQL_SUCCESS)
HSTMT 0x000001A05C6F97E0
UWORD 3
SWORD 1 <SQL_PARAM_INPUT>
SWORD 4 <SQL_C_LONG>
SWORD 4 <SQL_INTEGER>
SQLULEN 10
SWORD 0
PTR 0x000001A079BD3600
SQLLEN 0
SQLLEN * 0x000001A079BD35F8 (4)

MSACCESS 304c-89c ENTER SQLExecDirectW
HSTMT 0x000001A05C6F97E0
WCHAR * 0x000001A079BD2DE8 [ -3] "UPDATE `t_c_default` SET
`ida`=? WHERE `ida` = ? AND `col1` = ?\ 0"
SDWORD -3

MSACCESS 304c-89c EXIT SQLExecDirectW with return code 0
(SQL_SUCCESS)
HSTMT 0x000001A05C6F97E0
WCHAR * 0x000001A079BD2DE8 [ -3] "UPDATE `t_c_default` SET
`ida`=? WHERE `ida` = ? AND `col1` = ?\ 0"
SDWORD -3


The query has three parameters, but we want to check the first two.
Parameter #1 binding it does binding of SQL_C_WCHAR value type
to SQL_WVARCHAR SQL type:

MSACCESS 304c-89c ENTER SQLBindParameter
HSTMT 0x000001A05C6F97E0
UWORD 1
SWORD 1 <SQL_PARAM_INPUT>
SWORD -8 <SQL_C_WCHAR>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 16
SWORD 0
PTR 0x000001A079BD35F0
SQLLEN 0
SQLLEN * 0x000001A079BD35E8

Parameter #2:

MSACCESS 304c-89c ENTER SQLBindParameter
HSTMT 0x000001A05C6F97E0
UWORD 2
SWORD 1 <SQL_PARAM_INPUT>
SWORD 99 <SQL_C_DEFAULT>
SWORD -9 <SQL_WVARCHAR>
SQLULEN 16
SWORD 0
PTR 0x0000001D975E3E38
SQLLEN 0
SQLLEN * 0x0000001D975E3E30

it is binding SQL_C_DEFAULT parameter, which Unicode drivers map to
SQL_C_WCHAR. However, the data inside the buffer is not Unicode, but
ANSI, which corresponds to SQL_C_CHAR.

The problems with such approach are:

1. Application will only be able to use ANSI characters because Unicode
(including Japanese 'ほげ') cannot be represented.
2. SQL_C_DEFAULT is not recommended for using because the default types
can be different for ODBC Driver and client:

https://learn.microsoft.com/en-us/sql/odbc/reference/appendixes/default-c-data
-types?view=sql-server-ver16

It looks like there is a bug in MS Access and we cannot do anything on
the driver side to fix it. The fact that MS SQL ODBC Driver and
MS SQL Server linked tables have exactly the same problem is another
proof of the bug inside MS Access.

There are several workarounds:

1. Add a unique INT column to the table and make it PRIMARY KEY.
This requires modification of the tables, but allows viewing and editing
data rows.
2. Create a VIEW with automatically generated INT column (ROW_NUMBER()):

CREATE VIEW `v_c_default` AS select `t_c_default`.`ida` AS
`ida`,`t_c_default`.`col1` AS `col1`, row_number() OVER `w` AS `rnum`
FROM `t_c_default` WINDOW `w` AS (ORDER BY `t_c_default`.`ida` );
When linking the view in MS Access select the autogenerated column as
a PRIMARY KEY. All data will be visible. Unfortunately, such views are
not updatable.

At this point we cannot do anything to make it working because the bug is
inside MS Access.

Options: ReplyQuote


Subject
Written By
Posted
Re: Microsoft Access with ODBC-Connector 8
July 19, 2023 03:10AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.