Stored procedure universal calling interface

Details Stored procedure universal calling interface Stored procedure universal calling interface

1996-05-31

2001-12-04

Courtenay, III, St. John (Department: 2151)

Electrical computers and digital processing systems: multicomput

Computer-to-computer data routing

Least weight routing

C707S793000

Reexamination Certificate

active

06327629

ABSTRACT:

BACKGROUND OF THE INVENTION
1. Field of the Invention
The present invention relates generally to a universal calling interface for invoking stored procedures on a server, and in particular, to a method for executing stored procedures that eliminates the requirement for identifying the parameters or their types prior to invoking the stored procedure.
2. Description of the Related Art
Stored procedures are collections of pre-defined procedural code that typically employ SQL to access databases. There are a number of benefits in using stored procedures, including function encapsulation, performance enhancement, client-server processing, and security. Stored procedures are not unlike procedures found in other high level languages, in that input and output parameters can be passed to stored procedures. Generally, stored procedures may take any number of parameters, which are defined internally within the procedure.
For example, following is an example of an SQL stored procedure:
CREATE PROCEDURE PROC
@PARAM1 INTEGER
@PARAM2 LONG
@PARAM3 STRING
SELECT ROW FROM TABLE WHERE
(A=PARAM1 AND
B=PARAM2 AND
C=PARAM3)
In the above example, the stored procedure is named “PROC” and is passed three parameters, “PARAM1”, “PARAM2”, and “PARAM3” are of type integer, long, and string, respectively.
Generally, a user explicitly invokes stored procedures in an interactive environment, but such procedures can also be invoked by other programs. For example, a stored procedure may be called from a VisualBasic application or interpreter, or an Open DataBase Connectivity (ODBC) application, or any number of other environments. In these prior art methods, the parameters for the stored procedure are defined or described prior to the call
For example, the following is an example of a VisualBasic program using ODBC/CLI to invoke a stored procedure:
Sub main ( )
Dim henv As Long
′ Environment handle
Dim hdbc As Long
′ Database connection handle
Dim hstmt As Long
′ Statement handle
Dim rc As Integer
′ Return codes
Dim stmt As String
Dim plen(1) As Long
Dim Tab_Name As String
stmt = “CALL inpsrv(?)”
Tab_Name = “PRESIDENT”
rc =
SQLAllocEnv(henv)
rc =
SQLAllocConnect(henv, hdbc)
rc =
SQLSetConnectOption(hdbc, SQL_AUTOCOMMIT,
SQL_AUTOCOMMIT_OFF)
rc =
SQLConnect(hdbc, “sample”, Len(“sample”),
“userid”, Len(“userid”), “password”,
Len(“password”))
rc =
SQLAllocStmt(hdbc, hstmt)
rc =
SQLPrepare(hstmt, stmt, Len(stmt))
plen(0) = Len(Tab_Name)
rc =
SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT,
SQL_BASIC_STRING, SQL_CHAR, Len(Tab_Name), 0,
ByVal Tab_Name, Len(Tab_Name))
rc =
SQLExecute(hstmt)
rc =
SQLFreeStmt(hstmt, SQL_DROP)
rc =
SQLTransact(henv, hdbc, SQL_COMMIT)
rc =
SQLDisconnect(hdbc)
rc =
SQLFreeConnect(hdbc)
rc =
SQLFreeEnv(henv)
End Sub
In the above example, the “SQLAllocStmt”, “SQLPrepare”, “SQLBindParameter”, “SQLExecute”, and “SQLFreeStmt” statements are necessary to pass a parameter to a stored procedure. As can be seen, a lot of programming is required to describe the parameters passed to a stored procedure. This programming is inefficient and redundant. Thus, there is a need in the art for a more efficient method of invoking stored procedures with automated parameter checking.
SUMMARY OF THE INVENTION
To overcome the limitations in the prior art described above, and to overcome other limitations that will become apparent upon reading and understanding the present specification, the present invention discloses a method, apparatus, and article of manufacture for executing a stored procedure in a computer system. In accordance with the present invention, a stored procedure is invoked through a universal calling interface without first declaring the parameters. The computer retrieves parameter information, such as parameter type and address, for the stored procedure from a catalog, performs parameter checking for the parameters specified for the stored procedure using the retrieved parameter information, and then executes the stored procedure in the computer when the parameters match the retrieved parameter information. As a result, the present invention reduces the programming overhead involved in invoking a stored procedure.


REFERENCES:
patent: 4992971 (1991-02-01), Hayashi
patent: 5295256 (1994-03-01), Bapat
patent: 5469574 (1995-11-01), Chang et al.
patent: 5566330 (1996-10-01), Sheffield
patent: 5572673 (1996-11-01), Shurts
patent: 5694595 (1997-12-01), Jacobs et al.
patent: 5724556 (1998-03-01), Souder et al.
patent: 6112199 (2000-08-01), Nelson
IBM Technical Disclosure Bulletin vol. 18, No. 5 pp. 1552-1553, Oct. 1975.*
Mullins, Craig; “What is a stored procedure”, Enterprise Syste,s Journal, v9, n12, p67(4), Dec. 1994.*
Edelstein, Herb, Using stored procedures and triggers, DBMS, v5, n10, p66(5), Sep. 1992.*
Kelly C. Bourne, “Putting Rigor Back in RAD”, Database Programming and Design, p. 1-8, Aug. 1994.*
Marjorie Thorne, “Products”, DBMS, p. 1, Jan. 1995.*
Jurgen Annevelink, “Database Programming Languages: A Functional Approach”, ACM, p. 318-327, 1991.*
Shan, “Objects on the Server: A Natural Evolution”, Object Magazine May 1995.*
Rhodas, “Ease Into Client/Server With DB2 Stored Procedures”, Enterprise Systems Journal v11,n8 p. 38(4), 1996.*
Xoomes, “ODBMS Myths & Realities”, JOOP, 1994.

Affiliated with

Also associated with

Rating

Stored procedure universal calling interface does not yet have a rating. At this time, there are no reviews or comments for this patent.

If you have personal experience with Stored procedure universal calling interface, we encourage you to share that experience with our LandOfFree.com community. Your opinion is very important and Stored procedure universal calling interface will most certainly appreciate the feedback.

Rate now

Comments { 0 }

Profile ID: LFUS-PAI-O-2565691