asked on

powershell to insert data into mysql

hey all experts

I'm working on a script that will collected metrics data (cpu load, memory etc, ...) then insert numbers into mysql database server. Then, third-party software will visualize that data.

I used .NET mysql connector provided by mysql.net. The script is pasted below. I've tried many ways, from simple to complex method.

The script below will give up an error that said "Unexpected token '23' in expression or statement" . The query itself will work in mysql console.
Any insight will be so great !!

[void][system.reflection.Assembly]::LoadFrom(“C:\Program Files\MySQL\MySQL Connector Net 6.2.3\Assemblies\MySQL.Data.dll”)
$dbconnect = New-Object MySql.Data.MySqlClient.MySqlConnection
$dbconnect.ConnectionString = “server=192.168.0.25;user id=root;password=mypass$;database=perform;pooling=false”
$dbconnect.Open()
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand

$time=get-date -format u
$cpu = (gwmi win32_processor | select-object loadpercentage).loadpercentage
$availMem =(gwmi win32_perfFormattedData_perfos_memory | select-object availableMbytes).availableMBytes / 1024

$sql.Connection = $dbconnect
$sql.CommandText='INSERT INTO `serverA` (cpu_percentage,available_mbytes) VALUES ('23', '12')'
$dr = $sql.ExecuteNonQuery()
$sql.Dispose()
if ($dr)
{
	return $dr
	}
	else
	{
	return $false
	}
$dbconnect.Close()
$time

Open in new window

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

valleytech

ASKER

Hi C
yeah, i tried many ways, including that, and i got error
"Exception calling executeNoQuery with 0 arguments..." you syntax error ...."
thanks!

valleytech

ASKER

actually
it was my bad ! I created table name as "server-a". The minus sign really changed things alot.
The working command is as follow: it successfully inserts correct date-time , cpu load and memory usage into database ;)

$time=get-date -format u
$time=$time.TrimEnd("Z")
$cpu = (gwmi win32_processor | select-object loadpercentage).loadpercentage
$availMem =(gwmi win32_perfFormattedData_perfos_memory | select-object availableMbytes).availableMBytes / 1024

$sql.Connection = $dbconnect
$sql.CommandText="INSERT INTO clusterA (datetime, cpu_percentage,available_mbytes) VALUES ('$time', $cpu, $availMem)"

Open in new window