Stored Procedure

Plug-in Deployment

Verivo provides a SQL and Oracle Stored Procedure plug-in for use.  Below are the install instructions for the initial deployment:

SQL Stored Procedure Plug-in

  1. Download Plugins.SQLStoredProcedure.dll and MDM_Stored_Procedure.xml.
  2. Update your Verivo AppStudio plugin folder to contain Plugins.SQLStoredProcedure.dll and MDM_Stored_Procedure.xml.
  3. Update your Verivo AppServer bin\plugin folder to contain Plugins.SQLStoredProcedure.dll and MDM_Stored_Procedure.xml.

SQL92 Stored Procedure Plug-in

Download:

  • Encryption.dll
  • MDM_SQL92_Stored_Procedure.xml
  • mpDB.dll
  • Plugins.SQL92.dll
  • Plugins.SQL92StoredProcedure.dll
  • PyxisMobile.AppProperty.dll                                                                                                                                                                                                                            
  1. Update your Verivo AppStudio plugin folder to contain the plug-ins in Step 1 
  2. Update your Verivo Application Server bin\plugin folder to contain the plug-ins from Step 1.

Oracle Stored Procedure Plug-in

  1. Download MDM_Oracle_Stored_Procedure.xml, Plugins.OracleStoredProcedure.dll, Plugins.SQL92.dll, and Plugins.SQL92StoredProcedure.dll.
  2. Update your Verivo AppStudio plugin folder with the contents of Step 1.
  3. Update your Verivo Application Server bin\plugin folder with the contents of Step 1.


As you develop against this plug-in, the MDM_Stored_Procedure.xml, MDM_Oracle_Stored_Procedure.xml, or MDM_SQL92_Stored_Procedure.xml file will be updated.  You will need this updated file in both the AppStudio and AppServer locations to be able to develop and test against. When you are looking to migrate your changes to another environment, you will need to move this file along with the exported configuration.


Mappings

The fields available for a given stored procedure will be controlled by what you have selected in the Data Source for the Auto Describe Output checkbox. 

Advaned Tab

Auto Describe Output - Checkbox to indicate if the plug-in will attempt to automatically describe input/output fields or if it will describe using the MDM_Stored_Procedure.xml file.

 

If you have checked Auto Describe Output, you are allowing the data source to attempt to automatically describe the input and outputs from the stored procedure. The plug-in will attempt to run the stored procedure with all default values to determine the output fields.  Note that this method will make available the special Verivo Flag which does not need to be mapped. (What is the Verivo Flag sed for?)

If you do not check off Auto Describe Output, the data source will use the MDM_Stored_Procedure.xml file to explicitly define the input and outputs as well as their data type. The XML file will look at the stored procedure name and compare it against the stored procedure data source. If it finds a match between this name and an available stored procedure it will override the mappings with the values in the XML.  Note that the input field names do not have the exactly match the input names in the stored procedure. However for output the field names do have to match and are case sensitive.

 

Entities

Verivo entities operate under a relational database schema.  This means that every entity should have a Primary Key.  MDM will make an explicit check on uploads for these settings in the Entity and return an error to the user.  Stored Procedures by nature do not have a Primary Key so you will have to adjust things to work within MDM. 

Because download does not explicitly check for the Primary Key setting, the scenario where you will need to adjust things are on upload.  Within the stored procedure you will need to create two new varchar inputs.  The naming of these new inputs do not matter but you will need to know that they will be used to fulfill the Primary Key requirements. 

Within the Entity create two new fields: the Primary Key, and the Entity Primary Key.  Map the two keys to the two new inputs in the stored procedures and mark the Primary Key column as the Primary Key.  This will cause the application to send up Verivo generated GUIDs into those input fields.  You do not need to perform any specific action with these values within the stored procedure.

 

Download Stored Procedure

 A stored procedure is classified as a download stored procedure if it will be used to populate a List, Form, or Report screen.  In all scenarios (including empty data sets) the stored procedure must return back a single table

The following script creates a simple stored procedure that will return back a fixed set of data. There will be two columns in the result set: Ouput and UserID.  In AppStudio this entity would not have a Primary Key and everything would be marked as download.

 

CREATE PROCEDURE SAMPLE_STORED_PROCEDURE 
AS
BEGIN
SELECT 'Hello World!' as Output, 'mpdemo' as UserID
END
GO

The corresponding MDM_Stored_Procedure.xml file will look like the following:

<?xml version="1.0" encoding="utf-8" ?> 
<SQLServerStoredProcs>
 <storedprocedure>
  <name>SAMPLE_STORED_PROCEDURE</name>
  <param name="Output" datatype="System.String" type="output"/>
  <param name="UserID" datatype="System.String" type="output"/>  
 </storedprocedure> 
</SQLServerStoredProcs>

 

Download Stored Procedure Mapping

When you map a screen against a stored procedure for download, the input for the stored procedures will also be marked as download in the entity mapping.  You will then use the Narrow Selection or Join tab to send in values.

 

