FxGqlC 2.2 released

A new version of FxGqlC has been released.  There are many improvements, both in terms of performance and capabilities. 

Check it out on: https://sites.google.com/site/fxgqlc/home , and give it a try.

And many more things are in the pipeline, so come back in a few weeks for the next version.

The most important new features are:

• Change the working directory with the USE statement.  Similar to the cd/chdir commands in command prompts. USE [c:\temp]USE [../subdir]USE ['sub directory']

• Added support for variables.  Setting variables in select output (e.g. select )is not yet support
DECLARE @var string
SET @var = 'US' + ' ' + 'Open'
SELECT [Winner] FROM ['Tennis-ATP-2011.csv' -heading=on] WHERE [Tournament] = @var AND [Round] = 'The Final'
• System variable $filename: Returns current filename (without path).  The implementation of this system variable has been changed. Before v2.2, the full filename was returned (same behavior as current system variable $fullfilename).
  SELECT DISTINCT $filename FROM ['SampleFiles\*' -recurse]-- Returns:     AirportCodes.csv     AirportCodes.csv.zip     AirportCodesTwice.zip     CountryList.csv     IP2Country.csv.zip     Tennis-ATP-2011.csv
       AirportCodes2.csv     AirportCodes2.csv.zip

• System variable $fullfilename: Current full filename (with complete absolute path).
  This variable is only valid in the context of a running query.SELECT DISTINCT $fullfilename FROM ['SampleFiles\*' -recurse]-- Returns:     C:\Data\SampleFiles\AirportCodes.csv     C:\Data\SampleFiles\AirportCodes.csv.zip     C:\Data\SampleFiles\AirportCodesTwice.zip     C:\Data\SampleFiles\CountryList.csv     C:\Data\SampleFiles\IP2Country.csv.zip     C:\Data\SampleFiles\Tennis-ATP-2011.csv     C:\Data\SampleFiles\SubFolder\AirportCodes2.csv     C:\Data\SampleFiles\SubFolder\AirportCodes2.csv.zip

• Added FROM-clause options '-Heading=On', '-Heading=OnWithRule' and '-Heading=Off' (default).
  SELECT [Winner] from ['Tennis-ATP-2011.csv' -heading=on]     WHERE [Tournament] = 'US OPEN' AND [Round] = 'The Final'-- Returns:     Djokovic N.

  
  

• Added possibility to show column headers in output, using !SET HEADING
  !SET HEADING OFFSELECT [Winner] FROM ['Tennis-ATP-2011.csv' -heading=on]     WHERE [Tournament] = 'US OPEN' AND [Round] = 'The Final'-- Returns:     Djokovic N.
  
  !SET HEADING ONSELECT [Winner] FROM ['Tennis-ATP-2011.csv' -heading=on]      WHERE [Tournament] = 'US OPEN' AND [Round] = 'The Final'-- Returns:     Winner     Djokovic N.
  !SET HEADING ONWITHRULESELECT [Winner] FROM ['Tennis-ATP-2011.csv' -heading=on]      WHERE [Tournament] = 'US OPEN' AND [Round] = 'The Final'
  -- Returns:     Winner     ======     Djokovic N.

• The -Heading option can also be used in the INTO-clause:
  SELECT [Winner]      INTO ['US OPEN Winner.txt' -heading=onwithrule]     FROM ['Tennis-ATP-2011.csv' -heading=on]      WHERE [Tournament] = 'US OPEN' and [Round] = 'The Final'
• Added support for VIEWs:
  CREATE VIEW Tennis AS
       SELECT [Tournament], [Winner]
            FROM ['Tennis-ATP-2011.csv' -heading=on]
            WHERE [Round] = 'The final'
  SELECT * FROM Tennis
  DROP VIEW Tennis
• Added support for parameterized VIEWs:
  CREATE VIEW Tennis(@file string, @round string) AS
       SELECT [Tournament], [Winner]
            FROM [@file -heading=on]
            WHERE [Round] = @round
  SELECT * FROM Tennis('Tennis-ATP-2011.csv', 'The final')
  DROP VIEW Tennis
• Added support for count(*) as alternative to count(<expression>):
  SELECT count(*) FROM ['Tennis-ATP-2011.csv' -heading=on]
• Added support for count(distinct <expression>) to count unique values:
  SELECT count(distinct [Tournament]) FROM ['Tennis-ATP-2011.csv' -heading=on] 
• Block comments are now also supported.
  SELECT distinct [Tournament] /* block comment */ FROM ['Tennis-ATP-2011.csv' -heading=on] -- line comment
• Added support for option -columndelimiter in FROM-clause and INTO-clause.  Until now, the tab character "\t" was always used as delimiter, which is still the default. The string specified is unescaped using the RegularExpression syntax (e.g. \t becomes a tab character).SELECT [Date], [Winner]
       INTO ['output.txt' -columndelimiter=';']
       FROM ['Tennis-ATP-2011.csv' -heading=on]
       WHERE [Tournament] = 'US OPEN' AND [Round] = 'The Final'
  -- Output.txt contains:
       12/09/2011;Djokovic N.
• HAVING-clause: Add a filter that is applied AFTER the GROUP BY aggregation.
  SELECT [Winner], count(*) FROM ['Tennis-ATP-2011.csv' -heading=on] GROUP BY [Winner] HAVING count(*) > 60
  
  
• Added support for "alias" in FROM-clause, which makes it possible to link subquery columns to outer query columns.
  SELECT [Date], [Tournament], [Winner],      (     SELECT count(*)           FROM ['Tennis-ATP-2011.csv' -heading=on] [inner]           WHERE [outer].[Winner] = [inner].[Winner]     )      FROM ['Tennis-ATP-2011.csv' -heading=on] [outer]      WHERE [Round] = 'The Final'

• A startup script file is automatically executed when FxGqlC.exe is started in command mode (-c, -command), in file mode (-gqlfile) or in prompt mode (-p, -prompt).  This can be useful to create regularly used views or variables, or to execute any comand such as USE or SET.  The startup script file path can be configured using the startup option -autoexec <filename>.  When the startup option -autoexec is not present, the default startup script file "autoexec.gql" is searched, first in the current directory and then in the directory where FxGqlC.exe is located.