MySQL Bugs: #13230: MyODBC 3.51.11 don't work with the tool CognosScript

Bug #13230	MyODBC 3.51.11 don't work with the tool CognosScript
Submitted:	15 Sep 2005 16:04	Modified:	6 Oct 2005 11:44
Reporter:	Annabelle GERARD	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	Mysql 3.23.49	OS:	Windows (Windows 2000)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
Hi

I developped Cognos reports with Cognos Impromptu 7.1 on a Mysql 3.23.49 database. These reports are used since an interface named EasyBlue version 2.0. All was ok since i use the new ODBC driver 3.51.11 which give me this error : "[MySQL][ODBC 3.51 Driver]Invalid window handle for connection completion argument." The older versions are ok but others applications need the 3.51.11 version so it have to work with the new version !!

Thanks.

How to repeat:
Use the 3.51.11 odbc driver and create this Cognosscript base on a mysql database version 3.23.49.

Macro :
Dim ImpAppObject as object

  Set ImpAppObject = CreateObject("Impromptu.Application")
  ImpAppObject.OpenCatalog "c:\cognos\exemples\vacances.cat", "Auteur"

  ImpAppObject.Quit
  Set ImpAppObject = Nothing

Suggested fix:
This is the trace of the ODBC driver :

ImpAdmin        67c-5cc	ENTER SQLFreeEnv 
		HENV                02271250

ImpAdmin        67c-5cc	EXIT  SQLFreeEnv  with return code 0 (SQL_SUCCESS)
		HENV                02271250

ImpAdmin        61c-600	ENTER SQLAllocEnv 
		HENV *              00EBC4B4

ImpAdmin        61c-600	EXIT  SQLAllocEnv  with return code 0 (SQL_SUCCESS)
		HENV *              0x00EBC4B4 ( 0x022714f0)

ImpAdmin        61c-600	ENTER SQLAllocConnect 
		HENV                022714F0
		HDBC *              023947C8

ImpAdmin        61c-600	EXIT  SQLAllocConnect  with return code 0 (SQL_SUCCESS)
		HENV                022714F0
		HDBC *              0x023947C8 ( 0x02271598)

ImpAdmin        61c-600	ENTER SQLSetConnectOption 
		HDBC                02271598
		UWORD                      110 <SQL_ODBC_CURSORS>
		SQLLEN                     2

ImpAdmin        61c-600	EXIT  SQLSetConnectOption  with return code 0 (SQL_SUCCESS)
		HDBC                02271598
		UWORD                      110 <SQL_ODBC_CURSORS>
		SQLLEN                     2

ImpAdmin        61c-600	ENTER SQLDriverConnectW 
		HDBC                02271598
		HWND                00000000
		WCHAR *             0x1F7C4AA0 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x1F7C4AA0 
		SWORD                        8 
		SWORD *             0x00000000
		UWORD                        3 <SQL_DRIVER_COMPLETE_REQUIRED>

ImpAdmin        61c-600	EXIT  SQLDriverConnectW  with return code -1 (SQL_ERROR)
		HDBC                02271598
		HWND                00000000
		WCHAR *             0x1F7C4AA0 [      -3] "******\ 0"
		SWORD                       -3 
		WCHAR *             0x1F7C4AA0 
		SWORD                        8 
		SWORD *             0x00000000
		UWORD                        3 <SQL_DRIVER_COMPLETE_REQUIRED>

		DIAG [IM008] [MySQL][ODBC 3.51 Driver]Invalid window handle for connection completion argument. (0) 

ImpAdmin        61c-600	ENTER SQLErrorW 
		HENV                022714F0
		HDBC                02271598
		HSTMT               00000000
		WCHAR *             0x0012C834 (NYI) 
 		SDWORD *            0x0012C890
		WCHAR *             0x0012C434 
		SWORD                      511 
		SWORD *             0x0012C88C

