I don't "do Oracle", but I have a regularized method of dealing with database platforms. Adding in Oracle would be trivial. To wit:
# # dbRoutines.ps1 # # Access 2007 Reserved Words: # http://office.microsoft.com/en-us/access/HA100306431033.aspx # # SQL Server 2000 Reserved Words: # http://msdn2.microsoft.com/en-us/library/aa238507(SQL.80).aspx # # SQL Server 2005 Reserved Words: # http://msdn2.microsoft.com/en-us/library/ms189822.aspx # # MySQL AB MySQL 5.0 Reserved Words: # http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html # # dbConstants.ps1 # Set-Variable dbAccess -force -option ReadOnly -value 1 Set-Variable dbMSSQL -force -option ReadOnly -value 2 Set-Variable dbMySQL -force -option ReadOnly -value 3 # # Microsoft Office Access Example # #Set-Variable dbChoice -force -option ReadOnly -value $dbAccess #Set-Variable dbName -force -option ReadOnly -value PowerMLM.mdb #Set-Variable dbSource -force -option ReadOnly -value Not-Applicable #Set-Variable dbUser -force -option ReadOnly -value Not-Applicable #Set-Variable dbPass -force -option ReadOnly -value Not-Applicable # # Microsoft SQL Server Example # #Set-Variable dbChoice -force -option ReadOnly -value $dbMSSQL #Set-Variable dbName -force -option ReadOnly -value PowerMLM #Set-Variable dbSource -force -option ReadOnly -value .\MSSMLBIZ #Set-Variable dbUser -force -option ReadOnly -value Not-Applicable #Set-Variable dbPass -force -option ReadOnly -value Not-Applicable # # MySQL Example # Set-Variable dbChoice -force -option ReadOnly -value $dbMySQL Set-Variable dbName -force -option ReadOnly -value sampledb Set-Variable dbSource -force -option ReadOnly -value localhost Set-Variable dbUser -force -option ReadOnly -value root Set-Variable dbPass -force -option ReadOnly -value password # # dbQuote # # This routine ensures that a table or column name is properly # quoted for the database platform being utilized. Unlike the # column values, $word may not be NULL. # function dbQuote([string]$word) { if (!$word) { throw ("dbQuote: table/column name may not be null.") } # square brackets work for MS-SQL and MS-Access $start = '[' $end = ']' if ($dbChoice -eq $dbMySQL) { $start = $end = '`' } $result = $start + $word + $end log "dbQuote:" ("result='" + $result + "'") return $result } # # 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 } # # 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 } # # 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" } if ($dbChoice -eq $dbMSSQL) { if ($result -eq "True") { $result = "1" } else { $result = "0" } } log "dbBool:" ("result=" + $result) return $result } $global:MLMconnection = $null $global:MLMcommand = $null $global:MLMresult = $null function dbOpen { $global:MLMconnection = New-Object System.Data.Odbc.OdbcConnection $global:MLMcommand = New-Object System.Data.Odbc.OdbcCommand $global:MLMcommand.Connection = $global:MLMconnection switch ($dbChoice) { $dbAccess { $cStr = "Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=$pwd/$dbName;" } $dbMSSQL { $cStr = "Driver={SQL Server};Server=$dbSource;Database=$dbName;" if ($dbUser -eq "Not-Applicable") { $cStr += "Integrated Security=SSPI;" } else { $cStr += "UID=$dbUser;PWD=$dbPass;" } } $dbMySQL { $cStr = "Driver={MySQL ODBC 3.51 Driver};SERVER=$dbSource;DATABASE=$dbName;" if ($dbUser -ne "Not-Applicable") { $cStr += "UID=$dbUser;PWD=$dbPass;" } } Default { throw ("Unknown value of dbChoice $dbChoice can't open db $dbName") } } log "dbOpen:" ("ConnectionString='" + $cStr + "'") $global:MLMconnection.ConnectionString = $cStr $global:MLMconnection.Open() if ($global:MLMconnection.State -ne [System.Data.ConnectionState]::Open) { throw ("Cannot open $dbName") } } function dbPrepareResult { if ($global:MLMresult) { if ($global:MLMresult.Gettype().Name -ne "Int32") { $global:MLMresult.Close() } $global:MLMresult = $null } } function dbClose { dbPrepareResult if ($global:MLMcommand) { $global:MLMcommand.Cancel() $global:MLMcommand = $null } if ($global:MLMconnection) { if ($global:MLMconnection.State -ne [System.Data.ConnectionState]::Closed) { $global:MLMconnection.Close() } $global:MLMconnection = $null } } function dbExecute([string]$command) { dbPrepareResult if (!$global:MLMcommand) { dbOpen } log "dbExecute:" ("command='" + $command + "'") $global:MLMcommand.CommandType = [System.Data.CommandType]::Text $global:MLMcommand.CommandText = $command $global:MLMresult = $global:MLMcommand.ExecuteReader() if ($MLMresult) { log "dbExecute: " ("result type=" + $MLMresult.gettype()) } else { log "dbExecute: " "null result" } return $global:MLMresult } function dbExecuteNonQuery([string]$command) { dbPrepareResult if (!$global:MLMcommand) { dbOpen } log "dbExecuteNonQuery:" ("command='" + $command + "'") $global:MLMcommand.CommandType = [System.Data.CommandType]::Text $global:MLMcommand.CommandText = $command $global:MLMresult = $global:MLMcommand.ExecuteNonQuery() if ($MLMresult) { log "dbExecuteNonQuery: " ("result type=" + $MLMresult.gettype()) } else { log "dbExecuteNonQuery: " "null result" } return $global:MLMresult } ### ### samples of using the above routines ### function dbGetServerOptions { $result = dbExecute ("SELECT * FROM " + (dbQuote "ServerOptions")) if (!$result) { throw ("Failed to retrieve ServerOptions record.") } if ($result.Count -gt 1) { throw ("Database is corrupt. There is more than one ServerOptions record.") } $global:MLMid = $result.Item("ID") $global:MLMserver = $result.Item("SMTPserver") $global:MLMport = $result.Item("SMTPport") $global:MLMuser = $result.Item("SMTPuser") $global:MLMpassword = $result.Item("SMTPpassword") $global:MLMcycle = $result.Item("CycleTime") $global:MLMlogfile = $result.Item("Logfile") $global:MLMmaxsize = $result.Item("MaximumMessageSize") $global:MLMconfirmDays = $result.Item("ConfirmDays") return $result } function dbSetServerOptions { $result = dbExecuteNonQuery ("Delete from " + (dbQuote "ServerOptions")) $q = "INSERT INTO " + (dbQuote "ServerOptions") + " (" $q += (dbQuote "SMTPserver" ) + ", " $q += (dbQuote "SMTPport" ) + ", " $q += (dbQuote "SMTPuser" ) + ", " $q += (dbQuote "SMTPpassword" ) + ", " $q += (dbQuote "CycleTime" ) + ", " $q += (dbQuote "LogFile" ) + ", " $q += (dbQuote "ConfirmDays" ) + ", " $q += (dbQuote "MaximumMessageSize") + ") " $q += "VALUES (" $q += (dbText $MLMserver) + ", " $q += (dbInt $MLMport) + ", " $q += (dbText $MLMuser) + ", " $q += (dbText $MLMpasword) + ", " $q += (dbInt $MLMcycle) + ", " $q += (dbText $MLMlogfile) + ", " $q += (dbInt $MLMconfirmDays) + ", " $q += (dbInt $MLMmaxsize) $q += ")" log "dbSetServerOptions:" ("query='" + $q + "'") $result = dbExecuteNonQuery $q if ($result -lt 0) { throw ("Failed setting ServerOptions record.") } # we must always know the ID of the ServerOptions record return dbGetServerOptions } function dbDeleteServerOptions { $result = dbExecuteNonQuery ("Delete from " + (dbQuote "ServerOptions")) if ($result -lt 0) { throw ("Failed deleting ServerOptions record.") } return $result } function dbUpdateServeroptions { $q = "UPDATE " + (dbQuote "ServerOptions") + " SET " $q += (dbQuote "SMTPserver" ) + " = " + (dbText $MLMserver) + ", " $q += (dbQuote "SMTPport" ) + " = " + (dbInt $MLMport) + ", " $q += (dbQuote "SMTPuser" ) + " = " + (dbText $MLMuser) + ", " $q += (dbQuote "SMTPpassword" ) + " = " + (dbText $MLMpassword) + ", " $q += (dbQuote "CycleTime" ) + " = " + (dbInt $MLMcycle) + ", " $q += (dbQuote "Logfile" ) + " = " + (dbText $MLMlogfile) + ", " $q += (dbQuote "ConfirmDays" ) + " = " + (dbInt $MLMconfirmDays) + ", " $q += (dbQuote "MaximumMessageSize") + " = " + (dbInt $MLMmaxsize) + " " $q += "WHERE " + (dbQuote "ID") + "=" + (dbInt $MLMid) log "dbUpdateServerOptions:" ("query='" + $q + "'") $result = dbExecuteNonQuery $q if ($result -lt 0) { throw ("Failed updating ServerOptions record.") } return $result } -----Original Message----- From: Joseph L. Casale [mailto:[email protected]] Sent: Monday, April 02, 2012 12:39 PM To: NT System Admin Issues Subject: Powershell ODBC and SQL scripts I have to make a ps script that is portable for use with MSSQL and Oracle backends to apply regular schema updates then reload stored procs. Starting with SQL half first, there is plenty of sample code for running individual statements against an SQL server but that means rewriting the script each time. Anyone know a ps method using the DbCommand Class to play a script into the database? Thanks! jlc ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin
