SYSTEM FOR CONVERTING MESSAGE DATA INTO RELATIONAL TABLE FORMAT

• US 20090157596A1
• Filed: 12/09/2008
• Published: 06/18/2009
• Est. Priority Date: 01/02/2002
• Status: Abandoned Application

• Current Assignee: ServiceNow Incorporated
• Original Assignee: International Business Machines Corporation
• Inventors: COUCH, TANYA, MAYHEW, DEBRA L.
• Application Number:
  US12/331,377
• Publication Number:
  US 20090157596A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database

Abstract

The present invention is directed to a system and computer readable medium for converting messaging data into a relational table format in a database system, wherein the messaging data is within a messaging system. Through aspects of the system and computer readable medium of the present invention, the table function invokes at least one user defined function within the database system. Preferably, the table function is also a user-defined function. The table function can be used within a single SQL statement to access and convert the message data, and to populate directly a relational table. The user is no longer required to perform conversion steps because the conversion is automatically performed by the table function. Moreover, a table view can be created and utilized by a user to select a message and then have the selected message string returned in relational table format.

Claims

1. A computer readable medium containing programming instructions for converting messaging data into a relational table format in a database system, wherein the messaging data is within a messaging system, comprising the programming instructions for:

(a) providing a plurality of table formatting specifications;

(b) utilizing the plurality of table formatting specifications to automatically build and store a table function in the database system;

(c) invoking the table function from within the database system to access the messaging data; and

(d) converting the messaging data by the table function into specific data types according to the plurality of table formatting specifications, wherein the messaging data is transformed into the relational table format.

2. The computer readable medium of claim 1, wherein the table function invokes at least one messaging function within the database system.

3. The computer readable medium of claim 2, wherein the table function and the at least one messaging function are user-defined functions in the database system.

4. The computer readable medium of claim 3, wherein the at least one messaging function retrieves and reads messaging data in the message system.

5. The computer readable medium of claim 1, wherein the providing instruction (a) further includes the instruction for:

(a1) reading the plurality of table formatting specifications from a file.

6. The computer readable medium of claim 1, wherein the providing instruction (a) further includes the instructions for:

(a1) selecting a name and a type for the table function, wherein the type includes one of a retrieve function and a read function;

(a2) specifying where the table function is to be stored; and

(a3) indicating where the messaging data resides.

7. The computer readable medium of claim 6, wherein the specifying instruction (a2) further includes the instructions for:

(a2i) providing a database name and access information; and

(a2ii) allowing the user to validate the access information.

8. The computer readable medium of claim 6, wherein the indicating instruction (a3) further includes the instruction for:

(a3i) providing a service point name for the messaging data.

9. The computer readable medium of claim 6, wherein the indicating instruction (a3) further includes the instruction for:

(a3i) providing a system default endpoint for the messaging data.

10. The computer readable medium of claim 1, wherein the providing instruction (a) further includes the instruction for:

(a1) providing formatting information about the messaging data.

11. The computer readable medium of claim 10, wherein the providing instruction (a1) further includes the instruction for:

(a1i) designating a delimiter character, wherein the delimiter character separates the messaging data into column data.

12. The computer readable medium of claim 11, wherein the converting step (d) further comprising:

(d1) invoking a parser function within the database system for parsing the delimited messaging data.

13. The computer readable medium of claim 12, wherein the invoking step (d1) further includes:

(d1i) checking for the parser function within the database system;

(d1ii) building the parser function if it does not exist within the database system; and

(d1iii) registering the parser function to the database system after it is built.

14. The computer readable medium of claim 10, wherein the providing instruction (a1) further includes the instruction for:

(a1i) specifying a fixed-length format by indicating a position and length of each column.

15. The computer readable medium of claim 10, wherein the providing instruction (a) further includes the instruction for:

(a2) allowing a user to view the messaging data in the messaging system to verify the formatting information provided.

16. The computer readable medium of claim 1, wherein the messaging data comprises a message string, the message string including a plurality of substrings, wherein each substring represents data that is returned as a column in a table.

17. The computer readable medium of claim 16, wherein the providing instruction (a) further includes the instruction for:

(a1) defining a column for each substring of the plurality of substrings in the message string.

18. The computer readable medium of claim 17, wherein the defining instruction (a1) further includes the instructions for:

(a1i) naming each column; and

(a1ii) designating a data type for each column.

