Querying KACE database using ODBC and Powershell
I'm looking for the "idiots guide" on how to connect Powershell to a MySQL database. Specifically the K1000 ORG1 database.
I have MySQL Workbench connected and have no problem building the needed queries, I just need to be able to run those queries using Powershell.
I have an ODBC connection to the KACE database but after that I'm going beyond my knowledge.
I've tried the below with no luck. But I am completely guessing based on Google searches.
$connectstring = "DSN=K1000;"
$sql = "select * from HD_Ticket"
$conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
$conn.open()
$cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)
$da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)
$dt = New-Object system.Data.datatable
$null = $da.fill($dt)
$conn.close()
$dt
Running the above gives the following:
Exception calling "Fill" with "1" argument(s): "ERROR [42S02] [MySQL][ODBC 5.3(w) Driver][mysqld-5.6.10-log]Table 'ORG1.HD_Ticket' doesn't exist"
At C:\test\connecttomysql.ps1:12 char:2
+ $null = $da.fill($dt)
+ ~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : OdbcException
Answers (2)
I was able to connect with the following:
[system.reflection.assembly]::LoadWithPartialName("MySql.Data")
$cn = New-Object -TypeName MySql.Data.MySqlClient.MySqlConnection
$cn.ConnectionString = "SERVER=X.X.X.X;DATABASE=ORG1;UID=user;PWD=password"
$cn.Open()
$cm = New-Object -TypeName MySql.Data.MySqlClient.MySqlCommand
$sql = "SELECT ID FROM HD_TICKET LIMIT 50"
$cm.Connection = $cn
$cm.CommandText = $sql
$dr = $cm.ExecuteReader()
while($dr.Read()){ $dr.GetString(0) }
