{"id":296,"date":"2011-06-09T17:06:55","date_gmt":"2011-06-09T17:06:55","guid":{"rendered":"http:\/\/craftydba.com\/?p=296"},"modified":"2024-02-17T14:26:16","modified_gmt":"2024-02-17T14:26:16","slug":"working-with-databases","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=296","title":{"rendered":"Working with databases"},"content":{"rendered":"<p>Most Commercial off-the-self (<a href=\"http:\/\/en.wikipedia.org\/wiki\/Commercial_off-the-shelf\">COTS<\/a>) systems read and write data to a relational database such as <a href=\"http:\/\/en.wikipedia.org\/wiki\/Microsoft_SQL_Server\">SQL Server<\/a>, <a href=\"http:\/\/en.wikipedia.org\/wiki\/Oracle_database\">Oracle<\/a> or <a href=\"http:\/\/en.wikipedia.org\/wiki\/IBM_DB2\">DB2<\/a>.\u00a0 Canned reports and data interfaces included with the COTS system sometimes have limited features that do not fully meet the business requirements.\u00a0 That is where you, the software developer, can come to the rescue by providing a custom work-around solution.<\/p>\n<p>Actions against the database can be expressed in the data definition language (<a href=\"http:\/\/en.wikipedia.org\/wiki\/Data_Definition_Language\">DDL<\/a>), data control language (<a href=\"http:\/\/en.wikipedia.org\/wiki\/Data_Control_Language\">DCL<\/a>) or data manipulation language (<a href=\"http:\/\/en.wikipedia.org\/wiki\/Data_Manipulation_Language\">DML<\/a>).\u00a0 I have encapsulating the logic for working with a database, via Active Data Objects (ADO), into a abstract class.<\/p>\n<p>The ADO Connection class has the following properties and methods.<\/p>\n<ul>\n<li>OpenDatabase &#8211; Open a connection to the database<\/li>\n<li>CloseDatabase &#8211; Close the connection to the database<\/li>\n<li>OpenRecSet &#8211; Open a recordset given a SELECT statement<\/li>\n<li>CloseRecSet &#8211; Close the given recordset<\/li>\n<li>ReadRecSet &#8211; Read data from recordset into an value array<\/li>\n<li>ExecNonQuery &#8211; Execute a SQL statement \/ return rows effected<\/li>\n<li>ExecScalar &#8211; Execute a SQL statement \/ return specified field<\/li>\n<li>WriteRecSet &#8211; Given arrays to describe fields and values, write to the database.<\/li>\n<\/ul>\n<ul>\n<li>REC &#8211; the number of records read or written.<\/li>\n<li>EOF &#8211; a property that reflects if we are at the end of file.<\/li>\n<\/ul>\n<p>Since ADO works with many data sources, I choose a <a href=\"http:\/\/en.wikipedia.org\/wiki\/Ms_access\">MS Access<\/a> database as the target of the test program.\u00a0 We continue work with the first five presidents dataset.<\/p>\n<p>The following actions are carried out in the test program to give you a good idea of what can be functionally done.<\/p>\n<ol>\n<li>Drop table if it exists<\/li>\n<li>Create a new table<\/li>\n<li>Create a index for the table<\/li>\n<li>Insert data into the table<\/li>\n<li>Update data in the table<\/li>\n<li>Select data from the table<\/li>\n<li>Drop view if it exists<\/li>\n<li>Create a new view<\/li>\n<li>Delete one row of data<\/li>\n<li>Select data from the view<\/li>\n<\/ol>\n<p>The table below has starter code that you can use in your next Visual Basic (VB) Script.<\/p>\n<table border=\"1\" cellspacing=\"1\" cellpadding=\"1\" width=\"600\" align=\"left\">\n<tbody>\n<tr>\n<td style=\"border: thin solid gray;\"><a href=\"https:\/\/craftydba.com\/wp-content\/uploads\/2011\/06\/mod-ado-conn.vbs_.txt\">mod-ado-conn.vbs<\/a><\/td>\n<td style=\"border: thin solid gray;\">ADO Connection Class<\/td>\n<\/tr>\n<tr>\n<td style=\"border: thin solid gray;\"><a href=\"https:\/\/craftydba.com\/wp-content\/uploads\/2011\/06\/tst-ado-conn.vbs.txt\">tst-ado-conn.vbs<\/a><\/td>\n<td style=\"border: thin solid gray;\">Database Sample Program<\/td>\n<\/tr>\n<tr>\n<td style=\"border: thin solid gray;\"><a href=\"https:\/\/craftydba.com\/wp-content\/uploads\/2011\/06\/ado-rw-ouput.txt\">ado-rw-output.txt<\/a><\/td>\n<td style=\"border: thin solid gray;\">Sample Program Output<\/td>\n<\/tr>\n<tr>\n<td style=\"border: thin solid gray;\"><a href=\"https:\/\/craftydba.com\/wp-content\/uploads\/2011\/06\/ado-rw.cmd.txt\">ado-rw.cmd<\/a><\/td>\n<td style=\"border: thin solid gray;\">Run Sample Program<\/td>\n<\/tr>\n<tr>\n<td style=\"border: thin solid gray;\"><a href=\"https:\/\/craftydba.com\/wp-content\/uploads\/2011\/06\/myado.mdb\">myado.mdb<\/a><\/td>\n<td style=\"border: thin solid gray;\">MS Access Sample Database<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>\nTo recap this chat, relational databases are used all the time by both software systems.\u00a0\u00a0\u00a0 The ability to read\/write data from the system allows you to provide solutions to business problems that users encounter.\u00a0 This in turn makes you more valuable to the organization you work for.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Most Commercial off-the-self (COTS) systems read and write data to a relational database such as SQL Server, Oracle or DB2.\u00a0 Canned reports and data interfaces included with the COTS system sometimes have limited features that do not fully meet the business requirements.\u00a0 That is where you, the software developer, can come to the rescue by providing a custom work-around solution. Actions against the database can be expressed in the data definition language (DDL), data control language (DCL) or data manipulation language (DML).\u00a0 I have encapsulating the logic for working with&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7],"tags":[21,12,15,13],"class_list":["post-296","post","type-post","status-publish","format-standard","hentry","category-other","tag-databases","tag-free-code","tag-john-f-miner-iii","tag-vb-script-2"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/296","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=296"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/296\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=296"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=296"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=296"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}