How to echo print statements while executing a sql script

We have a simple sql script which needs to be executed against a MySQL database and we would like print log statements on the progress of the script (e.g. Inserted 10 records into foo or Deleted 5 records from bar). How do we do this?

1. I would like to know the syntax to be used for insert/update/delete statements.
2. How do I know about the number of rows affected by my statement(s).
3. I would also like to control printing them using a ECHO off or on command at the top of the script.
4. The script should be portable across Windows / Linux OS.

6 Answers

This will give you are simple print within a sql script:

select 'This is a comment' AS '';

Alternatively, this will add some dynamic data to your status update if used directly after an update, delete, or insert command:

select concat ("Updated ", row_count(), " rows") as ''; 

2

I don't know if this helps:

suppose you want to run a sql script (test.sql) from the command line:

mysql < test.sql

and the contents of test.sql is something like:

SELECT * FROM information_schema.SCHEMATA;
\! echo "I like to party...";

The console will show something like:

CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME def information_schema utf8 def mysql utf8 def performance_schema utf8 def sys utf8
I like to party...

So you can execute terminal commands inside an sql statement by just using \!, provided the script is run via a command line.

\! #terminal_commands

3

Just to make your script more readable, maybe use this proc:

DELIMITER ;;
DROP PROCEDURE IF EXISTS printf;
CREATE PROCEDURE printf(thetext TEXT)
BEGIN select thetext as ``; END;
;;
DELIMITER ;

Now you can just do:

call printf('Counting products that have missing short description');

1

What about using mysql -v to put mysql client in verbose mode ?

For mysql you can add \p to the commands to have them print out while they run in the script:

SELECT COUNT(*) FROM `mysql`.`user`
\p;

Run it in the MySQL client:

mysql> source example.sql
--------------
SELECT COUNT(*) FROM `mysql`.`user`
--------------
+----------+
| COUNT(*) |
+----------+
| 24 |
+----------+
1 row in set (0.00 sec)

You can use print -p -- in the script to do this example :

#!/bin/ksh
mysql -u username -ppassword -D dbname -ss -n -q |&
print -p -- "select count(*) from some_table;"
read -p get_row_count1
print -p -- "select count(*) from some_other_table;"
read -p get_row_count2
print -p exit ;
#
echo $get_row_count1
echo $get_row_count2
#
exit

1

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy