ODBC Connection – How to not hard-code username and password

It looks to me like it is saying SQLDriverConnect doesn’t support dialog

boxes; I don’t think it implies anything about SQLConnect other than it

won’t take as many parameters as SQLDriverConnect.

I assume you want to do this with a GUI?  A quicker way might be to use

the Readline module and just use it to set usr and pwd from

a command line (if you don’t need the GUI).

Can you supply a little more information about what you want to do?

Lingmei,

I’ve been thinking about this, and I’m not sure I understand how Cloverleaf

is involved?  If they are logging into Cloverleaf directly (That Would Be Bad)

you’d have this issue, but you have this issue anytime you have a user

logging into an application across The Net.  I’m not sure how a logging into a

DB directly would have anything to do with Cloverleaf.

There are a lot of solutions, and setting up a configuration file with encrypted

passwords is a common one.  Otherwise you can have them go through a

web interface, or a command line interface (like sftp or ssh)…

Are you writing a script for the clients, so they can use it to transfer message

files?

I have a script that grabs the name/pass from odbc.ini for whatever datasource you’re connecting to. Is it possible to use multiple usernames for the same datasource?

Ok, this seems to be new territory at lest for me. I have been using cloverleaf from it first version as HCI-link.  I have always used it as a backend processing application with no user having direct access to the system or even knowing any more that it is between our systems to handle protocol and mapping issues.

So I’m having a hard time trying to figure out what you are doing. I have done a bunch of ODBC stuff with cloverleaf to pull extra data out of systems and to poke stuff into databases.

But this is the first time I have heard of someone doing this. I would be real interested in what you are doing and I am sure if I understood better that. I could offer a better solution.

I know this may be asking a lot . But could you give maybe a process flow of what you are expecting of the engine and user.

What interface is the user going to get where they are prompted for the user-id and password.  Do they need to supply any other information.  

Are you doing some kind of HTTP front end stuff on the engine ?

Oho!

We get our Cloverleaf via ,

but it’s not a “streamlined, user-friendly value-added” Cloverleaf.  I think

they tried that, and it burned them.  (I know they dropped doing Cloverleaf

tech support in favor of letting Cloverleaf do it.  I would not care to speculate

about why, but I am not sorry they did.)

I think you’re in Terra Nova, unless you talk to someone at that level.

For what it’s worth, it sounds like you are going to need a completely scripted

setup anyway, so you may as well just gather the information during setup

and generate the script on-the-fly into an access protected file.

(I know you were asking John, [Hi, John!] but what the heck.)

–kk

I pass the DSN, UserID and password as param’s to the tcl proc I’m using.    If you have different threads for each clinic you could do the same.  But I guess this all depends on how you have the connection and process setup.

I have tcl proc that I call to process the data. Most of the time as a pre-xlate upoc.  When you add the tcl proc to the “Pre Procs:” list  there is a box to pass the paramaters as arguments to the procedure you are calling.

I was wondering the same thing.

Is there a way (using some odbc function?) to get the username and password from the odbc.ini file rather than hardcoding them in the tcl proc, or passing them in as parameters?  Our odbc.ini file has the username and password listed, so why not get them from there?  That way the username and password are only in one place, the odbc.ini file.  Easier to manage, no code changes whenever a username/passwd changes.

Hardcoding these values is obviously not the best solution…

thanks,

Steve

Steve,

What we are doing here is to use the Connect String when connecting.

That allows us to use the odbc.ini file if we wish and define DSNs therein that can contain the UserId and Password if desired.

It is our feeling at this time that we won’t use the odbc.ini file for more than the place where all of the parameters for connection, etc. for the various supported DBs live and we will override those default settings at connect time via the Connect string. The rationale here is there is only one odbc.ini file and if it gets mangled due to an update, existing ODBC activity may be impacted.

We tried to figure out how we can have a separate odbc.ini file for each site or process or (even better) thread but could see the engine only loading one odbc.ini file for all.

By specifying those arguments that are different than the default at connect time (via the connect string) we can use the same dsn many times and yet not worry about hosing the odbc.ini.

We get our arguments for our generic proc (our intent is to use one proc for all ODBC activity – or at least most) via a lookup table (this can even work with Xlates).

Thus a DSN in the odbc.ini file is the basis for any Oracle DB (for example – could be any supported DB) and the lookup table is the override arguments which are plugged into the Connect String.

Each specific Use of an Oracle DB would have those arguments specified to adapt the dsn basis to point to the appropriate DB.

JIm,

That all sounds very reasonable.  I like using a lookup table too.

But you lost me on the “Connect String”.  Do you mean the SQLConnect call:

“odbc SQLConnect $hdbc $dsn SQL_NTS $uid SQL_NTS $pwd SQL_NTS”

or?  Can you post an example?

thanks,

Steve

Steve,

Like this:

set err [odbc sqldriverconnect $hdbc $dsn $conn_string SQL_NTS “” 128 “” SQL_DRIVER_NOPROMPT]

Where $conn_string would contain the arguments (including the dsn name that would differ from the basis found in the referenced dsn) that the DataDirect drivers need.

By the way the $dsn field referenced in the above command is NOT the dsn to which I am referring (that field actually has a null value) the dsn is actually an argument in the connection string.

So a connection string might look like this: “Dsn=SQL Server Wire Protocol 5.2;Address=nnn.nnn.nnn.nnn,ppppp;Database=MyDB;UID=Me;PWD=ImOK”

Obviously the above is for Sql Server but it could just as well be for Oracle, or DB2, or any other supported DB.

The rules for a connection string, etc. are contained in the Healthvision provided documentation or from the DataDirect web site.

I see.  Interesting.  At our site we find that the SQLConnect call is sufficient for our needs.

thanks very much!

–Steve

bump