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
