A method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW

Abstract

The application relates to the technical field of databases, and provides a method for realizing transparent cross-database calling of a remote SQL Server stored procedure based on TDS_FDW, which comprises the following steps: identifying a four-part name in a stored procedure calling statement executed by a user; if the first field of the four-part name is a registered external server name, creating a calling operator node carrying a remote server identifier; acquiring parameter metadata of a remote stored procedure, and creating a mapping stored procedure with the same parameter signature as the remote stored procedure according to the parameter metadata; establishing a TDS protocol connection between the database and the remote SQL Server, constructing an execution statement of the remote SQL Server according to the parameter metadata, and sending the remote SQL Server for execution; receiving an execution result set returned by the remote SQL Server and converting the execution result set into a heap tuple recognizable by a database kernel; and performing directional output of the heap tuple according to the syntax of the calling statement. The application can provide an efficient, low-cost and progressive transition scheme for database localization migration.

Description

Technical Field

[0001] This invention relates to the field of database technology, and in particular to a method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW. Background Technology

[0002] Domestic database substitution has become a core task in enterprise IT infrastructure development. Over long-term operations, enterprises have built numerous stored procedures (such as financial accounting, inventory calculation, and report generation) based on SQL Server, carrying core business logic. These stored procedures, validated over time, are critical business assets of the enterprise's information systems. For example, openGauss, as an open-source relational database, provides a SQL Server compatible mode (MSSQL_FORMAT), offering a solid syntactic foundation for migration. However, stored procedure migration involves logical restructuring, performance tuning, and architectural adaptation, which cannot be completed in one go. Therefore, during the migration transition period, some openGauss business stored procedures will inevitably need to call remote SQL Server stored procedures that have not yet been migrated, obtain their result sets, and use them for subsequent processing, achieving a gradual transition of operation while migrating.

[0003] Existing technologies have significant shortcomings in addressing the aforementioned cross-database stored procedure call requirements:

[0004] First, existing FDW technology only supports read and write operations on remote tables, and its API does not cover stored procedure calls. Although openGauss's postgres_fdw can call remote openGauss-based stored procedures via dblink, this mechanism is based on the libpq protocol and cannot directly communicate with SQL Server. Second, SQL Server uses the TDS protocol, and its stored procedure naming parameters, OUTPUT / INOUT parameter mechanism, metadata query method (relying on system tables such as sys.procedures and sys.parameters), and multi-result set return format are fundamentally different from openGauss. These differences at both the protocol and application layers prevent existing solutions from being extended to SQL Server. Third, under current conditions, developers must handle low-level details such as TDS connections, type mapping, and result set conversions themselves, resulting in high development costs, difficult maintenance, and an inability to reuse openGauss's SQL execution framework and optimizer capabilities, leading to low integration.

[0005] US Patent No. 7739296B2 discloses a method for virtualizing relational stored procedures in a non-native relational database system. This method achieves virtualized calls to remote heterogeneous database stored procedures by automatically generating local stored procedures containing connection strings, call statements, and data type conversion code. However, this method relies on static code generation and the JDBC generic interface. It lacks underlying optimization for SQL Server's TDS protocol, multiple result sets, and OUTPUT / INOUT parameter mechanisms, and it lacks deep integration with database kernels (such as FDW), resulting in insufficient real-time performance and protocol compatibility.

[0006] Therefore, how to provide a method for transparently calling remote SQL Server stored procedures across databases, allowing users to complete the call using standard SQL syntax without having to worry about details such as underlying protocols, parameter conversions, and type mappings, has become an urgent technical problem to be solved. Summary of the Invention

[0007] In view of this, in order to overcome the shortcomings of the prior art, the present invention aims to provide a method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW.

[0008] This invention provides a method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW, the method comprising:

[0009] Step S1: Identify the four-segment name in the stored procedure call statement executed by the user. If the first field of the four-segment name is a registered external server name, create a call operator node carrying the remote server identifier.

[0010] Step S2: When the calling operator node carries the remote server identifier, obtain the parameter metadata of the remote stored procedure, and create a mapped stored procedure with the same parameter signature as the remote stored procedure locally based on the parameter metadata;

[0011] Step S3: Establish a TDS protocol connection with the remote SQL Server, construct the execution statement for the remote SQL Server based on the parameter metadata, and send it to the remote SQL Server for execution;

[0012] Step S4: Receive the execution result set returned by the remote SQL Server and convert it into a heap tuple that the database kernel can recognize. Then, output the heap tuple according to the syntax of the calling statement.

[0013] Optionally, in the method of transparently calling remote SQL Server stored procedures across databases based on TDS_FDW of the present invention, in step S1, the four-segment name consists of the server name, database name, schema name, and stored procedure name. When the syntax parser detects from the stored procedure call statement input by the user that the name list length is four and the first field is an existing external server name, a call operator node carrying the remote server identifier is created. The remote server name, remote database name, remote schema name, and remote stored procedure name corresponding to the first field are stored in the call operator node.

