SytelineMaster

For those who come from old school of Progress Syteline (Symix) like me, “For Each” command is so nature to us, in terms of loop through record for processing.  But SQL is pretty much bulk processing language.  There is simply no equivalent command as “For Each” in T-SQL. 

There are basically two way to loop through each record in a record set for processing in T-SQL.  The first one, also the most popular one being used through out Syteline SP, is to use the CURSOR. 

Use CURSOR

Here is a example:

DECLARE CUR_ITEMWHSE CURSOR LOCAL STATIC FOR
    SELECT
        item,
        whse,
        qty_on_hand,
        qty_alloc_co
    From itemwhse
    where itemwhse.qty_on_hand > 0 and qty_alloc_co > 0

OPEN CUR_ITEMWHSE
    WHILE @Severity = 0
    BEGIN
        FETCH CUR_ITEMWHSE INTO
            @item,
            @whse,
            @qty_on_hand,
            @qty_alloc_co

        IF @@FETCH_STATUS = -1
            BREAK

        …  /* Your process logic here */

     END

CLOSE CURSOR CUR_ITEMWHSE

Other way is to use the temp table.

Use Temp Table

You will first need to define a temp table with a field “Processed”

declare @Consumers table (
    [OrderID] [int] NOT NULL,
    [OrderDate] [smalldatetime] NULL,
    …   

    [Processed] [smallint] null
primary key (OrderID)
)

You then loop through the record set by select the first 1, which hasn’t been processed.

/* Loop through Consumers  to process     */       
While Exists(Select 1 from @Consumers where processed = 0)
begin
select top 1
    @OrderID = [OrderID],
    @OrderDate = [OrderDate],
     …
      from @Consumers where processed = 0

    … /* Your process logic here  */

Update @Consumers

set processed = 1 where OrderID = @OrderID

end

Be noted that these two method can not be mixed.  You can’t declare cursor for a temp table.

SQL server provides way to connect to remote database (SQL, Oracle and many others).  Within your store procedure, you can dynamically connect to the remote DB, fetch data, process them into Syteline DB. 

Business Case:

Let’s say you have a web store database, in a remote database server.  You want to replicate the orders into Syteline

Syteline Technical Component:

SQL Store Procedure

Solution:

In your store procedure, you will need to first connect to the remote DB server, and login to the database.  Here is a example with server name DBSRVR.

if not exists(select 1 from sys.servers where name = ‘DBSRVR’)
    EXEC sp_addlinkedserver
        ‘DBSRVR’,
        N’SQL Server’

exec sp_addlinkedsrvlogin ‘DBSRVR’
                         ,’false’
                         ,null
                         ,’sa’
                         ,’sapassword’

After getting the connection, you can use the select statement to fetch data into a temp table for further process.

Insert @Orders (
            [OrderID]
           ,[OrderDate]
           ……
           )
    Select
            [OrderID]
           ,[OrderDate]

           ……

        from [DBSRVR].[WEBORDER].dbo.orders

Once you done with all the process, you may want to drop off the connection

— Drop off from DBSRVR 
if exists(select 1 from sys.servers where name = ‘DBSRVR’)
Exec sp_dropserver ‘DBSRVR’, ‘droplogins’

To check what Database Servers are currently link to your DB Server, just query the sys.servers table. 

Select * from sys.servers

Here is a basic way to call a SQL Store Procedure (SP) within a script, either Form Script or Inline Script.

The sample we use below is calling a SP: GetCustType and return the CustType value to a form variable.

Dim nRetVal As InvokeResponseData
Dim nRequest As New InvokeRequestData()

nRequest.IDOName = “SP!”
nRequest.MethodName = “GetCustType”

nRequest.Parameters.Add(ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty(“CustNum”))
nRequest.Parameters.Add(“”)

nRetVal = IDOClient.Invoke(nRequest)

ThisForm.Variables(“vCustType”).Value = nRetVal.Parameters(1).ToString

And here is the SP:

PROCEDURE [dbo].[navGetCustType]
(@CustNum [CustNumType],
@CustType [CustTypeType] output)

as

select @CustType = cust_type
from customer
where cust_num = @CustNum and cust_seq = 0

Here is a multiple parameters  sample:

Sub nGetUnitDetails()
Dim nRetVal As InvokeResponseData
Dim nRequest As New InvokeRequestData()

