1 00:00:05,410 --> 00:00:11,830 Now it's time to write our first Visual Basic script in wink that can execute SQL queries against a 2 00:00:11,830 --> 00:00:12,640 database. 3 00:00:12,640 --> 00:00:16,570 But first, let's take another look at SQL Server Management Studio. 4 00:00:17,310 --> 00:00:24,060 When we open management studio, we go through a series of steps to be able to get the data we want 5 00:00:24,060 --> 00:00:25,020 from the database. 6 00:00:25,020 --> 00:00:31,770 The first one is we are going to connect to the database engine that has our database. 7 00:00:31,770 --> 00:00:33,630 We put in the server name. 8 00:00:33,630 --> 00:00:36,240 So in this case this is a named connection. 9 00:00:36,240 --> 00:00:38,220 This is the name of my computer. 10 00:00:38,220 --> 00:00:40,110 And then slash c c. 11 00:00:40,110 --> 00:00:43,230 So this is the database server that wink installs. 12 00:00:43,230 --> 00:00:45,510 And we're using Windows Authentication. 13 00:00:45,510 --> 00:00:46,710 We'll click connect. 14 00:00:46,980 --> 00:00:52,260 The next thing that we're going to do is connect to the database that we want. 15 00:00:52,260 --> 00:00:54,510 So I'll just pick battery Co. 16 00:00:54,510 --> 00:00:58,140 And then finally we will enter in a SQL command. 17 00:00:58,140 --> 00:01:00,480 I'm going to introduce you to the top command. 18 00:01:00,480 --> 00:01:05,850 If I don't want everything from the table I can just say top and then give it a number. 19 00:01:05,850 --> 00:01:12,390 This will return the top ten records from customers and ordered by their last name and the first name. 20 00:01:12,390 --> 00:01:17,280 The third step is we're going to execute that query and see the results here. 21 00:01:17,280 --> 00:01:20,820 We've got our column names and we've got our ten records. 22 00:01:20,820 --> 00:01:24,180 If we were to change this to say 20. 23 00:01:24,910 --> 00:01:27,520 Then of course we would get 20 records. 24 00:01:27,550 --> 00:01:34,630 The reason for me showing you this is that in a script we go through these exact same steps. 25 00:01:34,630 --> 00:01:41,680 So now let's take a look at a simple Ido script that executes the same query and returns the same results. 26 00:01:42,010 --> 00:01:51,370 So I will just create a button, and the caption on this button is just going to be my first Ido script. 27 00:01:51,400 --> 00:01:57,100 Ido stands for ActiveX Data Objects, and that's how we're going to be connecting to the database. 28 00:01:57,100 --> 00:02:00,280 Then I want to format it like this button. 29 00:02:00,280 --> 00:02:05,890 So I'll use my eyedropper to copy the properties from that button and paste them into this button. 30 00:02:05,890 --> 00:02:13,840 Next I am going to go to the events on my button, and I'm going to right click and do a VBS action 31 00:02:13,840 --> 00:02:15,370 on this button. 32 00:02:15,370 --> 00:02:21,550 Now, for those of you who work with VBS and other platforms, what you're learning in this course is 33 00:02:21,550 --> 00:02:23,440 directly transferable to that. 34 00:02:23,440 --> 00:02:31,060 And so here is just a simple script that will connect to the database, execute the same query and print 35 00:02:31,060 --> 00:02:32,650 out the results in wink. 36 00:02:32,680 --> 00:02:34,720 So let's execute this real quick. 37 00:02:34,750 --> 00:02:36,160 See what the output is. 38 00:02:36,160 --> 00:02:40,300 And then we'll spend some time going through the script to see how it works. 39 00:02:40,300 --> 00:02:44,500 I'll just click okay, save and go to runtime. 40 00:02:44,500 --> 00:02:50,530 In runtime, we'll click our button and we print out the number of records and number of fields. 41 00:02:50,530 --> 00:02:53,110 And then we print out our results. 42 00:02:53,110 --> 00:02:55,390 And we're just using like a debug print. 43 00:02:55,390 --> 00:03:00,310 Once we have this script, of course we could export it to a CSV file. 44 00:03:00,310 --> 00:03:01,690 We could throw it into tags. 45 00:03:01,690 --> 00:03:03,430 We could do whatever we want. 46 00:03:03,460 --> 00:03:07,630 Now let's go take a look at the script and see how it works. 47 00:03:08,140 --> 00:03:11,350 So we'll select the button on the events tab. 48 00:03:11,350 --> 00:03:12,760 We're going to open this script. 49 00:03:12,760 --> 00:03:14,230 What is ado? 50 00:03:14,260 --> 00:03:22,060 Well if you look in your links and your resources, this first one tells us about 80 objects and collections. 51 00:03:22,060 --> 00:03:26,740 And this link also gives you access to other information about Ado. 52 00:03:26,740 --> 00:03:28,150 It tells you what it is. 53 00:03:28,150 --> 00:03:34,300 It's basically a programming model, and you can use it in various programming languages from Java, 54 00:03:34,300 --> 00:03:36,940 JavaScript as well as C plus plus. 55 00:03:36,940 --> 00:03:41,200 So the skills you're learning in this class will transfer to those other languages. 56 00:03:41,200 --> 00:03:49,120 I think of it as an API, and it allows you to get information from databases and other data sources. 57 00:03:49,120 --> 00:03:52,000 Let's go back to our objects and collections. 58 00:03:52,000 --> 00:03:58,450 So there are certain objects that we're going to use in order to connect to the database and get our 59 00:03:58,450 --> 00:03:58,990 query. 60 00:03:58,990 --> 00:04:03,790 So we have a connection object which is a connection to a database. 61 00:04:03,790 --> 00:04:07,870 That's the first thing that we do when we open SQL Management Studio. 62 00:04:08,200 --> 00:04:12,670 There is a command object that allows us to create and execute commands. 63 00:04:13,030 --> 00:04:20,920 There is a record set object that gets the results of our SQL queries that we can go through and print 64 00:04:20,920 --> 00:04:23,860 out, and there's a few other things in here. 65 00:04:23,860 --> 00:04:26,590 So a record this is a record in a table. 66 00:04:26,590 --> 00:04:30,610 There's also an error object that allows us to handle errors. 67 00:04:30,610 --> 00:04:32,230 And then we have collections. 68 00:04:32,230 --> 00:04:36,160 So each record is made up of fields or columns. 69 00:04:36,160 --> 00:04:40,390 So we have a collection of fields that we can loop through and print out information. 70 00:04:40,390 --> 00:04:45,820 Also the database objects in Ado have properties so we can manipulate the properties. 71 00:04:45,820 --> 00:04:46,390 In our script. 72 00:04:46,390 --> 00:04:50,500 You'll see we're getting the field names and the field values and things like that. 73 00:04:50,500 --> 00:04:53,260 So this is where this information is coming from. 74 00:04:53,260 --> 00:04:54,850 Let's go back to our script. 75 00:04:54,850 --> 00:05:00,580 We create a connection object and this syntax is how we create it. 76 00:05:01,560 --> 00:05:06,510 And then we also just create a record set object to get the results of our query. 77 00:05:06,510 --> 00:05:12,150 And then we're just creating our SQL command in a string variable. 78 00:05:12,180 --> 00:05:17,370 Finally, we're creating our connection string which gives us the information to connect to the database. 79 00:05:17,370 --> 00:05:23,130 And I'm going to be going over this specifically a little later in the video to show you where you get 80 00:05:23,130 --> 00:05:27,090 this information and how you can build connection strings to different databases. 81 00:05:27,090 --> 00:05:31,170 So you can see that we are connecting to the local database. 82 00:05:31,170 --> 00:05:33,480 Now notice that I don't have my computer name here. 83 00:05:33,480 --> 00:05:34,530 I just have a dot. 84 00:05:34,530 --> 00:05:35,970 In this context. 85 00:05:35,970 --> 00:05:39,120 If you put a dot here, it means the local computer. 86 00:05:39,120 --> 00:05:46,170 That way I can transport this script to another computer and it will still work as long as the local 87 00:05:46,170 --> 00:05:47,880 SQL server has this database. 88 00:05:47,880 --> 00:05:55,230 Here we are specifying the database that we want to connect to and also security, integrated security 89 00:05:55,230 --> 00:05:56,640 which is windows security. 90 00:05:56,640 --> 00:05:59,310 And we are using an Ole DB driver. 91 00:05:59,520 --> 00:06:03,480 We take this connection string and give it to our connection object. 92 00:06:03,480 --> 00:06:06,660 And then we open it when we open it. 93 00:06:06,660 --> 00:06:11,400 That's basically the same as us having a connection here. 94 00:06:11,400 --> 00:06:13,080 We've opened the connection. 95 00:06:13,080 --> 00:06:19,590 Next we're going to open our record set and we're giving it our SQL command. 96 00:06:19,590 --> 00:06:21,930 We're giving it the connection it should use. 97 00:06:21,930 --> 00:06:24,780 And then this is a local cursor. 98 00:06:24,780 --> 00:06:29,760 And when the open command executes record set holds the results at this point. 99 00:06:29,760 --> 00:06:33,660 So we're just printing out the battery code database customers tables. 100 00:06:33,660 --> 00:06:40,410 And then we are using the record count property of the record set to tell us how many records we have. 101 00:06:40,410 --> 00:06:46,890 And we are using the record set fields count property to tell us how many fields we have, and we're 102 00:06:46,890 --> 00:06:52,290 just using my runtime trace to print that out into our diagnostic object. 103 00:06:52,350 --> 00:06:55,680 Next, we look and make sure that we have some records. 104 00:06:55,680 --> 00:07:01,560 So as long as the record count property is greater than zero, we're going to move to the first record. 105 00:07:02,010 --> 00:07:07,260 We're going to create a couple of indexes so that we can loop through our fields and loop through our 106 00:07:07,260 --> 00:07:08,880 records and print them all out. 107 00:07:09,610 --> 00:07:15,400 The first thing that we want to do is print out our column names, and we always start at zero on these 108 00:07:15,400 --> 00:07:19,480 connections and go to the fields count minus one. 109 00:07:19,480 --> 00:07:23,770 So if you've got ten fields are going to be numbered zero through nine. 110 00:07:24,190 --> 00:07:31,930 And we're just going to print out the name property of our fields, and we'll just put a comma after 111 00:07:31,930 --> 00:07:32,560 each. 112 00:07:32,560 --> 00:07:36,310 Next we're going to loop through each record that we have. 113 00:07:36,310 --> 00:07:38,080 So we'll use our R index. 114 00:07:38,080 --> 00:07:39,700 We're going to start at zero. 115 00:07:39,730 --> 00:07:42,100 Go to our record count minus one. 116 00:07:42,100 --> 00:07:47,440 And then for each record we're going to loop through our column indexes. 117 00:07:47,440 --> 00:07:51,160 And we're going to print out the value this time. 118 00:07:51,160 --> 00:07:56,770 Once we've printed out that line we're going to give it another carriage return line feed just so that 119 00:07:56,770 --> 00:08:00,100 it moves down to the next line in the debug window. 120 00:08:00,100 --> 00:08:04,720 That way, not everything is printing out on the same line, and we're going to move to the next record. 121 00:08:04,720 --> 00:08:09,700 And so we're going to do this until we reach the last record. 122 00:08:09,700 --> 00:08:13,330 And then at that point we're going to exit the loop. 123 00:08:13,330 --> 00:08:19,720 Now if there were no records here, then it would have just printed out selection return, no fields. 124 00:08:19,720 --> 00:08:23,320 So now we have all our information on the screen printed out. 125 00:08:23,320 --> 00:08:27,670 And then finally we need to close our connection and our record set. 126 00:08:27,670 --> 00:08:29,320 And then we just set these to nothing. 127 00:08:29,320 --> 00:08:31,570 And this just frees up the memory. 128 00:08:31,570 --> 00:08:33,640 So now I'll just click okay. 129 00:08:33,640 --> 00:08:36,190 And then now let's run it again. 130 00:08:36,190 --> 00:08:38,680 And you can see it does exactly that. 131 00:08:38,680 --> 00:08:43,360 This is the line that prints out how many records and how many fields. 132 00:08:43,360 --> 00:08:46,360 This is a line that prints out our field names. 133 00:08:46,360 --> 00:08:52,300 And then this is our loop that for each record prints out each field of this. 134 00:08:52,540 --> 00:08:58,660 In the next assignment, you will recreate this script in your C project and get a feel for how to use 135 00:08:58,660 --> 00:09:01,000 the Ada objects in VB script. 136 00:09:01,000 --> 00:09:03,010 See you in the next assignment.