[0014] Optionally, in the method of realizing transparent cross-database invocation of remote SQL Server stored procedures based on TDS_FDW of the present invention, in step S2, the parameter metadata of the remote stored procedure is obtained in the following manner: the semantic analyzer calls the metadata query interface registered by TDS_FDW, and obtains the parameter metadata of the target stored procedure from the remote SQL Server through the TDS protocol. The parameter metadata includes parameter name, parameter type and whether it is an output parameter.

[0015] Optionally, in the method of transparently calling remote SQL Server stored procedures across databases based on TDS_FDW of the present invention, in step S2, a mapped stored procedure with the same parameter signature as the remote stored procedure is created locally according to the parameter metadata in the following manner: a locally unique schema name is generated based on the identification information of the external server object, a proxy stored procedure is created under the schema name, the parameter list of the proxy stored procedure is generated by mapping the remote parameter type in the parameter metadata to the local database type, the function body of the proxy stored procedure is a placeholder implementation, used only to provide the type signature, and the proxy stored procedures corresponding to different remote servers and different remote schemas are isolated through the unique schema name.

[0016] Optionally, in the method of realizing transparent cross-database invocation of remote SQL Server stored procedures based on TDS_FDW of the present invention, in step S3, the connection configuration information of the remote SQL Server is obtained through the remote execution interface registered by TDS_FDW. The connection configuration information includes server address, port, database name, username and password. The TDS protocol client library environment is initialized and a TDS protocol session connection with the remote SQL Server is established.

[0017] Optionally, in the method of transparently calling remote SQL Server stored procedures across databases based on TDS_FDW of the present invention, in step S3, the execution statement of the remote SQL Server is constructed according to the parameter metadata and sent to the remote SQL Server for execution in the following manner: the remote schema name and the remote stored procedure name are parsed from the calling operator node; the metadata query interface is called to obtain the real parameter name and output parameter flag of the target stored procedure; all parameters are traversed and differentiated concatenation is performed according to the parameter type; for output parameters, output keywords are concatenated; for input and output parameters, variable declaration and assignment statements are concatenated first and then output keywords are concatenated; for ordinary input parameters, the type conversion function is called to convert the parameter value of the local database into a string representation form compatible with SQL Server; and the complete stored procedure call command is generated by concatenation.

[0018] Optionally, in the method of transparently calling remote SQL Server stored procedures across databases based on TDS_FDW of the present invention, step S3, for ordinary input parameters, calls a type conversion function to convert the parameter value of the local database into a SQL Server-compatible string representation, including: calling the type conversion function, performing differential conversion according to the data type identifier of the local database parameter, adding a Unicode prefix and quotation marks for string types and escaping the inner quotation marks, converting the logical true value of the local database to 1 and the logical false value to 0 for boolean types, and using the local database output function to convert numeric types and date / time types to strings and adding quotation marks.

[0019] Optionally, in the method of transparently calling remote SQL Server stored procedures across databases based on TDS_FDW of the present invention, in step S4, the execution result set is converted into a heap tuple recognizable by the database kernel in the following manner: calling the column metadata conversion function, traversing each column of the result set returned by SQL Server, obtaining the column name and SQL Server type code, converting the SQL Server type code of each column into the corresponding local database data type identifier according to the predefined type mapping relationship, the type mapping relationship includes integer type, floating point type, character type, date and time type, binary type and numeric type, generating a heap tuple recognizable by the database kernel after the conversion, traversing each data row of the result set, obtaining the original data and data length for each column in the row through the data access interface of the TDS protocol client library and judging the null value status, converting non-null data into C strings, and after all columns are converted, calling the tuple construction function of the database kernel to construct the heap tuple, and storing the heap tuple into the tuple table slot.

[0020] Optionally, in the method of transparently calling remote SQL Server stored procedures across databases based on TDS_FDW of the present invention, in step S4, when the stored procedure of the remote SQL Server returns multiple result sets, the remote execution interface processes each result set in a loop through the result set traversal function, independently performs the conversion of column type information to tuple descriptors and data rows to heap tuples for each result set, generates a corresponding tuple descriptor for each result set independently and performs row-by-row conversion independently, and outputs the heap tuples of each result set sequentially through the receiver interface.

[0021] Optionally, in the method of transparently calling remote SQL Server stored procedures across databases based on TDS_FDW of the present invention, in step S4, when the calling statement is an independent call, the heap tuple is returned to the client through the receiver interface; when the calling statement is an insert or execution nested call, the receiver interface is pointed to the insert executor of the local target table, and each row of heap tuples returned by the remote execution interface is automatically written to the local target table through the insert executor.

[0022] The method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW of the present invention has the following beneficial technical effects:

[0023] Based on the TDS_FDW extension, a transparent bridge for remote stored procedure invocation is built. Through standardized extensions of the FDW callback interface, automatic bridging and conversion of heterogeneous type systems, automatic discovery of remote parameter metadata and dynamic generation of local proxy stored procedures, and a multi-result set streaming processing mechanism, users can transparently invoke remote SQL Server stored procedures and obtain their result sets using fully standard local SQL syntax. This completely shields the underlying technical complexities such as TDS protocol differences, parameter passing mechanisms, and type mapping conversions, achieving efficient streaming return and flexible targeted output of multiple result sets. It effectively solves the technical challenges of cross-database invocation of heterogeneous stored procedures during the transition period of database localization migration, significantly reducing migration costs and development complexity. It provides an efficient, secure, and low-risk progressive technical support solution for the smooth migration of existing enterprise business systems. Attached Figure Description