nRequest.IDOName = “SP!”
nRequest.MethodName = “nGetUnitDetails”
nRequest.Parameters.Add(ThisForm.Components(“editUnit”).Text)
nRequest.Parameters.Add(CDate(ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty(“RmahdrRmaDate”)))
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)
nRequest.Parameters.Add(“”)

nRetVal = IDOClient.Invoke(nRequest)

ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“Warranty”, nRetVal.Parameters(2).ToString)
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“Item”, nRetVal.Parameters(3).ToString)
ThisForm.GenerateEvent(“ItemDataChange”)
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“CoNum”, nRetVal.Parameters(4).ToString)
ThisForm.GenerateEvent(“OrderNumLoseFocus”)
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“CoLine”, nRetVal.Parameters(5).ToString)
ThisForm.GenerateEvent(“DisplayRMAWarningMsgSp”)
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“CoRelease”, nRetVal.Parameters(6).ToString)
ThisForm.GenerateEvent(“CoLineChanged”)
‘New for Parag
‘ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“UnitCreditConv”, nRetVal.Parameters(7).ToString)
‘ThisForm.GenerateEvent(“RecalcTotals”)

If Not nRetVal.Parameters(3).ToString = “” Then
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh(“QtyToReturnConv”, “1”)
End If

End Sub

When deploying an UET customization in Syteline 7 & 8, there is SP can be used to script out the UET. Just do

exec ExportUETClassSP ‘classname’

Some server don’t this SP installed.  I just keep a copy here for in case

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[ExportUETClassSP] (

@PClassName Infobar

)

AS

DECLARE

@ClassName ClassNameType

, @ClassLabel LabelType

, @ClassDesc DescriptionType

, @SysHasFields ListYesNoType

, @SysHasTables ListYesNoType

, @SysApply UetSysApplyType

, @SysDelete ListYesNoType

, @FldName FldNameType

, @FldDataType UetDataTypeType

, @FldInitial UetDefaultType

, @FldDecimals UetScaleType

, @FldDesc ToolTipType

, @FldUDT sysname

, @FldPrec tinyint

, @IndexName IndexNameType

, @IndexDesc DescriptionType

, @IndexUnique ListYesNoType

, @IndexWord ListYesNoType

, @IndexSeq UetIndexSeqType

, @IndexAsc ListYesNoType

, @TableName TableNameType

, @TableRule QueryExpressionType

, @ExtendAllRecs ListYesNoType

, @AllowRecordAssoc ListYesNoType

, @Active ListYesNoType

, @SQLCmd InfobarType

, @SQLCmdWait InfobarType

, @Severity INT

, @Quote NCHAR(1)

, @RecordDate CurrentDateType

, @RowPointer RowPointerType

, @CommittedRowPointer RowPointerType

, @CreatedBy UsernameType

, @UpdatedBy UsernameType

, @CreateDate CurrentDateType

, @InWorkflow FlagNyType

SET @Severity = 0

SET @Quote = ””

DECLARE UserClassCrs CURSOR LOCAL STATIC

FOR SELECT

uc.class_name

, uc.class_label

, uc.class_desc

, uc.sys_has_fields

, uc.sys_has_tables

, uc.sys_apply

, uc.sys_delete

FROM user_class uc

WHERE class_name = @PClassName

OPEN UserClassCrs

WHILE @Severity = 0

BEGIN — cursor loop

FETCH UserClassCrs INTO

@ClassName

, @ClassLabel

, @ClassDesc

, @SysHasFields

, @SysHasTables

, @SysApply

, @SysDelete

IF @@FETCH_STATUS = -1

BREAK

SET @SQLCmd = ‘INSERT INTO user_class ( class_name, class_label,

class_desc, sys_has_fields, ‘

SET @SQLCmd = @SQLCmd + ‘sys_has_tables, sys_apply, sys_delete

) VALUES ( ‘

SET @SQLCmd = @SQLCmd + dbo.Quote(@ClassName) + ‘, ‘ +

dbo.Quote(@ClassLabel)

SET @SQLCmd = @SQLCmd + ‘, ‘ + dbo.Quote(@ClassDesc) + ‘, ‘ +

STR(ISNULL(@SysHasFields, 0))

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@SysHasTables, 0)) +

‘, ‘ + dbo.Quote(@SysApply)

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@SysDelete, 0)) + ‘ )’

print @SQLCmd

DECLARE UserClassFldCrs CURSOR LOCAL STATIC

FOR SELECT

ucf.class_name

, ucf.fld_name

, ucf.sys_apply

, ucf.sys_delete

FROM user_class_fld ucf

WHERE class_name = @PClassName

OPEN UserClassFldCrs

WHILE @Severity = 0

BEGIN — cursor loop

FETCH UserClassFldCrs INTO

@ClassName

, @FldName

, @SysApply

, @SysDelete

IF @@FETCH_STATUS = -1

BREAK

SET @SQLCmd = ‘INSERT INTO user_class_fld ( class_name,

fld_name, sys_apply, sys_delete ) VALUES ( ‘

SET @SQLCmd = @SQLCmd + dbo.Quote(@ClassName) + ‘, ‘ +

dbo.Quote(@FldName) + ‘, ‘ + dbo.Quote(@SysApply) + ‘, ‘ +

STR(ISNULL(@SysDelete, 0)) + ‘ )’

SET @SQLCmdWait = @SQLCmd

SELECT @FldName = fld_name

, @FldDataType = fld_data_type

, @FldInitial = fld_initial

, @FldDecimals = fld_decimals

, @FldDesc = fld_desc

, @SysApply = sys_apply

, @SysDelete = sys_delete

, @FldUDT = fld_UDT

, @FldPrec = fld_prec

FROM user_fld uf

WHERE uf.fld_name = @FldName

SET @SQLCmd = ‘INSERT INTO user_fld ( fld_name, fld_data_type,

fld_initial, fld_decimals, fld_desc, sys_apply, sys_delete,

fld_UDT, fld_prec ) VALUES ( ‘

SET @SQLCmd = @SQLCmd + dbo.Quote(@FldName) + ‘, ‘ +

dbo.Quote(@FldDataType) + ‘, ‘ + dbo.Quote(@FldInitial)

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@FldDecimals, 0)) +

‘, ‘ + dbo.Quote(@FldDesc) + ‘, ‘ + dbo.Quote(@SysApply)

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@SysDelete, 0)) + ‘,

‘ + dbo.Quote(@FldUDT) + ‘, ‘ + STR(ISNULL(@FldPrec, 0)) + ‘ )’

PRINT @SQLCmd

PRINT @SQLCmdWait

END — Cursor Loop UserClassFld

CLOSE UserClassFldCrs

DEALLOCATE UserClassFldCrs

DECLARE UserIndexCrs CURSOR LOCAL STATIC

FOR SELECT

ui.class_name

, ui.index_name

, ui.index_desc

, ui.index_unique

, ui.index_word

, ui.sys_apply

, ui.sys_delete

FROM user_index ui

WHERE class_name = @PClassName

OPEN UserIndexCrs

WHILE @Severity = 0

BEGIN — cursor loop

FETCH UserIndexCrs INTO

@ClassName

, @IndexName

, @IndexDesc

, @IndexUnique

, @IndexWord

, @SysApply

, @SysDelete

IF @@FETCH_STATUS = -1

BREAK

SET @SQLCmd = ‘INSERT INTO user_index ( class_name, index_name,

index_desc, index_unique, index_word, sys_apply, sys_delete )

VALUES ( ‘

SET @SQLCmd = @SQLCmd + dbo.Quote(@ClassName) + ‘, ‘ +

dbo.Quote(@IndexName) + ‘, ‘ + dbo.Quote(@IndexDesc)

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@IndexUnique, 0)) +

‘, ‘ + STR(ISNULL(@IndexWord, 0))

SET @SQLCmd = @SQLCmd + ‘, ‘ + dbo.Quote(@SysApply) + ‘, ‘ +

STR(ISNULL(@SysDelete, 0)) + ‘ )’

PRINT @SQLCmd

DECLARE UserIndexFldCrs CURSOR LOCAL STATIC

FOR SELECT

uif.class_name

, uif.index_name

, uif.index_seq

, uif.fld_name

, uif.index_asc

FROM user_index_fld uif

WHERE class_name = @PClassName

AND index_name = @IndexName

OPEN UserIndexFldCrs

WHILE @Severity = 0

BEGIN — cursor loop

FETCH UserIndexFldCrs INTO

@ClassName

, @IndexName

, @IndexSeq

, @FldName

, @IndexAsc

IF @@FETCH_STATUS = -1

