Calling SQL Server stored procedures from Microsoft Dexterity

This article will focus on techniques that can save your life when developing in Dexterity and especially when integrating with third party applications.

All too often, the only methods discussed for accessing third party data are the use of triggers and/or pass-through SQL in Dexterity. While these methods are very efficient, they rely heavily on the developer's ability to implement error handling at the application client side. Also, the amount of code needed perform these actions (to retrieve/save data) can be overwhealming at times.

One of my preferred methods is the use of SQL Server stored procedures. Stored procedure calls allow developers to separate things like data entry validation from actual business logic that deals with data saving and retrieval. It's fairly simple to fix a stored procedure that's already in a production environment versus the Dexterity code as a whole, since the latter involves redeploying chunk files. We all know too well how cumbersome this can become in large environments where downtime cannot be afforded.

The basics

In order to call stored procedures from Dexterity, they are 3 basic steps. In the process, we will consider the following example: say you are asked to create a form in which you will prompt the user to enter a service call number. In turn, you will retrieve the amount to be billed on the service call and estimate the gross revenue based on a formula that involves the duration of the service. With this in mind, let's take a look at how you can achieve this in 3 steps using Dexterity.

1) Write your stored procedure and grant access to the SQL Server DYNGRP role. This will make your stored procedure accessible from your Dexterity application, avoiding SQL Server permission issues. Our stored procedure will need to accept a service call number as a parameter, then return the total amount to be billed on the service call.

dbo.uspGetServiceCallTotal

IF OBJECT_ID ( 'dbo.uspGetServiceCallTotal', 'P' ) IS NOT NULL
  DROP PROCEDURE dbo.uspGetServiceCallTotal
GO

CREATE PROCEDURE dbo.uspGetServiceCallTotal
  @IN_callnbr char(20) = NULL,
  @IN_service_type smallint,
  @INOUT_serviceTotal numeric(19,5) output
AS

SELECT @INOUT_serviceTotal = TOTAL FROM dbo.SVC00200
WHERE CALLNBR = @IN_callnbr and SRVTYPE = @IN_service_type
GO

GRANT EXECUTE ON dbo.uspGetServiceCallTotal TO DYNGRP
GO

2) Create a prototype of the stored procedure in your Dexterity application. Since Dexterity cannot invoke a stored procedure directly, it is necessary to create a prototype of the stored procedure. Think of a prototype as a wrapper for your SQL Server stored procedure. This wrapper is Dexterity code that provides the compiler with a level of isolation from your SQL code.

Dex sproc uspGetServiceCallTotal

{ Created by Mariano Gomez, MVP.     }
{ global prototype procedure uspGetServiceCallTotal }
{                                                   }
sproc returns long sproc_status;
in string IN_callnbr;
in integer IN_service_type;
inout currency INOUT_serviceTotal;

local long l_ReturnCode, timedelay;
local integer i, n_loopcount;

try
  call sproc "uspGetServiceCallTotal",
    l_ReturnCode,
    IN_callnmbr,
    IN_service_type,
    INOUT_serviceTotal;
catch [EXCEPTION_CLASS_DB_DEADLOCK]
  if i <= n_loopcount then
    timedelay = Timer_Sleep(200);
    increment i;
    restart try;
  else
    exit try;
  end if;
else
  exit try;
end try;

set sproc_status to l_ReturnCode;

NOTE: From the Dexterity help file, the try...end try statement is used to implement structured exception handling in Dexterity. The try...end try statement must contain at least one catch clause or an else clause. If none of the catch clauses catch the exception and there is no else clause, the exception is considered unhandled. A dialog box is automatically displayed describing the situation to the user. For information about structured exception handling and system exceptions see try...end try statement in the Dexterity help file.

NOTE: When creating the prototype global procedure in Dexterity, make sure to select the proper Series for the script. If your stored procedure will run in the DYNAMICS database, you will want to select System as the Series for the prototype procedure.

3) Now your calling code! Finally, you can have your own script that calls the Dexterity prototype procedure. This call is implemented like any call to any other global scripts.

Sample Dex calling script

{ this can be a call from a form script or field script within your code }
local long sproc_status;
local currency call_total;

call uspGetServiceCallTotal, sproc_status,
   'Call Number' of window yourCustomWindow,
   2,
   call_total;
set 'Call Total' of window yourCustomWindow to call_total;

While the use of stored procedures is widely documented throughout the Dexterity manuals and help files, it is always useful to highlight the basic steps when creating and calling stored procedures. It does not take that much, and can be a good alternative to triggers and pass-through SQL whenever your code does not need to be interactive with the events in third party forms, windows, and tables.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/