switch_odbc.c File Reference

#include <switch.h>

Include dependency graph for switch_odbc.c:

Go to the source code of this file.

Functions
switch_odbc_handle_t *	switch_odbc_handle_new (const char *dsn, const char *username, const char *password)
void	switch_odbc_set_num_retries (switch_odbc_handle_t *handle, int num_retries)
switch_odbc_status_t	switch_odbc_handle_disconnect (switch_odbc_handle_t *handle)
switch_odbc_status_t	switch_odbc_statement_handle_free (switch_odbc_statement_handle_t *stmt)
switch_odbc_status_t	switch_odbc_handle_connect (switch_odbc_handle_t *handle)
switch_odbc_status_t	switch_odbc_handle_exec_string (switch_odbc_handle_t *handle, const char *sql, char *resbuf, size_t len, char **err)
switch_odbc_status_t	switch_odbc_handle_exec (switch_odbc_handle_t *handle, const char *sql, switch_odbc_statement_handle_t *rstmt, char **err)
switch_odbc_status_t	switch_odbc_handle_callback_exec_detailed (const char *file, const char *func, int line, switch_odbc_handle_t *handle, const char *sql, switch_core_db_callback_func_t callback, void *pdata, char **err)
	Execute the sql query and issue a callback for each row returned. More...
void	switch_odbc_handle_destroy (switch_odbc_handle_t **handlep)
switch_odbc_state_t	switch_odbc_handle_get_state (switch_odbc_handle_t *handle)
char *	switch_odbc_handle_get_error (switch_odbc_handle_t *handle, switch_odbc_statement_handle_t stmt)
int	switch_odbc_handle_affected_rows (switch_odbc_handle_t *handle)
switch_bool_t	switch_odbc_available (void)
switch_odbc_status_t	switch_odbc_SQLSetAutoCommitAttr (switch_odbc_handle_t *handle, switch_bool_t on)
switch_odbc_status_t	switch_odbc_SQLEndTran (switch_odbc_handle_t *handle, switch_bool_t commit)

Function Documentation

switch_bool_t switch_odbc_available

(

void

)

Definition at line 738 of file switch_odbc.c.

References SWITCH_FALSE, and SWITCH_TRUE.

Referenced by _switch_cache_db_get_db_handle(), and switch_load_core_config().

{
#ifdef SWITCH_HAVE_ODBC
        return SWITCH_TRUE;
#else
        return SWITCH_FALSE;
#endif
}

int switch_odbc_handle_affected_rows

(

switch_odbc_handle_t *

handle

)

Definition at line 729 of file switch_odbc.c.

Referenced by switch_cache_db_affected_rows().

{
#ifdef SWITCH_HAVE_ODBC
        return handle->affected_rows;
#else
        return 0;
#endif
}

switch_odbc_status_t switch_odbc_handle_callback_exec_detailed	(	const char *	file,
		const char *	func,
		int	line,
		switch_odbc_handle_t *	handle,
		const char *	sql,
		switch_core_db_callback_func_t	callback,
		void *	pdata,
		char **	err
	)

Execute the sql query and issue a callback for each row returned.

Parameters

file	the file from which this function is called
func	the function from which this function is called
line	the line from which this function is called
handle	the ODBC handle
sql	the sql string to execute
callback	the callback function to execute
pdata	the state data passed on each callback invocation

Returns

SWITCH_STATUS_SUCCESS if the operation was successful

Note

none

Definition at line 540 of file switch_odbc.c.

References memset(), switch_assert, SWITCH_CHANNEL_ID_LOG, SWITCH_LOG_ERROR, switch_log_printf(), SWITCH_ODBC_FAIL, switch_odbc_handle_get_error(), SWITCH_ODBC_SUCCESS, switch_str_nil, and zstr.

