GCQ SQL Command Line Query Toolset

GCQ GEN Commandline Query

GEN developed a tool back in 2004 to be enhance the functions provided by sqlcmd and similar. GCQ (GEN CommandLine Query) started as a tool to export data from Microsoft SQL Server Databases in various formats. The first format was XML, but CSV followed shortly after and more recently HTML was added. If your using Microsoft SQL Server and have a need to automate reporting in batch files then GCQ is the tool for you. 

Let's take a look at some of the functions available. 

Library (L)

In this mode, a query can be pre-configured into a library table and command line parameters can be substituted directly into the query at execution time. A simple query such as SELECT ID FROM MYTABLE WHERE FF=1 can be setup as SELECT ID FROM MYTABLE WHERE FF=##1 and the first command line parameter will be substituted for ##1. GCQ allows for up to 9 command line arguments to be substituted. A genuine use for this is substitution in the IN clause such as SELECT * FROM MYTABLE WHERE FF=##1 AND BB IN (##2) Which can then be executed with GCQ L NAME 1 3,4,5 Where NAME is the name of the query and 1 is substituted for ##1 and 3,4,5 is substituted for ##2 resulting in SELECT * FROM MYTABLE WHERE FF=1 AND BB IN (3,4,5) being sent to the server.

Below is an example running a query saved in the Library table called DYNDSTAT which takes 1 parameter on the command line. 

GCQ Library Function

As you can see, the data is exported to the screen along with a header and footer. If you want to hide the header/footer then you can simply use the -q option that only returns the data. Let's assume we want to run a query that we specify on the command line instead of a library function...

Query (Q)

GCQ Query Function

And there we have it, the same result using a direct query on the command line. The action functions available to each client can be controlled so that some users can only use library functions whereas others can use all functions. 

Moving on, a common use of GCQ is to export data from the SQL Server in various formats for example...

GCQ CSV Export

Comma Separated Values (CSV) is still a common format used to exchange data between systems. For example we have a number of charting programs that take CSV data to produce JPG charts for reporting, but a more useful format is probably XML...

GCQ XML Export

XML is very flexible and allows for automated transfer of data between systems, but if you wanted to produce reporting in an email as we all do regularly then HTML is probably more useful...

GCQ HTML Export

Which produces the output below: 

SITE CAMPAIGN TOTAL MAPPED AVAILABLE QUEUED DIALED CLOSED BURN EMPTY UPDATED
1 1 351 0 0 0 0 16 0 False 06/09/2017 09:51:43

 

Cell Formatting, Spacing, and alignment can all be configured from the command line as required, or can be fashioned after the fact using CSS or command line tools like SED or AWK.  

A common batch file to produce an email report in HTML format would be something like 

ECHO ^<html^>^<body^>^<p^>^<h2^> >EMAIL.HTML

ECHO DYNDSTAT OUTPUT (%DATE% %TIME%) >>EMAIL.HTML

ECHO ^</h2^>^</p^> >>EMAIL.HTML

GCQ4 L DYNDSTAT 1 -Q -HTML >>EMAIL.HTML

ECHO ^<BR^>^</body^>^</html^> >>EMAIL.HTML

Which produces a file EMAIL.HTML which can then be sent to recipients using, for example GEN's sendmail.exe command line email tool. Notice the ^ in the file which are used to escape the < and > so they are not interpreted as standard inputput/output redirects. 

The Help File

Basic Syntax

gcq <command> {parameter(s)} {-flags}

