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
simplyrun the .REG file, with all the keys of your ODBC connection. It allows you to configure a different username for
eachuser, 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
informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the
intendedaddressee, 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
thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.