Upload Stored Procedure

A stored procedure is classified as an upload stored procedure when it is used on a Form or Add screen to update/add data.  The first input is a special Verivo Flag to indicate the Upload Type.

The following script creates a stored procedure that will take in the Primary Key and Entity Primary Key values, as well as two other values to update/insert/delete on the data source.  Note the first input value as the Verivo Flag and an example of how to handle that logic within the stored procedure.  In AppStudio this entity would mark the ID control as the Primary Key, EntID as the Entity Primary Key with everything mapped as upload.

 

SQL

CREATE PROCEDURE SAMPLE_STORED_PROCEDURE
@VerivoFlag varchar(100)
@ID varchar(100)
@EntID varchar(100)
@Value1 varchar(100)
@Value2 varchar(100)
AS
BEGIN
--Insert Scenario
IF @VerivoFlag = '0' INSERT INTO SAMPLE_TABLE (ID, Value1, Value2) VALUES (@ID, @Value1, @Value2)
--Update Scenario
ELSE IF @VerivoFlag = '1' UPDATE SAMPLE_TABLE SET Value1 = @Value1, Value2 = @Value2 WHERE ID = @ID
--Delete Scenario
ELSE IF @VerivoFlag = '2' DELETE FROM SAMPLE_TABLE WHERE ID = @ID
END
GO

Oracle

CREATE OR REPLACE PROCEDURE SP_STORED_PROCEDURE (
    VerivoFlag IN VARCHAR, 
    ID  IN VARCHAR,
    EntID IN VARCHAR,
    ValueOne IN VARCHAR,
    ValueTwo IN VARCHAR
    Msg OUT VARCHAR) AS  
BEGIN
 IF (VerivoFlag == '0') THEN
  --Insert Scenario
 END IF (VerivoFlag == '1') THEN
  --Update Scenario
 END IF (VerivoFlag == '2') THEN
  --Delete Scenario
 END

  Msg := 'Return Success/Error Message here';  
END;

 

The corresponding MDM_Stored_Procedure.xml file will look like the following:

SQL

<?xml version="1.0" encoding="utf-8" ?> 
<SQLServerStoredProcs>
 <storedprocedure>
  <name>SAMPLE_STORED_PROCEDURE</name>
  <param name="ID" datatype="System.String" type="input" required="true" order="100"/>
  <param name="EntID" datatype="System.String" type="input" required="true" order="110"/>  
  <param name="Value1" datatype="System.String" type="output"/>  
  <param name="Value2" datatype="System.String" type="output"/>  
 </storedprocedure> 
</SQLServerStoredProcs>

Oracle

<?xml version="1.0" encoding="utf-8" ?> 
<SQLServerStoredProcs>
 <storedprocedure>
  <name>SAMPLE_STORED_PROCEDURE</name>
  <param name="ID" datatype="System.String" type="input" required="true" order="100"/>
  <param name="EntID" datatype="System.String" type="input" required="true" order="110"/>  
  <param name="Value1" datatype="System.String" type="output" required="false" size="100" order="200/>  
  <param name="Value2" datatype="System.String" type="output" required="false" size="100" order="210/>  
 </storedprocedure> 
</SQLServerStoredProcs>

 

Upload Stored Procedure Mapping

When you map a screen against a stored procedure for upload the input for the stored procedures will all be marked as upload in the entity mappings.  The controls on the form/add screen will compare against the entity mappings for determining the inputs into the stored procedure.

When the Verivo platform uses a Stored Procedure for an Upload on an Add or Edit screen, it will automatically send up a value to the stored procedure to indicate the action.

This value is always the first value that will be sent into the stored procedure and will either be a '0' for Add and '1' for edit. If you are defining your stored procedure via the MDM_Stored_Procedure.xml file you do not have to define this special Verivo input. However if you are using auto describe inputs for the stored procedure data source you will see this as an available mappable input field in MDM. You do not have to map this field.

 

Configuration

On a List or Form screen, the inputs to the stored procedure will be configured by adding a narrow selection or join instruction.  Uploads on a Form or Add screen will take the value being uploaded and send it into the proper input by the mappings for the entity.

Choice Lists

If you are populating a Choice List from the output of a Stored Procedure, it is important to note that MDM will look at matching up the output against a string. If you are outputting a data type other than string it will not match up even if they look correct to the eye. Use a CAST() to change the data type to String.

 

Naming

Inputs in stored procedures are prefixed with the value "@".  It is tempting to use the same naming structure when you define the stored procedure through the XML or if you are creating the mappings through MDM.  However the iPhone client uses JavaScript rules when it reads through control names on a screen.  This means that any control name that contains special characters and or spaces could cause unexpected behavior.  In order to fully comply to all three platforms keep your naming alphanumeric.

 

Debugging

The Verivo Logging table will contain important information about the request to the stored procedure that you can use to investigate any issues you are seeing in terms of errors or expected data.  If you have your logging level set to 40 you will see records that are being written from the stored procedure plug-in.

