Pages  [ 1  2  3  4 ]

An Introduction to mvServer 2.0 (continued):

Stored Procedures

dbObjects and mvServer allows the user to execute Stored Procedures, which are Pick/Basic program subroutines.

From within any Client program, you may initiate a stored procedure on the server by using the ExecuteProc method of the dbObject’s Database object. This object and its associated method are served by the generic Database Server, VPDb20.dll. Within the method you specify the procedure name to execute and may pass arguments to that procedure.

In addition to the ExecuteProc method of the Database object there are a number of methods associated with the MultiValue Database object (VPMdb20.dll). There is a multipurpose Execute method and a number of special purpose methods. These special purpose methods are supported by a set of pre-built stored procedures that are part of the mvServer Server software. These special purpose methods include the following:

mvBeginServer - This method begins a Transaction context for any future updates to the database through the mvWrite or mvUpdate methods.

mvCapture - This method will execute an Access/Recall List or Sort command sentence and capture the result.

mvClearFile – This method clears the contents of a given data file.

mvCommitTrans - This method commits a Transaction context associated with any updates to the database through the mvWrite or mvDelete methods. It transfers updates from the VSSTAGE file to the actual database files.

mvDate - This method returns the current Date in Pick internal format.

mvDelete - This method will delete an Item (record).

mvExecuteTcl - This method will execute a TCL command sentence and optionally returns any captured data from the Execute.

mvIConv - This method executes an ICONV conversion on any data passed to it.

mvOconv - This method executes an OCONV conversion on any data passed to it.

mvRead - This method will read either an Item (Record) or an attribute (Field) from an Item.

mvReadNext - This method will read the next item from the last created select list.

mvRelease - This method will execute a lock release, which can be either a release for a given item, all items for a given file, or a general release of all locks.

mvRollbackTrans - This method rolls back a Transaction context associated with any updates to the database through the mvWrite or mvDelete methods. It removes updates from the VSSTAGE file without update to database files.

mvSelect - This method will Select all the records from a given file and make them available as a server-side select list.

mvTime - This method returns the current Time in Pick internal format.

mvWrite - This method will write either an Item (record) or an attribute (field) to an Item.  

mvWriteItem - This method will write a full, complete Item (record) to a file (table).

Below is a simple example of a stored procedure (this example is from a mvBASE system and the source code may need to be changed for UniVerse or other platforms). What this code does is accept an argument that contains a particular Member record ID and generates a list of all donations associated with the given member. To generate this report the program saves and changes the term settings, executes a sort against the DONATIONS file, captures the output of the sort, strips the captured data to be data only and converts it to a dynamic array, and finally resets the term settings.

EXAMPLE: Member Donation Report.

SP.MEMBER.DONATION.REPORT

001 SUBROUTINE SP.DONATION.REPORT(DBID, ARGS, RTNCD, RESULT)

002 **********************************************************************

003 *

004 * Vantage Server Stored Procedure - DONATION REPORT for

005 * for given MEMBER record.

006 *

007 **********************************************************************

008 $INCLUDE VSINCLUDES VSCOMMON

009 $INCLUDE VSINCLUDES VSSP.COMMON.EQUATES

010 $INCLUDE VSINCLUDES VSSP.EQUATES

011 **********************************************************************

012 * CONDFLG is passed with the results to signify:

013 * CONDFLG = 0 means data in RESULT is OK

014 * CONDFLG = 1 means no data in RESULT, don't raise error

015 * CONDFLG = 2 means no data in RESULT, raise error to UI

016 **********************************************************************

017 * ARGS = MEMBER.ID

018 *

019 * RESULT REC1 = CONDFLG

020 * RESULT REC2 = FIRST DONATION REPORT ROW OR ERROR CODE

021 * RESULT REC3 = SECOND DONATION REPORT ROW OR ERROR DATA/MESSAGE

022 * RESULT REC4 = THIRD DONATION REPORT ROW ...

023 *

024 * DONRS<1> = DONATION.ID

025 * DONRS<2> = MEMBER.ID

026 * DONRS<3> = MEMBER.NAME

027 * DONRS<4> = DATE

028 * DONRS<5> = FUND

029 * DONRS<6> = FUND.DESC

030 * DONRS<7> = AMOUNT

031 *

032 **********************************************************************

033 RTNCD = 1 ; * Default to successful execution

034 CONDFLG = 2 ; * Assume no data and need to raise error

035 **********************************************************************

036 * Test to make sure file is available

037 CALL VSOPEN(DBID, "DONATIONS", 0, FVAR, RTN)

038 IF RTN EQ 0 THEN

039   RTNCD = 0

040   RESULT = RESULT : vxRM : "Cannot open DONATIONS file."

041   RETURN

042 END

043 *

044 * Get and save current term characteristics

045 EXECUTE "TERM" CAPTURING TERMDATA RETURNING NOTHING

046 PAGE.WIDTH = FIELD(TRIM(TERMDATA<2>), ' ', 3)

047 PAGE.DEPTH = FIELD(TRIM(TERMDATA<3>), ' ', 3)

048 LINE.SKIP = FIELD(TRIM(TERMDATA<4>), ' ', 4)

049 LF.DELAY = FIELD(TRIM(TERMDATA<5>), ' ', 4)

050 FF.DELAY = FIELD(TRIM(TERMDATA<6>), ' ', 4)

051 TERMDATA = ''

052 *

053 * Set term characteristics for capture

054 EXECUTE "TERM 350,32767,0,0,0" CAPTURING NOTHING RETURNING NOTHING

055 *

056 * Execute donation sort command

057 RESULT = ''

058 LINEDATA = ''

059 SENTENCE = 'SORT DONATIONS BY DATE WITH MEMBER.ID "' : ARGS : '" DONATION.ID MEMBER.ID MEMBER.NAME DATE FUND FUND.DESC TOTAL AMOUNT ID-SUPP (N'

060 *

061 EXECUTE SENTENCE CAPTURING LINEDATA RETURNING NOTHING

062 *

063 * Clean up LINEDATA data

064 * Trim off leading nulls

065 CNT = DCOUNT(LINEDATA, vxAM)

066 X = 0

067 LOOP X = X + 1 UNTIL X > CNT OR LINEDATA<X> NE '' DO

068   LINEDATA = DELETE(LINEDATA, 1, 0, 0)

069   CNT = CNT - 1

070 REPEAT

071 * Trim off any trailing nulls

072 X = CNT + 1

073 LOOP X = X - 1 UNTIL X = 0 OR LINEDATA<X> NE '' DO

074   LINEDATA = DELETE(LINEDATA, X, 0, 0)

075   CNT = CNT - 1

076 REPEAT

077 * Delete items(records) listed line

078 LINEDATA = DELETE(LINEDATA, CNT, 0, 0)

079 CNT = CNT - 1

080 * Trim off any additional trailing nulls

081 X = CNT + 1

082 LOOP X = X - 1 UNTIL X = 0 OR LINEDATA<X> NE '' DO

083   LINEDATA = DELETE(LINEDATA, X, 0, 0)

084   CNT = CNT - 1

085 REPEAT

086 * Delete first report header line

087 LINEDATA = DELETE(LINEDATA, 1, 0, 0)

088 * Trim off any additional leading nulls

089 CNT = DCOUNT(LINEDATA, vxAM)

090 X = 0

091 LOOP X = X + 1 UNTIL X > CNT OR LINEDATA<X> NE '' DO

092   LINEDATA = DELETE(LINEDATA, 1, 0, 0)

093   CNT = CNT - 1

094 REPEAT

095 * get first column heading line and convert data based on heading

096 H1 = TRIM(LINEDATA<1>)

097 CONVERT " " TO vxAM IN H1

098 * Delete heading lines

099 LINEDATA = DELETE(LINEDATA, 1, 0, 0)

100 LINEDATA = DELETE(LINEDATA, 1, 0, 0)

101 LINEDATA = DELETE(LINEDATA, 1, 0, 0)

102 *

103 CNT = DCOUNT(LINEDATA, vxAM)

104 X = CNT + 1

105  LOOP X = X - 1 UNTIL X = 0 DO

106  IF LINEDATA<X> = "" THEN

107   LINEDATA = DELETE(LINEDATA, X, 0, 0)

108   END

109 REPEAT

110 * Get column count

111 COLCNT = DCOUNT(H1, vxAM)

112 *

113 * Get report line count

114 CNT = DCOUNT(LINEDATA, vxAM)

115 * Extract column data for each line

116 FOR X = 1 TO COLCNT

117   FOR I = 1 TO CNT

118     RESULT<I,X> = TRIM(LINEDATA<I>[1,LEN(H1<X>)])

119     LINEDATA<I> = LINEDATA<I>[LEN(H1<X>) + 2, 32767]

120   NEXT I

121 NEXT X

122 CONVERT vxAM TO vxRM IN RESULT

123 CONVERT vxVM TO vxAM IN RESULT

124 *

125 * Reset terminal characteristics

126 SENTENCE = "TERM " : PAGE.WIDTH : "," : PAGE.DEPTH : "," : LINE.SKIP : "," : LF.DELAY : "," : FF.DELAY

