Articles - ODBC
Introduction:
This is certainly my biggest deviation from OneWorld (while still being technically 'IN' OneWorld). That's because I replaced part of OneWorlds primary functions, the connection to the database. Of course, it isn't exactly an easy integration, so this solution will only be feasible when there's no other viable solution. Also, when this solution was first researched I didn't have access to an 'up-to-date' AS/400. I did get this code to run on the server and called from OneWorld, but the fetch results didn't do what the manual claimed. I ultimately concluded that without consistent results I was wasting my time. But, don't let this discourage you. This code runs and operates excellently when called from a client! If you've got an up to date AS/400 then it's actually very simple to change the code to run on the 400 itself. Just look at the example code in the red books to figure it out.
When would this be used?
OneWorld, still in it's youth, continues to be plagued by some limitations. Chief among these, in my opinion, is it's inability to deal with complex table joins, simple queries, and it's inability to perform multiple 'LIKE' selects against a single field.
I'm sure if you're contemplating this endeavor you've found out that joins across multiple tables, or even worse Outer Joins, often results in unexpected results. This technique will let you execute any valid SQL statement against your database without having to worry about, or battle against, Business Views, or tricking OneWorld into giving you what you want. If the SQL is good, this will give you results. . . every time. Oh, by the way, have you ever wanted to just get a 'count' of records with a certain attribute? Well, in SQL it's simple beyond reason, but in OneWorld you have to physically count each record and advance a counter! GGGAAAAHHHH!
The example below deals with my third 'issue' with OneWorld; multiple 'LIKE' statements. It's fairly easy to see what we're getting at, and everyone has asked how to do this. Consider the QBE line on any grid. In this case, think about searching the Item Master for an Item that includes the letters "TI" and "AA". Your first thought might be to type "*TI*AA*" into the QBE. Doing so will result in the middle asterisk being removed and OneWorld searching for "*TIAA*". Try as you might, you can't search with two values (two 'LIKE' statements).
This is what you're really trying to do with the tool:
select imlitm, imdsc1 from F4101 WHERE ( imlitm LIKE '%TI%' ) and imlitm LIKE '%AA%'
Well, it doesn't do it.
Without going overboard on why OneWorld can't, or doesn't do something we'll quickly jump into the code. . .
The Code
This is what you're about to see. I've created three business functions that are all contained in the same 'source code'. This last bit may be significant if you end up mapping functions in OCM to different locations. Keeping this stuff together will safe you grief, believe me.
So, we've got three functions. The first opens a connection to the database and submits our query. The second receives the results of the query. This one is separate in case your query returns multiple rows; call this function until you run out of returns. The third, and final, function closes the database connections you opened in the first business function.
Ready?! Here we go!
The Data Structure:
ID idStatementHandle; /* use GENLNG for these*/ ID idConnectionHandle; ID idEnvironmentHandle; |
PrepareTables opens your connection and submits the query. . . So, declare all the variables and datastructures you're going to use.
JDEBFRTN (ID) JDEBFWINAPI PrepareTables (LPBHVRCOM lpBhvrCom, lpVoid, LPDSD55DRA lpDS)
{
/************************************************************************
* Variable declarations
************************************************************************/
SQLHENV EnvironmentHandle = '\0';
SQLHSTMT StatementHandle = '\0';
SQLHDBC ConnectionHandle;
char ServerName[30];
char UserName[15];
char Authentication[25];
char StatementText[200];
char Connection[50];
char szColumnBuffer[50];
unsigned char Sqlstate[5];
unsigned char messageBuffer[100];
int iCounter;
SQLSMALLINT TextLengthPtr;
SQLRETURN retcode;
SQLINTEGER NativeErrorPtr;
ID idReturn = ER_SUCCESS;
HUSER hUser;
ID idJDEDBReturn = JDEDB_PASSED;
/************************************************************************
* Declare structures
************************************************************************/
DSD98700B dsD98700B = {{0}};
/************************************************************************
* Declare pointers
************************************************************************/
/************************************************************************
* Check for NULL pointers
************************************************************************/
/** common code removed to save space . . . . */ /** Now get a hUser with InitBhvr. . **/
/** Set error 078S if it fails **/
/************************************************************************
* Set pointers
************************************************************************/
/************************************************************************
* Main Processing
************************************************************************/
/* Clear out the variables */
memset(ServerName,'\0',sizeof(ServerName));
memset(UserName,'\0',sizeof(UserName));
memset(Authentication,'\0',sizeof(Authentication));
memset(StatementText,'\0',sizeof(StatementText));
memset(&dsD98700B, 0, sizeof(dsD98700B));
|
Maybe it's a little late to say this here, but this code is meant as a Primer for you. I've got a number of 'hard-coded' values put in here to illustrate the concept. If you require more dynamic functionality. . . .well, I can't guess what you need, so the bad news is that you'll have to burn the midnight oil.
Here's a good example of 'hard-coding'. I've put F4101 right into the code that returns OCM mappings. You may need to send a variable instead if you've devised some tricky 'dynamic' table determination.
/* Get the Object Librarian Data Source*/ strcpy((char *)dsD98700B.szTableName, (const char *)"F4101"); |
This function call tells us which ODBC Data Source to use. . . Note that this insures we are using the right OCM mapping!!
idReturn = jdeCallObject("GetValidDataSource", NULL,
lpBhvrCom,lpVoid,(LPVOID)&dsD98700B,
(CALLMAP *)NULL,(int)0,(char *)NULL,
(char *)NULL,(int)0);
if (idReturn != ER_SUCCESS)
return ER_ERROR;
strcpy((char *)ServerName,(const char *)dsD98700B.szDataSource);
/* Chop off the trailing blanks */
iCounter=sizeof(ServerName);
while(ServerName[iCounter--]<=' ')
ServerName[iCounter+1]='\0';
|
Get the users password from OneWorld with JDB_GetPassword . . . .NOTE THAT THIS WON'T WORK IF UNIFIED LOGON IS BEING USED . . . You may have to hard code one in.
sprintf(UserName,lpBhvrCom->SysPref.szUserName); sprintf(Authentication,JDB_GetPassword( lpBhvrCom->hEnv )); |
Now this is where I've defined a SQL statement. If you read it, you'll see that I'm getting the Second Item Number and the First Description field from the Item Master for all items that have TI and AA in the second Item Number. It's just like our example from the introduction. You, or course, will probably want to create some mechanism for dynamically generating a statement.
Now for a bit of techy work. . . Open the connections to your database: You need a Handle, and Environment, and a Connection.
strcpy(StatementText,
"select imlitm, imdsc1 from F4101 WHERE ( imlitm lIKE '%TI%' ) and imlitm like '%AA%'");
/* Now for the ODBC Code. . . .
Get a SQL Environment Handle. . . */
retcode =SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvironmentHandle);
/* Specify the ODBC Environment Attributes. . . */
retcode =SQLSetEnvAttr(EnvironmentHandle,SQL_ATTR_ODBC_VERSION, |
This is the part you've been waiting for. Submit your SQL!!
retcode =SQLExecDirect(StatementHandle,
(SQLCHAR *) StatementText,
(SQLINTEGER) sizeof(StatementText));
|
You should feel immensely proud of yourself. We've completed the first phase of our custom ODBC program: we connected to our data source, and we submitted the SQL statement. Of course, the next step is to retrieve the results, but we don't want to do that here because the results will most likely return more than one record. We really should return control to OneWorld, so it can call the 'retrieve' function as many times as necessary.
Indeed, that's what we want to do, but we can't just end the function and return, because we'll lose track of the connections we made. We must have that information when we call the 'retrieve' function and then when we close everything up though. But, how do you move memory pointers between Applications and Business Functions in OneWorld?
This is how we do it: jdeStoreDataPtr. Give it a pointer and it will return an integer. These integers will be passed between the application and the business functions to retrieve the true memory location. We'll see more of the mechanism for this on the next page.
lpDS->idStatementHandle = jdeStoreDataPtr(hUser, StatementHandle);
lpDS->idConnectionHandle = jdeStoreDataPtr(hUser, ConnectionHandle);
lpDS->idEnvironmentHandle = jdeStoreDataPtr(hUser, EnvironmentHandle);
/************************************************************************
* Function Clean Up
************************************************************************/
return (ER_SUCCESS);
}
|
