void mysql_connect()
{
SQLRETURN ret; /* ODBC API return status */
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
/* Connect to the DSN mydsn */
ret = SQLDriverConnect(dbc, NULL, "DSN=MYSQL_TEST", SQL_NTS,
outstr, sizeof(outstr), &outstrlen,
SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret)) {
printf("Connected\n");
printf(" Returned connection string was:\n\t%s\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO) {
printf(" Driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
} else {
fprintf(stderr, "Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
printf("\n");
}
string ODBCHandler::execute_query(unsigned char* query, ssize_t threashold,
off_t *row_count, ssize_t *len, ssize_t *last_row_len)
{
SQLHSTMT stmt;
SQLRETURN ret;
SQLSMALLINT nr_columns;
string ODBC_error;
stringstream result_str;
bool row_bound = false;
cout<<"Query: "<<query<<endl;
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
ret = SQLPrepare(stmt, query , SQL_NTS);
if (!SQL_SUCCEEDED(ret)) {
ODBC_error = extract_error(stmt, SQL_HANDLE_STMT);
cout<<ODBC_error<<endl;
}
ret = SQLExecute(stmt);
if (!SQL_SUCCEEDED(ret)) {
ODBC_error = extract_error(stmt, SQL_HANDLE_STMT);
cout<<ODBC_error<<endl;
}
SQLNumResultCols(stmt, &nr_columns);
*row_count = 0;
*len = 0;
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
SQLUSMALLINT i;
*len += *last_row_len;
(*last_row_len) = 0;
for (i = 1; i <= nr_columns; i++) {
SQLLEN indicator;
char buf[512];
ret = SQLGetData(stmt, i, SQL_C_CHAR,
buf, sizeof(buf), &indicator);
if (i == nr_columns)
row_bound = true;
if (SQL_SUCCEEDED(ret)) {
if (indicator == SQL_NULL_DATA)
strcpy(buf, "NULL");
*last_row_len += encode_results(result_str, buf, row_bound);
}
}
if (*len + *last_row_len >= threashold)
break;
(*row_count)++;
row_bound = false;
}
return result_str.str();
}
bool odbcGetNextRow(const OdbcHandle handle)
{
bool ret = false;
if (handle)
{
SQLRETURN sqlRet = SQLFetch(handle->mStatementHandle);
if (!SQL_SUCCEEDED(sqlRet))
{
Os::Logger::instance().log(FAC_ODBC, PRI_DEBUG,
"odbcGetNextRow: SQLFetch failed, error code %d",
sqlRet);
extract_error("SQLFetch", handle->mStatementHandle, SQL_HANDLE_STMT);
}
else
{
Os::Logger::instance().log(FAC_ODBC, PRI_DEBUG,
"odbcGetNextRow: SQLFetch succeeded");
ret = true;
}
}
else
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcGetNextRow: handle == NULL");
}
return ret;
}
bool odbcClearResultSet(const OdbcHandle handle)
{
bool ret = false;
if (handle)
{
SQLRETURN sqlRet = SQLFreeStmt(handle->mStatementHandle,
SQL_CLOSE);
if (!SQL_SUCCEEDED(sqlRet))
{
Os::Logger::instance().log(FAC_ODBC, PRI_WARNING,
"odbcClearResultSet: SQLFreeStmt failed, "
"error code %d", sqlRet);
extract_error("SQLFreeStmt", handle->mStatementHandle, SQL_HANDLE_STMT);
}
else
{
Os::Logger::instance().log(FAC_ODBC, PRI_DEBUG,
"odbcClearResultSet: SQLFreeStmt succeeded");
ret = true;
}
}
else
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcClearResultSet: handle == NULL");
}
return ret;
}
int
main(void)
{
int i;
char cmd[128 + 110*10];
printf("SQLWCHAR size is: %d\n", (int) sizeof(SQLWCHAR));
odbc_use_version3 = 1;
odbc_connect();
/* this test do not work with Sybase */
if (!odbc_db_is_microsoft()) {
odbc_disconnect();
return 0;
}
strcpy(cmd, "create procedure #proc_longerror as\nbegin\nraiserror('");
for (i = 0; i < 110; ++i)
strcat(cmd, "reallylong");
strcat(cmd, " error', 16, 1)\nend\n");
odbc_command(cmd);
CHKR2(SQLExecDirectW,
(odbc_stmt, odbc_get_sqlwchar(&odbc_buf, "{CALL #proc_longerror}"), SQL_NTS),
SQL_HANDLE_STMT, odbc_stmt, "E");
extract_error(odbc_stmt, SQL_HANDLE_STMT);
odbc_disconnect();
return 0;
}
int odbcResultColumns(const OdbcHandle handle)
{
int ret = -1;
if (handle)
{
SQLSMALLINT columns;
SQLRETURN sqlRet = SQLNumResultCols(handle->mStatementHandle, &columns);
if (!SQL_SUCCEEDED(sqlRet))
{
Os::Logger::instance().log(FAC_ODBC, PRI_DEBUG,
"odbcResultColumns: SQLNumResultCols failed, "
"error code %d", sqlRet);
extract_error("SQLNumResultCols", handle->mStatementHandle, SQL_HANDLE_STMT);
}
else
{
ret = (int)columns;
Os::Logger::instance().log(FAC_ODBC, PRI_DEBUG,
"odbcResultColumns: SQLNumResultCols returned %d", ret);
}
}
else
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcResultColumns: handle == NULL");
}
return ret;
}
ODBCHandler::ODBCHandler(unsigned char *con_str)
{
string ODBC_error;
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
SQLRETURN ret = SQLDriverConnect(dbc, NULL, (SQLCHAR*)con_str, SQL_NTS,
NULL, 0, NULL, SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret)) {
if (ret == SQL_SUCCESS_WITH_INFO) {
ODBC_error = extract_error(dbc, SQL_HANDLE_DBC);
}
} else {
ODBC_error = extract_error(dbc, SQL_HANDLE_DBC);
if (&dbc)
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
if (&env)
SQLFreeHandle(SQL_HANDLE_ENV, env);
cout<<ODBC_error<<endl;
}
}
int main() {
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret; /* ODBC API return status */
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
/* Connect to the DSN mydsn */
//char enter[80];
//puts("Enter connect string!");
//char* connstr =trimwhitespace(gets(enter));
char* connstr = "Driver={SQL Server};Server=192.168.1.8;Database=yrxweb;uid=eltweb;pwd=bewt_111";
ret = SQLDriverConnect(dbc, NULL, connstr, SQL_NTS,
outstr, sizeof(outstr), &outstrlen,
SQL_DRIVER_NOPROMPT);
puts("END");
if (SQL_SUCCEEDED(ret)) {
printf("Connected\n");
printf("Returned connection string was:\n\t%s\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO) {
printf("Driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
SQLDisconnect(dbc); /* disconnect from driver */
} else {
fprintf(stderr, "Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
/* free up allocated handles */
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
puts("OK");
}
bool ODBCHandler::odbc_exec_stmt(SQLHSTMT *stmt, const char *sql)
{
SQLRETURN ret; /* ODBC API return status */
ret = SQLExecDirect(*stmt, (SQLCHAR *)sql, SQL_NTS);
if (!SQL_SUCCEEDED(ret)) {
fprintf(stderr, "Failed to execute SQLExecDirect [%s].\n", sql);
extract_error("SQLExecDirect", stmt, SQL_HANDLE_STMT);
//SQLFreeHandle(SQL_HANDLE_STMT, *stmt);//external alloc, so must be external dealloc
return false;
}
return true;
}
bool ODBCHandler::allocHandle(SQLHSTMT *stmt)
{
SQLRETURN ret; /* ODBC API return status */
ret = SQLAllocHandle(SQL_HANDLE_STMT, ODBC_con, stmt);
if (!SQL_SUCCEEDED(ret)) {
fprintf(stderr, "Failed to allocate stmt handle.\n");
extract_error("SQLAllocHandle", ODBC_con, SQL_HANDLE_DBC);
return false;
}
return true;
}
//
// get number of column/param records in descriptor
// count does not include bookmark record ever
//
SQLSMALLINT getDescRecCount (SQLHDESC descriptor) {
SQLRETURN retcode;
SQLSMALLINT descCount=0;
// get number of fields in the descriptor
retcode = SQLGetDescField(descriptor, 0, SQL_DESC_COUNT, &descCount, 0, 0);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
extract_error("SQLGetDescField (COUNT)",
descriptor, SQL_HANDLE_DESC);
descCount=0;
}
return descCount;
}
bool ODBCHandler::connect(const char *dsn)
{
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret; /* ODBC API return status */
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
//SQLSetEnvAttr(henv_, SQL_ATTR_CONNECTION_POOLING, (SQLPOINTER)SQL_CP_ONE_PER_HENV,
//SQL_IS_UINTEGER); (or) SQL_CP_ONE_PER_DRIVER
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
/* Connect to the DSN mydsn */
ret = SQLDriverConnect(dbc, NULL,(SQLCHAR*) dsn, SQL_NTS,
outstr, sizeof(outstr), &outstrlen,
SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret)) {
fprintf(stderr, "Connected\n");
fprintf(stderr, "Returned connection string was:\n\t%s\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO) {
fprintf(stderr, "Driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
SQLDisconnect(dbc); /* disconnect from driver */
} else {
fprintf(stderr, "Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
/* free up allocated handles */
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
return SQL_SUCCEEDED(ret);
}
bool ODBCHandler::odbc_execute_query(SQLHSTMT *stmt, const char *sql)
{
/* Call as: if (false == odbc_execute_query(&stmt, sql)) { goto SALIR; } */
SQLRETURN ret; /* ODBC API return status */
ret = SQLAllocHandle(SQL_HANDLE_STMT, ODBC_con, stmt);
if (!SQL_SUCCEEDED(ret)) {
fprintf(stderr, "Failed to allocate stmt handle.\n");
extract_error("SQLAllocHandle", ODBC_con, SQL_HANDLE_DBC);
return false;
}
ret = SQLExecDirect(*stmt, (SQLCHAR *)sql, SQL_NTS);
if (!SQL_SUCCEEDED(ret)) {
fprintf(stderr, "Failed to execute SQLExecDirect [%s].\n", sql);
extract_error("SQLExecDirect", ODBC_con, SQL_HANDLE_DBC);
SQLFreeHandle(SQL_HANDLE_STMT, *stmt);
return false;
}
return true;
}
//
// returns true/false if descriptor has/hasn't bookmark record
//
int hasBookmarkRecord (SQLHANDLE handle) {
SQLRETURN retcode;
SQLULEN bookmark;
retcode = SQLGetStmtAttr(handle, SQL_ATTR_USE_BOOKMARKS,
&bookmark, 0, 0);
if ( (retcode != SQL_SUCCESS) && (retcode != SQL_SUCCESS_WITH_INFO) ) {
extract_error("SQLGetStmtAttr (SQL_ATTR_USE_BOOKMARKS)",
handle, SQL_HANDLE_ENV);
return 0;
}
if (bookmark==SQL_UB_OFF)
return 0;
else
return 1;
}
bool odbcGetColumnStringData(const OdbcHandle handle,
int columnIndex,
char* data,
int dataSize)
{
bool ret = false;
if (handle)
{
SQLLEN indicator;
SQLRETURN sqlRet = SQLGetData(handle->mStatementHandle,
(SQLUSMALLINT)columnIndex,
SQL_C_CHAR,
(SQLCHAR*)data,
dataSize,
&indicator);
if (!SQL_SUCCEEDED(sqlRet))
{
Os::Logger::instance().log(FAC_ODBC, PRI_WARNING,
"odbcGetColumnStringData: SQLGetData on column %d "
"failed, error code %d",
columnIndex, sqlRet);
extract_error("SQLGetData", handle->mStatementHandle, SQL_HANDLE_STMT);
}
else
{
Os::Logger::instance().log(FAC_ODBC, PRI_DEBUG,
"odbcGetColumnStringData: SQLGetData on column %d "
"returned %s",
columnIndex,
data);
ret = true;
}
}
else
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcGetColumnStringData: handle == NULL");
}
return ret;
}
bool odbcDisconnect(OdbcHandle &handle)
{
bool ret = false;
if (handle)
{
SQLRETURN sqlRet;
SQLFreeStmt(handle->mStatementHandle, SQL_CLOSE);
sqlRet = SQLDisconnect(handle->mConnectionHandle);
if (!SQL_SUCCEEDED(sqlRet))
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcDisconnect - failed disconnecting from "
"database, error code %d", sqlRet);
extract_error("SQLDisconnect", handle->mConnectionHandle, SQL_HANDLE_DBC);
}
else
{
SQLFreeHandle(SQL_HANDLE_STMT, handle->mStatementHandle);
SQLFreeHandle(SQL_HANDLE_DBC, handle->mConnectionHandle);
SQLFreeHandle(SQL_HANDLE_ENV, handle->mEnvironmentHandle);
delete handle;
handle = NULL;
Os::Logger::instance().log(FAC_ODBC, PRI_DEBUG,
"odbcDisconnect - disconnecting from database");
ret = true;
}
}
else
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcDisconnect: handle == NULL");
}
return ret;
}
bool odbcExecute(const OdbcHandle handle,
const char* sqlStatement)
{
bool ret = false;
if (handle)
{
SQLRETURN sqlRet;
sqlRet = SQLExecDirect(handle->mStatementHandle,
(SQLCHAR*)sqlStatement,
SQL_NTS);
if (!SQL_SUCCEEDED(sqlRet))
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcExecute: statement %s failed, error code %d",
sqlStatement, sqlRet);
extract_error("SQLExecDirect", handle->mStatementHandle, SQL_HANDLE_STMT);
}
else
{
Os::Logger::instance().log(FAC_ODBC, PRI_DEBUG,
"odbcExecute: statement %s succeeded",
sqlStatement);
ret = true;
}
}
else
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcExecute: handle == NULL");
}
return ret;
}
static void *db_iodbc_open_int(TABDCA *dca, int mode, const char
**sqllines)
{
struct db_odbc *sql;
SQLRETURN ret;
SQLCHAR FAR *dsn;
SQLCHAR info[256];
SQLSMALLINT colnamelen;
SQLSMALLINT nullable;
SQLSMALLINT scale;
const char *arg;
int narg;
int i, j;
int total;
if (libodbc == NULL)
{
xprintf("No loader for shared ODBC library available\n");
return NULL;
}
if (h_odbc == NULL)
{
h_odbc = xdlopen(libodbc);
if (h_odbc == NULL)
{ xprintf("unable to open library %s\n", libodbc);
xprintf("%s\n", xerrmsg());
return NULL;
}
}
sql = (struct db_odbc *) xmalloc(sizeof(struct db_odbc));
if (sql == NULL)
return NULL;
sql->mode = mode;
sql->hdbc = NULL;
sql->henv = NULL;
sql->hstmt = NULL;
sql->query = NULL;
narg = mpl_tab_num_args(dca);
dsn = (SQLCHAR FAR *) mpl_tab_get_arg(dca, 2);
/* allocate an environment handle */
ret = dl_SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&(sql->henv));
/* set attribute to enable application to run as ODBC 3.0
application */
ret = dl_SQLSetEnvAttr(sql->henv, SQL_ATTR_ODBC_VERSION,
(void *) SQL_OV_ODBC3, 0);
/* allocate a connection handle */
ret = dl_SQLAllocHandle(SQL_HANDLE_DBC, sql->henv, &(sql->hdbc));
/* connect */
ret = dl_SQLDriverConnect(sql->hdbc, NULL, dsn, SQL_NTS, NULL, 0,
NULL, SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret))
{ /* output information about data base connection */
xprintf("Connected to ");
dl_SQLGetInfo(sql->hdbc, SQL_DBMS_NAME, (SQLPOINTER)info,
sizeof(info), NULL);
xprintf("%s ", info);
dl_SQLGetInfo(sql->hdbc, SQL_DBMS_VER, (SQLPOINTER)info,
sizeof(info), NULL);
xprintf("%s - ", info);
dl_SQLGetInfo(sql->hdbc, SQL_DATABASE_NAME, (SQLPOINTER)info,
sizeof(info), NULL);
xprintf("%s\n", info);
}
else
{ /* describe error */
xprintf("Failed to connect\n");
extract_error("SQLDriverConnect", sql->hdbc, SQL_HANDLE_DBC);
dl_SQLFreeHandle(SQL_HANDLE_DBC, sql->hdbc);
dl_SQLFreeHandle(SQL_HANDLE_ENV, sql->henv);
xfree(sql);
return NULL;
}
/* set AUTOCOMMIT on*/
ret = dl_SQLSetConnectAttr(sql->hdbc, SQL_ATTR_AUTOCOMMIT,
(SQLPOINTER)SQL_AUTOCOMMIT_ON, 0);
/* allocate a statement handle */
ret = dl_SQLAllocHandle(SQL_HANDLE_STMT, sql->hdbc, &(sql->hstmt));
/* initialization queries */
for(j = 0; sqllines[j+1] != NULL; j++)
{
sql->query = (SQLCHAR *) sqllines[j];
xprintf("%s\n", sql->query);
ret = dl_SQLExecDirect(sql->hstmt, sql->query, SQL_NTS);
switch (ret)
{
case SQL_SUCCESS:
case SQL_SUCCESS_WITH_INFO:
case SQL_NO_DATA_FOUND:
break;
default:
xprintf("db_iodbc_open: Query\n\"%s\"\nfailed.\n",
sql->query);
extract_error("SQLExecDirect", sql->hstmt, SQL_HANDLE_STMT);
dl_SQLFreeHandle(SQL_HANDLE_STMT, sql->hstmt);
dl_SQLDisconnect(sql->hdbc);
dl_SQLFreeHandle(SQL_HANDLE_DBC, sql->hdbc);
dl_SQLFreeHandle(SQL_HANDLE_ENV, sql->henv);
xfree(sql);
return NULL;
}
/* commit statement */
dl_SQLEndTran(SQL_HANDLE_ENV, sql->henv, SQL_COMMIT);
}
if ( sql->mode == 'R' )
{ sql->nf = mpl_tab_num_flds(dca);
for(j = 0; sqllines[j] != NULL; j++)
arg = sqllines[j];
total = strlen(arg);
if (total > 7 && 0 == strncmp(arg, "SELECT ", 7))
{
total = strlen(arg);
sql->query = xmalloc( (total+1) * sizeof(char));
strcpy (sql->query, arg);
}
else
{
sql->query = db_generate_select_stmt(dca);
}
xprintf("%s\n", sql->query);
if (dl_SQLExecDirect(sql->hstmt, sql->query, SQL_NTS) !=
SQL_SUCCESS)
{
xprintf("db_iodbc_open: Query\n\"%s\"\nfailed.\n", sql->query);
extract_error("SQLExecDirect", sql->hstmt, SQL_HANDLE_STMT);
dl_SQLFreeHandle(SQL_HANDLE_STMT, sql->hstmt);
dl_SQLDisconnect(sql->hdbc);
dl_SQLFreeHandle(SQL_HANDLE_DBC, sql->hdbc);
dl_SQLFreeHandle(SQL_HANDLE_ENV, sql->henv);
xfree(sql->query);
xfree(sql);
return NULL;
}
xfree(sql->query);
/* determine number of result columns */
ret = dl_SQLNumResultCols(sql->hstmt, &sql->nresultcols);
total = sql->nresultcols;
if (total > SQL_FIELD_MAX)
{ xprintf("db_iodbc_open: Too many fields (> %d) in query.\n"
"\"%s\"\n", SQL_FIELD_MAX, sql->query);
dl_SQLFreeHandle(SQL_HANDLE_STMT, sql->hstmt);
dl_SQLDisconnect(sql->hdbc);
dl_SQLFreeHandle(SQL_HANDLE_DBC, sql->hdbc);
dl_SQLFreeHandle(SQL_HANDLE_ENV, sql->henv);
xfree(sql->query);
return NULL;
}
for (i = 1; i <= total; i++)
{ /* return a set of attributes for a column */
ret = dl_SQLDescribeCol(sql->hstmt, (SQLSMALLINT) i,
sql->colname[i], SQL_FDLEN_MAX,
&colnamelen, &(sql->coltype[i]), &(sql->collen[i]), &scale,
&nullable);
sql->isnumeric[i] = is_numeric(sql->coltype[i]);
/* bind columns to program vars, converting all types to CHAR*/
if (sql->isnumeric[i])
{ dl_SQLBindCol(sql->hstmt, i, SQL_DOUBLE, sql->data[i],
SQL_FDLEN_MAX, &(sql->outlen[i]));
} else
{ dl_SQLBindCol(sql->hstmt, i, SQL_CHAR, sql->data[i],
SQL_FDLEN_MAX, &(sql->outlen[i]));
}
for (j = sql->nf; j >= 1; j--)
{ if (strcmp(mpl_tab_get_name(dca, j), sql->colname[i]) == 0)
break;
}
sql->ref[i] = j;
}
}
else if ( sql->mode == 'W' )
{ for(j = 0; sqllines[j] != NULL; j++)
arg = sqllines[j];
if ( NULL != strchr(arg, '?') )
{
total = strlen(arg);
sql->query = xmalloc( (total+1) * sizeof(char));
strcpy (sql->query, arg);
}
else
{
sql->query = db_generate_insert_stmt(dca);
}
xprintf("%s\n", sql->query);
}
return sql;
}
int ODBCHandler::odbc_init(const char *conn_string)
{
fprintf(stderr, "BEGIN\n");
SQLRETURN ret;
try
{
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
fprintf(stderr, "1\n");
if (ODBC_env == SQL_NULL_HANDLE || connected == 0)
{
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &ODBC_env);
if ((SQL_SUCCEEDED(ret) != SQL_SUCCESS) && (SQL_SUCCEEDED(ret) != SQL_SUCCESS_WITH_INFO)) {
fprintf(stderr, "odbc: Error AllocHandle\n");
connected = 0;
return -1;
}
fprintf(stderr, "2\n");
ret = SQLSetEnvAttr(ODBC_env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
if ((SQL_SUCCEEDED(ret) != SQL_SUCCESS) && (SQL_SUCCEEDED(ret) != SQL_SUCCESS_WITH_INFO)) {
fprintf(stderr, "odbc: Error SetEnv\n");
SQLFreeHandle(SQL_HANDLE_ENV, ODBC_env);
ODBC_env = SQL_NULL_HANDLE;
connected = 0;
return -1;
}
fprintf(stderr, "3\n");
ret = SQLAllocHandle(SQL_HANDLE_DBC, ODBC_env, &ODBC_con);
if ((SQL_SUCCEEDED(ret) != SQL_SUCCESS) && (SQL_SUCCEEDED(ret) != SQL_SUCCESS_WITH_INFO)) {
fprintf(stderr, "odbc: Error AllocHDB %d\n", ret);
SQLFreeHandle(SQL_HANDLE_ENV, ODBC_env);
ODBC_env = SQL_NULL_HANDLE;
connected = 0;
return -1;
}
fprintf(stderr, "4\n");
/* ret = SQLSetConnectAttr(ODBC_con, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)10, 0);
if ((SQL_SUCCEEDED(ret) != SQL_SUCCESS) && (SQL_SUCCEEDED(ret) != SQL_SUCCESS_WITH_INFO)) {
fprintf(stderr, "odbc: Error set Login timeout %d\n", ret);
SQLFreeHandle(SQL_HANDLE_ENV, ODBC_env);
ODBC_env = SQL_NULL_HANDLE;
connected = 0;
return -1;
}
fprintf(stderr, "5\n");
*/
}
fprintf(stderr, "6\n");
ret = SQLDriverConnect(ODBC_con, NULL, (SQLCHAR *)conn_string, SQL_NTS, outstr, sizeof( outstr), &outstrlen, SQL_DRIVER_COMPLETE);
fprintf(stderr, "6.1\n");
if (SQL_SUCCEEDED(ret) == SQL_SUCCESS_WITH_INFO) {
printf("Driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", ODBC_con, SQL_HANDLE_DBC);
}
fprintf(stderr, "7\n");
if ((SQL_SUCCEEDED(ret) != SQL_SUCCESS) && (SQL_SUCCEEDED(ret) != SQL_SUCCESS_WITH_INFO)) {
fprintf(stderr, "odbc: Error SQLConnect %d\n", ret);
extract_error("SQLDriverConnect", ODBC_con, SQL_HANDLE_DBC);
odbc_disconnect();
return -1;
} else {
connected = 1;
}
fprintf(stderr, "8\n");
}catch(...){
fprintf(stderr, "odbc: Error SQLConnect %d\n", ret);
}
fprintf(stderr, "END\n");
return 0;
}
OdbcHandle odbcConnect(const char* dbname,
const char* servername,
const char* username,
const char* driver,
const char* password)
{
OdbcHandle handle = new OdbcControlStruct;
if (handle)
{
if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV,
SQL_NULL_HANDLE,
&handle->mEnvironmentHandle)))
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcConnect: Failed to allocate "
"environment handle");
delete handle;
handle = NULL;
}
else
{
SQLSetEnvAttr(handle->mEnvironmentHandle, SQL_ATTR_ODBC_VERSION,
(void*) SQL_OV_ODBC3, 0);
if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC,
handle->mEnvironmentHandle,
&handle->mConnectionHandle)))
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcConnect: Failed to allocate "
"connection handle");
extract_error("SQLSetEnvAttr", handle->mEnvironmentHandle, SQL_HANDLE_ENV);
SQLFreeHandle(SQL_HANDLE_ENV, handle->mEnvironmentHandle);
delete handle;
handle = NULL;
}
else
{
// Connect - contruct connection string
UtlString connectionString;
char temp[128];
if (dbname)
{
sprintf(temp, "DATABASE=%s;", dbname);
connectionString.append(temp);
}
if (servername)
{
sprintf(temp, "SERVER=%s;", servername);
connectionString.append(temp);
}
if (username)
{
sprintf(temp, "UID=%s;", username);
connectionString.append(temp);
}
if (password)
{
sprintf(temp, "PWD=%s;", password);
connectionString.append(temp);
}
else
{
connectionString.append("PWD=;");
}
if (driver)
{
sprintf(temp, "DRIVER=%s;", driver);
connectionString.append(temp);
}
// Connect in read/write mode
connectionString.append("READONLY=0;");
SQLRETURN sqlRet =
SQLDriverConnect(handle->mConnectionHandle,
NULL,
(SQLCHAR*)connectionString.data(),
SQL_NTS,
NULL,
0,
NULL,
SQL_DRIVER_NOPROMPT);
if (!SQL_SUCCEEDED(sqlRet))
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcConnect: Failed to connect %s, error code %d",
connectionString.data(), sqlRet);
extract_error("SQLDriverConnect", handle->mConnectionHandle, SQL_HANDLE_DBC);
SQLFreeHandle(SQL_HANDLE_DBC, handle->mConnectionHandle);
SQLFreeHandle(SQL_HANDLE_ENV, handle->mEnvironmentHandle);
delete handle;
handle = NULL;
}
else
{
// Connected to database, now alloc a statement handle
if (!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT,
handle->mConnectionHandle,
&handle->mStatementHandle)))
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcConnect: Failed to allocate "
"statement handle");
extract_error("SQLAllocHandle", handle->mStatementHandle, SQL_HANDLE_STMT);
// Disconnect from database
SQLDisconnect(handle->mConnectionHandle);
// Free resources
SQLFreeHandle(SQL_HANDLE_DBC, handle->mConnectionHandle);
SQLFreeHandle(SQL_HANDLE_ENV, handle->mEnvironmentHandle);
delete handle;
handle = NULL;
}
else
{
Os::Logger::instance().log(FAC_ODBC, PRI_DEBUG,
"odbcConnect: Connected to database "
"%s, OdbcHandle %p",
connectionString.data(), handle);
}
}
}
}
}
else
{
Os::Logger::instance().log(FAC_ODBC, PRI_ERR,
"odbcConnect: Couldn't create OdbcHandle");
}
return handle;
}
void get_foreign_keys()
{
printf("get_foreign_keys\n");
SQLHENV env;
SQLHDBC dbc;
SQLRETURN ret; /* ODBC API return status */
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
ret = SQLDriverConnect(dbc, NULL, inString, SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret)) { printf("connected. Returned connection string was:\n\t%s\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO) {
printf("Driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
} else {
printf("Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
/*-------------------------------------------------*/
SQLHSTMT stmt;
SQLSMALLINT columns; /* number of columns in result-set */
SQLCHAR bufChar[10][64];
SQLSMALLINT bufSmallInt[4];
SQLINTEGER indicator[5];
ret = SQLAllocStmt(dbc, &stmt);
if (ret != SQL_SUCCESS) {
extract_error("SQLAllocStmt", dbc, SQL_HANDLE_DBC); return;
}
SQLTables(stmt, NULL, SQL_NTS, (SQLCHAR*)"installdb3", SQL_NTS, (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"TABLE", SQL_NTS );
/* How many columns are there */
SQLNumResultCols(stmt, &columns);
/* Loop through the rows in the result-set binding to */
/* local variables */
int i;
for (i = 0; i < columns; i++) {
SQLBindCol( stmt, i + 1, SQL_C_CHAR, bufChar[i], sizeof( bufChar[i] ), &indicator[i] );
}
/* Fetch the data */
while (SQL_SUCCEEDED(SQLFetch(stmt))) {
/* display the results that will now be in the bound area's */
for ( i = 0; i < columns; i ++ ) {
if (indicator[ i ] == SQL_NULL_DATA) {
// printf(" Column %u : NULL\n", i);
}
else {
if (strlen((char*)(bufChar[i])) > 0) {
printf(" Column %u : %s\n", i, bufChar[i]);
}
else {
// printf(" Column %u : Empty string\n", i);
}
}
}
printf("\n");
}
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
/*----------------------------------*/
SQLCHAR errormsg[SQL_MAX_MESSAGE_LENGTH];
ret = SQLAllocStmt(dbc, &stmt);
if (ret != SQL_SUCCESS) {
extract_error("SQLAllocStmt", dbc, SQL_HANDLE_DBC); return;
}
ret = SQLForeignKeys(stmt,
NULL, 0, /* Primary catalog */
NULL, 0, /* Primary schema */
NULL, 0, /* Primary table */
(SQLCHAR*)"instaldb123", SQL_NTS, /* Foreign catalog */
(SQLCHAR*)"instaldb3", SQL_NTS, /* Foreign schema */
(SQLCHAR*)"eventtypes", SQL_NTS /* Foreign table */
);
if (ret != SQL_SUCCESS) {
printf("error: %s\n", dbErrorMsg(env, dbc, stmt, &errormsg[0], SQL_MAX_MESSAGE_LENGTH));
printf("SQLForeignKeys - ret: %d\n", ret);
extract_error("SQLForeignKeys", stmt, SQL_HANDLE_STMT);
return;
}
SQLNumResultCols(stmt, &columns);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
/*-------------------------------------------------*/
/* free up allocated handles */
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
/*
* Create a new connection structure,
* open the UNIXODBC connection and set reference count to 1
*/
struct my_con* new_connection(struct db_id* id)
{
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
int ret;
struct my_con* ptr;
char conn_str[MAX_CONN_STR_LEN];
if (!id)
{
LM_ERR("invalid parameter value\n");
return 0;
}
ptr = (struct my_con*)pkg_malloc(sizeof(struct my_con));
if (!ptr)
{
LM_ERR("no more memory left\n");
return 0;
}
memset(ptr, 0, sizeof(struct my_con));
ptr->ref = 1;
// allocate environment handle
ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &(ptr->env));
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO))
{
LM_ERR("could not alloc a SQL handle\n");
if (ptr) pkg_free(ptr);
return 0;
}
// set the environment
ret = SQLSetEnvAttr(ptr->env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO))
{
LM_ERR("could not set the environment\n");
goto err1;
}
// allocate connection handle
ret = SQLAllocHandle(SQL_HANDLE_DBC, ptr->env, &(ptr->dbc));
if ((ret != SQL_SUCCESS) && (ret != SQL_SUCCESS_WITH_INFO))
{
LM_ERR("could not alloc a connection handle %d\n", ret);
goto err1;
}
if (!build_conn_str(id, conn_str)) {
LM_ERR("failed to build connection string\n");
goto err2;
}
LM_DBG("opening connection: unixodbc://xxxx:xxxx@%s/%s\n", ZSW(id->host),
ZSW(id->database));
ret = SQLDriverConnect(ptr->dbc, NULL, (SQLCHAR*)conn_str, SQL_NTS,
outstr, sizeof(outstr), &outstrlen,
SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret))
{
LM_DBG("connection succeeded with reply <%s>\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO)
{
LM_DBG("driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", ptr->dbc, SQL_HANDLE_DBC, NULL);
}
}
else
{
LM_ERR("failed to connect\n");
extract_error("SQLDriverConnect", ptr->dbc, SQL_HANDLE_DBC, NULL);
goto err2;
}
ptr->stmt_handle = NULL;
ptr->timestamp = time(0);
ptr->id = id;
return ptr;
err1:
SQLFreeHandle(SQL_HANDLE_ENV, &(ptr->env));
if (ptr) pkg_free(ptr);
return 0;
err2:
SQLFreeHandle(SQL_HANDLE_ENV, &(ptr->env));
SQLFreeHandle(SQL_HANDLE_DBC, &(ptr->dbc));
if (ptr) pkg_free(ptr);
return 0;
}
void test_log_message()
{
printf("test_log_message\n");
SQLHENV env;
SQLHDBC dbc;
SQLRETURN ret; /* ODBC API return status */
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
ret = SQLDriverConnect(dbc, NULL, inString, SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret)) { printf("connected. Returned connection string was:\n\t%s\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO) {
printf("Driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
} else {
printf("Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
/*-------------------------------------------------*/
SQLHSTMT stmt;
RETCODE r;
char errorMsg[LOG_MSG_WIDTH];
/* Allocate a statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
r = SQLPrepare(stmt, (SQLCHAR *)"CREATE TABLE process_log (process_id bigint NOT NULL, order_id integer NOT NULL, finish_date timestamp, PRIMARY KEY (process_id));", SQL_NTS);
if (r != SQL_SUCCESS) {
printf("SQLPrepare: executeSqlStatement failed\n");
}
else
{
r = SQLExecute(stmt);
if (!((r == SQL_SUCCESS) || (r == SQL_SUCCESS_WITH_INFO)))
{
printf("return value: %d\n", r);
log_message( dbErrorMsg(env, dbc, stmt, &errorMsg[0], SQL_MAX_MESSAGE_LENGTH), LOG_TYPE_ERROR );
printf("Error:%s\n", dbErrorMsg(env, dbc, stmt, &errorMsg[0], LOG_MSG_WIDTH));
printf("SQLExecute: executeSqlStatement failed\n");
}
}
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
/*-------------------------------------------------*/
/* free up allocated handles */
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
void getDatabases()
{
printf("getPostgreSQLDatabases\n");
SQLHENV env;
SQLHDBC dbc;
SQLRETURN ret; /* ODBC API return status */
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
ret = SQLDriverConnect(dbc, NULL, inString, SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret)) { printf("connected. Returned connection string was:\n\t%s\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO) {
printf("Driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
} else {
printf("Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
/*-------------------------------------------------*/
// check existing databases for Netezza or PostgreSQL
SQLCHAR string_val[1024];
SQLSMALLINT string_len;
int isPostgreSQL;
char seqQueryStr[1024];
SQLHSTMT stmtQueryDbs;
int i;
SQLCHAR buf[1][64];
SQLINTEGER indicator;
SQLSMALLINT columns;
printf("\nSQL_DBMS_NAME test\n");
memset(string_val, '\0', 1024);
ret = SQLGetInfo(dbc, SQL_DBMS_NAME, string_val, sizeof(string_val), &string_len);
print_string_with_error_checking(ret, "SQL_DBMS_NAME", string_val);
isPostgreSQL = isPostgreSQLDatabase(dbc);
memset(seqQueryStr, '\0', 1024);
if (isPostgreSQL > 0) {
printf("PostgreSQL database\n");
strcpy(seqQueryStr, "select datname from pg_database where datistemplate = false and datname='temp_db'");
} else {
printf("Netezza database\n");
strcpy(seqQueryStr, "select database from _V_DATABASE where database='temp_db';");
}
printf("query string: %s\n", &seqQueryStr[0]);
// query system table for database names
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmtQueryDbs);
ret = SQLPrepare(stmtQueryDbs, (SQLCHAR*)seqQueryStr, SQL_NTS);
if (ret != SQL_SUCCESS) {
printf("SQLPrepare: executeSqlStatement failed\n");
} else {
ret = SQLExecute(stmtQueryDbs);
if (!((ret == SQL_SUCCESS) || (ret == SQL_SUCCESS_WITH_INFO)))
{
printf("return value: %d\n", ret);
// printf("Error:%s\n", dbErrorMsg(env, dbc, stmtQueryDbs, &errorMsg[0], LOG_MSG_WIDTH));
printf("SQLExecute: executeSqlStatement failed\n");
}
}
SQLNumResultCols(stmtQueryDbs, &columns);
printf("columns: %d\n", columns);
for (i=0; i < columns; i++) {
SQLBindCol(stmtQueryDbs, i+1, SQL_C_CHAR, buf[i], sizeof(buf[i]), (SQLLEN*)&indicator);
}
while (SQL_SUCCEEDED(SQLFetch(stmtQueryDbs))) {
for (i=0; i < columns; i++) {
if (indicator == SQL_NULL_DATA) {
printf(" Column %u : NULL\n", i);
}
else {
if (strlen((char*)(buf[i])) > 0) {
printf(" Column %u : %s\n", i, buf[i]);
} else {
printf(" Column %u : Empty string\n", i);
}
}
}
printf("\n");
}
/*-------------------------------------------------*/
/* free up allocated handles */
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
void getCatalogs()
{
printf("getCatalogs\n");
SQLHENV env;
SQLHDBC dbc;
SQLRETURN ret; /* ODBC API return status */
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
ret = SQLDriverConnect(dbc, NULL, inString, SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);
// ret = SQLConnect(dbc, "DSNtestPostgres", SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS);
if (SQL_SUCCEEDED(ret)) { printf("connected. Returned connection string was:\n\t%s\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO) {
printf("Driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
} else {
printf("Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
/*-------------------------------------------------*/
SQLHSTMT stmt;
SQLSMALLINT columns; /* number of columns in result-set */
SQLCHAR buf[5][64];
SQLINTEGER indicator[ 5 ];
int i;
/* Allocate a statement handle */
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
/* Retrieve a list of tables */
SQLTables(stmt, (SQLCHAR*)SQL_ALL_CATALOGS, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS );
/* How many columns are there */
SQLNumResultCols(stmt, &columns);
/* Loop through the rows in the result-set binding to */
/* local variables */
for (i = 0; i < columns; i++) {
SQLBindCol( stmt, i + 1, SQL_C_CHAR, buf[i], sizeof(buf[i]), (SQLLEN*)&indicator[i]);
}
/* Fetch the data */
while (SQL_SUCCEEDED(SQLFetch(stmt))) {
/* display the results that will now be in the bound area's */
for ( i = 0; i < columns; i ++ ) {
if (indicator[ i ] == SQL_NULL_DATA) {
printf(" Column %u : NULL\n", i);
}
else {
if (strlen((char*)(buf[i])) > 0) {
printf(" Column %u : %s\n", i, buf[i]);
} else {
printf(" Column %u : Empty string\n", i);
}
}
}
printf("\n");
}
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
/*-------------------------------------------------*/
/* free up allocated handles */
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
/* w blob_number identyfikator obiektu binarnego w tabeli tblob */
long db2_bmd_db_export_blob(void *hDB,GenBuf_t *dataGenBuf,char **blob_number)
{
DB_bmd_db_conn_t *db_conn=NULL;
long err=0;
#ifndef BLOB_IN_BYTEA
long lobj_fd=0,tmp=0;
Oid lobjId ;
#else
char *SQLQuery = NULL;
char *c_tmp = NULL;
long fetched_counter = 0;
long rows = 0;
long cols = 0;
//Oid in_oid[]={0,17}; /* varchar, bytea */
char* params[]={NULL,NULL}; //{chunk_size,bobj};
int params_length[2]={0,0}; //nalezy ustawic rozmiary przynajmniej dla danych nie tekstowych
//int params_format[2]={0,1}; /* text,binary */
void *bmd_db_res = NULL;
SQLRETURN rc;
#endif
PRINT_INFO("LIBBMDPGINF Exporting file to LO\n");
if (hDB==NULL)
return BMD_ERR_PARAM1;
if (dataGenBuf == NULL)
{ BMD_FOK(BMD_ERR_PARAM2); }
if (blob_number == NULL)
{ BMD_FOK(BMD_ERR_PARAM3); }
if ((*blob_number) != NULL)
{ BMD_FOK(BMD_ERR_PARAM3); }
if( (*((long *)hDB)) != BMD_DBCONN_MAGIC )
return BMD_ERR_INVALID_PTR;
db_conn=(DB_bmd_db_conn_t *)hDB;
if( db_conn->hdbc == NULL )
return BMD_ERR_NODATA;
SBT(hDB,db_conn);
/*
* Utworz large object.
*/
#ifndef BLOB_IN_BYTEA
//lobjId = lo_creat(db_conn->pg_conn, INV_READ | INV_WRITE);
if( lobjId == 0)
{
RBT(hDB,db_conn);
PRINT_ERROR("LIBBMDPGERR Error in creating large object - lo_creat.\n");
return BMD_ERR_OP_FAILED;
}
//lobj_fd = lo_open(db_conn->pg_conn, lobjId, INV_WRITE);
//if(lobj_fd == -1)
{
RBT(hDB,db_conn);
BMD_FOK(BMD_ERR_OP_FAILED);
}
//tmp = lo_write(db_conn->pg_conn, lobj_fd, (char *)dataGenBuf->buf, dataGenBuf->size);
if(tmp < dataGenBuf->size)
{
RBT(hDB,db_conn);
BMD_FOK(BMD_ERR_OP_FAILED);
}
//(void) lo_close(db_conn->pg_conn, lobj_fd);
asprintf(blob_number, "%li", (long)lobjId);
#else
/* pobierz sekwencj� numeru obiektu binarnego lobjId (blob_number)*/
asprintf(&SQLQuery, "VALUES NEXT VALUE FOR co_raster_id");
;
if(SQLQuery == NULL) return NO_MEMORY;
err = db2_bmd_db_execute_sql( hDB, SQLQuery,
&rows, &cols, (void **)&bmd_db_res);
/**if(SQLQuery != NULL) */{ free(SQLQuery); SQLQuery=NULL; }
if(err<BMD_OK)
{
PRINT_ERROR("LIBBMDSQLERR Error in while execute VALUES NEXT VALUE FOR co_raster_id\n");
db2_bmd_db_rollback_transaction(hDB, NULL);
if(SQLQuery != NULL) { free(SQLQuery); SQLQuery=NULL; }
return err;
}
err=db2_bmd_db_result_get_value(hDB,(void *)bmd_db_res,0,0,&c_tmp,FETCH_ABSOLUTE,&fetched_counter);
if(err<BMD_OK)
{
PRINT_ERROR("LIBBMDSQLERR Error while getting value\n");
db2_bmd_db_rollback_transaction(hDB, NULL);
db2_bmd_db_result_free((void**)&bmd_db_res);
if(SQLQuery != NULL) { free(SQLQuery); SQLQuery=NULL; }
return err;
}
db2_bmd_db_result_free((void**)&bmd_db_res);
bmd_db_res = NULL;
//wlasciwy etap zapisu
params[0] = (char *)c_tmp;
params[1] = (char *) dataGenBuf->buf;
params_length[0] = (int)strlen(c_tmp);
params_length[1] = dataGenBuf->size;
db_conn = (DB_bmd_db_conn_t *) hDB;
SQLCHAR *stmt = ( SQLCHAR * )"INSERT INTO tblob(fk_co_raster_id,bobj) VALUES (?, ?)";
//sizeof(params)/sizeof(params[0]),
//in_oid, (const char *const * )params, (const int*)params_length,
//(const int*)params_format, 1
rc = SQLAllocHandle(SQL_HANDLE_STMT, *(db_conn->hdbc), db_conn->hstmt);
if (rc != SQL_SUCCESS)
{
PRINT_ERROR("Error while esec sql query: INSERT INTO tblob...\n");
extract_error("SQLAllocHandle", *(db_conn->hstmt), SQL_HANDLE_STMT);
db2_bmd_db_rollback_transaction(hDB, NULL);
return BMD_DB_EXECUTION_ERROR;
}
rc = SQLBindParameter(*(db_conn->hstmt), 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_VARCHAR, params_length[0], 0, params[0], params_length[0], NULL);
if (rc != SQL_SUCCESS)
{
PRINT_ERROR(">--- Error while SQLBindParameter, (SQL_VARCHAR)\n");
extract_error("SQLAllocHandle", *(db_conn->hstmt), SQL_HANDLE_STMT);
db2_bmd_db_rollback_transaction(hDB, NULL);
return BMD_DB_EXECUTION_ERROR;
}
rc = SQLBindParameter(*(db_conn->hstmt), 2, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BLOB, params_length[1], 0, params[1], params_length[1], NULL);
if (rc != SQL_SUCCESS)
{
PRINT_ERROR(">--- Error while SQLBindParameter, (SQL_BLOB)\n");
extract_error("SQLAllocHandle", *(db_conn->hstmt), SQL_HANDLE_STMT);
db2_bmd_db_rollback_transaction(hDB, NULL);
return BMD_DB_EXECUTION_ERROR;
}
rc = SQLExecDirect(*(db_conn->hstmt), stmt, SQL_NTS);
if (rc != SQL_SUCCESS)
{
PRINT_ERROR("Driver reported the following diagnostics: ");
extract_error("SQLAllocHandle", *(db_conn->hstmt), SQL_HANDLE_STMT);
db2_bmd_db_rollback_transaction(hDB, NULL);
return BMD_DB_EXECUTION_ERROR;
}
*blob_number = c_tmp;
c_tmp = NULL; //przekazywane na zewnatrz funkcji
params[0] = NULL;
if (rc == SQL_SUCCESS)
{
/* success */
err = BMD_OK;
}
else
{
PRINT_ERROR("LIBBMDPINFO DBresultStatus(pq_db_res)\n");
db2_bmd_db_rollback_transaction(hDB, NULL);
BMD_FOK(BMD_ERR_NODATA);
}
#endif
EBT(hDB,db_conn);
return err;
}
/** \internal
* \brief The system call that implements the hthread_join functionality.
*
* \author Wesley Peck <*****@*****.**>
*
* This function is the system call routine which implements the
* hthread_join functionality for hthreads. The user space function
* hthread_join invokes this function to join to a thread.
*
* \param th The thread which the currently running thread will
* join with. This thread must be a valid thread which
* is in the joinable state and must not have another
* thread waiting for its termination.
* \param retval If this value is not NULL then the return value from
* the thread th will be stored into the location pointed
* to by retval.
* \return The return value is one of the following:
* - EINVAL : One of the parameters used to call this
* function is invalid
* - SUCCESS : The thread was joined successfully and is
* now exited
*/
Hint _syscall_join( hthread_t th, void **retval )
{
Huint exited;
Huint status;
// Print out a trace message about this system call
TRACE_PRINTF( TRACE_FINE, TRACE_SYSCALL, "SYSCALL: (OP=JOIN) (THR=0x%8.8x) (RET=0x%8.8x)\n", (Huint)th, (Huint)retval );
// Join the current thread with the given thread.
status = _join_thread( th );
// If there was an error trying to join the thread then we
// will return that error to the caller.
if( has_error(status) )
{
// If the thread that is being joined has already exited then we do
// not need to wait on it so this statement will not be executed. If
// the thread has not yet exited then we will release the proccessor
// without adding ourself back onto the ready-to-run queue. The thread
// we are joining will add us back to the queue once it has exited.
exited = extract_error( status );
if( exited != HT_ALREADY_EXITED )
{
DEBUG_PRINTF( "JOIN ERROR=0x%8.8x\n", exited );
return EINVAL;
}
}
// If the thread has not already exited then block the currently
// running thread until that thread has exited.
exited = extract_error( status );
if( exited != HT_ALREADY_EXITED )
{
_run_sched( Htrue );
}
// At this point the thread we are joining is guaranteed to have exited.
// We should store the return value of the thread into the retval
// variable. We then destroy and clear the thread so that some other
// process can make used of the thread ID.
if( retval != NULL )
{
// Commented out, as fast V-HWTI re-usage via smart dispatch will re-use a V-HWTI as soon as a thread is exited,
// thus causing the return value to be overwritten. The new HAL bootloop copies the return value to the threads.retval location
// just as SW threads do, so hardware thread's return values can be retrieved from the same place (as they are now preserved)
//if( threads[ th ].hardware != 0 ) *retval = (void**)_hwti_results( threads[th].hardware - HT_HWTI_COMMAND_OFFSET );
if( threads[ th ].hardware != 0 ) *retval = threads[th].retval;
else *retval = threads[th].retval;
}
// Deallocate the software side structures used to keep track of the
// thread that was just joined.
if( !_detached_thread( th ) )
{
_destroy_thread( th );
}
// Print out a trace message about this system call
TRACE_PRINTF( TRACE_FINE, TRACE_SYSCALL, "SYSCALL DONE: (OP=JOIN) (THR=0x%8.8x) (RET=0x%8.8x)\n", (Huint)th, (Huint)retval );
// Return successfully.
return SUCCESS;
}
void ODBCHandler::testQuery(const char* query, const char* dsn)
{
SQLHENV env;
SQLHDBC dbc;
SQLHSTMT stmt;
SQLRETURN ret; /* ODBC API return status */
SQLRETURN ret2; /* ODBC API return status */
SQLSMALLINT columns; /* number of columns in result-set */
int row = 0;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
/* Connect to the DSN mydsn */
/* You will need to change mydsn to one you have created and tested */
SQLDriverConnect(dbc, NULL, (SQLCHAR*) dsn, SQL_NTS,
NULL, 0, NULL, SQL_DRIVER_COMPLETE);
/* Allocate a statement handle */
ret = SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);
if (!SQL_SUCCEEDED(ret)) {
fprintf(stderr, "Failed to allocate stmt handle.\n");
extract_error("SQLAllocHandle", ODBC_con, SQL_HANDLE_DBC);
}
ret = SQLExecDirect(stmt, (SQLCHAR *)query, SQL_NTS);
if (!SQL_SUCCEEDED(ret)) {
fprintf(stderr, "Failed to execute SQLExecDirect [%s].\n", query);
extract_error("SQLExecDirect", ODBC_con, SQL_HANDLE_DBC);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
}
/* How many columns are there */
SQLNumResultCols(stmt, &columns);
/* Loop through the rows in the result-set */
while (SQL_SUCCEEDED(ret = SQLFetch(stmt))) {
SQLUSMALLINT i;
ColDesc c;
printf("Row %d\n", row++);
/* Loop through the columns */
for (i = 1; i <= columns; i++) {
SQLLEN indicator;
SQLCHAR buf[512];
/* retrieve column data as a string */
ret2 = SQLDescribeCol(stmt,i,
c.colName, sizeof(c.colName), &c.nameLen,
&c.dataType, &c.colSize, &c.decimalDigits, &c.nullable);
ret = SQLGetData(stmt, i, SQL_C_CHAR,buf, sizeof(buf), &indicator);
if (SQL_SUCCEEDED(ret)) {
/* Handle null columns */
if (indicator == SQL_NULL_DATA) strcpy((char*)buf, "NULL");
printf(" Column %s %u : %s\n", c.colName,i, buf);
}
}
}
}
void get_info_test()
{
printf("get_info_test\n");
SQLHENV env;
SQLHDBC dbc;
SQLRETURN ret; /* ODBC API return status */
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
ret = SQLDriverConnect(dbc, NULL, inString, SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret)) { printf("connected. Returned connection string was:\n\t%s\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO) {
printf("Driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
} else {
printf("Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
/*-------------------------------------------------*/
SQLCHAR string_val[1024];
SQLSMALLINT string_len;
int i;
for (i = 0; i < sizeof(driver_info) / sizeof(driver_info[0]); i++) {
memset(string_val, '\0', 1024);
ret = SQLGetInfo(dbc, driver_info[i].code, string_val, sizeof(string_val), &string_len);
print_string_with_error_checking(ret, driver_info[i].code_str, string_val);
}
printf("\nSQL_DBMS_NAME test\n");
memset(string_val, '\0', 1024);
ret = SQLGetInfo(dbc, SQL_DBMS_NAME, string_val, sizeof(string_val), &string_len);
print_string_with_error_checking(ret, "SQL_DBMS_NAME", string_val);
char seqQueryStr[1024];
memset(seqQueryStr, '\0', 1024);
if (strcmp(POSTGRESQL_NAME_LONG, (const char*)string_val) == 0) {
strcpy(seqQueryStr, "SELECT c.relname as sequence_name FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'S'");
// seqQueryStr[0] = "SELECT c.relname as sequence_name FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'S'";
} else if (strcmp(POSTGRESQL_NAME_SHORT, (const char*)string_val) == 0) {
strcpy(seqQueryStr, "SELECT c.relname as sequence_name FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'S'");
// seqQueryStr[0] = "SELECT c.relname as sequence_name FROM pg_class c INNER JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'S'";
} else if (strcmp(NETEZZA_NAME, (const char*)string_val) == 0) {
strcpy(seqQueryStr, "SELECT SEQ_ID as sequence_name FROM _VT_SEQUENCE");
// seqQueryStr[0] = "SELECT SEQ_ID as sequence_name FROM _VT_SEQUENCE";
}
printf("query string: %s\n", &seqQueryStr[0]);
if (strcmp(POSTGRESQL_NAME_LONG, (const char*)string_val) == 0) {
printf("Long postgresql driver name: %s\n", string_val);
} else if (strcmp(POSTGRESQL_NAME_SHORT, (const char*)string_val) == 0) {
printf("Short postgresql driver name: %s\n", string_val);
} else if (strcmp(NETEZZA_NAME, (const char*)string_val) == 0) {
printf("Netezza driver name: %s\n", string_val);
}
/* for string values
* memset(string_val, '\0', 1024);
* ret = SQLGetInfo(dbc, SQL_DATABASE_NAME, string_val, sizeof(string_val), &string_len);
* print_string_with_error_checking(ret, "SQL_DATABASE_NAME", string_val);
*
* SQLUINTEGER u_val;
* SQLUSMALLINT su_val;
* // for SQLUINTEGER values - mostly bitmasks , last two arguments ignored
* ret = SQLGetInfo(dbc, SQL_XXX, (SQLPOINTER)&u_val, 0, 0);
* if (SQL_SUCCEEDED(ret)) {
* printf("Returned value is %lx\n", u_val);
} else {
// error
}
// for SQLUSMALLINT values - mostly counts/limits, last two arguments ignored
ret = SQLGetInfo(dbc, SQL_XXX, (SQLPOINTER)&su_val, 0, 0);
if (SQL_SUCCEEDED(ret)) {
printf("Returned value is %u\n", su_val);
} else { // error
}
*/
/*-------------------------------------------------*/
/* free up allocated handles */
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
void get_foreign_keys()
{
printf("get_foreign_keys\n");
SQLHENV env;
SQLHDBC dbc;
SQLRETURN ret; /* ODBC API return status */
SQLCHAR outstr[1024];
SQLSMALLINT outstrlen;
/* Allocate an environment handle */
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
/* We want ODBC 3 support */
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
/* Allocate a connection handle */
SQLAllocHandle(SQL_HANDLE_DBC, env, &dbc);
ret = SQLDriverConnect(dbc, NULL, inString, SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);
if (SQL_SUCCEEDED(ret)) { printf("connected. Returned connection string was:\n\t%s\n", outstr);
if (ret == SQL_SUCCESS_WITH_INFO) {
printf("Driver reported the following diagnostics\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
}
} else {
printf("Failed to connect\n");
extract_error("SQLDriverConnect", dbc, SQL_HANDLE_DBC);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}
/*-------------------------------------------------*/
SQLHSTMT stmt;
SQLSMALLINT columns; /* number of columns in result-set */
SQLCHAR bufChar[10][64];
SQLSMALLINT bufSmallInt[4];
SQLINTEGER indicator[5];
/*----------------------------------*/
SQLCHAR errormsg[SQL_MAX_MESSAGE_LENGTH];
ret = SQLAllocStmt(dbc, &stmt);
if (ret != SQL_SUCCESS) {
extract_error("SQLAllocStmt", dbc, SQL_HANDLE_DBC); return;
}
ret = SQLForeignKeys(stmt,
NULL, 0, /* Primary catalog */
NULL, 0, /* Primary schema */
NULL, 0, /* Primary table */
NULL, 0, /* Foreign catalog */
NULL, 0, /* Foreign schema */
(SQLCHAR*)"ne_node_version", SQL_NTS /* Foreign table */
);
printf("SQLForeignKeys - ret: %d\n", ret);
if (ret != SQL_SUCCESS) {
printf("error: %s\n", dbErrorMsg(env, dbc, stmt, &errormsg[0], SQL_MAX_MESSAGE_LENGTH));
extract_error("SQLForeignKeys", stmt, SQL_HANDLE_STMT);
return;
}
SQLNumResultCols(stmt, &columns);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
/*-------------------------------------------------*/
/* free up allocated handles */
SQLDisconnect(dbc);
SQLFreeHandle(SQL_HANDLE_DBC, dbc);
SQLFreeHandle(SQL_HANDLE_ENV, env);
}