Let's take a closer look at the Edo connection object.
We'll use this object here as a connection to the database.
And of course we have to provide it a connection string.
Now there are two types of database connection protocols that we're going to discuss in this course
Open Database Connectivity or ODBC and Object Linking and Embedding database which is Ole DB in your
class.
Resource links.
There's a link to a page about open database connectivity.
And this is a low level meaning high performance database connection protocol.
It only connects to relational databases such as SQL server, MySQL, Postgres and things like that.
It offers high performance because it's based on the C language, and it will pretty much allow you
to do anything you can do directly into the database.
So ODBC is a good fit for most compliant databases.
You have to check and see if the database you're connecting to supports ODBC, and most of them do.
Ole DB stands for Object Linking and Embedding for databases.
Ole is based on Com, which is Component Object Model, which is the foundation for ActiveX.
Of course Ado is based on ActiveX.
The difference here is that Ole DB can connect to many other types of data sources such as files, XML
streams, non relational databases, as well as relational systems like SQL server.
A good rule of thumb when creating a connection is to take a look at the database you're connecting
to and see what interfaces it supports, and then you will know your options.
Since SQL server supports both Ole DB and ODBC, either of these can be used.
In our initial script.
We use an Ole DB connection, as you can see from the provider here.
But where did this connection string come from?
Let's take a look at different ways we can configure an Ado connection using Ole DB.
One method is to just go to connection strings.com.
There are a lot of sites like this.
And if you're building a connection string to a database, you can get a lot of information on this
website.
And so here we've got Ole DB providers, ODBC drivers and things like that.
So if we take a look at Microsoft Ole DB driver for SQL server, then it gives you a lot of different
options based on how you're connecting what security.
So here is an example using the Ole DB driver where you have to put in a username and password.
So you use this if you create your users in SQL server versus windows here this is using Windows authentication.
You see integrated security equals SSP which is what our original script used is the exact same thing
of saying we are a trusted connection.
So we can actually just copy this here and then go back to our button.
And I'm just going to paste it in here.
We're going to copy this.
And we just need to fill in the details of this connection string.
So the provider is going to stay the same.
The server is going to be the same thing as the data source.
So we'll just copy the dot slash link and then the database.
And this provider it's called a catalog.
And this provider it's called a database.
So you'll get used to the nomenclature here.
We can just kind of pop that in there.
And then I'm going to comment out this first one.
And we have to end with a double quote here.
And so if I've gotten all my arguments right here in our connection string it should use this new provider.
We'll click okay save and go to runtime click the button still works.
So we're just using a slightly different connection string here.
Now there's another way that you can build an Ole DB connection string to SQL server.
And I've provided this as a link in our class resources.
And it's by creating a UDL file.
Basically it is a universal data link.
And it prompts a wizard where you can go out and fill out information about how you're connecting to
SQL server, and it will generate the connection string for you.
And so this link basically takes you step by step on how to do what we're about to do.
But I'm just going to show you just go create a new text file.
And I'm just going to create a file called my Ole DB open string.
And then you give it the file extension UDL.
Hit enter.
And then just say yes to this.
And notice the icon changes.
When you double click this.
It brings up a wizard to create a data link.
And.
People use this to test whether or not they can hit a database, but it also generates the connection
string.
So you don't really want to browse for your SQL server unless you have to, because it just takes so
long.
And we know that our database is just dot slash c c.
Again, the dot means the local computer name.
I could also put the computer name in there.
We're going to use Windows Integrated Security and then select our database.
As long as I got this server right it'll connect to it.
And we want to connect to our battery code database.
Now if you've got a database file that is not attached to SQL server yet, you can also do that.
And we can just hit Test Connection.
That looks good.
And just looking at the provider tab here, it's using the Ole DB provider for SQL server.
You can see that we have a lot of other Ole DB providers and drivers.
So I could choose any of these others.
Also, you'll notice there is a wink DB provider for archives that's tag logging.
So a little later in the class we're going to learn how we can pull information directly out of our
tag logging archives.
But we're just going to leave this on our DB provider for SQL server.
And then we'll just go over and redo the test connection that looks good.
And we'll click okay.
Now what you have to do is just right click and open this up.
And I'm opening this up in notepad plus.
Plus if you don't have this program it's free.
I highly recommend it.
And here this should look familiar because it generated this string for me I can copy that.
Go back to our button script I'll just paste it down here.
You can see that it is exactly the same string we have here SQL DB dot one, Integrated Security, SSP
Initial Catalog, and our data source.
So that's how I generated that connection string.
And that's a that's a pretty good trick.
If you don't have access to go look something up.
So I am just going to comment this guy out and put our original one back in.
So next let's go look at our ODBC data sources and we can go back to our connection strings.
And I'll just go to the main thing here.
So you can see they have connection strings to a lots of different databases.
And we will go to SQL server.
And there's ODBC drivers here.
And these are just different versions.
And we need to know which ones of these we have on our system.
And so there's a quick way to do this.
Just go to your windows search bar and type in ODBC and you'll see ODBC data sources 64 and 32.
These are what you call Dsns data source names.
And you can see that with Winrk.
I've got data source names on different Winrk projects.
This is what Winrk uses.
They use 32 bit.
But if you go to drivers go down to SQL server, you can see that I've got the SQL server version 17
driver, the version 13 driver, the native client 11.0.
And there's some other drivers on here.
So this tells me which ODBC drivers that I have on this system.
We'll just go with the latest version and click ODBC driver 17.
And then again it's going to give you several different examples.
And so we like using the trusted connection.
Again you can put in integrated security equals SSP on that.
So I am just going to copy this connection string here and go back to our script.
And I'll go down and kind of paste it in here.
And we're going to do the same thing.
So I'm just going to copy my variable instantiation there.
Here we're just putting the name of the driver in parentheses letting us know that it is a literal string.
The server is going to be dot slash c c.
And the way that I remember which way the slash goes is it goes parallel to the W.
And then the database that's going to be.
Battery.
And then of course, we can leave.
The trusted connection equals.
Yes, which is the same as the integrated security spy.
And then again I'm going to comment this out.
So now we are using this string.
We'll click okay.
Save go to runtime and it's going to work with ODBC as well.
The last connection example I want to show you is using an ODBC, DSN.
We're going to call back up our ODBC data sources, but this time we're going to use the 32 bit ODBC
data sources.
This is because the VB script engine for Winc is 32 bit.
You'll notice that all of the Winc Dsns that are used by each project are also 32 bit.
If you try to connect a 64 bit DSN from a VB script in one cc, you'll get an architecture mismatch
error.
So when you're doing this example, make sure that we use 32 bit.
So I'm just going to click add.
And we're going to select a driver I'm going to use this generic SQL server driver here near the bottom.
And we'll click finish and a wizard comes up, similar to the ODB example we did earlier.
I'm going to call this my DB class DSN and just DSN example for the description.
And then for the server, I'm just going to type it in because the browser takes too long.
So this is just going to be ./1 cc and we'll hit next.
We're going to use windows NT authentication.
Make sure you have this checked because on the next screen we won't be able to see our databases.
We'll click next change default database to Batterico and we'll hit the next button.
And I'll hit Test Data Source.
And it is a test completed successfully.
So the DSN does work.
And I'm just going to click okay.
So now we have our DB class DSN.
It's 32 bit talking to SQL server.
And I'll just click okay.
Now when we go back to our script all we have to do is put our connection string variable down and just
type in DSN equals my DB class DSN semicolon.
And then add your double quote there and we'll just click okay.
We'll save and test it out.
And it works.
Nothing could be simpler.
And this is a good method if you just kind of want to stay away from doing the complex strings in your
scripts.
And you can always go back to the DSN and change it if you need to.
Now, of course, if you move this to another project, then you'll need to make sure you create that
DSN.
Coming up next, you're going to get some practice creating different types of Edo connections.
So I'll see you in the next assignment.