List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:May 12 2006 12:10pm
Subject:Re: PRINT statement?
View as plain text  
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
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>
>>
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.6/336 - Release Date: 5/10/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