MyReport Command Line Interface
< Return to MyReport Index
Although MyReport is now the reporting work-horse of our OLAP applications, it’s usefulness as an interactive command line query tool remains it’s strength in our development environment!
It is primarily useful to those who know at least a little SQL, but makes a fast information retrieval tool for anyone if the query templates are in use.
How easy is it to use? One example is worth a lot of explanation.
Suppose that you are working in the default database and need quick access to the structure of your ‘order_fact’ table…
myreport -f order_fact
Database: dw3, 19:00:49 Mon Aug 20, 2007
+-------------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| order_id | int(11) | NO | PRI | 0 | |
| date_id | int(11) | NO | MUL | 0 | |
| time_id | int(11) | NO | | 0 | |
| trackid_id | int(11) | NO | MUL | 0 | |
| asin_id | int(10) unsigned | NO | MUL | 0 | |
| title_id | int(10) unsigned | NO | | 0 | |
| category_id | int(11) | NO | MUL | 0 | |
| price | decimal(8,2) | NO | | 0.00 | |
| qty | int(11) | YES | | 0 | |
| order_sum | bigint(1) | NO | | 0 | |
+-------------+------------------+------+-----+---------+-------+
Instant access – no application to open, no client to login to, no connectivity problems, no documentation to open!
Or maybe you want a quick peek at items that you have sold for more than $400.00 through your amazon store:
myreport -q "select order_id, price, title from order_fact join title_dim using(title_id) where price > 400"
Database: dw3, 19:12:00 Mon Aug 20, 2007
+----------+--------+----------------------------------------------------+
| order_id | price | title |
+----------+--------+----------------------------------------------------+
| 112 | 449.99 | Envision EN-7100s 17" LCD High-Resolution Color Dsp|
| 703 | 417.00 | Porter-Cable FCN200 2" Flooring Cleat Nailer |
| 704 | 417.00 | Porter-Cable FCN200 2" Flooring Cleat Nailer |
| 705 | 417.00 | Porter-Cable FCN200 2" Flooring Cleat Nailer |
| 1401 | 409.84 | Sony DVP-FX700 Portable DVD Player with 7" Screen |
| 2312 | 419.95 | Black & Decker CMM1000 19" Cordless Mulching Mower |
| 2442 | 676.00 | Samsung TX-R3079WH 30" Wide SlimFit HDTV |
| 4687 | 489.99 | Envision EN-7100s 17" LCD High-Resolution Color Dsp|
| 4925 | 429.99 | Dyson DC 07 Low Reach Upright Vacuum |
| 4981 | 499.99 | HP iPAQ h1945 with iPAQ Bluetooth GPS Navigation |
| 5332 | 802.24 | Netter Collection of Medical Illustrations Set/13 |
| 5506 | 499.99 | DEWALT DW708 12" Double-Bevel Sliding Compound Saw |
| 5595 | 418.94 | Porter-Cable FCN200 2" Flooring Cleat Nailer |
| 5828 | 439.99 | Audiovox D1020 10" Portable DVD Player |
+----------+--------+----------------------------------------------------+
And if you want to keep your query for later use, write it to a template file using ’-w filename’, replace ‘400’ with /*PRICE*/, then when you want to see the same result with a different price limit:
myreport -t filename --PRICE='800' -x 1
demo: Report 1 - Items sold above a price limit
Database: dw3, 19:35:17 Mon Aug 20, 2007
+----------+--------+----------------------------------------------------+
| order_id | price | title |
+----------+--------+----------------------------------------------------+
| 5332 | 802.24 | Netter Collection of Medical Illustrations Set/13 |
+----------+--------+----------------------------------------------------+
You get the idea! For a complete set of command line options, see the list below.
Command Line Options
Following is the current output of MyReport’s help function:
myreport --help
Usage: myreport [-cdDefghHilqsSTvVw, --help] (-t template_file) [--key=value]
Options:
-c Run elapsed time clock on queries
-d --use-database=dbname, Select database, overrides default or template
(Accepts database name or numeric index, see -l)
-D Show all configured databases on the host server
-e Explain user query (See 'Explain' in MySQL manual)
-f [tablename], Lists tables from current database, or fields from tablename
-g Ignore missing parameters, execute template query with NULL substitutions
-h, --help, Print this help message and exit
-H Format output of all 'select' queries as HTML Tables
-i [n] Display information about report templates, or details of report #n
-l, --list-databases, Displays a list of currently configured databases
-q --query="querystring", Enter query from command line (double quote always)
-s Silent mode, suppresses query result heading information
-S Show template source file specification
-t path/filename Template file location to use for report
-T Displays query output as tabbed rows, default is formatted tables
(Overrides HTML formatting, see -H)
-v Set output to verbose mode, normal processing messages
-V Set output to extra verbose mode, add diagnostic messages
-w path/filename Write CLI query to template file, append if file exists
-x 1[,2,3] Execute querie(s) from report template, report #s from -i
Extra parameters:
--key=value, Sets option 'key' to 'value' for special processing instructions
--config-file=filename, Reads/writes configuration from/to this filename
(Default is .myreport_cfg in current directory, then user's home directory)
--add-database='dbname,user,passwd,host', Configure database for use with MyReport
--default-database=dbname, Sets the default database (must be configured first)
--drop-database=dbname, Removes the specified database from the config file
--test-database=dbname, Tests configuration of database
(Accepts database name or numeric index, see -l)
| 
Contact us
