Bug #23428 Problem in adding and configuring a File DSN
Submitted: 18 Oct 2006 15:21 Modified: 13 Sep 2012 8:06
Reporter: Andy Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.12 OS:Microsoft Windows (Windows XP)
Assigned to: CPU Architecture:Any

[18 Oct 2006 15:21] Andy
Description:
I recently upgraded my OBDC driver from (I think) 3.51.06 to 3.51.12.

The problem is in trying to create a new File DSN in ODBC setup.

I think this may be related to problems I've encountered with setting up a database connection.  This code works fine with Access and SQL server:

    SQLHENV hEnv = SetupSiteDbApp::GetApp()->GetOdbcSession().GetEnvHandle();

    //
    // Create a connection
    //
    SQLHDBC hConn = NULL;

    SQLRETURN nStatus = SQLAllocHandle( SQL_HANDLE_DBC, hEnv, &hConn );

    if ( !SQL_SUCCEEDED( nStatus ) )
    {
        AfxMessageBox( IDS_ERROR_CREATEDBCONN, MB_ICONEXCLAMATION );
        return false;
    }

    //
    // Create the DSN with user prompting
    //
    strFile = AUDIT_LOG_DSN_FILE;
    
    std::wstring strDsnPath = FileUtil::JoinPath(
                                    m_pSetupInfo->m_strLocation,
                                    strFile
                                    );

    std::wstring strConn = StringPrintf(
                                L"DRIVER={%s}; SAVEFILE=%s",
                                m_pSetupInfo->m_strOdbcDriver.c_str(),
                                strDsnPath.c_str()
                                );

    SQLWCHAR szConnOut[ MAX_CONNECT_STRING ];
    SQLSMALLINT nCount;

    nStatus = SQLDriverConnect(
                    hConn,
                    m_hWnd,
                    (SQLWCHAR *) strConn.c_str(),
                    (SQLSMALLINT) strConn.length(),
                    szConnOut,
                    sizeof( szConnOut ) / sizeof( szConnOut[ 0 ] ),
                    &nCount,
                    SQL_DRIVER_PROMPT
                    );    

    if ( !SQL_SUCCEEDED( nStatus ) )
    {     
        /* while ( SQLError( hEnv, hConn, hHstmt, sqlstate, &sqlcode, buffer,
            SQL_MAX_MESSAGE_LENGTH + 1, &length) == SQL_SUCCESS )
        {
            printf("\n **** ERROR *****\n");
            printf("         SQLSTATE: %s\n", sqlstate);
            printf("Native Error Code: %ld\n", sqlcode);
            printf("%s \n", buffer);          
        }; */

        AfxMessageBox( IDS_ERROR_CREATEDBCONN, MB_ICONEXCLAMATION );
        SQLFreeHandle( SQL_HANDLE_DBC, hConn );
        return false;
    }

The third parameter passed to SQLDriverConnect() is "DRIVER={MySQL ODBC 3.51 Driver}; SAVEFILE=C:\IndigoAudit\audit.dsn"

Error info captured:

error code: HY000
error string: "[MySQL][OBDC 3.51 Driver]Could not find driver {MySQL ODBC 3.51 Driver} in system information."

It's saying it can't find the driver but I know it is because it's listed as one of the ODBC drivers...  

Hope this information is understandable.  Please ask if you need any other information or clarification.

Andy

How to repeat:
To reproduce:

1. Install latest ODBC driver: MySql ODBC 3.51.12
2. In Administrative Tools -> Data Sources (ODBC) select the 
   File DSN tab.
3. Select Add to create a new data source, select the MySql ODBC 3.51.12 Driver,  click Next, give it a name (eg audit) and Finish.
4. Now click the Configure button for this data source just created.  The  
   error message was "General Error: Invalid file dsn 'C:\program Files\Common            Files\ODBC\Data Sources\audit.dsn'"
[19 Oct 2006 8:25] Tonci Grgin
Hi Andy and thanks for your problem report.
What happens if you "Set directory" prior to creating FileDSN? 
By default, "Verify connection" is chacked, did you provide full and correct connection string in "Advanced" window? Is MySQL server you are referring to running? If not, uncheck "Verify connection" and try again.
[19 Oct 2006 12:51] Andy
Hi Tonci,

I've tried what you suggested below but without success.

By "Full and correct connection string in "Advanced" window",
,for me this was set to: DRIVER={MySQL ODBC 3.51 Driver}

I think I managed to get somewhere using a previous odbc driver
( 3.51.06 ) but I dont know for certain. I seem no longer able to
re-install older driver versions, once 3.51.12 has been installed,
they keep showing up in the list as 3.51.12...

Best regards
Andy
[19 Oct 2006 16:15] Tonci Grgin
Hi Andy,
>By "Full and correct connection string in "Advanced" window", for me this was set to: DRIVER={MySQL ODBC 3.51 Driver}
If so, did you uncheck "Verify connection" check box?

>I think I managed to get somewhere using a previous odbc driver ( 3.51.06 ) but I dont know for certain. I seem no longer able to re-install older driver versions, once 3.51.12 has been installed, they keep showing up in the list as 3.51.12...
It doesn't matter what is written in DM, just check file versions for winsys32\myodbc3*.* files. If myodbc3.dll says 3.51.06, than it's 3.51.06

How big is your driver (in bytes)? It should be about 1.4-1.5 MB (can't remember exact size right now).
[20 Oct 2006 9:21] Andy
Hi Tonci,
Actually, when I uncheck "Verify connection" check box
the file dsn appears to get added ok.  Then try to Configure
I get "General Error: Invalid file dsn 'C:\audit\audit.dsn'

Other information: I can link to an MySQL ODBC table in Access fine using 
Get External Data -> link tables -> ODBC databases -> Machine data source.
This seems ok, its just if I try the (rarely used) File dsn source I get problems.

The driver file size is approx. 1.5 MB myodbc3.dll dated 15/10/2005.
Regards
Andy
[22 Oct 2006 12:24] Tonci Grgin
Andy, since I can't repeat it, we should try to narrow down the problem. 
1) Please make sure File DSN is put into place windows search for it by default. Make sure your windows account has privileges to read/write in that place (C:\Program Files\Common Files\ODBC...).
2) Create paswordless root account for MySQL server (remember to remove it later)
and try again. Please inform me of your findings.
[23 Nov 2006 0:01] Bugs System
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".