SQLSERVER-DB Connector

The SQLSERVER-DB connector can access the SQL Server database by either calling stored procedures (SP) or executing prepared statements. The following options determine which access method is used:

Stored Procedures (SP): Set the --executable option to the name and location of the stored procedure, or
Prepared Statements: Set the --query option to the SQL statement.

Stored procedure fetch mode

If you are using the stored procedures access method, the SQLSERVER-DB connector provides you with three different modes for fetching result-set data. Set the --result-set-fetch-mode option to one of the following three values:

Default: the connector uses default algorithms provided by the SQL Server for extracting result set metadata (default).
Advanced: the connector executes the stored procedure or function and processes the result set to generate the metadata.
None: the connector generates result sets metadata based on procedure or function parameters without invoking the stored procedures or functions.

Prerequisites

Install the OpenLegacy Assembly on the SQLSERVER-DB server (only if you use stored parameters and --result-set-fetch-mode is set to advanced).

To learn about the OpenLegacy Assembly and how to install it, see: Install OpenLegacy Assembly on the SQL Server.

Properties

--query (required for prepared-statement DB access)

SQL prepared statement

--query "SELECT * FROM ACCOUNTS WHERE ID = ?"

--operation-name (required for prepared-statement DB access)

The name of the generated query-based asset

--operation-name mySpecialName

--executable (required for stored procedure DB access)

Database path to a stored procedure or function

--executable DEMO.BANK_EXAMPLE.GET_ACCOUNT_BY_ID

--result-set-fetch-mode (for stored procedure DB access)

Determine how the result sets are fetched from the SQL Server

DEFAULT: The connector runs the default MS SQL Server algorithms for extracting result set metadata (default). For stored procedures the connector executes sys.dm_exec_describe_first_result_set_for_object. For functions, the connector refers to the ROUTINE_COLUMNS table.
ADVANCED: the connector executes the stored procedure or function and processes the result set metadata. By default, the advanced mode runs the stored procedure or function with mock parameters, but you can run a custom query, see advanced-result-set-query below.

The mode requires the installation of the OpenLegacy assembly on the SQLSERVER-DB server (see.)

❗️
The advanced mode is not recommended if the business logic of the procedure or function creates, updates, or deletes a record in your database.
NONE: The connector generates result-set metadata based on procedure or function parameters without invoking the stored procedures or functions. This mode might not get the best results when the result-set structure is not described in the SP declarations.

--result-set-fetch-mode advanced

--advanced-result-set-query (for stored procedure DB access)

You can use the advanced result set query only when you set the --result-set-fetch-mode to advanced.

The default behavior of the advanced result fetch mode is to execute the stored procedure with mock parameters automatically. However, the mock parameters may not produce the result set you want. To fulfill that objective, you can specify in --advanced-result-set-query a call to the stored procedure with different parameters.

For example, if the stored procedure is:

CREATE PROCEDURE TEST
@val int
AS
BEGIN
  IF @val > 0
  BEGIN
    SELECT * FROM TABLE_1 WHERE id = @val;
    SELECT * FROM TABLE_2 WHERE id = @val;
  END;
END;

The connector executes it with mock parameters as follows:

DECLARE @val int;
SET @val = 0;
EXEC TEST @val = @val;

Because, in the above example, the default mock parameters will not produce any result set, you can set the --advanced-result-set-query to the following:

EXEC TEST @val = 1;

--combine-result-sets (for stored procedure DB access)

You can use the advanced result set query only when you set the --result-set-fetch-mode to advanced.

When a stored procedure returns multiple result sets with the same structure, the connector will generate duplicate entities. To avoid duplicates, set --combine-result-set to true, and the connector will combine sequential and identical result sets into one result set.

--combine-result-set true

--url

The JDBC url

--url jdbc:sqlserver://52.206.218.139:1450;databaseName=demo_db

--username

The database user

--username demo

--password

The database password

--password 12345

--query-timeout

The query timeout (in milliseconds) of callable statements for stored procedures.

--query-timeout 5000

--pool-max-size

Thread pool maximal size

--pool-max-size 5

Examples

ol test connection

[module folder]> ol test connection --url jdbc:sqlserver://52.206.218.139:1450;databaseName=demo_db --username sa --password Open1egacy! --query-timeout 3000 --pool-max-size 5

[module folder]$ ol test connection --url jdbc:sqlserver://52.206.218.139:1450;databaseName=demo_db --username sa --password Open1egacy! --query-timeout 3000 --pool-max-size 5

ol add - prepared statement

[module folder]> ol add --query "SELECT * FROM ACCOUNTS WHERE ID = ?" --operation-name account-by-id

