Menü  LinguVersa icon 

LinguVersa Qex

Anouncement:

You can find  qx   the successor of qex on github. It has been completely new developed, is platform independent and depends only on ODBC and the c++ std library. Although it does not yet cover all features of qex it rapidly catches up.

This is the home of LinguVersa Qex, a simple and yet powerful database tool completely on the command line.

Usage:

Simply include the path to qex.exe into your PATH environment variable and you can use it in your command line windows. Of course you can use qex.exe in batch files, too.

Download current version

Simple syntax:

You can get an elementary syntax help also by:
  • qex --help

Complete syntax:

<connectionstring> can be of form:

  • "DSN=myDsn; Database=dbname; Uid=myUsername; Pwd=myPassword;"
  • "FILEDSN=c:\myDsnFile.dsn; Uid=myUsername; Pwd=myPassword;"
  • "Driver={SQL Server}; Server=MyServerName; Database=myDatabaseName; Uid=myUsername; Pwd=myPassword"
  • "Driver={SQL Server}; Server=MyServerName; Database=myDatabaseName; Trusted_Connection=Yes;"
  • "Driver={Microsoft Text Driver (*.txt; *.csv)}; Dbq=<dirpath>; Extensions=asc,csv,tab,txt;"
  • "Driver={Microsoft dBASE Driver (*.dbf)}; DriverID=277; Dbq=<dirpath>;"
  • "Driver={Microsoft Access Driver (*.mdb)}; Dbq=<full-path-to-db>; Uid=Admin;Pwd=;"
  • "Driver={SQLite3 ODBC Driver}; Database=<full-path-to-db>; ..."
  • "Driver={MariaDB ODBC 3.0 Driver}; Server=myServer; Port=3306; charset=UTF8; Database=myDataBase; User=myUsername; Password=myPassword; Option=3;"
  • "Driver={MySQL ODBC 5.3 ANSI Driver}; Server=myServer; Port=3306; charset=UTF8; Database=myDataBase; User=myUsername; Password=myPassword; Option=3;"
  • "Driver={PostgreSQL ANSI}; Server=MyServerName; Port=MyPort; Database=myDatabaseName; Uid=myUsername; Pwd=myPassword;"
  • "Driver={IBM INFORMIX ODBC DRIVER}; Host=myHost; Server=myServerAddress; Service=myPort; Protocol=onsoctcp; Database=myDatabaseName; Uid=myUsername; Pwd=myPassword"

These are only a few examples for connection strings to some widespread database engines. You will find many more for other engines, too, if you search the world wide web for "odbc connection string <name of your database engine>".

Please understand that we do not deliver any ODBC drivers, LinguVersa Qex only makes use of them. But most database manufacturers offer an ODBC driver for their product or at least a hyperlink to a website where you find third party drivers. You will have to download the appropriate driver for your database from there.

Normally you will specify the connection string of your database as a command line parameter. But if you want to execute several sql statements subsequently or you have one large sql statement it may be easier to define the source connection with the environment variable QEXCONNECTION:

As long as this environment variable is defined you do not need to specify the connection string in the following qex commands.
You should be aware to close your command line window, when you leave your computer unattended, because anyone with access to your command line history or the QEXCONNECTION environment variable can find out your connection string and password.

File based databases:

For simple file based databases without a server engine you do not need to explicitly specify a complete connection string. Instead you specify the path to the database folder or file.

Syntax:

<dirpath> is the relative or absolute path to a folder containing files with table data. The folder may include either plain text files or files in dbase format or a format derived thereof.
If the folder includes plain textfiles you may specify it with the command line parameter -dir=<dirpath> or with the command line parameter -sourcepath=<dirpath>
If the folder includes files in dbase format or a format derived thereof you must specify it with the command line parameter -dbase=<dirpath>

<datafilepath> is the relative or absolute path of a file containig table data. This may be a single file with the extension dbf or csv or txt.

<full-path-to-db> is the relative or absolute path of a file containing the complete db. This path can include the folder and must include the file name and extension of the database file. Typical examples are Access databases with the extension mdb or SQLite3 databases with the extension db3. If a SQLite file has another extension you must use command line parameter -sqlite3=<full-path-to-db> to specify the type of database.


<target> can be:

  • "stdout"
    The standard output. This is the default as long as no other target is specified, but it can also be specified explicitly.
  • "clipboard"
    All output will be inserted into Windows' clipboard as plain text.
  • "file:<local file path>"
    The output will be appended to that file.
  • "ODBC;..."
    A complete connection string (see above) directing to the same database as source or any other data source.
    In this case <output-format> must specify a sql command in a syntax the target db understands.
    Of course target can be of a completely different database type. Thus LinguVersa Qex is convenient to migrate data from one database to another.

