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.
- Drop table if it exists
- Create a new table
- Create a index for the table
- Insert data into the table
- Update data in the table
- Select data from the table
- Drop view if it exists
- Create a new view
- Delete one row of data
- 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.