19. The computer readable medium of claim 18, wherein the defining instruction (a1) further includes the instruction for:

(a1iii) allowing the user to view the messaging data formatted according to the column definitions provided.

20. The computer readable medium of claim 19, wherein the providing instruction (a) further includes the instruction for:

(a2) building the table function based on the plurality of table formatting specifications collected from the user.

21. The computer readable medium of claim 20, wherein the converting step (d) further includes:

(d1) parsing the message string into the plurality of substrings; and

(d2) converting each substring into the designated data type corresponding to its column.

22. The computer readable medium of claim 1, wherein the providing instruction (a) further includes the instruction for:

(a1) allowing a user to create and name a table view based on the table formatting specifications.

23. The computer readable medium of claim 22, wherein the invoking instruction (c) further includes the instruction for:

(c1) selecting messaging data from the table view.

24. The computer readable medium of claim 1, wherein the providing instruction (a) further includes the instruction for:

(a1) allowing a user to review a summary of the table formatting specifications before building the table function.

25. The computer readable medium of claim 3, wherein the invoking instruction (c) further includes the instruction for:

(c1) integrating the table function within a structured query language statement.

26. The computer readable medium of claim 4 further populating directly a relational table in the database system with the returned messaging data.

27. A system for converting messaging data into a relational table format in a database system, wherein the messaging data is within a messaging system, the system comprising:

a processor;

a table function building application executable by the processor for receiving a plurality of table formatting specifications and for utilizing the plurality of table formatting specifications to automatically build and store a table function in the database system; and

means for invoking the table function from within the database system to access the messaging data;

wherein, once invoked, the table function converts the messaging data into specific data types according to the plurality of table formatting specifications and transforms the messaging data into the relational table format.

28. The system of claim 27, wherein the table function invokes at least one messaging function within the database system.

29. The system of claim 28, wherein the table function and the at least one messaging function are user-defined functions within the database system.

30. The system of claim 29, wherein the at least one messaging function retrieves and reads messaging data in the message system.

31. The system of claim 27, wherein the table function building application includes a means for collecting the table formatting specifications from a user.

32. The system of claim 27, wherein the table function building application includes means for downloading the table formatting specifications from a file.

33. The system of claim 32, wherein the collecting means comprises a graphical user interface, wherein the graphical user interface prompts a user to select a name and a type for the table function, wherein the type includes one of a retrieve function and a read function, to specify where the table function is to be stored, and to indicate where the messaging data resides.

34. The system of claim 33, wherein the graphical user interface further prompts the user to provide formatting information about the messaging data.

35. The system of claim 33, wherein the messaging data comprises a message string, the message string including a plurality of substrings, wherein each substring represents data that is returned as a column in a table.

36. The system of claim 35, wherein the graphical user interface further allows the user to define a column for each substring of the plurality of substrings in the message string.

37. The system of claim 33, wherein the table function building application builds the table function based on the plurality of table formatting specifications collected through the graphical user interface.

38. The system of claim 27, wherein the table function building application allows a user to create and name a table view based on the plurality of table formatting specifications.

39. The system of claim 38, wherein the invoking means includes means for selecting messaging data from the table view.

40. The system of claim 29, wherein the invoking means includes means for integrating the table function within a structured query language statement.

41. A system for generating a customized invocation mechanism, comprising:

an interface for receiving customizations; and

a software module coupled to the interface for building an invocation mechanism based on the customization specifications and storing the invocation mechanism in a database, wherein the invocation mechanism is invokable by the database for accessing data external to the database.

42. The system of claim 41, wherein the invocation mechanism is dynamically generated.

43. The system of claim 41, wherein the invocation mechanism further comprises at least one of the group consisting of:

a UDF, a table function, a virtual table, a stored procedure, a trigger, a query statement, and a federated table, and an equivalent of any of the foregoing.

44. The system of claim 41, further comprising means for invoking the invocation mechanism from a database.

45. The system of claim 41, further comprising means for converting data accessed by the invocation mechanism into a format understood by the database.

46. The system of claim 41, wherein the interface further comprising a graphical user interface for receiving function customization specifications.

47. The system of claim 41, wherein the customization specifications further comprise specification of a relational format for nonrelational data accessed by the customized function.

48. The system of claim 41, wherein the interface further comprises means for previewing nonrelational data in relational format based on customization specifications.