The record that you will reference the most will be the one which states what inputs are being sent to the stored procedure.  The message will start with the text: "Stored Procedure Parameters:"

Each value after this string separated by a comma will represent the input.  They will correspond from left to right to the inputs top to bottom in the stored procedure (if you are looking at the XML or SQL definition).  You can use this string to manually call the stored procedure in a query analyzer to help your investigation.  One thing to note here though is that this record in the database does not accurately represent null value and string values.  This means the plug-in will send in a null value and it will show up as blank in the string.  If the plug-in needs to send in a string value it will not encase the value in single quotes. 

For example if a stored procedure has three inputs and the application sends in a null in the second value it will look like this:

Stored Procedure Parameters: ValueOne, , ValueTwo

When you are generating your manual query physically add the null value and single quotes around the string values:

EXEC StoredProcedure 'ValueOne', null, 'ValueTwo'

If the stored procedure exists on the Verivo configuration database or has access to the database, you can use the Verivo stored procedure usp_mp_logmsg to write to the Verivo Logging tables to assist you in any issue investigation.

The usp_mp_logmsg procedures takes the following input parameters:

  • serviceName
  • logMsg
  • logLevel (Default 20)
  • userID (Default mPCon)
  • sessionID (not required)

Common Error Scenarios 

Description: User receives popup error on download "Cannot find table 0".

Solution: The stored procedure is not returning back a table on download.

 

Description: User receives a popup error on download/upload "There was an error in this transaction".  In the Verivo logs there is a record with the message text "There are too many inputs specified for this stored procedure".

Solution: On upload the stored procedure may not account for the special Verivo Flag which is always included as the first input. There was an error in this transaction.

 

Description: User receives popup error on download/upload "There was an error in this transaction"

Solution: Run the stored procedure manually with the same inputs to expose the exact error.

 

Description: The request to the stored procedure is being shown as a SQL query.

Solution: There is a version mismatch with the SQLPlugin on the Verivo AppServer. Download the latest SQL Stored Procedure Plugin raw file and update the files in the bin/plugin folder.  Afterwards take the SQLPlugin.dll file and update the bin directory.

 

Description: User receives popup error on upload "Cannot find screen ID [GUID]".

Solution: If this is after you Save on a Form/Add screen this is because you do not have a Primary Key set for the Entity.

 

Description: When attempting to call a screen using a stored procedure you see this error in the logs: 

ERROR: MDM Exception thrown (Error Code 2014): Error writing uploads to database.  Exception: Error while executing query: exec sp_executesql N'Insert into na_sp_DeleteAnnouncement (TitleMessage,EntryBy,UpdateBy) Values (''14cf4334-1112-4d65-8386-25c1208b2510'',''adpmotm11'',''adpmotm11'')'.Error: Invalid object name 'na_sp_DeleteAnnouncement'.

Solution: There is a version mismatch between the Plugin.SQLServer.dll file in the Verivo Application Server directory and the Plugins.SQLStoredProcedure.dll in the bin/plugin folder.

 To correct this you will need to take the Plugin.SQLServer.dll file from the Stored Procedure installer and replace the version in the bin directory of your Verivo AppServer.

 

Temporary Table Management

If you are using temporary tables in your custom stored procedures you can run into a problem when you are attempting to DROP the table using a LIKE comparison against sysobject.

For example for the following code:

IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE name LIKE '#UpdateActivity%')
BEGIN
DROP TABLE #UpdateActivity;
END

 

Can give you this error: 

Cannot drop the table '#UpdateActivity', because it does not exist or you do not have permission.

Unless you created #UpdateActivity in another session, using Object_ID and letting SQL Server figure out the suffix to the table name (and thus making sure that the #UpdateActivity it finds is YOUR #UpdateActivity) is much better. 

Example: 
IF isNull(object_id('tempdb..#InsertDeleteActivityReltn'),0)>0
DROP TABLE #InsertDeleteActivityReltn

 

Searching Duplicate Records

If you run into Primary Key Insert errors you can use following script as a template to search for the offending record. It takes advantage of SQL Server's HAVING clause: 

SELECT RepID
FROM tmi_Rep
GROUP BY RepID
HAVING COUNT(*) > 1

 

Dynamically create XML rows automatically based on the Stored Procedure's schema

(intro paragraph?)

declare @procName varchar(50)
set @procName = 'mP_Upload_Contact'
select ''
from INFORMATION_SCHEMA.PARAMETERS
where SPECIFIC_NAME = @procName
order by ORDINAL_POSITION

 

Date Comparison
Because date comparisons take into account the time, you can use the following code to create the appropriate narrow selection to any records for today's date:

 

Start of Day: DateAdd(day, DateDiff(day, 0, getdate()), 0)

End of Day: DateAdd(second, -1, DateAdd(day, DateDiff(day, 0, getdate())+1, 0))

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk