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