output-options can be:

  • [-format=<output-format>]
    This simple form allows a quick and simple output for each row of the resultset. Each <output-format> should contain an explicit \n at the end to produce a carriage return after each row.
  • [-createinsert <table-name>] | [[-create <table-name>] [-insert <table-name>]]
    This second form is very convenient if you want to extract data from one data source to sql-scripts or even to enter it into another database specified as connection string by the previous <target> definition. All you have to specify is the name of the table you want to create or insert into.
    Note that table-name is not necessarily the name of a table in the source database. The <sql-statement> can even include several tables with appropriate joins.
  • [-group=N] [-firstrow=<output-format>] [-eachrow=<output-format>] [-lastrow=<output-format>] ...
    With this definition it is even possible to define a grouped output of hierarchical structures, for instance into an xml file.
    N is then a 32-bit value in decimal notation. By default each bit refers to a column of the resultset where the least significant bit (1) refers to column 0, the second (2) to column 1, the third (4) to column 2, the fourth (8) to column 3 and so on.
    Whenever, while iterating through the resultset, a column value is different to the value of the same column in the previous row this is taken as a group change for all groups with the respective bit set. Then the lastrow output-format for the previous row is written to the target and the firstrow output-format for the current row is written to the target.
    If the group flag is specified by -group=0 firstrow and lastrow refer to the the dataset as a whole.

If no output-option is given and the specified statement yields any results LinguVersa Qex gives out a header row with the column names separated by <TAB> characters and terminated by <Carriage-Return> and afterwards for each row in default format the data fields separated by <TAB> characters and terminated by <Carriage-Return>, too.

<output-format> is a string which can include any text.

Before writing this text to the target LinguVersa Qex will do the following replacements.

Important! In this section the rectangular brackets [ ] are part of the format. They must be entered to indicate that their inside is a result column of the query and not a literal!

  • [<column-name>]
    will be replaced by the value of that column in standard format. Null-values will be replaced by an empty string.
  • [<column-name>:<column-format>]
    will be replaced by the value of that column in the specified C / C++ -like format (see below). Null-values will be replaced by an empty string.
  • [<column-name>|<literal>]
    will be replaced by the value of that column in standard format. Null-values will be replaced by the specified literal.
  • [<column-name>:<column-format>|<literal>]
    will be replaced by the value of that column in the specified C / C++ -like format (see below). Null-values will be replaced by the specified literal.
  • \n
    will be replaced by a <Carriage-Return>.
  • \t
    will be replaced by a <TAB> character.

Usually each <output-format> should contain an explicit \n at the end to make the output better readable.

<column-format> should be a C / C++ -like format specification

It must fit the data type of the respective result column. That is for example

  • for columns of type int or long in decimal notation
  • %d%0d or %nd or %0nd
    where n is the number of characters which should at least be used. If the number does not fit into the n characters the replacement string will be extended to the length needed.
  • for columns of type decimal(m,n) or float or double
    %f or %m.nf or %m,nf
    where m ist the total number of digits to be used and n is the number of digits after the decimal separator to be used. Other as in C / C++ you can specify the decimal separator to be used by putting either the character . or the character , between m and n.
  • for character or varchar columns
    %s or %ns or %m,ns
    where n is the number of character at least to be used. If the string from the result column is shorter than the specified length it will be filled up with blanks. If it is longer and m is not specified the string will be truncated after n characters. If it is longer and m is specified it will be truncated after m characters.
  • for columns of type datetime or smalldatetime the column format can be specified by a string containing the placeholders
    • YYYY which will be replaced by the four digit year
    • YY which will be replaced by the the last two digits of the year
    • MM which will be replaced by two digits for the month
    • DD which will be replaced by two digits of the day-of-month
    • hh which will be replaced by two digits for the hour-of-day
    • mm which will be replaced by two digits for the minutes
    • ss which will be replaced by two digits for the seconds
    • . followed by one or more times the digit 9 which will be the fractional part of the seconds.
      The maximum granularity here is defined by ODBC as nine times the digit 9 meaning billionth seconds.
      Note that this is usually not the the granularity the database engine is able to resolve.

Be careful with column-format specifications, as LinguVersa Qex will terminate with an exception if the specified format does not fit the datatype of the result column.

Special features:

LinguVersa Qex can also create or modify Named Data Sourses (DSN) by specifying a dsn-option:

  • -configuserdsn=<data source name>
    If a user data source of that name does not yet exist, it will be created according to the driver and options within the current connection string. Otherwise an existing user dsn will be modified accordingly.
    If the windows user executing qex.exe does not have write access to the registry tree beneath HKEY_CURRENT_USER\Software\ODBC\ODBC.INI, the program exits with an %ERRORLEVEL% different from 0.
    Normally windows user do have write access there.
  • -configsystemdsn=<data source name>
    If a system data source of that name does not yet exist, it will be created according to the driver and options within the current connection string. Otherwise an existing system dsn will be modified accordingly.
    If the windows user executing qex.exe does not have write access to the registry tree beneath HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI, the program exits with an %ERRORLEVEL% different from 0.
    Normally windows users do not have write access there unless the process is started with administrative privileges.
  • -configfiledsn=<dsn file path>
    A dsn file will be created or modified according to the driver and options within the current connection string. It can be specified with a relative or absolute path. If the file path contains no extension the extension .ext will be appended.
Creating and extending schema.ini for plain textfiles:
  • -addschema=<textdatafilepath>
    Creates or appends to a file schema.ini a stub for the current resultset. The section name is the filename (including file extension but excluding its directory path) of <textdatafilepath>. The folder of the schema.ini file is either the directory of <textdatafilepath> or the current working folder if  <textdatafilepath> doesn't contain a directory part.
  • -config=<configuration name>
    When querying a plain text file and there is a file qex.ini and it contains a section named [QueryConfig\<configuration name>\schema] then this section is a template for the section of the text file's name in the schema.ini file. By this mechanism you can specify one schema for a multitude of text files which share the same format.

FAQ

  • What is LinguVersa Qex?
    LinguVersa Qex is a command line tool for accessing SQL databases.
  • What does Qex mean?
    LinguVersa Qex is an abbreviation for LinguVersa Query Exexcutor.
  • What is special about LinguVersa Qex?
    While most command line programs  are only suited for their respective backend LinguVersa Qex uses the ODBC standard libraries to retrieve data and metadata. Together with its easy to learn output formatting syntax this enables you to retrieve data from many different databases in the format you need.
  • For which databases is it suited?
    You can use LinguVersa Qex on practical every database engine if there is an ODBC driver available. Since ODBC has evolved into a longtime established industrial standard there exists an ODBC driver package for nearly every relational database. Microsoft delivers Windows also with drivers for Excel and many text based files such as comma-separated-values (CSV), tabulator-separated-values and fixed-length-field files.
  • What is the client platform?
    While the database can be on any server with any operating system, to which an ODBC connection can be established, the LinguVersa Qex client is restricted to the Windows operating system.
  • What is the technology behind?
    LinguVersa qex is written in C++. It relies on Microsofts MFC library and encapsulates the ODBC API in LinguVersa's LwQuery class.
  • How do I specify my database?
    You have to specify the connection string. This can be done with the command line parameter -s .
    If you want to do several queries subsequently it might be more convenient to define an environment variable QEXCONNECTION, e.g. with the set command for the lifetime of the current command shell.
    Another possibility is to include the connection string in an ini file, named qex.ini. The file qex.ini can be located in the current workdir. This is especially convenient, if you are dealing with text base files. If you frequently switch your workdir but want to continue using the same data source it may be more convenient to locate your qex.ini file in one fixed folder. This may be %HOME%\qex.ini if you have an environment variable HOME defined to specify a special folder or this may be %TEMP%\qex.ini in your personal temporary folder. If you do so be aware that specifying your database user password in the connection string is dangerous, since everyone with read access to the ini file will be able to read your database password even after you logged out. 
  • How can I retrieve the data?
    If you are familiar with SQL it is fairly simple: the whole sql query is just a command line parameter of LinguVersa Qex. This enables you to use it also in cmd batch files.
  • How do I format the output?
    If you need a special output format just add another command line parameter -format with the format you want. If you omit the parameter you will get column headers in the first line of the output and a line with the data of each row of the query result. For migrating content from one database system to another you can also get the create and/or insert statements you need with the special command line parameters -create <target tablename>, -insert <target tablename>, or combined -createinsert <target tablename>.
  • Is there any restriction regarding to the type of statements I can use?
    No. You can simple select statements on one table, you can use select statement with joins on several tables, you can modify data with insert, update and delete, and you can call data definition statements such as create table and drop table etc.
  • How much does it cost?
    LinguVersa Qex can be downloaded and used free of charge. It has been designed with all proper care and attention. However, we must emphasize that we cannot be held responsible for any loss or destruction of data by using LinguVersa Qex.