Using Interoperability Features

Applications can run SQR programs by using the SQR application program interface (API). An SQR program can also call an external application's API.

This chapter discusses how to:

• Call SQR from another application.

• Invoke an SQR program by using the SQR API.

• Invoke an external application API by using the ufunc.c interface.

• Add a user function.

• Use UFUNC in Microsoft Windows.

• Implement new user functions in Microsoft Windows.

Calling SQR from Another Application

To invoke an SQR program from another application, use:

• The SQR command line

  The application initiates a process for running SQR. The SQR command includes all of the necessary parameters.

• The SQR API

  The application makes a call to the SQR API. This method is covered in the next section.

See Using the SQR Command Line.

Invoking an SQR Program by Using the SQR API

The SQR API is provided in Microsoft Windows through a Dynamic Link Library (dll). You can use the SQR API from any application that is capable of calling dll functions. For C and C++ applications, a header file (sqrapi.h) and an import library (sqrwin.lib) are provided. SQR requires the following .dll files to run for Microsoft Windows: sqrw.dll, bclw32.dll, libsti32.dll, and stimages.dll . These dll files are located in the BINW directory.

On platforms other than Microsoft Windows, the SQR API is provided as a static library (sqr.a or sqr.lib) . For C and C++ applications, a header file (SQRAPI.H or sqrapi.h) is provided. Be sure to include the SQR API library and your database library when you link your C or C++ application. Two additional libraries are required: bcl.a and libsti.a.

The following table describes the API functions that are defined for calling SQR:

For the benefit of C and C++ programmers, the APIs are declared in the sqrapi.h file. Include this header file in your source code:

#include 'sqrapi.h'

When you call SQR from a program, the most recently run SQR program is saved in memory. If the same SQR program is run again with either the same or different arguments, the program is not scanned again and the SQL statements are not parsed again. This feature provides a significant improvement in processing time.

To force SQR to release its memory and database cursors, call sqrend() at any time.

Although memory is automatically released when the program exits, you must call sqrend before the calling program quits to ensure that SQR properly cleans up any database resources such as database cursors and temporary stored procedures.

To relink SQR on all UNIX/Linux platforms, use the sqrmake and makefile files that are located in $SQRDIR/../lib. After you invoke sqrmake and optionally select the specific database version to link with, the SQR executables are re-created.

Check which cc command line gets created and invoked for SQR, and adapt it to your program. Each UNIX/Linux platform and database has its own requirements. Consult your operating system and database product documentation for specific information.

You may see the following output when you relink with Sybase CT-LIB under Sun/Solaris:

cc -o {user program} {user objects} {user libraries} \
$SQRDIR/../lib/sqr.a $SQRDIR/../lib/bcl.a \
$SQRDIR/../lib/pdf.a $SQRDIR/../lib/libsti.a \
-L$ (SYBASE) /lib -Bstatic -lct -lcs -ltcl -lcomm \
-lintl -Bdynamic -lm -lnsl -ldl

Check the make files or link scripts that are supplied with SQR for details. You may want to copy and modify those to link in your program.

To call SQR, call sqr() and pass a command line. For example, in C:

status = sqr("myprog sammy/baker arg1 arg2 arg3");
if (status != 0)
      ...error occurred...

The following table describes error values that are returned by SQR, both standalone and callable:

The error codes 9 and 12 are applicable to the Microsoft Windows release only.

For more information about linking with SQR, see your installation guide.

See PeopleTools 8.50 Installation Guide for your database platform.

Invoking an External Application API by Using the UFUNC.C Interface

The SQR language can be extended by adding user functions that are written in standard languages, such as C. This feature enables you to integrate your own code and third-party libraries into SQR. For example, suppose that you had a library for communication over a serial line, with functions for initiating the connection and sending and receiving data. SQR enables you to call these functions from SQR programs.

