MySQL Bugs: #40398: CONNECT TEMPORARY TABLE VIA MICROSOFT ACCESS FAILS IN 5.1.5

Bug #40398	CONNECT TEMPORARY TABLE VIA MICROSOFT ACCESS FAILS IN 5.1.5
Submitted:	29 Oct 2008 16:50	Modified:	30 May 2013 6:32
Reporter:	OMT PETRINI	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.5	OS:	Windows (ANY)
Assigned to:	Lawrenty Novitsky	CPU Architecture:	Any
Tags:	ODBC CONNECTOR FAILS CONNECT TEMPORARY TABLE

Description:
Impossible to link a temporary table from msaccess 97

How to repeat:
the sequence to repeat the problem is.
Create a table on mysql server (any version); no problem myisam or innodb
Connect this table in microsoft access97 (all is ok)
drop the table on server
Via a query pass trough create the same table BUT TEMPORARY !
Now if you use 3.51.x connector you are able to select the temporary table; instead if you have 5.1.5 version this table is not visible from access.
But please note:
- if the link connection to the table was created time ago when you have still the 3.51  version, and now you have deleted the 3,51 version, and installed 5.1.5 version connector, you can still use the temporary table!

The problem is about the string connection stored in msaccess, returned from connector where:
in 3.51. the string was like this
ODBC;DATABASE=omtTMP;DESCRIPTION=MySQL ODBC 3.51 Driver DSN;DSN=MYSQL-OMTTMP;OPTION=3;PORT=3306;SERVER=mastertmp;;TABLE=wrk_invsl

instead in 5.1.5 the string is like this:

ODBC;DSN=mysql-omttmp;;TABLE=wrk_invsl

I this was the different method to obtain from mysql the table associated with name, where in 3.51.x returned also the temprary table, instead in 5.1.5  not !

Thanks in advance

Hi Massimo and thanks for your report.

I am unable to repeat it with Access2003. At the very start, 3.51 part already fails and I'm unable to open linked temporary table. So my guess would be it's something Access specific.
Also, I do not have Access97 so it's out of question to test on it. Can you test with Access2003?

3.51 driver trace from Access2k3

Attachment: SQL.7z (application/octet-stream, text), 1.37 KiB.

Requirements to test  temporary table problems
install mysql odbc 3.x

install mysql odbc 5.x

create a database name omttmp in your mysql server

define in the file hosts (located in %windir%\system32\drivers\etc  a server named masterdati linked to your mysqlserver address

configure on odbc 3.51 with name mysql-omttmp, server masterdati, your login information, and connect to database omttmp 

configure on odbc 5.1 with name mysql-omttmp_v5, server masterdati, your login information, and connect to database omttmp 

open my test2k3.mdb

run the macro TEST_TMP

Thank you to force me to analyze better the question !

So I have found the problem (mine and not in odbc!!!)

Please look in the module “TmpTbl_Utilities”; you can found the instruction to make a connect to a table with the appropriate odbc; first use the string related to 3.5X version; rename the line to the table “wrk_invsl_v35x” in  “wrk_invsl”; so run the macro TEST_TMP; you can see the 767 record inserted.
Now second test; change ONLY the name of the linked table “wrk_invsl_v515” in  “wrk_invsl”, but see inside (as property of the table) which is the string connection;
Now run the macro; you can see the error !

The solution is the string  the string connection different in the version 3.5 and 5.1.!

Now for me the problem is solved, but it is interesting to known why then syntax to connect a table is different  ; 
in v3 mode is :
ODBC;DATABASE=omttmp;DESCRIPTION=MySQL ODBC 3.51 Driver DSN;DSN=MYSQL-OMTTMP;OPTION=3;PORT=3306;SERVER=masterdati;;TABLE="

while in v5 mode is:
"ODBC;DSN=mysql-omttmp_v5;;TABLE=wrk_invsl"

for the same table !
And this question is bad to standardize the code, because the odbc is installed in the client (not all with the same version of odbc), while the mdb code and the link to the tabae are inside mdb code is only in a server !
It possible to make a unique mode of connection ? I understand that the v5 version is better, but to use it nedd to convert in the same moment all the pc client and all the mdb !

Regards

Requirements to test  temporary table problems
install mysql odbc 3.x

install mysql odbc 5.x

create a database name omttmp in your mysql server

define in the file hosts (located in %windir%\system32\drivers\etc  a server named masterdati linked to your mysqlserver address

configure on odbc 3.51 with name mysql-omttmp, server masterdati, your login information, and connect to database omttmp 

configure on odbc 5.1 with name mysql-omttmp_v5, server masterdati, your login information, and connect to database omttmp 

open my test2k3.mdb

run the macro TEST_TMP

Thank you to force me to analyze better the question !

So I have found the problem (mine and not in odbc!!!)

Please look in the module “TmpTbl_Utilities”; you can found the instruction to make a connect to a table with the appropriate odbc; first use the string related to 3.5X version; rename the line to the table “wrk_invsl_v35x” in  “wrk_invsl”; so run the macro TEST_TMP; you can see the 767 record inserted.
Now second test; change ONLY the name of the linked table “wrk_invsl_v515” in  “wrk_invsl”, but see inside (as property of the table) which is the string connection;
Now run the macro; you can see the error !

The solution is the string  the string connection different in the version 3.5 and 5.1.!

Now for me the problem is solved, but it is interesting to known why then syntax to connect a table is different  ; 
in v3 mode is :
ODBC;DATABASE=omttmp;DESCRIPTION=MySQL ODBC 3.51 Driver DSN;DSN=MYSQL-OMTTMP;OPTION=3;PORT=3306;SERVER=masterdati;;TABLE="

while in v5 mode is:
"ODBC;DSN=mysql-omttmp_v5;;TABLE=wrk_invsl"

for the same table !
And this question is bad to standardize the code, because the odbc is installed in the client (not all with the same version of odbc), while the mdb code and the link to the tabae are inside mdb code is only in a server !
It possible to make a unique mode of connection ? I understand that the v5 version is better, but to use it nedd to convert in the same moment all the pc client and all the mdb !

Regards

Sorry Massimo I do not understand you...

If problem is CONCATenation, then it is in that functions in MySQL set the BINARY flag. This is well discussed all over manual and BugsDB. To avoid this, please check "Always handle binary function results as character data".

If the problem is who's better for you, 5.1 is much stricter (in terms of ODBC compliance) and has UTF-8 support. Enforced ODBC compliance can actually make problems with MS own ODBC clients so you'll have to test and see.

Is there anything else here that will make me keep this report open?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.