List:General Discussion« Previous MessageNext Message »
From:Rhino Date:May 11 2006 3:56pm
Subject:Re: PRINT statement?
View as plain text  
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" <Quentin.Bennett@stripped>
To: "Rhino" <rhino1@stripped>; "Stephen Cook" <sclists@stripped>
Cc: "MySQL List" <mysql@stripped>
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:rhino1@stripped]
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" <sclists@stripped>
To: "Rhino" <rhino1@stripped>
Cc: "MySQL List" <mysql@stripped>
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" <sclists@stripped>
>> To: "MySQL List" <mysql@stripped>
>> 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
>
>



-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 09/05/2006


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 
http://lists.mysql.com/mysql?unsub=1
The information contained in this email is privileged and confidential and
intended for the addressee only. If you are not the intended recipient, you
are asked to respect that confidentiality and not disclose, copy or make use
of its contents. If received in error you are asked to destroy this email
and contact the sender immediately. Your assistance is appreciated.


-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/336 - Release Date: 10/05/2006



-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/336 - Release Date: 10/05/2006

Thread
PRINT statement?Stephen Cook7 May
  • Re: PRINT statement?Rhino7 May
    • Re: PRINT statement?Stephen Cook11 May
  • Re: PRINT statement?Rhino11 May
RE: PRINT statement?Quentin Bennett11 May
  • Re: PRINT statement?Rhino11 May
    • Re: PRINT statement?Stephen Cook12 May
      • Re: PRINT statement?Peter Brawley12 May
        • Re: PRINT statement?Stephen Cook12 May
          • Re: PRINT statement?Mark Leith12 May
            • MySQL commercial licenceAdam Lipscombe12 May
              • Re: MySQL commercial licenceDavid Logan12 May
                • RE: MySQL commercial licenceAdam Lipscombe12 May
                  • Re: MySQL commercial licencesheeri kritzer12 May
          • Re: PRINT statement?Peter Brawley12 May
            • Re: PRINT statement?Stephen Cook13 May