Re: ODBC DSN Connections

Philippe Lang wrote:
> Hello,
> 
> I have made some tests with .REG files, and it looks like what I need. After installing the driver on each PC, you simply run the .REG file, with all the keys of your ODBC connection. It allows you to configure a different username for each user, which is fine if you want to manage permissions server-side.
> 
> I guess this .REG file can be run from a domain login script.
> 
> Does anyone have a better solution for distributing DSN (and drivers?) on a large network?

The solution is to not use DSNs - instead, write your application to use 
a DSN-less connection. This way, the application, when it runs, sets up 
its own "temporary" DSN(s), and you don't have a slew of them all over 
the place, easy for the user to get at and (possibly) screw up. Note 
that this doesn't eliminate the need for installing the driver on each 
machine. I am not sure what to do to get around this issue.

The only time you should use a system DSN is if it is on a single 
machine, and the DSN is likely to be used by many different applications 
on that machine (like perhaps a web or application server of some sort). 
That way, if the DB location changes, you update the one DSN that all 
apps use, and they are all happy. If you had each app set up on one 
machine using its own DSN-less connection, then you would likely need to 
update each app to tell it where the new location was.

Now, perhaps the DSNs being put onto each machine are used by multiple 
apps on the machine. This can be a thorny issue. DSNs are tough to 
maintain, and DSN-less connections are also tough to maintain across 
multiple apps.

I would probably make an app that is run at startup that has a DSN-less 
connection to a simple database set up on a small system that will 
likely never change. This database would hold information telling the 
location and DSN name for a bunch of databases. The app reading the 
database would be put on each of the PC's. Now, from here, one of two 
things can occur. Either each app can query this one app, to get the DSN 
name and location for the DSN-less connection, or at startup, the app 
can run, read the small database, and *overwrite* old system DSN 
entries. So that, the user always has the latest up-to-date system DSNs 
set up (you manage the DSN database yourself, likely through a small web 
application or something). If they change them, the next reboot resolves 
any issues. I suppose a service could also be created that runs on 
startup that auto-checks the DSNs against the DB periodically and 
updates as well, as needed. I suppose a third option could be that each 
application could use the single DSN to first read the small DB to know 
how to set up the DSN-less connection itself, instead of creating the 
system DSNs each time.

As far as the drivers are concerned, I would say you need to do the same 
as you currently do for installs of any other software on the systems. 
Of course, this can be time consumming. I will tell you what I did for 
an application I take "care" of:

This application is a VB executable that is distributed across many 
machines in the company I work for. For a long time, we were compiling 
the app, rebuilding the install, and telling users to download and 
install the application. After a while, this became a big deal, because 
sometime people wouldn't install the new application, and continue to 
use the old app version, or we had to send people to each machine to 
update it.

I ended up writing a little application that would act as an "update" 
utility. Each user of the main application being distributed has to log 
into the application. I set up on the database a flag that says "update 
this user". When the flag is checked (handled by admins on the main 
application), and the user nexts logs in, it checks the flag and if set, 
only allows the user to update or log out - nothing else. They *cannot* 
log back into the system until they update the application. When they 
decide to update, the system runs the updater application, and kills the 
main application. The updater application reads an update area on the 
network, and copies over any updates (it is actually more complex than 
this - the updater utility is actually a custom scripting engine that 
allows it to do many different things to the user machine, under control 
of an update script that administrators can change to suit the update 
needed). After it is done, it "unflags" the user's update flag, and 
restarts the main application to allow them to re-login.

There is other logic that allows the updater (and the main application) 
to update the updater (the issue involved is that you can't overwrite a 
running EXE under Windows - so you have to "juggle" the applications, 
shutting one down while running another that does the update - updating 
the updater is a pain, but the functionality is there). So - we then 
only had to make one last "manual" update to everybody's system - and 
then we could do all future updates remotely. Since its inception, it 
has worked as designed, and has virtually eliminated our need to 
manually update users systems, at least for the application which it 
services (and technically, it could probably be used for other updates, 
such as external software and such, but we haven't had a need to explore 
that path yet).

Something like this could be what you need to implement, to distribute 
the driver (and any other future updates) to users. If you write such a 
system, try to anticipate *all* possible uses, and design the scripting 
system to allow for this (for example, my updater allows for moving, 
copying, and deleting of files on the client machines - there are other 
commands in the scripting language for displaying message boxes, 
yes/no/ok/cancel boxes, etc - as well as branching of the script so the 
user can do certain things). I know when I first created the updater, I 
didn't originally design it to update the updater - then I found I 
needed to do this. However, because I had designed in a ton of stuff 
into the updater on the first round, creative scripting for the updater 
(and some extra coding in the application) allowed me to get around this 
  issue, and update the updater. Had I not designed a lot of extra 
functionality into the system in the beginning, I likely would have had 
a problem when it came time to update the updater.

I hope this makes sense...

Andrew Ayers
Phoenix, Arizona

-- CONFIDENTIALITY NOTICE --

This message is intended for the sole use of the individual and entity to whom it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you.