https://vwiki.co.uk/index.php?title=MySQL_and_PowerShell&feed=atom&action=history
MySQL and PowerShell - Revision history
2024-03-29T05:24:02Z
Revision history for this page on the wiki
MediaWiki 1.37.2
https://vwiki.co.uk/index.php?title=MySQL_and_PowerShell&diff=2668&oldid=prev
Sstrutt: /* Connect */ Made function names consistent
2017-08-14T21:00:09Z
<p><span dir="auto"><span class="autocomment">Connect: </span> Made function names consistent</span></p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en-GB">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 21:00, 14 August 2017</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l36">Line 36:</td>
<td colspan="2" class="diff-lineno">Line 36:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Improved connect function with error catcher...</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>Improved connect function with error catcher...</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><source lang="powershell"></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div><source lang="powershell"></div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>function <del style="font-weight: bold; text-decoration: none;">ConnectMySQL</del>([string]$user, [string]$pass, [string]$MySQLHost, [string]$database) { </div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>function <ins style="font-weight: bold; text-decoration: none;">Connect-MySQL</ins>([string]$user, [string]$pass, [string]$MySQLHost, [string]$database) { </div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> # Load MySQL .NET Connector Objects </div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> # Load MySQL .NET Connector Objects </div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") </div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") </div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l61">Line 61:</td>
<td colspan="2" class="diff-lineno">Line 61:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></source></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></source></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>{{#widget:Widget:GoogleAdSenseSkyscraper}}</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>{{#widget:Widget:GoogleAdSenseSkyscraper}}</div></td></tr>
<tr><td colspan="2" class="diff-side-deleted"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></ins></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>= Commands =</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>= Commands =</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>All database operations are done through methods of the <code> MySqlCommand </code> object, the two methods of main interest are...</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>All database operations are done through methods of the <code> MySqlCommand </code> object, the two methods of main interest are...</div></td></tr>
</table>
Sstrutt
https://vwiki.co.uk/index.php?title=MySQL_and_PowerShell&diff=2649&oldid=prev
Sstrutt: Add Google Ads
2016-10-04T21:49:20Z
<p>Add Google Ads</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en-GB">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 21:49, 4 October 2016</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l1">Line 1:</td>
<td colspan="2" class="diff-lineno">Line 1:</td></tr>
<tr><td colspan="2" class="diff-side-deleted"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">{{#widget:Widget:GoogleAdSenseSkyscraper}}</ins></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>To be able to interact with a MySQL database from PowerShell you need to have the MySQL .NET Connector installed first on the machine that you're going to run PowerShell from - http://dev.mysql.com/downloads/connector/net/</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>To be able to interact with a MySQL database from PowerShell you need to have the MySQL .NET Connector installed first on the machine that you're going to run PowerShell from - http://dev.mysql.com/downloads/connector/net/</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l59">Line 59:</td>
<td colspan="2" class="diff-lineno">Line 60:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>} </div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>} </div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></source></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></source></div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div> </div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;">{{#widget:Widget:GoogleAdSenseSkyscraper}}</ins></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>= Commands =</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>= Commands =</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>All database operations are done through methods of the <code> MySqlCommand </code> object, the two methods of main interest are...</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>All database operations are done through methods of the <code> MySqlCommand </code> object, the two methods of main interest are...</div></td></tr>
</table>
Sstrutt
https://vwiki.co.uk/index.php?title=MySQL_and_PowerShell&diff=2112&oldid=prev
Sstrutt: Minor reformatting, and Meta
2012-08-17T10:29:40Z
<p>Minor reformatting, and Meta</p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en-GB">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 10:29, 17 August 2012</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l1">Line 1:</td>
<td colspan="2" class="diff-lineno">Line 1:</td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>To be able to interact with a MySQL database from PowerShell you need to have the MySQL .NET Connector installed first - http://dev.mysql.com/downloads/connector/net/</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>To be able to interact with a MySQL database from PowerShell you need to have the MySQL .NET Connector installed first <ins style="font-weight: bold; text-decoration: none;">on the machine that you're going to run PowerShell from </ins>- http://dev.mysql.com/downloads/connector/net/</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>= Connect =</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>= Connect =</div></td></tr>
<tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l19">Line 19:</td>
<td colspan="2" class="diff-lineno">Line 19:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> $conn.Close()</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div> $conn.Close()</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>}</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>}</div></td></tr>
<tr><td colspan="2" class="diff-side-deleted"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"></source></ins></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"># </del>So, for example...</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>So, for example...</div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div> </div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"><source lang="powershell"></ins></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># Connection Variables </div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div># Connection Variables </div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>$user = 'myuser' </div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>$user = 'myuser' </div></td></tr>
</table>
Sstrutt
https://vwiki.co.uk/index.php?title=MySQL_and_PowerShell&diff=2004&oldid=prev
Sstrutt: /* Test for NULL */ Added additional error example
2012-06-21T09:38:42Z
<p><span dir="auto"><span class="autocomment">Test for NULL: </span> Added additional error example</span></p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en-GB">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 09:38, 21 June 2012</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l145">Line 145:</td>
<td colspan="2" class="diff-lineno">Line 145:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>= Test for NULL =</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>= Test for NULL =</div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>In order to be able to test for a NULL value in a set of results from a query, you need to compare against a specific DB NULL value. Otherwise you<del style="font-weight: bold; text-decoration: none;">'ll </del>get <del style="font-weight: bold; text-decoration: none;">an error </del>similar to...</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>In order to be able to test for a NULL value in a set of results from a query, you need to compare against a specific DB NULL value. Otherwise you <ins style="font-weight: bold; text-decoration: none;">can </ins>get <ins style="font-weight: bold; text-decoration: none;">errors </ins>similar to...</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div><del style="font-weight: bold; text-decoration: none;"><code> </del>Error: "Cannot convert the "0" value of type "System.Int32" to type "System.DBNull" <del style="font-weight: bold; text-decoration: none;"></code></del></div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> </ins>Error: "Cannot convert the "0" value of type "System.Int32" to type "System.DBNull" </div></td></tr>
<tr><td colspan="2" class="diff-side-deleted"></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div><ins style="font-weight: bold; text-decoration: none;"> Method invocation failed because [System.DBNull] doesn't contain a method named 'xxx'.</ins></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><br/></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>So to correctly test, use the following...</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>So to correctly test, use the following...</div></td></tr>
</table>
Sstrutt
https://vwiki.co.uk/index.php?title=MySQL_and_PowerShell&diff=1982&oldid=prev
Sstrutt: /* Reader Query */ Corrected error in example
2012-06-13T15:25:57Z
<p><span dir="auto"><span class="autocomment">Reader Query: </span> Corrected error in example</span></p>
<table style="background-color: #fff; color: #202122;" data-mw="interface">
<col class="diff-marker" />
<col class="diff-content" />
<col class="diff-marker" />
<col class="diff-content" />
<tr class="diff-title" lang="en-GB">
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">← Older revision</td>
<td colspan="2" style="background-color: #fff; color: #202122; text-align: center;">Revision as of 15:25, 13 June 2012</td>
</tr><tr><td colspan="2" class="diff-lineno" id="mw-diff-left-l115">Line 115:</td>
<td colspan="2" class="diff-lineno">Line 115:</td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>$query = "SELECT * FROM subnets;"</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>$query = "SELECT * FROM subnets;"</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>$result = Execute-MySQLQuery $query</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>$result = Execute-MySQLQuery $query</div></td></tr>
<tr><td class="diff-marker" data-marker="−"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #ffe49c; vertical-align: top; white-space: pre-wrap;"><div>Write-Host "Found" <del style="font-weight: bold; text-decoration: none;">(</del>$result.<del style="font-weight: bold; text-decoration: none;">Length) </del>"rows..."</div></td><td class="diff-marker" data-marker="+"></td><td style="color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #a3d3ff; vertical-align: top; white-space: pre-wrap;"><div>Write-Host <ins style="font-weight: bold; text-decoration: none;">(</ins>"Found " <ins style="font-weight: bold; text-decoration: none;">+ </ins>$result.<ins style="font-weight: bold; text-decoration: none;">rows.count + </ins>" rows..."<ins style="font-weight: bold; text-decoration: none;">)</ins></div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>$result | Format-Table</div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div>$result | Format-Table</div></td></tr>
<tr><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></source></div></td><td class="diff-marker"></td><td style="background-color: #f8f9fa; color: #202122; font-size: 88%; border-style: solid; border-width: 1px 1px 1px 4px; border-radius: 0.33em; border-color: #eaecf0; vertical-align: top; white-space: pre-wrap;"><div></source></div></td></tr>
</table>
Sstrutt
https://vwiki.co.uk/index.php?title=MySQL_and_PowerShell&diff=1827&oldid=prev
Sstrutt: Initial creation - content from PowerShell Examples page
2012-04-18T13:30:28Z
<p>Initial creation - content from PowerShell Examples page</p>
<p><b>New page</b></p><div>To be able to interact with a MySQL database from PowerShell you need to have the MySQL .NET Connector installed first - http://dev.mysql.com/downloads/connector/net/<br />
<br />
= Connect =<br />
Make sure you have the .NET connector installed 1st - http://dev.mysql.com/downloads/connector/net/<br />
<br />
<source lang="powershell"><br />
function Connect-MySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) { <br />
# Load MySQL .NET Connector Objects <br />
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") <br />
<br />
# Open Connection <br />
$connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE" <br />
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) <br />
$conn.Open() <br />
return $conn <br />
} <br />
<br />
function Disconnect-MySQL($conn) {<br />
$conn.Close()<br />
}<br />
<br />
# So, for example...<br />
<br />
# Connection Variables <br />
$user = 'myuser' <br />
$pass = 'mypass' <br />
$database = 'mydatabase' <br />
$MySQLHost = 'database.server.com' <br />
<br />
# Connect to MySQL Database <br />
$conn = Connect-MySQL $user $pass $MySQLHost $database <br />
</source><br />
<br />
Improved connect function with error catcher...<br />
<source lang="powershell"><br />
function ConnectMySQL([string]$user, [string]$pass, [string]$MySQLHost, [string]$database) { <br />
# Load MySQL .NET Connector Objects <br />
[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") <br />
<br />
# Open Connection <br />
$connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE" <br />
try {<br />
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) <br />
$conn.Open()<br />
} catch [System.Management.Automation.PSArgumentException] {<br />
Log "Unable to connect to MySQL server, do you have the MySQL connector installed..?"<br />
Log $_<br />
Exit<br />
} catch {<br />
Log "Unable to connect to MySQL server..."<br />
Log $_.Exception.GetType().FullName<br />
Log $_.Exception.Message<br />
exit<br />
}<br />
Log "Connected to MySQL database $MySQLHost\$database"<br />
<br />
return $conn <br />
} <br />
</source><br />
<br />
= Commands =<br />
All database operations are done through methods of the <code> MySqlCommand </code> object, the two methods of main interest are...<br />
* '''ExecuteNonQuery''' - Used for queries that don't return any real information, such as an INSERT, UPDATE, or DELETE.<br />
* '''ExecuteReader''' - Used for normal queries that return multiple values. Results need to be received into <code> MySqlDataReader </code> object.<br />
* '''ExecuteScalar''' - Used for normal queries that return a single. The result needs to be received into a variable.<br />
<br />
== Non-Query ==<br />
<source lang="powershell"><br />
function Execute-MySQLNonQuery($conn, [string]$query) { <br />
$command = $conn.CreateCommand() # Create command object<br />
$command.CommandText = $query # Load query into object<br />
$RowsInserted = $command.ExecuteNonQuery() # Execute command<br />
$command.Dispose() # Dispose of command object<br />
if ($RowsInserted) { <br />
return $RowInserted <br />
} else { <br />
return $false <br />
} <br />
} <br />
<br />
# So, to insert records into a table <br />
$query = "INSERT INTO test (id, name, age) VALUES (1, 'Joe', 33)" <br />
$Rows = Execute-MySQLNonQuery $conn $query <br />
Write-Host $Rows " inserted into database" <br />
</source><br />
<br />
== Reader Query ==<br />
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.<br />
<source lang="powershell"><br />
$query = "SELECT * FROM subnets;"<br />
$cmd = $connMySQL.CreateCommand() <br />
$cmd.CommandText = $query <br />
$results = $cmd.ExecuteReader() <br />
$cmd.Dispose() <br />
while ($results.Read()) {<br />
for ($i= 0; $i -lt $reader.FieldCount; $i++) {<br />
write-output $reader.GetValue($i).ToString()<br />
}<br />
}<br />
</source><br />
<br />
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.<br />
<source lang="powershell"><br />
function Execute-MySQLQuery([string]$query) { <br />
# NonQuery - Insert/Update/Delete query where no return data is required<br />
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $connMySQL) # Create SQL command<br />
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd) # Create data adapter from query command<br />
$dataSet = New-Object System.Data.DataSet # Create dataset<br />
$dataAdapter.Fill($dataSet, "data") # Fill dataset from data adapter, with name "data" <br />
$cmd.Dispose()<br />
return $dataSet.Tables["data"] # Returns an array of results<br />
}<br />
<br />
# So, to produce a table of results from a query...<br />
$query = "SELECT * FROM subnets;"<br />
$result = Execute-MySQLQuery $query<br />
Write-Host "Found" ($result.Length) "rows..."<br />
$result | Format-Table<br />
</source><br />
<br />
== Scalar Query ==<br />
<source lang="powershell"><br />
function Execute-MySQLScalar([string]$query) {<br />
# Scalar - Select etc query where a single value of return data is expected<br />
$cmd = $SQLconn.CreateCommand() # Create command object<br />
$cmd.CommandText = $query # Load query into object<br />
$cmd.ExecuteScalar() # Execute command<br />
}<br />
</source><br />
<br />
== Other ==<br />
To perform other random commands that don't read or write data to/from a database...<br />
<source lang="powershell"><br />
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)<br />
</source><br />
<br />
Basic escaping of text can be performed by...<br />
<source lang="powershell"><br />
function Escape-MySQLText([string]$text) {<br />
[regex]::replace($text, "'", "\'")<br />
[regex]::replace($text, "\\", "\\")<br />
}<br />
</source><br />
...you'd need to add additional cases to this to allow for string formats that you expect, there isn't an equivalent of PHP's [http://www.php.net/mysql_real_escape_string mysql_real_escape_string()]<br />
<br />
= Test for NULL =<br />
In order to be able to test for a NULL value in a set of results from a query, you need to compare against a specific DB NULL value. Otherwise you'll get an error similar to...<br />
<br />
<code> Error: "Cannot convert the "0" value of type "System.Int32" to type "System.DBNull" </code><br />
<br />
So to correctly test, use the following...<br />
<source lang="powershell"><br />
if ([System.DBNull]::Value.Equals($db_query_result)) {<br />
Write-Host "Result is NULL"<br />
}<br />
</source><br />
The above was sourced from http://blogs.technet.com/b/industry_insiders/archive/2008/09/15/testing-for-database-null-values-from-powershell.aspx<br />
<br />
[[Category:PowerShell]]<br />
[[Category:MySQL]]</div>
Sstrutt