[0024] To more clearly illustrate the technical solutions of the embodiments of the present invention, the drawings used in the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0025] Figure 1This is a flowchart illustrating a method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to an exemplary embodiment 1 of the present invention.

[0026] Figure 2 This is a flowchart illustrating the method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to an exemplary embodiment 1 of the present invention, which parses a four-segment name and creates a calling operator node.

[0027] Figure 3 This is a flowchart illustrating the method for constructing and sending remote stored procedure execution statements based on TDS_FDW to achieve transparent cross-database invocation of remote SQL Server stored procedures according to an exemplary embodiment 1 of the present invention.

[0028] Figure 4 This is a flowchart illustrating the process of converting ordinary input parameter types in the method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to an exemplary embodiment 1 of the present invention.

[0029] Figure 5 This is a flowchart illustrating the result set redirection of a method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to an exemplary embodiment 1 of the present invention.

[0030] Figure 6 This is a schematic diagram illustrating the architecture of a method for transparently invoking remote SQL Server stored procedures across databases based on TDS_FDW, according to Exemplary Embodiment 4 of the present invention. Detailed Implementation

[0031] The embodiments of the present invention will now be described in detail with reference to the accompanying drawings.

[0032] It should be noted that, in the absence of conflict, the following embodiments and features can be combined with each other; and, based on the embodiments of this disclosure, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of this disclosure.

[0033] It should be noted that various aspects of embodiments within the scope of the appended claims are described below. It will be apparent that the aspects described herein can be embodied in a wide variety of forms, and any particular structure and / or function described herein is merely illustrative. Based on this disclosure, those skilled in the art will understand that one aspect described herein can be implemented independently of any other aspect, and two or more of these aspects can be combined in various ways. For example, any number of aspects set forth herein can be used to implement the device and / or practice the method. Additionally, this device and / or method can be implemented using structures and / or functionalities other than one or more of the aspects set forth herein.

[0034] Example 1

[0035] Exemplary embodiment 1 of the present invention provides a method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW. Figure 1 This is a flowchart illustrating a method for transparently invoking remote SQL Server stored procedures across databases based on TDS_FDW according to an exemplary embodiment 1 of the present invention. Figure 1 As shown, in this embodiment, the method of the present invention is implemented in the following manner:

[0036] Step S1: Identify the four-segment name in the stored procedure call statement executed by the user. If the first field of the four-segment name is a registered external server name, create a call operator node carrying the remote server identifier.

[0037] Figure 2 To illustrate the flowchart of the method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to Exemplary Embodiment 1 of the present invention, which parses a four-segment name and creates a calling operator node, as follows: Figure 2 As shown in this embodiment, the four-segment name consists of the server name, database name, schema name, and stored procedure name. When the parser detects from the stored procedure call statement input by the user that the name list length is four and the first field is an existing external server name, it creates a call operator node carrying the remote server identifier. The remote server name, remote database name, remote schema name, and remote stored procedure name corresponding to the first field are stored in the call operator node.

[0038] By accurately identifying the four-part call statement through the syntax parser and fully storing the remote server name, database name, schema name, and stored procedure name in the call operator node, it is possible to ensure that the remote call intent is accurately captured, providing complete and structured context information for subsequent semantic analysis, metadata retrieval, and remote execution, and avoiding call errors caused by name resolution ambiguity.

[0039] Step S2: When the calling operator node carries the remote server identifier, obtain the parameter metadata of the remote stored procedure, and create a mapped stored procedure with the same parameter signature as the remote stored procedure locally based on the parameter metadata.

[0040] In this embodiment, the parameter metadata of the remote stored procedure is obtained as follows: The semantic analyzer calls the metadata query interface registered by TDS_FDW to obtain the parameter metadata of the target stored procedure from the remote SQL Server via the TDS protocol. The parameter metadata includes the parameter name, parameter type, and whether it is an output parameter. This enables automatic discovery of remote stored procedure parameter definitions, eliminating the need for users to manually predefine parameter structures, ensuring the accuracy and real-time nature of parameter information, and providing a reliable data foundation for the automatic generation of locally mapped stored procedures.

[0041] In this embodiment, a locally unique schema name is generated based on the identification information of the external server object. A proxy stored procedure is created under this schema name. The parameter list of the proxy stored procedure is generated by mapping the remote parameter type in the parameter metadata to the local database type. The function body of the proxy stored procedure is a placeholder implementation, used only to provide type signature. Proxy stored procedures corresponding to different remote servers and different remote schemas are isolated through this unique schema name, ensuring that proxy stored procedures corresponding to different remote servers and different remote schemas do not conflict with each other, avoiding object naming pollution. At the same time, the proxy stored procedure, as a type signature, can participate in local syntax verification and query optimization, improving maintainability and scalability.

