SQL Client Reference

ODBC Manual

The ODBC Driver and command-line interface (CLI) program enable you to connect to Ocient using an ODBC connection. See the setup steps to initialize the Ocient ODBC Driver for connection. Then, explore the supported commands to use with the ODBC CLI program.

Setup

The unixODBC Driver Manager package must first be installed and configured before using the Ocient ODBC driver.

  • If the unixODBC package isn’t already installed on the system, install it now as follows:
  • Locate the odbcinst.ini and odbc.ini files for your installation by issuing the odbcinst -j command as follows.
Shell

  • Add the following lines to the odbcinst.ini and odbc.ini files respectively.
Shell

Shell


Both files can have an optional "Description" field as well. For more information go to http://www.unixodbc.org/odbcinst.html.

The rules the Driver Manager uses for locating the two files are as follows. Place them in one of these locations as required.

Shell

  • If either of these two environmental variables are set, they will be searched first during database connection requests.
  • If a User Data Source file exists in $HOME/.odbc.ini, it will override any duplicate parameters found in the System Data Source file. (Note this is a hidden file)
  • The final location searched for these files is the unixODBC installation directory.

Invoking the Ocient ODBC CLI Program

The command-line interface can be invoked as follows:

Shell


The user can supply no credentials, a username only, or a username and password. Throughout this document, <angle brackets> indicate name parameters chosen by the user and [square brackets] indicate that the enclosed text is optional. Parentheses are literal.

If a username or password is not provided, the user will be prompted to enter it on startup.

Supported Commands

Commands are not case sensitive. All commands, except QUIT, must end with a semicolon. The following commands are supported.

For DDL statements, see Data Definition Language (DDL) Command Reference.

CANCEL

Cancels the current query that is still running on the system.

Syntax

Shell


Parameter

Data Type

Description

uuid

String

A query uuid that is currently running. Use LIST ALL QUERIES or search the sys.queries table to find the uuid.

CONNECT

Connects to an Ocient database using the parameters specified in an obdc.ini file. For more information, see the ODBC Manual section.

Syntax

Shell


If you do not specify the username and password, then the database uses the default credentials provided by the user at startup.

Parameter

Description

dsn

The value for the Ocient ODBC driver located inside the square brackets in the odbc.ini file, as explained in the Setup section.

The CONNECT command uses the HOST and PORT values specified in the odbc.ini file. The application will attempt to connect to each IP address associated with the hostname until one succeeds or all fails. The first host will be paired with the first port, second host with second port, etc.

Multiple hosts and ports can be given in odbc.ini, as shown in this example.

Shell


Be sure to avoid any spaces after commas.

DESCRIBE TABLE

Lists the columns of the specified table and its associated data types.

Syntax

Shell


Parameter

Description

table

A table identifier

schema

A qualifying schema name for the specified table.



If a qualifying schema is not given for the table, the default name schema will be assumed.

verbose

If specified, the system prints the full column metadata for the given table as returned by SQLColumns.

DESCRIBE VIEW

Returns the query text used to create the specified view.

Syntax

Shell


Parameter

Description

view

A view identifier.

schema

A qualifying schema name for the specified table.



If a qualifying schema is not given for the table, the default name schema will be assumed.

verbose

If specified, the system prints the full column metadata for the given table as returned by SQLTables.

KILL

Issues a hard kill command to the virtual machine to terminate a query that is running on the system. Use only if CANCEL fails.

Syntax

Shell


Parameter

Description

uuid

A query uuid that is currently running. Use LIST ALL QUERIES or search the sys.queries table to find the uuid.

LIST ALL QUERIES

List all the current running queries across all the cmdComp servers. This is the equivalent of running SELECT * FROM sys.queries on all cmdComp servers.

Syntax

Shell


LIST INDEXES

Lists the indexes on the specified table and its columns.

Syntax

Shell


Parameter

Description

table

A table identifier.

schema

A qualifying schema name for the specified table.



If a qualifying schema is not given for the table, the default name schema will be assumed.

verbose

If specified, the system prints the full column metadata for the given table as returned by SQLStatistics.

LIST SYSTEM TABLES

Lists all system catalog tables in the database.

Syntax

Shell


Parameter

Description

verbose

If verbose is specified, the full table metadata as returned by SQLTables is printed.

LIST TABLES

Lists all tables (in all schemata) in the database.

Syntax

Shell


Parameter

Description

verbose

If verbose is specified, the full table metadata as returned by SQLTables is printed.

LIST TABLE PRIVILEGES

List the privileges for all the tables. This does a join on the sys.tables and sys.privileges tables.

Make sure forceNetworkedVirtualTables is false if running on local machine.

Syntax

Shell


LIST TABLES VIEWS

Runs the LIST TABLES command first, followed by the LIST VIEWS command.

Syntax

Shell


Parameter

Description

verbose

If verbose is specified, the full metadata is printed.

LIST VIEWS

Lists all views (in all schemata) in the database.

Syntax

Shell


Parameter

Description

verbose

If verbose is specified, the full table metadata as returned by SQLTables is printed.

LIST VIEWS TABLES

Runs the LIST VIEWS command first, followed by the LIST TABLES command.

Syntax

Shell


Parameter

Description

verbose

If verbose is specified, the full metadata is printed.

PERFORMANCE

Enables or disables suppressing query result set output. Intended for use during benchmarks. If enabled, the query execution time is the only output that the system returns.

Syntax

Shell


QUIT

Closes the command-line interface.

Syntax

Shell


SELECT

Retrieves the appropriate result set of a SQL SELECT statement.

Syntax

Shell


SET PSO

Sets the cost threshold for running PSO to the given integer, which is the estimated cost in milliseconds.

It can also be reset to the default value by instead using the ON keyword, or disabled entirely with OFF. The default is the same PSO setting used when initializing the cluster.

Syntax

Shell


Parameter

Description

integer

The cost threshold for PSO, in milliseconds.



Providing a negative value disables PSO entirely

SOURCE

Reads and executes commands found in the specified file.

Syntax

Shell


Parameter

Description

filename

A valid file name or path.

TIMING

Enables or disables reporting the execution time of each query.

Syntax

Shell


Supported C/C++ ODBC Functions

The following ODBC functions are supported by the Ocient ODBC Driver for use in C/C++ programs.

SQLAllocHandle SQLBindCol SQLBindParameter SQLBrowseConnect SQLCloseCursor SQLColAttribute SQLColumns SQLColumnPrivileges SQLConnect SQLCopyDesc SQLDescribeCol SQLDisconnect SQLExecDirect SQLExecute

SQLFetch SQLFreeHandle SQLFreeStmt SQLGetConnectAttr SQLGetCursorName SQLGetData SQLGetDescField SQLGetDescRec SQLGetDiagField SQLGetDiagRec SQLGetEnvAttr SQLGetInfo SQLGetStmtAttr

SQLGetTypeInfo SQLMoreResults SQLNumParams SQLNumResultCols SQLPrepare SQLSetConnectAttr SQLSetCursorName SQLSetDescField SQLSetDescRec SQLSetEnvAttr SQLSetStmtAttr SQLStatistics SQLTables SQLTablePrivileges