G:/ScriptBasic/source/extensions/odbc/interface.c

Go to the documentation of this file.

/* odbcinterf.c interface to ODBC for ScriptBasic

NTLIBS: odbc32.lib odbccp32.lib
UXLIBS: -lodbc

*/
#ifdef WIN32
#include <windows.h>
#endif
#include <sql.h>
#include <sqlext.h>

#include <stdio.h>

#define BYTE_TYPE_ALREADY_DEFINED 1
#include "../../basext.h"


/* The opened connections are stored in linked list */
typedef struct _odbcHANDLE {
  SQLHANDLE hConn;
  SQLHANDLE hStmt;
  SQLHANDLE hDesc;
  SQLSMALLINT num_fields;
  struct _odbcHANDLE *next,*prev;
  } odbcHANDLE, *podbcHANDLE;

/* each interpreter opens an environment and uses it to store connections */
typedef struct _odbcOBJECT {
  SQLHANDLE hEnv;
  void *HandleArray;
  podbcHANDLE first;
  } odbcOBJECT, *podbcOBJECT;


/* no such connection name as specified */
#define ODBC_ERROR_NOCN 0x00081001
/* bad connection this means an error in the configuration file  */
#define ODBC_ERROR_BDCN 0x00081002
/* connection was refused by the odbc subsystem */
#define ODBC_ERROR_CREF 0x00081003
/* execute returned error */
#define ODBC_ERROR_EXEC 0x00081004
/* there was no statement executed before fetch */
#define ODBC_ERROR_NORS 0x00081005
/* fetch requires left value */
#define ODBC_ERROR_LVAL 0x00081006


besSUB_ERRMSG

  switch( iError ){
    case ODBC_ERROR_NOCN: return "No such connection name.";
    case ODBC_ERROR_BDCN: return "Bad connection information in the configuration file.";
    case ODBC_ERROR_CREF: return "Connection refused.";
    case ODBC_ERROR_EXEC: return "Query execution error.";
    case ODBC_ERROR_NORS: return "There was no query before the fetch.";
    case ODBC_ERROR_LVAL: return "Fetch resures left value as second argument.";
     }
  return "Unknown error.";
besEND

besVERSION_NEGOTIATE
  return (int)INTERFACE_VERSION;
besEND



besDLL_MAIN

SUPPORT_MULTITHREAD

besSUB_PROCESS_START
  INIT_MULTITHREAD
  return 1;
besEND

besSUB_PROCESS_FINISH
besEND

besSUB_KEEP
  long lTC;

  /* get the actual value of the thread counter that counts the number of threads using the library
     currently */
  GET_THREAD_COUNTER(lTC);
  /* if this was the last thread then we will return 0 not to unload the library and therefore
     this thread finish should not be counted */
  if( lTC == 0 ){
    INC_THREAD_COUNTER
    }
  /* conver the long counter to (int) boolean */
  return lTC ? 1 : 0;
besEND

besSUB_START
  podbcOBJECT p;
  SQLRETURN ret;

  INITLOCK /* lock the init mutex */
  if( iFirst ){ /* if it was not initialized yet*/
    ret = SQLSetEnvAttr(NULL, /* we set the process level connection pooling */
                        SQL_ATTR_CONNECTION_POOLING ,
                        (SQLPOINTER)SQL_CP_ONE_PER_DRIVER, /* process level connection pooling is done */
                        0 /* this is ignored */
                        );
    iFirst = 0;/* successful initialization was done*/
    }
  INITUNLO /* unlock the init mutex */

  besMODULEPOINTER = besALLOC(sizeof(odbcOBJECT));
  if( besMODULEPOINTER == NULL )return COMMAND_ERROR_MEMORY_LOW;
  p = (podbcOBJECT)besMODULEPOINTER;
  p->HandleArray = NULL;
  p->first = NULL; /* list of opened ODBC handles */
  /* allocate the environment */
  ret = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&(p->hEnv));
  if( ret == SQL_ERROR )return COMMAND_ERROR_MEMORY_LOW;
  ret = SQLSetEnvAttr(p->hEnv,SQL_ATTR_CP_MATCH,SQL_CP_STRICT_MATCH ,0);
  ret = SQLSetEnvAttr(p->hEnv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,0);
  return 0;
besEND

