Thanks, Quentin, for the documentation.
Assuming that the Transact-SQL Help file is using various terms in
the same way as MySQL does, particularly "string expression" and
"function", I think we will find that the SQL SELECT will do all of
the things that Stephen has come to expect from the PRINT statement
in MS SQL Server.
I've just put together an SQL Script that I think demonstrates that
SELECT can do mostl of the same things as the PRINT statement.
Here is the script, which works perfectly in MySQL 4.0.15:
=================================================================
select "=== S C R I P T B E G I N S ===" as "";
select "CONNECT TO DATABASE" as "Action";
use tmp;
select "DROP/CREATE TABLE" as "Action";
drop table if exists users;
create table if not exists users
(user_id smallint not null,
user_fname char(20) not null,
user_lname char(20) not null,
user_birthdate date not null,
user_education_years int not null,
primary key(user_id));
select "POPULATE TABLE AND DISPLAY CONTENTS" as "Action";
insert into users values
(1, 'Alan', 'Adams', '1970-04-08', 15),
(2, 'Bill', 'Baker', '1964-02-01', 18),
(3, 'Cass', 'Cooke', '1981-12-04', 12),
(4, 'Dina', 'Davis', '1944-06-06', 19),
(5, 'Earl', 'Edger', '1990-08-02', 17);
select * from users;
select "SET AND DISPLAY SCRIPT VARIABLES" as "Action";
set @minimum_education_years = 16;
set @birthdate_of_youngest_legal_worker = date_sub(curdate(),
interval 16 year);
select " " as "Variable",
" " as "Value"
UNION
select "minimum_education_years=", @minimum_education_years
UNION
select "birthdate_of_youngest_legal_worker=",
@birthdate_of_youngest_legal_worker;
select " " as "Variable",
" " as "Value"
UNION
select "minimum_education_years=", @minimum_education_years
UNION
select "birthdate_of_youngest_legal_worker=",
@birthdate_of_youngest_legal_worker;
select "EXECUTE QUERIES THAT USE SCRIPT VARIABLES" as "Action";
select concat("Get users who have more than ", @minimum_education_years,
" years of education") as "Query";
select * from users
where user_education_years >= @minimum_education_years;
select concat("Get users who are old enough to work, i.e. were born
before ",
@birthdate_of_youngest_legal_worker) as "Query";
select * from users
where user_birthdate <= @legal_to_work;
select "DISPLAY FUNCTION RESULTS" as "Action";
select " " as "Function", " "
as "Value"
UNION
select "curdate()=", curdate()
UNION
select "now()=", now()
UNION
select "Firstname+Lastname=", concat(user_fname, ' ', user_lname)
from users where user_id = 1;
select "=== S C R I P T E N D S ===" as "";
=================================================================
and this is the output of the script:
=================================================================
+-----------------------------------+
| |
+-----------------------------------+
| === S C R I P T B E G I N S === |
+-----------------------------------+
1 row in set (0.00 sec)
+---------------------+
| Action |
+---------------------+
| CONNECT TO DATABASE |
+---------------------+
1 row in set (0.00 sec)
Database changed
+-------------------+
| Action |
+-------------------+
| DROP/CREATE TABLE |
+-------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+-------------------------------------+
| Action |
+-------------------------------------+
| POPULATE TABLE AND DISPLAY CONTENTS |
+-------------------------------------+
1 row in set (0.00 sec)
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
+---------+------------+------------+----------------+----------------------+
| user_id | user_fname | user_lname | user_birthdate |
user_education_years |
+---------+------------+------------+----------------+----------------------+
| 1 | Alan | Adams | 1970-04-08
| 15 |
| 2 | Bill | Baker | 1964-02-01
| 18 |
| 3 | Cass | Cooke | 1981-12-04
| 12 |
| 4 | Dina | Davis | 1944-06-06
| 19 |
| 5 | Earl | Edger | 1990-08-02
| 17 |
+---------+------------+------------+----------------+----------------------+
5 rows in set (0.00 sec)
+----------------------------------+
| Action |
+----------------------------------+
| SET AND DISPLAY SCRIPT VARIABLES |
+----------------------------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
+-------------------------------------+------------+
| Variable | Value |
+-------------------------------------+------------+
| | |
| minimum_education_years= | 16 |
| birthdate_of_youngest_legal_worker= | 1990-05-11 |
+-------------------------------------+------------+
3 rows in set (0.00 sec)
+-------------------------------------------+
| Action |
+-------------------------------------------+
| EXECUTE QUERIES THAT USE SCRIPT VARIABLES |
+-------------------------------------------+
1 row in set (0.00 sec)
+----------------------------------------------------+
| Query |
+----------------------------------------------------+
| Get users who have more than 16 years of education |
+----------------------------------------------------+
1 row in set (0.00 sec)
+---------+------------+------------+----------------+----------------------+
| user_id | user_fname | user_lname | user_birthdate |
user_education_years |
+---------+------------+------------+----------------+----------------------+
| 2 | Bill | Baker | 1964-02-01
| 18 |
| 4 | Dina | Davis | 1944-06-06
| 19 |
| 5 | Earl | Edger | 1990-08-02
| 17 |
+---------+------------+------------+----------------+----------------------+
3 rows in set (0.00 sec)
+------------------------------------------------------------------------+
|
Query |
+------------------------------------------------------------------------+
| Get users who are old enough to work, i.e. were born before
1990-05-11 |
+------------------------------------------------------------------------+
1 row in set (0.00 sec)
Empty set (0.00 sec)
+--------------------------+
| Action |
+--------------------------+
| DISPLAY FUNCTION RESULTS |
+--------------------------+
1 row in set (0.00 sec)
+---------------------+---------------------+
| Function | Value |
+---------------------+---------------------+
| | |
| curdate()= | 2006-05-11 00:00:00 |
| now()= | 2006-05-11 11:39:49 |
| Firstname+Lastname= | Alan Adams |
+---------------------+---------------------+
4 rows in set (0.00 sec)
+-------------------------------+
| |
+-------------------------------+
| === S C R I P T E N D S === |
+-------------------------------+
1 row in set (0.00 sec)
=================================================================
If you execute this script on your own MySQL servers, you'll see that
SELECT can display all of the following on the console:
- 'any ASCII text'; examples: "Action", "DROP/CREATE TABLE", etc.
- local variables; examples: @minimum_education_years,
@birthdate_of_youngest_legal_worker
- functions; examples: curdate(), now(), concat()
The only thing I'm not sure about is string expressions. I can't find
a clear definition/example of a string expression in MySQL so I can't
construct an example to see if SELECT can handle it. If anyone can
give me one or two things that are indisputably string expressions, I
can add them to the script and verify that I can print them with SELECT.
--
By the way, I should explain one technique I'm using, just to make
sure that everyone understands its significance. In several of the
examples, I use UNIONs. For instance, in the statements that display
the script variables, the code reads as follows:
select " " as "Variable",
" " as "Value"
UNION
select "minimum_education_years=", @minimum_education_years
UNION
select "birthdate_of_youngest_legal_worker=",
@birthdate_of_youngest_legal_worker;
The first SELECT produces only a blank line in the result set.
Naturally, this is not important and you can delete the first SELECT
and the UNION keyword that follows it if you want to remove the blank
line. However, the first SELECT combines two other functions: it
controls the column headings for the result set, via the "AS"
clauses, AND, most importantly, it sets the width of the columns in
the table, via the long blank-filled strings in the SELECT clause,
e.g. " ". Therefore, if you drop the
first SELECT (and its UNION), you will find that the column names of
the result set are the values from the (new) first SELECT, i.e.
"minimum_education_years=" and "@minimum_education_years", and, more
importantly, that the width of the columns is too narrow and some of
the information is truncated. For example the value shown for the
second variable name is shown as "birthdate_of_youngest_le" and the
VALUE of that variable is shown only as "1990", NOT the correct
value, which is "1990-05-11". The danger is that it is not obvious
that the value of the variable has been truncated. When I first
encountered this, I thought I'd written the date_sub() function
incorrectly and messed around with it for awhile before I discovered
the truncation problem. Therefore, my technique is to always use the
first SELECT to set the column names for the result set AND to
control the width of the result set columns.
--
Okay then, aside from the issue of string expressions, which I'm not
sure about yet, I think we can see that SELECT can do everything else
that the PRINT command supports.
--
Rhino
----- Original Message ----- From: "Quentin Bennett"
<[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>; "Stephen Cook" <[EMAIL PROTECTED]>
Cc: "MySQL List" <mysql@lists.mysql.com>
Sent: Wednesday, May 10, 2006 11:59 PM
Subject: RE: PRINT statement?
From Transact-SQL Help file:
PRINT
Returns a user-defined message to the client.
Syntax
PRINT 'any ASCII text' | @local_variable | @@FUNCTION | string_expr
Arguments
'any ASCII text'
Is a string of text.
@local_variable
Is a variable of any valid character data type. @local_variable must
be char or varchar, or be able to be implicitly converted to those
data types.
@@FUNCTION
Is a function that returns string results. @@FUNCTION must be char or
varchar, or be able to be implicitly converted to those data types.
string_expr
Is an expression that returns a string. Can include concatenated
literal values and variables. The message string can be up to 8,000
characters long; any characters after 8,000 are truncated.
-----Original Message-----
From: Rhino [mailto:[EMAIL PROTECTED]
Sent: Thursday, 11 May 2006 3:51 p.m.
To: Stephen Cook
Cc: MySQL List
Subject: Re: PRINT statement?
I am not familiar with the PRINT command so I don't know what it does. I
played with MS SQL Server once for a couple of days a few years back and
that is the only contact I've ever had with SQL Server.
If you can tell me what PRINT does, in detail, maybe I can suggest
another
alternative.
--
Rhino
----- Original Message ----- From: "Stephen Cook" <[EMAIL PROTECTED]>
To: "Rhino" <[EMAIL PROTECTED]>
Cc: "MySQL List" <mysql@lists.mysql.com>
Sent: Wednesday, May 10, 2006 8:09 PM
Subject: Re: PRINT statement?
I've started using the SELECT with no other clauses but I am still
curious
about a PRINT-like command. It is for SQL scripts.
Rhino wrote:
----- Original Message ----- From: "Stephen Cook" <[EMAIL PROTECTED]>
To: "MySQL List" <mysql@lists.mysql.com>
Sent: Sunday, May 07, 2006 3:53 AM
Subject: PRINT statement?
Is there a statement similar to PRINT in T-SQL (MicroSoft SQL
Server)?
It would be handy to debug some scripts.
If you're talking about a script that is running SQL, you can
simply use
the SELECT statement without any FROM, WHERE, ORDER BY, GROUP BY or
HAVING clauses. For example:
select "Creating Foo table" as "Action";
will produce the following output:
+----------------------+
| Action |
+----------------------+
| Creating Foo table |
+----------------------+
1 row in set (0.00 sec)
If you're talking about an OS script, you can use OS commands to
display
things. For example, I have some BASH scripts on our Linux server so I
can use the BASH echo command, like this:
#!/bin/bash
report_date=`/bin/date`
echo "Report Date:" $report_date;
to produce this output:
Report Date: Sun May 7 09:42:57 EDT 2006
--
Rhino
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date:
09/05/2006
