Peoplecode ODBC Function

Open Database Connectivity (ODBC) allows an application to access data stored in variety of DBMSs. Commonly within an organization departmental data may be kept in their own specific repository. For example, you may need to look up a county code that's in DB2, a Commodity Code in SQL Server, or even your HR data in the same DBMS as your Peoplesoft on another server. Will you try to bring them into PS through a bunch of download and upload processes? What about keeping them in sync? This article will demonstrate a simple alternative by using ODBC interface APIs to achieve the goal.

To use ODBC in peoplecode, ODBC needs to be installed and configured properly for the target DBMS. Go to Control Panel and double click the ODBC Administrator. You should see something like this:

The Drivers tab lists all the drivers available for various DBMS. You also need to set up at least one DSN for the target DBMS. Here's a quick rundown on how the interface works:

1. Application (peoplecode) calls ODBC manager(odbc32.dll) with a series of requests
2. ODBC manager relays the requests to appropriate drivers as defined in DSN
3. platform specific ODBC driver performs DB query/update and send results back

Here's an example using the above information to read a Ship Order name from a push button on a panel:

When user clicks the Access button, the following data is read from Access into PS and displayed by Winmessage().

Let's now create a simple peoplecode function that will do the above, reading Access DB and retrieving a string, number and date field. The code uses v3 of 32-bit of ODBC driver. Create a User DSN as follows for the sample Northwind database included in Access:

(You can also set up System DSN which is available to all uses on client pc, add default UID and password. If you leave them blank, a prompt will be displayed)

Copy the following code into a push button FieldChange event:

Download Code Sample 1.

This section of code opens an ODBC connection with DSN we just set up in Read-Only mode. It then selects 3 fields from orders table and displays their values in a message box. The code demonstrates steps in establishing various connection handles, executing SQL statements, retrieving result columns, and disconnection steps.

This code example barely skims the surface of ODBC programming. It's wide range of function calls enables you to code what's best suited for your needs. For example, if a SQL is executed only once, you may use SQLExecuteDirect(). But you may use SQLPrepare() and SQLExecute() if the SQL will be run multiple times. The same applies to retrieval of data. It's a subject worth deeper exploration. Here's some points to consider:

The best place to use this code is in a lookup function. A push button brings in the data from a non-PS DBMS based on a PS field value. PS discourages the use of external function calls from inside a scroll and in all Save events, for obvious integrity and performance reasons, although I have gone as far as inserting rows of MS Access data into PS in a loop (by opening a 2nd connection into PS, thus bypassing all peoplecode Save restrictions). It may work for you if you examine your data thoroughly and explore appropriate ODBC functions.

Make sure your DSN is set up correctly before using it in peoplecode. I included a simple utility to help test your DSN.

If the DSN cannot connect, it will not work in peoplecode function.

Execute you SQL statements in native DBMS before using them in peoplecode. Each DBMS may use a specific syntax. For example, I have to use ‘#' to denote a date field in Access:

select orders.orderid from orders where orders.orderdate = #04/24/2002#

If the SQL does not work in it's native DBMS, it will not work in peoplecode function.

I find it easier to read all data in as Char, and then convert them into other data types rather than reading them in as the original data type.

The state of a table can effect the result of a query. For example, when the table is in the process of being altered or modified, or an Access table is opened by someone in Design mode, you may not get the correct results back.

After each function call, we are supposed to check the return code (&RC). If non-zero, it could indicate an error (SQL_ERROR), or success with warning (SQL_SUCCESS_WITH_INFO). Generally I found this to be true, but at times it's not consistent. Not sure why, it might have something to do with the call translation. As you can see, the check is omitted in the sample ( just rely on the result column values to be present.) You can certainly add them to the function.

A good practice is to set up User DSN with correct login information. Otherwise, a prompt will be displayed for user to login. This would require users to have knowledge about UID and password. Each ODBC driver has it's own parameters. The following is an ODBC setup for a secured Access database:

An alternative is to embed this info in your peoplecode SQLConnect string. But this also exposes the login information. The balance between maintenance and security are to be determined at your organization.

A good source of information is Microsoft's own ODBC page:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/dasdkodbcoverview.asp

(this article was originally published on 05/02/02 at www.slerp.com. PSTools version 5,6,7)