# encoding: ascii
# api: powershell
# title: Create-Mdb.ps1
# description: A Script that demonstrates how to create a Microsoft Access database in powershell without Microsoft Access installed. It works in 32 and 64 bit instances of powershell.
# version: 0.1
# type: script
# author: Justin Dearing
# license: MITL
# function: Create-MDB
# x-poshcode-id: 2928
# x-archived: 2012-02-02T06:26:04
# x-published: 2012-08-25T04:17:00
#
#
# Copyright (c) 2011 Justin Dearing <zippy1981@gmail.com>
#
# Permission is hereby granted, free of charge, to any person obtaining a copy
# of this software and associated documentation files (the "Software"), to deal
# in the Software without restriction, including without limitation the rights
# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
# copies of the Software, and to permit persons to whom the Software is
# furnished to do so, subject to the following conditions:

# The above copyright notice and this permission notice shall be included in
# all copies or substantial portions of the Software.

# The authoritative version of this script lives at: https://gist.github.com/1166670


#TODO: Get The PInvoke calls to actually use these ENUMS.
Add-Type -TypeDefinition @"
namespace PInvoke {
	public enum ODBC_Constants {
		ODBC_ADD_DSN = 1,
		ODBC_CONFIG_DSN,
		ODBC_REMOVE_DSN,
		ODBC_ADD_SYS_DSN,
		ODBC_CONFIG_SYS_DSN,
		ODBC_REMOVE_SYS_DSN,
		ODBC_REMOVE_DEFAULT_DSN,
	};

	public enum SQL_RETURN_CODE {
		SQL_ERROR = -1,
		SQL_INVALID_HANDLE = -2,
		SQL_SUCCESS = 0,
		SQL_SUCCESS_WITH_INFO = 1,
		SQL_STILL_EXECUTING = 2,
		SQL_NEED_DATA = 99,
		SQL_NO_DATA = 100
	}
}
"@;

$signature = @'
[DllImport("ODBCCP32.DLL",CharSet=CharSet.Unicode, SetLastError=true)]
public static extern int SQLConfigDataSource 
	(int hwndParent, int fRequest, string lpszDriver, string lpszAttributes);

[DllImport("odbccp32", CharSet=CharSet.Auto)]
public static extern int SQLInstallerError(int iError, ref int pfErrorCode, StringBuilder lpszErrorMsg, int cbErrorMsgMax, ref int pcbErrorMsg);
'@;

Add-Type -MemberDefinition $signature -Name Win32Utils -Namespace PInvoke -Using PInvoke,System.Text;

Function Create-MDB ([string] $fileName, [switch] $DeleteIfExists = $false) {
	# We need to pass the full path of the file to SQLConfigDataSource(). Relative paths will fail.
	$fileName = [System.IO.Path]::GetFullPath($fileName);
	if ($DeleteIfExists -and (Test-Path $fileName)) {
		Remove-Item $fileName;
	}
	[string] $attrs = [string]::Format("CREATE_DB=`"{0}`" General`0", $fileName);
	# For 32 bit processes we use the older ODBCJT32.dll driver
	[string] $driver = 'Microsoft Access Driver (*.mdb)';
	# There is no 64 bit version of this driver so we use ACEODBC.dll
	# This requires the Microsoft Access Database Engine 2010 Redistributable
	# http://www.microsoft.com/download/en/details.aspx?id=13255
	if ([IntPtr]::Size -eq 8) { 
		$driver = 'Microsoft Access Driver (*.mdb, *.accdb)';
	}
	# TODO: Interogate the registry (HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI) to search for which driver to use.
	# TODO: If no driver found use the 64 bit ones
	[int] $retCode = [PInvoke.Win32Utils]::SQLConfigDataSource(
		0, [PInvoke.ODBC_Constants]::ODBC_ADD_DSN, 
		$driver, $attrs);
	if ($retCode -eq 0) {
		[int] $errorCode = 0 ;
		[int]  $resizeErrorMesg = 0 ;
		$sbError = New-Object System.Text.StringBuilder(512);
	[PInvoke.Win32Utils]::SQLInstallerError(1, [ref] $errorCode, $sbError, $sbError.MaxCapacity, [ref] $resizeErrorMesg);
	if ($sbError.ToString() -eq 'Component not found in the registry') {
		$sbError = New-Object System.Text.StringBuilder(512);
		if ([intptr]::Size -eq 8) { $sbError.Append("Their appears to be no 64 bit MS Access Driver installed. Please install a 64 bit access driver or run this script from a 32 bit powershell instance"); }
		if ([intptr]::Size -eq 4) { $sbError.Append("Their appears to be no 32 bit MS Access Driver installed. Please install a 32 bit access driver or run this script from a 64 bit powershell instance"); }
		}
		throw New-Object ApplicationException([string]::Format("Cannot create file: {0}. Error: {1}", $fileName, $sbError));
	}
}


Create-Mdb ".\foo.mdb" -DeleteIfExists