{
#ifdef SWITCH_HAVE_ODBC
        SQLHSTMT stmt = NULL;
        SQLSMALLINT c = 0, x = 0;
        SQLLEN m = 0;
        char *x_err = NULL, *err_str = NULL;
        int result;
        int err_cnt = 0;
        int done = 0;

        handle->affected_rows = 0;

        switch_assert(callback != NULL);

        if (!db_is_up(handle)) {
                x_err = "DB is not up!";
                goto error;
        }

        if (SQLAllocHandle(SQL_HANDLE_STMT, handle->con, &stmt) != SQL_SUCCESS) {
                x_err = "Unable to SQL allocate handle!";
                goto error;
        }

        if (SQLPrepare(stmt, (unsigned char *) sql, SQL_NTS) != SQL_SUCCESS) {
                x_err = "Unable to prepare SQL statement!";
                goto error;
        }

        result = SQLExecute(stmt);

        if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO && result != SQL_NO_DATA) {
                x_err = "execute error!";
                goto error;
        }

        SQLNumResultCols(stmt, &c);
        SQLRowCount(stmt, &m);
        handle->affected_rows = (int) m;


        while (!done) {
                int name_len = 256;
                char **names;
                char **vals;
                int y = 0;

                result = SQLFetch(stmt);

                if (result != SQL_SUCCESS) {
                        if (result != SQL_NO_DATA) {
                                err_cnt++;
                        }
                        break;
                }

                names = calloc(c, sizeof(*names));
                vals = calloc(c, sizeof(*vals));

                switch_assert(names && vals);

                for (x = 1; x <= c; x++) {
                        SQLSMALLINT NameLength = 0, DataType = 0, DecimalDigits = 0, Nullable = 0;
                        SQLULEN ColumnSize = 0;
                        names[y] = malloc(name_len);
                        memset(names[y], 0, name_len);

                        SQLDescribeCol(stmt, x, (SQLCHAR *) names[y], (SQLSMALLINT) name_len, &NameLength, &DataType, &ColumnSize, &DecimalDigits, &Nullable);

                        if (!ColumnSize) {
                                ColumnSize = 255;
                        }
                        ColumnSize++;

                        vals[y] = malloc(ColumnSize);
                        memset(vals[y], 0, ColumnSize);
                        SQLGetData(stmt, x, SQL_C_CHAR, (SQLCHAR *) vals[y], ColumnSize, NULL);
                        y++;
                }

                if (callback(pdata, y, vals, names)) {
                        done = 1;
                }

                for (x = 0; x < y; x++) {
                        free(names[x]);
                        free(vals[x]);
                }
                free(names);
                free(vals);
        }

        SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        stmt = NULL; /* Make sure we don't try to free this handle again */
        
        if (!err_cnt) {
                return SWITCH_ODBC_SUCCESS;
        }

  error:

        if (stmt) {
                err_str = switch_odbc_handle_get_error(handle, stmt);
        }

        if (zstr(err_str) && !zstr(x_err)) {
                err_str = strdup(x_err);
        }

        if (err_str) {
                switch_log_printf(SWITCH_CHANNEL_ID_LOG, file, func, line, NULL, SWITCH_LOG_ERROR, "ERR: [%s]\n[%s]\n", sql, switch_str_nil(err_str));
                if (err) {
                        *err = err_str;
                } else {
                        free(err_str);
                }
        }

        if (stmt) {
                SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        }


#endif
        return SWITCH_ODBC_FAIL;
}

switch_odbc_status_t switch_odbc_handle_connect

(

switch_odbc_handle_t *

handle

)

Definition at line 336 of file switch_odbc.c.

References FALSE, SWITCH_CHANNEL_LOG, SWITCH_FALSE, SWITCH_LOG_DEBUG1, SWITCH_LOG_ERROR, switch_log_printf(), SWITCH_ODBC_FAIL, switch_odbc_handle_disconnect(), switch_odbc_handle_get_error(), SWITCH_ODBC_STATE_CONNECTED, SWITCH_ODBC_SUCCESS, SWITCH_TRUE, and TRUE.

Referenced by _switch_cache_db_get_db_handle().

