2,187
edits
m (→Variables: Added "Variable Infromation") |
m (→MySQL: Added Syntax Highlighting and updated Query function) |
||
Line 234: | Line 234: | ||
== MySQL == | == MySQL == | ||
=== Connect === | === Connect === | ||
<source lang="powershell"> | |||
< | |||
function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) { | function ConnectMySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) { | ||
# Load MySQL .NET Connector Objects | # Load MySQL .NET Connector Objects | ||
Line 261: | Line 260: | ||
# Connect to MySQL Database | # Connect to MySQL Database | ||
$conn = ConnectMySQL $user $pass $MySQLHost $database | $conn = ConnectMySQL $user $pass $MySQLHost $database | ||
</source> | |||
</ | |||
=== Commands === | === Commands === | ||
Line 271: | Line 269: | ||
'''Non-Query''' | '''Non-Query''' | ||
< | <source lang="powershell"> | ||
function ExecuteMySQLNonQuery($conn, [string]$query) { | function ExecuteMySQLNonQuery($conn, [string]$query) { | ||
$command = $conn.CreateCommand() # Create command object | $command = $conn.CreateCommand() # Create command object | ||
Line 288: | Line 286: | ||
$Rows = ExecuteMySQLNonQuery $conn $query | $Rows = ExecuteMySQLNonQuery $conn $query | ||
Write-Host $Rows " inserted into database" | Write-Host $Rows " inserted into database" | ||
</ | </source> | ||
'''Reader Query''' | '''Reader Query''' | ||
In theory, this should work, but it doesn't seem to for me. There's something wrong with the <code>while ($results.Read())</code>, in that you end up displaying the last row returned by the SQL query multiple times. Suspect its due to the way that a Reader object only seems to hold a result temporarily. | In theory, this should work, but it doesn't seem to for me. There's something wrong with the <code>while ($results.Read())</code>, in that you end up displaying the last row returned by the SQL query multiple times. Suspect its due to the way that a Reader object only seems to hold a result temporarily. | ||
< | <source lang="powershell"> | ||
$query = "SELECT * FROM subnets;" | $query = "SELECT * FROM subnets;" | ||
$cmd = $connMySQL.CreateCommand() | $cmd = $connMySQL.CreateCommand() | ||
Line 303: | Line 301: | ||
} | } | ||
} | } | ||
</ | </source> | ||
Instead, this approach seems to work more reliably. By loading the data into a dataset, it becomes available for offline manipulation and isn't reliant on the database once the data is loaded in. | Instead, this approach seems to work more reliably. By loading the data into a dataset, it becomes available for offline manipulation and isn't reliant on the database once the data is loaded in. | ||
< | <source lang="powershell"> | ||
function ExecuteMySQLQuery([string]$query) { | function ExecuteMySQLQuery([string]$query) { | ||
# NonQuery - Insert/Update/Delete query where no return data is required | # NonQuery - Insert/Update/Delete query where no return data is required | ||
Line 312: | Line 310: | ||
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd) # Create data adapter from query command | $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd) # Create data adapter from query command | ||
$dataSet = New-Object System.Data.DataSet # Create dataset | $dataSet = New-Object System.Data.DataSet # Create dataset | ||
$dataAdapter.Fill($dataSet, "data") # Fill dataset from data adapter, with name "data" | |||
$cmd.Dispose() | $cmd.Dispose() | ||
return $dataSet.Tables["data"] | return $dataSet.Tables["data"] # Returns an array of results | ||
} | } | ||
# So, to produce a table of results from a query... | # So, to produce a table of results from a query... | ||
$query = "SELECT * FROM subnets;" | $query = "SELECT * FROM subnets;" | ||
ExecuteMySQLQuery $query | Format-Table | $result = ExecuteMySQLQuery $query | ||
Write-Host "Found" ($result.Length) "rows..." | |||
$result | Format-Table | |||
</source> | |||
'''Scalar Query''' | '''Scalar Query''' | ||
'''Other''' | '''Other''' | ||
< | <source lang="powershell"> | ||
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn) | $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn) | ||
</ | </source> | ||
== Exceptions and Error Handling == | == Exceptions and Error Handling == |