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)

Peoplecode OLE Automation

OLE (Object Linking and Embedding) enables you to exchange data into/from any application that has registered previously in your system. Frequently used to populate data into Word, Access, or Outlook. Here I’ll skip the background information about OLE, and go right into demonstrating how to export data in a Grid into an Excel spreadsheet. There are 4 peoplecode OLE functions we’ll use to implement this: CreateObject, ObjectDoMethod, ObjectGetProperty, ObjectSetProperty.
Consider the Budget Inquiry panel in PS Financials:

When user clicks the Excel button, the Grid data is transferred into the spreadsheet:

Peoplecode behind the Excel button:

     Local object &WORKAPP;
Declare Function SendClip PeopleCode D_WIN32_FUNCLIB.D_CLIP_IO FieldFormula;

&WORKAPP = CreateObject("Excel.Application");
ObjectSetProperty(&WORKAPP, "UserControl", "True");
&WORKBOOKS = ObjectGetProperty(&WORKAPP, "Workbooks");
&WORKBOOK = ObjectDoMethod(&WORKBOOKS, "Add");
&WORKSHEETS = ObjectGetProperty(&WORKBOOK, "Worksheets");
&WORKSHEET = ObjectGetProperty(&WORKSHEETS, "Item", 1);
ObjectSetProperty(&WORKSHEET, "Name", "MyOLEtest");
&RANGE = ObjectGetProperty(&WORKSHEET, "Range", "A1:E1");
&FONT = ObjectGetProperty(&RANGE, "Font");
ObjectSetProperty(&FONT, "Bold", "True");
&HEADING = "Select" | Char(9) | "Budget Period" | Char(9) |
"Begin Date" | Char(9) | "End Date" | Char(9) | "Description";&DTL_LINES = "";
For &IDX = 1 To ActiveRowCount(RECORD.CTL_CRIT_SEG) Step 1;
&SELECT = FetchValue(CTL_CRIT_SEG.SELECT_BDGT_PERIOD, &IDX);
&BP = FetchValue(CTL_CRIT_SEG.BUDGET_PERIOD, &IDX);
&BEG_DT = FetchValue(CTL_CRIT_SEG.BEGIN_DT, &IDX);
&END_DT = FetchValue(CTL_CRIT_SEG.END_DT, &IDX);
&DESCR = FetchValue(CTL_CRIT_SEG.DESCR, &IDX);
&LINE = &SELECT | Char(9) | &BP | Char(9) | &BEG_DT | Char(9) |
&END_DT | Char(9) | &DESCR;
&DTL_LINES = &DTL_LINES | &LINE | Char(10);
End-For;
&XX = SendClip(&HEADING);
ObjectDoMethod(&RANGE, "Select");
ObjectDoMethod(&WORKSHEET, "Paste");
&XX = SendClip(&DTL_LINES);
&CELL = "E" | &IDX;
&RANGE2 = ObjectGetProperty(&WORKSHEET, "Range", "A2:" | &CELL);
ObjectDoMethod(&RANGE2, "Select");
ObjectDoMethod(&WORKSHEET, "Paste");
&IDX = &IDX + 1;
&CELL = ObjectGetProperty(&WORKSHEET, "Range", "F" | &IDX);
ObjectDoMethod(&CELL, "Select");
ObjectSetProperty(&WORKAPP, "Visible", True);
ObjectDoMethod(&WORKBOOK, "SaveAs", "c:\temp\oletest.xls");

Code Dissection

Local object &WORKAPP;

**Must declare the Object before instantiating it properly

Declare Function djSendClip PeopleCode
D_WIN32_FUNCLIB.D_CLIP_IO FieldFormula;

**Use a clipboard function posted at this site earlier, this allows us to copy data into clipboard and paste to spreadsheet-same way we manually paste data. Not the only way to do it, but it simplifies the job. You can copy in the code here or in a FUNCLIB as above

&WORKAPP = CreateObject("Excel.Application");
::::::::::::::::
&WORKSHEETS = ObjectGetProperty(&WORKBOOK, "Worksheets");

**Instantiate anExcelspreadsheet,hierarchy:Application->Workbooks->Workbook->Worksheet, allow user to control it after we are done

&WORKSHEET = ObjectGetProperty(&WORKSHEETS, "Item", 1);
ObjectSetProperty(&WORKSHEET, "Name", "MyOLEtest");

**Name sheet #1 MyOLETest

&RANGE = ObjectGetProperty(&WORKSHEET, "Range", "A1:E1");
:::::::::::::::::::::
ObjectDoMethod(&WORKSHEET, "Paste");

**Set title text of the 5 columns in Bold

&DTL_LINES = "";
For &IDX = 1 To ActiveRowCount(RECORD.CTL_CRIT_SEG) Step 1;
:::::::::::::::::::::::
End-For;
&XX = SendClip(&DTL_LINES);

**Prepares the grid data & copy to clipboard

&CELL = "E" | &IDX;
&RANGE2 = ObjectGetProperty(&WORKSHEET, "Range", "A2:" | &CELL);
ObjectDoMethod(&RANGE2, "Select");
ObjectDoMethod(&WORKSHEET, "Paste");

**the data will occupy cells A2 (Row 1 is title) through column E and whatever number of rows as the value &IDX

&IDX = &IDX + 1;
&CELL = ObjectGetProperty(&WORKSHEET, "Range", "F" | &IDX);
ObjectDoMethod(&CELL, "Select");

**move cursor to a new cell

ObjectSetProperty(&WORKAPP, "Visible", True);
ObjectDoMethod(&WORKBOOK, "SaveAs", "c:\temp\oletest.xls");
**display the spreadsheet and save it

You can further define formula, subtotal, sort cells, or set up a template that includes these formulas, subtotals etc. When you populate the cells, this additional information will be readily present. This method is a simple and efficient alternative to nVision reports. With your knowledge about specific application, you can invoke different methods to achieve the same results as we did here.

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

Peoplecode FTP function

Many processes are executed on the server for performance reasons. Their output and log files, such as process logs and *.spf files from SQR, are usually handled in the following ways:

loaded into a table to be viewed in a on-line panel

saved to another location and picked up through 3-party reporting tools

moved to another server by using a server script

Here's a simple alternative to the above methods - FTP to client directly and use the rich set of tools on the client side to view or manipulate it.
The function accepts 5 parameters:

FTP site

Directory

File Name

Login ID

Password

The sample code retrieves a readme.txt from Microsoft's ftp site into the directory path pointed to by the environment variable TEMP. As is, it supports the Get operation in ASCII mode. Binary mode and other operations can be easily implemented. It’s doable to even build a simple FTP client into PS.

Copy the function declaration:

