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

Go to the documentation of this file.
00001 /* odbcinterf.c interface to ODBC for ScriptBasic
00002 
00003 NTLIBS: odbc32.lib odbccp32.lib
00004 UXLIBS: -lodbc
00005 
00006 */
00007 #ifdef WIN32
00008 #include <windows.h>
00009 #endif
00010 #include <sql.h>
00011 #include <sqlext.h>
00012 
00013 #include <stdio.h>
00014 
00015 #define BYTE_TYPE_ALREADY_DEFINED 1
00016 #include "../../basext.h"
00017 
00018 
00019 /* The opened connections are stored in linked list */
00020 typedef struct _odbcHANDLE {
00021   SQLHANDLE hConn;
00022   SQLHANDLE hStmt;
00023   SQLHANDLE hDesc;
00024   SQLSMALLINT num_fields;
00025   struct _odbcHANDLE *next,*prev;
00026   } odbcHANDLE, *podbcHANDLE;
00027 
00028 /* each interpreter opens an environment and uses it to store connections */
00029 typedef struct _odbcOBJECT {
00030   SQLHANDLE hEnv;
00031   void *HandleArray;
00032   podbcHANDLE first;
00033   } odbcOBJECT, *podbcOBJECT;
00034 
00035 
00078 /* no such connection name as specified */
00079 #define ODBC_ERROR_NOCN 0x00081001
00080 /* bad connection this means an error in the configuration file  */
00081 #define ODBC_ERROR_BDCN 0x00081002
00082 /* connection was refused by the odbc subsystem */
00083 #define ODBC_ERROR_CREF 0x00081003
00084 /* execute returned error */
00085 #define ODBC_ERROR_EXEC 0x00081004
00086 /* there was no statement executed before fetch */
00087 #define ODBC_ERROR_NORS 0x00081005
00088 /* fetch requires left value */
00089 #define ODBC_ERROR_LVAL 0x00081006
00090 
00091 
00108 besSUB_ERRMSG
00109 
00110   switch( iError ){
00111     case ODBC_ERROR_NOCN: return "No such connection name.";
00112     case ODBC_ERROR_BDCN: return "Bad connection information in the configuration file.";
00113     case ODBC_ERROR_CREF: return "Connection refused.";
00114     case ODBC_ERROR_EXEC: return "Query execution error.";
00115     case ODBC_ERROR_NORS: return "There was no query before the fetch.";
00116     case ODBC_ERROR_LVAL: return "Fetch resures left value as second argument.";
00117      }
00118   return "Unknown error.";
00119 besEND
00120 
00121 besVERSION_NEGOTIATE
00122   return (int)INTERFACE_VERSION;
00123 besEND
00124 
00125 
00126 
00127 besDLL_MAIN
00128 
00129 SUPPORT_MULTITHREAD
00130 
00131 besSUB_PROCESS_START
00132   INIT_MULTITHREAD
00133   return 1;
00134 besEND
00135 
00136 besSUB_PROCESS_FINISH
00137 besEND
00138 
00139 besSUB_KEEP
00140   long lTC;
00141 
00142   /* get the actual value of the thread counter that counts the number of threads using the library
00143      currently */
00144   GET_THREAD_COUNTER(lTC);
00145   /* if this was the last thread then we will return 0 not to unload the library and therefore
00146      this thread finish should not be counted */
00147   if( lTC == 0 ){
00148     INC_THREAD_COUNTER
00149     }
00150   /* conver the long counter to (int) boolean */
00151   return lTC ? 1 : 0;
00152 besEND
00153 
00154 besSUB_START
00155   podbcOBJECT p;
00156   SQLRETURN ret;
00157 
00158   INITLOCK /* lock the init mutex */
00159   if( iFirst ){ /* if it was not initialized yet*/
00160     ret = SQLSetEnvAttr(NULL, /* we set the process level connection pooling */
00161                         SQL_ATTR_CONNECTION_POOLING ,
00162                         (SQLPOINTER)SQL_CP_ONE_PER_DRIVER, /* process level connection pooling is done */
00163                         0 /* this is ignored */
00164                         );
00165     iFirst = 0;/* successful initialization was done*/
00166     }
00167   INITUNLO /* unlock the init mutex */
00168 
00169   besMODULEPOINTER = besALLOC(sizeof(odbcOBJECT));
00170   if( besMODULEPOINTER == NULL )return COMMAND_ERROR_MEMORY_LOW;
00171   p = (podbcOBJECT)besMODULEPOINTER;
00172   p->HandleArray = NULL;
00173   p->first = NULL; /* list of opened ODBC handles */
00174   /* allocate the environment */
00175   ret = SQLAllocHandle(SQL_HANDLE_ENV,SQL_NULL_HANDLE,&(p->hEnv));
00176   if( ret == SQL_ERROR )return COMMAND_ERROR_MEMORY_LOW;
00177   ret = SQLSetEnvAttr(p->hEnv,SQL_ATTR_CP_MATCH,SQL_CP_STRICT_MATCH ,0);
00178   ret = SQLSetEnvAttr(p->hEnv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER)SQL_OV_ODBC3,0);
00179   return 0;
00180 besEND
00181 
00182 besSUB_FINISH
00183   podbcOBJECT p;
00184   podbcHANDLE q;
00185 
00186   p = (podbcOBJECT)besMODULEPOINTER;
00187   if( p != NULL ){
00188     for( q = p->first ; q ; q = q->next ){
00189       SQLDisconnect(q->hConn);
00190       SQLFreeHandle(SQL_HANDLE_DBC,q->hConn);
00191       }
00192     besHandleDestroyHandleArray(p->HandleArray);
00193     }
00194   SQLFreeHandle(SQL_HANDLE_ENV,p->hEnv);
00195   return 0;
00196 besEND
00197 
00198 #define GET_DB_HANDLE \
00199   p = (podbcOBJECT)besMODULEPOINTER;\
00200   Argument = besARGUMENT(1);\
00201   besDEREFERENCE(Argument);\
00202   if( ! Argument )return EX_ERROR_TOO_FEW_ARGUMENTS;\
00203   Argument = besCONVERT2LONG(Argument);\
00204   q = besHandleGetPointer(p->HandleArray,LONGVALUE(Argument));\
00205   if( q == NULL )return COMMAND_ERROR_ARGUMENT_RANGE;
00206 
00207 
00208 static int _GetData(pSupportTable pSt,
00209                     LEFTVALUE Lval,
00210                     podbcHANDLE q,
00211                     long i,
00212                     long j
00213   ){
00214   char szTmp[256];
00215   SQLINTEGER cbCol,cbrCol;
00216   SQLRETURN ret;
00217   VARIABLE vDebug;
00218   long iIndex;
00219 
00220   /* first get the first 256 characters of the record */
00221   cbCol = 256;
00222   ret = SQLGetData(q->hStmt,
00223                    i+1,
00224                    SQL_C_CHAR,
00225                    szTmp,
00226                    cbCol,
00227                    &cbrCol);
00228   if( ! (SQL_SUCCEEDED(ret)) )return ODBC_ERROR_EXEC;
00229   if( cbrCol == 0 ){
00230     ARRAYVALUE(*Lval,j) = NULL;
00231     }else{
00232     vDebug = ARRAYVALUE(*Lval,j) = besNEWSTRING(cbrCol);
00233     if( vDebug == NULL )return COMMAND_ERROR_MEMORY_LOW;
00234     iIndex = 0;
00235     while( 1 ){
00236       memcpy(STRINGVALUE(vDebug)+iIndex,szTmp, (cbrCol < cbCol ? cbrCol : cbCol) );
00237       iIndex += cbCol-1;
00238       if( cbrCol <= cbCol )break;
00239       /* read more value */
00240       ret = SQLGetData(q->hStmt,
00241                        i+1,
00242                        SQL_C_CHAR,
00243                        szTmp,
00244                        cbCol,
00245                        &cbrCol);
00246       if( ! (SQL_SUCCEEDED(ret)) )return ODBC_ERROR_EXEC;
00247       }
00248     }
00249   return COMMAND_ERROR_SUCCESS;
00250   }
00251 
00275 besFUNCTION(odbc_fetcharray)
00276   VARIABLE Argument;
00277   LEFTVALUE Lval;
00278   podbcHANDLE q;
00279   SQLSMALLINT i;
00280   unsigned long __refcount_;
00281   podbcOBJECT p;
00282   SQLRETURN ret;
00283   int iError;
00284 
00285   GET_DB_HANDLE
00286 
00287   besRETURNVALUE = NULL;
00288   /* db , array */
00289   if( besARGNR < 2 )return EX_ERROR_TOO_FEW_ARGUMENTS;
00290 
00291   if( q->hStmt == NULL )return ODBC_ERROR_NORS;
00292 
00293   Argument = besARGUMENT(2);
00294 
00295   besLEFTVALUE(Argument,Lval);
00296   if( ! Lval )return ODBC_ERROR_LVAL;
00297 
00298   besRELEASE(*Lval);
00299   *Lval = NULL; /* to be safe it is undef until we fill in some new value */
00300 
00301   /* if there are no columns */
00302   if( q->num_fields == 0 ){
00303     besRETURNVALUE = NULL;
00304     return COMMAND_ERROR_SUCCESS;
00305     }
00306 
00307   *Lval = besNEWARRAY(0,q->num_fields-1);
00308   if( *Lval == NULL )return COMMAND_ERROR_MEMORY_LOW;
00309 
00310   /* fetch the next row */
00311   ret = SQLFetch(q->hStmt);
00312   if( ! (SQL_SUCCEEDED(ret)) ){
00313     besALLOC_RETURN_LONG;
00314     LONGVALUE(besRETURNVALUE) = 0;
00315     return COMMAND_ERROR_SUCCESS;
00316     }
00317 
00318   for( i= 0 ; i < q->num_fields ; i++ ){
00319 
00320     iError = _GetData(pSt,Lval,q,i,i);
00321     if( iError )return iError;
00322     }
00323   besALLOC_RETURN_LONG;
00324   LONGVALUE(besRETURNVALUE) = -1;
00325 besEND
00326 
00357 besFUNCTION(odbc_fetchhash)
00358   VARIABLE Argument;
00359   LEFTVALUE Lval;
00360   podbcHANDLE q;
00361   int i;
00362   unsigned long __refcount_;
00363   podbcOBJECT p;
00364   char *pszColNameBuffer;
00365   SQLSMALLINT cbColNameBuffer;
00366   SQLSMALLINT cbrColNameBuffer;
00367   SQLSMALLINT DataType;
00368   SQLUINTEGER ColSize;
00369   SQLSMALLINT DecimalDigits;
00370   SQLSMALLINT Nullable;
00371   SQLRETURN ret;
00372   int iError;
00373 
00374   /* the initial size of the buffer to store the name of the columns */
00375   cbColNameBuffer = 256;
00376 
00377   /* allocate the initial column name buffer */
00378   pszColNameBuffer = besALLOC(cbColNameBuffer);
00379   if( pszColNameBuffer == NULL )return COMMAND_ERROR_MEMORY_LOW;
00380 
00381   GET_DB_HANDLE
00382 
00383   besRETURNVALUE = NULL;
00384   /* db , array */
00385   if( besARGNR < 2 )return EX_ERROR_TOO_FEW_ARGUMENTS;
00386 
00387   if( q->hStmt == NULL )return ODBC_ERROR_NORS;
00388 
00389   Argument = besARGUMENT(2);
00390 
00391   besLEFTVALUE(Argument,Lval);
00392   if( ! Lval )return ODBC_ERROR_LVAL;
00393 
00394   besRELEASE(*Lval);
00395   *Lval = NULL; /* to be safe it is undef until we fill in some new value */
00396 
00397   /* if there are no columns */
00398   if( q->num_fields == 0 ){
00399     besRETURNVALUE = NULL;
00400     return COMMAND_ERROR_SUCCESS;
00401     }
00402 
00403   /* fetch the next row */
00404   ret = SQLFetch(q->hStmt);
00405   if( ! (SQL_SUCCEEDED(ret)) ){
00406     besALLOC_RETURN_LONG;
00407     LONGVALUE(besRETURNVALUE) = 0;
00408     return COMMAND_ERROR_SUCCESS;
00409     }
00410 
00411   *Lval = besNEWARRAY(0,2*q->num_fields-1);
00412   if( *Lval == NULL )return COMMAND_ERROR_MEMORY_LOW;
00413 
00414   for( i= 0 ; i < q->num_fields ; i++ ){
00415     ret = SQLDescribeCol(q->hStmt,
00416                          i+1,
00417                          pszColNameBuffer,
00418                          cbColNameBuffer,
00419                         &cbrColNameBuffer,
00420                         &DataType,
00421                         &ColSize,
00422                         &DecimalDigits,
00423                         &Nullable);
00424 
00425     /* if the column name is longer than the currently available buffer
00426        then reallocate the buffer and call the column describing function again*/
00427     if( cbrColNameBuffer > cbColNameBuffer - 1 ){
00428       cbColNameBuffer = cbrColNameBuffer +1;
00429       besFREE(pszColNameBuffer);
00430       pszColNameBuffer = besALLOC(cbColNameBuffer);
00431       if( pszColNameBuffer == NULL )return COMMAND_ERROR_MEMORY_LOW;
00432       ret = SQLDescribeCol(q->hStmt,
00433                            i+1,
00434                            pszColNameBuffer,
00435                            cbColNameBuffer,
00436                           &cbrColNameBuffer,
00437                           &DataType,
00438                           &ColSize,
00439                           &DecimalDigits,
00440                           &Nullable);
00441       }
00442 
00443     /* store the name of the column */
00444     ARRAYVALUE(*Lval,2*i) = besNEWSTRING(cbrColNameBuffer);
00445     if( ARRAYVALUE(*Lval,2*i) == NULL )return COMMAND_ERROR_MEMORY_LOW;
00446     memcpy(STRINGVALUE(ARRAYVALUE(*Lval,2*i)),pszColNameBuffer,
00447                                          STRLEN(ARRAYVALUE(*Lval,2*i)));
00448 
00449     iError = _GetData(pSt,Lval,q,i,2*i+1);
00450     if( iError )return iError;
00451     }
00452   besALLOC_RETURN_LONG;
00453   LONGVALUE(besRETURNVALUE) = -1;
00454 besEND
00455 
00471 besFUNCTION(odbc_affected_rows)
00472   VARIABLE Argument;
00473   podbcHANDLE q;
00474   podbcOBJECT p;
00475   SQLINTEGER nRow;
00476   SQLRETURN ret;
00477 
00478   p = (podbcOBJECT)besMODULEPOINTER;
00479   Argument = besARGUMENT(1);
00480   besDEREFERENCE(Argument);
00481   if( ! Argument )return EX_ERROR_TOO_FEW_ARGUMENTS;
00482   Argument = besCONVERT2LONG(Argument);
00483   q = besHandleGetPointer( p->HandleArray,LONGVALUE(Argument));
00484 
00485   ret = SQLRowCount(q->hStmt,&nRow);
00486   if( ret == SQL_ERROR )return ODBC_ERROR_EXEC;
00487   besALLOC_RETURN_LONG;
00488   LONGVALUE(besRETURNVALUE) = (long)nRow;
00489 besEND
00490 
00509 besFUNCTION(odbc_error)
00510   VARIABLE Argument;
00511   podbcHANDLE q;
00512   podbcOBJECT p;
00513   char szSqlState[256],szErrorMsg[256];
00514   SDWORD NativeError;
00515   SWORD cbErrorMsgMax=256;
00516 
00517   GET_DB_HANDLE
00518 
00519   SQLError(p->hEnv,q->hConn,q->hStmt,szSqlState,&NativeError,szErrorMsg,cbErrorMsgMax,&cbErrorMsgMax);
00520   besALLOC_RETURN_STRING(strlen(szErrorMsg));
00521   memcpy(STRINGVALUE(besRETURNVALUE),szErrorMsg,STRLEN(besRETURNVALUE));
00522 besEND
00523 
00541 besFUNCTION(odbc_query)
00542   VARIABLE Argument;
00543   podbcHANDLE q;
00544   podbcOBJECT p;
00545   SQLRETURN ret;
00546 
00547   GET_DB_HANDLE
00548 
00549   /* we need the db connection and the query */
00550   if( besARGNR < 2 )return EX_ERROR_TOO_FEW_ARGUMENTS;
00551 
00552   /* if there is any statement handle from any previous query then release it */
00553   if( q->hStmt ){
00554     SQLFreeHandle(SQL_HANDLE_STMT,q->hStmt);
00555     q->hStmt = NULL;
00556     }
00557 
00558   /* get the query */
00559   Argument = besARGUMENT(2);
00560   besDEREFERENCE(Argument);
00561   if( ! Argument )return EX_ERROR_TOO_FEW_ARGUMENTS;
00562 
00563   Argument = besCONVERT2STRING(Argument);
00564 
00565   ret = SQLAllocHandle(SQL_HANDLE_STMT,q->hConn,&(q->hStmt));
00566   if( ret == SQL_ERROR )return COMMAND_ERROR_MEMORY_LOW;
00567   ret = SQLExecDirect(q->hStmt,STRINGVALUE(Argument),STRLEN(Argument));
00568 
00569   if( ret == SQL_ERROR )return ODBC_ERROR_EXEC;
00570 
00571   ret = SQLNumResultCols(q->hStmt,&(q->num_fields));
00572   if( ret == SQL_ERROR )return ODBC_ERROR_EXEC;
00573   besRETURNVALUE = NULL;
00574 besEND
00575 
00592 besFUNCTION(odbc_close)
00593   VARIABLE Argument;
00594   podbcHANDLE q;
00595   podbcOBJECT p;
00596   unsigned long lHandle;
00597 
00598   GET_DB_HANDLE
00599   lHandle = LONGVALUE(Argument);
00600 
00601   SQLDisconnect(q->hConn);
00602   SQLFreeHandle(SQL_HANDLE_DBC,q->hConn);
00603 
00604   if( q->prev )
00605     q->prev->next = q->next;
00606   else
00607     p->first = q->next;
00608 
00609   if( q->next )
00610     q->next->prev = q->prev;
00611 
00612   besFREE(q);
00613   /* this was missing until v1.0b30 and it caused SB to stop when a once closed handle was used */
00614   besHandleFreeHandle(p->HandleArray,lHandle);
00615   besRETURNVALUE = NULL;
00616   return COMMAND_ERROR_SUCCESS;
00617 besEND
00618 
00646 besFUNCTION(odbc_real_connect)
00647   podbcHANDLE pH;
00648   VARIABLE Argument;
00649   char *pszDataSourceName,*pszUser,*pszPassword;
00650   podbcOBJECT p;
00651   SQLRETURN ret;
00652 
00653   p = (podbcOBJECT)besMODULEPOINTER;
00654 
00655   pH = besALLOC(sizeof(odbcHANDLE));
00656   if( pH == NULL )return COMMAND_ERROR_MEMORY_LOW;
00657 
00658   pH->hStmt = NULL;
00659 
00660   if( besARGNR < 3 )return EX_ERROR_TOO_FEW_ARGUMENTS;
00661 
00662   ret = SQLAllocHandle(SQL_HANDLE_DBC,p->hEnv,&(pH->hConn));
00663   if( ret == SQL_ERROR )return COMMAND_ERROR_MEMORY_LOW;
00664 
00665   /* Get the data source name */
00666   Argument = besARGUMENT(1);
00667   besDEREFERENCE(Argument);
00668   if( Argument ){
00669     besCONVERT2ZCHAR(Argument,pszDataSourceName);
00670   }else pszDataSourceName = NULL;
00671 
00672   /* Get the user */
00673   Argument = besARGUMENT(2);
00674   besDEREFERENCE(Argument);
00675   if( Argument ){
00676     besCONVERT2ZCHAR(Argument,pszUser);
00677   }else pszUser = NULL;
00678 
00679 
00680   /* Get the password */
00681   Argument = besARGUMENT(3);
00682   besDEREFERENCE(Argument);
00683   if( Argument ){
00684     besCONVERT2ZCHAR(Argument,pszPassword);
00685   }else pszPassword = NULL;
00686 
00687 
00688   ret = SQLConnect(pH->hConn,
00689                    pszDataSourceName,(SQLSMALLINT)strlen(pszDataSourceName),
00690                    pszUser,(SQLSMALLINT)strlen(pszUser),
00691                    pszPassword,(SQLSMALLINT)strlen(pszPassword));
00692   pH->hStmt = NULL;
00693   if( pszDataSourceName )besFREE(pszDataSourceName);
00694   if( pszUser           )besFREE(pszUser);
00695   if( pszPassword       )besFREE(pszPassword);
00696 
00697   if( SQL_SUCCEEDED(ret) ) {
00698 
00699     besALLOC_RETURN_LONG;
00700     if( p->first )p->first->prev = pH;
00701     pH->next = p->first;
00702     p->first = pH;
00703     pH->prev = NULL;
00704     LONGVALUE(besRETURNVALUE) = besHandleGetHandle(p->HandleArray,pH);
00705     return COMMAND_ERROR_SUCCESS;
00706     }else{
00707     besFREE(pH);
00708     besRETURNVALUE = NULL;
00709     return ODBC_ERROR_CREF;
00710     }
00711 
00712 besEND
00713 
00761 besFUNCTION(odbc_config_connect)
00762   VARIABLE Argument;
00763   SQLRETURN ret;
00764   char *pszDataSourceName,*pszUser,*pszPassword;
00765   podbcOBJECT p;
00766 #define CONFLEN 100
00767 #define CONFROOT "odbc.connections."
00768 #define MAXKL 20 /* the maximum key length used in connection config */
00769   char szConfigPath[CONFLEN],*pszConf;
00770   char *pszCname;
00771   podbcHANDLE pH;
00772 
00773   p = (podbcOBJECT)besMODULEPOINTER;
00774 
00775   pH = besALLOC(sizeof(odbcHANDLE));
00776   if( pH == NULL )return COMMAND_ERROR_MEMORY_LOW;
00777 
00778   ret = SQLAllocHandle(SQL_HANDLE_DBC,p->hEnv,&(pH->hConn));
00779   if( ret == SQL_ERROR )return COMMAND_ERROR_MEMORY_LOW;
00780 
00781   /* Get connection name  */
00782   Argument = besARGUMENT(1);
00783   besDEREFERENCE(Argument);
00784   if( Argument ){
00785     besCONVERT2ZCHAR(Argument,pszCname);
00786   }else return ODBC_ERROR_NOCN;
00787 
00788   strcpy(szConfigPath,CONFROOT);
00789   if( STRLEN(Argument) > CONFLEN - strlen(CONFROOT) - MAXKL )
00790     return ODBC_ERROR_BDCN;
00791   pszConf = szConfigPath + strlen(szConfigPath);
00792 
00793   memcpy(pszConf,STRINGVALUE(Argument),STRLEN(Argument));
00794   pszConf += STRLEN(Argument);
00795   *pszConf++ = '.';
00796 
00797   /* Get the data source name */
00798   strcpy(pszConf,"dsn");
00799   pszDataSourceName = besCONFIG(szConfigPath);
00800 
00801   /* Get the user */
00802   strcpy(pszConf,"user");
00803   pszUser = besCONFIG(szConfigPath);
00804 
00805   /* Get the password */
00806   strcpy(pszConf,"password");
00807   pszPassword = besCONFIG(szConfigPath);
00808 
00809   /* if there is no connection defined with that name then return error */
00810   if( NULL == pszDataSourceName ||
00811       NULL == pszUser           ||
00812       NULL == pszPassword       )return ODBC_ERROR_NOCN;
00813 
00814   ret = SQLConnect(pH->hConn,
00815                    pszDataSourceName,(SQLSMALLINT)strlen(pszDataSourceName),
00816                    pszUser,(SQLSMALLINT)strlen(pszUser),
00817                    pszPassword,(SQLSMALLINT)strlen(pszPassword));
00818   pH->hStmt = NULL;
00819 
00820   if( SQL_SUCCEEDED(ret) ){
00821     besALLOC_RETURN_LONG;
00822     if( p->first )p->first->prev = pH;
00823     pH->next = p->first;
00824     p->first = pH;
00825     pH->prev = NULL;
00826     LONGVALUE(besRETURNVALUE) = besHandleGetHandle(p->HandleArray,pH);
00827     return COMMAND_ERROR_SUCCESS;
00828     }else{
00829     besFREE(pH);
00830     besRETURNVALUE = NULL;
00831     return ODBC_ERROR_CREF;
00832     }
00833 
00834 besEND

Generated on Sun Mar 12 23:56:29 2006 for ScriptBasic by  doxygen 1.4.6-NO