Monday, January 26, 2009

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/

16 comments:

Marcos said...

Mariano, in the DEX script, the variable 'n_loopcount' isn't initilized with a value (I think that it's set witt zero value by the compiler), so the condition 'i < n_loopcount' will never be true

Mariano Gomez said...

Marcos:
You are correct! n_loopcount should be set to a value for the number of attempts you would like the runtime engine to perform if a deadlock exception is encountered.

n_loopcount = 5;

For example.

Thanks for pointing this out.

MG.-
Mariano Gomez, MVP

Adriaan Davel said...

Hi,
Any idea why my code below never goes into the 'else' block? (Using Dex 2010 and I believe I have all the updates)

try
call sproc "PF_sp_IntegrateCustomerDirectly", sproc_status, CustomerNumber, BranchId, IntegrationRunId;
catch [EXCEPTION_CLASS_SCRIPT_STRING_OVERFLOW]
{do nothing}
else
warning str(Exception_Class());

call PF_sp_CreateIntegrationExceptionLog,
sproc_status,
IntegrationRunId,
"Customer",
CustomerNumber,
BranchId,
-1,
Exception_Message(),
'User ID' of globals,
sysdatetime();

sproc_status = 1;

end try;

Mariano Gomez said...

Adriaan,

The only thing I can conclude from your code is that your else is not being met due to the type of exception you are trying to catch.

Try changing your exception to EXCEPTION_CLASS_DB_DEADLOCK

MG.-
Mariano Gomez, MVP

Anonymous said...

Hi Mariano,

Is there a way of accomplishing Stored Procedure call but within the Support Debugging Tool? Looking to insert RW_ function calls in Report Writer following your http://dynamicsgpblogster.blogspot.com/2012/05/adding-customer-item-user-defined.html example, but want to move the majority of the code into a Stored Procedure. If not, do you know of an example of being able to access Extender fields in Dexterity within the same confines on the "adding-customer-item..." page?

Thanks,

Michael J. Nola

Mariano Gomez said...

@Michael:

You can certainly use the Support Debugging Tool's SQL scripting capabilities to call a stored procedure. Furthermore, your SQL Script ID's can be called from a Dexterity script in the SDT.

I should write a blog about this.

MG.-
Mariano Gomez, MVP

Michael J. Nola said...

Hi Mariano,
Thank you for the quick response! Could you give an example of the Dexterity syntax that would be used to call the SDT SQL Script ID or a possible link?

As for getting to our Extender fields within SDT Dex Script, David's How To article (https://community.dynamics.com/gp/b/developingforgp/archive/2011/09/08/how-to-display-more-than-80-characters-of-an-extender-long-string-field-in-reports-using-the-support-debugging-tool.aspx#.UkxZ1uTD-ew) gave me the insight in what I was looking for. I've known about and have long since used the RW_TableHeaderString function inside Report Writer, but it didn't even dawn on me that this simple function could be addressed like any other function in Dexterity - had it in my head that it could only be called in Report Writer (live and learn). In any case, it will still be nice to be able to call a Stored Procedure that will result in fewer RW_TableHeaderString calls needed in the Dex Script in addition to being able to pull the Extender field's Label value concatenated with the field's value. Otherwise I'm looking at 15 RW_ calls and 15 hardcoded string fields to represent the field labels in the Dex Script.

Anything you can do to shed further light on this is greatly appreciated.

Thanks again!

Michael J. Nola

Michael J. Nola said...

Hi Mariano,
On follow-up to my last set of questions posted today (10/04/2013), believe I've located the answer I was looking for (and what you were hinting at). After doing more research I happened to come about the following commands by means of the following link(https://community.dynamics.com/gp/b/ifks/archive/2011/11/03/support-debugging-tool-customization-10-credit-limit-check-skip-in-receivables-transaction-entry-using-sql-execute-in-sdt.aspx) and the Debugger.pdf:

 MBS_SQL_Check_Exists
 MBS_Export_SQL_Query_To_File


I will need to do a bit of experimenting to see what the data being returned by the SQL_Check_Exists call looks like and how best to parse it and pass it back. Fortunately, for my current issue, I only need to pass back a single string value which will make matters simpler. However, it will still be good a good exercise in translating multiple records, if need be.

Thank you very much for getting me on the right path!