[module folder]$ ol add --query "SELECT * FROM ACCOUNTS WHERE ID = ?" --operation-name account-by-id

ol add - SP default result-set fetch mode

[module folder]> ol add --executable demo_db.dbo.proc_get_employee_by_id

[module folder]$ ol add --executable demo_db.dbo.proc_get_employee_by_id

ol add - SP advanced result-set fetch mode

[module folder]> ol add --executable demo_db.dbo.proc_get_employee_by_id --result-set-fetch-mode advanced --advanced-result-set-query "EXEC proc_get_employee_by_id @val = 1;" --combine-result-sets true

[module folder]$ ol add --executable demo_db.dbo.proc_get_employee_by_id --result-set-fetch-mode advanced --advanced-result-set-query "EXEC proc_get_employee_by_id @val = 1;" --combine-result-sets true

Install OpenLegacy Assembly on SQLSERVER-DB server

The OpenLegacy Assembly is a CLR procedure written in C#. The procedure objective is to execute the SQL query and processes the result set metadata.

The installation of the OpenLegacy assembly is required only when the --result-set-fetch-mode is set to advanced (see Stored procedure fetch mode).

Copy the source code below to a new file and name the file OpenLegacy.cs.

/**
 * To avoid unexpected issues, DO NOT edit this source
 */
  
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
  
public class OpenLegacy {
     
    public static void DescribeResultSets(SqlString exp) {
        SqlPipe pipe = SqlContext.Pipe;
        if (exp == null) {
            pipe.Send("Expression is null");
            return;
        }
        SqlMetaData[] columns = new SqlMetaData[7];
        columns[0] = new SqlMetaData("result_set", SqlDbType.Int);
        columns[1] = new SqlMetaData("column_name", SqlDbType.NVarChar, 128);
        columns[2] = new SqlMetaData("column_type", SqlDbType.NVarChar, 128);
        columns[3] = new SqlMetaData("precision", SqlDbType.Int);
        columns[4] = new SqlMetaData("scale", SqlDbType.Int);
        columns[5] = new SqlMetaData("size", SqlDbType.Int);
        columns[6] = new SqlMetaData("hidden", SqlDbType.Bit);
 
        using (var connection = new SqlConnection("context connection=true")) {
            connection.Open();
            bool sentStart = false;
            try {
                using (var command = new SqlCommand(exp.ToString(), connection)) {
                    using (var reader = command.ExecuteReader()) {
                        SqlDataRecord rec = new SqlDataRecord(columns);
                        var i = 0;
                        do {
                            var schemaTable = reader.GetSchemaTable();
                            if (schemaTable == null) {
                                pipe.Send("Schema table is null");
                                return;
                            }
                             
                            foreach (DataRow row in schemaTable.Rows) {
                                rec.SetSqlInt32(0, i);
                                rec.SetSqlString(1, row["ColumnName"].ToString());
                                rec.SetSqlString(2, row["DataTypeName"].ToString());
                                rec.SetSqlInt32(3, Int32.Parse(row["NumericPrecision"].ToString()));
                                rec.SetSqlInt32(4, Int32.Parse(row["NumericScale"].ToString()));
                                rec.SetSqlInt32(5, Int32.Parse(row["ColumnSize"].ToString()));
 
                                bool hidden;
                                Boolean.TryParse(row["IsHidden"].ToString(), out hidden);
                                rec.SetSqlBoolean(6, hidden);
 
                                if (!sentStart) {
                                    sentStart = true;
                                    pipe.SendResultsStart(rec);
                                }
 
                                pipe.SendResultsRow(rec);
                            }
                            i++;
                        } while (reader.NextResult());
 
                        pipe.SendResultsEnd();
                        reader.Close();
                    }
                }
            }
            catch (Exception e) {
                if (sentStart) {
                    pipe.SendResultsEnd();
                }
                pipe.Send(e.Message);
                throw e;
            } finally {
                connection.Close();   
            }
        }
    }
}

Compile the source code with a C# compiler .

temp> csc /target:library OpenLegacy.cs

temp$ csc /target:library OpenLegacy.cs

The openlegacy.dll file is created from the compiled code.

Generate an SHA-512 for the openlegacy.dll file.
Convert openlegacy.dll to a hexadecimal file.
In the SQL query code below, replace:
- {OpenLegacy.dll hash} with the hash you created in step 3.
- {OpenLegacy.dll hex} with the HEX text you created in step 4.

Execute the SQL query:

EXEC sp_configure @configname = 'clr enabled', @configvalue = '1';
RECONFIGURE;
 
