The interaction between an application and the OBServer using OceanBase Connector/ODBC typically involves the following operations:
Configure the 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, execute SQL statements with or without preprocessing.
Retrieve results. The results may include the column count, column information, row data, etc.
Commit or roll back transactions.
Disconnect from the OBServer and release the connection and environment handles.
The following example shows how OceanBase Connector/ODBC is used to create a table, insert data into the table, query data in 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 login timeout 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;
}