From: Peter Brawley Date: May 12 2006 3:26am Subject: Re: PRINT statement? List-Archive: http://lists.mysql.com/mysql/197923 Message-Id: <44640078.7030501@earthlink.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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