{
#ifdef SWITCH_HAVE_ODBC
        int result;
        SQLINTEGER err;
        int16_t mlen;
        unsigned char msg[200] = "", stat[10] = "";
        SQLSMALLINT valueLength = 0;
        int i = 0;

        init_odbc_handles(handle, SWITCH_FALSE); /* Init ODBC handles, if they are already initialized, don't do it again */

        if (handle->state == SWITCH_ODBC_STATE_CONNECTED) {
                switch_odbc_handle_disconnect(handle);
                switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG1, "Re-connecting %s\n", handle->dsn);
        }

        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG1, "Connecting %s\n", handle->dsn);

        if (!strstr(handle->dsn, "DRIVER")) {
                result = SQLConnect(handle->con, (SQLCHAR *) handle->dsn, SQL_NTS, (SQLCHAR *) handle->username, SQL_NTS, (SQLCHAR *) handle->password, SQL_NTS);
        } else {
                SQLCHAR outstr[1024] = { 0 };
                SQLSMALLINT outstrlen = 0;
                result =
                        SQLDriverConnect(handle->con, NULL, (SQLCHAR *) handle->dsn, (SQLSMALLINT) strlen(handle->dsn), outstr, sizeof(outstr), &outstrlen,
                                                         SQL_DRIVER_NOPROMPT);
        }

        if ((result != SQL_SUCCESS) && (result != SQL_SUCCESS_WITH_INFO)) {
                char *err_str;
                if ((err_str = switch_odbc_handle_get_error(handle, NULL))) {
                        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "%s\n", err_str);
                        free(err_str);
                } else {
                        SQLGetDiagRec(SQL_HANDLE_DBC, handle->con, 1, stat, &err, msg, sizeof(msg), &mlen);
                        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "Error SQLConnect=%d errno=%d [%s]\n", result, (int) err, msg);
                }

                /* Deallocate handles again, more chanses to succeed when reconnecting */
                init_odbc_handles(handle, SWITCH_TRUE); /* Reinit ODBC handles */
                return SWITCH_ODBC_FAIL;
        }

        result = SQLGetInfo(handle->con, SQL_DRIVER_NAME, (SQLCHAR *) handle->odbc_driver, 255, &valueLength);
        if (result == SQL_SUCCESS || result == SQL_SUCCESS_WITH_INFO) {
                for (i = 0; i < valueLength; ++i)
                        handle->odbc_driver[i] = (char) toupper(handle->odbc_driver[i]);
        }

        if (strstr(handle->odbc_driver, "SQORA32.DLL") != 0 || strstr(handle->odbc_driver, "SQORA64.DLL") != 0) {
                handle->is_firebird = FALSE;
                handle->is_oracle = TRUE;
        } else if (strstr(handle->odbc_driver, "FIREBIRD") != 0 || strstr(handle->odbc_driver, "FB32") != 0 || strstr(handle->odbc_driver, "FB64") != 0) {
                handle->is_firebird = TRUE;
                handle->is_oracle = FALSE;
        } else {
                handle->is_firebird = FALSE;
                handle->is_oracle = FALSE;
        }

        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG1, "Connected to [%s]\n", handle->dsn);
        handle->state = SWITCH_ODBC_STATE_CONNECTED;
        return SWITCH_ODBC_SUCCESS;
#else
        return SWITCH_ODBC_FAIL;
#endif
}

void switch_odbc_handle_destroy

(

switch_odbc_handle_t **

handlep

)

Definition at line 671 of file switch_odbc.c.

References switch_odbc_handle_disconnect(), and switch_safe_free.

Referenced by _switch_cache_db_get_db_handle(), and sql_close().

{
#ifdef SWITCH_HAVE_ODBC

        switch_odbc_handle_t *handle = NULL;

        if (!handlep) {
                return;
        }
        handle = *handlep;

        if (handle) {
                switch_odbc_handle_disconnect(handle);

                if (handle->env != SQL_NULL_HANDLE) {
                        SQLFreeHandle(SQL_HANDLE_DBC, handle->con);
                        SQLFreeHandle(SQL_HANDLE_ENV, handle->env);
                }
                switch_safe_free(handle->dsn);
                switch_safe_free(handle->username);
                switch_safe_free(handle->password);
                free(handle);
        }
        *handlep = NULL;
#else
        return;
#endif
}

