dui-odbc.c

Go to the documentation of this file.

/********************************************************************\
 * dui-odbc.c -- dui ODBC driver                                    *
 * Copyright (C) 2002 Linas Vepstas <linas@linas.org>               *
 * http://dwi.sourceforge.net                                       *
 *                                                                  *
 * This library is free software; you can redistribute it and/or    *
 * modify it under the terms of the GNU Lesser General Public       *
 * License as published by the Free Software Foundation; either     *
 * version 2.1 of the License, or (at your option) any later version.
 *                                                                  *
 * This library is distributed in the hope that it will be useful,  *
 * but WITHOUT ANY WARRANTY; without even the implied warranty of   *
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the    *
 * GNU Lesser General Public License for more details.              *
 *                                                                  *
 * You should have received a copy of the GNU Lesser General Public *
 * License along with this program; if not, contact:                *
 *                                                                  *
 * Free Software Foundation           Voice:  +1-617-542-5942       *
 * 59 Temple Place - Suite 330        Fax:    +1-617-542-2652       *
 * Boston, MA  02111-1307,  USA       gnu@gnu.org                   *
\********************************************************************/

#include "config.h"
#include "perr.h"

#ifdef USE_ODBC

#include <glib.h>
#include <sql.h>
#include <sqlext.h>
#include <string.h>

#include "dui-initdb-p.h"
#include "dui-odbc.h"

typedef struct DuiODBCConnection_s DuiODBCConnection;
typedef struct DuiODBCRecordSet_s  DuiODBCRecordSet;

struct DuiODBCConnection_s
{
    DuiDBConnection dbcon;
    const char * dbname;
    const char * username;
    SQLHENV sql_henv;
    SQLHDBC sql_hdbc;

    GList * free_pool;
};

struct DuiODBCRecordSet_s
{
    DuiDBRecordSet recset;
    DuiODBCConnection *conn;
    SQLHSTMT sql_hstmt;

    gint ncols;
    gint arrsize;
    gchar **column_labels;
    gint  *column_datatype;
    gchar **values;
    gint  *vsizes;
};

static void dui_odbc_recordset_free (DuiODBCRecordSet *rs);

/* =========================================================== */

#define PRINT_SQLERR(HTYPE,HAN)                                \
{                                                              \
    guchar sql_stat[10];                                          \
    SQLSMALLINT msglen;                                         \
    SQLINTEGER err;                                             \
    guchar msg[200];                                              \
                                                                \
    SQLGetDiagRec(HTYPE, HAN, 1, sql_stat,                      \
                  &err, msg, sizeof(msg), &msglen);             \
    PERR("(%d) %s\n", err, msg);                               \
}


/* =========================================================== */

DuiDBConnection *
dui_odbc_connection_new (const gchar * dbname, 
                         const gchar * username, 
                         const gchar * authentication)
{
    SQLRETURN rc;
    DuiODBCConnection *conn;

    ENTER ("(dbname=%s, username=%s)", dbname, username);
    if (NULL == dbname) return NULL;

    conn = g_new (DuiODBCConnection, 1);
    
    conn->free_pool = NULL;

    /* Allocate environment handle */
    rc = SQLAllocEnv(&conn->sql_henv);
    if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
    {
        PERR("Can't SQLAllocEnv, rc=%d", rc);
        g_free (conn);
        return NULL;
    }


    /* set the ODBC version */
    rc = SQLSetEnvAttr(conn->sql_henv, SQL_ATTR_ODBC_VERSION, 
                   (void*)SQL_OV_ODBC3, 0); 
    if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
    {
        PERR("Can't SQLSetEnv, rc=%d", rc);
        PRINT_SQLERR (SQL_HANDLE_ENV, conn->sql_henv);
        SQLFreeHandle(SQL_HANDLE_ENV, conn->sql_henv);
        g_free (conn);
        return NULL;
    }

    /* allocate the connection handle */
    rc = SQLAllocConnect(conn->sql_henv, &conn->sql_hdbc); 
    if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
    {
        PERR ("Can't SQLAllocConnect handle rc=%d", rc);
        PRINT_SQLERR (SQL_HANDLE_ENV, conn->sql_henv);
        SQLFreeHandle(SQL_HANDLE_ENV, conn->sql_henv);
        g_free (conn);
        return NULL;
    }

    /* set the timeout to 5 seconds ?? hack alert fixme */
    SQLSetConnectAttr(conn->sql_hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER *)5, 0);

    if (NULL == authentication) authentication = "";
    rc = SQLConnect(conn->sql_hdbc, 
             (SQLCHAR*) dbname, SQL_NTS,
             (SQLCHAR*) username, SQL_NTS,
             (SQLCHAR*) authentication, SQL_NTS);

    if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
    {
        PERR ("Can't perform SQLConnect rc=%d", rc);
        PRINT_SQLERR (SQL_HANDLE_DBC, conn->sql_hdbc);
        SQLFreeHandle(SQL_HANDLE_DBC, conn->sql_hdbc);
        SQLFreeHandle(SQL_HANDLE_ENV, conn->sql_henv);
        return NULL;
    }

    conn->dbname = g_strdup (dbname);
    conn->username = g_strdup (username);

    LEAVE ("(dbname=%s, username=%s)", dbname, username);
    return &conn->dbcon;
}

