The driver supports User Defined Functions (UDF) and Stored Procedures (SP). UDF and SP are subroutines made up of jBC code, having input and output parameters, and are used in combination with SELECT and EXECUTE statements. An UDF may return valid values for one or more fields (output parameters). A SP may or may not return a record set.

Creating an UDF or SP includes two steps:

  • Declaring the item in Administrator tool
  • Writing and compiling/cataloging jBC code in jBASE environment

Please note: Due to caching mechanism implemented in the application server, it is required to close active sessions whenever you recompile an UDF or stored procedure. This can be accomplished from Administrator tool, [Close active sessions] button.

 

User Defined Functions

 

An UDF can offer great convenience in speed and flexibility when there is a requirement to extract and transform jBASE data. UDFs can be used in select_expression part of the SELECT statement.

Open Administrator tool, connect to an alias and go to to [UDF/Stored Procedures] tab:

On the left panel, select the group of items you would like to use (UDF or SP), and open a popup menu with mouse right click and choose [New Function...]. In an opened dialog input UDF "nickname" and hit OK. Then, fill in the field [Subroutine] the actual name of a jBASE subroutine.

Add necessary output field definitions with [+] and [-] buttons. There you can specify parameter name, value type, optional format, field and parameter type:

 

Parameter Name Specify here a relevant parameter name. The name is supplied to ODBC driver manager and is used by client module to locate the parameter and use it to pass and retrieve values.
Value Type Choose one of the supported value types from the drop down box. Currently INTEGER, CURRENCY, FLOAT, DATETIME and CHAR types are supported. Selected type should match with the actual data type accepted or sent by jBASE subroutine.
Format

An optional field used together with DATETIME and CHAR data types.

DATETIME - mask that would properly map date and time value
CHAR - size of the field in characters
S/M Value chosen here from a drop down box will identify the data as being single or multivalued.
Parameter Type One of the following data types types should be assigned to each UDF parameter:
INPUT The parameter will accept default values that will be passed to jBASE subroutine. Must be declared as parameter in subroutine's header.
RTNVALUE Output value of an UDF. Please note, UDF can supply more than one resulting value to the recordset (ex. AMT.DB.CR function). In this case, the number of fields in SQL recordset will increase by the number of RTNVALUE parameters. Must NOT be declared as parameter in subroutine's header.

 

The following example is included in the installation package. UDF function declaration AMT.DB.CR has assigned actual jBASE subroutine VT.UDF.AMT.DB.CR.

001     SUBROUTINE VT.UDF.AMT.DB.CR(AMOUNT)
002
003     $INSERT I_VT.COMMON
004
005     IF AMOUNT < 0 THEN
006         VT.FIELD.VALUE<1> = AMOUNT
007         VT.FIELD.VALUE<2> = ""
008     END ELSE
009         VT.FIELD.VALUE<1> = ""
010         VT.FIELD.VALUE<2> = AMOUNT
011     END
012
013     RETURN
014 END

 

In jBASE environment all UDF subroutines must have I_VT.COMMON include file added. The VT.FIELD.VALUE variable should be used in order to return results back to the driver.

The above code belongs to AMT.DB.CR function that accepts one input parameter and returns 2 columns declared as RtnValue in Administrator tool. There is no restriction on the number of Input or RtnValue parameters. Function result must be written in VT.FIELD.VALUE variable which is a dynamic array for multiple RtnValue columns.

 

Stored Procedures

 

In special cases, SELECT functionality based on tables may not cover users' requirements, for example collecting and merging the data from several files. In such cases, it is advisable to use stored procedures.

In our implementation, a stored procedure is a jBC subroutine which can produce and pass to the driver a recordset or individual values through output parameters. A stored procedure (SP) can be defined in Vultar Administrator tool in the same way as an UDF. However, there are some differences between them. Most notable difference appears on how parameter types are defined and how values are assigned. While UDF supports 2 parameter types (Input and RtnValue), SP accepts 4 parameter types as described below.

 

Parameter Name Specify here a relevant parameter name. The name is supplied to ODBC driver manager and is used by client module to locate the parameter and use it to pass and retrieve values.
Value Type Choose one of the supported value types from the drop down box. Currently INTEGER, CURRENCY, FLOAT, DATETIME and CHAR types are supported. Selected type should match with the actual data type accepted or sent by jBASE subroutine.
Format

An optional field used together with DATETIME and CHAR data types.

