Differences

This shows you the differences between two versions of the page.

Link to this comparison view

goplayer:dbconnector [2011/09/08 05:56]
aiartificer
goplayer:dbconnector [2011/09/13 17:31] (current)
aiartificer
Line 116: Line 116:
 /* /*
  ============================================================================  ============================================================================
- Name        : DBConnectorTest.c+ Name        : DBConnector.c
  Author      : Stephen Cannon  Author      : Stephen Cannon
  Version     : 0.1  Version     : 0.1
Line 140: Line 140:
  */  */
  
-#include <windows.h> 
-#include <stdio.h> 
-//#include <tchar.h> 
-// DEBUG 
-#include <sql.h> 
-#include <sqlext.h> 
-// END DEBUG 
-#define _CRTDBG_MAP_ALLOC 
-#include <stdlib.h> 
-#include <crtdbg.h> 
-#include <string.h> 
 #include "DBConnector.h" #include "DBConnector.h"
-#include "assert.h" 
  
  
-void initDestroyTest()+/** 
 + * Initializes the DBConnector 
 + * @param connectionString The connection string to use for this DB connection 
 + * @return Returns true on error, false on success. 
 + */ 
 +char DBConnector__init(DBConnector *self, const char *connectionString)
 { {
- DBConnector dbConnection;+ self->connectionString = connectionString; 
 + 
 + if(!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &self->h_env))) 
 + return ERR_GENERAL_ERROR; 
 + if(!SQL_SUCCEEDED(SQLSetEnvAttr(self->h_env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0))) 
 + return ERR_GENERAL_ERROR; 
 + if(!SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, self->h_env, &self->h_dbc))) 
 + return ERR_GENERAL_ERROR;
  
- assert(!DBConnector__init(&amp;dbConnection, + self-&gt;state DBCONNECTOR_STATE_NOT_CONNECTED
- "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=test;User=root;Password=francis841;Option=3;"))+ return 0;
- assert(!DBConnector__del(&dbConnection));+
 } }
  
-void initConnectDisconnectDestroyTest()+/** 
 + * Connects to the DB.  This method must be called after DBConnector__init(
 + * @return Returns true on error, false on success. 
 + */ 
 +char DBConnector__connect(DBConnector *self)
 { {
- DBConnector dbConnection;+ SQLRETURN retVal; 
 + SQLSMALLINT dwLength; 
 + 
 + // Check that DBConnector is not already connected 
 + if(self->state > DBCONNECTOR_STATE_NOT_CONNECTED) 
 + return ERR_ALREADY_CONNECTED; 
 + 
 + if(!SQL_SUCCEEDED(retVal = SQLDriverConnect(self->h_dbc, 
 + 0, 
 + (SQLCHAR *)self->connectionString, 
 + SQL_NTS, 
 + 0, 
 + 0, 
 + &dwLength, 
 + SQL_DRIVER_NOPROMPT))) 
 +
 + __DBConnector__extractError("SQLDriverConnect", self->h_dbc, SQL_HANDLE_DBC); 
 + return (char)retVal; 
 +
 + if(!SQL_SUCCEEDED(retVal = SQLAllocHandle(SQL_HANDLE_STMT, self->h_dbc, &self->h_stmt))) 
 +
 + __DBConnector__extractError("SQLDriverConnect", self->h_dbc, SQL_HANDLE_DBC); 
 + return (char)retVal; 
 + }
  
- assert(!DBConnector__init(&amp;dbConnection, + self-&gt;state DBCONNECTOR_STATE_CONNECTED
- "Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=test;User=root;Password=francis841;Option=3;"))+ return 0;
- assert(!DBConnector__connect(&dbConnection)); +
- assert(!DBConnector__disconnect(&dbConnection)); +
- assert(!DBConnector__del(&dbConnection));+
 } }
  
