Cannot display ODBC login prompt - want to connect without DSN

[John Brown]

Well, I wasn't an ODBC expert, but I doubt the accuracy of that statement.
By it's very definition, ODBC required DSN's. The DSN's were the middle-man
created by the ODBC manager. The DSN was the extra layer that is precisely
the thing that causes the performance bottlenecks. It was OleDB that freed
us from ODBC and thus, DSN's. In other words ODBC = DSN.

-Scott

You can trust me on this. The function in the ODBC API that provides this
capability is SQLDriverConnect. According to the ODBC API reference, this
function was introduced in ODBC 1.0. The only doubt is when ODBC 1.0 was
released, but it was certainly long before OleDB. *1992* I got from
Wikipedia, but the rest is coming straight from the horse's mouth. See:

http://msdn.microsoft.com/en-us/library/ms715433(VS.85).aspx

If you are able to compile C programs, I would be happy to write a small C
program to demonstrate connecting without a DSN using the ODBC API, but that
would be Wednesday.

Regards,
John Brown.

 

[Scott M.]

Thanks for the offer, but I take your word for it. Question though: doesn't
that mean that the DSN-Less connection was available only to SQL databases?
And, for non-SQL users, you still needed a DSN?

-Scott

 

[John Brown]

Thanks for the offer, but I take your word for it. Question though: doesn't
that mean that the DSN-Less connection was available only to SQL databases?
And, for non-SQL users, you still needed a DSN?

-Scott

No. Going back to my subject line, each ODBC driver contains a dialog that
allows you to configure it to make a connection. So all you have to do is
supply a minimum connection string, which can be a DSN or a driver name; a
totally empty connection string would not work.


/* incomplete code fragment */
char szOutConnectionString[256];
SQLSMALLINT lenReturnedString;

rc = SQLDriverConnect(hdbc, NULL, "Driver={Microsoft Access Driver
(*.mdb)}", SQL_NTS,
szOutConnectionString, 255,
&lenReturnedString, SQL_DRIVER_COMPLETE);

In this case, the MS Access driver will display a dialog box with all the
necessary and optional parameters that can be set when connecting to an
Access database. If you supply a connection string with all the required
keywords, the box will not appear at all. You will just connect. If the
string is incomplete, the dialog box will be filled in with whatever
information you supplied, and then you can fill in the rest, or change the
initial values.

I hard-coded the Access driver name, but I could have read a list of driver
names from the Registry. Whichever name I supply, I will get that driver's
login box. It does not matter whether it is Access, SQL Server, Oracle, or
CSV files. When the function returns, I will be connected and I will have the
*complete* connection string in szOutconnectionString.



Regards,
John Brown.

 

[John Brown]

First, for the benefit of others who may find this thread, let me state a
method of displaying a dialog box prompt that will let a user connect to any
data source that has an ADO.NET provider. This program will work unmodified
with SuperDB 2012, even though their website is not up yet, and I have no
information about the connection string that I will need.

' sProviderName can be selected from a list box. Of course, we would
' display the friendly names, but use the object names internally
Dim dbpf as DBProviderFactory = GetFactory(sProviderName)

'Get a ConectionStringBuilder that will build a connection string for
sProviderName
Dim csb as DbConnectionStringBuilder = dbpf.ConnectionStringBuilder()

' Use a PropertyGrid contol on your form to display the properties
' of the connection string, which will correspond to the keywords in
' the connection string; user id, password, etc.
PropertyGrid1.SelectedObject = csb

' When you run your form, it will show a property grid that
' corresponds to the conection string for your database.

' After you show the form, you can create a generic connection object

Dim cnn as DbConnection = dbpf.CreateDbConnection()
cnn.ConnectionString = csb.ConnectionString
cnn.Open

' At this point, you will be connected to a database about which you knew
' nothing beforehand. You could have started with an empty
' config file, but you can save the whole string or any part of it in the
'config file.

Now, I will address the points that you raised.


:

You are telling me that you'll need to connect to Access and
Oracle, so that means you need to have two different connection strings.

This is the sequence of events, which I evidently did not make clear.

- I needed to connect to Access and Oracle with one program.

- The initial suggestion was that I should write:

If Access
DoThis
Else
DoThat
End If

- I was not interested, because I never had to do that before.

- So I asked myself "Does this mean that I cannot write what the ODBC
documentation calls a Generic application, i.e., one that 'must work with a
variety of different DBMSs and that the developer does not know beforehand
what these DBMSs will be.'"?

- The DbProviderFactory methods and generic Dbxxx classes were suggested,
but there was still a gap. In my opinion, such an application needs a way to
present a login box that is appropriate for the database. Yes, the user can
edit my config file, but if they wanted to edit config files, they would use
Linux.

- The PropertyGrid control was the final piece of the puzzle. It allows me
to present a GUI that will help the user to build a connection string.

First, I reject the scenario you have started out with. All you need from
the end user is their id, password, and database.

What you need depends entirely on what the developers of the database say
you need. You sent me to ConnectionStrings.com, so you know this. SuperDB's
connection string is not there, by the way.

You would not present
them with a list of OleDb Providers because they wouldn't understand what
that list represents.

I am assuming that ADO.NET providers have friendly names like "Data Provider
for SuperDB" for display purposes, and a name like Data.SuperDB.Client for
use in programs. Also, we never defined "user". If you use my program, then
you are a user, even if you are an MVP. I also don't care who is using the
program.

This is less a programming issue and more of a configuration issue. But, if
you need windows authentication, you can certainly set up the connection
string to use it.

At this point, I don't know which authentication methods are supported by
SuperDB, and I no longer care, because I now have a way to present the
options to them. They can set up their environment however they like. I don't
need to know. Don't ask, don't tell.

This is where I am bit confused. Are you saying that the user gets to tell
your program if Windows Authentication is being used? This is not something
that end users know and understand and hardly something that you'd ask them
to tell you. You should know how your users need to connect and code for
that.

Excel allows you to populate a spreadsheet with data from a database. You
can select a DSN, or you can create one on the fly. If you select the SQL
Server driver, it will present you with a login box with user name, password,
etc., as well as a "Use trusted connection" checkbox, which you can tick or
not. The Excel developers don't know whether your data is in SQL Server or
text files. They don't care whether you use WIndows or SQL Server
authentication. They also expect that Excel will be able to import data from
SuperDB when it is released, and they don't tell anybody that they need to
edit a file to connect to MS Access, SQL Server, or SuperDB.

Solution, the SqlConnectionStringBuilder class. This is not the problem you
think it is.

The solution is the generic ConnectionStringBuilder, along with a
PropertyGrid to allow the user to set the properties. It's useless without
the PropertyGrid. Or more accurately, I woud have to find out how the
PropertyGrid does what it does, but I have no reason to do that unless I was
trying to display a really elegant login box with tabs and what not.

Your OP was that you need to code against Access or Oracle, now you are
saying it could be anything?

Explained above. I *need* to support Access and Oracle, but I *want* to
support anything.

With the help of the PropertyGrid control, DbProviderFactory can certainly
help me figure out my connection string.

Since SuperDB has not been released yet, I cannot possibly create my own
dialog. I don't know what will be required. You keep insisting that all you
need is a user id, password and database, but it simply is not true. I am
going ask SuperSoft to add a surprise required parameter for connection.
We'll see how far you get with a dialog box with fields just for user name,
password and database.

What I am having trouble understanding is that it seems that you want the
user to tell the program (or you think that I'm saying that a user should
tell the program) explicit details about how to connect to the desired data
source.

This is exactly what I want. Users of Excel, Access, Crystal Reports and a
host of other applications do this every day. The real mystery is why you
think that it is so strange.

But none of this matters anymore. The problem has been solved. I can do
using ADO.NET what I used to do using ODBC.

Regards,
John Brown.

 

[Scott M.]

Hi John,

I'm happy that you were able to get the solution that you desired.

At the risk of beating a dead horse, and really for others who might stumble
across this thread, I wanted to just address one point that was central to
our discussion.

As a best practice, it is not a good idea to assume anything about the user
of your application, or at least to assume a level of knowledge that you can
not reasonably expect the user would have. Given this, when creating
database connected applications, it is not reasonable to expect your end
user to know anything about their data other than their own credentials and
what data they want. I know that in your particular case, you wanted a
scenario where the program wouldn't have to know about the database, because
the user was going to supply all the details, but unless you are building an
application for DBA's, this approach goes against all best practices for
good useability and design. You mentioned several times, when I pointed out
that a user should only enter these 3 pieces of info., that that was
incorrect, and I must finish out this thread by saying that it is correct
and to expect a user to supply/know any more than this is really what's
considered incorrect.

This is very much like two of the core tennants of Object-Oriented Design,
which are the concept of encapsulation and abstraction - - or to think of
your object as a "black box" of code. Only the creator need know how the
box does what it does, but the user of the box, need not (and should not)
know anything at all about the inner workings of the box. They only need
know how to interface with the box. To expect a database user to know (and
input) the details of connection string parameters, not only puts
unreasonable pressure on the end users, but also exposes your application to
a multitude of security concerns as well as potential corruption to the
underlying data store because, in your design (hooking the
ConnectionStringBuilder up to a PropertyGrid for the end user to populate),
you are breaking encapsulation of your application.

The first thing a hacker tries to do when wanting to get into a system, is
try to enter invalid data into input fields to see what error messages are
returned. These error messages (if unfiltered and passed directly to the
UI) in a database application, can contain path information as well as
information about the security model and database schema itself. Armed with
this information, the hacker can hone the attack directly against a specific
resource within the system. Allowing the ConnectionStringBuilder to bind
directly to a PropertyGrid, so that the end user can put whatever they want
in whatever parameters are listed is very hard to justify in a production
environment because you have no input validation layer in the applicaiton.

I do understand your stated requirements: that the program be completely
ignorant of the data source being connected to and that the program be
compatible with any and all other data sources that may come down the line.
And, in a perfect, monitored world, that may work. I don't know enough
about the environment you'll be implementing your application to be able to
predict exactly the success or failure you will have with your chosen
approach. But, as I said, expecting the users to supply all the pertinent
details of the connection, for the sake of the program being able to connect
to anything at any time, is generally considered a poor design choice.

I belive (well, actually I know) that you could accomplish much the same
results by having the basic shell of the potentially needed connection
strings standing by and simply ask the user for the desired data source,
their user name, and their password. Yes, the program will have to know
more details about the potential data sources and how to connect to them and
yes, I understand that this was not what you wanted, but the reality is that
this would generally be the better choice for improved security, control
over scalability, and the end user's experience.

I honestly believe that your design requirements (program doesn't know
anything about the data source, usesr supplies all pertinent connection
info., no custom UI need be developed) are unrealistic for any mainstream
application.

Please understand, I'm not trying to insult you in any way. I just want
anyone else reading this to understand what the best practices are in this
situation and why.

Good luck,

Scott M.

 

[John Brown]

:

As a best practice, it is not a good idea to assume anything about the user
of your application, or at least to assume a level of knowledge that you can
not reasonably expect the user would have.

I assume nothing. I just put the information out there. If they need help,
they can get it.

You mentioned several times, when I pointed out
that a user should only enter these 3 pieces of info., that that was
incorrect, and I must finish out this thread by saying that it is correct
and to expect a user to supply/know any more than this is really what's
considered incorrect.

I misunderstood you. I thought that you meant that those pieces of
information would always be sufficient, not that I should write my app so
that they would always be sufficient. I can save the connection string, minus
the user id password, and database, but of course I have to make the initial
connection first.

<security concerns snipped, but noted />

I honestly believe that your design requirements (program doesn't know
anything about the data source, usesr supplies all pertinent connection
info., no custom UI need be developed) are unrealistic for any mainstream
application.

Excel is not a mainstream application? This is what I suggest: convince
Bill. When he tells the Excel team to take this feature out, I also won't
bother with it anymore.

Please understand, I'm not trying to insult you in any way. I just want
anyone else reading this to understand what the best practices are in this
situation and why.

If you wanted to insult me, you could go right ahead. I don't know you. Why
would I care?

Regards,
John Brown.

 

[Scott M.]

Excel is not a mainstream application? This is what I suggest: convince
Bill. When he tells the Excel team to take this feature out, I also won't
bother with it anymore.

Ah, but the feature, as you describe it, is not in Excel. When you tell
Excel to connect to a data source, it does exactly what I'm advocating and
not what you are suggesting.

It prompts for the type of data source, the log on credentials, and the
particular database data deired. It does not propmt for the connection
timeout, the buffer size, whether Multiple Active Result Sets should be
used, if the connection should be pooled, etc.

Excel does, in fact, need to know something about the available data sources
and how to connect to them. Well, to be more precise, the Windows component
that Excel uses to provide connectivity to various data sources does need to
know certain aspects of how to connect to those resources and it does not
ask the user to supply those data. Also, depending on the data source you
do select as a data source to connect to in Excel, you will get a different
dialog to complete, which makes my point that someone over there at MS had
to program various custom dialogs depending on the data source selected.

In effect, when MS created the connect to data source feature for Excel,
they followed the exact design pattern I've suggested for you:

1. The program does know something about the various types of data sources
you can connect to.
2. The program does put up custom dialogs, based on the data source chosen.
3. The user is not required to supply any connection configuration
parameters beyond:
a. The type of data source to connect to.
b. Windows vs. custom authentication with the user's credentials
c. The specific database/table to connect to.

-Scott

 

[John Brown]

Ah, but the feature, as you describe it, is not in Excel. When you tell
Excel to connect to a data source, it does exactly what I'm advocating and
not what you are suggesting.

It prompts for the type of data source, the log on credentials, and the
particular database data deired. It does not propmt for the connection
timeout, the buffer size, whether Multiple Active Result Sets should be
used, if the connection should be pooled, etc.

You can use Microsoft Query to connect to an ODBC data source, or you can
use their Data Connection Wizard to connect to an OLEDB data source.

If you use Microsoft Query, and select "New Connection" and then select "SQL
Server", you get a small login box with the berver, authentication method,
user name and password . You have to click "Options" to select database, and
language, application name and workstation name are also available.

Connection pooling is implemented by the ODBC Driver Manager, not the
driver, so if Excel is only displaying SQL Server driver properties, you
wouldn't see it here. However, it does seem that properties exposed by the
driver are hidden here. I am not sure that Excel is hiding them. It could be
that ODBC's on-the-fly connection mechanism hides "Advanced" properties. I
could find out by calling SQLDriverConnect, but I will assume for the sake of
this discussion that Excel is the culprit.

When you use OLEDB, you are presented with a list of categories, including
SQL Server and Other. If you choose SQL Server, then only the bare
necessities are presented, but if you choose Other, and then select the
OLEDB Provider for SQL Server from the list of all installed providers, all
the gory details are available. The important stuff is on the first tab, but
there is an "Advanced" tab with "Connection Timeout" and other properties,
and an "All" tab with many properties that are meaningless to your
hypothetical pathetic user.

This list may not be complete; I see an "Extended Properties" property,
which I assume is used to manually enter keywords that are not specified in
the property grid. However, 22 properties are avalable, which goes way beyond
user name, password, and database. Therefore, when Excel knows the data
source, it displays a minimal dialog box, but when it doesn't know, it puts
everything out there, and you can do what you want.

Excel does, in fact, need to know something about the available data sources
and how to connect to them.

No, it does not *need* to know, but if it does, it can use that knowledge.

Well, to be more precise, the Windows component
that Excel uses to provide connectivity to various data sources does need to
know certain aspects of how to connect to those resources and it does not
ask the user to supply those data.

It does not *need* to know, and if it doesn't, it will certainly ask you.

Also, depending on the data source you
do select as a data source to connect to in Excel, you will get a different
dialog to complete, which makes my point that someone over there at MS had
to program various custom dialogs depending on the data source selected.

When you know the data source, certainly it is easier to design a custom
dialog box. However, when the source is unknown, that doesn't stop you.
Obviously, in ADO.NET and OLEDB, there is a way to query the provider at run
time to find out property names and the legal values for those that have a
finite set of legal values. This information can be used to dynamically
create a form. I did it using a PropertyGrid, but it can be done without one.
You would need a lot more code, especially if there is not layout manager
available so that you had to calculate the size and position of your controls
manually. You can use a checkbox for True/False properties, a list box for
properties with a finite set of values, and text boxes for everything else.

