Spaces in parameter values when executing sql stored procedures in powershell

Question

I'm having an issue sending a msyqlcommand to execute a store procedure.

This the code that I am using, I have a cmdlet called set-outage I've created. It has parameters set and the only code in it that's important to this is these two lines. Everything else handles what happens after. It errors on the ExecuteNonQuery line.

$query = New-Object Mysql.data.mysqlclient.mysqlcommand("set_outage('$system','$startd','$startt','$endd','$endt','$changes','$user')", (invoke-mysql))
$rdr = $query.ExecuteNonQuery()

all of my parameters are setup as

[string]$variable

The stored procedure is

CREATE DEFINER=`root`@`localhost` PROCEDURE `set_outage`(in system varchar(55), in startd varchar(25), in startt varchar(25), in endd varchar(25), in endt varchar(25),  in changes longtext, in user varchar(55))

BEGIN
Insert into to_process (node_name, User_Requested, Start_date, Start_time, End_Date, End_time, Started ) Values (system, user, startd, startt, endd, endt, 'no');
end

MySQL allows text values to be stored in date and time fields so that's not a worry. I know changes isn't being used in this command but it will be after I work this out.

Here are two examples... one that works.. and one that doesn't.

set-outage -system viper12 -startd 2014/12/12 -startt 12:00 -endd 2014/12/17 -endt 17:00 -user 'Daniel asdfsdaf' -changes 'test'

The error returned:

Exception calling "ExecuteNonQuery" with "0" argument(s): "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set_outage('viper12','2014/12/12','12:00','2014/12/17','17:00','test','Daniel as' at line 1" At N:\NOC\Powershell Scripts\testingmysql.ps1:194 char:9 + $rdr = $query.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : MySqlException

If I simply remove the extra words or the spaces like so:

set-outage -system viper14 -startd 2014/12/12 -startt 12:00 -endd 2014/12/17 -endt 17:00 -user 'Daniel' -changes 'aasdfsadfsadfsadfasfsdfsadfsatest'

I no longer get an error. I've put " " around them I've used ' ' around them. I've tried to convert the input into a here_strings, I've used the backtick... nothing I do seems to allow the spaces. I've even tried to use @query.prepare() but that doesn't help either. I've tried to single quote the variables in MySQL as well. No luck.

If I direct the output of $query it looks like this.

LastInsertedId    : 0
CommandText       : set_outage('viper12','2014/12/12','12:00','2014/12/17','17:00','test','Daniel asdfsdaf')
CommandTimeout    : 30
CommandType       : Text
IsPrepared        : False
Connection        : MySql.Data.MySqlClient.MySqlConnection
Parameters        : {}
Transaction       : 
EnableCaching     : False
CacheAge          : 0
UpdatedRowSource  : Both
DesignTimeVisible : False
Site              : 
Container         :

If I take the commandtext and use it directly in MySQL it works perfectly. I can't seem to find the right way to quote the data show it will pass with spaces in the variables. Can you point me in the right direction? This could be a stupid overlook on my part.. but I'm still kind of new to this stuff so be gentle :)

Do you create a connection & assign it to the command anywhere? — JohnLBevan
– JohnLBevan, Commented Sep 14, 2014 at 22:00
The (invoke-mysql) portion of the $query is the connection portion. it's a separate cmdlet. — Daniel Peel
– Daniel Peel, Commented Sep 14, 2014 at 22:01
If you take a closer look at the error message, it looks like your SQL command is getting truncated after 80 characters. Notice how the error ends with ... ,'Daniel as' at line 1. Do you have some kind of setting that is causing it to end after 80 characters? What happens if you pass the string "Dan X" for the user parameter? — JamesQMurphy
– JamesQMurphy, Commented Sep 15, 2014 at 3:04
If you look at the command that works.. you'll notice it's quite a bit longer than the one that doesn't. I did that on purpose because I saw that.. I think the error is truncated.. not the sql statement. — Daniel Peel
– Daniel Peel, Commented Sep 15, 2014 at 3:42
What happens if you escape the space? eg. "Daniel\ asdfdsaf" — Bluecakes
– Bluecakes, Commented Sep 15, 2014 at 4:50

Daniel Peel · Accepted Answer · 2014-09-15 22:31:18Z

It looks like, from what I can find, that it's the way I'm passing the information.

Could be wrong...but this way works... by setting the parameters this way verses passing them in the command string.

$query = New-Object Mysql.data.mysqlclient.mysqlcommand("set_outage", (invoke-mysql))
        $query.CommandType = [System.Data.CommandType]'StoredProcedure'
        $query.Parameters.Add("user","$user")
        $query.Parameters.Add("system","$system")
        $query.Parameters.Add("changes","$changes")
        $query.Parameters.Add("startd","$startd")
        $query.Parameters.Add("startt","$startt")
        $query.Parameters.Add("endd","$endd")
        $query.Parameters.Add("endt","$endt")

This appears to only be an issue if I have spaces in my sql command inside the mysqlcommand.

I tested this with the following command:

set-outage -system viper3 -startd 2014/12/12 -startt 12:00 -endd 2014/12/17 -endt 17:00 -changes 'this is a test with lots of spaces' -user 'Daniel Peel'

and I had no issues, it ran and the stored procedure worked as it was supposed to. Any additional input is always welcome.

Collectives™ on Stack Overflow

Spaces in parameter values when executing sql stored procedures in powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related