Description

Stored procedures can enforce data integrity (i.e. the accuracy, consistency, and reliability of data that is stored in the database) by database constraints. For example, if you add a customer an Insert procedure makes sure that related data will be added too.

You can use the IISERPStoredProc interface to retrieve or change data from the database. If you use it to run stored procedures you can fill in input/output parameters. The IISERPStoredProc interface is very similar to the IISERPQuery interface.

A user must have at least read rights for the program code 100009980 (Data via Integration Server) to run a stored procedure. Creating a stored procedure and filling in the properties do not require any rights, as opposed to retrieving or changing data. So users can only retrieve or change data from Isah if they have sufficient rights.

The Isah database contains a large number of stored procedures for retrieving data. You can add your own stored procedures, although it requires expertise. If you created a stored procedure object you can retrieve data by filling in the name and the parameters. The stored procedure definition determines which parameters are required and which ones are optional. Reading the data sets is similar to the methods used for queries.

The following code retrieves data form the customer table using two parameters that determine the result set.

Dim oStoredProc As IERPLink.IISERPStoredProc

Set oStoredProc = oERPLink.CreateStoredProc()

oStoredProc.StoredProcName = "IP_sel_CustomerBrowse"

oStoredProc.Params.ParamByName("@CustObsInd ").Value = True

oStoredProc.Params.ParamByName("@SendToFinInd ").Value = False

oStoredProc.Open()

MsgBox "Number of lines: " & oStoredProc.RecordCount

oStoredProc.Close()

Adding, Deleting or Changing Records

Although it is possible to modify tables with queries, usually stored procedures are used for adding, deleting or changing data in Isah tables and even in custom tables because of the data integrity.

Adding Records

To add a record to a table with a stored procedure carry out the following steps:

• Fill in the parameters: data type (see below), parameter name, type (input/output) and value.
• Run the stored procedure. If an error occurs, you have to address it, for example by using a try/catch statement.

Some frequently used data types are:

• 1 = String
• 2 = Smallint
• 3 = Integer
• 5 = Boolean
• 6 = Float
• 11 = DateTime
• ...

The following code adds a record to the language table:

Dim oStoredProc As IERPLink.IISERPStoredProc

oStoredProc = oERPLink.CreateStoredProc()

oStoredProc.StoredProcName = "IP_ins_Language"

oStoredProc.Params.ParamByName("@LangCode ").Value = "NT"

oStoredProc.Params.ParamByName("@Description ").Value = "New language"

oStoredProc.Params.ParamByName("@LangId ").Value = 1

oStoredProc.Params.ParamByName("@IsahUserCode").Value = "GUEST"

Try

oStoredProc.ExecProc()

Catch ex As Exception

MsgBox(ex.Message)

lLUO = oStoredProc.Params.ParamByName("@LastUpdatedOn").Value

End Try

Changing Records

In order to change a record in the database with a stored procedure you have to enter a value in the LastUpdatedOn field that matches the database value. This prevents users from attempting to update records at the same instant. The code sample below contains the method GetLastUpdatedOn that retrieves the LastUpdatedOn value. This method returns the value as text. The text format must be: 0:yyyyMMdd HH:mm:ss.fff.

The following code retrieves the value of the LastUpdatedOn field for a particular language:

Private Function GetLastUpdatedOn(ByVal aLangCode As String) As String

Dim oQuery As IERPLink.IISERPQuery

GetLastUpdatedOn = ""

oQuery = oERPLink.CreateQuery()

oQuery.SQL = "SELECT LastUpdatedOn FROM T_Language" &_

" WHERE LangCode = '" & aLangCode & "'"

oQuery.Open()

If oQuery.RecordCount > 0 Then

GetLastUpdatedOn = String.Format("{0:yyyyMMdd HH:mm:ss.fff}",

oQuery.FieldByName("LastUpdatedOn").AsDateTime)

End If

oQuery.Close()

End Function

The following code changes the language description:

Dim oStoredProc As IERPLink.IISERPStoredProc

Dim lLUO as string

lLUO = GetLastUpdatedOn("NT")

oStoredProc = oERPLink.CreateStoredProc()

oStoredProc.StoredProcName = "IP_upd_Language"

oStoredProc.Params.ParamByName("@old_LangCode ").Value = "NT"

oStoredProc.Params.ParamByName("@Description ").Value = "Changed language"

oStoredProc.Params.ParamByName("@old_LastUpdatedOn").Value = lLUO

oStoredProc.Params.ParamByName("@IsahUserCode").Value = "GUEST"

Try

oStoredProc.ExecProc()

lLUO = oStoredProc.Params.ParamByName("@LastUpdatedOn").Value

Catch ex As Exception

MsgBox(ex.Message)

lLUO = oStoredProc.Params.ParamByName("@LastUpdatedOn").Value

End Try

Deleting Records

In order to delete a record in the database with a stored procedure you have to enter a value in the LastUpdatedOn field that matches the database value. This prevents users from attempting to update or delete records at the same instant.

Dim oStoredProc As IERPLink.IISERPStoredProc

oStoredProc = oERPLink.CreateStoredProc()

oStoredProc.StoredProcName = "IP_del_Language"

o oStoredProc.Params.ParamByName("@old_LangCode ").Value = "NT"

o oStoredProc.Params.ParamByName("@old_LastUpdatedOn").Value = lLUO

o oStoredProc.Params.ParamByName("@IsahUserCode").Value = "GUEST"

Try

oStoredProc.ExecProc()

Catch ex As Exception

MsgBox(ex.Message)

End Try