/* =========================================================== */

void
dui_odbc_connection_free (DuiDBConnection *dbc)
{
    DuiODBCConnection *conn = (DuiODBCConnection *) dbc;
    GList *node;

    if (!conn) return;

    if (conn->dbname) g_free ((gchar *) conn->dbname);
    conn->dbname = NULL;

    if (conn->username) g_free ((gchar *) conn->username);
    conn->username = NULL;

    SQLDisconnect(conn->sql_hdbc);
    SQLFreeHandle(SQL_HANDLE_DBC, conn->sql_hdbc);
    conn->sql_hdbc = NULL;

    SQLFreeHandle(SQL_HANDLE_ENV, conn->sql_henv);
    conn->sql_henv = NULL;
    
    for (node=conn->free_pool; node; node=node->next)
    {
        dui_odbc_recordset_free (node->data);
    }
    g_list_free (conn->free_pool);
    conn -> free_pool = NULL;
}

/* =========================================================== */

#define DEFAULT_VARCHAR_SIZE 4040

static void
dui_odbc_recordset_alloc_and_bind_cols (DuiODBCRecordSet *rs, gint ncols)
{
    SQLLEN err;
    SQLRETURN rc;
    gint i;

    if (ncols > rs->arrsize)
    {
        rs->column_labels = g_renew (gchar *, rs->column_labels, ncols);
        rs->column_datatype = g_renew (gint, rs->column_datatype, ncols);
        rs->values = g_renew (gchar *, rs->values, ncols);
        rs->vsizes = g_renew (gint, rs->vsizes, ncols);

        /* intialize */
        for (i = rs->arrsize; i<ncols; i++)
        {
            rs->column_labels[i] = NULL;
            rs->column_datatype[i] = 0;
            rs->values[i] = NULL;
            rs->vsizes[i] = 0;
        }
        rs->arrsize = ncols; 
    }

    for (i=0; i<ncols; i++)
    {
        if (rs->column_labels[i]) g_free (rs->column_labels[i]);
        rs->column_labels[i] = NULL;
        rs->column_datatype[i] = 0;

        if (NULL == rs->values[i])
        {
            rs->values[i] = g_new (gchar, DEFAULT_VARCHAR_SIZE);
            rs->vsizes[i] = DEFAULT_VARCHAR_SIZE;
            rs->values[i][0] = 0;
        }
        rc = SQLBindCol(rs->sql_hstmt, i+1, SQL_C_CHAR, 
            rs->values[i], rs->vsizes[i], &err);
        if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
        {
            PERR ("Can't bind col=%d rc=%d", i, rc);
            PRINT_SQLERR (SQL_HANDLE_STMT, rs->sql_hstmt);
            return;
        }
    }
}

/* =========================================================== */
/* pseudo-private routine */

#define DEFAULT_NUM_COLS 50

static DuiODBCRecordSet *
dui_odbc_recordset_new (DuiODBCConnection *conn)
{
    SQLRETURN rc;
    DuiODBCRecordSet *rs;

    if (!conn) return NULL;
    if (conn->free_pool)
    {
        rs = conn->free_pool->data;
        conn->free_pool = g_list_remove (conn->free_pool, rs);
        rs->ncols = -1;
    }
    else
    {
    
        rs = g_new (DuiODBCRecordSet, 1);
        rs->conn = conn;
    
        rs->ncols = -1;
        rs->arrsize = 0;
        rs->column_labels = NULL;
        rs->column_datatype = NULL;
        rs->values = NULL;
        rs->vsizes = NULL;
    }

    rc = SQLAllocStmt (conn->sql_hdbc, &rs->sql_hstmt);
    if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
    {
        PERR("Can't allocate statment handle, rc=%d", rc);
        PRINT_SQLERR (SQL_HANDLE_STMT, rs->sql_hstmt);
        /* oops memory leak */
        return NULL;
    }

    dui_odbc_recordset_alloc_and_bind_cols (rs, DEFAULT_NUM_COLS);

    return rs;
}

/* =========================================================== */

void
dui_odbc_recordset_release (DuiDBRecordSet *recset)
{
    DuiODBCRecordSet *rs = (DuiODBCRecordSet *) recset;
    if (!rs) return;

    SQLFreeHandle(SQL_HANDLE_STMT, rs->sql_hstmt);
    rs->sql_hstmt = NULL;

    rs->conn->free_pool = g_list_prepend (rs->conn->free_pool, rs);

}