ImpAdmin        61c-600	EXIT  SQLErrorW  with return code 0 (SQL_SUCCESS)
		HENV                022714F0
		HDBC                02271598
		HSTMT               00000000
		WCHAR *             0x0012C834 (NYI) 
 		SDWORD *            0x0012C890 (0)
		WCHAR *             0x0012C434 [      82] "[MySQL][ODBC 3.51 Driver]Invalid window h"
		SWORD                      511 
		SWORD *             0x0012C88C (82)

ImpAdmin        61c-600	ENTER SQLErrorW 
		HENV                022714F0
		HDBC                02271598
		HSTMT               00000000
		WCHAR *             0x0012C5D0 (NYI) 
 		SDWORD *            0x0012C62C
		WCHAR *             0x0012C1D0 
		SWORD                      511 
		SWORD *             0x0012C628

ImpAdmin        61c-600	EXIT  SQLErrorW  with return code 100 (SQL_NO_DATA_FOUND)
		HENV                022714F0
		HDBC                02271598
		HSTMT               00000000
		WCHAR *             0x0012C5D0 (NYI) 
 		SDWORD *            0x0012C62C
		WCHAR *             0x0012C1D0 
		SWORD                      511 
		SWORD *             0x0012C628

ImpAdmin        61c-600	ENTER SQLFreeConnect 
		HDBC                02271598

ImpAdmin        61c-600	EXIT  SQLFreeConnect  with return code 0 (SQL_SUCCESS)
		HDBC                02271598

112-EasyBlue    2f4-2d4	ENTER SQLFreeEnv 
		HENV                00000000

112-EasyBlue    2f4-2d4	EXIT  SQLFreeEnv  with return code -2 (SQL_INVALID_HANDLE)
		HENV                00000000

ImpAdmin        61c-600	ENTER SQLFreeEnv 
		HENV                022714F0

ImpAdmin        61c-600	EXIT  SQLFreeEnv  with return code 0 (SQL_SUCCESS)
		HENV                022714F0

Are you tried the last MyODBC release 3.51.11-2?

Yes, I try with 3.51.11-2 ODBC version and it doesn't work.

Sorry but I was unable for to find at least a trial version of CognosScript
for to test on our side. Could you please enter in contact with their support
for analyze on that incompatibility you are experiencing.

Thanks in advance.

Ok we wait a response from Cognos but the problem seems to come from the function SQLDriverConnect in the ODBC driver. The liste of changes in the 3.51.10a explains the SQLDriverConnect was heavily reworked to more fully support prompting. CognosScript is very near Visual Basic. Can this change explain my problem ?

Is there some way to edit the connection string for Cognos script?
Seems the problem is in FLAG_NO_PROMPT option. To avoid the checking of a window handle it is needed to provide option=16 in the connection string or set "Don't prompt upon connect" option in MyODBC DSN configuration dialog.

I tried a CognosScript macro with this command :
Set objConnection = CreateObject("ADODB.Connection")
   
strConnectionString = "PROVIDER=MYSQLProv" _
                      + ";location=xxx.xx.xx.xxx" _
                      + ";DATA SOURCE=" + DBNAME _  
                      + ";USER ID=" + USER_ID _
                      + ";PASSWORD=" + PASSWORD + ";"
                                  
    objConnection.ConnectionString = strConnectionString
    objConnection.Open objConnection.ConnectionString
    Set oImpApp = CreateObject("CognosImpromptu.Application")
   oImpApp.Opencatalog CAT_PATH + "\CATALOGUE WATT     STATISTIQUES.CAT", "util", "util"

The database connection is ok but the same error appears during the opencatalog function.

The option "Don't prompt upon connect" option in MyODBC DSN
configuration dialog doesn't work more.

If you have a safe environment, you may try to grant user ODBC (default ODBC user) with no password and for all host.

grant all on *.* to `ODBC`@`%`;
flush privileges

If you need a secure environment, this solution is not good.  You will have to find where the default ODBC user is used and replace it by a other user and password.

I found the solution in my CognosScript macro :

the function OpenCatalog has a parameter which specifies if the database is connected or not. So I put 0 for not connected to database and then i use the function connectdatabase (User Database, Password Database) which is normally prescribed if we use an ODBC connection !

Thank you for the feedback, I am changing then for a not a bug.