System, method and computer program product for passing host variables to a database management system

• US 5,742,810 A
• Filed: 08/31/1995
• Issued: 04/21/1998
• Est. Priority Date: 08/31/1995
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Lo, Tak Ming, Pirahesh, Mir Hamid, Sharp, Frederick Thomas, Wang, I-Shin Andy, Ng, John Shek-Luen, Roth, Gerald Herman
• Primary Examiner(s): Amsbury, Wayne
• Assistant Examiner(s): HOMERE, JEAN RAYMOND
• Application Number:
  US08/521,710
• Time in Patent Office: 964 Days
• Field of Search: 395/604, 395/610, 395/612, 395/705, 395/704, 395/702
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2443 Stored procedures
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99942 Manipulating data structure, e.g. compression, compaction, compilation

Abstract

A system and method for passing arrays and structures as host variables to a stored procedure. An application program packs an array or structure into a binary large object (BLOB) and sends the BLOB to a database management system. An adapter called by the database management system, and coupled to the stored procedure, passes a reference to the data area in the BLOB to the stored procedure. After completion of the stored procedure, the BLOB is returned to the application program. Finally, upon the return from the stored procedure call, the application program unpacks the BLOB back into the array or structure.

Claims

1. A system for passing arrays or structures as host variables to a stored procedure, comprising:

means for packing an array or structure into a large object;

means for sending said large object to a database management system, wherein said large object comprises a descriptor area and a data area; and

an adaptor, coupled to said database management system, for passing a reference to said data area to the stored procedure.

2. The system of claim 1, further comprising:

means for returning said large object to a user after the stored procedure has populated said data area of said large object; and

means for unpacking said large object returned to said user back into said array or structure.

3. The system of claim 1, wherein the array is an array of scalar values.

4. The system of claim 1, wherein the array is an array of instances of a user-defined structure.

5. The system of claim 1, wherein the array is multi-dimensional.

6. A system for passing host variables to a stored procedure, comprising:

means for packing the contents of a first data type, defined in a host language, into a second data type, wherein said first data type is not a recognized SQL data type and said second data type is a recognized SQL data type;

means for sending said second data type to a database management system; and

adapting means, coupled to said database management system, for passing a reference to a data area in said second data type that is associated with said first data type to the stored procedure.

7. The system of claim 6, wherein said first data type is a variable length string and said second data type is a SQL character data type.

8. The system of claim 6, wherein said first data type is an array and said second data type is a large object, said large object having an array descriptor area and a data area.

9. The system of claim 6, wherein said first data type is a user-defined structure and said second data type is a large object.

10. A system for generating a client application/stored procedure, comprising:

means for interpreting source code written in a host language;

means for generating p-code to pack the contents of a host variable into a large object, wherein said host variable is not a recognized SQL data type, and wherein said large object is a recognized SQL data type;

means for registering said large object with a pre-processing service means in a database management system;

means for receiving an array of function pointers from said pre-processing means;

means for using said array of function pointers within the client application/stored procedure to call a stored procedure; and

means for generating p-code to unpack the contents of said large object returned from the stored procedure into said host variable.

11. The system of claim 10, wherein said host variable is a user-defined structure.

12. The system of claim 10, wherein said host variable is an array.

13. The system of claim 10, wherein said host variable is a multi-dimensional array.

14. A method for passing arrays or structures as host variables to a stored procedure, comprising the steps of:

packing a first array or structure into a large object;

sending said large object to a database management system on a server, wherein said large object comprises a descriptor area and a data area;

invoking an adapter for the stored procedure, wherein said adapter is coupled to said database management system;

said adapter passing a reference to said data area to the stored procedure.

15. The method of claim 14, further comprising the steps of:

returning said large object to a user; and

unpacking said large object back into said first array or structure.

16. The method of claim 14, wherein said step of packing comprises the step of packing an array of scalar values into a large object.

17. The method of claim 14, wherein said step of packing comprises the step of packing an array of instances of a user-defined structure into a large object.

18. The method of claim 14, wherein said step of packing comprises the step of packing a multi-dimensional array into a large object.

19. A method for passing host variables to a stored procedure, comprising the steps of:

packing the contents of a first data type, defined in a host language, into a second data type, wherein said first data type is not a recognized SQL data type and said second data type is a recognized SQL data type;

sending said second data type to a database management system; and

passing a reference to a data area in said second data type that is associated with said first data type to the stored procedure.

20. The method of claim 19, wherein said step of translating a first data type comprises the step of translating a varying length string into a second data type that is a recognized SQL data type.

21. The method of claim 19, wherein said step of translating a first data type comprises the step of translating an array into a large object.

22. The method of claim 19, wherein said step of translating a first data type comprises the step of translating a user-defined structure into a large object.

23. A method for generating a client application/stored procedure, comprising the steps of:

interpreting source code written in a host language;

generating p-code to pack the contents of a host variable into a large object, wherein said host variable is not a recognized SQL data type, and wherein said large object is a recognized SQL data type;

registering said large object with a pre-processing service means in a database management system;

receiving an array of function pointers from said pre-processing means;

calling a stored procedure using said array of function pointers within the client application/stored procedure; and

generating p-code to unpack the contents of said large object returned from the stored procedure into said host variable.

24. The method of claim 23, wherein said step of generating comprises the step of generating p-code to pack the contents of a user-defined structure into a large object.

25. The method of claim 23, wherein said step of generating comprises the step of generating p-code to pack the contents of an array into a large object.

26. The method of claim 23, wherein said step of generating comprises the step of generating p-code to pack the contents of a multi-dimensional into a large object.

27. A program storage device readable by a machine, tangibly embodying a program of instructions executable by the machine for passing data structures as host variables to a stored procedure, the program of instructions comprising the steps of:

packing the contents of a first data type, defined in a host language, into a second data type, wherein said first data type is not a recognized SQL data type and said second data type is a recognized SQL data type;

sending said second data type to a database management system; and

passing a reference to a data area in said second data type that is associated with said first data type to the stored procedure.

28. The program storage device of claim 27, wherein said step of translating a first data type comprises the step of translating a varying length string into a second data type that is a recognized SQL data type.

29. The program storage device of claim 27, wherein said step of translating a first data type comprises the step of translating an array into a large object.

30. The program storage device of claim 27, wherein said step of translating a first data type comprises the step of translating a user-defined structure into a large object.

31. A program storage device readable by a machine, tangibly embodying a program of instructions executable by the machine for generating a client application/stored procedure, the program of instructions comprising the steps of:

interpreting source code written in a host language;

generating p-code to pack the contents of a host variable into a large object, wherein said host variable is not a recognized SQL data type, and wherein said large object is a recognized SQL data type;

registering said large object with a pre-processing service means in a database management system;

receiving an array of function pointers from said pre-processing means;

calling a stored procedure using said array of function pointers within the client application/stored procedure; and

generating p-code to unpack the contents of said large object returned from the stored procedure into said host variable.

32. The program storage device of claim 31, wherein said step of generating comprises the step of generating p-code to pack the contents of a user-defined structure into a large object.

33. The program storage device of claim 31, wherein said step of generating comprises the step of generating p-code to pack the contents of an array into a large object.

34. The program storage device of claim 31, wherein said step of generating comprises the step of generating p-code to pack the contents of a multi-dimensional into a large object.