switch_odbc_status_t switch_odbc_handle_disconnect

(

switch_odbc_handle_t *

handle

)

Definition at line 119 of file switch_odbc.c.

References SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG10, SWITCH_LOG_ERROR, switch_log_printf(), SWITCH_ODBC_FAIL, SWITCH_ODBC_STATE_CONNECTED, SWITCH_ODBC_STATE_DOWN, and SWITCH_ODBC_SUCCESS.

Referenced by switch_odbc_handle_connect(), and switch_odbc_handle_destroy().

{
#ifdef SWITCH_HAVE_ODBC

        int result;

        if (!handle) {
                return SWITCH_ODBC_FAIL;
        }

        if (handle->state == SWITCH_ODBC_STATE_CONNECTED) {
                result = SQLDisconnect(handle->con);
                if (result == SWITCH_ODBC_SUCCESS) {
                        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_DEBUG10, "Disconnected %d from [%s]\n", result, handle->dsn);
                } else {
                        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "Error Disconnecting [%s]\n", handle->dsn);
                }
        }

        handle->state = SWITCH_ODBC_STATE_DOWN;

        return SWITCH_ODBC_SUCCESS;
#else
        return SWITCH_ODBC_FAIL;
#endif
}

switch_odbc_status_t switch_odbc_handle_exec	(	switch_odbc_handle_t *	handle,
		const char *	sql,
		switch_odbc_statement_handle_t *	rstmt,
		char **	err
	)

Definition at line 449 of file switch_odbc.c.

References SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, switch_log_printf(), SWITCH_ODBC_FAIL, switch_odbc_handle_get_error(), SWITCH_ODBC_SUCCESS, switch_str_nil, switch_stristr(), and zstr.

Referenced by switch_cache_db_execute_sql_real(), switch_cache_db_test_reactive(), and switch_odbc_handle_exec_string().

{
#ifdef SWITCH_HAVE_ODBC
        SQLHSTMT stmt = NULL;
        int result;
        char *err_str = NULL, *err2 = NULL;
        SQLLEN m = 0;

        handle->affected_rows = 0;

        if (!db_is_up(handle)) {
                goto error;
        }

        if (SQLAllocHandle(SQL_HANDLE_STMT, handle->con, &stmt) != SQL_SUCCESS) {
                err2 = "SQLAllocHandle failed.";
                goto error;
        }

        if (SQLPrepare(stmt, (unsigned char *) sql, SQL_NTS) != SQL_SUCCESS) {
                err2 = "SQLPrepare failed.";
                goto error;
        }

        result = SQLExecute(stmt);

        switch (result) {
        case SQL_SUCCESS:
        case SQL_SUCCESS_WITH_INFO:
        case SQL_NO_DATA:
                break;
        case SQL_ERROR:
                err2 = "SQLExecute returned SQL_ERROR.";
                goto error;
                break;
        case SQL_NEED_DATA:
                err2 = "SQLExecute returned SQL_NEED_DATA.";
                goto error;
                break;
        default:
                err2 = "SQLExecute returned unknown result code.";
                goto error;
        }

        SQLRowCount(stmt, &m);
        handle->affected_rows = (int) m;

        if (rstmt) {
                *rstmt = stmt;
        } else {
                SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        }

        return SWITCH_ODBC_SUCCESS;

  error:


        if (stmt) {
                err_str = switch_odbc_handle_get_error(handle, stmt);
        }

        if (zstr(err_str)) {
                if (err2) {
                        err_str = strdup(err2);
                } else {
                        err_str = strdup((char *)"SQL ERROR!");
                }
        }
        
        if (err_str) {
                if (!switch_stristr("already exists", err_str) && !switch_stristr("duplicate key name", err_str)) {
                        switch_log_printf(SWITCH_CHANNEL_LOG, SWITCH_LOG_ERROR, "ERR: [%s]\n[%s]\n", sql, switch_str_nil(err_str));
                }
                if (err) {
                        *err = err_str;
                } else {
                        free(err_str);
                }
        }

        if (rstmt) {
                *rstmt = stmt;
        } else if (stmt) {
                SQLFreeHandle(SQL_HANDLE_STMT, stmt);
        }
#endif
        return SWITCH_ODBC_FAIL;
}

