Articles - ODBC page 2
Getting Your Results
I have to be honest, you've done the hardest part. Getting the returned values back to OneWorld is actually very easy. The only easier thing is closing up your connections.
Again, The Code
The Data Structure:
ID idStatementHandle; /* use GENLNG for this*/ char szIdentifier2ndItem[26]; char szDescriptionLine1[31]; |
Just like always, define your variables. .
JDEBFRTN (ID) JDEBFWINAPI FetchFromTable (LPBHVRCOM lpBhvrCom, lpVoid, LPDSD55DRB lpDS)
{
/************************************************************************ * Variable declarations ************************************************************************/ SQLHSTMT StatementHandle = '\0'; short int ColumnCountPtr; char ColumnName[50]; SQLSMALLINT BufferLengthSm=0; SQLSMALLINT NameLengthPtr; SQLSMALLINT DataTypePtr; SQLUINTEGER ColumnSizePtr; SQLSMALLINT DecimalDigitsPtr; SQLSMALLINT NullablePtr; SQLINTEGER StrLen_or_Ind; HUSER hUser; SQLRETURN retcode; ID idJDEDBReturn = JDEDB_PASSED; /************************************************************************ * Check for NULL pointers ************************************************************************/ /** common code removed to save space . . . . */ |
Now, this is sort of important. Remember how we're passing pointers around in the data structure fields? Well, this next statement is how you get the pointer back from the structure: jdeRetrieveDataPtr. Hand it the integer we got in the last business function and it returns the pointer. You may notice that for our 'retrieve' business function we just need the StatementHandle pointer.
StatementHandle =(SQLHSTMT *) jdeRetrieveDataPtr(hUser, lpDS->idStatementHandle); |
We don't need this in the code, but it's interesting to see that SQL has all sorts of functionality. SQLNumResultCols, for instance, will return just what it says, the number of columns returned by your 'fetch'. I'll do this at the end too, but let me take the time to strongly encourage you to look at the SQL documentation that came with your compiler. It really is amazing what you can do and discover about your queries without having to churn through the results.
I'll let you look up SQLDescribeCol on your own. It's not relevant in this code, but it's interesting to see what it does. In a dynamic fetch where you're not sure which columns are coming back this function is absolutely necessary.
SQLNumResultCols(StatementHandle, &ColumnCountPtr); SQLDescribeCol((SQLHSTMT)StatementHandle, ColumnCountPtr, (SQLCHAR *)ColumnName, BufferLengthSm, &NameLengthPtr, &DataTypePtr, &ColumnSizePtr, &DecimalDigitsPtr, &NullablePtr); |
Ok, enough of the fun stuff, lets get back to our primary goal; getting those two columns from the Item Master. We submitted our query in the last function and now we're about to retrieve the results, but we have to do one thing first. We have to tell the API where to put the results. This is achieved by using SQLBindCol. Look closely and you can see that we are calling this function two times, once for each result column we asked for. If you were really fancy you could use the SQLNumResultsCols above and bind the columns in a loop (using the results from SQLDescribeCol of course).
Inspecting the parameters should be almost self describing. Give it the statement handle, the column number of the return set, the type of data, where to put it (wow, right into the return data structure!) and how big the data is (we don't use that last value, feel free to look up it's meaning). I'm sure there's no need to point out that OneWorld isn't really fun when it comes to 'dynamic', remember that you have a finite data structure to pass these results back through, that's why I'm focusing on the 'known result' track.
SQLBindCol(StatementHandle, 1, SQL_C_CHAR, lpDS->szIdentifier2ndItem, (ColumnSizePtr+1), &StrLen_or_Ind); SQLBindCol(StatementHandle, 2, SQL_C_CHAR, lpDS->szDescriptionLine1, (ColumnSizePtr+1), &StrLen_or_Ind); |
Now for the moment you've been waiting for. . . Fetch your data!
retcode =SQLFetch(StatementHandle); |
Is it really that easy!? It sure is, look in the field you sent into the SQLBindCol API. Your data is there. . .(well, if any was found). Amazing.
Now, this last part is to prevent memory leaks. Call SQLBindCol again with NULL in the 'target' field to release the bound column. Sure, this function binds and unbinds the result columns each time it's called, but you probably won't notice any performance hit.
To be perfectly honest, I'm not positive this step is necessary, but I put it in to be safe. Memory leaks are just that scary.
SQLBindCol(StatementHandle, 1, SQL_C_CHAR, NULL, (ColumnSizePtr+1), &StrLen_or_Ind); SQLBindCol(StatementHandle, 2, SQL_C_CHAR, NULL, (ColumnSizePtr+1), &StrLen_or_Ind); /************************************************************************ * Function Clean Up ************************************************************************/ return (ER_SUCCESS); } |
That's it, my friend. Call this function as many times as it takes, or until you run out of results. When you have what you want, call the last function to close your connections.
