Method and system for converting message data into relational table format

• US 20030126109A1
• Filed: 01/02/2002
• Published: 07/03/2003
• Est. Priority Date: 01/02/2002
• Status: Abandoned Application

• Current Assignee: ServiceNow Incorporated
• Original Assignee: ServiceNow Incorporated
• Inventors: Couch, Tanya, Mayhew, Debra L.
• Application Number:
  US10/037,659
• Publication Number:
  US 20030126109A1
• Time in Patent Office: Days
• US Class Current: 707/1
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database

Abstract

A method and system for converting messaging data into a relational table format in a database system, wherein the messaging data is within a messaging system is disclosed. The method and system of the present invention includes providing a table function that includes a plurality of table formatting specifications. The method and system of the present invention also includes invoking the table function to access the messaging data, and to convert the messaging data into specific data types according to the table formatting specifications, thereby transforming the messaging data into the relational table format.

Through aspects of the method and system 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.

Claims

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

(a) providing a table function within the database system, wherein the table function includes a plurality of table formatting specifications;

(b) invoking the table function to access the messaging data; and

(c) 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 method of claim 1, wherein the table function invokes at least one messaging function within the database system.

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

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

5. The method of claim 1, wherein the providing step (a) further includes the step of:

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

6. The method of claim 1, wherein the providing step (a) further includes the steps of:

(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 method of claim 6, wherein the specifying step (a2) further includes the steps of:

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

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

8. The method of claim 6, wherein the indicating step (a3) further includes the step of:

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

9. The method of claim 6, wherein the indicating step (a3) further includes the step of:

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

10. The method of claim 1, wherein the providing step (a) further includes the step of:

(a1) providing formatting information about the messaging data.

11. The method of claim 10, wherein the providing step (a4) further includes the steps of:

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

12. The method of claim 11, wherein the converting step (c) further comprising:

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

13. The method of claim 12, wherein the invoking step (c1) further includes:

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

(c1ii) building the parser function ifit does not exist within the database system; and

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

14. The method of claim 10, wherein the providing step (a1) further includes the step of:

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

15. The method of claim 10, wherein the providing step (a) further includes the step of:

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

16. The method 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 method of claim 16, wherein the providing step (a) further includes the step of:

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

18. The method of claim 17, wherein the defining step (a1) further includes the steps of:

(a1i) naming each column; and

(a1ii) designating a data type for each column.

19. The method of claim 18, wherein the defining step (a1) further includes the step of:

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

20. The method of claim 19, wherein the providing step (a) further includes the step of:

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

21. The method of claim 20, wherein the converting step (c) further includes:

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

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

22. The method of claim 1, wherein the providing step (a) further includes the step of:

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

23. The method of claim 22, wherein the invoking step (b) further includes the step of:

(b1) selecting messaging data from the table view.

24. The method of claim 1, wherein the providing step (a) further includes the step of:

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

25. The method of claim 3, wherein the invoking step (b) further includes the step of:

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

26. The method of claim 4 further including the step of (d) populating directly a relational table in the database system with the returned messaging data.

27. 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 table function within the database system, wherein the table function includes a plurality of table formatting specifications;

(b) invoking the table function to access the messaging data; and

(c) 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.

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

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

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

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

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

32. The computer readable medium of claim 27, 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.

33. The computer readable medium of claim 32, 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.

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

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

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

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

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

(a1) providing formatting information about the messaging data.

37. The computer readable medium of claim 36, 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.

38. The computer readable medium of claim 37, wherein the converting step (c) further comprising:

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

39. The computer readable medium of claim 38, wherein the invoking step (c1) further includes:

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

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

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

40. The computer readable medium of claim 36, 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.

41. The computer readable medium of claim 36, 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.

42. The computer readable medium of claim 27, 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.

43. The computer readable medium of claim 42, 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.

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

(a1i) naming each column; and

(a1ii) designating a data type for each column.

45. The computer readable medium of claim 44, 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.

46. The computer readable medium of claim 45, 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.

47. The computer readable medium of claim 46, wherein the converting step (c) further includes:

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

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

48. The computer readable medium of claim 27, 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.

49. The computer readable medium of claim 48, wherein the invoking instruction (b) further includes the instruction for:

(b1) selecting messaging data from the table view.

50. The computer readable medium of claim 27, 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.

51. The computer readable medium of claim 29, wherein the invoking instruction (b) further includes the instruction for:

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

52. The computer readable medium of claim 30 further including the instruction for (d) populating directly a relational table in the database system with the returned messaging data.

53. 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 building a table function, wherein the table function includes a plurality of table formatting specifications; and

means for invoking the table function 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.

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

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

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

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

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

59. The system of claim 57, 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.

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

61. The system of claim 59, 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.

62. The system of claim 61, 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.

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

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

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

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