Access 64-bit DSN from vb.net

[Former Member]

Well, I've run into a road block and could use some ideas. I built a program that looks at a SQL Anywhere database 9.0. It has worked fine under XP or windows 7 as long as the DB is in the 32bit ODBC directory. As the application that creates the database, not mine, puts it there. Seems like the application in W7'puts the DSN in the 64-bit directory now. I've managed to get the listing off the DSN from the 64-bit directory but when I take the DSN and put it in the connect string the error says "DSN' blah,blah,blah' does not exist.   So, I'm assuming that for some reason the connect string is missing something or my library the iAnyhere.Data.SQLAnywhere.v3.5 does not have the idea to look at the 64-bit ODBC sources.   I've tried DSN-less connect string without any luck cannot seem to geta valid connect string built. Have scowered the Internet for examples without any success.   Any ideas?  

[Former Member]

Hi Chris,

Thanks for the quick response. The issue is that the SQL Anywhere Local database ODBC is created by another program. I'm trying to document the local DB using VB with imbedded word document creation. So, now to figure out how to create a 64-bit program I guess so that the code and db.conn will know where the string is located.

I did create the program to be 64 bit only but still it cannot find the 64 bit DSN although I can find the DSN walking the register.

any other ideas?

[thomas_duemesnil]

Can you show us the connection String you are using. It is mostly the best way to avoide the ODBC Manager completely. Btw you have both 32 and 64bit driver installed on the machine ?

[chris_keating]

The key is understanding how Windows handles 32 vs 64 bit ODBC DSNs. This link discusses the details.

Your application and client software must be the same bitness. In this case, it appears the application is 32 bit - although by default .NET runs applications with the same bitness of the operating system - and the driver is being setup in the 64 bit ODBC environment. And the process for creating the DSN is incorrectly using the 64 bit version of the ODBC. Provided you have the 32 bit ASA9 odbc driver, you can simple create the 32 bit DSN using the 32 bit ODBC Administrator.

[Former Member]

Hi Chris, Well, here is what I'm trying to accomplish. I've included an import: Imports iAnywhere.Data.SQLAnywhere

And then the following code: Dim strConnectString As String Dim dbConn As New SAConnection strConnectString = "Data Source=" & Form1.gsODBC & ";UID=" & Form1.gsLoginName & ";PWD=" & Form1.gsLoginPass 'strConnectString = "Data Source=" & "C:sea81xtoolsLOCALsse_data.dbf" & ";UID=" & Form1.gsLoginName & ";PWD=" & Form1.gsLoginPass 'strConnectString = "Provider=ASAProv; Driver=Adaptive Server Anywhere 9.0;" & _ ' "DefaultDir=C:sea81xclientlocal;" & _ ' "Dbf=c:sea81xtoolslocalsse_data.dbf;" & _ ' "Uid=" & Form1.gsLoginName & _ ' ";Pwd=" & Form1.gsLoginPass & _ ' ";Dsn=""""" 'strConnectString = "Provider=ASAProv; Data Source=" & Form1.gsODBC & ";UID=" & Form1.gsLoginName & ";PWD=" & Form1.gsLoginPass dbConn.ConnectionString = strConnectString dbConn.Open()

You can see I've tried a lot of different scenarios. If I did a provider the connect string did not work for an error on the parameter.

Load DSN into the a form: If strOSBits = "Bit64" Then regKey = Registry.LocalMachine.OpenSubKey("SOFTWAREWow6432NodeODBCODBC.INIODBC Data Sources", True) Else regKey = Registry.LocalMachine.OpenSubKey("SOFTWAREODBCODBC.INIODBC Data Sources", True) End If

        ' check if either sqlanywhere 8 or 9
        bValidDLL = CheckODBCDll(Left(strResult, lngValueLen), strOSBits)
        If bValidDLL Then
            'strResult = strResult & lngCurIdx & ": " & Left(strValue, lngValueLen) & vbCrLf
            Form1.ComboBoxODBC.Items.Add(Left(strResult, lngValueLen))
        End If

[chris_keating]

What is your actual desired connectivity technology? You seem to be only importing support for .NET Data Provider. Do you really need ODBC as it is not needed if you are going the .NET root. That said, your code referencing the ODBC layer appears to be incorrect. The Wow6432Node in the registry is the 32 bit registry hive. It appears that your IF condition is reversed .

[Former Member]

Hi Chris,

When I check for bitness and go to the 64 bit 32 bit driver and retrive the source I get the correct DSN. When I put the dsn into the odbc call I get an error it cannot find the source.

My goal is to access a SQL Anywhere 9.0 database. I've even registred the dobodb9.dll but it seems to not want to put the driver in the set.