/* =========================================================== */

static void
dui_odbc_recordset_free (DuiODBCRecordSet *rs)
{
    gint i;

    if (!rs) return;

    rs->conn = NULL;

    for (i=0; i<rs->arrsize; i++)
    {
        g_free (rs->column_labels[i]);
        g_free (rs->values[i]);
    }
    g_free (rs->column_labels);
    rs->column_labels = NULL;

    g_free (rs->column_datatype);
    rs->column_datatype = NULL;

    g_free (rs->values);
    rs->values = NULL;

    g_free (rs->vsizes);
    rs->vsizes = NULL;

    g_free (rs);
}

/* =========================================================== */

DuiDBRecordSet *
dui_odbc_connection_exec (DuiDBConnection *dbc, const char * buff)
{
    DuiODBCConnection *conn = (DuiODBCConnection *) dbc;
    DuiODBCRecordSet *rs;
    SQLRETURN rc;

    ENTER ("(conn=%p, buff=%s)", conn, buff);
    g_return_val_if_fail (conn, NULL);

    rs = dui_odbc_recordset_new (conn);
    g_return_val_if_fail (rs, NULL);

    rc = SQLExecDirect(rs->sql_hstmt, (guchar *)buff, SQL_NTS);

    if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
    {
        PERR ("Can't perform query rc=%d", rc);
        PRINT_SQLERR (SQL_HANDLE_STMT, rs->sql_hstmt);
        dui_odbc_recordset_release (&rs->recset);
        return NULL;
    }

    LEAVE ("(conn=%p, buff=%s)", conn, buff);
    /* Use number of columns to indicate that the query hasn't 
     * given results yet. */
    rs->ncols = -1;
    return &rs->recset;
}


/* =========================================================== */

DuiDBRecordSet *
dui_odbc_connection_tables (DuiDBConnection *dbc)
{
    DuiODBCConnection *conn = (DuiODBCConnection *) dbc;
    DuiODBCRecordSet *rs;
    SQLRETURN rc;

    ENTER ("(conn=%p)", conn);
    if (!conn) return NULL;

    rs = dui_odbc_recordset_new (conn);
    if (!rs) return NULL;

    rc = SQLTables (rs->sql_hstmt,NULL, 0, NULL, 0, NULL,0, NULL, 0);

    if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
    {
        PERR ("Can't perform query rc=%d", rc);
        PRINT_SQLERR (SQL_HANDLE_STMT, rs->sql_hstmt);
        dui_odbc_recordset_release (&rs->recset);
        return NULL;
    }

    LEAVE ("(conn=%p)", conn);
    /* Use number of columns to indicate that the query hasn't 
     * given results yet. */
    rs->ncols = -1;
    return &rs->recset;
}


/* =========================================================== */

DuiDBRecordSet *
dui_odbc_connection_table_columns (DuiDBConnection *dbc, 
                                   const gchar * tablename)
{
    DuiODBCConnection *conn = (DuiODBCConnection *) dbc;
    DuiODBCRecordSet *rs;
    SQLRETURN rc;

    ENTER ("(conn=%p, table=%s)", conn, tablename);
    if (!conn || !tablename) return NULL;

    rs = dui_odbc_recordset_new (conn);
    if (!rs) return NULL;

   rc=SQLColumns (rs->sql_hstmt,NULL, 0, NULL, 0, 
                  (guchar *) tablename, SQL_NTS, NULL, 0);

    if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
    {
        PERR ("Can't perform query rc=%d", rc);
        PRINT_SQLERR (SQL_HANDLE_STMT, rs->sql_hstmt);
        dui_odbc_recordset_release (&rs->recset);
        return NULL;
    }

    LEAVE ("(conn=%p, table=%s)", conn, tablename);
    /* Use number of columns to indicate that the query hasn't 
     * given results yet. */
    rs->ncols = -1;
    return &rs->recset;
}


/* =========================================================== */

static void
dui_odbc_recordset_get_column_labels (DuiODBCRecordSet *rs)
{
    SQLSMALLINT ncols;
    SQLRETURN rc;
    gint i;

    if (0 <= rs->ncols) return;

    /* If number of columns is negative, then we haven't 
     * gotten any results back yet.  Start by getting the 
     * column labels. 
     */

    rc = SQLNumResultCols (rs->sql_hstmt, &ncols);
    if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
    {
        PERR ("Can't get num columns rc=%d", rc);
        PRINT_SQLERR (SQL_HANDLE_STMT, rs->sql_hstmt);
        return;
    }

    if (ncols > rs->arrsize)
    {
        PERR( "screwed not enough columns !! ");
        ncols =  rs->arrsize;
    }

    for (i=0; i<ncols; i++)
    {
        guchar namebuff[300];
        SQLSMALLINT namelen;
        SQLULEN column_size;
        SQLSMALLINT datatype;
        SQLSMALLINT decimal_digits;
        SQLSMALLINT nullable;

        rc = SQLDescribeCol (rs->sql_hstmt, i+1, namebuff, 299, &namelen,
             &datatype, &column_size, &decimal_digits, &nullable);
        if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
        {
            PERR ("Can't describe col rc=%d", rc);
            PRINT_SQLERR (SQL_HANDLE_STMT, rs->sql_hstmt);
            return;
        }

        namebuff[namelen] = 0x0;
        PINFO ("column %d has name\'%s\'", i, namebuff);

        g_free (rs->column_labels[i]);
        rs->column_labels[i] = g_strdup ((gchar*)namebuff);
        rs->column_datatype[i] = datatype;
    }

    rs->ncols = ncols;
}