127 EXECUTE SENTENCE CAPTURING NOTHING RETURNING NOTHING

128 *

129 * Setup RESULT for return

130 IF RESULT NE '' THEN

131   CONDFLG = 0

132   RESULT = CONDFLG : vxRM : RESULT

133 END ELSE

134   RESULT = "Error in generating donation report for member: " : ARGS<1>

135 END

136 RETURN

137 END

The Server program will make an indirect call to the program called SP.MEMBER.DONATION.REPORT (CALL @PROGRAM  where the variable PROGRAM has be set to “SP.MEMBER.DONATION.REPORT”). The first line of the program must contain the word SUBROUTINE and define four arguments for the subroutine call:

SUBROUTINE SP.DONATION.REPORT(DBID, ARGS, RTNCD, RESULT)

A Stored Procedure Subroutine also must be cataloged in the master dictionary or Voc file of the Servers account (MVSERVER).  The physical program may exist in another account, as may all the data.  However, the programmer must make sure the program can be called from the MVSERVER account.

How files are processed within the MVSERVER account should be considered.  If the files needed have been mapped (via the mvServer client software), their q-pointer names may easily be found in the VSDATABASE file.  The VSOPEN routine has been supplied, by VantagePoint Software, as part of the mvServer software to provide access to any mapped files.  A Stored Procedure program, needing to open files, should use the VSOPEN program.  More information on this routine is given later.  If the file has not been mapped, the programmer will have to create/maintain his or her own q-pointer within the MVSERVER Account and use normal File Opens.

The first argument DBID is a reference to a given Account on the PICK Host system. This is based on the Virtual Database Definition (VDD) used to make the connection from the Client program to the Server. If you want to deal with files that have their pointers defined in a particular account then you would use a VDD that is defined to point to that Pick account. This DBID argument refers to the “Database ID” which is the same as a reference to a given PICK account (from the client point of view each PICK Account is a separate database). This DBID argument is only used by the VSOPEN utility program. In conjunction with the VSOPEN utility program there are other utility programs that you can use from a stored procedure. These subroutine programs can be found in the VSBP file on the MVSERVER account and include: VSREAD, VSWRITE, VSRELEASE, and VSSP.PROCEDURES. You would use the VSREAD, VSWRITE, and VSRELEASE programs to read items (with or without locks) and to write or release items. The VSSP.PROCEDURES is a pre-defined stored procedure that can do general Pick-type operations. There may be many occasions that you can use this subroutine rather than writing your own custom subroutine.

To extract the parameters passed by the client, the programmer need only get the data from the ARGS variable.

*

* Get data for Report

*

MemberID = ARGS 

If there are multiple parameters you can use the field statement to extract each parameter passed within the ARGS variable. Typically, different parameters are passed from the client program delimited by record marks (Char 255), which become substitute record marks (Char 249) within the ARGS variable of the stored procedure. The following is an example that extracts two parameters from the ARGS variable:

*

* Get data for Report

*

MemberID = Field(ARGS, vxRM, 1)

ReportOption = Field(ARGS, vxRM, 2)

The programmer should be able to create the Sales report within the Stored Procedure routine, or by calling or executing some standard program.

For most procedures there will be a requirement to pass information back to the client program.  As in this example, the programmer needs to return a list of donation information for a particular member back to the client, so the client program may display this information in a multi-column listbox or grid control.  To accomplish this task a dynamic string would be created where each field of data would be separated by attribute marks and a record or row would be delimited by a record mark. Because record marks in the client side are CHAR(255)s (which are segment marks and are illegal in most PICK implementations) the stored procedure uses an equate value vxRM which in the PICK subroutine is defined as CHAR(249) and is converted to a CHAR(255) by the time it gets back to the client. The dynamic array is assigned to the RESULT argument for return to the Client system.

A stored procedure may also call its own procedures, which in turn, may call other procedures.  The programmer must remember that all files and procedures handled within their stored procedure program must be defined and/or compiled in the Server Account.

File references used in a called stored procedure can use a special subroutine available on the MVSERVER account.  This is the VSOPEN subroutine and provides to a called subroutine program, a file variable that can be used for file processing based on the VSDATABASE mapping and resulting q-pointer generated by the mapping process.  The VSOPEN program has four arguments that must be passed when calling this routine.  The calling syntax is as follows:

CALL VSOPEN(DBID, FILE.NAME, TYPE, FILE.REF, RTN)

