Multi-platform Data Manipulation Language (DML) With PowerShell, Part 3

In Part 1 of this mini-series I presented the beginnings of a database layer supporting three different database architectures, that could easily be extended to handle more database engines. In Part 2 we built on Part 1 and discussed variable handling techniques.

In this article, Part 3, we'll build on Parts 1 and 2 to display techniques for searching, creating, modifying, and deleting data using this database layer.

First, while we haven't covered DDL - Data Definition Language - the part of SQL that deals with the creation of databases, tables, indexes, etc. - we need at least one table with which to practice. Here are the three sets of DDL that will create our practice table for each interesting platform. You'll note that all three are actually quite similar, but every single one is a little different. There are also some differences in how Access specifically handles NULLs; but that does not come into play in this example.

# create database and table for MS-SQL Server
CREATE DATABASE [SampleDB]
GO
USE [SampleDB]
GO
CREATE TABLE [Users] (
  [ID] int NOT NULL IDENTITY(1,1),
  [Name] varchar(255) default NULL,
  [Email] varchar(255) default NULL,
  [Approved] bit NOT NULL,
  PRIMARY KEY  ([ID])
)
GO
#
# now some test records
#
INSERT INTO [Users] ([Name], [Email], [Approved]) VALUES ('Michael B. Smith', 'michael@TheEssentialExchange.com', 1)
INSERT INTO [Users] ([Name], [Email], [Approved]) VALUES ('John C. Doe',      'john@cvilleswings.com',            0)
INSERT INTO [Users] ([Name], [Email], [Approved]) VALUES ('Jane E. Doe',      'jane@msmithome.com',               1)
GO

# create database and table for mysql
CREATE DATABASE `SampleDB`;
USE `SampleDB`;
CREATE TABLE `Users` (
  `ID` int NOT NULL auto_increment,
  `Name` varchar(255) default NULL,
  `Email` varchar(255) default NULL,
  `Approved` boolean NOT NULL,
  PRIMARY KEY  (`ID`)
);
#
# now some test records
#
INSERT INTO `Users` (`Name`, `Email`, `Approved`) VALUES ('Michael B. Smith', 'michael@TheEssentialExchange.com', TRUE);
INSERT INTO `Users` (`Name`, `Email`, `Approved`) VALUES ('John C. Doe',      'john@cvilleswings.com',            FALSE);
INSERT INTO `Users` (`Name`, `Email`, `Approved`) VALUES ('Jane E. Doe',      'jane@msmithome.com',               TRUE);

# create database and table for ms-access
# Access doesn't recognize "create database" or "use"
# you must do them manually or via ADO.
#
# CREATE DATABASE [SampleDB];
# USE [SampleDB];
#
# create table
#
CREATE TABLE [Users] (
  [ID] counter NOT NULL ,
  [Name] varchar(255),
  [Email] varchar(255),
  [Approved] bit NOT NULL,
  PRIMARY KEY  ([ID])
)
# 
# now some test records
#
INSERT INTO [Users] ([Name], [Email], [Approved]) VALUES ('Michael B. Smith', 'michael@TheEssentialExchange.com', TRUE)
INSERT INTO [Users] ([Name], [Email], [Approved]) VALUES ('John C. Doe',      'john@cvilleswings.com',            FALSE)
INSERT INTO [Users] ([Name], [Email], [Approved]) VALUES ('Jane E. Doe',      'jane@msmithome.com',               TRUE)

For use of the database layer, in this article I showed using global variables to maintain database state. I continue using that mechanism for modification of values in each table. For each table, I establish a set of global variables. To wit:

$global:SampleUsersID       = $null
$global:SampleUsersName     = $null
$global:SampleUsersEmail    = $null
$global:SampleUsersApproved = $null

Now, each time I want to transfer a record from the Users table or to the Users table, the code will go through the global variables. Let's take a look at the code that retrieves a record first.

function dbTransferUsersToGlobal($result)
{
	$global:SampleUsersID       = $result.Item("ID")
	$global:SampleUsersName     = $result.Item("Name")
	$global:SampleUsersEmail    = $result.Item("Email")
	$global:SampleUsersApproved = $result.Item("Approved")
}

The function dbTransferUsersToGlobal will be called after a dbExecute routine has been called. For example, to display all of the records in the Users table, we would execute this code:

function dbDisplayAllUsers
{
	$result = dbExecute ("SELECT * FROM " + (dbQuote "Users"))
	if (!$result)
	{
		"The query returned no records from the Users table."
	}
	else
	{
		if ($result.Count -gt 1)
		{
			foreach ($rec in $result)
			{
				dbTransferUsersToGlobal $rec
				"ID=$SampleUsersID Name=$SampleUsersName Email=$SampleUsersEmail Approved=$SampleUsersApproved"
			}
		}
		else
		{
			dbTransferUsersToGlobal $result
			"ID=$SampleUsersID Name=$SampleUsersName Email=$SampleUsersEmail Approved=$SampleUsersApproved"
		}
	}
}

