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

valleytechAsked:
Who is Participating?
 
Chris StanyonCommented:
I think your SQL line is breaking when it gets to the single quote.

Try changing line 12 to the following - i.e wrap the whole line in double quotes)


$sql.CommandText="INSERT INTO `serverA` (cpu_percentage,available_mbytes) VALUES ('23', '12')"

Open in new window

0
 
valleytechAuthor Commented:
Hi C
yeah, i tried many ways, including that, and i got error
"Exception calling executeNoQuery with 0 arguments..." you syntax error ...."
thanks!
0
 
valleytechAuthor Commented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.