Usually, you must perform the following operations when you use OceanBase Connector/ODBC in an application to interact with an OBServer:
- Configure a data source name (DSN) for OceanBase Connector/ODBC.
- Connect to the OBServer. For example, allocate an environment handle, allocate a connection handle, establish the connection, and set optional connection attributes.
- Initialize SQL statements. For example, allocate a statement handle and set optional statement attributes.
- Execute SQL statements. For example, preprocess and execute SQL statements, or directly execute SQL statements without preprocessing.
- Search for execution results. The results can contain the number of columns, column information, or row data.
- Commit or roll back transactions.
- Disconnect from the OBServer, and release the connection and environment handles.
The following example shows how to use OceanBase Connector/ODBC to create a table, insert data into the table, query data from the table, and delete data from the table.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sql.h"
#include "sqlext.h"
typedef struct tagODBCHandler {
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
}ODBCHandler;
int IS_SUCC(SQLRETURN retcode) {
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) return 1;
return 0;
}
void checkError(SQLRETURN retcode, const char* msg, ODBCHandler* handler) {
SQLCHAR message[SQL_MAX_MESSAGE_LENGTH + 1];
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER error;
SQLSMALLINT len;
SQLRETURN tmpcode;
switch (retcode){
case SQL_SUCCESS:
printf("%s retcode is SQLRETURN\n", msg);
break;
case SQL_SUCCESS_WITH_INFO:
printf("%s retcode is SQL_SUCCESS_WITH_INFO\n", msg);
break;
case SQL_ERROR:
printf("%s retcode is SQL_ERROR\n", msg);
tmpcode = SQLError(handler->henv, handler->hdbc, handler->hstmt, sqlstate, &error, message, sizeof(message), &len);
if (tmpcode != SQL_SUCCESS && tmpcode != SQL_SUCCESS_WITH_INFO) {
printf("get sqlerror failed %d", tmpcode);
} else {
printf("error is %d, meeesage is %s, sqlstate is %s, len is %d\n", error, message, sqlstate, len);
}
break;
case SQL_INVALID_HANDLE:
printf("%s retcode is SQL_INVALID_HANDLE\n", msg);
break;
case SQL_STILL_EXECUTING:
printf("%s retcode is SQL_STILL_EXECUTING\n", msg);
break;
case SQL_NO_DATA:
printf("%s retcode is SQL_NO_DATA\n", msg);
break;
default:
printf("%s retcode is UNKNOWN retcode\n", msg);
break;
}
}
int main(int argc, char** argv) {
ODBCHandler handler;
SQLRETURN retcode;
#define MAX_NAME_LEN 255
SQLCHAR connOut[MAX_NAME_LEN+1];
SQLSMALLINT len;
// Allocate environment handle
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &handler.henv);
// Set the ODBC version environment attribute
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLAllocHandle", &handler);
return -1;
}
retcode = SQLSetEnvAttr(handler.henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3_80, 0);
// Allocate connection handle
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLSetEnvAttr", &handler);
return -1;
}
retcode = SQLAllocHandle(SQL_HANDLE_DBC, handler.henv, &handler.hdbc);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLAllocHandle", &handler);
return -1;
}
// Set the logon timeout value to 5 seconds
SQLSetConnectAttr(handler.hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
// Connect to data source
retcode = SQLDriverConnect(handler.hdbc, NULL, (SQLCHAR*)"DSN=oboracle", SQL_NTS, connOut, MAX_NAME_LEN, &len,SQL_DRIVER_NOPROMPT);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLDriverConnect", &handler);
return -1;
}
retcode = SQLAllocHandle(SQL_HANDLE_STMT, handler.hdbc, &handler.hstmt);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLAllocHandle", &handler);
return -1;
}
{
// Drop
SQLExecDirect(handler.hstmt, (SQLCHAR*)"drop table PERSON", SQL_NTS);
SQLFreeStmt(handler.hstmt, SQL_CLOSE);
}
{
// Create
retcode = SQLExecDirect(handler.hstmt, (SQLCHAR*)"create table PERSON(id int, num int, name varchar2(100))", SQL_NTS);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLExecDirect", &handler);
return -1;
}
SQLFreeStmt(handler.hstmt, SQL_CLOSE);
}
{
// Insert
retcode = SQLPrepare(handler.hstmt, (SQLCHAR*)"insert into PERSON values(?,?,'test')", SQL_NTS);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLPrepare", &handler);
return -1;
}
SQLINTEGER id = 0;
SQLINTEGER num = 0;
retcode = SQLBindParameter(handler.hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &id, 0, NULL);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLBindParameter", &handler);
return -1;
}
retcode = SQLBindParameter(handler.hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &num, 0, NULL);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLBindParameter", &handler);
return -1;
}
retcode = SQLExecute(handler.hstmt);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLExecute", &handler);
return -1;
}
SQLFreeStmt(handler.hstmt, SQL_CLOSE);
}
{
// Update
SQLINTEGER id = 0;
retcode = SQLPrepare(handler.hstmt, (SQLCHAR*)"update PERSON set num=2, name='hello' where id=?", SQL_NTS);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLPrepare", &handler);
return -1;
}
retcode = SQLBindParameter(handler.hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &id, 0, NULL);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLBindParameter", &handler);
return -1;
}
retcode = SQLExecute(handler.hstmt);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLExecute", &handler);
return -1;
}
SQLFreeStmt(handler.hstmt, SQL_CLOSE);
}
{
// Select
retcode = SQLPrepare(handler.hstmt, (SQLCHAR*)"select * from PERSON", SQL_NTS);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLPrepare", &handler);
return -1;
}
retcode = SQLExecute(handler.hstmt);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLExecute", &handler);
return -1;
}
SQLINTEGER id = 0;
SQLINTEGER num = 0;
char name[100] = { 0 };
retcode = SQLBindCol(handler.hstmt, 1, SQL_INTEGER, &id, sizeof(id), NULL);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLBindCol", &handler);
return -1;
}
retcode = SQLBindCol(handler.hstmt, 2, SQL_INTEGER, &num, sizeof(num), NULL);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLBindCol", &handler);
return -1;
}
retcode = SQLBindCol(handler.hstmt, 3, SQL_CHAR, name, sizeof(name), NULL);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLBindCol", &handler);
return -1;
}
retcode = SQLFetch(handler.hstmt);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLFetch", &handler);
return -1;
}
SQLFreeStmt(handler.hstmt, SQL_CLOSE);
printf("id=%d, num=%d, name=%s", id, num, name);
}
{
// Delete
SQLINTEGER id = 0;
retcode = SQLPrepare(handler.hstmt, (SQLCHAR*)"delete from PERSON where id=?", SQL_NTS);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLPrepare", &handler);
return -1;
}
retcode = SQLBindParameter(handler.hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, &id, 0, NULL);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLBindParameter", &handler);
return -1;
}
retcode = SQLExecute(handler.hstmt);
if (!IS_SUCC(retcode)) {
checkError(retcode, "SQLExecute", &handler);
return -1;
}
SQLFreeStmt(handler.hstmt, SQL_CLOSE);
}
// Clean handle
SQLFreeHandle(SQL_HANDLE_STMT, handler.hstmt);
SQLDisconnect(handler.hdbc);
SQLFreeHandle(SQL_HANDLE_DBC, handler.hdbc);
SQLFreeHandle(SQL_HANDLE_ENV, handler.henv);
return 0;
}