You must either Quote (") strings with spaces or use + For example

gcq q "select * from thistable"
gcq q select+*+from+thistable

* Flags must follow any command or parameters

Command Parameters
=========================

Q - Query
Specify the SQL Query as the parameter, for example
gcq q "select * from sometable"

F - Query from File
Specify a filename as the parameter containing one or more SQL
Statements, e.g...
gcq f myquery.sql

L - Query from Library
Specify the library function name as the first parameter, e.g...
gcq l mylibfunc
Additional parameters may be specified in order after the function
name. example...
gcq l mylibfunc 123 fred

B - Process Batch File
Open the batch file specified as the parameter and parse it, e.g...
gcq b mybatch.txt

H - Help system, shows this help file.

V - Shows the current version and other information about the installation.

update - Runs the stub updater to check for and if required update
your copy of GCQ. You should do this often to ensure that
your version is the most up to date.

*See Initial configuration for more commmands

Command Switches
=========================

-p -pause Wait after output for enter key

-q -quiet Surpress everything except data output

-c -csv Switch to Comma Seperated Output

-x -xml Switch to XML Output

-dv:X -vdivider:x Specify X as Formatting Column Divider (text mode only)

-dh:X -hdivider:x Specify X as Horizontal Column Divider (text mode only)

-s:. -spacer:. Specify . as Substitute for Spaces in formatted output (text mode only)

-oh:Header Specify Output Header, mostly used with -q

-of:Footer Specify Output Footer, mostly used with -q

-t -tableschema Show the output Schema

-sql Show the database query

-n Surpress Data Result

-m -more Pause at Page End

-cb:n Set Background Colour

-cf:n Set Background Colour

-l -log Turn on Execution Logging to Library db Log table

-spawn Run in Spawned Mode with special options

Email Sending Commands
=========================

-e:addr -email:addr Send copy of output to email address 'addr'. Can
add more than one -e to send to multiple receipients.

-eb:msg -body:msg use 'msg' as the email body. Can use + to represent
space

-ea:filename Add additional attachment to email. Can use more than
once to attach multiple files.

-es:Subject Specify email subject, can use + to represent space

Initial Configuration
=========================

GCQ connects to databases by using OLEDB Connection Strings.
These connection strings must be specified before gcq can be used.

cd - Specify the Query Connection String, for example
gcq cd "Provider=sqloledb;Data Source=192.168.1.2,1433;Network ...
Library=DBMSSOCN;Initial Catalog=test;User ID=Fred;Password=test;"

cl - Specify the Library Connection String. Use this if you want
to enable Library and Log functionality. See section on
Library and Log later in this document.

cf - Specify persistent default switches. Use this to specify
command switches that should be applied to every gcq operation.
For example...
gcq cf "m p"
Then every time you run
gcq l test
You will actually run
gcq l test -m -p

cp - Specify Initial Parameters required for GCQ to function.
The fomat is...
gcq cp maxdanger mailserverip mailfrom
Where maxdanger is a number from 0 to 99, mailserverIP is the
ip address of your SMTP Server, and Mailfrom is the 'FROM:'
address to be used when gcq sends email.

license Installs the license number into GCQ. The format is
gcq license licensename licensenumber
Specify name and number exactly as they were supplied to you.

GCQ can be configured using a batch file, and an example would be....

gcq license fred 123123
gcq cd "Provider=sqloledb;Data Source=192.168.1.2,1433;Network Library...
gcq cl "Provider=sqloledb;Data Source=192.168.1.2,1433;Network Library...
gcq cp 99 192.168.1.1 This email address is being protected from spambots. You need JavaScript enabled to view it.

Note: Its perfectly ok to have the library and query DSN's the same.

Batch File format
=========================

A batch file can be used to create text based reports containing both template
text and data which is inserted at runtime. For example...

This is a batch file template
##q "select * from thistable"
the results above are from thistable
now we'll see what's going on with thattable
##l qrythattable
above is that table.

When the above batch file is parsed with the B command, the commands after
## will be executed and the results inserted into the output.
You may also use the following substitutions...

{date}
{time}
{datetime}
{username}
{version}

Which will be translated at runtime into the appropriate values. For example

Report of Sales for {date}
--------------------------
##l showsales
--------------------------
Report Generated by {username}

Appendix A
=========================

Colours for use with -cb/-cf
=======

0 :Black
1 :Blue
2 :Cyan
3 :DarkBlue
4 :DarkCyan
5 :DarkGray
6 :DarkGreen
7 :DarkMagenta
8 :DarkRed
9 :DarkYellow
10:Gray
11:Green
12:Magenta
13:Red
14:White
15:Yellow

 

Our 2022/2023 Referral Program

The majority of our new business comes from customer referrals, and that's awesome, but we wanted to reward those customers who refer us somehow. Traditionally, we did this by crediting their account with £25

...

yealink blockYealink have a wide range of IP Telephony endpoints with some impressive corporate features. Leveraging these features to maximum effect requires close integration with your IP Telephony Platform. At GEN we have been supplying Yealink for m

...

Every year, Congress must follow through on an enormous and complicated task: agreeing on how to fund the government for the following year. The wrangling over spending often comes down to

Apple are probably one of the best known technology manufacturers having been in the business for more than twenty years. GEN are both Apple Partners and Apple Developers investing heavily in Apple based products and services in house and w

...