In ODBC, you don't have to do all of that, because a login box lives in the
driver. You don't have to use it, but it is there.

In effect, when MS created the connect to data source feature for Excel,
they followed the exact design pattern I've suggested for you:

1. The program does know something about the various types of data sources
you can connect to.

Yes, it does. The OLEDB connection wizard presents an initial list of
categories on my system:

1 - SQL Server
2 - SQL Server Analysis Services
3 - ODBC DSN
4 - Microsoft Data Access - OLE DB Provider for Oracle
5 - Other/Advanced

2. The program does put up custom dialogs, based on the data source chosen.

As I do with any database, known or unknown, even if it is just a property
grid right now.

3. The user is not required to supply any connection configuration
parameters beyond:

a. The type of data source to connect to.
b. Windows vs. custom authentication with the user's credentials
c. The specific database/table to connect to.

This is presented up front. In the case of Other/Advanced, other properties
are available in the Advanced and All tabs. You are not "required" to look at
them, but Excel does not hide them either, as you insist that it should.

I insist that Excel can connect to any data source for which a driver is
available, and that it exposes all the properties, or at least many more than
it really needs to, and does not save you from yourself. I can't prove it
until SuperDB is released.

I repeat, take it up with Bill. Maybe Bill might be less stubborn than I am.
I would tell him that if he removed the ability to talk to unknown databases
from Excel, then he can kill SuperDB in the egg. Even if he does not agree
with your philosophy about good and bad designn, he might buy that argument,
and you would get what you want. But a lot of people might want to get their
money back.

Regards,
John Brown.

 

[Scott M.]

I think the big point you've glossed over is that, while Excel *can* show
you extended connection properties, you don't *have* to see them. They are
not provided up front for the user to look at and guess which ones they are
supposed to fill in. This is the key point and the reason that I say that
Excel is more of an example of how I suggest you build a DB program and less
of an example of how you have indicated you'd like to go.

-Scott

 

[John Brown]

I think the big point you've glossed over is that, while Excel *can* show
you extended connection properties, you don't *have* to see them.

They are
not provided up front for the user to look at and guess which ones they are
supposed to fill in. This is the key point and the reason that I say that
Excel is more of an example of how I suggest you build a DB program and less
of an example of how you have indicated you'd like to go.

-Scott

True, this is a refinement that my crude PropertyGrid dialog lacks.

Of course, when SuperDB is released, my users may have to guess (or read the
SuperDB manual as they should), unless the OLEDB and/or ADO.NET specs say
that each property must indicate whether it is absolutely required. They
probably do, because I do not and never will believe that Excel contains a
gigantic select case statement with a branch for every OLEDB provider that
has ever been written.

And don't forget about the ones that have not been written. What do you
think Excel 2007 is likely to do when it is confronted with SuperDB 2012?

Regards,
John Brown.

Regards,
John Brown.

 

[Scott M.]

And don't forget about the ones that have not been written. What do you
think Excel 2007 is likely to do when it is confronted with SuperDB 2012?

Well, that's exactly my point. The current programming of Excel (or more
accuartely the Windows Data Source Connection component) would prohibit
connections to SuperDB if it could not be connected to using a current
provider/driver. An updated version of the program would have to be written
and released giving Excel those capabilities. This happens all the time
with the Microsoft Data Access Component librry (MDAC). New providers and
drivers are added so that existing software will know how to connect and
communicate with them. But, the software itself is not written in such a
way that it can handle any new data source that comes down the line (SuperDB
2012).

-Scott

 

[John Brown]

Well, that's exactly my point. The current programming of Excel (or more
accuartely the Windows Data Source Connection component) would prohibit
connections to SuperDB if it could not be connected to using a current
provider/driver. An updated version of the program would have to be written
and released giving Excel those capabilities. This happens all the time
with the Microsoft Data Access Component librry (MDAC). New providers and
drivers are added so that existing software will know how to connect and
communicate with them. But, the software itself is not written in such a
way that it can handle any new data source that comes down the line (SuperDB
2012).

-Scott

I find that very hard to believe, but since SuperDB isn't out yet, I'll
leave there.

Regards,
John Brown.