Creating an Install with an ODBC Connection

[arrian]

I have a VB.NET program that requires a MySQL ODBC connection. How do I add this in to the installer and have it create the ODBC connection automatically? Is this even possible?

 

[SiJP]

If you know all the values before hand, then you can create a DSN entry on the fly.

You *may* have to modify this code as it was written in vb6 (and not by myself - I dont have an authors name, sorry).

(vb.net has classes for registry keys rather than kacking around with API's).

Perhaps you could create the DSN when the application first runs?

    Option Explicit

    Private Const REG_SZ = 1    'Constant for a string variable type.
    Private Const HKCU = &H80000001
    Private Declare Function RegCreateKey Lib "advapi32.dll" Alias "RegCreateKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
    Private Declare Function RegSetValueEx Lib "advapi32.dll" Alias "RegSetValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal Reserved As Long, ByVal dwType As Long, lpData As Any, ByVal cbData As Long) As Long
    Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long

Private Sub CreateDSN()
On Error GoTo err_Handle

   Dim DataSourceName As String
   Dim DatabaseName As String
   Dim Description As String
   Dim DriverPath As String
   Dim DriverName As String
   Dim LastUser As String
   Dim Regional As String
   Dim Server As String
   Dim TrustedConnection As String

   Dim lResult As Long
   Dim hKeyHandle As Long

   'Specify the DSN parameters.

   DataSourceName = "e.g. My DSN Connection to SERVER"
   DatabaseName = "MYDATA"
   Description = "Some connection blarg"
   DriverPath = "C:\WINNT\System32\sqlsrv32.dll"
   LastUser = "sa"
   Server = "MYSERVER"
   DriverName = "SQL Server"
   TrustedConnection = "Yes"

   'Create the new DSN key.
   lResult = RegCreateKey(HKCU, "SOFTWARE\ODBC\ODBC.INI\" & _
        DataSourceName, hKeyHandle)

   'Set the values of the new DSN key.
   lResult = RegSetValueEx(hKeyHandle, "Database", 0&, REG_SZ, _
      ByVal DatabaseName, Len(DatabaseName))
   lResult = RegSetValueEx(hKeyHandle, "Description", 0&, REG_SZ, _
      ByVal Description, Len(Description))
   lResult = RegSetValueEx(hKeyHandle, "Driver", 0&, REG_SZ, _
      ByVal DriverPath, Len(DriverPath))
   lResult = RegSetValueEx(hKeyHandle, "LastUser", 0&, REG_SZ, _
      ByVal LastUser, Len(LastUser))
   lResult = RegSetValueEx(hKeyHandle, "Server", 0&, REG_SZ, _
      ByVal Server, Len(Server))
   lResult = RegSetValueEx(hKeyHandle, "Trusted_Connection", 0&, REG_SZ, _
      ByVal Server, Len(TrustedConnection))


   'Close the new DSN key.
   lResult = RegCloseKey(hKeyHandle)

   'Open ODBC Data Sources key to list the new DSN in the ODBC Manager.
   'Specify the new value.
   'Close the key.

   lResult = RegCreateKey(HKCU, _
      "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources", hKeyHandle)
   lResult = RegSetValueEx(hKeyHandle, DataSourceName, 0&, REG_SZ, _
      ByVal DriverName, Len(DriverName))
   lResult = RegCloseKey(hKeyHandle)



    MsgBox "Created new ODBC DSN!", vbOKOnly + vbInformation, "ODBC Creation"

err_Exit:
    End
    
err_Handle:
    MsgBox "Failed to create new ODBC DSN!", vbOKOnly + vbCritical, "ODBC Creation"
    Resume err_Exit

End Sub

------------------------
Hit any User to continue

 

[arrian]

So, to create an ODBC connection, all I need to do is add the proper keys and values, right?

 

[SiJP]

Yep, add in your values to:

DataSourceName = "e.g. My DSN Connection to SERVER"
DatabaseName = "MYDATA"
Description = "Some connection blarg"
DriverPath = "C:\WINNT\System32\sqlsrv32.dll"
LastUser = "sa"
Server = "MYSERVER"
DriverName = "SQL Server"
TrustedConnection = "Yes"

Then run that block of code to create a DSN. You can then use this dsn in your app.

hth's

------------------------
Hit any User to continue

 

[arrian]

Ok, thanks! I've actually got an installer for the program, so I can have it add the registry keys there. Hopefully it'll work!

 

[SiJP]

There we go..

------------------------
Hit any User to continue

 

[virusstorm]

does that registry key value work for any windows version?

 

[RiverGuy]

Why force a dsn entry on the user's computer? If MySQL ODBC is like most, you can create a DSN-less connection. I would look into this first. Then all you need to distribute is the ODBC driver. And have your connection string set.