besSUB_FINISH
  podbcOBJECT p;
  podbcHANDLE q;

  p = (podbcOBJECT)besMODULEPOINTER;
  if( p != NULL ){
    for( q = p->first ; q ; q = q->next ){
      SQLDisconnect(q->hConn);
      SQLFreeHandle(SQL_HANDLE_DBC,q->hConn);
      }
    besHandleDestroyHandleArray(p->HandleArray);
    }
  SQLFreeHandle(SQL_HANDLE_ENV,p->hEnv);
  return 0;
besEND

#define GET_DB_HANDLE \
  p = (podbcOBJECT)besMODULEPOINTER;\
  Argument = besARGUMENT(1);\
  besDEREFERENCE(Argument);\
  if( ! Argument )return EX_ERROR_TOO_FEW_ARGUMENTS;\
  Argument = besCONVERT2LONG(Argument);\
  q = besHandleGetPointer(p->HandleArray,LONGVALUE(Argument));\
  if( q == NULL )return COMMAND_ERROR_ARGUMENT_RANGE;


static int _GetData(pSupportTable pSt,
                    LEFTVALUE Lval,
                    podbcHANDLE q,
                    long i,
                    long j
  ){
  char szTmp[256];
  SQLINTEGER cbCol,cbrCol;
  SQLRETURN ret;
  VARIABLE vDebug;
  long iIndex;

  /* first get the first 256 characters of the record */
  cbCol = 256;
  ret = SQLGetData(q->hStmt,
                   i+1,
                   SQL_C_CHAR,
                   szTmp,
                   cbCol,
                   &cbrCol);
  if( ! (SQL_SUCCEEDED(ret)) )return ODBC_ERROR_EXEC;
  if( cbrCol == 0 ){
    ARRAYVALUE(*Lval,j) = NULL;
    }else{
    vDebug = ARRAYVALUE(*Lval,j) = besNEWSTRING(cbrCol);
    if( vDebug == NULL )return COMMAND_ERROR_MEMORY_LOW;
    iIndex = 0;
    while( 1 ){
      memcpy(STRINGVALUE(vDebug)+iIndex,szTmp, (cbrCol < cbCol ? cbrCol : cbCol) );
      iIndex += cbCol-1;
      if( cbrCol <= cbCol )break;
      /* read more value */
      ret = SQLGetData(q->hStmt,
                       i+1,
                       SQL_C_CHAR,
                       szTmp,
                       cbCol,
                       &cbrCol);
      if( ! (SQL_SUCCEEDED(ret)) )return ODBC_ERROR_EXEC;
      }
    }
  return COMMAND_ERROR_SUCCESS;
  }

besFUNCTION(odbc_fetcharray)
  VARIABLE Argument;
  LEFTVALUE Lval;
  podbcHANDLE q;
  SQLSMALLINT i;
  unsigned long __refcount_;
  podbcOBJECT p;
  SQLRETURN ret;
  int iError;

  GET_DB_HANDLE

  besRETURNVALUE = NULL;
  /* db , array */
  if( besARGNR < 2 )return EX_ERROR_TOO_FEW_ARGUMENTS;

  if( q->hStmt == NULL )return ODBC_ERROR_NORS;

  Argument = besARGUMENT(2);

  besLEFTVALUE(Argument,Lval);
  if( ! Lval )return ODBC_ERROR_LVAL;

  besRELEASE(*Lval);
  *Lval = NULL; /* to be safe it is undef until we fill in some new value */

  /* if there are no columns */
  if( q->num_fields == 0 ){
    besRETURNVALUE = NULL;
    return COMMAND_ERROR_SUCCESS;
    }

  *Lval = besNEWARRAY(0,q->num_fields-1);
  if( *Lval == NULL )return COMMAND_ERROR_MEMORY_LOW;

  /* fetch the next row */
  ret = SQLFetch(q->hStmt);
  if( ! (SQL_SUCCEEDED(ret)) ){
    besALLOC_RETURN_LONG;
    LONGVALUE(besRETURNVALUE) = 0;
    return COMMAND_ERROR_SUCCESS;
    }

  for( i= 0 ; i < q->num_fields ; i++ ){

    iError = _GetData(pSt,Lval,q,i,i);
    if( iError )return iError;
    }
  besALLOC_RETURN_LONG;
  LONGVALUE(besRETURNVALUE) = -1;