BREAK

SET @SQLCmd = ‘INSERT INTO user_index_fld ( class_name,

Scripting UET Definitions D-8

Modifying Infor ERP SyteLine

Copyright © 2009 Infor

index_name, index_seq, fld_name, index_asc ) VALUES ( ‘

SET @SQLCmd = @SQLCmd + dbo.Quote(@ClassName) + ‘, ‘ +

dbo.Quote(@IndexName) + ‘, ‘ + STR(ISNULL(@IndexSeq, 0))

SET @SQLCmd = @SQLCmd + ‘, ‘ + dbo.Quote(@FldName) + ‘, ‘ +

STR(ISNULL(@IndexAsc, 0)) + ‘ )’

PRINT @SQLCmd

END — Cursor Loop UserIndexFld

CLOSE UserIndexFldCrs

DEALLOCATE UserIndexFldCrs

END — Cursor Loop UserIndex

CLOSE UserIndexCrs

DEALLOCATE UserIndexCrs

DECLARE TableClassCrs CURSOR LOCAL STATIC

FOR SELECT

tc.table_name

, tc.class_name

, tc.table_rule

, tc.extend_all_recs

, tc.sys_apply

, tc.sys_delete

, tc.allow_record_assoc

, tc.active

FROM table_class tc

WHERE class_name = @PClassName

OPEN TableClassCrs

WHILE @Severity = 0

BEGIN — cursor loop

FETCH TableClassCrs INTO

@TableName

, @ClassName

, @TableRule

, @ExtendAllRecs

, @SysApply

, @SysDelete

, @AllowRecordAssoc

, @Active

IF @@FETCH_STATUS = -1

BREAK

SET @SQLCmd = ‘INSERT INTO table_class ( table_name, class_name,

table_rule, extend_all_recs, sys_apply, sys_delete,

allow_record_assoc, active ) VALUES ( ‘

SET @SQLCmd = @SQLCmd + dbo.Quote(@TableName) + ‘, ‘ +

dbo.Quote(@ClassName) + ‘, ‘ + dbo.Quote(@TableRule)

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@ExtendAllRecs, 0)) +

‘, ‘ + dbo.Quote(@SysApply)

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@SysDelete, 0)) + ‘,

‘ + STR(ISNULL(@AllowRecordAssoc, 0))

SET @SQLCmd = @SQLCmd + ‘, ‘ + STR(ISNULL(@Active, 0)) + ‘)’

PRINT @SQLCmd

END — Cursor Loop TableClass

CLOSE TableClassCrs

DEALLOCATE TableClassCrs

END — Cursor Loop UserClass

CLOSE UserClassCrs

DEALLOCATE UserClassCrs

RETURN @Severity

One of the trickier aspects of application development is dealing with null or nonexistent data. It gets complicated when the application expects a data value and gets nothing or unexpected values; that’s when you must perform coding that properly handles these situations to avoid application failure. This requires a close examination of the data wherever and whenever it’s needed. Let’s take a closer look at working with null values within the .NET Framework.

Null and void

A null value is a value that doesn’t refer to any object. It’s the default value of reference-type variables (e.g., class, interface, delegate).

These items are called reference types because they’re objects that store references to the actual data. String, object, and struct are reference type examples. The null keyword is a literal that represents a null reference with the C# .NET environment. You may use the null keyword to check or assign the value of an object. For example, the following C# code determines if a string value is null:

string sTest = “Test”;
if (sTest == null) {
Console.WriteLine(“sTest is Null”)
}

This code works without any problems with C#, but there’s no VB.NET equivalent of the null keyword. Instead, VB.NET uses the Nothing keyword. The following code demonstrates its use:

Dim sTest As String
If (sTest Is Nothing) Then
Console.WriteLine(“sTest is Null”)
End If

Another area of confusion stems from the fact that VB.NET doesn’t treat null and Nothing as equals; consequently, a VB.NET programmer may have to check for both values.

The variation in support may cause confusion, but a developer rarely develops in both languages simultaneously. On the other hand, Microsoft provides a uniform method for working with null values: The base System.Convert namespace includes the DBNull object.

DBNull

The use of the DBNull class indicates the absence of a known value. While the Microsoft documentation says it’s typically in a database application, you may also use it with any type of data.