To extend SQR in this way, you must prepare the functions, specify them to SQR, and then link the objects (and libraries) with the SQR objects and libraries to form a new SQR executable. The new SQR executable then recognizes the new functions as if they were standard SQR functions.

One example of such an extension would be an initcap function. Oracle users are familiar with this function. The initcap function changes the first letter of every word to uppercase and changes the rest of the letters to lowercase. The result value in the following code example would be Mr. Joseph Jefferson:

let $a = initcap('MR. JOSEPH JEFFERSON')

Adding a User Function

This section provides an overview of the ufunc.c file and discusses how to:

• Add a function prototype.

• Add an entry to the USERFUNCS table.

• Add an implementation code.

• Relink SQR.

Understanding the UFUNC.C File

The code examples in the following sections demonstrate how to extend SQR with an initcap function.

The key to this process is an SQR source file called ufunc.c. This file contains the list of user-defined functions. It also contains comments with a description of the process of adding a function to SQR. Ufunc.c is in the lib subdirectory (LIBW in Microsoft Windows).

To add initcap to SQR, you must add it to a global array called userfuncs in ufunc.c.

Adding a Function Prototype

Begin by adding a function prototype to the function declaration list:

static void max CC_ARGS((int, double *[], double *));
static void split CC_ARGS((int, char *[], double *));
static void printarray CC_ARGS((int, char*[], double *));
​static void initcap CC_ARGS((int, char *[], char *, int));

The preceding code segment is taken from the file ufunc.c. The first three lines are part of the original ufunc.c. The line that adds the initcap function is shown like this. The modified version of ufunc.c is in the LIBW (Microsoft Windows) or LIB (UNIX) directory under <PS_HOME>\bin\sqr\<database_platform>.

This code defines a prototype for a C function called initcap. The prototype is required by the C compiler. The name of the C function does not have to be the same as the name of the SQR function. The SQR name for the function is defined in the next step.

The CC_ARGS macro makes the code portable between compilers that expect full prototyping and compilers in which the argument prototype is omitted. You could also write:

static void initcap();

Note also that the STATIC keyword means that the code for initcap will be added in the file ufunc.c. If you have the code in a separate file, remove the STATIC keyword.

The first argument of the C function is the argument count of the corresponding SQR function. In the case of initcap, this argument count should be 1 because initcap takes exactly one argument.

The second argument of the C function is an array of pointers. This array is the argument list. In this case, because initcap takes only one argument, only the first pointer is actually used.

The third argument of the C function is a pointer to the result buffer. Because initcap returns a string, it is defined as char*.

The last argument sets the maximum length of the result string. The length of this string is the size of the result buffer, which you must not overflow. You cannot return a value that is longer than the maximum length. The maximum length is typically around 2000 bytes, depending on the platform.

Adding an Entry to the USERFUNCS Table

The next step is to define the initcap function to SQR. As stated before, this table exists in the ufunc.c file. Here is the modified code:

} userfuncs[] =
 {
 /* (2) Define functions in userfuncs table:
                           Number of 
 Name          Return_type Arguments Arg_Types Function
 ----          ----------- --------- --------- -------- */
 "max",        'n',        0,        "n",      PVR max,
 "split",      'n',        0,        "C",      PVR split,
 "printarray", 'n',        4,        "cnnc",   PVR printarray,
 ​"initcap",    'c',        1,        "c",      PVR initcap,
​ /* Last entry must be NULL do not change */
 "", '\0', 0, "", 0
};

The userfuncs table is an array of structures. The added line is shown like this, and it initializes one structure in the array. The line contains five arguments, which correspond to the five fields of the structure.

The first argument is the name of the SQR function that is being added. This is the name that you will use in the LET, IF, and WHILE commands. The second argument is the return type, which 'c' (enclosed in single quotation marks) indicates is a character string. The third argument is the number of arguments that initcap will take. Set it to 1.