besEND

besFUNCTION(odbc_fetchhash)
  VARIABLE Argument;
  LEFTVALUE Lval;
  podbcHANDLE q;
  int i;
  unsigned long __refcount_;
  podbcOBJECT p;
  char *pszColNameBuffer;
  SQLSMALLINT cbColNameBuffer;
  SQLSMALLINT cbrColNameBuffer;
  SQLSMALLINT DataType;
  SQLUINTEGER ColSize;
  SQLSMALLINT DecimalDigits;
  SQLSMALLINT Nullable;
  SQLRETURN ret;
  int iError;

  /* the initial size of the buffer to store the name of the columns */
  cbColNameBuffer = 256;

  /* allocate the initial column name buffer */
  pszColNameBuffer = besALLOC(cbColNameBuffer);
  if( pszColNameBuffer == NULL )return COMMAND_ERROR_MEMORY_LOW;

  GET_DB_HANDLE

  besRETURNVALUE = NULL;
  /* db , array */
  if( besARGNR < 2 )return EX_ERROR_TOO_FEW_ARGUMENTS;

  if( q->hStmt == NULL )return ODBC_ERROR_NORS;

  Argument = besARGUMENT(2);

  besLEFTVALUE(Argument,Lval);
  if( ! Lval )return ODBC_ERROR_LVAL;

  besRELEASE(*Lval);
  *Lval = NULL; /* to be safe it is undef until we fill in some new value */

  /* if there are no columns */
  if( q->num_fields == 0 ){
    besRETURNVALUE = NULL;
    return COMMAND_ERROR_SUCCESS;
    }

  /* fetch the next row */
  ret = SQLFetch(q->hStmt);
  if( ! (SQL_SUCCEEDED(ret)) ){
    besALLOC_RETURN_LONG;
    LONGVALUE(besRETURNVALUE) = 0;
    return COMMAND_ERROR_SUCCESS;
    }

  *Lval = besNEWARRAY(0,2*q->num_fields-1);
  if( *Lval == NULL )return COMMAND_ERROR_MEMORY_LOW;

  for( i= 0 ; i < q->num_fields ; i++ ){
    ret = SQLDescribeCol(q->hStmt,
                         i+1,
                         pszColNameBuffer,
                         cbColNameBuffer,
                        &cbrColNameBuffer,
                        &DataType,
                        &ColSize,
                        &DecimalDigits,
                        &Nullable);

    /* if the column name is longer than the currently available buffer
       then reallocate the buffer and call the column describing function again*/
    if( cbrColNameBuffer > cbColNameBuffer - 1 ){
      cbColNameBuffer = cbrColNameBuffer +1;
      besFREE(pszColNameBuffer);
      pszColNameBuffer = besALLOC(cbColNameBuffer);
      if( pszColNameBuffer == NULL )return COMMAND_ERROR_MEMORY_LOW;
      ret = SQLDescribeCol(q->hStmt,
                           i+1,
                           pszColNameBuffer,
                           cbColNameBuffer,
                          &cbrColNameBuffer,
                          &DataType,
                          &ColSize,
                          &DecimalDigits,
                          &Nullable);
      }

    /* store the name of the column */
    ARRAYVALUE(*Lval,2*i) = besNEWSTRING(cbrColNameBuffer);
    if( ARRAYVALUE(*Lval,2*i) == NULL )return COMMAND_ERROR_MEMORY_LOW;
    memcpy(STRINGVALUE(ARRAYVALUE(*Lval,2*i)),pszColNameBuffer,
                                         STRLEN(ARRAYVALUE(*Lval,2*i)));

    iError = _GetData(pSt,Lval,q,i,2*i+1);
    if( iError )return iError;
    }
  besALLOC_RETURN_LONG;
  LONGVALUE(besRETURNVALUE) = -1;
besEND

besFUNCTION(odbc_affected_rows)
  VARIABLE Argument;
  podbcHANDLE q;
  podbcOBJECT p;
  SQLINTEGER nRow;
  SQLRETURN ret;

  p = (podbcOBJECT)besMODULEPOINTER;
  Argument = besARGUMENT(1);
  besDEREFERENCE(Argument);
  if( ! Argument )return EX_ERROR_TOO_FEW_ARGUMENTS;
  Argument = besCONVERT2LONG(Argument);
  q = besHandleGetPointer( p->HandleArray,LONGVALUE(Argument));

  ret = SQLRowCount(q->hStmt,&nRow);
  if( ret == SQL_ERROR )return ODBC_ERROR_EXEC;
  besALLOC_RETURN_LONG;
  LONGVALUE(besRETURNVALUE) = (long)nRow;
