Now it's time to write our first Visual Basic script in wink that can execute SQL queries against a
database.
But first, let's take another look at SQL Server Management Studio.
When we open management studio, we go through a series of steps to be able to get the data we want
from the database.
The first one is we are going to connect to the database engine that has our database.
We put in the server name.
So in this case this is a named connection.
This is the name of my computer.
And then slash c c.
So this is the database server that wink installs.
And we're using Windows Authentication.
We'll click connect.
The next thing that we're going to do is connect to the database that we want.
So I'll just pick battery Co.
And then finally we will enter in a SQL command.
I'm going to introduce you to the top command.
If I don't want everything from the table I can just say top and then give it a number.
This will return the top ten records from customers and ordered by their last name and the first name.
The third step is we're going to execute that query and see the results here.
We've got our column names and we've got our ten records.
If we were to change this to say 20.
Then of course we would get 20 records.
The reason for me showing you this is that in a script we go through these exact same steps.
So now let's take a look at a simple Ido script that executes the same query and returns the same results.
So I will just create a button, and the caption on this button is just going to be my first Ido script.
Ido stands for ActiveX Data Objects, and that's how we're going to be connecting to the database.
Then I want to format it like this button.
So I'll use my eyedropper to copy the properties from that button and paste them into this button.
Next I am going to go to the events on my button, and I'm going to right click and do a VBS action
on this button.
Now, for those of you who work with VBS and other platforms, what you're learning in this course is
directly transferable to that.
And so here is just a simple script that will connect to the database, execute the same query and print
out the results in wink.
So let's execute this real quick.
See what the output is.
And then we'll spend some time going through the script to see how it works.
I'll just click okay, save and go to runtime.
In runtime, we'll click our button and we print out the number of records and number of fields.
And then we print out our results.
And we're just using like a debug print.
Once we have this script, of course we could export it to a CSV file.
We could throw it into tags.
We could do whatever we want.
Now let's go take a look at the script and see how it works.
So we'll select the button on the events tab.
We're going to open this script.
What is ado?
Well if you look in your links and your resources, this first one tells us about 80 objects and collections.
And this link also gives you access to other information about Ado.
It tells you what it is.
It's basically a programming model, and you can use it in various programming languages from Java,
JavaScript as well as C plus plus.
So the skills you're learning in this class will transfer to those other languages.
I think of it as an API, and it allows you to get information from databases and other data sources.
Let's go back to our objects and collections.
So there are certain objects that we're going to use in order to connect to the database and get our
query.
So we have a connection object which is a connection to a database.
That's the first thing that we do when we open SQL Management Studio.
There is a command object that allows us to create and execute commands.
There is a record set object that gets the results of our SQL queries that we can go through and print
out, and there's a few other things in here.
So a record this is a record in a table.
There's also an error object that allows us to handle errors.
And then we have collections.
So each record is made up of fields or columns.
So we have a collection of fields that we can loop through and print out information.
Also the database objects in Ado have properties so we can manipulate the properties.
In our script.
You'll see we're getting the field names and the field values and things like that.
So this is where this information is coming from.
Let's go back to our script.
We create a connection object and this syntax is how we create it.
And then we also just create a record set object to get the results of our query.
And then we're just creating our SQL command in a string variable.
Finally, we're creating our connection string which gives us the information to connect to the database.
And I'm going to be going over this specifically a little later in the video to show you where you get
this information and how you can build connection strings to different databases.
So you can see that we are connecting to the local database.
Now notice that I don't have my computer name here.
I just have a dot.
In this context.
If you put a dot here, it means the local computer.
That way I can transport this script to another computer and it will still work as long as the local
SQL server has this database.
Here we are specifying the database that we want to connect to and also security, integrated security
which is windows security.
And we are using an Ole DB driver.
We take this connection string and give it to our connection object.
And then we open it when we open it.
That's basically the same as us having a connection here.
We've opened the connection.
Next we're going to open our record set and we're giving it our SQL command.
We're giving it the connection it should use.
And then this is a local cursor.
And when the open command executes record set holds the results at this point.
So we're just printing out the battery code database customers tables.
And then we are using the record count property of the record set to tell us how many records we have.
And we are using the record set fields count property to tell us how many fields we have, and we're
just using my runtime trace to print that out into our diagnostic object.
Next, we look and make sure that we have some records.
So as long as the record count property is greater than zero, we're going to move to the first record.
We're going to create a couple of indexes so that we can loop through our fields and loop through our
records and print them all out.
The first thing that we want to do is print out our column names, and we always start at zero on these
connections and go to the fields count minus one.
So if you've got ten fields are going to be numbered zero through nine.
And we're just going to print out the name property of our fields, and we'll just put a comma after
each.
Next we're going to loop through each record that we have.
So we'll use our R index.
We're going to start at zero.
Go to our record count minus one.
And then for each record we're going to loop through our column indexes.
And we're going to print out the value this time.
Once we've printed out that line we're going to give it another carriage return line feed just so that
it moves down to the next line in the debug window.
That way, not everything is printing out on the same line, and we're going to move to the next record.
And so we're going to do this until we reach the last record.
And then at that point we're going to exit the loop.
Now if there were no records here, then it would have just printed out selection return, no fields.
So now we have all our information on the screen printed out.
And then finally we need to close our connection and our record set.
And then we just set these to nothing.
And this just frees up the memory.
So now I'll just click okay.
And then now let's run it again.
And you can see it does exactly that.
This is the line that prints out how many records and how many fields.
This is a line that prints out our field names.
And then this is our loop that for each record prints out each field of this.
In the next assignment, you will recreate this script in your C project and get a feel for how to use
the Ada objects in VB script.
See you in the next assignment.