The fourth argument is a string representing the types of the arguments. Because initcap has only one argument, the string contains one character enclosed in double quotation marks, "c". This character indicates that the argument for initcap is a string. The last argument is a pointer to a C function that implements the SQR function that you are adding. This argument is the initcap function for which we have provided a prototype in the previous step. Note that the PVR macro provides proper cast for the pointer.

Adding an Implementation Code

The next step is to add the implementation code for initcap. You can insert it into the file ufunc.c.

Note. To put the code in a separate file, you must remove the STATIC keyword from the prototype. You may also need to include standard C header files, such as CTYPE.H.

Here is the code that is inserted at the end of ufunc.c:

static void initcap CC_ARGL((argc,argv,result,maxlen))
CC_ARG(int, argc)       /* Number of actual arguments */
CC_ARG(char*, argv[])   /* Pointers to arguments: */
CC_ARG(char*, result)   /* Where to store result */
CC_LARG(int, maxlen)    /* Result's maximum length */
{
  int flag = 1;
  char *ptr;
  char *p;
  ptr = argv[0];
  p = result;
  while (*ptr) {
    if (ptr - argv[0] >= maxlen) break; /* don't exceed maxlen */
    if (isalnum(*ptr)) {
       if (flag) *p = islower(*ptr)?toupper(*ptr):*ptr;
       else *p = isupper(*ptr)?tolower(*ptr):*ptr;
       flag = 0;
    } else {
       flag = 1;
       *p = *ptr;
    }
     p++; ptr++;
  }
  *p = '\0';
  return;
}

Note the use of the CC_ARGL, CC_ARG, and CC_LARG macros. You can also write the code as follows (only the first five lines are shown):

static void initcap(argc,argv,result,maxlen)
int argc;       /* Number of actual arguments */
char* argv[];   /* Pointers to arguments: */
char* result;   /* Where to store result */
int maxlen;     /* Result's maximum length */

Relinking SQR

After you have modified ufunc.c, you must relink SQR. Use the make file that is provided in the LIB (or LIBW) subdirectory of SQR. This step is very specific to the operating system and database. SQR is linked with the database libraries, whose names and locations may vary. You may have to modify the make file for your system.

See PeopleTools 8.50 Installation Guide for your database platform.

After SQR is relinked, you are ready to test. Try the following program:

begin-program
  let $a = initcap('MR. JOSEPH JEFFERSON')
  print $a ()
end-program

The result in the output file should be:

Mr. Joseph Jefferson

See the ufunc.c file for further information about argument types in user-defined functions.

Using UFUNC in Microsoft Windows

In Microsoft Windows, ufunc resides in sqrext.dll. You can rebuild sqrext.dll by using any language or tool, as long as the appropriate calling protocol is maintained. The source code for sqrext.dll is included in the shipped package (extufunc.c).

When sqrw.dll and sqrwt.dll are loaded, they look for sqrext.dll in the same directory and for any .dlls that are specified in the SQR Extension section in pssqr.ini. If sqrw.dll and sqrwt.dll find sqrext.dll and the .dlls that are specified in the pssqr.ini file, they make the following calls in all of the .dlls, passing the instance handle (of the calling module) and three function pointers:

void InitSQRExtension (
   HINSTANCE hInstance,
   FARPROC lpfnUFuncRegister, 
   FARPROC lpfnConsole, 
   FARPROC lpfnError
   );

Implementing New User Functions in Microsoft Windows

You can implement new user functions in sqrext.dll or any other extension .dll. All of the extension .dlls must have the InitSQRExtension() function exported. If you implement user functions in sqrext.dll, you should rebuild the .dll by using the supplied make file, sqrext.mak. If new extension .dlls containing new user functions are to be used, they must be listed in the SQR Extension section in pssqr.ini in the system directory.

For any ufunc, you must register it by making the following call in InitSQRExtension():

lpfnUFuncRegister(struct ​ufnns* ​ufunc);

The function pointer, lpfnUFuncRegister, is passed in from the calling module. Refer to extufunc.c for the definition of struct ufnns and the sample user functions.