Method, system, and program for accessing stored procedures in a message broker

• US 6,636,855 B2
• Filed: 03/09/2001
• Issued: 10/21/2003
• Est. Priority Date: 03/09/2001
• Status: Active Grant

• Current Assignee: Snap, Inc.
• Original Assignee: International Business Machines Corporation
• Inventors: Wolfson, Charles Daniel, Todd, Stephen James, Nelin, Constance Jane, Schmidt, Marc-Thomas, Holloway, Timothy Nicholas
• Primary Examiner(s): Metjahic, Safet
• Assistant Examiner(s): LEROUX, ETIENNE PIERRE
• Application Number:
  US09/802,409
• Publication Number:
  US 20020129023A1
• Time in Patent Office: 956 Days
• Field of Search: 707/2, 707/3, 707/10, 707/100, 707/102, 709/203, 709/205, 709/201, 709/216, 709/225, 709/228, 709/229, 709/206
• US Class Current: 1/1
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

Provided is a method, system, and program for processing a data flow. A message is received at an input node. The message is transferred to a plurality of nodes according to a defined data flow. At one stored procedure node, the message is processed to generate input parameters for a stored procedure call. The stored procedure node transfers the stored procedure call to a stored procedure at a server computer, wherein the server executes the stored procedure to process data and generate output.

Claims

1. A method for processing a data flow, wherein the data flow defines connected nodes, and wherein each node defines an operation to perform with respect to a message, comprising:

receiving the message at an input node;

transferring the message to a plurality of nodes according to a defined data flow;

processing the message, at a stored procedure node, to generate input parameters for a stored procedure call; and

transferring, with the stored procedure node, the stored procedure call with the input parameters to the stored procedure at a database server executing in a computer, wherein the database server executes the stored procedure to generate stored procedure output.

2. The method of claim 1, wherein the stored procedure processes a database and generates the output by performing operations on data in the database, wherein the output is capable of comprising output that is a member of the set of output comprising one or more result sets of data from the database table and one or more output parameters resulting from stored procedure operations performed on data in the database table.

3. The method of claim 1, further comprising processing, with the stored procedure node, an input mapping to determine the data in the message to use as at least one input parameter to the stored procedure call.

4. The method of claim 1, further comprising:

receiving the stored procedure output after the stored procedure program completes execution;

adding data from the stored procedure output to at least one output message; and

.

further processing the output message according to the data flow.

5. The method of claim 1, further comprising processing an output mapping indicating how the output is mapped to locations in the message.

6. The method of claim 5, wherein the output is capable of including result sets and parameters, wherein the output mapping indicates a result set or parameter to element correspondence, wherein adding the data in the received result sets or parameters comprises adding the result set or parameter data to at least one element in the at least one message.

7. The method of claim 6, further comprising:

generating metadata describing each received result set; and

for each received result set in the output, adding the metadata to the element corresponding to the result set in the message.

8. The method of claim 6, wherein the result sets in the output comprise multiple rows of columns of data, wherein the output mapping indicates a row element to row correspondence, wherein adding the data in the received result set to the at least one message further comprises adding the data for each row in each result set received from the stored procedure to one row element in the at least one message.

9. The method of claim 6, wherein the output mapping indicates a column element to column correspondence, wherein adding the data in the received result set to the message further comprises adding the data for each column in each row in each result set received from the stored procedure to one column element.

10. The method of claim 6, wherein the output mapping indicates a result set to result set correspondence, a row to row element correspondence, and a column to column element correspondence, wherein adding the data in each received result set to the message further comprises:

adding metadata describing the result set to the result set element;

adding one row element to the message for each row in the result set; and

for each row, adding one column element including column data from the row for each returned column in the rows of the result set.

11. The method of claim 10, wherein each row element from one result set is a child element of the result set element of the result set, and wherein each column element is a child element of the row element of the row including the column element data.

12. The method of claim 6, wherein the at least one message is in the Extensible Markup Language (XML) format, and wherein the elements comprise XML tagged elements.

13. The method of claim 1, wherein multiple nodes in the data flow are capable of calling one or more stored procedures in the server.