[0042] Step S3: Establish a TDS protocol connection with the remote SQL Server, construct the execution statement for the remote SQL Server based on the parameter metadata, and send it to the remote SQL Server for execution.

[0043] In this embodiment, the connection configuration information of the remote SQL Server is obtained through the remote execution interface registered with TDS_FDW. This connection configuration information includes the server address, port, database name, username, and password. The TDS protocol client library environment is initialized, and a TDS protocol session connection is established with the remote SQL Server. This automates the establishment of the communication link with the remote SQL Server. The connection process is completely transparent to the user; the user does not need to manually configure the underlying communication parameters, simplifying the operation process and improving the availability and reliability of cross-database calls.

[0044] Figure 3 This is a flowchart illustrating the method for constructing and sending remote stored procedure execution statements based on TDS_FDW to achieve transparent cross-database invocation of remote SQL Server stored procedures according to Exemplary Embodiment 1 of the present invention. Figure 3As shown, as an optional example, this embodiment constructs the execution statement of the remote SQL Server based on the parameter metadata and sends it to the remote SQL Server for execution in the following manner: The remote schema name and remote stored procedure name are parsed from the calling operator node; the metadata query interface is called to obtain the real parameter name and output parameter flag of the target stored procedure; all parameters are traversed and differentiated according to parameter type; for output parameters, output keywords are concatenated; for input and output parameters, variable declaration and assignment statements are concatenated first, and then output keywords are concatenated; for ordinary input parameters, a type conversion function is called to convert the parameter value of the local database into a SQL Server-compatible string representation; and the complete stored procedure call command is generated by concatenating the parameters.

[0045] By parsing the remote schema name and stored procedure name from the calling operator node, obtaining the actual parameter name and output parameter flag, and performing differentiated concatenation processing based on three types of parameters: output parameters, input / output parameters, and ordinary input parameters, it is possible to construct syntactically correct remote stored procedure call commands. It fully supports the OUTPUT, INOUT, and ordinary input parameter mechanisms of SQL Server stored procedures, ensuring the semantic accuracy and execution success rate of the call commands.

[0046] This embodiment uses a type conversion function to perform differentiated conversion based on the data type identifier of the local database parameters. This can accurately solve the differences in type systems between heterogeneous databases, ensuring that the parameter values ​​of various data types are semantically intact and correctly formatted during cross-database transmission, and effectively avoiding execution errors caused by type mismatch.

[0047] Figure 4 This is a flowchart illustrating the process of converting ordinary input parameter types in the method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to Exemplary Embodiment 1 of the present invention. Figure 4 As shown, for ordinary input parameters, the type conversion function is called to convert the parameter value of the local database into a SQL Server-compatible string representation. This includes: calling the type conversion function, performing differential conversion based on the data type identifier of the local database parameter, adding a Unicode prefix and quotation marks for string types and escaping the inner quotation marks, converting the logical true value of the local database to 1 and the logical false value to 0 for boolean types, and using the local database output function to convert numeric and date / time types to strings and adding quotation marks.

[0048] Step S4: Receive the execution result set returned by the remote SQL Server and convert it into a heap tuple that the database kernel can recognize. Then, output the heap tuple according to the syntax of the calling statement.

[0049] This embodiment uses a column metadata conversion function to traverse each column of the result set to obtain the column name and SQL Server type code. Based on a predefined type mapping relationship, it converts the data into a local database data type identifier, generates a tuple descriptor, and then obtains the original data and length row by row and column by column through the data access interface of the TDS protocol client library. After null value judgment, it is converted into a C string and the database kernel tuple construction function is called to construct a heap tuple. This achieves standardized and fully automated conversion from SQL Server result set to database kernel, and completely preserves the type information and null value semantics of the original data.

[0050] Specifically, this embodiment converts the execution result set into a heap tuple recognizable by the database kernel in the following manner: The column metadata conversion function is called to traverse each column of the result set returned by SQL Server, obtain the column name and SQL Server type code, and convert the SQL Server type code of each column into the corresponding local database data type identifier according to the predefined type mapping relationship. The type mapping relationship includes integer type, floating-point type, character type, date and time type, binary type, and numeric type. After the conversion is completed, a heap tuple recognizable by the database kernel is generated. Each data row of the result set is traversed, and for each column in the row, the original data and data length are obtained through the data access interface of the TDS protocol client library, and the null value status is determined. Non-null data is converted into C strings. After all columns are converted, the database kernel's tuple construction function is called to construct the heap tuple, and the heap tuple is stored in the tuple table slot.

[0051] In this embodiment, when a remote SQL Server stored procedure returns multiple result sets, the remote execution interface processes each result set in a loop using a result set traversal function. It independently performs the conversion from column type information to tuple descriptors and from data rows to heap tuples for each result set. Each result set independently generates its corresponding tuple descriptor and performs row-by-row conversion. The heap tuples of each result set are output sequentially through the receiver interface. This fully supports the multi-result set return feature of SQL Server stored procedures, ensuring that each result set is correctly parsed and output independently, avoiding result set loss or confusion, and ensuring compatibility with complex return scenarios of SQL Server stored procedures.