DECLARE @hash BINARY(64);
SELECT @hash = CAST( 0x{OpenLegacy.dll.hash} AS BINARY(64) );
exec sp_add_trusted_assembly @hash = @hash;
CREATE ASSEMBLY OpenLegacy FROM 0x{OpenLegacy.dll.hex};
 
CREATE PROCEDURE ol_describe_result_sets
@exp nvarchar(1024)
AS EXTERNAL NAME OpenLegacy.OpenLegacy.DescribeResultSets;

The query enables CLR execution, adds the OpenLegacy assembly, and creates the CLR procedure.

Run a stored procedure with a cursor parameter

OpenLegacy does not support procedures with CURSOR parameters. This is a limitation caused by the JDBC driver. To bypass this limitation, the connector, upon detecting that the procedure has at least one CURSOR parameter, generates an empty wrapper template file. You can then manually fill in the cursor procedure details in the template. Once you do that, you can import the wrapped and updated procedure to the database.

Try to add the stored procedure with the cursor parameter.

temp> ol add --executable demo_db.dbo.proc_get_employees_cursor

temp$ ol add --executable demo_db.dbo.proc_get_employees_cursor

The CLI displays the following message:

This executable must be wrapped. Wrapper script was saved to demo_db.dbo.proc_get_employes_cursor_wrapper.sql

Go to the location above and open the file.

/*
    Wrapper for proc_get_employees_cursor was generated due to cursor variables, not supported by JDBC.
    Add it to your demo_db database and generate entity.
    Please, follow comments provided below.
*/
CREATE PROCEDURE dbo.proc_get_employees_cursor_wrapper
AS
BEGIN
    SET NOCOUNT ON;
    /*
        Declare temporary table "@table0" with fields, that matches "@cursor0".
        I.e. DECLARE @table0 TABLE (int id);
    */
    /*
        Declare scalar variables, that matches "@table0".
        I.e. DECLARE @table0_id int;
    */
    DECLARE @cursor0 CURSOR;
    EXEC dbo.proc_get_employees_cursor @cur = @cursor0 OUTPUT;
    WHILE 1 = 1
    BEGIN
        /*
            Fetch cursor into variables assigned with "@table0"
            I.e. FETCH NEXT FROM @cursor0 INTO @table0_id;
        */
        FETCH NEXT FROM @cursor0;
        IF (@@FETCH_STATUS != 0)
            BREAK;
        /*
            Insert into "@table0" values from variables fetched before
            I.e. INSERT INTO @table0 VALUES (@table0_id);
        */
    END;
    /*
        Select all from "@table0"
        I.e. SELECT * FROM @table0;
    */
    CLOSE @cursor0;
    DEALLOCATE @cursor0;
END;

Update the procedure template according to the instructions placed in the comments.

CREATE PROCEDURE dbo.proc_get_employees_cursor_wrapper
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @id int;
    DECLARE @first_name varchar(255);
    DECLARE @last_name varchar(255);
    DECLARE @email varchar(255);
    DECLARE @birth_date datetime;
    DECLARE @comission_pct float;
    DECLARE @cursorTable TABLE (id int, first_name varchar(255), last_name varchar(255), email varchar(255), birth_date datetime, comission_pct float);
 
    DECLARE @cursor0 CURSOR;
    EXEC demo_db.dbo.proc_get_employees_cursor @cur = @cursor0 OUTPUT;
    WHILE 1 = 1
    BEGIN
        FETCH NEXT FROM @cursor0 INTO @id, @first_name, @last_name, @email, @birth_date, @comission_pct;       
        IF (@@FETCH_STATUS != 0)
            BREAK;
        INSERT INTO @cursorTable VALUES (@id, @first_name, @last_name, @email, @birth_date, @comission_pct);       
    END;
    SELECT * FROM @cursorTable;
    CLOSE @cursor0;
    DEALLOCATE @cursor0;
END;

Import the procedure to the database.

Now you can add the asset for the wrapped cursor procedure.

temp> ol add --executable demo_db.dbo.proc_get_employees_cursor_wrapper

temp$ ol add --executable demo_db.dbo.proc_get_employees_cursor_wrapper

Stored procedure fetch mode

Prerequisites

Properties

❗️The advanced mode is not recommended if the business logic of the procedure or function creates, updates, or deletes a record in your database.

Examples

ol test connection

ol add - prepared statement

ol add - SP default result-set fetch mode

ol add - SP advanced result-set fetch mode

Install OpenLegacy Assembly on SQLSERVER-DB server

Run a stored procedure with a cursor parameter

❗️
The advanced mode is not recommended if the business logic of the procedure or function creates, updates, or deletes a record in your database.