List:General Discussion« Previous MessageNext Message »
From:Stephen Cook Date:May 12 2006 7:46am
Subject:Re: PRINT statement?
View as plain text  
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.

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