Software & Systems Development

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. 

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)

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

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

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

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

 

As the market slowly shifts from proprietary closed source systems to a more open source ecosystem GEN will always be ahead of the trend. We've been supplying and supporting open source for the last decade and will continue to invest in open source support and development in the years to come. We already have dedicated MySql, MariaDB, Joomla and vTiger developers on staff and we run the majority of our in house systems on CentOS Linux. We have a strong open source customer base and will continue to develop that market. 

If you are considering replacing your windows workstations with linux, or migrating your online presence to an open source platform, or would like a test server setup for evaluation then contact us today for some advice and support. 

Systems currently Deployed and Supported

• Joomla / Wordpress
• Magneto
• Drupal
• MySql/MariaDB
• Ubuntu / CentOS / Debian
• Asterisk 14/15

We also support Docker Nodes to allow shared hosting of open source systems within the Docker ecosystem. 

 

 

 

 

We've spent many years working with many different OEM's and integrators designing and developing embedded systems for everything from household appliances to military communication satellites.

Our developers work in C, C++, C# or assembly language as required and specified.

With a lengthy experience of ATMEL, Microdesigns, National Semiconductor, Hitachi and Motorola processors we've got the knowledge base to take your protects to completion. W're also experienced in vehicle embedded systems such as QNX and other proprietary systems delivering applications for smartphone synchronisation, messaging, instrumentation, performance logging and offload, over the air deployment, tracking and intervention and many more. We also partner with DigitalResearch who are able to take on the design, development and testing of microelectronics with our embedded software providing a one-stop collaborative environment for your product. 

 

How cool would it be if you could email your system asking for some data and receive a reply with that data moments later? Well, that's Email Automation and its just one of the processes we specialise in. Email based automation leverages your email client to provide on-demand business and process data. 

Let's take a real world example, GEN uses email automation to interface with our CMS system. A member of staff can email the system and request, for example, customer contact details for a given customer and moments later have that information sent back to them - brillaint!. 

We already have a resilient and proven AutomationEmail Engine (AE) that is compatable with Windows Server & Linux and provides a framework to process commands via email into 

• SQLtoCSV - Take a Query and convert output to CSV and return as attachment. 
• SQLtoHTML - Take a Query and convert output to HTML tables and return. 
• SQLtoTXT - Take a Query and convert output to flat format text and return. 
• CMDtoTXT - Execute a command, capture the response and return. 
• FiletoTXT - Read a file and return as email body or as attachment. 
• DLLtoTXT - Call a .dll function and return the data as body or attachment. 
• TexttoHTML - Take a given ASCII response and convert to HTML tables

and Many more data translators, and of course custom translators are always welcome. 

Leveraging email automation is just another part of workflow and process automation which we guarantee will save you both time and money so why not contact us today to discuss your requirements? 

 

We started back in the 90's with EDI over X400 interfacing disparate systems for supply chain management and over the years we've build a specialism in being able to exchange data between systems that don't naturally wish to do so. The process of developing data interfaces is often a long one especially when your trying to move data in both directions and from systems that are decades apart. A good example would be exchanging data from a HP9000 running HPUX and a Windows Server running SQL2014 - the process is both complex and risky, but it can of course be done and we've done it. Our data connectors are custom built to be reliable and highly secure methods of interchanging data. 

So if you have one systems that you need to talk to another one then give us the chance to prove just how good we are at making that work and drop us a message? 

 

vTiger CRM is one of the few systems that is not only totally flexible and customizable but is also open source. The power of open source gives you complete control over your investment and the unhindered ability to mould it to your needs. vTiger is written in PHP and runs on Linux providing a rock solid platform for long term installations.  We have used vTiger in house for the last decade and have integrated it to all our provisioning systems, billing, connectivity, quote portals and so on. With vTiger's powerfull workflows we can automate the majority of the day to day processing of orders, quotes, invoices, collections etc.  Our developers are continually working to expand the feature set and interoperability of the system and we offer a full range of options from monthly/user to dedicated servers and your own custom installation. vTiger CRM is eligible for service and support options up to 1 hour response 24 hours a day 365 days a year.

 

 There are simply too many features to list in this short article, but rest assured there is nothing that vTiger cannot do for you. The real power of vTiger is that you don't need to use all the features, you can pick and choose the ones that work for you. 

In our environment, sales leads generated from the website(s), agents and referrals come into the lead section of vTiger, our workflows automatically allocates the lead to a sales advisor who then processes that into a quote or sale. The arrival of a sale generates another workflow that provides services or equipment making intelligent choices based on stock levels. Once services are provisioned or hardware shipped then another workflow generates invoicing, and yet another workflow sends that to the customer. Automation is the key here, we don't have anyone pushing paper around the office, we don't even own a photocopier anymore!

Let us show you the true power of vTiger by completing the contact form, in the knowledge that your contact request will be fulfilled by some vTiger workflows! 

Whatever your requirements, our experienced developers can provide. We have on staff developers in ASP, ASP.NET, C#, Java, Javascript, PHP and Swift. 

The Microsoft .Net Framework provides a stable and functional developement platform for VB and C# as well as the compact Framework for mobile applications. We have Virtual Development Environments for .Net 2 through to 4.5 and our Developers are experienced in all aspects of Microsoft Developement Environments. 

Whether your looking for Java compiled applications or thin client based on TomCat, we have the experience and platforms to provide it. 

When it comes to thin client applications based on HTML5 and PHP we're one of the best software houses having developed numerous large scale systems based on this configuration. PHP is one of those languages that is written solely for server side processing and gives rapid development times as compared to other configurations. 

Swift, which has now gone OpenSource provides the framework for Developing native applications for Apple Based Devices and Operating systems such as OSX and IOS. We're experienced Developers for Apple handset applications.