SQLConfigDataSource how to guess Attributes? 1

[acjim]

Hi,

I'm trying to setup DSN's dynamically using VB in Access. I've used the following source code (modified) from MSDN to try and connect to a Unify DBIntegrator Database.

Option Explicit
Const ODBC_ADD_SYS_DSN = 4 'Add data source
Const ODBC_CONFIG_SYS_DSN = 5 'Configure (edit) data source
Const ODBC_REMOVE_SYS_DSN = 6 'Remove data source

Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal _
hwndParent As Long, ByVal fRequest As Long, ByVal _
lpszDriver As String, ByVal lpszAttributes As String) As Long

Function Build_SystemDSN(Description As String, ServerDSN As String, ServerName As String)

Dim ret%, Driver$, Attributes$

Driver = "Unify DBIntegrator Client" & Chr(0)
Attributes = "DSN=" & ServerDSN & Chr(0) & "DATABASE=" & ServerDSN & Chr(0) & "SERVER=" & "ServerName" & Chr(0)
Attributes = Attributes & "DESCRIPTION=" & Description & Chr(0) & "UID=" & Chr(0)

ret = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, Driver, Attributes)


'ret is equal to 1 on success and 0 if there is an error
If ret <> 1 Then
MsgBox "DSN Creation Failed"
End If

End Function


I know the code works for other types of database, sql server and access for example, but when I try to run it for the Unify db it crashes out.

Any guesses on how I could find out what Attributes the database driver is expecting?

Many Thanks
Jim

 

[NoCoolHandle]

Just for yUks...

What i tend to do, if I need to know what attributes are being set, is to build a filedsn, then open the file and look at its values.. (at this point 100% success but I can test if for the UNITY server)

Memory says that filedsn's are found at
C:\Program Files\Common Files\ODBC\Data Sources

then jsut open with your favorite text editor

Rob

 

[acjim]

Thanks Rob,

I think that may work for some DSN's / DB's but all my system shows is:

[ODBC]
DSN="DSN Name"


The details are held for the system DSN, which is where this instruction is linking to, in the Registry.
I've tried using some or all of the attirbutes listed there but I'm getting crashes.

Anyone got experience with this database?

Thanks,
Jim

 

[NoCoolHandle]

Jim,

I just checked.. I went 1 directory too deep.

Look at
C:\Program Files\Common Files\ODBC\

The Datasources seems to point toward Reg Based DSN's..

Sorry. Memory isn't so good sometimes.

Rob

 

[acjim]

Hi Rob,

There's nothing in that Folder apart from the "Data Sources" folder.

I'm on win 2k btw, don't know if that makes any difference.

Thanks

Jim

 

[NoCoolHandle]

Jim,
Wow... You seem to have the found the correct entry the first time. I just built one for my SQL system and it was in the datasources section.

I am guessing that you are trying to setup a dsn during deployemnt.

If you don't mind registry hacks..
Take a look at HKLM\SOftware\ODBC\ODBC.INI
and then look for a regbased entry for your database..
And
HKLM\Software\ODBC\ODBCINST.INI

You have a couple of options for recreating them..
1. RegWrite
2. Export the key and then use RegEdit to install it during deployment.

I did use the RegEdit option to do RegKey creations in a project a while ago and it works exteemly well. It builds all keys and subkeys in a single action. (I will check the swithc you will need and synatx and post back)

Give it a go and let me know if it works.

Rob
PS there might be another location in the registry, I will take a poke around and check..

Worth looking at.

 

[NoCoolHandle]

As per

http://support.microsoft.com/kb/q131352/

The syntax and command-line switches for using Regedit to import to, create, or export from the registry in real mode are as follows:

To merge or import a text file into the registry, use the following command:
regedit [/L:system] [/R:user] filename1
To create and replace an existing registry from a text file, use the /c switch as follows:
regedit [/L:system] [/R:user] /C filename2
To export text from the registry, use the /e switch as follows:
regedit [/L:system] [/R:user] /E filename3 [regpath]

 

[acjim]

hi rob,

I've kind of tried that out and it works well, using RegSetValue and RegCreateKey - not done anything with the Registry before and I'm impressed.

Much easier than trying to set the DSN up with SQLConfig...blah blah!

Have a star for your quick responses and for getting me onto a new path!

Jim

 

[NoCoolHandle]

Jim, thanks for the star.

Just a couple of notes (from doing some serious registry diving and grasping ways of manipulating data there..)


You can use win32api(declares calls), but they are an extreem pain in the rear. The windows scripting host is the easiest way I have found to code reading and writing single line entrys..

RegEdit.Exe is by far the easisest way to build entire key\subkey entrys.. The only negative is that you will get a message box popup to tell you that the "key was successfully created" or something like that.

However {code]Shell "CMD /C 'RegEdit ODBCKeys.reg'" is very easy....[/code]

For windowscripting host..
(reference "Windows Scripting Host" wshom.ocx)

Private Sub Form_Load()
Dim w As WshShell
Set w = New WshShell
w.RegWrite "HKLM\SOFTWARE\Rob\Somekey", "test"
Set w = Nothing
End Sub