14. The method of claim 1, wherein the stored procedure is capable of transmitting output to multiple nodes in the data flow.

15. The method of claim 1, wherein the received message comprises a customer order for a product, and wherein the data flow processes the customer order through the nodes.

16. A method for processing a data flow, wherein the data flow defines connected nodes, and wherein each node defines an operation to perform with respect to a message, comprising:

receiving, at a receiving node in the data flow, output generated by a stored procedure program executing on a database server after the stored procedure program completes execution;

adding, with the receiving node, data from the stored procedure output to at least one output message; and

.

further processing, with the receiving node, the output message according to the data flow to transmit the output message to another node in the data flow.

17. The method of claim 16, further comprising:

transferring, with a stored procedure node in the data flow, a stored procedure call with the input parameters to the stored procedure at the server, wherein the output generated by the stored procedure is sent to the receiving node.

18. A system for processing a data flow, wherein the data flow defines connected nodes, and wherein each node defines an operation to perform with respect to a message, comprising:

means for receiving the message at an input node;

means for transferring the message to a plurality of nodes according to a defined data flow;

means for processing the message to generate input parameters for a stored procedure call; and

means for transferring the stored procedure call with the input parameters to the stored procedure at a database server computer, wherein the database server executes the stored procedure to generate stored procedure output.

19. The system of claim 18, wherein the stored procedure processes a database and generates the output by performing operations on data in the database, wherein the output is capable of comprising output that is a member of the set of output comprising one or more result sets of data from the database table and one or more output parameters resulting from stored procedure operations performed on data in the database table.

20. The system of claim 18, further comprising means for processing an input mapping to determine the data in the message to use as at least one input parameter to the stored procedure call.

21. The system of claim 18, further comprising:

means for receiving the stored procedure output after the stored procedure program completes execution;

means for adding data from the stored procedure output to at least one output message; and

means for further processing the output message according to the data flow.

22. The system of claim 18, further comprising means for processing an output mapping indicating how the output is mapped to locations in the message.

23. The system of claim 22, wherein the output is capable of including result sets and parameters, wherein the output mapping indicates a result set or parameter to element correspondence, wherein the means for adding the data in the received result sets or parameters performs adding the result set or parameter data to at least one element in the at least one message.

24. The system of claim 22, further comprising:

means for generating metadata describing each received result set; and

means for adding, for each received result set in the output, the metadata to the element corresponding to the result set in the message.

25. The system of claim 23, wherein the result sets in the output comprise multiple rows of columns of data, wherein the output mapping indicates a row element to row correspondence, wherein the means for adding the data in the received result set to the at least one message performs adding the data for each row in each result set received from the stored procedure to one row element in the at least one message.

26. The system of claim 23, wherein the output mapping indicates a column element to column correspondence, wherein the means for adding the data in the received result set to the message further performs adding the data for each column in each row in each result set received from the stored procedure to one column element.

27. The system of claim 23, wherein the output mapping indicates a result set to result set correspondence, a row to row element correspondence, and a column to column element correspondence, wherein the means for adding the data in each received result set to the message further performs:

adding metadata describing the result set to the result set element;

adding one row element to the message for each row in the result set; and

for each row, adding one column element including column data from the row for each returned column in the rows of the result set.

28. The system of claim 27, wherein each row element from one result set is a child element of the result set element of the result set, and wherein each column element is a child element of the row element of the row including the column element data.

29. The system of claim 23, wherein the at least one message is in the Extensible Markup Language (XML) format, and wherein the elements comprise XML tagged elements.

30. The system of claim 18, wherein multiple nodes in the data flow are capable of calling one or more stored procedures in the server.

31. The system of claim 18, wherein the stored procedure is capable of transmitting output to multiple nodes in the data flow.

32. The system of claim 18, wherein the received message comprises a customer order for a product, and wherein the data flow processes the customer order through the nodes.

33. A system for processing a data flow, wherein the data flow defines connected nodes, and wherein each node defines an operation to perform with respect to a message, comprising:

means for receiving output generated by a stored procedure program executing on a database server after the stored procedure program completes execution;

means for adding data from the stored procedure output to at least one output message; and