DATETIME - mask that would properly map date and time value
CHAR - size of the field in characters
S/M Value chosen here from a drop down box will identify the data as being single or multivalued.
Parameter Type One of the following data types types should be assigned to each SP parameter:
INPUT Parameter will accept default values that will be passed to jBASE subroutine. Must be declared as parameter in subroutine's header.
IN/OUT Parameter behaves as both, INPUT and OUTPUT parameter. While it may accept default input values, it will also return result values to driver. Must be declared as parameter in subroutine's header.
OUTPUT Parameter can be used only for returning result values to driver. Must be declared as parameter in subroutine's header.
RTNVALUE Field value of the resulted recordset. Values for this parameter type can be assigned only with VT.API.WRITE.RECORD function. See NOFILE.AC.S stored procedure example from the installation package. Must NOT be declared as parameter in subroutine's header.

 

Stored procedures are used in dataset_reference part of the SELECT and EXECUTE statements.

Important: Any jQL SELECT query within stored procedure must be executed with VT.API.EXECUTE.SELECT call. It has one input parameter (jQL SELECT statement), and one output parameter (dynamic array containing list of IDs).

Stored procedures can be of two types, single or multithreaded. There is a checkbox [Multithreaded] on the right side of the tab that has to be set accordingly.

The difference between single and multi threaded SP is in number of threads (jBASE sessions) allocated for data processing and transportation to the driver manager. Whether a large amount of resulting data is expected, it is advisable to prepare a multithreaded SP. Although more complicated from developing point of view, multithreaded SP will greatly boost the performance. The jBASE subroutine of a multithreaded SP must be written in a special way which is different than a single threaded SP.

Find below the specification of the both formats.

 

Single-threaded stored procedures

 

This is the simplest way to write a stored procedure. When a task does not require to process large volumes of data, single-threaded format is the preferred one. 

In the following example, a stored procedure named NOFILE.AC.S is declared in Administrator tool with one Input and three RtnValue parameters. It means the procedure will return a resultset with three columns.

It has attached VT.SP.NOFILE.AC.S subroutine compiled in jBASE environment:

  SUBROUTINE VT.SP.NOFILE.AC.S(TOP.COUNT)

  $INSERT             I_VT.COMMON
  $INCLUDE GLOBUS.BP  I_F.ACCOUNT
  $INCLUDE GLOBUS.BP  I_F.CUSTOMER

  FN.ACCOUNT = "F.ACCOUNT"; F.ACCOUNT = ""
  CALL OPF(FN.ACCOUNT, F.ACCOUNT)

  SELECT F.ACCOUNT

  REC.NO = 0
  LOOP
      READNEXT ID.ACC ELSE BREAK

      OUT.RECORD = ""
      READ R.ACCOUNT FROM F.ACCOUNT, ID.ACC THEN
          OUT.RECORD<-1> = ID.ACC
          OUT.RECORD<-1> = R.ACCOUNT<AC.CURRENCY>
          OUT.RECORD<-1> = R.ACCOUNT<AC.CUSTOMER>
          CALL VT.API.WRITE.RECORD(OUT.RECORD)
      END

      IF ++REC.NO >= TOP.COUNT THEN
          BREAK
      END

  REPEAT
  RETURN
END

 

As we can see, it opens the F.ACCOUNT file, reads first TOP.COUNT records, prepares a record with three columns (local dynamic array OUT.RECORD), and send it to the driver manager for further processing. Subroutine's key element represents an API call to VT.API.WRITE.RECORD. It is being used to pass RtnValue parameters (record by record) to driver manager. As input parameter, VT.API.WRITE.RECORD receives OUT.RECORD array that contains data for RtnValue params. The API call will append a new record to the dataset. 

 

Single-threaded stored procedures

 

Multi-threaded format of the SP is more complicate, but brings greater performance benefits. It has to be declared in the same way as single-threaded procedures, in addition, "multi-threaded" checkbox enabled. The subroutine is split into four stages: 

LOAD initializing variables, opening files etc...
SELECT retrieving list of IDs (by executing jQL SELECT or gathering from other sources) into common output variable VT.ID.LIST
PROCESS each thread receives elements one by one from VT.ID.LIST array (in VT.ID variable); reading and processing the data, writing the output record to the driver manager. This stage is triggered by the driver until all elements from VT.ID.LIST array will be processed.
DONE finalization activities, closing files etc...

 

SELECT stage is executing once in the main thread, PROCESS stage is triggered in the main thread as well, but repeatedly for each record ID, LOAD and DONE are triggered once in each thread. The number of threads has to be specified in Administrator tool [Max. number of SPU per request]. As in the single threaded format, the data is being written to driver manager with VT.API.WRITE.RECORD call.