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