Working with databases

Most Commercial off-the-self (COTS) systems read and write data to a relational database such as SQL Server, Oracle or DB2.  Canned reports and data interfaces included with the COTS system sometimes have limited features that do not fully meet the business requirements.  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).  I have encapsulating the logic for working with a database, via Active Data Objects (ADO), into a abstract class.

The ADO Connection class has the following properties and methods.

  • OpenDatabase – Open a connection to the database
  • CloseDatabase – Close the connection to the database
  • OpenRecSet – Open a recordset given a SELECT statement
  • CloseRecSet – Close the given recordset
  • ReadRecSet – Read data from recordset into an value array
  • ExecNonQuery – Execute a SQL statement / return rows effected
  • ExecScalar – Execute a SQL statement / return specified field
  • WriteRecSet – Given arrays to describe fields and values, write to the database.
  • REC – the number of records read or written.
  • EOF – a property that reflects if we are at the end of file.

Since ADO works with many data sources, I choose a MS Access database as the target of the test program.  We continue work with the first five presidents dataset.

The following actions are carried out in the test program to give you a good idea of what can be functionally done.

  1. Drop table if it exists
  2. Create a new table
  3. Create a index for the table
  4. Insert data into the table
  5. Update data in the table
  6. Select data from the table
  7. Drop view if it exists
  8. Create a new view
  9. Delete one row of data
  10. Select data from the view

The table below has starter code that you can use in your next Visual Basic (VB) Script.

mod-ado-conn.vbs ADO Connection Class
tst-ado-conn.vbs Database Sample Program
ado-rw-output.txt Sample Program Output
ado-rw.cmd Run Sample Program
myado.mdb MS Access Sample Database

 

 

 

 

To recap this chat, relational databases are used all the time by both software systems.    The ability to read/write data from the system allows you to provide solutions to business problems that users encounter.  This in turn makes you more valuable to the organization you work for.

Related posts

2 Thoughts to “Working with databases”

  1. Wow, superb blog layout! How long have you been blogging for? you made blogging look easy. The overall look of your website is excellent, let alone the content!

  2. Loving the information on this internet site , you have done outstanding job on the posts .

Leave a Comment