[0052] Figure 5 This is a flowchart illustrating the result set redirection of the method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to Exemplary Embodiment 1 of the present invention. Figure 5As shown in this embodiment, when the call statement is an independent call, the heap tuple is returned to the client through the receiver interface. When the call statement is an insert / execute nested call, the receiver interface is pointed to the insert executor of the local target table. Each row of heap tuple returned by the remote execution interface is automatically written to the local target table through the insert executor. It can seamlessly adapt to two mainstream stored procedure call scenarios, supporting both real-time viewing of result sets and direct persistence of result sets returned by remote stored procedures to the local target table, meeting the actual needs of data synchronization and business continuity during the migration transition period.

[0053] Example 2

[0054] Exemplary Example 2 of the present invention provides a method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW. In this embodiment, the method of the present invention is further described in detail using the openGauss database as an example.

[0055] openGauss is an open-source relational database management system evolved from the open-source database PostgreSQL. It supports SQL Server compatible mode (MSSQL_FORMAT), in which it supports SQL Server compatible syntax and behavior. Its FDW (Foreign Data Wrapper) mechanism allows access to external data sources through standard interfaces.

[0056] TDS_FDW is an FDW extension plugin for the openGauss database, which uses the FreeTDS DB-Library API to communicate with SQL Server. TDS (Tabular Data Stream) is a proprietary communication protocol of SQL Server, and DB-Library is a C language client library provided by SQL Server.

[0057] Specifically, in this embodiment, the method of the present invention is implemented in the following manner:

[0058] Step 1: Syntax parsing stage, identifying remote stored procedure calls.

[0059] When a user executes a stored procedure call statement in the SQL Server compatibility mode of openGauss, the syntax parser checks whether the length of the name list in the call statement is a four-part name: server.database.schema.procedure. When the length is 4, it checks whether the first field is an existing Foreign Server name. If so, it creates a DolphinCallStmt operator node with a remote_server attribute, where remote_server stores the remote server name, and the subsequent fields store the remote database name, schema name, and stored procedure name respectively.

[0060] Step 2: Semantic analysis phase, automatic discovery of remote metadata and creation of local proxy stored procedures

[0061] In the semantic analysis phase, it checks whether the remote_server attribute of the DolphinCallStmt operator is non-null. If it is non-null, the following sub-steps are executed:

[0062] (1) Obtain the remote server configuration: Obtain the Foreign Server object, user mapping, and FDW type according to the remote_server name, and determine whether the FDW type is TDS_FDW; for servers of the TDS_FDW type, extract the database attribute from the server options as the remote database name;

[0063] (2) Query the remote stored procedure metadata: Call the QueryProcFields callback interface registered by TDS_FDW, connect to the remote SQL Server through DB-Library, execute the metadata query SQL, and obtain the parameter definition information (parameter name, parameter type, whether it is an output parameter) of the stored procedure from the sys.procedures and sys.parameters system tables;

[0064] (3) Create a local mapped stored procedure: Automatically generate a mapped stored procedure locally according to the remote metadata. The Schema name of the mapped stored procedure is generated by the make_tdsfdw_schema function, which concatenates and generates a unique Schema name (such as vb_tdsfdw_pub_<hash>_<schema>) based on information such as the hash value of the server name and URL, username, and remote schema name, ensuring that the mapped stored procedures of different remote servers and different schemas do not conflict. The parameter definition of the mapped stored procedure is converted from the remote parameter metadata, and the function body is a placeholder implementation (RAISEEXCEPTION) because the mapped stored procedure is only used as a type signature and will not be actually executed.

[0065] Step 3: Execution Phase - Remote Stored Procedure Call and Result Set Return

[0066] During the execution phase, when the remote_server of the DolphinCallStmt operator is not null, the ExecDblinkProc callback interface registered by TDS_FDW is called to perform the following sub-steps:

[0067] (1) Establish TDS connection: Obtain the connection configuration of the remote SQL Server (server address, port, database name, username, password, etc.) through tdsGetForeignServerOptionsFromCatalog, initialize the DB-Library environment, create the login structure, and establish a TDS connection with SQL Server;

[0068] (2) Obtain the real parameter name of the remote stored procedure: extract schema and proc from the call name, call tdsQueryProcFields to obtain parameter metadata, parse out the real parameter name (such as @UserName) of each parameter and whether it is an output parameter, and cache it in the RemoteProcParam array;

[0069] (3) Construct the EXEC call statement: Iterate through all parameters and process them differently according to their types:

[0070] For output parameters (OUT): concatenate as "@param_name OUTPUT";

[0071] For input / output parameters (INOUT): First, concatenate the DECLARE / SET statement to declare the SQL Server variable and assign a value, then concatenate "@param_name OUTPUT";

[0072] For regular input parameters: convert the openGauss Datum value to a SQL Server compatible string using tdsConvertPgDatumToMssqlStr, add an N prefix to string type parameters to support Unicode, and concatenate them into the format "@param_name= N'value'".

