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