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
>>>>
>>>>
>>>
>>>
>>>
>>
>
>
