## ODBC and SQL - NDB Cluster v2.11

NDB Cluster v2.11 includes a version of ODBC and SQL.

This document has 4 sections.

1. PLATFORMS
2. ODBC
3. SQL
4. DIAGNOSTICS

Features which are currently incomplete or planned for next release are marked with v2.x.

### 1. PLATFORMS

#### 1.1 Linux / Unix

We use RedHat package names to describe supporting software. Packages starting with perl- are perl modules. If your installation does not include them you can get them from a CPAN archive ( `ftp://ftp.funet.fi/pub/languages/perl/CPAN` ).

Version numbers are given only as examples. Other versions will work.

An ODBC driver manager is required, one of:

- unixODBC-2.2.3 (this is more common)
- libiodbc-3.0.5

Additional packages are convenient. Following include perl scripting interface and an "interactive SQL" tool dbish:

- perl-DBI-1.30
- perl-DBD-ODBC-0.43
- readline-4.2
- perl-Term-ReadLine-Gnu-1.11

The NDB ODBC driver is located under NDB Cluster installation directory and is named `libNDB_ODBC.so`. It includes NDB API. To use it create a text file `/etc/odbc.ini` or `$HOME/.odbc.ini` containing at least:

`[ndb]
Driver = <path-to-your-NDB-installation>/lib/libNDB_ODBC.so`

Then try the shell command `dbish dbi:ODBC:ndb` in an NDB API node directory.

#### 1.2 Windows

[ TODO - documentation ]

### 2. ODBC

#### 2.1 External data types

Usual external data types are supported and converted to and from SQL data types.

type | description
--- | ---
SQL_C_CHAR | character buffers
SQL_C_SLONG, etc | integer types
SQL_C_DOUBLE, etc | floating types
SQL_C_TYPE_TIMESTAMP | timestamp

#### 2.2 ODBC functions

The driver implements basic ODBC functions. Main exceptions are:

- no named cursors
- no scrollable cursors
- no bulk operations
- no asynchronous execution

Following lists main ODBC 3.0 functions and their status in the driver.

function | supported
--- | ---
SQLAllocHandle | yes
SQLConnect | yes
SQLGetInfo | yes
SQLGetFunctions | yes
SQLGetTypeInfo | yes
SQLSetConnectAttr | yes
SQLGetConnectAttr | yes
SQLSetEnvAttr | yes
SQLGetEnvAttr | yes
SQLSetStmtAttr | yes
SQLGetStmtAttr | yes
SQLGetDescField | yes
SQLGetDescRec | yes
SQLSetDescField | yes
SQLSetDescRec | yes
SQLPrepare | yes
SQLBindParameter | yes
SQLGetCursorName | yes, but cursor names cannot be used in SQL
SQLSetCursorName | yes, but cursor names cannot be used in SQL
SQLSetScrollOptions | not implemented
SQLExecute | yes
SQLExecDirect | yes
SQLNativeSql | not implemented
SQLDescribeParam | not supported
SQLNumParams | yes
SQLParamData | yes
SQLPutData | yes
SQLRowCount | yes
SQLNumResultCols | yes
SQLDescribeCol | yes
SQLColAttribute | yes
SQLBindCol | yes
SQLFetch | yes
SQLFetchScroll | not implemented
SQLGetData | yes
SQLSetPos | not implemented
SQLBulkOperations | not implemented
SQLMoreResults | yes, but multiple result sets are not supported
SQLGetDiagField | yes
SQLGetDiagRec | yes
SQLColumnPrivileges | not applicable
SQLColumns | yes
SQLForeignKeys | not applicable
SQLPrimaryKeys | yes
SQLProcedureColumns | not applicable
SQLProcedures | not applicable
SQLSpecialColumns | yes v2.x
SQLStatistics | not applicable
SQLTablePrivileges | not applicable
SQLTables | yes
SQLFreeStmt | yes
SQLCloseCursor | yes
SQLCancel | yes
SQLEndTran | yes
SQLDisconnect | yes
SQLFreeHandle | yes

### 3. SQL

#### 3.1 Data types

type | description
--- | ---
CHAR(n) | fixed-width blank-padded string
VARCHAR(n) | variable length string
INT INTEGER | integer 32 bits
BIGINT | integer 64 bits
DECIMAL(m,n) | exact number with precision and scale v2.x
REAL | float 32 bits
FLOAT DOUBLE PRECISION | float, at least 64 bits
DATE | date with precision 1 second v2.x
DATETIME | date with precision 1 nanosecond (SQL_TYPE_TIMESTAMP)