Michael J. Nola

Sanjay Kumar Rajarao said...

Can we call a stored proc from report writer ?

msgpdev10 said...

I am having issue with calling a SQL procedure from dexterity. The procedure contains cursor. This cursor is suppose to call another procedure which has a Dynamics GP Procedure 'taComputerChecklineinsert'. The working is supposed to be that the overall process has to insert transactions in the payroll transaction entry. Only a fixed number of 42 transactions. I have more than 42 transactions. If i execute the same procedure from SQL server with the same parameters itself it gives the required result. the issue comes up when i call from dexterity.

Mariano Gomez said...

msdyndev10,

I cannot help you if you don't tell me what the issue is. Are you getting any errors? If you step through the Dex code, what's the prototype procedure return status?

MG.-

Anonymous said...

We have a stored procedure that does not return anything if we define the parameters this way:.

sproc returns long sproc_status;
in string FindVendorID;
inout string StateTaxCode;
inout string CountyTaxCode;
inout string CityTaxCode;
inout string FedTaxCode;




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

try
warning "Before Call VendorID= " + FindVendorID + " FedTaxCode = "+ FedTaxCode + " StateTaxCode= " + StateTaxCode + " CountyTaxCode= " + CountyTaxCode + " CityTaxCode= " + CityTaxCode;

call sproc "RMC_StockHolderTaxCodeVendorID", sproc_status, FindVendorID StateTaxCode, CountyTaxCode, CityTaxCode, FedTaxCode;

warning "After Call VendorID= " + FindVendorID + " FedTaxCode = "+ FedTaxCode + " StateTaxCode= " + StateTaxCode + " CountyTaxCode= " + CountyTaxCode + " CityTaxCode= " + CityTaxCode;

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;

But does return values if we call it this way:

sproc returns long sproc_status;
in long RMC_OwnerID;
inout string StateTaxCode;
inout string CountyTaxCode;
inout string CityTaxCode;
inout string FedTaxCode;
in string FindVendorID;

{
inout string RMC_FedExempt;
inout string RMC_StateExempt;
inout string RMC_CountyExempt;
inout string RMC_CityExempt;
}

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

try
warning "Before Call VendorID= " + FindVendorID + " FedTaxCode = "+ FedTaxCode + " StateTaxCode= " + StateTaxCode + " CountyTaxCode= " + CountyTaxCode + " CityTaxCode= " + CityTaxCode;
call sproc "RMC_StockHolderTaxCodeVendorID", sproc_status, RMC_OwnerID, StateTaxCode, CountyTaxCode, CityTaxCode, FedTaxCode, FindVendorID;
warning "After Call VendorID= " + FindVendorID + " FedTaxCode = "+ FedTaxCode + " StateTaxCode= " + StateTaxCode + " CountyTaxCode= " + CountyTaxCode + " CityTaxCode= " + CityTaxCode;

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;

The difference being the field Owner_id is added as a long to the parameter list. It is being used as a dummy parameter, nothing is being sent in on it. Yet without it the procedure does not return any data. Any ideas?

Joshua Pelkola said...

Hi Mariano,

Do you have a simple example of calling procs when using the SDK for Visual Studio using the built in connectors for GP?

Thanks,
Josh

Mariano Gomez said...

Anonymous,

I won't be able to help without understanding what the stored procedure looks like. If it's returning data with the added parameter, it means that the parameter list being submitted to the stored procedure is complete. In addition, even if the parameter is a dummy parameter, apparently it is required for the stored procedure to work.

MG.-

Joshua Pelkola said...

It was more a general question. If I have any stored procedure, can it be called from vs tools? I am trying to avoid using a separate connector like SqlClient. I'm looking through the sdk, but there is not a lot of information. That, or I missed it.

Thanks,
Josh

Mariano Gomez said...

Joshua,

You can call stored procedures from Visual Studio Tools IF AND ONLY IF you use the GPConnNet.dll. A VST application runs within the context of the runtime engine and requires credentials to be passed to the database server. GP user passwords are encrypted so the ONLY ways today that you can run a stored proc using the credentials of the GP user is by referencing the GPConnNet.dll assembly.

Take a look at my article http://dynamicsgpblogster.blogspot.com/2011/04/hybrid-development-for-managed-code_27.html for samples.