List:General Discussion« Previous MessageNext Message »
From:Stephen Cook Date:May 13 2006 9:45pm
Subject:Re: PRINT statement?
View as plain text  
Microsoft did not invent the concept of outputting a user-defined line 
of text; I'm not going to research this but I doubt if they invented 
using the word "PRINT" for it either. Also, EVERY vendor includes 
extensions to the standard (I just happen to know the keywords for the 
T-SQL ones since that is what I get paid for).

I'm not here to discuss people's religious beliefs, I'm here to learn 
what MySQL can and can not do.


Peter Brawley wrote:
> Stephen Cook wrote:
>> There are such things as extensions to the standard, and many 
>> languages besides BASIC that have the ability to output a character 
>> string. No need to be snippy.
> The preference expressed is to that SQL not be bowdlerised into 
> Microsoftese.
> 
> PB
> 
> -----
>>
>> I will look into the --silent option, thanks!
>>
>>
>> Peter Brawley wrote:
>>> Stephen Cook wrote:
>>>> I appreciate it but SELECT isn't quite what I want.  It adds an 
>>>> extra 4 to 6 lines to the output (drawing the table, headers, row 
>>>> counts, etc).  PRINT simply outputs whatever comes after it:
>>> PRINT is not a SQL command. The mysql client (fortunately) does not 
>>> speak Basic.
>>>
>>> To minimise output in the mysql client, have a look at the -s 
>>> --silent option.
>>>
>>> PB
>>>
>>> -----
>>>
>>>>
>>>> PRINT 'hey you!'
>>>>
>>>> would show:
>>>> hey you!
>>>>
>>>>
>>>>
>>>> Not a big deal I suppose but it makes for a lot more scrolling around.
>>>>
>>>> I've started just dumping the comments (i.e. '') into a table with a 
>>>> timestamp, so I can review it afterwards. Its a close second.
>>>>
>>>>
>>>> Rhino wrote:
>>>>> 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
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>>
> 
> 
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