besEND

besFUNCTION(odbc_error)
  VARIABLE Argument;
  podbcHANDLE q;
  podbcOBJECT p;
  char szSqlState[256],szErrorMsg[256];
  SDWORD NativeError;
  SWORD cbErrorMsgMax=256;

  GET_DB_HANDLE

  SQLError(p->hEnv,q->hConn,q->hStmt,szSqlState,&NativeError,szErrorMsg,cbErrorMsgMax,&cbErrorMsgMax);
  besALLOC_RETURN_STRING(strlen(szErrorMsg));
  memcpy(STRINGVALUE(besRETURNVALUE),szErrorMsg,STRLEN(besRETURNVALUE));
besEND

besFUNCTION(odbc_query)
  VARIABLE Argument;
  podbcHANDLE q;
  podbcOBJECT p;
  SQLRETURN ret;

  GET_DB_HANDLE

  /* we need the db connection and the query */
  if( besARGNR < 2 )return EX_ERROR_TOO_FEW_ARGUMENTS;

  /* if there is any statement handle from any previous query then release it */
  if( q->hStmt ){
    SQLFreeHandle(SQL_HANDLE_STMT,q->hStmt);
    q->hStmt = NULL;
    }

  /* get the query */
  Argument = besARGUMENT(2);
  besDEREFERENCE(Argument);
  if( ! Argument )return EX_ERROR_TOO_FEW_ARGUMENTS;

  Argument = besCONVERT2STRING(Argument);

  ret = SQLAllocHandle(SQL_HANDLE_STMT,q->hConn,&(q->hStmt));
  if( ret == SQL_ERROR )return COMMAND_ERROR_MEMORY_LOW;
  ret = SQLExecDirect(q->hStmt,STRINGVALUE(Argument),STRLEN(Argument));

  if( ret == SQL_ERROR )return ODBC_ERROR_EXEC;

  ret = SQLNumResultCols(q->hStmt,&(q->num_fields));
  if( ret == SQL_ERROR )return ODBC_ERROR_EXEC;
  besRETURNVALUE = NULL;
besEND

besFUNCTION(odbc_close)
  VARIABLE Argument;
  podbcHANDLE q;
  podbcOBJECT p;
  unsigned long lHandle;

  GET_DB_HANDLE
  lHandle = LONGVALUE(Argument);

  SQLDisconnect(q->hConn);
  SQLFreeHandle(SQL_HANDLE_DBC,q->hConn);

  if( q->prev )
    q->prev->next = q->next;
  else
    p->first = q->next;

  if( q->next )
    q->next->prev = q->prev;

  besFREE(q);
  /* this was missing until v1.0b30 and it caused SB to stop when a once closed handle was used */
  besHandleFreeHandle(p->HandleArray,lHandle);
  besRETURNVALUE = NULL;
  return COMMAND_ERROR_SUCCESS;
besEND