switch_odbc_status_t switch_odbc_handle_exec_string	(	switch_odbc_handle_t *	handle,
		const char *	sql,
		char *	resbuf,
		size_t	len,
		char **	err
	)

Definition at line 405 of file switch_odbc.c.

References SWITCH_ODBC_FAIL, switch_odbc_handle_exec(), switch_odbc_statement_handle_free(), and SWITCH_ODBC_SUCCESS.

Referenced by switch_cache_db_execute_sql2str().

{
#ifdef SWITCH_HAVE_ODBC
        switch_odbc_status_t sstatus = SWITCH_ODBC_FAIL;
        switch_odbc_statement_handle_t stmt = NULL;
        SQLCHAR name[1024];
        SQLLEN m = 0;

        handle->affected_rows = 0;

        if (switch_odbc_handle_exec(handle, sql, &stmt, err) == SWITCH_ODBC_SUCCESS) {
                SQLSMALLINT NameLength, DataType, DecimalDigits, Nullable;
                SQLULEN ColumnSize;
                int result;

                SQLRowCount(stmt, &m);
                handle->affected_rows = (int) m;

                if (m == 0) {
                        goto done;
                }

                result = SQLFetch(stmt);

                if (result != SQL_SUCCESS && result != SQL_SUCCESS_WITH_INFO && result != SQL_NO_DATA) {
                        goto done;
                }

                SQLDescribeCol(stmt, 1, name, sizeof(name), &NameLength, &DataType, &ColumnSize, &DecimalDigits, &Nullable);
                SQLGetData(stmt, 1, SQL_C_CHAR, (SQLCHAR *) resbuf, (SQLLEN) len, NULL);

                sstatus = SWITCH_ODBC_SUCCESS;
        }

        done:

        switch_odbc_statement_handle_free(&stmt);

        return sstatus;
#else
        return SWITCH_ODBC_FAIL;
#endif
}

char* switch_odbc_handle_get_error	(	switch_odbc_handle_t *	handle,
		switch_odbc_statement_handle_t	stmt
	)

Definition at line 709 of file switch_odbc.c.

References switch_mprintf().

Referenced by switch_odbc_handle_callback_exec_detailed(), switch_odbc_handle_connect(), and switch_odbc_handle_exec().

{
#ifdef SWITCH_HAVE_ODBC

        char buffer[SQL_MAX_MESSAGE_LENGTH + 1] = "";
        char sqlstate[SQL_SQLSTATE_SIZE + 1] = "";
        SQLINTEGER sqlcode;
        SQLSMALLINT length;
        char *ret = NULL;

        if (SQLError(handle->env, handle->con, stmt, (SQLCHAR *) sqlstate, &sqlcode, (SQLCHAR *) buffer, sizeof(buffer), &length) == SQL_SUCCESS) {
                ret = switch_mprintf("STATE: %s CODE %ld ERROR: %s\n", sqlstate, sqlcode, buffer);
        };

        return ret;
#else
        return NULL;
#endif
}

switch_odbc_state_t switch_odbc_handle_get_state

(

switch_odbc_handle_t *

handle

)

Definition at line 700 of file switch_odbc.c.

References SWITCH_ODBC_STATE_ERROR, and SWITCH_ODBC_STATE_INIT.

{
#ifdef SWITCH_HAVE_ODBC
        return handle ? handle->state : SWITCH_ODBC_STATE_INIT;
#else
        return SWITCH_ODBC_STATE_ERROR;
#endif
}

