From: Peter Brawley Date: May 12 2006 12:10pm Subject: Re: PRINT statement? List-Archive: http://lists.mysql.com/mysql/197933 Message-Id: <44647B40.9060203@earthlink.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Stephen Cook wrote: > 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. The preference expressed is to that SQL not be bowdlerised into Microsoftese. PB ----- > > 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" >>>> >>>> To: "Rhino" ; "Stephen Cook" >>>> Cc: "MySQL List" >>>> 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" >>>> To: "Rhino" >>>> Cc: "MySQL List" >>>> 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" >>>>>> >>>>>> To: "MySQL List" >>>>>> 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