Integer types may be qualified as UNSIGNED.
Strings and numbers are not converted to each other automatically. Following is an error (unlike in oracle).

    select 123 + '456' from tab

#### 3.2 Expressions

syntax | description
--- | ---
NULL | null value
12.34e5 | integer or decimal or float constant
'abc' | string constant
+ - * / ( ) | arithmetic operations
|| | string concatenation v2.x

Integer and decimal arithmetic is done in BIGINT.
Floating arithmetic is done in DOUBLE PRECISION.
Numeric literals use largest applicable type.
String operations are done in CHAR or in VARCHAR (if any operand is VARCHAR).
String literals have type CHAR.

#### 3.3 Functions : non-aggregate

syntax | description
--- | ---
SUBSTR() LEFT() RIGHT() | substring
TO_NUMBER() TO_CHAR() | basic conversions v2.x
ROWNUM | row number in query
SYSDATE | current date as DATETIME

#### 3.4 Functions : aggregate

syntax | description
--- | ---
COUNT(*) COUNT(expr) | count rows or non-NULL values
MIN(expr) MAX(expr) | min and max of strings and numbers
SUM(expr) AVG(expr) | sum and average of numbers

GROUP BY and HAVING are supported.

#### 3.5 Predicates

syntax | description
--- | ---
IS NULL / IS NOT NULL | test if value is null
< <= = != > >= | comparisons
LIKE / NOT LIKE | string matching
AND OR NOT ( ) | boolean operators

#### 3.6 Tables

An NDB table requires a primary key. There are 2 ways to specify it.

#### Case 1

    create table t (
        a integer not null,
        b char(20) not null,
        c float,
        primary key(a, b)
    )

#### Case 2

A column can be specified as AUTO_INCREMENT. The column has following requirements.

- it must be the primary key (not just part of one)
- its type must be one of the integer types

    create table t (
        a int unsigned auto_increment primary key,
        b char(20) not null,
        c float
    )

The values of an AUTO_INCREMENT column are unique (until wrap-around) and form an ascending sequence. Gaps in the sequence are possible.

#### Default values

Columns can be specified with DEFAULT value which is used on insert if the column is not on the insert list.

    create table t (
        a int primary key,
        b int default 100
    )
    insert into t(a) values(1) -- inserts (1,100)

The value must evaluate to constant. Using SYSDATE (if allowed at all) evaluates to table creation time.

#### Logging / nologging

By default tables are created in logging mode, meaning the data is preserved across database restart. The mode can be specified explicitly:

`create table t1 (a int primary key, b int) logging`
`create table t1 (a int primary key, b int) nologging`

#### Schemas

Schemas do not exist in current NDB Cluster. As a convenience, a single period is allowed in table names:

    create table mydb.mytable (a int primary key)

#### Drop table

Deletes a table, all of its indexes, and all data:

`drop table t`

#### 3.7 Indexes

Only unique non-ordered indexes exist currently. The columns must be not nullable and are stored in same order as underlying table columns.

`create unique hash index x1 on t1(b, c) logging`

Internally, a unique hash index is a table where index key is primary key. If the index is `nologging`, it is rebuilt on database restart before the database is opened.

Indexes can of course be dropped:

`drop index x1`

#### 3.8 Select

Features:

- Expressions and predicates
  `select a + b * c from t where a < b + c and (b > c or c > 10)`
- JOIN to any depth
  `select a.x, b.y, c.z from t1 a, t2 b, t2 c where a.x + b.y < c.z`
- ORDER BY
  `select * from t1, t2 where a1 > 5 order by b1 + b2, c1 desc`
- DISTINCT
  `select distinct a, b + c from t`
- Aggregates without grouping.
  `select count(*), max(a), 1 + sum(b) + avg(c * d) from t`
- Aggregates with grouping.
  `select a, sum(b) from t group by a having sum(c) > 0 order by a, sum(d)`

Major omissions:

- no OUTER JOIN
- no subqueries and no EXISTS clause

Queries are optimized to minimize scans, by using primary keys and existing unique hash indexes. Simple predicates in scans (column compared to constant) are passed to an interpreter in NDB kernel. Joins are done via nested loops only.

- SCAN
  `select * from t where a < b`
- INTERPRETED SCAN (much faster)
  `select * from t1, t2 where t1.a < 10 and t2.b > t1.c`
- PRIMARY KEY lookup
  `select * from t where pk = 5 and b > 10`
- NESTED LOOPS
  `select * from t1, t2, t3 where t1.pk = t2.x and t2.pk = t3.y`

#### 3.9 Insert and write