switch_odbc_handle_t* switch_odbc_handle_new	(	const char *	dsn,
		const char *	username,
		const char *	password
	)

Definition at line 65 of file switch_odbc.c.

References DEFAULT_ODBC_RETRIES, memset(), SWITCH_ODBC_STATE_INIT, and switch_safe_free.

Referenced by _switch_cache_db_get_db_handle().

{
#ifdef SWITCH_HAVE_ODBC
        switch_odbc_handle_t *new_handle;

        if (!(new_handle = malloc(sizeof(*new_handle)))) {
                goto err;
        }

        memset(new_handle, 0, sizeof(*new_handle));

        if (!(new_handle->dsn = strdup(dsn))) {
                goto err;
        }

        if (username) {
                if (!(new_handle->username = strdup(username))) {
                        goto err;
                }
        }

        if (password) {
                if (!(new_handle->password = strdup(password))) {
                        goto err;
                }
        }

        new_handle->env = SQL_NULL_HANDLE;
        new_handle->state = SWITCH_ODBC_STATE_INIT;
        new_handle->affected_rows = 0;
        new_handle->num_retries = DEFAULT_ODBC_RETRIES;

        return new_handle;

  err:
        if (new_handle) {
                switch_safe_free(new_handle->dsn);
                switch_safe_free(new_handle->username);
                switch_safe_free(new_handle->password);
                switch_safe_free(new_handle);
        }
#endif
        return NULL;
}

void switch_odbc_set_num_retries	(	switch_odbc_handle_t *	handle,
		int	num_retries
	)

Definition at line 110 of file switch_odbc.c.

{
#ifdef SWITCH_HAVE_ODBC
        if (handle) {
                handle->num_retries = num_retries;
        }
#endif
}

switch_odbc_status_t switch_odbc_SQLEndTran	(	switch_odbc_handle_t *	handle,
		switch_bool_t	commit
	)

Definition at line 760 of file switch_odbc.c.

References SWITCH_FALSE.

Referenced by do_trans(), switch_cache_db_persistant_execute_trans_full(), and switch_core_sqldb_start().

{
#ifdef SWITCH_HAVE_ODBC
        if (commit) {
                return SQLEndTran(SQL_HANDLE_DBC, handle->con, SQL_COMMIT);
        } else {
                return SQLEndTran(SQL_HANDLE_DBC, handle->con, SQL_ROLLBACK);
        }
#else
        return (switch_odbc_status_t) SWITCH_FALSE;
#endif
}

switch_odbc_status_t switch_odbc_SQLSetAutoCommitAttr	(	switch_odbc_handle_t *	handle,
		switch_bool_t	on
	)

Definition at line 747 of file switch_odbc.c.

References SWITCH_FALSE.

Referenced by do_trans(), switch_cache_db_persistant_execute_trans_full(), and switch_core_sqldb_start().

{
#ifdef SWITCH_HAVE_ODBC
        if (on) {
                return SQLSetConnectAttr(handle->con, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER *) SQL_AUTOCOMMIT_ON, 0 );
        } else {
                return SQLSetConnectAttr(handle->con, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER *) SQL_AUTOCOMMIT_OFF, 0 );
        }
#else
        return (switch_odbc_status_t) SWITCH_FALSE;
#endif
}

switch_odbc_status_t switch_odbc_statement_handle_free

(

switch_odbc_statement_handle_t *

stmt

)

Definition at line 321 of file switch_odbc.c.

References SWITCH_ODBC_FAIL, and SWITCH_ODBC_SUCCESS.

Referenced by switch_odbc_handle_exec_string().

{
        if (!stmt || !*stmt) {
                return SWITCH_ODBC_FAIL;
        }
#ifdef SWITCH_HAVE_ODBC
        SQLFreeHandle(SQL_HANDLE_STMT, *stmt);
        *stmt = NULL;
        return SWITCH_ODBC_SUCCESS;
#else
        return SWITCH_ODBC_FAIL;
#endif
}