nhutils

help

nhdbt command line

Connecting to a database in terminal mode

nhdbt.exe [-cs:<connection string>] [-do] [-cd:<column delimiter>]

The program connects to database using specified connection string and prompts for entering SQL query (the prompt string turns to SQL>). Enter a query and press Enter to execute SQL. Type exit to close the program.
If no connection string specified the program enters terminal mode without being connected to a database. To connect to a database user has to use one of CONNECT terminal commands:

  • connect "<connection string>" – connect to database using specified connection string
  • connect driver "<ODBC driver name>" "<ODBC driver options>" – connect to database using ODBC driver name and ODBC driver options
  • connect datasource "<источник данных ODBC>" – connect to configured ODBC data source by its name

The terminal mode requires manual exit and therefore does not suite for using in batch files.
An example of using nhdbt in terminal mode:

Connect to database and execute SQL query from command line argument

THis command configures connection string and SQL query to execute:

nhdbt.exe -cs:<connection string> [-do] [-cd:<column delimiter>] -q:<SQL queries>

The program connects to database using specified connection string and executes SQL queries one-by-one. After all the queries are executed the program disconnects from the database and closes. This way is perfect for using the program in bat/cmd batch files.

There’s no explicit limits on number of SQL queries. They just have to be separated with semicolon. The queries will be executed one-by-one in the same order they are listed in the command line.

Connect to database and execute SQL queries from text file

This command configures connection string and text file containing SQL queries:

nhdbt.exe -cs:<connection string> [-do] [-cd:<column delimiter>] -qf:<SQL file name>[,<code page>]

The program will firstly read SQL queries from the file then connect to database and execute all queries and after that disconnect from the database and then close. The intended usage of this kind of command are batch files.

If code page was not specified in the command line the program detects it automatically by file content and BOM (Byte Order Mark).

Connect to database and execute SQL queries from stdin

This command configures connection string and -qi option:

nhdbt.exe -cs:<connection string> [-do] [-cd:<column delimiter>] -qi

The program connected to the database and starts reading standard stream stdin. As soon as a query comes from stdin the program executes it. When the program detects stdin is closed it disconnects from the database and terminates. This behaviour is intended for batch files.

All command line arguments

-cs:<connection string>
-connection_string:<connection string>

Connection string to database. This option introduced in v1.5.0.

Connection string options depend on ODBC driver.

Connectionstring example for Microsoft dBase driver (*.dbf) (see also Microsoft dBase driver):

Driver={Microsoft dBase driver (*.dbf)};DBQ=.

-do
-data_only

Use this option to make the progam display only field data i.e. no column titles, no query text etc. This option was introduced in v1.5.0.

When this option is not set the program displays query text, column titles, query statistics etc.

This option intended for cases when the program output is forwarded to another program or script for automated processing.

-cd:<column delimiter>
-column_delimiter:<column delimiter>

A character to be column delimiter. This option was introduced in v1.5.0.

By default columns are aligned with spaces.Examples: -cd:"," or -cd:";" or -cd:"\t" for Tab.

-q:<SQL queries>
-query:<SQL queries>

Specifies SQL queries to execute. This option was introduced in v1.5.0.

Queries are delimited with semicolon (;) and executed one-by-one in the same order as listed in the command line argument.

The program does not limit query types i.e possible queries are DML (Data Manipulation Language – SELECT/INSERT/UPDATE/DELETE), DDL (Data Definition Language – CREATE TABLE/INDEX и т.д.), DCL (Data Control Language) etc because the program sends queries to database as they are.

Downloading or uploading files not supported in this version of nhdbt. If these are needed another utility nhdbf.exe can be used.

-qf:<file name>[,<code page>]
-query_from_file:<file name>[,<code page>]

Specifies text file containing SQL queries. This option was introduced in v1.5.0.

-qi
-query_from_stdin

Specifies the program has to read SQL queries from stdin. This option was introduced in v1.5.0.

The program does not limit number of SQL queries. SQL query can be written over several lines and has to be ended with semicolon.

nhdbt command line before v1.5

This command line was essential for nhdbt up to v1.4.1. Starting of v1.5.0 it is kept for backward compatibility purposes.

nhdbt [dataonly] [cs:<column delimiter>] [<ODBC driver>] [<connection details>] [<SQL file>]

Examples:

nhdbt dataonly cs:, "Microsoft dBase driver (*.dbf)" "DBQ=C:\Database" q1.sql
nhdbt "Microsoft Access driver (*.mdb)" "DBQ=C:\Database\db1.mdb;UID=admin;PWD=1234"

Terminal mode

In terminal mode you can work with databases interactively. Terminal mode commands:

    CONNECT <connection string>
    CONNECT DRIVER <ODBC driver name> <ODBC driver options>
    CONNECT DATASOURCE <ODBC data source name>
    DISCONNECT    disconnect from database
    EXIT          exit nhdbt and return to cmd
    HELP          show help 
    HELP <command>
                  show help on particular command
    LIST DRIVERS  list installed ODBC drivers
    LIST DATASOURCES       
                  list configured ODBC data sources

Downloading and uploading files with BLOB fields

Reading and writing SQL_LONGVARBINARY fields is done with nhdbf.exe (included with nhdbt).

Upload a file to BLOB field

The command:

nhdbf.exe <connection string> <запрос insert/update> <file path and name>
    <connection string> 
                  Connection string like:
                  "driver={Microsoft dBase driver (*.dbf)};dbq=C:\Temp"
    <запрос insert/update>
                  SQL query on INSERT or UPDATE with question mark (?) 
                  on place of SQL_LONGVARBINARY value. Example:
                  "insert into Files(id,file) values (1,?)"
    <file path and name>
                  File to upload.

INSERT example:

nhdbf.exe "driver={Microsoft dBase driver (*.dbf)};dbq=C:\Database" "insert into Files(id,file) values (1,?)" C:\Temp\file.dat

UPDATE example:

nhdbf.exe "driver={Microsoft dBase driver (*.dbf)};dbq=C:\Database" "update Files set file=? where id=1" C:\Temp\file12.dat

Only one file can be uploaded at a time.

Download BLOB value to a file

The command:

nhdbf.exe <connection string> <запрос select>
    <connection string> 
                  Connection string like:
                  "driver={Microsoft dBase driver (*.dbf)};dbq=C:\Temp"
    <запрос select>
                  SQL query on SELECT. First column has to be  
                  SQL_LONGVARBINARY, value of second column will make 
                  file path and name. 
				  Examples:
                  "select file,'file.dat' from Files where id=152"
                  "select Image,LastName+'.'+FirstName+'.jpg' from Employees"

Example – downloading a single file to C:\Temp\file.dat:

nhdbf.exe "driver={Microsoft dBase driver (*.dbf)};dbq=C:\Database" "select file,'C:\Temp\file.dat' from Files where id=1"

Example – downloading multiple files:

nhdbf.exe "driver={Microsoft dBase driver (*.dbf)};dbq=C:\Database" "select Image,LastName+'.'+FirstName+'.jpg' from Employees where Dept=4"

See also

download