In database applications, a null object is a valid value for a field. This class differentiates between a null value (a null object) and an uninitialized value (the DBNull.Value instance). For example, a table can have records with uninitialized fields. By default, these uninitialized fields have the DBNull value.

You can utilize the DBNull object by way of its Value property. This represents a null value, which is never equal to anything. The following C# snippet revises the previous example to utilize the DBNull class:

if (sTest.Equals(System.DBNull.Value)) {
Console.WriteLine(“sTest is null.”);

Notice how this code utilizes the string class’s equality method. The VB.NET sample may utilize the same code, like this:

If (sTest.Equals(System.DBNull.Value) Then
Console.WriteLine(“sTest is Null”)
End  If

You may also use the DBNull.Value constant on the right-hand of an assignment operation, thus storing a null value within a reference type. In addition, the .NET Framework provides the IsDBNull function (in the System.Convert namespace), which is a shorthand approach for determining if a reference type contains a null value. The following VB.NET uses IsDBNull to check an object’s value:

Dim oTest As Object = DBNull.Value
If ((IsDBNull(oTest))) Then
Console.WriteLine(“oTest is Null”)
End If

Even if you check for null values at every conceivable location within your code, they still may creep into the application. Fortunately, the try/catch exception block provides another line of defense; it allows you to catch any unhandled exceptions that null values cause. The System namespace provides the NullReferenceException for these situations. See how the following C# code uses NullReferenceException.

try {
if (sTest.Equals(System.DBNull.Value)) {
Console.WriteLine(“sTest is null.”);
}

} catch (System.NullReferenceException e) {
Console.WriteLine(“Error: ” + e.ToString());
}

Handle your data with care

Applications don’t always receive the type of data they expect; for instance, an app may be looking for integer values and receive string or null values. Therefore, since data is the backbone of an application, you must be extremely careful when working with it.

It is very often that you need to get current date/time in your development.

T-SQL

To get today date without time as string variable use following script:

Select CONVERT( CHAR(8), GetDate(), 112)

To get datetime variable of today date without time use following script:

Select CAST( CONVERT( CHAR(8), GetDate(), 112) AS DATETIME)

VB

System.DateTime.Now.ToString(“yyyyMMddhhmmss”)

Now()

To initiate a date field as today’s date in Syteline form, need to use CURDATE().

Problem:

Many business would not allow product shipped to a “PO Box” address.  So would like to block any “PO Box” address being entered into any ship to address.

Solution:

1)      Enter design mode of “Customer Ship Tos” Form.

2)      We are going to first create a script.  Go to “Menu -> Edit -> Script”, a Script window should open.  Click “New”, enter Script Name called “NoPOBox”, then click OK.

Put in the following script:

Option Explicit On

Option Strict On

Imports System

Imports Microsoft.VisualBasic

Imports Mongoose.IDO.Protocol

Imports Mongoose.Scripting

Namespace SyteLine.GlobalScripts

Public class NoPOBox

Inherits GlobalScript

Sub Main()

Dim strValue As String

Dim logicYN as boolean

Dim logicYN1 as boolean

Dim logicYN2 as boolean

Dim logicYN3 as boolean

Dim logicYN4 as boolean

strValue = GetParameter(0).ToUpper()

logicYN = strValue like “*PO BOX*”

logicYN1 = strValue like “*P.O BOX*”

logicYN2 = strValue like “*P.O.BOX*”

logicYN3 = strValue like “*PO. BOX*”

logicYN4 = strValue like “*P.O. BOX*”

If logicYN or logicYN1 or logicYN2 or logicYN3 or logicYN4

ReturnValue = “1”

else

ReturnValue = “0”

end if

End Sub

End Class

End Namespace

Then “OK” to save the Script, and “Done” to close the Script window.

3)      Create a Validator.

While we are still in the design mode of “Ship Tos” Form, from menu, go to “Edit -> Validator”.  A “Validator” window open, click “New”.  In the “Validator Properties” window, enter “NoPOBox” as name, “Run Script” as type.  Select “NoPOBox” as script name from the pull-down list, that is the script we just created in step 2).

Then, create Error Message: “PO Box Is Not Valid Address”.

Click OK all the way back, we now have created a validator called “NoPOBox”, and we can apply it to the fields that we want to validate.

4)      Apply validator

Click to enter PV(Addr_1), that means property value of Addr_1, as parameters.

5) Now save the change and exit out of design mode, we should be able to see the validation is working: