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

Sometime, while you are making modification on a Syteline form in design mode, system would crush on you.  Even after you log out and log back in, and try get into the design mode again, the error message would keep coming up, preventing you using any function in design mode. 

This usually only happen to your PC.  To fix it, do the following:

1) log off Syteline.

2) Go to C:\Documents and Settings\YOUR WINDOW USER NAME\Local Settings\Application Data\Infor\WinStudio.  You should see two xml files there.  Delete them.

3) Log back in Syteline, go to design mode.  You should now see anything working OK again.

In Syteline 8, some time in some forms, the horizontal scrollbar in Grid mod would some how missing.  User can not see all records in the data set.

This often due to that when developer making modification to the form, some how make the height of Grid mod screen disagree with the height of the main screen.  To correct that, enter into design mod, in the form property, copy down whatever value in the Height property.  Then click on the grid to go to FormCollectionGrid, locate the height property, paste the value in.  This way, you make sure the height for both Grid and main screen are the same.  This should fix the problem