-void queryDBTest()+/** 
 + * Prepares a statement to query the DB with.  This method must be called after 
 + * DBConnector__connect(
 + * @param stmt The prepared statement with "?" to represent bound parameters to 
 + * be filled in later 
 + * @param paramBinding An array of ParamBindings describing each parameter 
 + * described with a "?" in the given statement 
 + * @param numParams The number of ParamBindings in the paramBinding parameter 
 + * @param colBinding An array of ColBindings describing each column in the 
 + * result set generated after a query is made with the given statement 
 + * @param numCols The number of ColBindings in the colBinding parameter 
 + * @return Returns true on error, false on success. 
 + */ 
 +char DBConnector__prepareStatement(DBConnector *self, const char *stmt, ParamBinding *paramBinding, size_t numParams, ColBinding *colBinding, size_t numCols)
 { {
 + SQLRETURN retVal;
  size_t i = 0;  size_t i = 0;
- char error; 
- SQLINTEGER clsInput; 
- SQLINTEGER clsInputInd = 0; 
- SQLINTEGER s1, r1, s2, r2, s3, r3, s4, r4, s5, r5, cls; 
- SQLBIGINT id, freq; 
- SQLLEN s1Len, r1Len, s2Len, r2Len, s3Len, r3Len, s4Len, r4Len, s5Len, r5Len, clsLen, idLen, freqLen; 
- DBConnector dbConnection; 
- ParamBinding pBinding[1] = {{SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 10, 0, &clsInput, 0, &clsInputInd}}; 
- ColBinding cBinding[13] = 
- { 
- {SQL_C_SLONG, &s1, 0, &s1Len}, 
- {SQL_C_SLONG, &r1, 0, &r1Len}, 
- {SQL_C_SLONG, &s2, 0, &s2Len}, 
- {SQL_C_SLONG, &r2, 0, &r2Len}, 
- {SQL_C_SLONG, &s3, 0, &s3Len}, 
- {SQL_C_SLONG, &r3, 0, &r3Len}, 
- {SQL_C_SLONG, &s4, 0, &s4Len}, 
- {SQL_C_SLONG, &r4, 0, &r4Len}, 
- {SQL_C_SLONG, &s5, 0, &s5Len}, 
- {SQL_C_SLONG, &r5, 0, &r5Len}, 
- {SQL_C_SLONG, &cls, 0, &clsLen}, 
- {SQL_C_SBIGINT, &id, 0, &idLen}, 
- {SQL_C_SBIGINT, &freq, 0, &freqLen} 
- }; 
  
- // Connect + // Check that DBConnector is connected 
- assert(!DBConnector__init(&amp;dbConnection, + if(self-&gt;state &ltDBCONNECTOR_STATE_CONNECTED
- &quot;Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=test;User=root;Password=francis841;Option=3;";)); + return ERR_NOT_CONNECTED;
- assert(!DBConnector__connect(&dbConnection));+
  
- // Prepare statement + // Prepare statement handle 
- assert(!DBConnector__prepareStatement(&amp;dbConnection, "SELECT * FROM  `goscratchpad`.`poker_testing` WHERE CLASS = ?;", pBinding1, cBinding, 13));+ self->stmt = (SQLCHAR *)stmt; 
 + if(!SQL_SUCCEEDED(retVal = SQLPrepare(self-&gt;h_stmtself->stmt, SQL_NTS))) 
 +
 + __DBConnector__extractError("SQLPrepare", self->h_stmtSQL_HANDLE_STMT)
 + return (char)retVal; 
 + }
  
- // Make query and verify all hands are flushes + // Bind parameters to buffers 
- i = 0; + for(i = 0; i <numParams && &lt(size_t)-1; i++)
- clsInput = 5; +
- assert(!DBConnector__executePreparedStatement(&dbConnection)); +
- while(DBConnector__fetchExecutedStatementResult(&dbConnection, &amp;error))+
  {  {
- assert(clsInput == cls); + //SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, EMPLOYEE_ID_LEN, 0, szEmployeeID, 0, &;cbEmployeeID); 
- assert(s1 == s2); + if(!SQL_SUCCEEDED(retVal SQLBindParameter(self->;h_stmt, 
- assert(s2 == s3)+ i + 1, 
- assert(s3 == s4); + paramBinding[i].ioType, 
- assert(s4 == s5); + paramBinding[i].valueType, 
- assert(!error); + paramBinding[i].paramType, 
- i++;+ paramBinding[i].columnSize, 
 + paramBinding[i].decimalDigits, 
 + paramBinding[i].paramValuePtr, 
 + paramBinding[i].bufferLength, 
 + paramBinding[i].indPtr))
 +
 + __DBConnector__extractError("SQLBindParameter", self->h_stmt, SQL_HANDLE_STMT); 
 + return (char)retVal
 + }
  }  }
- assert(i = 1996); + 
- = 0; + // Bind columns to buffers to collect results 
- clsInput = 8; + for(i = 0; i <numCols && &lt(size_t)-1; i++)
- assert(!DBConnector__executePreparedStatement(&dbConnection)); +
- while(DBConnector__fetchExecutedStatementResult(&dbConnection, &amp;error))+
  {  {
- assert(clsInput == cls)+ if(!SQL_SUCCEEDED(retVal SQLBindCol(self->;h_stmt, 
- assert(s1 == s2); + i + 1, 
- assert(s2 == s3); + colBinding[i].type, 
- assert(s3 == s4); + colBinding[i].valuePtr, 
- assert(s4 == s5); + colBinding[i].bufferLength, 
- assert(!error); + colBinding[i].indPtr))
- i++;+ { 
 + __DBConnector__extractError("SQLBindCol", self->;h_stmt, SQL_HANDLE_STMT); 
 + return (char)retVal
 + }
  }  }
- assert(i 12)+ 
- i = 0; + self->state DBCONNECTOR_STATE_STMT_PREPARED
- clsInput = 9; + return 0; 
- assert(!DBConnector__executePreparedStatement(&amp;dbConnection)); +} 
- while(DBConnector__fetchExecutedStatementResult(&amp;dbConnection, &error))+ 
 +/** 
 + * Executes the prepared statement.  This method must be called after 
 + * DBConnector__prepareStatement(
 + * @return Returns true on error, false on success. 
 + */ 
 +char DBConnector__executePreparedStatement(DBConnector *self) 
 +
 + SQLRETURN retVal; 
 + 
 + // Check that there is a prepared statement 
 + if(self->state &ltDBCONNECTOR_STATE_STMT_PREPARED) 
 + return ERR_NO_PREPARED_STMT
 + 
 + if(!SQL_SUCCEEDED(retVal = SQLExecute(self-&gt;h_stmt)))
  {  {
- assert(clsInput == cls); + __DBConnector__extractError(";SQLExecute";, self->;h_stmt, SQL_HANDLE_STMT); 
- assert(s1 == s2); + return (char)retVal;
- assert(s2 == s3); +
- assert(s3 == s4); +
- assert(s4 == s5)+
- assert(!error); +
- i++;+
  }  }
- assert(i = 3); 
  
- // Disconnect + self-&gt;state = DBCONNECTOR_STATE_STMT_EXECUTED
- assert(!DBConnector__disconnect(&amp;dbConnection))+ return 0;
- assert(!DBConnector__del(&dbConnection));+
 } }
  
-void wrongOrderTestTest()+/** 
 + * Returns a single result from the result set generated when the DB is 
 + * queried.  This method must be called after 
 + * DBConnector__executePreparedStatement(
 + * @param error [OUT] If an error occors, the error value is returned here. 
 + * Otherwise, the value returned here is 0 
 + * @return Returns true if there are more results to retrieve in the result set 
 + * and no error has occured, false otherwise. 
 + */ 
 +char DBConnector__fetchExecutedStatementResult(DBConnector *self, char *error)
 { {
- DBConnector dbConnection;+ SQLRETURN retVal;
  
- assert(!DBConnector__init(&amp;dbConnection, + // Check that there is a prepared statement that was executed 
- ";Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=test;User=root;Password=francis841;Option=3;")); + if(self-&gt;state < DBCONNECTOR_STATE_STMT_EXECUTED) 
- assert(DBConnector__disconnect(&amp;dbConnection) == ERR_NOT_CONNECTED); + return ERR_STMT_NOT_EXECUTED; 
- assert(!DBConnector__connect(&dbConnection)); + 
- assert(!DBConnector__disconnect(&dbConnection))+ // Fetch the next line of results from the result set 
- assert(!DBConnector__del(&;dbConnection));+ if(!SQL_SUCCEEDED(retVal SQLFetch(self->;h_stmt))) 
 +
 + // Lack of success might just mean at end of result set 
 + if(retVal == SQL_NO_DATA || retVal == SQL_STILL_EXECUTING) 
 + *error 0; 
 + else 
 +
 + __DBConnector__extractError("SQLFetch";, self-&gt;h_stmt, SQL_HANDLE_STMT); 
 + *error = (char)retVal
 +
 + 
 + return 0
 +
 + 
 + // No errors so set error to 0 
 + *error = 0; 
 + 
 + return retVal == SQL_SUCCESS || SQL_SUCCESS_WITH_INFO;
 } }
  
 +/**
 + * Disconnects from the DB.  This method must be called after
 + * DBConnector__connect()
 + * @return Returns true on error, false on success.
 + */
 +char DBConnector__disconnect(DBConnector *self)
 +{
 + SQLRETURN retVal;
  
-int mainy(int argcconst char* argv[])+ // Check that there is a prepared statement 
 + if(self->state < 1) 
 + return ERR_NOT_CONNECTED; 
 + 
 + if(!SQL_SUCCEEDED(retVal = SQLFreeHandle(SQL_HANDLE_STMTself->h_stmt))) 
 + return (char)retVal; 
 + if(!SQL_SUCCEEDED(retVal = SQLDisconnect(self->h_dbc))) 
 + return (char)retVal; 
 + 
 + self->state = DBCONNECTOR_STATE_NOT_CONNECTED; 
 + return 0; 
 +
 + 
 +/** 
 + * Destroys the DBConnector.  This method must be called after 
 + * DBConnector__init() 
 + * @return Returns true on error, false on success. 
 + */ 
 +char DBConnector__del(DBConnector *self)
 { {
- _CrtDumpMemoryLeaks(); + SQLRETURN retVal;
- _CrtSetDbgFlag ( _CRTDBG_ALLOC_MEM_DF | _CRTDBG_LEAK_CHECK_DF );+
  
- initDestroyTest()+ if(!SQL_SUCCEEDED(retVal = SQLFreeHandle(SQL_HANDLE_DBC, self->;h_dbc))) 
- initConnectDisconnectDestroyTest(); + return (char)retVal
- queryDBTest()+ if(!SQL_SUCCEEDED(retVal = SQLFreeHandle(SQL_HANDLE_ENV, self->;h_env))) 
- wrongOrderTestTest();+ return (char)retVal;
  
- printf(";no errors\npress enter key to continue...\n";);+ return 0; 
 +
 + 
 +void __DBConnector__extractError(const char *fn, SQLHANDLE handle, SQLSMALLINT type) 
 +
 +    SQLSMALLINT i = 0; 
 +    SQLINTEGER native; 
 +    SQLCHAR state[7]; 
 +    SQLCHAR text[1024]; 
 +    SQLSMALLINT len; 
 +    SQLRETURN ret;
  
- getchar();+    fprintf(stderr, 
 +            "\n" 
 +            "Error while running " 
 +            "%s\n\n", 
 +            fn);
  
- return EXIT_SUCCESS;+    printf("index : state : native : error message\n"); 
 +    do 
 +    { 
 +        ret = SQLGetDiagRec(type, handle, ++i, state, &native, text, 
 +                            sizeof(text), &len ); 
 +        if (SQL_SUCCEEDED(ret)) 
 +            printf("%ld:%s:%ld:%s\n", i, state, native, text); 
 + else 
 + printf("Error while running SQLGetDiagRec"); 
 +    } 
 +    while( ret == SQL_SUCCESS );
 } }
 </code> </code>

goplayer/dbconnector.txt · Last modified: 2011/09/13 17:31 by aiartificer