/* =========================================================== */

static int
get_col_by_name (DuiODBCRecordSet *rs, const gchar * fieldname)
{
    gint i;
    const gchar * fp;

    /* lookup the column number based on the column name */
    for (i=0; i<rs->ncols; i++)
    {
        if (!strcasecmp (fieldname, rs->column_labels[i])) return i;
    }

    /* oops. Try removing the table name if possible */
    fp = strrchr (fieldname, '.');
    if (!fp) return -1;
    fp ++;

    for (i=0; i<rs->ncols; i++)
    {
        if (!strcasecmp (fp, rs->column_labels[i])) return i;
    }

    return -1;
}

/* =========================================================== */

static gint
dui_odbc_recordset_rewind (DuiDBRecordSet *recset)
{
    /* DuiODBCRecordSet *rs = (DuiODBCRecordSet *) recset; */
    
    return dui_odbc_recordset_fetch_row (recset);
}

/* =========================================================== */

gint
dui_odbc_recordset_fetch_row (DuiDBRecordSet *recset)
{
    DuiODBCRecordSet *rs = (DuiODBCRecordSet *) recset;
    SQLRETURN rc;

    g_return_val_if_fail (rs, 0);

    rc = SQLFetch(rs->sql_hstmt);  

    /* no more data */
    if (SQL_NO_DATA == rc) return 0;
    if (SQL_NULL_DATA == rc) return 0;

    if ((SQL_SUCCESS != rc) && (SQL_SUCCESS_WITH_INFO != rc))
    {
        PERR ("Can't fetch row rc=%d", rc);
        PRINT_SQLERR (SQL_HANDLE_STMT, rs->sql_hstmt);
        return 0;
    }

    return 1;
}

/* =========================================================== */

const gchar *
dui_odbc_recordset_get_value (DuiDBRecordSet *recset, const gchar * fieldname)
{
    DuiODBCRecordSet *rs = (DuiODBCRecordSet *) recset;
    gint column;

    g_return_val_if_fail (rs, NULL);

    /* If number of columns is negative, then we haven't 
     * gotten any results back yet.  Start by getting the 
     * column labels. 
     */
    if (0 > rs->ncols)
    {
        dui_odbc_recordset_get_column_labels (rs);
    }

    column = get_col_by_name (rs, fieldname);
    if (0 > column) return NULL;

    LEAVE ("(rs=%p, fieldname=%s) {val=\'%s\'}", rs, fieldname,  rs->values[column]);
    return rs->values[column];
}

/* =========================================================== */

static void
dui_odbc_plugin_free (DuiDBPlugin *plg)
{
    g_free (plg);
}

/* =========================================================== */

static DuiDBPlugin *
dui_odbc_plugin_new (void)
{
    DuiDBPlugin *plg;
    plg = g_new0 (DuiDBPlugin, 1);
    plg->db_provider_name = "odbc";
    plg->plugin_free = dui_odbc_plugin_free;
    plg->connection_new = dui_odbc_connection_new;
    plg->connection_free = dui_odbc_connection_free;
    plg->connection_exec = dui_odbc_connection_exec;
    plg->connection_tables = dui_odbc_connection_tables;
    plg->connection_table_columns = dui_odbc_connection_table_columns;
    plg->recordset_free = dui_odbc_recordset_release;
    plg->recordset_rewind = dui_odbc_recordset_rewind;
    plg->recordset_fetch_row = dui_odbc_recordset_fetch_row;
    plg->recordset_get_value = dui_odbc_recordset_get_value;
    plg->recordset_get_error = NULL;

    return plg;
}

#endif /* USE_ODBC */
/* =========================================================== */

void 
dui_odbc_init (void)
{
#ifdef USE_ODBC
    DuiDBPlugin *plg;
    plg = dui_odbc_plugin_new();
    dui_db_provider_register (plg);
#else

    PERR ("The DWI db drivers were compiled without ODBC support");
#endif /* USE_ODBC */
}

/* ============================= END OF FILE ================= */
 

---