[0073] (4) Execute stored procedure: First, execute the DECLARE / SET statement with INOUT parameter to initialize SQL Server variables, and then execute the concatenated EXEC statement to call the remote stored procedure;

[0074] (5) Processing Result Sets: The `dbresu1ts` loop processes multiple result sets that the SQL Server stored procedure may return. For each non-empty result set:

[0075] The `tdsGenerateTupleDescFromDBProc` function is called to generate a TupleDesc that the openGauss kernel can recognize from the column metadata (column names, column type codes) of the SQL Server result set. The mapping relationship from SQL Server types to openGauss OIDs includes:

[0076] SYBINT2→INT20ID, SYBINT4→INT4OID, SYBINT8→INT8OID, SYBREAL→FLOAT40ID, SYBFLT8→FLOAT8OID, SVBCHAR / SYBVARCHAR / SYBTEXT→TEXTOID, SYBDATE→TINETINESTAMPOID, SYBNUMERIC→NUMERICOID, etc.;

[0077] The tdsStoreRowFromDBProc function is called to convert each row of raw data from SQL Server into a C string using tdsConvertToCString, and then builds the openGauss HeapTuple using BuildTupleFromCStrings.

[0078] Store the HeapTuple into TupleTableSlot, and stream the result rows to the client or the INSERT executor of the local table through the receiveSlot interface of DestReceiver.

[0079] (6) Clean up resources: Close the DB-Library connection, release the login structure, and clean up the DB-Library environment.

[0080] Step 4: INSERT INTO ... EXEC mode – insert the result set into the local table.

[0081] When a user uses the syntax INSERT INT0 local_table EXEC server.database.schema.procedure, in the stmt→remote_server branch of the main process, the result set returned by tdsExecRemoteProc is inserted into the local table through the tds_insert_resultset_to_local_table interface, realizing the writing of data from the remote stored procedure result set to the local table.

[0082] Example 3

[0083] Exemplary Example 3 of the present invention provides a method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW. In this embodiment, the method of the present invention is further described in detail using the openGauss database as an example.

[0084] In this embodiment, the method of the present invention is implemented in the following manner:

[0085] 1. Syntax parsing stage:

[0086] When a user executes a stored procedure call statement, the syntax parser determines the call name:

[0087] Check if the name list length is 4 (server.database.schema.procedure); if the length is 4, check if the first field is an existing Foreign Server name; if so, create a DolphinCallStmt operator, set remote_server to the server name, and save the remote database name, schema name, stored procedure name, and other fields.

[0088] 2. Semantic analysis stage:

[0089] The semantic analysis phase processes the DolphinCa1lStmt operator, and when remote_server is detected to be non-empty:

[0090] (1) Call checkDblinkProcServer to verify the validity of the remote server and obtain the FDW type. For servers of type TDS_FDW, extract the database attribute from the server options as the remote database name;

[0091] (2) Call the QueryProcFields callback (i.e. tdsQueryProcFields), which is used to: obtain the server connection configuration and initialize the DB-Library environment; establish a TDS connection with SQL Server; construct and execute metadata query SQL (query sys.procedures, sys.parameters, sys.types); parse the returned results and concatenate the parameter definition string (format: "@param1ltype1 0UTPUT,@param2ltype2"); return the parameter definition array and the number of parameters.

[0092] (3) Call make_tdsfdw_schema to generate the local mapping schema name;

[0093] (4) Construct the CREATE OR REPLACE PROCEDURE DDL statement to create a mapped stored procedure locally, with parameter definitions converted from remote parameter metadata;

[0094] (5) Record the strategy information in a local hash table to avoid duplicate creation.

[0095] 3. Execution Phase:

[0096] When the remote_server of the DolphinCallStmt operator is not empty, the ExecDblinkProc callback (i.e., tdsExecRemoteProc) is invoked, and the specific process is as follows:

[0097] (1) Parameter processing:

[0098] The tdsConvertPgDatumToMssqlStr function performs differential conversion based on the OID type of the openGauss parameter as shown in Table 1.

[0099] Table 1

[0100]

[0101] (2) EXEC statement construction:

[0102] DECLARE @out1 INT; SET @out1= <value>-- Initialize INOUT parameters.

[0103] EXEC dbo.proc_name @param1=N'value1', @param2=123, @out1= OUTPUT.

[0104] (3) Result set processing:

[0105] The `tdsGenerateTupleDescFromDBProc` function converts SQL Server result set metadata into TupleDesc. The type mapping table is shown in Table 2 below.

[0106] Table 2

[0107]

[0108] The tdsStoreRowFromDBProc function converts each row of SQL Server data into a HeapTuple:

[0109] Iterate through each column, obtaining the original data and length via dbdata / dbdatlen; handle NULL values ​​(length is 0 or pointer is NULL); call tdsConvertToCString to convert SQL Server data to C string; call BuildTupleFromCStrings to build HeapTuple.

[0110] The result rows are streamed to the client or the INSERT executor of the local table via the receiveSlot interface of DestReceiver.

