Michael's meanderings...

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

In yesterday's post http://theessentialexchange.com/blogs/michael/archive/2008/01/22/DML-with-PS-_2D00_-Part-1.aspx, I promised that we would talk more about DML with PowerShell.

In writing resilient code, the database layer has to be prepared to deal with NULL, invalid variables, missing variables, etc. PowerShell's NULL experience is pretty decent, but like any language, it has it's quirks and has to be specfically handled for the difference between variable types.

Thankfully, the "normal" sets of variables that you use in a database are pretty small. For my use, I got by with BOOLEAN, INTEGER, VARCHAR, and DATE.

In every case, before I create a DML statement, I evaluate the variables to be used in the DML statement. First, I determine if the value of the variable is null. If not, I cast the variable to the variable type I need. Then, I convert that variable to the format required for the particular database engine.

The dbInt routine is a good example:

#
# dbInt
#
# This routine ensures that a integer/number variable is properly returned. If
# the value is zero or NULL then a zero is returned.
# 
# Note that some database engines do not support a NULLable integer.
#
function dbInt($i)
{
	if ($i)
	{
		$ii = $i -as [int]
		$result = $ii.ToString()
	}
	else
	{
		$result = "0"
	}

	log "dbInt:" ("result=" + $result)

	return $result
}

I start by checking to see whether the variable has a value. In this case, $i might not return true for two reasons. It could either have a value of zero, or it could be NULL. Some database engines do not support having a simple type as NULL. In that case dbInt simply returns a value of zero. Note that the return value is a string.

If the parameter has a value, the parameter is cast to an integer using the PowerShell -as operator and then the ToString() method is executed against the resultant value and the string value is returned.

Finally, the result of the routine is written to a log file, and the result is returned to the caller.

The rest of the routines are below. Note that the dbDate routine is required to treat each database engine differently, as each requires different formating to store a date in the database! Also note that the dbText routine handles quoting properly in order to guard against certain SQL injection attacks.

In the next (final) post in this short series, I'll show how to use these routines in creating "safe DML".

#
# dbDate
#
# This routine ensures that a date variable is formatted properly
# for the database platform being utilized, and if the variable is
# null, that a NULL is returned.
#
function dbDate($d)
{
	if ($d)
	{
		$date = $d -as [DateTime]
		switch ($dbChoice)
		{
			$dbAccess
			{
				$result = "#" + $date.ToString() + "#"
			}
			$dbMSSQL
			{
				$result = "'" + $date.ToString() + "'"
			}
			$dbMySQL
			{
				## MySQL demands a certain date-format
				$result = "'" + $date.ToString("yyyy-MM-dd HH:mm:ss") + "'"
			}
			Default
			{
				throw ("Bad dbChoice value $dbChoice in dbDate")
			}
		}
	}
	else
	{
		$result = "NULL"
	}

	log "dbDate:" ("dbChoice=" + $dbChoice + ", result='" + $result + "'")

	return $result
}

#
# dbText
# 
# This routine ensures that a text/string/varchar variable is properly quoted
# or a NULL is returned for an empty string.
#
# Note that some database platforms do not support a zero length string.
#
function dbText($s)
{
	if ($s)
	{
		$str = $s -as [string]
		if ($str.Length -gt 0)
		{
			$result = "'" + $str.Replace("'", "''") + "'"
		}
		else
		{
			$result = "NULL"
		}
	else
	{
		$result = "NULL"
	}

	log "dbText:" ("result=" + $result)

	return $result
}

#
# dbBool
#
# This routine ensures that a boolean/yes-no variable is properly returned. If
# the value is $false or NULL then False is returned - otherwise True.
#
function dbBool($b)
{
	if ($b)
	{
		$bb = $b -as [bool]
		if ($bb)
		{
			$result = "True"
		}
		else
		{
			$result = "False"
		}
	}
	else
	{
		$result = "False"
	}

	log "dbBool:" ("result=" + $result)

	return $result
}