1 00:00:04,970 --> 00:00:08,630 Let's take a closer look at the Edo connection object. 2 00:00:08,750 --> 00:00:13,700 We'll use this object here as a connection to the database. 3 00:00:13,700 --> 00:00:17,270 And of course we have to provide it a connection string. 4 00:00:17,300 --> 00:00:22,700 Now there are two types of database connection protocols that we're going to discuss in this course 5 00:00:22,700 --> 00:00:30,650 Open Database Connectivity or ODBC and Object Linking and Embedding database which is Ole DB in your 6 00:00:30,650 --> 00:00:30,980 class. 7 00:00:30,980 --> 00:00:31,910 Resource links. 8 00:00:31,910 --> 00:00:36,230 There's a link to a page about open database connectivity. 9 00:00:36,230 --> 00:00:42,020 And this is a low level meaning high performance database connection protocol. 10 00:00:42,020 --> 00:00:49,310 It only connects to relational databases such as SQL server, MySQL, Postgres and things like that. 11 00:00:49,310 --> 00:00:55,280 It offers high performance because it's based on the C language, and it will pretty much allow you 12 00:00:55,280 --> 00:00:58,340 to do anything you can do directly into the database. 13 00:00:58,340 --> 00:01:03,440 So ODBC is a good fit for most compliant databases. 14 00:01:03,440 --> 00:01:08,930 You have to check and see if the database you're connecting to supports ODBC, and most of them do. 15 00:01:08,960 --> 00:01:13,100 Ole DB stands for Object Linking and Embedding for databases. 16 00:01:13,250 --> 00:01:19,640 Ole is based on Com, which is Component Object Model, which is the foundation for ActiveX. 17 00:01:19,640 --> 00:01:22,820 Of course Ado is based on ActiveX. 18 00:01:22,820 --> 00:01:29,690 The difference here is that Ole DB can connect to many other types of data sources such as files, XML 19 00:01:29,690 --> 00:01:36,110 streams, non relational databases, as well as relational systems like SQL server. 20 00:01:36,260 --> 00:01:42,080 A good rule of thumb when creating a connection is to take a look at the database you're connecting 21 00:01:42,110 --> 00:01:47,270 to and see what interfaces it supports, and then you will know your options. 22 00:01:47,270 --> 00:01:53,540 Since SQL server supports both Ole DB and ODBC, either of these can be used. 23 00:01:53,540 --> 00:01:54,590 In our initial script. 24 00:01:54,590 --> 00:01:59,120 We use an Ole DB connection, as you can see from the provider here. 25 00:01:59,120 --> 00:02:01,850 But where did this connection string come from? 26 00:02:01,850 --> 00:02:07,760 Let's take a look at different ways we can configure an Ado connection using Ole DB. 27 00:02:08,450 --> 00:02:12,620 One method is to just go to connection strings.com. 28 00:02:12,620 --> 00:02:14,630 There are a lot of sites like this. 29 00:02:14,630 --> 00:02:19,610 And if you're building a connection string to a database, you can get a lot of information on this 30 00:02:19,610 --> 00:02:20,420 website. 31 00:02:20,420 --> 00:02:25,610 And so here we've got Ole DB providers, ODBC drivers and things like that. 32 00:02:25,610 --> 00:02:33,440 So if we take a look at Microsoft Ole DB driver for SQL server, then it gives you a lot of different 33 00:02:33,440 --> 00:02:36,860 options based on how you're connecting what security. 34 00:02:36,860 --> 00:02:43,490 So here is an example using the Ole DB driver where you have to put in a username and password. 35 00:02:43,490 --> 00:02:51,800 So you use this if you create your users in SQL server versus windows here this is using Windows authentication. 36 00:02:51,800 --> 00:02:59,060 You see integrated security equals SSP which is what our original script used is the exact same thing 37 00:02:59,060 --> 00:03:01,430 of saying we are a trusted connection. 38 00:03:01,430 --> 00:03:06,950 So we can actually just copy this here and then go back to our button. 39 00:03:06,950 --> 00:03:09,050 And I'm just going to paste it in here. 40 00:03:09,050 --> 00:03:11,000 We're going to copy this. 41 00:03:11,210 --> 00:03:15,830 And we just need to fill in the details of this connection string. 42 00:03:15,830 --> 00:03:18,500 So the provider is going to stay the same. 43 00:03:18,950 --> 00:03:23,270 The server is going to be the same thing as the data source. 44 00:03:23,270 --> 00:03:29,150 So we'll just copy the dot slash link and then the database. 45 00:03:29,150 --> 00:03:31,880 And this provider it's called a catalog. 46 00:03:31,880 --> 00:03:34,490 And this provider it's called a database. 47 00:03:34,490 --> 00:03:37,580 So you'll get used to the nomenclature here. 48 00:03:37,580 --> 00:03:39,890 We can just kind of pop that in there. 49 00:03:40,190 --> 00:03:43,190 And then I'm going to comment out this first one. 50 00:03:43,190 --> 00:03:46,550 And we have to end with a double quote here. 51 00:03:46,550 --> 00:03:52,880 And so if I've gotten all my arguments right here in our connection string it should use this new provider. 52 00:03:52,880 --> 00:03:59,780 We'll click okay save and go to runtime click the button still works. 53 00:03:59,780 --> 00:04:03,680 So we're just using a slightly different connection string here. 54 00:04:03,680 --> 00:04:09,320 Now there's another way that you can build an Ole DB connection string to SQL server. 55 00:04:09,320 --> 00:04:12,770 And I've provided this as a link in our class resources. 56 00:04:12,770 --> 00:04:16,400 And it's by creating a UDL file. 57 00:04:16,400 --> 00:04:19,190 Basically it is a universal data link. 58 00:04:19,190 --> 00:04:24,650 And it prompts a wizard where you can go out and fill out information about how you're connecting to 59 00:04:24,650 --> 00:04:28,700 SQL server, and it will generate the connection string for you. 60 00:04:28,700 --> 00:04:34,490 And so this link basically takes you step by step on how to do what we're about to do. 61 00:04:34,490 --> 00:04:38,720 But I'm just going to show you just go create a new text file. 62 00:04:38,720 --> 00:04:44,030 And I'm just going to create a file called my Ole DB open string. 63 00:04:44,030 --> 00:04:48,290 And then you give it the file extension UDL. 64 00:04:48,290 --> 00:04:49,010 Hit enter. 65 00:04:49,010 --> 00:04:51,020 And then just say yes to this. 66 00:04:51,020 --> 00:04:53,300 And notice the icon changes. 67 00:04:53,300 --> 00:04:55,010 When you double click this. 68 00:04:55,010 --> 00:04:58,370 It brings up a wizard to create a data link. 69 00:04:58,370 --> 00:04:58,460 And. 70 00:04:58,600 --> 00:05:04,750 People use this to test whether or not they can hit a database, but it also generates the connection 71 00:05:04,750 --> 00:05:05,200 string. 72 00:05:05,200 --> 00:05:12,370 So you don't really want to browse for your SQL server unless you have to, because it just takes so 73 00:05:12,370 --> 00:05:13,120 long. 74 00:05:13,450 --> 00:05:17,560 And we know that our database is just dot slash c c. 75 00:05:17,590 --> 00:05:19,870 Again, the dot means the local computer name. 76 00:05:19,870 --> 00:05:22,270 I could also put the computer name in there. 77 00:05:22,450 --> 00:05:28,090 We're going to use Windows Integrated Security and then select our database. 78 00:05:28,090 --> 00:05:30,730 As long as I got this server right it'll connect to it. 79 00:05:30,730 --> 00:05:33,580 And we want to connect to our battery code database. 80 00:05:33,580 --> 00:05:38,590 Now if you've got a database file that is not attached to SQL server yet, you can also do that. 81 00:05:38,590 --> 00:05:40,900 And we can just hit Test Connection. 82 00:05:41,050 --> 00:05:42,250 That looks good. 83 00:05:42,250 --> 00:05:48,160 And just looking at the provider tab here, it's using the Ole DB provider for SQL server. 84 00:05:48,160 --> 00:05:51,790 You can see that we have a lot of other Ole DB providers and drivers. 85 00:05:51,790 --> 00:05:54,970 So I could choose any of these others. 86 00:05:54,970 --> 00:05:59,890 Also, you'll notice there is a wink DB provider for archives that's tag logging. 87 00:05:59,890 --> 00:06:04,120 So a little later in the class we're going to learn how we can pull information directly out of our 88 00:06:04,120 --> 00:06:05,500 tag logging archives. 89 00:06:05,500 --> 00:06:10,810 But we're just going to leave this on our DB provider for SQL server. 90 00:06:10,810 --> 00:06:14,560 And then we'll just go over and redo the test connection that looks good. 91 00:06:14,560 --> 00:06:15,610 And we'll click okay. 92 00:06:15,610 --> 00:06:19,750 Now what you have to do is just right click and open this up. 93 00:06:19,750 --> 00:06:22,960 And I'm opening this up in notepad plus. 94 00:06:22,960 --> 00:06:24,970 Plus if you don't have this program it's free. 95 00:06:24,970 --> 00:06:26,230 I highly recommend it. 96 00:06:26,410 --> 00:06:33,490 And here this should look familiar because it generated this string for me I can copy that. 97 00:06:33,490 --> 00:06:37,630 Go back to our button script I'll just paste it down here. 98 00:06:37,720 --> 00:06:47,230 You can see that it is exactly the same string we have here SQL DB dot one, Integrated Security, SSP 99 00:06:47,260 --> 00:06:49,510 Initial Catalog, and our data source. 100 00:06:49,510 --> 00:06:53,170 So that's how I generated that connection string. 101 00:06:53,170 --> 00:06:54,790 And that's a that's a pretty good trick. 102 00:06:54,790 --> 00:06:57,130 If you don't have access to go look something up. 103 00:06:57,130 --> 00:07:03,460 So I am just going to comment this guy out and put our original one back in. 104 00:07:03,460 --> 00:07:12,250 So next let's go look at our ODBC data sources and we can go back to our connection strings. 105 00:07:12,250 --> 00:07:15,670 And I'll just go to the main thing here. 106 00:07:15,670 --> 00:07:19,570 So you can see they have connection strings to a lots of different databases. 107 00:07:19,570 --> 00:07:22,780 And we will go to SQL server. 108 00:07:22,780 --> 00:07:25,570 And there's ODBC drivers here. 109 00:07:25,570 --> 00:07:28,060 And these are just different versions. 110 00:07:28,060 --> 00:07:31,810 And we need to know which ones of these we have on our system. 111 00:07:31,810 --> 00:07:34,570 And so there's a quick way to do this. 112 00:07:34,570 --> 00:07:41,890 Just go to your windows search bar and type in ODBC and you'll see ODBC data sources 64 and 32. 113 00:07:41,890 --> 00:07:45,280 These are what you call Dsns data source names. 114 00:07:45,280 --> 00:07:48,970 And you can see that with Winrk. 115 00:07:48,970 --> 00:07:52,420 I've got data source names on different Winrk projects. 116 00:07:52,420 --> 00:07:54,160 This is what Winrk uses. 117 00:07:54,160 --> 00:07:55,900 They use 32 bit. 118 00:07:55,900 --> 00:08:02,260 But if you go to drivers go down to SQL server, you can see that I've got the SQL server version 17 119 00:08:02,260 --> 00:08:05,950 driver, the version 13 driver, the native client 11.0. 120 00:08:05,950 --> 00:08:08,200 And there's some other drivers on here. 121 00:08:08,200 --> 00:08:12,640 So this tells me which ODBC drivers that I have on this system. 122 00:08:12,640 --> 00:08:17,230 We'll just go with the latest version and click ODBC driver 17. 123 00:08:17,230 --> 00:08:21,790 And then again it's going to give you several different examples. 124 00:08:21,790 --> 00:08:24,910 And so we like using the trusted connection. 125 00:08:24,910 --> 00:08:28,570 Again you can put in integrated security equals SSP on that. 126 00:08:28,570 --> 00:08:35,080 So I am just going to copy this connection string here and go back to our script. 127 00:08:35,140 --> 00:08:38,050 And I'll go down and kind of paste it in here. 128 00:08:38,050 --> 00:08:39,640 And we're going to do the same thing. 129 00:08:39,640 --> 00:08:43,990 So I'm just going to copy my variable instantiation there. 130 00:08:43,990 --> 00:08:50,590 Here we're just putting the name of the driver in parentheses letting us know that it is a literal string. 131 00:08:50,590 --> 00:08:53,650 The server is going to be dot slash c c. 132 00:08:53,800 --> 00:08:58,900 And the way that I remember which way the slash goes is it goes parallel to the W. 133 00:08:59,230 --> 00:09:01,840 And then the database that's going to be. 134 00:09:02,820 --> 00:09:03,540 Battery. 135 00:09:04,140 --> 00:09:06,480 And then of course, we can leave. 136 00:09:06,480 --> 00:09:07,950 The trusted connection equals. 137 00:09:07,950 --> 00:09:12,480 Yes, which is the same as the integrated security spy. 138 00:09:12,480 --> 00:09:14,370 And then again I'm going to comment this out. 139 00:09:14,370 --> 00:09:17,670 So now we are using this string. 140 00:09:18,120 --> 00:09:19,500 We'll click okay. 141 00:09:20,240 --> 00:09:25,520 Save go to runtime and it's going to work with ODBC as well. 142 00:09:25,550 --> 00:09:30,170 The last connection example I want to show you is using an ODBC, DSN. 143 00:09:30,200 --> 00:09:36,050 We're going to call back up our ODBC data sources, but this time we're going to use the 32 bit ODBC 144 00:09:36,050 --> 00:09:36,770 data sources. 145 00:09:36,800 --> 00:09:40,610 This is because the VB script engine for Winc is 32 bit. 146 00:09:40,640 --> 00:09:47,480 You'll notice that all of the Winc Dsns that are used by each project are also 32 bit. 147 00:09:47,510 --> 00:09:54,470 If you try to connect a 64 bit DSN from a VB script in one cc, you'll get an architecture mismatch 148 00:09:54,470 --> 00:09:54,920 error. 149 00:09:54,920 --> 00:09:58,730 So when you're doing this example, make sure that we use 32 bit. 150 00:09:58,730 --> 00:10:00,620 So I'm just going to click add. 151 00:10:00,620 --> 00:10:06,770 And we're going to select a driver I'm going to use this generic SQL server driver here near the bottom. 152 00:10:08,420 --> 00:10:14,540 And we'll click finish and a wizard comes up, similar to the ODB example we did earlier. 153 00:10:14,540 --> 00:10:21,530 I'm going to call this my DB class DSN and just DSN example for the description. 154 00:10:22,790 --> 00:10:28,130 And then for the server, I'm just going to type it in because the browser takes too long. 155 00:10:28,190 --> 00:10:33,650 So this is just going to be ./1 cc and we'll hit next. 156 00:10:33,650 --> 00:10:36,260 We're going to use windows NT authentication. 157 00:10:36,260 --> 00:10:41,300 Make sure you have this checked because on the next screen we won't be able to see our databases. 158 00:10:41,300 --> 00:10:47,510 We'll click next change default database to Batterico and we'll hit the next button. 159 00:10:47,510 --> 00:10:49,610 And I'll hit Test Data Source. 160 00:10:49,610 --> 00:10:52,610 And it is a test completed successfully. 161 00:10:52,610 --> 00:10:54,530 So the DSN does work. 162 00:10:54,530 --> 00:10:56,000 And I'm just going to click okay. 163 00:10:56,000 --> 00:10:58,670 So now we have our DB class DSN. 164 00:10:58,670 --> 00:11:01,280 It's 32 bit talking to SQL server. 165 00:11:01,280 --> 00:11:02,930 And I'll just click okay. 166 00:11:02,930 --> 00:11:10,430 Now when we go back to our script all we have to do is put our connection string variable down and just 167 00:11:10,430 --> 00:11:16,430 type in DSN equals my DB class DSN semicolon. 168 00:11:16,430 --> 00:11:20,300 And then add your double quote there and we'll just click okay. 169 00:11:20,300 --> 00:11:22,550 We'll save and test it out. 170 00:11:22,550 --> 00:11:23,570 And it works. 171 00:11:23,570 --> 00:11:24,920 Nothing could be simpler. 172 00:11:24,920 --> 00:11:30,500 And this is a good method if you just kind of want to stay away from doing the complex strings in your 173 00:11:30,500 --> 00:11:31,190 scripts. 174 00:11:31,190 --> 00:11:35,660 And you can always go back to the DSN and change it if you need to. 175 00:11:35,660 --> 00:11:41,210 Now, of course, if you move this to another project, then you'll need to make sure you create that 176 00:11:41,210 --> 00:11:42,200 DSN. 177 00:11:43,070 --> 00:11:48,500 Coming up next, you're going to get some practice creating different types of Edo connections. 178 00:11:48,500 --> 00:11:50,900 So I'll see you in the next assignment.