[0111] (4) INSERT INTO ... EXEC mode

[0112] When using the `INSERT INT0 local_table EXEC server.database.schema.procedure` syntax, the execution engine points `DestReceiver` to the local table's INSERT executor, and each row returned by `tdsExecRemoteProc` is automatically written to the local table.

[0113] 4. Output parameter query:

[0114] The output parameters (OUTPUT) of the stored procedure are implemented through the QueryDblinkProcOutVar callback (i.e., tdsQueryProcOutVar):

[0115] Establish a TDS connection with SQL Server; construct a SELECT statement to query the values ​​of the output variables; obtain the column type code and column name, and map them to the OpenGauss 0ID type; extract the variable values ​​and return them.

[0116] Example 4

[0117] Exemplary embodiment 4 of the present invention provides a method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW. Figure 6 This is a schematic diagram illustrating the architecture of a method for transparently invoking remote SQL Server stored procedures across databases based on TDS_FDW, according to Exemplary Embodiment 4 of the present invention. Figure 6 As shown, the method of this embodiment is implemented in the following architecture:

[0118] User layer: Users send SQL statements (such as INSERT INTO t1 EXEC srv1.db1.dbo.proc1 @p1=1) through gsql clients or applications.

[0119] The OpenGauss kernel layer includes a syntax parser (which recognizes four-segment names and creates the DolphinCallStmt operator), a semantic analyzer (which calls QueryProcFields to retrieve remote metadata and creates local mapping stored procedures), and an execution engine (which calls ExecDblinkProc to execute calls and process the result set).

[0120] TDS_FDW layer: Implements TDS protocol communication with SQL Server through DB-Library (FreeTDS), including metadata query (tdsQueryProcFields), remote execution (tdsExecRemoteProc), type mapping (tdsMapPgTypeToMssqlType / tdsConvertPgDatumToMssqlStr), and result set transformation (tdsGenerateTupleDescFromDBProc / tdsStoreRowFromDBProc).

[0121] SQL Server layer: Remote SQL Server database, executes stored procedures and returns result sets.

[0122] Example 5

[0123] Exemplary Example 5 of the present invention provides a method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW. The method of the present invention will be further described in detail in the following specific examples.

[0124] In this embodiment, a Foreign Server srv_mssql pointing to SQL Server is created using CREATE SERVER; a user mapping is created using CREATE USER MAPPING; and a stored procedure dbo.GetOrderByCustomer exists on SQL Server, accepting one input parameter @customerId(INT) and returning a result set (OrderIdINT, OrderDate DATETIME, Amount DECIMAL). Specifically, the operation is as follows:

[0125] (1) The user executes the following in OpenGauss: The syntax parser recognizes the four-segment name srv_mssql.salesdb.dbo.GetOrderByCustomer, creates the DolphinCallStmt operator, and remote_server = 'srv_mssql';

[0126] (2) The semantic analyzer detects that remote_server is not empty: confirms that srv_mssql is a TDS_FDW type server; calls tdsQueryProcFields to query the remote stored procedure metadata, returning the parameter definition "@customerId|INT"; calls make_tdsfdw_schema to generate the local schema name "vb_tdsfdw_pub_\<hash\> "_dbo"; Create a local mapped stored procedure;

[0127] (3) The execution engine calls tdsExecRemoteProc: establishes a TDS connection; obtains the real parameter name @CustomerId; constructs an EXEC statement: "EXEC dbo.GetOrderByCustomer @CustomerId = 1001"; executes and processes the result set: converts the (OrderId, OrderDate, Amount) columns returned by SQL Server into OpenGauss TupleDesc, and inserts them into the local_orders table row by row through DestReceiver;

[0128] (4) Users can view all order data returned by the remote stored procedure in the local_orders table.

[0129] The process is completely transparent to the user, who does not need to understand the details of the TDS protocol, type mapping, or parameter conversion.

[0130] Through the above description of the embodiments, those skilled in the art can clearly understand that each embodiment can be implemented by means of software plus necessary general-purpose hardware platforms, and of course, it can also be implemented by hardware. Based on this understanding, the above technical solutions, in essence or the part that contributes to the prior art, can be embodied in the form of a software product. This computer software product can be stored in a computer-readable storage medium, such as ROM / RAM, magnetic disk, optical disk, etc., including several instructions to cause a computer device (which may be a personal computer, server, or network device, etc.) to execute the methods of various embodiments or some parts of embodiments.

[0131] The above description is merely a specific embodiment of the present invention, but the scope of protection of the present invention is not limited thereto. Any variations or substitutions that can be easily conceived by those skilled in the art within the technical scope disclosed in the present invention should be included within the scope of protection of the present invention. Therefore, the scope of protection of the present invention should be determined by the scope of the claims.< / value>

Claims