.

means for further processing the output message according to the data flow to transmit the output message to another node in the data flow.

34. The system of claim 33, further comprising:

means for transferring a stored procedure call with the input parameters to the stored procedure at the server, wherein the stored procedure generates the output.

35. An article of manufacture implementing code for processing a data flow, wherein the data flow defines connected nodes, and wherein each node defines an operation to perform with respect to a message, by:

receiving the message at an input node;

transferring the message to a plurality of nodes according to a defined data flow;

processing the message, at a stored procedure node, to generate input parameters for a stored procedure call; and

transferring, with the stored procedure node, the stored procedure call with the input parameters to the stored procedure at a database server, wherein the database server executes the stored procedure to generate stored procedure output.

36. The article of manufacture of claim 35, wherein the stored procedure processes a database and generates the output by performing operations on data in the database, wherein the output is capable of comprising output that is a member of the set of output comprising one or more result sets of data from the database table and one or more output parameters resulting from stored procedure operations performed on data in the database table.

37. The article of manufacture of claim 35, further comprising processing, with the stored procedure node, an input mapping to determine the data in the message to use as at least one input parameter to the stored procedure call.

38. The article of manufacture of claim 35, further comprising:

receiving the stored procedure output after the stored procedure program completes execution;

adding data from the stored procedure output to at least one output message; and

further processing the output message according to the data flow.

39. The article of manufacture of claim 35, further comprising processing an output mapping indicating how the output is mapped to locations in the message.

40. The article of manufacture of claim 39, wherein the output is capable of including result sets and parameters, wherein the output mapping indicates a result set or parameter to element correspondence, wherein adding the data in the received result sets or parameters comprises adding the result set or parameter data to at least one element in the at least one message.

41. The article of manufacture of claim 40, further comprising:

generating metadata describing each received result set; and

for each received result set in the output, adding the metadata to the element corresponding to the result set in the message.

42. The article of manufacture of claim 40, wherein the result sets in the output comprise multiple rows of columns of data, wherein the output mapping indicates a row element to row correspondence, wherein adding the data in the received result set to the at least one message further comprises adding the data for each row in each result set received from the stored procedure to one row element in the at least one message.

43. The article of manufacture of claim 40, wherein the output mapping indicates a column element to column correspondence, wherein adding the data in the received result set to the message further comprises adding the data for each column in each row in each result set received from the stored procedure to one column element.

44. The article of manufacture of claim 40, wherein the output mapping indicates a result set to result set correspondence, a row to row element correspondence, and a column to column element correspondence, wherein adding the data in each received result set to the message further comprises:

adding metadata describing the result set to the result set element;

adding one row element to the message for each row in the result set; and

for each row, adding one column element including column data from the row for each returned column in the rows of the result set.

45. The article of manufacture of claim 44, wherein each row element from one result set is a child element of the result set element of the result set, and wherein each column element is a child element of the row element of the row including the column element data.

46. The article of manufacture of claim 40, wherein the at least one message is in the Extensible Markup Language (XML) format, and wherein the elements comprise XML tagged elements.

47. The article of manufacture of claim 35, wherein multiple nodes in the data flow are capable of calling one or more stored procedures in the server.

48. The article of manufacture of claim 35, wherein the stored procedure is capable of transmitting output to multiple nodes in the data flow.

49. The article of manufacture of claim 35, wherein the received message comprises a customer order for a product, and wherein the data flow processes the customer order through the nodes.

50. An article of manufacture implementing code for processing a data flow, wherein the data flow defines connected nodes, and wherein each node defines an operation to perform with respect to a message, by:

receiving, at a receiving node in the data flow, output generated by a stored procedure program executing on a database server after the stored procedure program completes execution;

adding, with the receiving node, data from the stored procedure output to at least one output message; and

.

further processing, with the receiving node, the output message according to the data flow to transmit the output message to another node in the data flow.

51. The article of manufacture of claim 50, further comprising:

transferring, with a stored procedure node in the data flow, a stored procedure call with the input parameters to the stored procedure at the server, wherein the output generated by the stored procedure is sent to the receiving node.