Where the DBID is the Database handle passed to your stored procedure in your first argument. The FILE.NAME variable is a string containing the name of the file you want open. The TYPE variable has either the value 0 or 1. A 0 value represents the data level of the file, a 1 value represents the dictionary level of the file. The FILE.REF variable is the Pick file reference variable, returned from the VSOPEN routine. Once returned to your stored procedure program, you can use this file reference variable as you could any file variable that you opened with the standard Pick Basic OPEN statement. The RTN value is a flag indicating if the VSOPEN routine was successful in returning a valid file reference for the supplied file name. If RTN has a value of 1, the execution was successful and the FILE.REF argument has a valid file reference variable. If RTN has a zero value, the VSOPEN call failed for some reason, usually due to the supplied file name not being mapped.

In our example stored procedure we used the VSOPEN program as follows:

037 CALL VSOPEN(DBID, "DONATIONS", 0, FVAR, RTN)

038 IF RTN EQ 0 THEN

039   RTNCD = 0

040   RESULT = RESULT : vxRM : "Cannot open DONATIONS file."

041   RETURN

042 END

In addition to the resulting data being returned via the RESULT argument there is also returned to the Client side a flag in the form of the RTNCD argument. This argument indicates to the Client whether the Stored Procedure was executed properly or not. In our example program we initialize the RTNCD argument with the value “1” to indicate a proper execution:

033 RTNCD = 1 ; * Default to successful execution

In the case the VSOPEN routine fails we set the RTNCD argument to the value “0” to indicate a failure (see the code above). Most of the time the RTNCD will be set to 1 even though the result of the stored procedure is not what client wants or expects. In addition to the result data being passed back to the client through the RESULT argument we typically also pass a flag as the first record (before any result data) of the RESULT argument dynamic array. This flag is known as the condition flag and the variable we traditionally use within the stored procedure is the CONDFLG variable. The CONDFLG value is passed with any results to signify the following:

If CONDFLG is 0 this means any data in the RESULT argument is OK and what is expected. If CONDFLG has a value of “1” this means that there is no data in the RESULT argument but that this is OK and not an error condition sufficient to raise and error to the Client User Interface (UI). If CONDFLG has a value of “2” then there is no data or incorrect data in the RESULT argument and that the Client should be notified by raising and error to the user interface (UI). Typically this is done by display a message box with the error by the Client program. Usually if the CONDFLG is set to anything other than “0” one or more error message lines can be passed back to the Client as additional records (delimited by vxRM) beyond the CONDFLG record in the RESULT argument. The example code deals with this CONDFLG value as follows:

034 CONDFLG = 2 ; * Assume no data and need to raise error

129 * Setup RESULT for return

130 IF RESULT NE '' THEN

131   CONDFLG = 0

132   RESULT = CONDFLG : vxRM : RESULT

133 END ELSE

134   RESULT = CONDFLG : vxRM : "Error in generating donation report for member: " : ARGS<1>

135 END

Another important point to remember is that any PICK/Basic program routine used as a stored procedure cannot perform any standard input/output statements, as this could interrupt the normal client/server protocol flow between the server and the client program.

VPMv20 Dynamic Link Library

The VantagePoint MultiValue Server Dynamic Link Library (DLL) (VPMv20.dll) provides the means of communication between a client program and the mvServer software on a PICK Host system.  It is responsible for managing the type of connection, whether network or serial, and the messaging protocol which transfers commands and data between the client and the server.

There are a number of exported DLL functions that are part of the Vantage MultiValue Server Library (VPMv20.dll).  The following are a few of the more important ones:  

If the communication between the client and the server gets road blocked, or if communication gets out of synchronization, there is a separate program that you can use to try and reset the Vantage MultiValue Server DLL (VPMv20.dll). In most cases this will free up the DLL and even remove it from memory in the case of a catastrophic communication failure. This separate program is the MultiValue Connection Reset program as shown below:  

There are two methods of resetting the connection, a soft reset and a hard reset, as represented by two Command buttons. A soft reset will attempt to close the connection record on the PICK Host system, and close down the mvServer software. It then cleans up any client Windows resources used by the VPMv20 DLL. A hard reset will only attempt to clean up any client Windows resource used. If this fails to restore proper connections the problem is in the Gateway connection Channel object (VPChnl20.EXE). In this case you can shut down Gateway service or in extremely rare conditions use the Task Manager of Windows to kill any process associated with the gateway server objects.

This concludes our introduction to the mvServer Product.

mvServer 2.0 Pricing

mvServer 2.0 is not sold separately and is only available as part of the mvComponents Suite 2.0.

Previous Page                                                           Pages  [ 1  2  3  4 ]   

Back to mvComponents Suite 2.0 Overview

support@vpsoft.com