1. A method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW, characterized in that, The method includes: Step S1: Identify the four-segment name in the stored procedure call statement executed by the user. If the first field of the four-segment name is a registered external server name, create a call operator node carrying the remote server identifier. Step S2: When the calling operator node carries the remote server identifier, obtain the parameter metadata of the remote stored procedure, and create a mapped stored procedure with the same parameter signature as the remote stored procedure locally based on the parameter metadata; Step S3: Establish a TDS protocol connection with the remote SQL Server, construct the execution statement for the remote SQL Server based on the parameter metadata, and send it to the remote SQL Server for execution; Step S4: Receive the execution result set returned by the remote SQL Server and convert it into a heap tuple that the database kernel can recognize. Then, output the heap tuple according to the syntax of the calling statement.

2. The method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to claim 1, characterized in that, In step S1, the four-segment name consists of the server name, database name, schema name, and stored procedure name. When the parser detects from the stored procedure call statement entered by the user that the name list is four in length and the first field is an existing external server name, it creates a call operator node carrying the remote server identifier. The remote server name, remote database name, remote schema name, and remote stored procedure name corresponding to the first field are stored in the call operator node.

3. The method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to claim 1, characterized in that, In step S2, the parameter metadata of the remote stored procedure is obtained as follows: The semantic analyzer calls the metadata query interface registered by TDS_FDW and obtains the parameter metadata of the target stored procedure from the remote SQL Server through the TDS protocol. The parameter metadata includes the parameter name, parameter type, and whether it is an output parameter.

4. The method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to claim 1, characterized in that, In step S2, a mapped stored procedure with the same parameter signature as the remote stored procedure is created locally based on the parameter metadata in the following manner: a locally unique schema name is generated based on the identification information of the external server object, a proxy stored procedure is created under this schema name, the parameter list of the proxy stored procedure is generated by mapping the remote parameter type in the parameter metadata to the local database type, and the function body of the proxy stored procedure is a placeholder implementation, which is only used to provide the type signature. Proxy stored procedures corresponding to different remote servers and different remote schemas are isolated through this unique schema name.

5. The method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to claim 1, characterized in that, In step S3, the connection configuration information of the remote SQL Server is obtained through the remote execution interface registered by TDS_FDW. The connection configuration information includes the server address, port, database name, username and password. The TDS protocol client library environment is initialized and a TDS protocol session connection with the remote SQL Server is established.

6. The method for transparently invoking remote SQL Server stored procedures across databases based on TDS_FDW according to claim 1, characterized in that, In step S3, the execution statement for the remote SQL Server is constructed based on the parameter metadata and sent to the remote SQL Server for execution in the following manner: the remote schema name and the remote stored procedure name are parsed from the calling operator node; the metadata query interface is called to obtain the actual parameter name and output parameter flag of the target stored procedure; all parameters are traversed and differentiated according to the parameter type; for output parameters, output keywords are concatenated; for input and output parameters, variable declaration and assignment statements are concatenated first and then output keywords are concatenated; for ordinary input parameters, the type conversion function is called to convert the parameter value of the local database into a SQL Server-compatible string representation; and the complete stored procedure call command is generated by concatenating the parameters.

7. The method for transparently invoking remote SQL Server stored procedures across databases based on TDS_FDW according to claim 6, characterized in that, In step S3, for ordinary input parameters, the type conversion function is called to convert the parameter value of the local database into a SQL Server compatible string representation. This includes: calling the type conversion function, performing differential conversion based on the data type identifier of the local database parameter, adding a Unicode prefix and quotation marks for string types and escaping the inner quotation marks, converting the logical true value of the local database to 1 and the logical false value to 0 for boolean types, and using the local database output function to convert numeric types and date / time types to strings and adding quotation marks.

8. The method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to claim 1, characterized in that, In step S4, the execution result set is converted into a heap tuple recognizable by the database kernel in the following manner: the column metadata conversion function is called to traverse each column of the result set returned by SQL Server, obtain the column name and SQL Server type code, and convert the SQL Server type code of each column into the corresponding local database data type identifier according to the predefined type mapping relationship. The type mapping relationship includes integer type, floating point type, character type, date and time type, binary type and numeric type. After the conversion is completed, a heap tuple recognizable by the database kernel is generated. Each data row of the result set is traversed, and for each column in the row, the original data and data length are obtained through the data access interface of the TDS protocol client library and the null value status is determined. Non-null data is converted into C strings. After all columns are converted, the tuple construction function of the database kernel is called to construct the heap tuple and the heap tuple is stored in the tuple table slot.

9. The method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to claim 8, characterized in that, In step S4, when the stored procedure of the remote SQL Server returns multiple result sets, the remote execution interface processes each result set in a loop through the result set traversal function. For each result set, the conversion of column type information to tuple descriptors and data rows to heap tuples is performed independently. Each result set generates a corresponding tuple descriptor independently and performs row-by-row conversion independently. The heap tuples of each result set are output sequentially through the receiver interface.

10. The method for transparently calling remote SQL Server stored procedures across databases based on TDS_FDW according to claim 1, characterized in that, In step S4, when the call statement is an independent call, the heap tuple is returned to the client through the receiver interface. When the call statement is an insert or execution nested call, the receiver interface is pointed to the insert executor of the local target table. Each row of heap tuple returned by the remote execution interface is automatically written to the local target table through the insert executor.

Images