Declare Function InternetOpenA Library "wininet.dll" (string Value As string, long Value As number,
string Value As string, string Value As string, long Value As number) Returns long As number;
Declare Function InternetConnectA Library "wininet.dll" (long Value As number, string Value As
string, integer Value As number, string Value As string, string Value As string, long Value As
number, long Value As number, long Value As number) Returns long As number;
Declare Function FtpSetCurrentDirectoryA Library "wininet.dll" (long Value As number, string Value
As string) Returns long As number;
Declare Function FtpGetFileA Library "wininet.dll" (long Value As number, string Value As string,
string Value As string, long Value As number, long Value As number, long Value As number, long Value
As number) Returns boolean;
Declare Function InternetCloseHandle Library "wininet.dll" (long Value As number) Returns integer
As number;Function FTPGet(&HOST As string, &DIR As string, &FILE As string, &ACCT As string, &PW As string)
Returns boolean;
&OPEN_PRECONFIG = 0;
&FTP_PORT = 21;
&INET_FTP = 1;
&INET_PASSIVE = 134217728;
&FTP_ASCII = 1;
&HOPEN = InternetOpenA("Peoplecode FTP", &OPEN_PRECONFIG, "", "", 0);
If &HOPEN > 0 Then
  &HCONN = InternetConnectA(&HOPEN, &HOST, &FTP_PORT, &ACCT, &PW, &INET_FTP,
  &INET_PASSIVE, 0);
  If &HCONN > 0 Then
     If All(&DIR) Then
        &HDIR = FtpSetCurrentDirectoryA(&HCONN, &DIR);
     Else
        &HDIR = 1;
     End-If;
     If &HDIR Then
        &HFILE = FtpGetFileA(&HCONN, &FILE, GetEnv("TEMP") | "\" | &FILE, 0, 0, &FTP_ASCII, 0);
        If &HFILE Then
           &RC = InternetCloseHandle(&HCONN);
           &RC = InternetCloseHandle(&HOPEN);
           Return True;
        End-If;
     End-If;
     &RC = InternetCloseHandle(&HCONN);
  End-If;
  &RC = InternetCloseHandle(&HOPEN);
End-If;
Return False;
End-Function;

Copy the sample test to a FieldChange event run on the Client:

&XX = FTPGet("ftp.microsoft.com", "softlib", "readme.txt","anonymous","");
If &XX Then
WinExec("start " | GetEnv("TEMP") | "\"|"readme.txt", 1, False);
Else
WinMessage("fail");
End-If;

For NT client, replace "start" with "cmd /c start" so the line looks like:

WinExec("cmd /c start " | GetEnv("TEMP") | "\"|"readme.txt", 1, False);

Account ID & Password considerations - The demonstration uses a simple "anonymous" and null as password. Additional code can be added to prompt the user. They can also be defined based on operator ID/Classes. Permission for this account should then be set up accordingly for the FTP purpose only. The retrieval of ID & password can be encapsulated in the FTP function as well. The function then requires 3 parameters instead of 5.

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

Peoplecode 3-tier Detection

The Status Bar is a control that is at the bottom of a panel that displays information about the application status. It is divided into individual panes. Each pane displays specific aspects of the process/function the user is in. 5 panes are present in v.7 and 6 panes in v.8. This article will demonstrate a platform independent method to detect 3-tier connection by accessing the information stored on status bar, provided the database sever and application server have different names.
Take a look at a sample status bar:

The 1st pane does not have a border, and is used for panel loading. We are interested in the 3rd pane. It displays the server name, GF75DMO. In PS8, there’s another pane for Time, but server name is still on 3rd pane.
If server name is not displayed, turn it on in Configuration Manager Display tab:

Copy the function declaration:

Declare Function GetActiveWindow Library "user32"
() Returns long As number;
Declare Function GetWindow Library "user32"
(long Value As number, long Value As number) Returns long As number;
Declare Function GetClassNameA Library "user32"
(long Value As number, string Ref As string, integer Value As number) Returns long As number;
Declare Function SendMsg2 Library "user32" Alias "SendMessageA"
(long Value As number, integer Value As number, integer Value As number, string Ref As string)
Returns long As number;Function GetSB(&PANETEXT As string) Returns boolean;
&STATBAR = "msctls_statusbar32";
&HWND = GetActiveWindow();
&GW_CHILD = 5;
&GW_HWNDNEXT = 2;

&MAXLEN = 128;   &CLSNAME = Rept(" ", &MAXLEN);
&CWND = GetWindow(&HWND, &GW_CHILD);
While &CWND > 0
&CLSLEN = GetClassNameA(&CWND, &CLSNAME, &MAXLEN);
If &CLSNAME = &STATBAR Then
  Break;
End-If;
&CWND = GetWindow(&CWND, &GW_HWNDNEXT);
End-While;
If &CWND > 0 Then
&NPARTS = 0;
&CORD = 0;
&SB_GETTEXTA = 1026;
&RC = SendMsg2(&CWND, &SB_GETTEXTA, 2, &PANETEXT);
If &RC > 0 Then
  Return True;
End-If;
End-If;
Return False;
End-Function;

Test the function in a FieldChange event:

&XX = GetSB(&PANETEXT);
If &XX Then
If &PANETEXT = "MyAppSvr" Then
WinMessage("3-tier");
Else
WinMessage("2-tier");
End-If;
Else
WinMessage("fail");
End-If;

The server name is returned in &PANETEXT. Compare it to a list of known App Server names. If a match is found, then the user has logged into the App server.
As with all external Windows function APIs, if your app server is not NT, set the execution location of this code to client:

You can also read or write into this or other panes on the status bar. But there are some considerations when writing text to it. Please email the author if you’d like to see how to do it.

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

Peoplecode Windows Registry Access

Windows Registry stores various settings about the Windows system and other applications. To be able to access these information provides greater capability of your Peoplecode applications. For example, only a small number of Peoplesoft’s own settings (through Configuration Manager) are available as system variables. But they are all accessible in registry. The following are typical registry settings for 7.5 PSTools:

This Peoplecode function ReadReg() will let you glance at all the ASCII registry data of installed software under HKEY_CURRENT_USER, which contains the sub-key Software that describes the current user's software settings and contains program-specific information previously stored in the Win.ini or private initialization files in Windows 3.x.
To use the function, set &SUBKEY to the path in registry, as shown on the status bar above, and set &VALNAME to the Value Name in question. The Value Data will be returned in &KEY_VAL. For example, to read the value of PS_HOME, use subkey “Software\PeopleSoft\PeopleTools\Release7.5\Process Scheduler” and Value Name “PS_HOME”. Be sure to include spaces where needed. If the data string is longer than 128 bytes, you need to increase the value of &KEY_LEN.

Copy the function declaration:

Local number &H_REG_KEY;
Declare Function RegOpenKeyExA Library "advapi32" (long Value As number,
string Value As string, long Value As number, long Value As number,
long Ref As number) Returns long As number;
Declare Function RegQueryValueExA Library "advapi32" (long Value As
number, string Value As string, long Value As number, long Ref As
number, string Ref As string, long Ref As number)
Returns long As number;
Declare Function RegCloseKey Library "advapi32" (long Value As
number) Returns long As number;

Function ReadReg(&SUBKEY As string, &VALNAME As string,
&KEY_VAL As string) Returns boolean;
&HKEY_CURRENT_USER = - 2147483647;
&KEY_QUERY_VALUE = 1;

&RC = RegOpenKeyExA(&HKEY_CURRENT_USER, &SUBKEY, 0,
&KEY_QUERY_VALUE, &H_REG_KEY);
If &RC <>  0 Then
MessageBox(48, "ReadReg", 0, 0, "Cannot open Registry");
Return False;
End-If;
/*key value type is null-terminated string */
&REG_SZ = 1;
/* initialize string to 128 bytes */
&KEY_LEN = 129;
&KEY_VAL = Rept(" ", &KEY_LEN);
&RC = RegQueryValueExA(&H_REG_KEY, &VALNAME, 0, &REG_SZ,
&KEY_VAL, &KEY_LEN);
If &RC <>  0 Then
MessageBox(48, "ReadReg", 0, 0, "Cannot query Registry");
Return False;
End-If;
&RC = RegCloseKey(&H_REG_KEY);
Return True;
End-Function;
Copy the following function call test:&SUBKEY = "Software\PeopleSoft\PeopleTools\release7.5\startup";
&VALNAME = "dbtype";
&KEY_VAL = "";
&XX = ReadReg(&SUBKEY, &VALNAME, &KEY_VAL);

If &XX Then
MessageBox(48, "ReadReg", 0, 0, &SUBKEY | "\" | &VALNAME | "=" | &KEY_VAL | ".");
Else
WinMessage("Fail");
End-If;

(This article was originally published on 1/11/02 at www.slerp.com for PSTools version 6,7.)