Both VALUES and subquery variants can be used.

    insert into t(a, c) values (123, 'abc')
    insert into t1(a, c) select a + 10 * b, c from t2

For convenience, the non-standard MySql syntax is also supported.

    insert into t set a = 123, c = 'abc'

The non-standard operation WRITE is used exactly like INSERT. The record is updated if it exists. Otherwise a new record is inserted.

    write into t(a, c) values (123, 'abc')

#### 3.10 Update

Update allows no subqueries. Update is optimized to minimize scans and reads, by using primary keys and existing unique hash indexes.

- SCAN
  `update t set a = b + 5, c = d where c > 10`
- PRIMARY KEY or INDEX lookup
  `update t set a = b + 5, c = d where pk = 5 and c > 10`
- PRIMARY KEY or INDEX direct
  `update t set a = 5, c = 7 where pk = 5`

#### 3.11 Delete

Delete allows no subqueries. Delete is optimized to minimize scans and reads, by using primary keys and existing unique hash indexes.

- SCAN
  `delete from t where c > 10`
- PRIMARY KEY or INDEX lookup
  `delete from t where pk = 5 and c > 10`
- PRIMARY KEY or INDEX direct
  `delete from t where pk = 5`

#### 3.12 Virtual tables

The driver implements some virtual tables. They can only be queried, not modified.

- DUAL
  A 1-row table - example: select SYSDATE from DUAL.
- ODBC$TYPEINFO
  Corresponds to SQLGetTypeInfo.
- ODBC$TABLES
  Corresponds to SQLTables but shows all NDB kernel objects.
- ODBC$COLUMNS
  Corresponds to SQLColumns.
- ODBC$PRIMARYKEYS
  Corresponds to SQLPrimaryKeys.

### 4. DIAGNOSTICS

#### 4.1 Diagnostic records

The driver manager and driver return 3 main diagnostics (see `SQLGetDiagRec`).

- SQLSTATE (a string of 5 characters)
- Native error code
- Message text

Message text format is
`[Alzato][ODBC driver][NDB Cluster] NDB-ssccnnn error text (in SQLXxx)`
Here ssccnnnn is native error code (decimal number), with following parts:

- `ss` - status
- `cc` - classification
- `nnnn` - error code

  See NDB API guide for further information.

  For non-database errors the last prefix `[NDB Cluster]` is omitted and native error code is always 02015001.

  #### 4.2 Tracing

  Following environment variables may be useful.
  - `NDB_ODBC_TRACE`
    Values ≥ 2 cause SQL execution plan to be printed on standard output.
    Values ≥ 3 show the ODBC API functions called by the driver manager.
  - `NDB_ODBC_DEBUG`
    Non-zero value makes the driver abort the application on unhandled conditions.
    By default the ODBC API function is aborted gracefully.

  #### 4.3 Thread safety

  The driver has same thread-safety model as NDB API. In NDB API each thread must use its own `Ndb` object. In NDB ODBC a `SQLConnect` corresponds to an `Ndb` object. It is required that each thread allocates its own ODBC handles (of all types).

  #### 4.4 Data formats

  SQL types are represented as (old) NDB types as follows.

  SQL type | NDB type
  --- | ---
  CHAR(n) | String(n), blank-padded to n
  VARCHAR(n) | String(n+2), zero-padded to n, length in last 2 bytes (big-endian)
  integers | Signed(x) or UnSigned(x), x=16,32,64, native format
  floats | Float(x), x=32,64, native format
  DATETIME | String(12) = cc yy mm dd HH MM SS \0 ff ff ff ff (big-endian)

  Note: SQL types exist now in NDB API in `NdbDictionary` class. However they are not yet understood by NDB API operations.

  #### 4.5 NDB Cluster limitations

  - Isolation level is READ COMMITTED. A scan (non-primary-key select of several rows) does not see consistent data.
  - Inserting into a table from itself is likely to cause a deadlock or a random result.
    `no: insert into t(a, b) select a*100, b+100 from t`
  - Number of uncommitted rows is limited by NDB configuration parameter `MaxNoOfConcurrentOperations` (typical default 4096). To delete all rows from a large table one may need to do repeatedly:
    `delete from t where rownum < 4000`

  #### 4.6 Known problems v2.11

  Following lists specific known problems.

  - ORDER BY works only with expressions, not with column aliases or positions.
    `no: select a+b x from t order by x`
    `no: select * from t order by 1, 2, 3`
  - Join optimizer does not always minimize number of scans. Changing the order of tables in the statement may help.

  #### 4.7 Useful links

  Microsoft ODBC page
  unixODBC home page
  iODBC home page