diff options
Diffstat (limited to 'ndb/src/old_files/client/odbc/docs/ndbodbc.html')
| -rw-r--r-- | ndb/src/old_files/client/odbc/docs/ndbodbc.html | 659 |
1 files changed, 0 insertions, 659 deletions
diff --git a/ndb/src/old_files/client/odbc/docs/ndbodbc.html b/ndb/src/old_files/client/odbc/docs/ndbodbc.html deleted file mode 100644 index 6be624dfa1b..00000000000 --- a/ndb/src/old_files/client/odbc/docs/ndbodbc.html +++ /dev/null @@ -1,659 +0,0 @@ -<HTML> -<HEAD> -<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1252"> -<TITLE>ODBC and SQL</TITLE> -</HEAD> -<BODY LINK="#0000ff" VLINK="#800080" BGCOLOR="#ffffff"> - -<h2>ODBC and SQL - NDB Cluster v2.11</h2> - -<p> -NDB Cluster v2.11 includes a version of ODBC and SQL. -<p> -This document has 4 sections. -<ol> -<li>PLATFORMS -<li>ODBC -<li>SQL -<li>DIAGNOSTICS -</ol> -<p> -Features which are currently incomplete or planned for next release -are marked with <b>v2.x</b>. - -<h3>1. PLATFORMS</h3> - -<h4>1.1 Linux / Unix</h4> -<p> -We use RedHat package names to describe supporting software. -Packages starting with <b>perl-</b> are perl modules. -If your installation does not include them you can get them -from a CPAN archive ( <tt><b>ftp://ftp.funet.fi/pub/languages/perl/CPAN</b></tt> ). -<p> -Version numbers are given only as examples. -Other versions will work. -<p> -An ODBC driver manager is required, one of: -<ul> -<li>unixODBC-2.2.3 (this is more common) -<li>libiodbc-3.0.5 -</ul> -<p> -Additional packages are convenient. -Following include perl scripting interface -and an "interactive SQL" tool <b>dbish</b>: -<ul> -<li>perl-DBI-1.30 -<li>perl-DBD-ODBC-0.43 -<li>readline-4.2 -<li>perl-Term-ReadLine-Gnu-1.11 -</ul> -<p> -The NDB ODBC driver is located under NDB Cluster installation -directory and is named <tt><b>libNDB_ODBC.so</b></tt>. -It includes NDB API. -To use it create a text file -<tt><b>/etc/odbc.ini</b></tt> or <tt><b>$HOME/.odbc.ini</b></tt> -containing at least: -<p> -<tt> -<b> -[ndb] -<br> -Driver = <path-to-your-NDB-installation>/lib/libNDB_ODBC.so -</b> -</tt> -<p> -Then try the shell command <tt><b>dbish dbi:ODBC:ndb</b></tt> -in an NDB API node directory. - -<h4>1.2 Windows</h4> - -[ TODO - documentation ] - -<h3>2. ODBC</h3> - -<h4>2.1 External data types</h4> - -Usual external data types are supported and converted to and from SQL -data types. -<p> -<table width="80%" border=1> -<tr align="left"><th width="40%">type</th> <th>description</th></tr> -<tr align="left"><td>SQL_C_CHAR</td><td>character buffers</tr> -<tr align="left"><td>SQL_C_SLONG, etc</td><td>integer types</tr> -<tr align="left"><td>SQL_C_DOUBLE, etc</td><td>floating types</tr> -<tr align="left"><td>SQL_C_TYPE_TIMESTAMP</td><td>timestamp</tr> -</table> - -<h4>2.2 ODBC functions</h4> -<p> -The driver implements basic ODBC functions. -Main exceptions are: -<ul> -<li>no named cursors -<li>no scrollable cursors -<li>no bulk operations -<li>no asynchronous execution -</ul> -<p> -Following lists main ODBC 3.0 functions and -their status in the driver. -<p> -<table width="80%" border=1> -<tr align="left"><th width="40%">function</th><th>supported</th></tr> -<tr align="left"><td>SQLAllocHandle</td><td> -yes -</td></tr> -<tr align="left"><td>SQLConnect</td><td> -yes -</td></tr> -<tr align="left"><td>SQLGetInfo</td><td> -yes -</td></tr> -<tr align="left"><td>SQLGetFunctions</td><td> -yes -</td></tr> -<tr align="left"><td>SQLGetTypeInfo</td><td> -yes -</td></tr> -<tr align="left"><td>SQLSetConnectAttr</td><td> -yes -</td></tr> -<tr align="left"><td>SQLGetConnectAttr</td><td> -yes -</td></tr> -<tr align="left"><td>SQLSetEnvAttr</td><td> -yes -</td></tr> -<tr align="left"><td>SQLGetEnvAttr</td><td> -yes -</td></tr> -<tr align="left"><td>SQLSetStmtAttr</td><td> -yes -</td></tr> -<tr align="left"><td>SQLGetStmtAttr</td><td> -yes -</td></tr> -<tr align="left"><td>SQLGetDescField</td><td> -yes -</td></tr> -<tr align="left"><td>SQLGetDescRec</td><td> -yes -</td></tr> -<tr align="left"><td>SQLSetDescField</td><td> -yes -</td></tr> -<tr align="left"><td>SQLSetDescRec</td><td> -yes -</td></tr> -<tr align="left"><td>SQLPrepare</td><td> -yes -</td></tr> -<tr align="left"><td>SQLBindParameter</td><td> -yes -</td></tr> -<tr align="left"><td>SQLGetCursorName</td><td> -yes, but cursor names cannot be used in SQL -</td></tr> -<tr align="left"><td>SQLSetCursorName</td><td> -yes, but cursor names cannot be used in SQL -</td></tr> -<tr align="left"><td>SQLSetScrollOptions</td><td> -not implemented -</td></tr> -<tr align="left"><td>SQLExecute</td><td> -yes -</td></tr> -<tr align="left"><td>SQLExecDirect</td><td> -yes -</td></tr> -<tr align="left"><td>SQLNativeSql</td><td> -not implemented -</td></tr> -<tr align="left"><td>SQLDescribeParam</td><td> -not supported -</td></tr> -<tr align="left"><td>SQLNumParams</td><td> -yes -</td></tr> -<tr align="left"><td>SQLParamData</td><td> -yes -</td></tr> -<tr align="left"><td>SQLPutData</td><td> -yes -</td></tr> -<tr align="left"><td>SQLRowCount</td><td> -yes -</td></tr> -<tr align="left"><td>SQLNumResultCols</td><td> -yes -</td></tr> -<tr align="left"><td>SQLDescribeCol</td><td> -yes -</td></tr> -<tr align="left"><td>SQLColAttribute</td><td> -yes -</td></tr> -<tr align="left"><td>SQLBindCol</td><td> -yes -</td></tr> -<tr align="left"><td>SQLFetch</td><td> -yes -</td></tr> -<tr align="left"><td>SQLFetchScroll</td><td> -not implemented -</td></tr> -<tr align="left"><td>SQLGetData</td><td> -yes -</td></tr> -<tr align="left"><td>SQLSetPos</td><td> -not implemented -</td></tr> -<tr align="left"><td>SQLBulkOperations</td><td> -not implemented -</td></tr> -<tr align="left"><td>SQLMoreResults</td><td> -yes, but multiple result sets are not supported -</td></tr> -<tr align="left"><td>SQLGetDiagField</td><td> -yes -</td></tr> -<tr align="left"><td>SQLGetDiagRec</td><td> -yes -</td></tr> -<tr align="left"><td>SQLColumnPrivileges</td><td> -not applicable -</td></tr> -<tr align="left"><td>SQLColumns</td><td> -yes -</td></tr> -<tr align="left"><td>SQLForeignKeys</td><td> -not applicable -</td></tr> -<tr align="left"><td>SQLPrimaryKeys</td><td> -yes -</td></tr> -<tr align="left"><td>SQLProcedureColumns</td><td> -not applicable -</td></tr> -<tr align="left"><td>SQLProcedures</td><td> -not applicable -</td></tr> -<tr align="left"><td>SQLSpecialColumns</td><td> -yes <b>v2.x</b> -</td></tr> -<tr align="left"><td>SQLStatistics</td><td> -not applicable -</td></tr> -<tr align="left"><td>SQLTablePrivileges</td><td> -not applicable -</td></tr> -<tr align="left"><td>SQLTables</td><td> -yes -</td></tr> -<tr align="left"><td>SQLFreeStmt</td><td> -yes -</td></tr> -<tr align="left"><td>SQLCloseCursor</td><td> -yes -</td></tr> -<tr align="left"><td>SQLCancel</td><td> -yes -</td></tr> -<tr align="left"><td>SQLEndTran</td><td> -yes -</td></tr> -<tr align="left"><td>SQLDisconnect</td><td> -yes -</td></tr> -<tr align="left"><td>SQLFreeHandle</td><td> -yes -</td></tr> -</table> - -<h3>3. SQL</h3> - -<h4>3.1 Data types</h4> - -<table width="80%" border=1> -<tr align="left"><th width="40%">type</th> <th>description</th></tr> -<tr align="left"><td>CHAR(n)</td><td>fixed-width blank-padded string</tr> -<tr align="left"><td>VARCHAR(n)</td><td>variable length string</tr> -<tr align="left"><td>INT<br>INTEGER</td><td>integer 32 bits</tr> -<tr align="left"><td>BIGINT</td><td>integer 64 bits</tr> -<tr align="left"><td>DECIMAL(m,n)</td><td>exact number with precision and scale <b>v2.x</b></tr> -<tr align="left"><td>REAL</td><td>float 32 bits</tr> -<tr align="left"><td>FLOAT<br>DOUBLE PRECISION</td><td>float, at least 64 bits</tr> -<tr align="left"><td>DATE</td><td>date with precision 1 second <b>v2.x</b></tr> -<tr align="left"><td>DATETIME</td><td>date with precision 1 nanosecond (SQL_TYPE_TIMESTAMP)</tr> -</table> -<p> - -Integer types may be qualified as UNSIGNED. -<br><br> -Strings and numbers are not converted to each other automatically. -Following is an error (unlike in oracle). -<br> -<pre><tt>select 123 + '456' from tab</tt></pre> - -<h4>3.2 Expressions</h4> - -<table width="80%" border=1> -<tr align="left"><th width="40%">syntax</th> <th>description</th></tr> -<tr align="left"><td align="center"><b>NULL</b></td><td>null value</td></tr> -<tr align="left"><td align="center">12.34<b>e</b>5</td><td>integer or decimal or float constant</td></tr> -<tr align="left"><td align="center"><b>'</b>abc<b>'</b></td><td>string constant</td></tr> -<tr align="left"><td align="center"><b>+ - * / ( )</b></td><td>arithmetic operations</td></tr> -<tr align="left"><td align="center"><b>||</b></td><td>string concatenation <b>v2.x</b></td></tr> -</table> - -<br> -Integer and decimal arithmetic is done in BIGINT. -<br> -Floating arithmetic is done in DOUBLE PRECISION. -<br> -Numeric literals use largest applicable type. -<br> -String operations are done in CHAR or in VARCHAR (if any operand is VARCHAR). -<br> -String literals have type CHAR. - -<h4>3.3 Functions : non-aggregate</h4> - -<table width="80%" border=1> -<tr align="left"><th width="40%">syntax</th> <th>description</th></tr> -<tr align="left"><td align="center">SUBSTR() LEFT() RIGHT()</td><td>substring</td></tr> -<tr align="left"><td align="center">TO_NUMBER() TO_CHAR()</td><td>basic conversions <b>v2.x</b></td></tr> -<tr align="left"><td align="center">ROWNUM</td><td>row number in query</td></tr> -<tr align="left"><td align="center">SYSDATE</td><td>current date as DATETIME</td></tr> -</table> - -<h4>3.4 Functions : aggregate</h4> - -<table width="80%" border=1> -<tr align="left"><th width="40%">syntax</th> <th>description</th></tr> -<tr align="left"><td align="center">COUNT(*) COUNT(expr)</td><td>count rows or non-NULL values</td></tr> -<tr align="left"><td align="center">MIN(expr) MAX(expr)</td><td>min and max of strings and numbers</td></tr> -<tr align="left"><td align="center">SUM(expr) AVG(expr)</td><td>sum and average of numbers</td></tr> -</table> -<br> -GROUP BY and HAVING are supported. - -<h4>3.5 Predicates</h4> - -<table width="80%" border=1> -<tr align="left"><th width="40%">syntax</th> <th>description</th></tr> -<tr align="left"><td align="center">IS NULL / IS NOT NULL</td><td>test if value is null</td></tr> -<tr align="left"><td align="center"><b>< <= = != > >=</b></td><td>comparisons</td></tr> -<tr align="left"><td align="center">LIKE / NOT LIKE</td><td>string matching</td></tr> -<tr align="left"><td align="center">AND OR NOT <b>( )</b></td><td>boolean operators</td></tr> -</table> - -<h4>3.6 Tables</h4> - -An NDB table requires a primary key. -There are 2 ways to specify it. - -<p> -<h4>Case 1</h4> -<pre><tt>create table t ( - a integer not null, - b char(20) not null, - c float, - primary key(a, b) -) -</tt></pre> -<p> -<h4>Case 2</h4> -<p> -A column can be specified as AUTO_INCREMENT. -The column has following requirements. -<ul> -<li>it must be the primary key (not just part of one) -<li>its type must be one of the integer types -</ul> -<pre><tt>create table t ( - a int unsigned auto_increment primary key, - b char(20) not null, - c float -) -</tt></pre> -<p> -The values of an AUTO_INCREMENT column are unique (until wrap-around) -and form an ascending sequence. -Gaps in the sequence are possible. -<h4>Default values</h4> -Columns can be specified with DEFAULT value -which is used on insert if the column is not on the insert list. -<p> -<pre><tt>create table t ( - a int primary key, - b int default 100 -) -insert into t(a) values(1) -- inserts (1,100) -</tt></pre> -<p> -The value must evaluate to constant. -Using SYSDATE (if allowed at all) evaluates to table creation time. -<p> - -<h4>Logging / nologging</h4> - -By default tables are created in logging mode, meaning the data -is preserved across database restart. -The mode can be specified explicitly: -<p> -<tt>create table t1 (a int primary key, b int) logging</tt> -<br> -<tt>create table t1 (a int primary key, b int) nologging</tt> - -<h4>Schemas</h4> - -Schemas do not exist in current NDB Cluster. -As a convenience, a single period is allowed in table names: -<p> -<pre><tt>create table mydb.mytable (a int primary key)</tt></pre> -<p> - -<h4>Drop table</h4> - -Deletes a table, all of its indexes, and all data: -<p> -<tt>drop table t</tt> - -<h4>3.7 Indexes</h4> -Only unique non-ordered indexes exist currently. -The columns must be not nullable and are stored in same -order as underlying table columns. -<p> -<tt>create unique hash index x1 on t1(b, c) logging</tt> -<p> -Internally, a unique hash index is a table where index key is primary key. -If the index is <tt>nologging</tt>, it is rebuilt on database restart -before the database is opened. -<p> -Indexes can of course be dropped: -<p> -<tt>drop index x1</tt> - -<h4>3.8 Select</h4> - -Features: - -<ul> -<li>Expressions and predicates -<br><tt>select a + b * c from t where a < b + c and (b > c or c > 10)</tt> -<li>JOIN to any depth -<br><tt>select a.x, b.y, c.z from t1 a, t2 b, t2 c where a.x + b.y < c.z</tt> -<li>ORDER BY -<br><tt>select * from t1, t2 where a1 > 5 order by b1 + b2, c1 desc</tt> -<li>DISTINCT -<br><tt>select distinct a, b + c from t</tt> -<li>Aggregates without grouping. -<br><tt>select count(*), max(a), 1 + sum(b) + avg(c * d) from t</tt> -<li>Aggregates with grouping. -<br><tt>select a, sum(b) from t group by a having sum(c) > 0 order by a, sum(d)</tt> -</ul> - -Major omissions: -<ul> -<li>no OUTER JOIN -<li>no subqueries and no EXISTS clause -</ul> - -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 <em>nested loops</em> only. -<p> -<ul> -<li>SCAN -<br><tt>select * from t where a < b</tt> -<li>INTERPRETED SCAN (much faster) -<br><tt>select * from t1, t2 where t1.a < 10 and t2.b > t1.c</tt> -<li>PRIMARY KEY lookup -<br><tt>select * from t where pk = 5 and b > 10</tt> -<li>NESTED LOOPS -<br><tt>select * from t1, t2, t3 where t1.pk = t2.x and t2.pk = t3.y</tt> -</ul> - -<h4>3.9 Insert and write</h4> - -Both VALUES and subquery variants can be used. -<p> -<pre><tt>insert into t(a, c) values (123, 'abc') -insert into t1(a, c) select a + 10 * b, c from t2 -</tt></pre> -<p> -For convenience, the non-standard <i>MySql</i> syntax is also supported. -<p> -<pre><tt>insert into t set a = 123, c = 'abc'</tt></pre> -<p> -The non-standard operation WRITE is used exactly like INSERT. -The record is updated if it exists. -Otherwise a new record is inserted. -<p> -<pre><tt>write into t(a, c) values (123, 'abc') -</tt></pre> - -<h4>3.10 Update</h4> - -Update allows no subqueries. -Update is optimized to minimize scans and reads, -by using primary keys and existing unique hash indexes. -<p> -<ul> -<li>SCAN -<br><tt>update t set a = b + 5, c = d where c > 10</tt> -<li>PRIMARY KEY or INDEX lookup -<br><tt>update t set a = b + 5, c = d where pk = 5 and c > 10</tt> -<li>PRIMARY KEY or INDEX direct -<br><tt>update t set a = 5, c = 7 where pk = 5</tt> -</ul> - -<h4>3.11 Delete</h4> - -Delete allows no subqueries. -Delete is optimized to minimize scans and reads, -by using primary keys and existing unique hash indexes. -<p> -<ul> -<li>SCAN -<br><tt>delete from t where c > 10</tt> -<li>PRIMARY KEY or INDEX lookup -<br><tt>delete from t where pk = 5 and c > 10</tt> -<li>PRIMARY KEY or INDEX direct -<br><tt>delete from t where pk = 5</tt> -</ul> - -<h4>3.12 Virtual tables</h4> - -The driver implements some virtual tables. -They can only be queried, not modified. -<p> -<ul> -<li>DUAL -<br>A 1-row table - example: select SYSDATE from DUAL. -<li>ODBC$TYPEINFO -<br>Corresponds to SQLGetTypeInfo. -<li>ODBC$TABLES -<br>Corresponds to SQLTables but shows all NDB kernel objects. -<li>ODBC$COLUMNS -<br>Corresponds to SQLColumns. -<li>ODBC$PRIMARYKEYS -<br>Corresponds to SQLPrimaryKeys. -</ul> - -<h3>4. DIAGNOSTICS</h3> - -<h4>4.1 Diagnostic records</h4> - -The driver manager and driver return 3 main diagnostics -(see <b><tt>SQLGetDiagRec</tt></b>). -<ul> -<li>SQLSTATE (a string of 5 characters) -<li>Native error code -<li>Message text -</ul> -<p> -Message text format is -<br><br> -<b><tt>[Alzato][ODBC driver][NDB Cluster] NDB-ssccnnn <i>error text</i> (in SQLXxx)</tt></b> -<br><br> -Here <b>ssccnnnn</b> is native error code (decimal number), with following parts: -<p> -<li><b><tt>ss</tt></b> - status -<li><b><tt>cc</tt></b> - classification -<li><b><tt>nnnn</tt></b> - error code -</ul> -<p> -See NDB API guide for further information. -<p> -For non-database errors the last prefix <b><tt>[NDB Cluster]</tt></b> is omitted -and native error code is always 02015001. - -<h4>4.2 Tracing</h4> - -Following environment variables may be useful. -<ul> -<li><b><tt>NDB_ODBC_TRACE</tt></b> -<br> -Values ≥ 2 cause SQL execution plan -to be printed on standard output. -<br> -Values ≥ 3 show the ODBC API functions -called by the driver manager. -<br><br> -<li><b><tt>NDB_ODBC_DEBUG</tt></b> -<br> -Non-zero value makes the driver abort -the application on unhandled conditions. -<br> -By default the ODBC API function is aborted gracefully. -</ul> - -<h4>4.3 Thread safety</h4> -<p> -The driver has same thread-safety model as NDB API. -In NDB API each thread must use its own <b><tt>Ndb</tt></b> object. -In NDB ODBC a <b><tt>SQLConnect</tt></b> corresponds to an <b><tt>Ndb</tt></b> object. -It is required that each thread allocates its -own ODBC handles (of all types). - -<h4>4.4 Data formats</h4> -<p> -SQL types are represented as (old) NDB types as follows. -<p> -<table width="80%" border=1> -<tr align="left"><th width="20%">SQL type</th> <th>NDB type</th></tr> -<tr align="left"><td align="left">CHAR(n)</td><td>String(n), blank-padded to n</td></tr> -<tr align="left"><td align="left">VARCHAR(n)</td><td>String(n+2), zero-padded to n, length in last 2 bytes (big-endian)</td></tr> -<tr align="left"><td align="left">integers</td><td>Signed(x) or UnSigned(x), x=16,32,64, native format</td></tr> -<tr align="left"><td align="left">floats</td><td>Float(x), x=32,64, native format</td></tr> -<tr align="left"><td align="left">DATETIME</td><td>String(12) = cc yy mm dd HH MM SS \0 ff ff ff ff (big-endian)</td></tr> -</table> -<p> -Note: SQL types exist now in NDB API in <b><tt>NdbDictionary</tt></b> class. -However they are not yet understood by NDB API operations. - -<h4>4.5 NDB Cluster limitations</h4> -<p> -<ul> -<li>Isolation level is READ COMMITTED. -A scan (non-primary-key select of several rows) does not see consistent data. -<br><br> -<li>Inserting into a table from itself is likely to cause a deadlock -or a random result. -<br><tt><b>no:</b> insert into t(a, b) select a*100, b+100 from t</tt> -<br><br> -<li>Number of uncommitted rows is limited by NDB configuration -parameter <b><tt>MaxNoOfConcurrentOperations</tt></b> (typical default 4096). -To delete all rows from a large table one may need to do repeatedly: -<br><tt>delete from t where rownum < 4000</tt> -</ul> - -<h4>4.6 Known problems v2.11</h4> -<p> -Following lists specific known problems. -<ul> -<li>ORDER BY works only with expressions, -not with column aliases or positions. -<br><tt><b>no:</b> select a+b x from t order by x</tt> -<br><tt><b>no:</b> select * from t order by 1, 2, 3</tt> -<br><br> -<li>Join optimizer does not always minimize number of scans. -Changing the order of tables in the statement may help. -</ul> - -<h4>4.7 Useful links</h4> -<p> -<a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/dasdkodbcoverview_whatsnew.asp/">Microsoft ODBC page</a> -<br> -<a href="http://www.unixodbc.org/">unixODBC home page</a> -<br> -<a href="http://www.iodbc.org/">iODBC home page</a> - -</BODY> -</HTML> |