You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by is...@apache.org on 2023/07/19 10:16:29 UTC
[ignite-3] branch main updated: IGNITE-19923 Docs for ODBC in Ignite 3 (#2333)
This is an automated email from the ASF dual-hosted git repository.
isapego pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new 7b8307c1a9 IGNITE-19923 Docs for ODBC in Ignite 3 (#2333)
7b8307c1a9 is described below
commit 7b8307c1a91f823ad347656969f4977fc2a9def1
Author: IgGusev <ig...@gridgain.com>
AuthorDate: Wed Jul 19 13:16:23 2023 +0300
IGNITE-19923 Docs for ODBC in Ignite 3 (#2333)
---
docs/_data/toc.yaml | 9 +
docs/_docs/sql/odbc/connection-string.adoc | 77 +++++
docs/_docs/sql/odbc/index.adoc | 6 +
docs/_docs/sql/odbc/odbc-driver.adoc | 96 ++++++
docs/_docs/sql/odbc/querying-modifying-data.adoc | 363 +++++++++++++++++++++++
5 files changed, 551 insertions(+)
diff --git a/docs/_data/toc.yaml b/docs/_data/toc.yaml
index 841f5efccb..326b355abe 100644
--- a/docs/_data/toc.yaml
+++ b/docs/_data/toc.yaml
@@ -33,6 +33,15 @@
url: sql/calcite-based-sql-engine
- title: JDBC Driver
url: sql/jdbc-driver
+ - title: ODBC Driver
+ url: sql/odbc/odbc-driver
+ items:
+ - title: ODBC Driver
+ url: sql/odbc/odbc-driver
+ - title: Connection String
+ url: sql/odbc/connection-string
+ - title: Querying and Modifying Data
+ url: sql/odbc/querying-modifying-data
- title: Java API
url: sql/java
- title: SQL Reference
diff --git a/docs/_docs/sql/odbc/connection-string.adoc b/docs/_docs/sql/odbc/connection-string.adoc
new file mode 100644
index 0000000000..770e2193c8
--- /dev/null
+++ b/docs/_docs/sql/odbc/connection-string.adoc
@@ -0,0 +1,77 @@
+= Connection String
+
+
+== Connection String Format
+
+The ODBC Driver supports standard connection string format. Here is the formal syntax:
+
+[source,text]
+----
+connection-string ::= empty-string[;] | attribute[;] | attribute; connection-string
+empty-string ::=
+attribute ::= attribute-keyword=attribute-value | DRIVER=[{]attribute-value[}]
+attribute-keyword ::= identifier
+attribute-value ::= character-string
+----
+
+
+In simple terms, an ODBC connection URL is a string with parameters of the choice separated by semicolon.
+
+== Supported Arguments
+
+The ODBC driver supports and uses several connection string/DSN arguments. All parameter names are case-insensitive - `ADDRESS`, `Address`, and `address` all are valid parameter names and refer to the same parameter. If an argument is not specified, the default value is used. The exception to this rule is the `ADDRESS` attribute. If it is not specified, `SERVER` and `PORT` attributes are used instead.
+
+[width="100%",cols="20%,60%,20%"]
+|=======================================================================
+|Attribute keyword |Description |Default Value
+
+|`ADDRESS`
+|Address of the remote node to connect to. The format is: `<host>[:<port>]`. For example: `localhost`, `example.com:12345`, `127.0.0.1`, `192.168.3.80:5893`.
+If this attribute is specified, then `SERVER` and `PORT` arguments are ignored.
+|None.
+
+|`SERVER`
+|Address of the node to connect to.
+This argument value is ignored if ADDRESS argument is specified.
+|None.
+
+|`PORT`
+|Port on which `OdbcProcessor` of the node is listening.
+This argument value is ignored if `ADDRESS` argument is specified.
+|`10800`
+
+|`SCHEMA`
+|Schema name.
+|`PUBLIC`
+
+|`PAGE_SIZE`
+|Number of rows returned in response to a fetching request to the data source. Default value should be fine in most cases. Setting a low value can result in slow data fetching while setting a high value can result in additional memory usage by the driver, and additional delay when the next page is being retrieved.
+|`1024`
+
+|=======================================================================
+
+== Connection String Samples
+
+You can find samples of the connection string below. These strings can be used with `SQLDriverConnect` ODBC call to establish connection with a node.
+
+
+[tabs]
+--
+tab:Specific schema[]
+[source,text]
+----
+DRIVER={Apache Ignite 3};ADDRESS=localhost:10800;SCHEMA=yourSchemaName
+----
+
+tab:Default schema[]
+[source,text]
+----
+DRIVER={Apache Ignite 3};ADDRESS=localhost:10800
+----
+
+tab:Custom page size[]
+[source,text]
+----
+DRIVER={Apache Ignite 3};ADDRESS=localhost:10800;SCHEMA=yourSchemaName;PAGE_SIZE=4096
+----
+--
diff --git a/docs/_docs/sql/odbc/index.adoc b/docs/_docs/sql/odbc/index.adoc
new file mode 100644
index 0000000000..cafbc7250f
--- /dev/null
+++ b/docs/_docs/sql/odbc/index.adoc
@@ -0,0 +1,6 @@
+---
+layout: toc
+---
+
+= ODBC Driver
+
diff --git a/docs/_docs/sql/odbc/odbc-driver.adoc b/docs/_docs/sql/odbc/odbc-driver.adoc
new file mode 100644
index 0000000000..f4aa81d159
--- /dev/null
+++ b/docs/_docs/sql/odbc/odbc-driver.adoc
@@ -0,0 +1,96 @@
+= ODBC Driver
+
+== Overview
+
+Apache Ignite 3 includes an ODBC driver that allows you both to select and to modify data stored in a distributed cache by using standard SQL queries and native ODBC API. ODBC driver uses your link:clients/overview[client connection configuration].
+
+ODBC driver only provides thread-safety at the connections level. This means that you should not access the same connection from multiple threads without additional synchronization, though you can create separate connections for every thread and use them simultaneously.
+
+The ODBC driver implements version 3.8 of the ODBC API. For detailed information on ODBC please refer to link:https://msdn.microsoft.com/en-us/library/ms714177.aspx[ODBC Programmer's Reference].
+
+== Installing ODBC Driver
+
+To use ODBC driver, register it in your system so that your ODBC Driver Manager will be able to locate it.
+
+=== Installing on Windows
+
+NOTE: Microsoft Visual C++ 2017 Redistributable Package should be installed first.
+
+Launch the provided installer and follow the instructions.
+
+
+=== Installing on Linux
+
+To build and install ODBC driver on Linux, you need to first install
+ODBC Driver Manager. The ODBC driver has been tested with link:http://www.unixodbc.org[UnixODBC].
+
+==== Download from website
+
+You can get the built rpm or deb package from the provided website. Then, install the package locally to use it.
+
+== Supported Data Types
+
+The following SQL data types are supported:
+
+- `SQL_CHAR`
+- `SQL_VARCHAR`
+- `SQL_LONGVARCHAR`
+- `SQL_SMALLINT`
+- `SQL_INTEGER`
+- `SQL_FLOAT`
+- `SQL_DOUBLE`
+- `SQL_BIT`
+- `SQL_TINYINT`
+- `SQL_BIGINT`
+- `SQL_BINARY`
+- `SQL_VARBINARY`
+- `SQL_LONGVARBINARY`
+- `SQL_GUID`
+- `SQL_DECIMAL`
+- `SQL_TYPE_DATE`
+- `SQL_TYPE_TIMESTAMP`
+- `SQL_TYPE_TIME`
+
+== Using pyodbc
+
+Ignite can be used with link:https://pypi.org/project/pyodbc/[pyodbc]. Here is how you can use pyodbc in Apache Ignite 3:
+
+- Install pyodbc
++
+[source,shell]
+----
+pip3 install pyodbc
+----
++
+- Import pyodbc to your project:
++
+[source,python]
+----
+import pyodbc
+----
++
+- Connect to the database:
++
+[source,python]
+----
+conn = pyodbc.connect('Driver={Apache Ignite 3};Address=127.0.0.1:10800;')
+----
++
+- Set encoding to UTF-8:
++
+[source,python]
+----
+conn.setencoding(encoding='utf-8')
+conn.setdecoding(sqltype=pyodbc.SQL_CHAR, encoding="utf-8")
+conn.setdecoding(sqltype=pyodbc.SQL_WCHAR, encoding="utf-8")
+----
++
+- Get data from your database:
++
+[source,python]
+----
+cursor = conn.cursor()
+cursor.execute('SELECT * FROM table_name')
+----
+
+For more information on using pyodbc, use the link:https://github.com/mkleehammer/pyodbc/wiki[official documentation].
\ No newline at end of file
diff --git a/docs/_docs/sql/odbc/querying-modifying-data.adoc b/docs/_docs/sql/odbc/querying-modifying-data.adoc
new file mode 100644
index 0000000000..18f24250b5
--- /dev/null
+++ b/docs/_docs/sql/odbc/querying-modifying-data.adoc
@@ -0,0 +1,363 @@
+= Querying and Modifying Data
+
+== Overview
+
+This page describes how to connect to a cluster and execute a variety of SQL queries by using the ODBC driver.
+
+The ODBC driver supports DML (Data Modification Layer), which means that you can modify your data using an ODBC connection.
+
+== Creating Tables
+
+The simplest way to create tables by using ODBC Driver is to use DDL statements:
+
+[tabs]
+--
+tab:DDL[]
+[source,cpp]
+----
+SQLHENV env;
+
+// Allocate an environment handle
+SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
+
+// Use ODBC ver 3.8
+SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, reinterpret_cast<void*>(SQL_OV_ODBC3_80), 0);
+
+SQLHDBC dbc;
+
+// Allocate a connection handle
+SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
+
+// Prepare the connection string
+SQLCHAR connectStr[] = "Driver={Apache Ignite 3};ADDRESS=localhost:10800;SCHEMA=PUBLIC;";
+
+// Connecting to the Cluster.
+SQLDriverConnect(dbc, NULL, connectStr, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
+
+SQLHSTMT stmt;
+
+// Allocate a statement handle
+SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
+
+SQLCHAR query1[] = "CREATE TABLE Person ( "
+ "id LONG PRIMARY KEY, "
+ "firstName VARCHAR, "
+ "lastName VARCHAR, "
+ "salary FLOAT) "";
+
+SQLExecDirect(stmt, query1, SQL_NTS);
+
+SQLCHAR query2[] = "CREATE TABLE Organization ( "
+ "id LONG PRIMARY KEY, "
+ "name VARCHAR) "";
+
+SQLExecDirect(stmt, query2, SQL_NTS);
+
+SQLCHAR query3[] = "CREATE INDEX idx_organization_name ON Organization (name)";
+
+SQLExecDirect(stmt, query3, SQL_NTS);
+----
+--
+
+As you can see, we defined two tables that will contain the data of `Person` and `Organization` types.
+For both types, we listed specific fields and indexes that will be read or updated using SQL.
+
+== Handling Errors
+
+The section below covers how you can handle possible errors when working with ODBC. In this example we handle an issue with connecting to the cluste
+
+[source,c++]
+----
+// Connecting to Ignite Cluster.
+SQLRETURN ret = SQLDriverConnect(dbc, NULL, connectStr, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_COMPLETE);
+
+if (!SQL_SUCCEEDED(ret))
+{
+ SQLCHAR sqlstate[7] = { 0 };
+ SQLINTEGER nativeCode;
+
+ SQLCHAR errMsg[BUFFER_SIZE] = { 0 };
+ SQLSMALLINT errMsgLen = static_cast<SQLSMALLINT>(sizeof(errMsg));
+
+ SQLGetDiagRec(SQL_HANDLE_DBC, dbc, 1, sqlstate, &nativeCode, errMsg, errMsgLen, &errMsgLen);
+
+ std::cerr << "Failed to connect to Ignite: "
+ << reinterpret_cast<char*>(sqlstate) << ": "
+ << reinterpret_cast<char*>(errMsg) << ", "
+ << "Native error code: " << nativeCode
+ << std::endl;
+
+ // Releasing allocated handles.
+ SQLFreeHandle(SQL_HANDLE_DBC, dbc);
+ SQLFreeHandle(SQL_HANDLE_ENV, env);
+
+ return;
+}
+----
+
+== Querying Data
+
+After everything is up and running, we're ready to execute `SQL SELECT` queries using the `ODBC API`.
+
+[source,c++]
+----
+SQLHSTMT stmt;
+
+// Allocate a statement handle
+SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
+
+SQLCHAR query[] = "SELECT firstName, lastName, salary, Organization.name FROM Person "
+ "INNER JOIN Organization ON Person.orgId = Organization.id"
+SQLSMALLINT queryLen = static_cast<SQLSMALLINT>(sizeof(queryLen));
+
+SQLRETURN ret = SQLExecDirect(stmt, query, queryLen);
+
+if (!SQL_SUCCEEDED(ret))
+{
+ SQLCHAR sqlstate[7] = { 0 };
+ SQLINTEGER nativeCode;
+
+ SQLCHAR errMsg[BUFFER_SIZE] = { 0 };
+ SQLSMALLINT errMsgLen = static_cast<SQLSMALLINT>(sizeof(errMsg));
+
+ SQLGetDiagRec(SQL_HANDLE_DBC, dbc, 1, sqlstate, &nativeCode, errMsg, errMsgLen, &errMsgLen);
+
+ std::cerr << "Failed to perfrom SQL query: "
+ << reinterpret_cast<char*>(sqlstate) << ": "
+ << reinterpret_cast<char*>(errMsg) << ", "
+ << "Native error code: " << nativeCode
+ << std::endl;
+}
+else
+{
+ // Printing the result set.
+ struct OdbcStringBuffer
+ {
+ SQLCHAR buffer[BUFFER_SIZE];
+ SQLLEN resLen;
+ };
+
+ // Getting a number of columns in the result set.
+ SQLSMALLINT columnsCnt = 0;
+ SQLNumResultCols(stmt, &columnsCnt);
+
+ // Allocating buffers for columns.
+ std::vector<OdbcStringBuffer> columns(columnsCnt);
+
+ // Binding colums. For simplicity we are going to use only
+ // string buffers here.
+ for (SQLSMALLINT i = 0; i < columnsCnt; ++i)
+ SQLBindCol(stmt, i + 1, SQL_C_CHAR, columns[i].buffer, BUFFER_SIZE, &columns[i].resLen);
+
+ // Fetching and printing data in a loop.
+ ret = SQLFetch(stmt);
+ while (SQL_SUCCEEDED(ret))
+ {
+ for (size_t i = 0; i < columns.size(); ++i)
+ std::cout << std::setw(16) << std::left << columns[i].buffer << " ";
+
+ std::cout << std::endl;
+
+ ret = SQLFetch(stmt);
+ }
+}
+
+// Releasing statement handle.
+SQLFreeHandle(SQL_HANDLE_STMT, stmt);
+----
+
+
+[NOTE]
+====
+[discrete]
+=== Columns binding
+
+In the example above, we bind all columns to the SQL_C_CHAR columns. This means that all values are going to be converted to strings upon fetching. This is done for the sake of simplicity. Value conversion upon fetching can be pretty slow; so your default decision should be to fetch the value the same way as it is stored.
+====
+
+== Inserting Data
+
+To insert new data into the cluster, `SQL INSERT` statements can be used from the ODBC side.
+
+
+[source,c++]
+----
+SQLHSTMT stmt;
+
+// Allocate a statement handle
+SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
+
+SQLCHAR query[] =
+ "INSERT INTO Person (id, orgId, firstName, lastName, resume, salary) "
+ "VALUES (?, ?, ?, ?, ?, ?)";
+
+SQLPrepare(stmt, query, static_cast<SQLSMALLINT>(sizeof(query)));
+
+// Binding columns.
+int64_t key = 0;
+int64_t orgId = 0;
+char name[1024] = { 0 };
+SQLLEN nameLen = SQL_NTS;
+double salary = 0.0;
+
+SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_BIGINT, 0, 0, &key, 0, 0);
+SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_BIGINT, 0, 0, &orgId, 0, 0);
+SQLBindParameter(stmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR, sizeof(name), sizeof(name), name, 0, &nameLen);
+SQLBindParameter(stmt, 4, SQL_PARAM_INPUT, SQL_C_DOUBLE, SQL_DOUBLE, 0, 0, &salary, 0, 0);
+
+// Filling cache.
+key = 1;
+orgId = 1;
+strncpy(name, "John", sizeof(name));
+salary = 2200.0;
+
+SQLExecute(stmt);
+SQLMoreResults(stmt);
+
+++key;
+orgId = 1;
+strncpy(name, "Jane", sizeof(name));
+salary = 1300.0;
+
+SQLExecute(stmt);
+SQLMoreResults(stmt);
+
+++key;
+orgId = 2;
+strncpy(name, "Richard", sizeof(name));
+salary = 900.0;
+
+SQLExecute(stmt);
+SQLMoreResults(stmt);
+
+++key;
+orgId = 2;
+strncpy(name, "Mary", sizeof(name));
+salary = 2400.0;
+
+SQLExecute(stmt);
+
+// Releasing statement handle.
+SQLFreeHandle(SQL_HANDLE_STMT, stmt);
+----
+
+
+Next, we are going to insert additional organizations without the usage of prepared statements.
+
+
+[source,c++]
+----
+SQLHSTMT stmt;
+
+// Allocate a statement handle
+SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
+
+SQLCHAR query1[] = "INSERT INTO Organization (id, name) VALUES (1L, 'Some company')";
+
+SQLExecDirect(stmt, query1, static_cast<SQLSMALLINT>(sizeof(query1)));
+
+SQLFreeStmt(stmt, SQL_CLOSE);
+
+SQLCHAR query2[] = "INSERT INTO Organization (id, name) VALUES (2L, 'Some other company')";
+
+ SQLExecDirect(stmt, query2, static_cast<SQLSMALLINT>(sizeof(query2)));
+
+// Releasing statement handle.
+SQLFreeHandle(SQL_HANDLE_STMT, stmt);
+----
+
+
+[WARNING]
+====
+[discrete]
+=== Error Checking
+
+For simplicity the example code above does not check for an error return code. You will want to do error checking in production.
+====
+
+== Updating Data
+
+Let's now update the salary for some of the persons stored in the cluster using SQL `UPDATE` statement.
+
+
+[source,c++]
+----
+void AdjustSalary(SQLHDBC dbc, int64_t key, double salary)
+{
+ SQLHSTMT stmt;
+
+ // Allocate a statement handle
+ SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
+
+ SQLCHAR query[] = "UPDATE Person SET salary=? WHERE id=?";
+
+ SQLBindParameter(stmt, 1, SQL_PARAM_INPUT,
+ SQL_C_DOUBLE, SQL_DOUBLE, 0, 0, &salary, 0, 0);
+
+ SQLBindParameter(stmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG,
+ SQL_BIGINT, 0, 0, &key, 0, 0);
+
+ SQLExecDirect(stmt, query, static_cast<SQLSMALLINT>(sizeof(query)));
+
+ // Releasing statement handle.
+ SQLFreeHandle(SQL_HANDLE_STMT, stmt);
+}
+
+...
+AdjustSalary(dbc, 3, 1200.0);
+AdjustSalary(dbc, 1, 2500.0);
+----
+
+== Deleting Data
+
+Finally, let's remove a few records with the help of SQL `DELETE` statement.
+
+[source,c++]
+----
+void DeletePerson(SQLHDBC dbc, int64_t key)
+{
+ SQLHSTMT stmt;
+
+ // Allocate a statement handle
+ SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
+
+ SQLCHAR query[] = "DELETE FROM Person WHERE id=?";
+
+ SQLBindParameter(stmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_BIGINT,
+ 0, 0, &key, 0, 0);
+
+ SQLExecDirect(stmt, query, static_cast<SQLSMALLINT>(sizeof(query)));
+
+ // Releasing statement handle.
+ SQLFreeHandle(SQL_HANDLE_STMT, stmt);
+}
+
+...
+DeletePerson(dbc, 1);
+DeletePerson(dbc, 4);
+----
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+