2,187
edits
Difference between revisions of "Power Shell"
Jump to navigation
Jump to search
m
→Commands: Added Reader Query
(→MySQL: Reworked) |
m (→Commands: Added Reader Query) |
||
| Line 218: | Line 218: | ||
# So, to insert records into a table | # So, to insert records into a table | ||
$query = "INSERT INTO test (id, name, age) VALUES (1, 'Joe', 33)" | $query = "INSERT INTO test (id, name, age) VALUES (1, 'Joe', 33)" | ||
$Rows = | $Rows = ExecuteMySQLNonQuery $conn $query | ||
Write-Host $Rows " inserted into database" | Write-Host $Rows " inserted into database" | ||
</pre> | </pre> | ||
'''Reader Query''' | '''Reader Query''' | ||
In theory, this should work, but it doesn't seem to. 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 | |||
<pre> | |||
$query = "SELECT * FROM subnets;" | |||
$cmd = $connMySQL.CreateCommand() | |||
$cmd.CommandText = $query | |||
$results = $cmd.ExecuteReader() | |||
$cmd.Dispose() | |||
while ($results.Read()) { | |||
for ($i= 0; $i -lt $reader.FieldCount; $i++) { | |||
write-output $reader.GetValue($i).ToString() | |||
} | |||
} | |||
</pre> | |||
Instead, this function seems to work perfectly<ref name=suite101.com>{{cite web |url=http://database-programming.suite101.com/article.cfm/connecting_to_mysql_from_powershell |title=Connecting to MySQL from PowerShell |publisher=Mark Alexander Bain |year=2009 |accessdate=2010-01-07}}</ref>. I suspect it could be simplified, but it does the job I want, returning a table of results. | |||
<pre> | |||
function ExecuteMySQLQuery([string]$query) { | |||
# NonQuery - Insert/Update/Delete query where no return data is required | |||
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $connMySQL) # Create SQL command | |||
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd) # Create data adapter from query command | |||
$dataSet = New-Object System.Data.DataSet # Create dataset | |||
$recordCount = $dataAdapter.Fill($dataSet, "data") # Fill dataset from data adapter, with name "data" | |||
$cmd.Dispose() | |||
return $dataSet.Tables["data"] | |||
} | |||
# So, to produce a table of results from a query... | |||
$query = "SELECT * FROM subnets;" | |||
ExecuteMySQLQuery $query | Format-Table | |||
'''Scalar Query''' | '''Scalar Query''' | ||