How to print output of affected rows with Powershell and MySQL?

Question

I'm having a little problem on how to execute two queries one after the other.

In my Powershell script I'm adding records in a loop (which works perfectly fine). Outside the LOOP I execute a query to Delete "x" number of records based on the TIMESTAMP (which is working) but after that one is executed I want to run a query to display the number of ROWS that have been affected and display it. Below is my code.

Thanks in advance!

### ============================================================= 
       ### Deleting every record that is 30 minutes old
       ### =============================================================  

        $command = $connection.CreateCommand();
        $command.Connection  = $connection;
        $command.CommandText = "DELETE FROM workstation_userlogged
                                WHERE lastupdate < (NOW() - INTERVAL 30 MINUTE)";


        $records_deleted = "SELECT row_count()"; #count number of rows affected by the delete query

        $command.CommandText = "$records_deleted";

         TRY{
            $command.ExecuteNonQuery() | out-null;
            Write-Host "Successfully deleted $records_deleted records from the Database" -ForegroundColor Green;
        }
        CATCH{
            Write-Host "Caught the exception" -ForegroundColor Red;
            Write-Host "$_" -ForegroundColor Red;
        }

$command.ExecuteNonQuery() should return the number of affected rows. '[Int]$rows = $command.ExecuteNonQuery()' — Paul
– Paul, Commented Oct 28, 2014 at 16:34
@paul it does not work, it returns 0. you're telling me to assign [int]$rows to command.executenonquery() when it is being used by 2 queries. — mario
– mario, Commented Oct 28, 2014 at 16:46
Does it work if You only execute 1 query? I don't have a db available for testing — Paul
– Paul, Commented Oct 28, 2014 at 16:52
well my delete query does work.. but it does make sense not to assign $row to a command.executenonquery when it is being used for both queries. — mario
– mario, Commented Oct 28, 2014 at 16:53
well in the case that it works with your delete query doesnt that make the second query unnecessary and thus solves the problem? If not then why not just run a select COUNT() with your where condition before deleting the rows? — Paul
– Paul, Commented Oct 28, 2014 at 17:43

dpw · Accepted Answer · 2014-10-28 18:03:28Z

1

You can't use ExecuteNonQuery if you want the result set returned for you (which you do.. that's why you're running SELECT row_count()). Try the ExecuteScalar method instead.

answered Oct 28, 2014 at 18:03

dpw

1,5869 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to print output of affected rows with Powershell and MySQL?

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