#Dexterity Creating Large SQL Store Procedures

Today I was asked about how you can use Dexterity to create SQL Stored Procedures when the stored procedure itself exceeds 32,768 characters.

This 32K limit is because the largest datatype supported by Dexterity is the Text field which is limited to 32,768 characters. This is the maximum size of a signed 16 bit integer (-32767 to 32768).

This limit causes issues because it is not possible to work with more than 32K characters at a time in Dexterity.

There are two main methods used to create SQL Stored Procedures using Dexterity:

1. Read a text file into a text variable and then execute it as pass through SQL.
2. Use Dexterity scripts to build up a text variable and then execute it as pass through SQL.

Both of these methods are limited by the fact that Dexterity does not have a datatype larger than 32K. All of Dexterity including the SQL_Execute() and TextFile_ReadText() commands are limited.

[Edit] If you create a text variable as an array, you can passthrough the first element of the text array to the SQL_Execute() command and it will automatically concatenate all of the array elements together for a larger than 32K script.

So, how can I create a SQL stored procedure which exceeds this limit using Dexterity?

Below are methods based on how GP Power Tools achieves this.

1. Create a Dexterity table definition for a TEMP table with Sequence Number field and a 255 character string field. Create an index on the Sequence Number field.
   
2. Populate this TEMP table with the lines of the script you want to create, incrementing the Sequence Number for each line (see below for more information).
3. Once the TEMP table has been populated, use passthrough SQL code to generate the actual SQL stored procedure from the contents of the table.

SQL Code Example

DECLARE @mystmt varchar(max)

SET @mystmt = '' -- initialise 

SELECT @mystmt = @mystmt + rtrim(STRGA255) + ' ' + CHAR(13)
FROM XXXX
ORDER BY SEQNUMBR 

exec (@mystmt)

To get the data into the TEMP table there are two approaches:

• Reading a text file with TextFile_Open() using the TextFile_ReadLine() command to pull in the lines of the file and write them into the table incrementing the Sequence Number for each line. For this to work, ensure that no one line list longer than 255 characters.

or

• Create the script in code as a text field and then use Field_ParseText() to break the text variable into lines and write them into the table. Repeat this process as many times as needed to populate the table with the full script.

Putting this all together into a full Dexterity script to drop an existing procedure if it exists, create the new procedure and grant access to it gives you this script copied from GP Power Tools.

Dexterity Global Function: MBS_Create_Stored_Procedure

function returns boolean OUT_Created;
inout table MBS_Create_Stored_Procedure;
in string IN_DB;
in string IN_Procedure;

local long SQL_connection, status;
local text SQL_Statements;
local text l_text;

{SQL error information}
local long GPS_error_number, SQL_error_number;
local string SQL_error_string, ODBC_error_string;

OUT_Created = false;

pragma(disable warning LiteralStringUsed);

{Connect to the SQL data source.}
status = SQL_Connect(SQL_connection);
if status = 0 then
    {Build SQL statement to use the appropriate database.}
    SQL_Statements = "use "+IN_DB;
    {Execute the SQL statements.}
    status = SQL_Execute(SQL_connection, SQL_Statements);
    if status = 0 then
        {Build the SQL statements.}
		clear SQL_Statements;
		if 'MBS SQL Version' of globals >= 9 then { SQL Server 2005 onwards }
	        SQL_Statements = SQL_Statements + "IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[" + IN_Procedure + "]') AND type in (N'P', N'PC')) " + char(13);
    	else
	        SQL_Statements = SQL_Statements + "IF  EXISTS (SELECT * FROM sys.sysobjects WHERE id = OBJECT_ID(N'[dbo].[" + IN_Procedure + "]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1) " + char(13);
		end if;
	    SQL_Statements = SQL_Statements + "DROP PROCEDURE [dbo].[" + IN_Procedure + "] " + char(13);
        {Execute the SQL statements.}
        status = SQL_Execute(SQL_connection, SQL_Statements);
		if status = 0 then
	        {Build the SQL statements.}
			clear SQL_Statements;
			SQL_Statements = SQL_Statements + "DECLARE @mystmt varchar(max) " + char(13);
			SQL_Statements = SQL_Statements + " " + char(13);
			SQL_Statements = SQL_Statements + "SET @mystmt = '' -- initialise " + char(13);
			SQL_Statements = SQL_Statements + " " + char(13);
			SQL_Statements = SQL_Statements + "SELECT @mystmt = @mystmt + rtrim(STRGA255) + ' ' + CHAR(13) " + char(13);
			SQL_Statements = SQL_Statements + "FROM " + Table_GetOSName(table MBS_Create_Stored_Procedure) + char(13);
			SQL_Statements = SQL_Statements + "ORDER BY SEQNUMBR " + char(13);
			SQL_Statements = SQL_Statements + " " + char(13);
			SQL_Statements = SQL_Statements + "--print @mystmt " + char(13);
			SQL_Statements = SQL_Statements + "exec (@mystmt) " + char(13);
	        {Execute the SQL statements.}
	        status = SQL_Execute(SQL_connection, SQL_Statements);
			if status = 0 then
		        {Build the SQL statements.}
				clear SQL_Statements;
		        SQL_Statements = SQL_Statements + "GRANT EXECUTE ON  [dbo].[" + IN_Procedure + "] TO DYNGRP " + char(13);
		        {Execute the SQL statements.}
		        status = SQL_Execute(SQL_connection, SQL_Statements);
			end if;
		end if;
        if status = 0 then
			{ Nothing returned }
        else
			clear l_text;
            l_text = l_text + "An error occurred executing SQL statements." + char(13);
            {Retrieve the specific error information.}
            status = SQL_GetError(SQL_connection, GPS_error_number, SQL_error_number, SQL_error_string, ODBC_error_string);
            if status = 0 then
                l_text = l_text + "GPS Error: " + str(GPS_error_number) + char(13);
                l_text = l_text + "SQL Error: " + str(SQL_error_number) + " " + SQL_error_string + char(13);
                l_text = l_text + "ODBC Error: " + ODBC_error_string + char(13);
            else
                l_text = l_text + "Unable to retrieve SQL error information." + char(13);
            end if;
			beep ERRORSOUND;
			error l_text;
        end if;
    else
        error "Could not switch to the correct database.";
    end if;
    {Disconnect from the SQL data source.}
    status = SQL_Terminate(SQL_connection);

	OUT_Created = true;
else
    {An error occurred creating the pass-through SQL connection.}
    warning "An error occurred creating the pass-through SQL connection: " + str(status);
end if;

pragma(enable warning LiteralStringUsed);

Note the use of Table_GetOSName() to pass the temporary table’s physical name to the pass through SQL.

Hope you find this technique useful.

David

10-Dec-2021: Added method using an array of Text variable to create a script larger than 32K.

This article was originally posted on http://www.winthropdc.com/blog.