Relational operations between database tables and application tables
View Patent ↗Disclosed aspects perform a join operation between a database table and second in-memory data of an application may be provided. The operation is expressed as an join query between first data residing in the relational database and second data residing in a memory space of an application. Aspects can include serializing rows of the second data into a character large binary object, generating a composite statement by decoding, using a table function, rows of the character large binary object into columns of associated database data-types. The rows are substrings of the character large binary object. Aspects may include sending the character large binary object and the composite statement to a relational database, executing it, and receiving a result.
1. A computer-implemented method for performing an operation in a relational database, wherein the operation is expressed as a join query between first data residing in the relational database and second data residing in a memory space of an application, the method comprising:
serializing rows of the second data from the application into a character large binary object, wherein the second data are organized in rows with columns of variable lengths, and wherein the columns of the second data are encoded with at least one of:
a variable length encoding multi-byte ASCII;
a variable length encoding UTF-8; and
a variable length encoding UTF-16;
wherein the columns of the second data are padded with space characters up to a maximum column width for a related data type and character set;
generating a composite statement which includes:
a section that decodes rows of the character large binary object into columns of associated database data-types, wherein the rows are substrings of the character large binary object resulting from a table function, and
a section containing text of the join query;
sending the character large binary object and the composite statement to a relational database;
executing the composite statement between a database table and the character large binary object in the relational database; and
receiving a returned result of executing the composite statement to the application.
2. The method according to claim 1 , wherein data values in the character large binary object are represented by a one byte ASCII representation.
3. The method according to claim 1 , wherein data values in the character large binary object are represented by a two byte UCS2 representation.
4. The method according to claim 1 , wherein data values in the character large binary object are represented by a four byte UTF32 representation.
5. The method according to claim 1 , wherein the second data are organized in rows with columns of fixed lengths.
6. The method according to claim 1 , wherein a Null indicator is encoded in the table large binary object.
7. The method according to claim 1 , wherein the table function is a user defined table function written in SQL.
8. The method according to claim 1 , wherein the table function is an external user defined table function written in a programing language which is predetermined.
9. The method according to claim 1 , wherein the table function is an external user defined table function written in any programing language.
10. The method according to claim 1 , wherein a decoding portion of the composite statement is represented as a Common Table Expression.
11. The method according to claim 1 , wherein the second data represent one or more application data tables.
12. The method according to claim 1 , wherein:
the table function is an external user defined table function written in any programing language;
a decoding portion of the composite statement is represented as a Common Table Expression; and
the second data represent one or more application data tables.
13. The method according to claim 1 , wherein:
a Null indicator is encoded in the table large binary object;
the table function is a user defined table function written in SQL;
a decoding portion of the composite statement is represented as a Common Table Expression; and
the second data represent one or more application data tables.
14. The method according to claim 13 , wherein the second data are organized in rows with columns of variable lengths.
15. A system for performing an operation in a relational database, wherein the operation is expressed as a join query between first data residing in the relational database and second data residing in a memory space of an application, the system comprising:
a memory having a set of computer readable computer instructions, and
a processor for executing the set of computer readable instructions, the set of computer readable instructions including:
serializing rows of the second data from the application into a character large binary object, wherein the second data are organized in rows with columns of variable lengths, and wherein the columns of the second data are encoded with at least one of:
a variable length encoding multi-byte ASCII;
a variable length encoding UTF-8; or
a variable length encoding UTF-16;
wherein the columns of the second data are padded with space characters up to a maximum column width for a related data type and character set;
generating a composite statement which includes:
a section that decodes rows of the character large binary object into columns of associated database data-types, wherein the rows are substrings of the character large binary object resulting from a table function, and
a section containing text of the join query;
sending the character large binary object and the composite statement to a relational database;
executing the composite statement between a database table and the character large binary object in the relational database; and
receiving a returned result of executing the composite statement to the application.
16. A computer program product for performing an operation in a relational database, wherein the operation is expressed as a join query between first data residing in the relational database and second data residing in a memory space of an application, the computer program product comprising a computer readable storage medium having program instructions embodied therewith, wherein the computer readable storage medium is not a transitory signal per se, the program instructions executable by a processor to cause the processor to perform a method comprising:
serializing rows of the second data from the application into a character large binary object, wherein the second data are organized in rows with columns of variable lengths, and wherein the columns of the second data are encoded with at least one of:
a variable length encoding multi-byte ASCII;
a variable length encoding UTF-8; or
a variable length encoding UTF-16;
wherein the columns of the second data are padded with space characters up to a maximum column width for a related data type and character set;
generating a composite statement which includes:
a section that decodes rows of the character large binary object into columns of associated database data-types, wherein the rows are substrings of the character large binary object resulting from a table function, and
a section containing text of the join query;
sending the character large binary object and the composite statement to a relational database;
executing the composite statement between a database table and the character large binary object in the relational database; and
receiving a returned result of executing the composite statement to the application.