I'm game for any method to access the database via whatever mechanism I can use. I chose odbc as that is what has worked for the 32bit xp environment. In fact I got a sql 9 developer 64 bit version that created an entry into the 32 odbc manager and created a dsn for the database and the program worked just fine again.

On the strings - there are like 5 versions of it 4 with a comment in front and the first one that does the call that works is the microsoft odbc dll.

I've tried the iAnywhere 12 .dll and odbc but some of the issues are then that the db is 9 and 12 won't read it.

So, again whatever you think will work.

gotta run catch a plan.

thanks tharre

[chris_keating]

Here is a sample connection call for an ASA Data Provider based connection.

  AsaConnection conn = new AsaConnection(
    "Eng=<db_server_name>;
    DBN=<db_name>;
    Links=TCPIP;
    UID=DBA;PWD=SQL" );

If you want or need to use ODBC, you have to install the ASA9 ODBC driver and create a DSN that has the same bitness as the client application. For 64 bit, this means you have to install the ODBC driver from %asany9%\\Win64 and use the 64 ODBC administrator to create the DSN. And when referencing the registry, ensure that you are reference the appropriate bitness hive.

[Former Member]

Hi Chris,

Thanks, I'll try the connection as it looks close to one of the many that I have tried. You suggested something that clicks a little and that is referencing the appropriate bitness hive.

I think that is where I'm off. As I've been able to find the proper ODBC DSN I want but when I put it in the string it says not found and I'm sure that is because the program is pointing to the 32-bit path. So, I've been looking on the internet for the way to add the bitness hive to the path of the dsn so that I'm pointing to the correct ODBC DSN.

Thanks again

[chris_keating]

I don't understand the path issue? Are you using a FILE DSN? If you are simply trying to use a DSN, you don't provide a path. You simply reference it. But you need to make sure that the DSN is created. Did you review the information that I referenced in my first post? Here is that link again http://sqlanywhere-forum.sap.com/questions/11005/access-64-bit-dsn-from-vbnet

[chris_keating]

As previously stated, the following is looking at the 32 BIT ODBC entries.
Registry.LocalMachine.OpenSubKey( "SOFTWAREWow6432NodeODBCODBC.INIODBC Data Sources", True)

So, the condition check "If sOSBits = "Bit64" Then" means that the OS is 64 bit and you want to look at a 64 bit ODBC entry, you are are incorrectly looking at the 32 bit DSN entry (as Wow6432Node is 32 bit not 64 bit. It is a confusing naming standard but it is what it is.

The following is a 64 bit entry on a 64 operating system (and 32 bit on a 32 bit machine) Registry.LocalMachine.OpenSubKey( "SOFTWAREODBCODBC.INIODBC Data Sources", True)

[Former Member]

Hi Chris,

That's what my code does:

Public Function LoadODBC() As Boolean ' this sub calls the registry to get all the odbc files and then before writing them ' to the pick applet will verify they are SQL Anywhere local databases Dim regKey As RegistryKey

    Dim lngCurIdx As Long
    Dim strValue As String
    Dim lngValueLen As Long
    Dim lngDataLen As Long
    Dim strResult As String
    'Dim iListCnt As Integer
    Dim i As Integer
    Dim bValidDLL As Boolean
    Dim strOSName As String
    Dim strOSEdition As String
    Dim strOSSvcPkg As String
    Dim strOSVer As String
    Dim strProcessor As String
    Dim strOSBits As String
    Dim strPgmBits As String

    strOSName = JCS.OSVersionInfo.Name
    strOSEdition = JCS.OSVersionInfo.Edition
    If (JCS.OSVersionInfo.ServicePack <> String.Empty) Then
        strOSSvcPkg = JCS.OSVersionInfo.ServicePack
    Else
        strOSSvcPkg = ("Service Pack = None")
    End If
    strOSVer = JCS.OSVersionInfo.VersionString
    strProcessor = JCS.OSVersionInfo.ProcessorBits
    strProcessor = (String.Format("{0}", JCS.OSVersionInfo.ProcessorBits))
    strOSBits = JCS.OSVersionInfo.OSBits
    strOSBits = (String.Format("{0}", JCS.OSVersionInfo.OSBits))
    strPgmBits = JCS.OSVersionInfo.ProgramBits
    strPgmBits = (String.Format("{0}", JCS.OSVersionInfo.ProgramBits))

    Form1.ComboBoxODBC.Items.Clear()
    If strOSBits = "Bit64" Then
        regKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\\Wow6432Node\\ODBC\\ODBC.INI\\ODBC Data Sources", True)
    Else
        regKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", True)
    End If

    If regKey IsNot Nothing Then
        lngCurIdx = regKey.ValueCount
    Else
        GoTo Exit_LoadODBC
    End If

    If lngCurIdx = 0 Then
        MsgBox("Cannot open key")
        Exit Function
    End If
    lngCurIdx = lngCurIdx - 1
    For i = 0 To lngCurIdx
        lngValueLen = 2000
        strValue = Str(lngValueLen)
        lngDataLen = 2000

        strResult = regKey.GetValueNames(i)

        ' check if either sqlanywhere 8 or 9
        bValidDLL = CheckODBCDll(Left(strResult, lngValueLen), strOSBits)
        If bValidDLL Then
            'strResult = strResult & lngCurIdx & ": " & Left(strValue, lngValueLen) & vbCrLf
            Form1.ComboBoxODBC.Items.Add(Left(strResult, lngValueLen))
        End If
    Next
    regKey.Close()

Exit_LoadODBC: 'MsgBox("Cannot find ODBC Data source entries in the registry") Exit Function End Function

[Former Member]

Public Function CheckODBCDll(ByVal sODBCDsn As String, ByVal sOSBits As String) As Boolean

    Dim regKey As RegistryKey
    Dim regValue As String
    Dim sODBCDll As String

    ' query the Registry to see if the ODBC is SQL Anywhere
    ' open the key
    If sOSBits = "Bit64" Then
        regKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\\Wow6432Node\\ODBC\\ODBC.INI\\" & sODBCDsn, True)
    Else
        regKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\" & sODBCDsn, True)
    End If
    'regKey = Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\" & sODBCDsn, True)
    regValue = regKey.GetValue("Driver")
    If regValue <> Nothing Then
        ' Check the ODBCDll to insure it is only SQL Anywhere ODBC entry.
        sODBCDll = Right(regValue, 11)
        If sODBCDll = "dbodbc8.dll" Or sODBCDll = "dbodbc9.dll" Or sODBCDll = "bodbc12.dll" Then
            CheckODBCDll = True
        End If
    End If
    ' close the key
    regKey.Close()

End Function

[Former Member]

Since I have the correct ODBC DSN what am I missing to get the connect string to work with the ODBC DSN.

[chris_keating]

This is what I think your code should look like if the sOSBIts reports the bitness of the OS. Notice that the OpenSubKey calls are reversed.

' open the key If sOSBits = "Bit64" Then regKey = Registry.LocalMachine.OpenSubKey("SOFTWAREODBCODBC.INI" & sODBCDsn, True) Else regKey = Registry.LocalMachine.OpenSubKey("SOFTWAREWow6432NodeODBCODBC.INI" & sODBCDsn, True) End If

You should also verify the DSN manually in the administrator. Check the bitness of the administrator in taskmgr by looking for *32 appended to the exe name. This means it is a 32 bit process. This is important as both the 32 bit and 64 bit administrators are named odbcad32.exe. You should also manually walk the registry to verify you entries actually exist. I am working under the assumption that you have created the DSN correctly and you are wanting to use a 64 bit DSN. When you check the 64 bit dsn, make sure that you pointing to the 64 bit ODBC SQL Anywhere driver particularly if you have not used the SA installer.

[Former Member]

Thanks Chris - this all makes perfect sense to me. How would I know whether or not I'm pointing to the 64 bit ODBC SQL Anywhere? As I'm pretty sure I'm calling the 32 bit driver but I don't know how or why.

[chris_keating]

If you were loading the 32 bit driver in a 64 bit ODBC driver manager, you should get a system error code 126. What is more likely is that you do not have the DSN defined in the 64 bit administrator. To ensure that you are using the 64 bit administrator, open task manager and ensure that the image name is odbadm32.exe (ensure that it is not odbadm32.exe*32 which means it is a 32 bit process). Then verify that the DSN is defined and can connect to your database using the Test Connection button for the DSN. If the DSN exists, is it an user or system DSN. You code is looking for a SYSTEM DSN. If defined at as an user DSN, it may explain the problem as those DSN have limited visibility. Try making it a system DSN and retest,

[VolkerBarth]

Just a slight correction: The ODBC Administrator's program file is named "odbcad32.exe", not "odbadm32.exe".

[Former Member]

Hi Chris - I got so frustrated with this whole thing that I gave up until now. Now, I need to get this program to work. So, I did as you suggested above. I brought up the ODBC 64 data source manager. I then verified that I am looking at a System DSN. I tested the connection and it worked. Went to the Task Manager and the program running is odbcad32.exe *32. So, this is a 32 bit process running under the 64 bit manager. Any new clues?

[Breck_Carter]

I suggest you start over with a new question, separate from this one. Talk about your situation as it exists now: the exact error messages or symptoms you are seeing now, the exact code you are using, the exact regedit export of the DSN you are using, and the proof that the DSN posted is the one being used by the code posted (use a new, unique DSN name). Do not re-type anything, use only copy-and-paste. As explained in Wikipedia here, http://en.wikipedia.org/wiki/Satan, ODBC was created to test our sanity.