besFUNCTION(odbc_real_connect)
  podbcHANDLE pH;
  VARIABLE Argument;
  char *pszDataSourceName,*pszUser,*pszPassword;
  podbcOBJECT p;
  SQLRETURN ret;

  p = (podbcOBJECT)besMODULEPOINTER;

  pH = besALLOC(sizeof(odbcHANDLE));
  if( pH == NULL )return COMMAND_ERROR_MEMORY_LOW;

  pH->hStmt = NULL;

  if( besARGNR < 3 )return EX_ERROR_TOO_FEW_ARGUMENTS;

  ret = SQLAllocHandle(SQL_HANDLE_DBC,p->hEnv,&(pH->hConn));
  if( ret == SQL_ERROR )return COMMAND_ERROR_MEMORY_LOW;

  /* Get the data source name */
  Argument = besARGUMENT(1);
  besDEREFERENCE(Argument);
  if( Argument ){
    besCONVERT2ZCHAR(Argument,pszDataSourceName);
  }else pszDataSourceName = NULL;

  /* Get the user */
  Argument = besARGUMENT(2);
  besDEREFERENCE(Argument);
  if( Argument ){
    besCONVERT2ZCHAR(Argument,pszUser);
  }else pszUser = NULL;


  /* Get the password */
  Argument = besARGUMENT(3);
  besDEREFERENCE(Argument);
  if( Argument ){
    besCONVERT2ZCHAR(Argument,pszPassword);
  }else pszPassword = NULL;


  ret = SQLConnect(pH->hConn,
                   pszDataSourceName,(SQLSMALLINT)strlen(pszDataSourceName),
                   pszUser,(SQLSMALLINT)strlen(pszUser),
                   pszPassword,(SQLSMALLINT)strlen(pszPassword));
  pH->hStmt = NULL;
  if( pszDataSourceName )besFREE(pszDataSourceName);
  if( pszUser           )besFREE(pszUser);
  if( pszPassword       )besFREE(pszPassword);

  if( SQL_SUCCEEDED(ret) ) {

    besALLOC_RETURN_LONG;
    if( p->first )p->first->prev = pH;
    pH->next = p->first;
    p->first = pH;
    pH->prev = NULL;
    LONGVALUE(besRETURNVALUE) = besHandleGetHandle(p->HandleArray,pH);
    return COMMAND_ERROR_SUCCESS;
    }else{
    besFREE(pH);
    besRETURNVALUE = NULL;
    return ODBC_ERROR_CREF;
    }

besEND

besFUNCTION(odbc_config_connect)
  VARIABLE Argument;
  SQLRETURN ret;
  char *pszDataSourceName,*pszUser,*pszPassword;
  podbcOBJECT p;
#define CONFLEN 100
#define CONFROOT "odbc.connections."
#define MAXKL 20 /* the maximum key length used in connection config */
  char szConfigPath[CONFLEN],*pszConf;
  char *pszCname;
  podbcHANDLE pH;

  p = (podbcOBJECT)besMODULEPOINTER;

  pH = besALLOC(sizeof(odbcHANDLE));
  if( pH == NULL )return COMMAND_ERROR_MEMORY_LOW;

  ret = SQLAllocHandle(SQL_HANDLE_DBC,p->hEnv,&(pH->hConn));
  if( ret == SQL_ERROR )return COMMAND_ERROR_MEMORY_LOW;

  /* Get connection name  */
  Argument = besARGUMENT(1);
  besDEREFERENCE(Argument);
  if( Argument ){
    besCONVERT2ZCHAR(Argument,pszCname);
  }else return ODBC_ERROR_NOCN;

  strcpy(szConfigPath,CONFROOT);
  if( STRLEN(Argument) > CONFLEN - strlen(CONFROOT) - MAXKL )
    return ODBC_ERROR_BDCN;
  pszConf = szConfigPath + strlen(szConfigPath);

  memcpy(pszConf,STRINGVALUE(Argument),STRLEN(Argument));
  pszConf += STRLEN(Argument);
  *pszConf++ = '.';

  /* Get the data source name */
  strcpy(pszConf,"dsn");
  pszDataSourceName = besCONFIG(szConfigPath);

  /* Get the user */
  strcpy(pszConf,"user");
  pszUser = besCONFIG(szConfigPath);

  /* Get the password */
  strcpy(pszConf,"password");
  pszPassword = besCONFIG(szConfigPath);

  /* if there is no connection defined with that name then return error */
  if( NULL == pszDataSourceName ||
      NULL == pszUser           ||
      NULL == pszPassword       )return ODBC_ERROR_NOCN;

  ret = SQLConnect(pH->hConn,
                   pszDataSourceName,(SQLSMALLINT)strlen(pszDataSourceName),
                   pszUser,(SQLSMALLINT)strlen(pszUser),
                   pszPassword,(SQLSMALLINT)strlen(pszPassword));
  pH->hStmt = NULL;

  if( SQL_SUCCEEDED(ret) ){
    besALLOC_RETURN_LONG;
    if( p->first )p->first->prev = pH;
    pH->next = p->first;
    p->first = pH;
    pH->prev = NULL;
    LONGVALUE(besRETURNVALUE) = besHandleGetHandle(p->HandleArray,pH);
    return COMMAND_ERROR_SUCCESS;
    }else{
    besFREE(pH);
    besRETURNVALUE = NULL;
    return ODBC_ERROR_CREF;
    }

besEND

---