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
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>>
>>
>
>