dbOpen
dbDisplayAllUsers
dbClose

Executing queries is just that simple. Obviously, for efficiencies sake, you would open the database at the beginning of a program and close it at the end of a program, not do an open and close for each query. You will note some minor differences in the results output by the database engines. Both MS-Access and MS-SQL Server treat the boolean values as booleans, and output 'TRUE' or 'FALSE' for the [Approved] column. However, MySQL will output '0' for 'False' and '1' for 'True'. I find this most interesting, as MySQL supports using TRUE and FALSE in the INSERT statement whereas MS-SQL does not. (And in one of the rare cases where MS-Access has a feature that MS-SQL does not, MS-Access allows it as well.)

The mechanisms we've established so far for accessing the ODBC layer of the database allow us fine grained control of the results from the queries. This can come in quite handy. Of course, the power of SQL comes into play when we can create records and when we can make conditional queries. To create a record, we follow the same ideas as we've seen so far. For example, see this routine:

function dbCreateUsers
{
	# we depend on the auto-increment functionality to set the ID
	# variable automagically
	$q  = "INSERT INTO " + (dbQuote "Users") + " ("
	$q += (dbQuote "Name") + ", " + (dbQuote "Email") + ", " + (dbQuote "Approved")

	$q += ") VALUES ("

	$q += (dbText $SampleUsersName    ) + ", "
	$q += (dbText $SampleUsersEmail   ) + ", "
	$q += (dbBool $SampleUsersApproved) + ")"

	#log "dbCreateUsers:" ("query='" + $q + "'")

	$result = dbExecuteNonQuery $q
	if ($result -lt 0)
	{
		throw ("Failed creating Users record.")
	}

	return $result
}

dbOpen
$global:SampleUsersApproved=0
$global:SampleUsersEmail='jack@example.com'
$global:SampleUsersName='Jack Spratt'
dbCreateUsers
dbClose

Deleting a record is almost exactly the same. However, unless we intend to delete all of the records in a table then we must specify a condition to be satisfied to delete the record. Since the ID variable is the primary key of the table, it is the most efficient value to use. However, you can use any column in a table for a deletion condition. However, if you are going to commonly use a particular column for searching or deletion, then it should be added into an index and that index used to speed searching.

Here is an example routine for executing a deletion.

function dbDeleteUsers([int]$id)
{
	$q  = "DELETE FROM " + (dbQuote "Users") + " "
	$q += "WHERE " + (dbQuote "ID") + "=" + (dbInt $id)

	#log "dbDeleteUsers:" ("query='" + $q + "'")

	$result = dbExecuteNonQuery $q
	if ($result -ne 1)
	{
		throw ("dbDeleteUsers failed. result = $result")
	}

	return $result
}

dbOpen
# delete the "Jack Spratt" record
dbDeleteUsers 4
dbClose

And finally, last but certainly not least, we need to have a way to update an existing Users record. The same issues mentioned before regarding use of the ID column apply to updates as well as deletes. If you have been following along with these examples, then the ID value for "John C. Doe" is 2. But we've just discovered that his middle initial is "A." So, we execute the code below to correct this problem.

function dbUpdateUsers([int]$id)
{
	$q  = "UPDATE " + (dbQuote "Users") + " SET "

	$q += (dbQuote "Name"    ) + " = " + (dbText $SampleUsersName)     + ", "
	$q += (dbQuote "Email"   ) + " = " + (dbText $SampleUsersEmail)    + ", "
	$q += (dbQuote "Approved") + " = " + (dbBool $SampleUsersApproved) + " "

	$q += "WHERE " + (dbQuote "ID") + "=" + (dbInt $id)

	#log "dbUpdateUsers:" ("query='" + $q + "'")

	$result = dbExecuteNonQuery $q
	if ($result -lt 0)
	{
		throw ("Failed updating Users record. result = $result")
	}

	return $result
}

dbOpen
$result = dbExecute ("Select * from " + (dbQuote "Users") + " WHERE " + (dbQuote "ID") + "=2")
dbTransferUsersToGlobal($result) $global:SampleUsersName = "John A. Doe" dbUpdateUsers 2 dbDisplayAllUsers dbClose

I hope you've enjoyed this introduction to using PowerShell for cross-platform data access. If there are any other topics that you would like me to cover, please leave me a message on the blog, or shoot me an e-mail directly at: michael at TheEssentialExchange dot com. Thanks for reading!

Published Sunday, January 27, 2008 12:13 PM by michael
Filed under: ,

Comments

Monday, January 28, 2008 6:05 AM by Pages tagged "conditional"

# Pages tagged "conditional"

Pingback from  Pages tagged "conditional"

# http://theessentialexchange.com/blogs/michael/archive/2008/01/27/DML-with-PS_2C00_-Part-3.aspx