ODBC and SQL

ODBC and SQL - NDB Cluster v2.11

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

This document has 4 sections.

PLATFORMS
ODBC
SQL
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

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

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)

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