server-side paging of grids with data coming from stored procedure

Carolyn Anderson
Carolyn Anderson
in AppWorks #1

Create a business object that gets its data from a stored procedure. Create a grid on a form and bind it to the business object. Set the business object's page size to something other than 'None'.

 

When it runs, the form produces an 'incorrect syntax' error and your form will not open. This is because it creates a dynamic sql statement to count the number of records in the result set to make sure it doesn't exceed the paging limit. But it can't wrap an 'Exec() function inside a sp_executesql function, so this causes a failure when you use server-side paging with a stored prcedure.

 

The form works with client-side paging instead of server-side paging. But we don't necessarily want that if we've got large result sets because it slows the form load down.

 

At some point, please can there be an alternative method for implementing server-side paging of business objects which doesn't cause an error when used with stored procedures? It would help with performance of forms with large datasets to process.

Tagged:

Comments

  • Jerome_Pearce
    Jerome_Pearce
    #2

    Can you describe how that Exec() is employed for paging? I am very interested to see how that iaa built.

  • Carolyn Anderson
    Carolyn Anderson
    #3

    The stored procedure behind my business object is like this:

     

    Exec

    ('[dbo].[spSearchByStatus] @MapNameSearch = '''+@Parameter1+;''', @StageSearch = '''+@Parameter2+;''', @CreationDateSearch = '''+@Parameter3+;''',@EntryTimeSearch='''+@Parameter4+;''', @PrioritySearch = '''+@Parameter5+;'''')

     

    When I put the page size on the business object, I get this out of the sql log:

     

    exec sp_executesql N'SELECT COUNT(*) FROM ( EXEC(''[dbo].[spSearchByStatus] @MapNameSearch = '''''' + @P1 + '''''', @StageSearch = '''''' + @P2 + '''''' , @CreationDateSearch = '''''' + @P3 + '''''', @EntryTimeSearch='''''' + @P4 + '''''', @PrioritySearch = '''''' + @P5 + '''''''')   ) q',N'@P1 nvarchar(1),@P2 nvarchar(1),@P3 nvarchar(10),@P4 nvarchar(10),@P5 nvarchar(1)',NULL,NULL,N'25/10/2009',N'18/10/2010',N'9'

     

    ... and an error message 'Incorrect syntax near the keyword 'EXEC'.'

  • Carolyn Anderson
    Carolyn Anderson
    #4

    ... which means that when I set the page size for the business object, it's trying to count the number of records coming out of my stored procedure and getting the sql wrong.

     

    I'm not using stored procedure for paging, I just set the page size on the business object so that it wouldn't return lots of records to the client and slow the form down. Metastorm system is doing the paging.

  • Jerome_Pearce
    Jerome_Pearce
    #5

    I wonder if you tried the Exec @1, @p2 etc...

     

    ie without the sp name & named params in  quotes, would  it work? I understand that you need to do it this way to use named paramaters, I was just wondering if it would work is all.

  • Thomas_Prinsloo
    Thomas_Prinsloo
    #6

    Has anyone had any luck on executing a stored procedure from a BO, and overcoming the issue as described above? 

     

    We're running into the same issue with the SELECT COUNT(*) FROM (EXEC SP @p1 @p2)  where it throws the syntax error from SQL, as the above statement is indeed not parsable.

    Is it possible to somehow bypass the SELECT COUNT(*) statement which the engine executes? Paging on my BO is set to None. 

     

  • Paul Denton
    Paul Denton E mod
    #7

    Hi,

     

    So basically when Server Side Paging is enabled for a specific instance of a BO it prompted the engine to run:

     

    SELECT COUNT(*) FROM () as q

     This is to find the size of the dataset to work how many pages there are.

     

    However as soon as you have something that will not work as a sub-query it will break this statement.  We first noticed this with Query BO's with a JOIN where the same column name is being returned multiple times in the query, i.e:

     

    SELECT eFolder.eFolderID, eFolder.eFolderName, eFolder.eStageName,
  eFolder.eMapName, eFolder.eOriginator, Migration_Test.Check1, eEvent.eMapName
FROM eFolder LEFT JOIN
  Migration_Test ON eFolder.eFolderID = Migration_Test.EFOLDERID RIGHT JOIN
  eEvent ON eEvent.eFolderID = eFolder.eFolderID

     This resutls in the engine trying to run: 

     

    select COUNT(*) FROM (SELECT eFolder.eFolderID, eFolder.eFolderName, eFolder.eStageName, 
eFolder.eMapName, eFolder.eOriginator, Migration_Test.Check1, eEvent.eMapName 
FROM eFolder LEFT JOIN Migration_Test ON eFolder.eFolderID = Migration_Test.EFOLDERID 
RIGHT JOIN eEvent ON eEvent.eFolderID = eFolder.eFolderID) as q

Result:
Msg 8156, Level 16, State 1, Line 4
The column 'eMapName' was specified multiple times for 'q'.

     In the case of a SP call we see:

     

    select COUNT(*) FROM (exec MyTestProc) as q

Result:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'exec'.

     

    Presently the only way to avoid this situation is to ensure paging is set to "None" for BO's with queries that will break as a sub-query.

     

     

     * Note the the setting for Paging on the BO itself is just the Default setting, the actually value being used is set on the instance of the BO in the Data Access view for the form where the BO is used.   Setting the Deault value back to "None" does not automatically change the setting on instances of the BO already bound to forms.

Categories

image
OpenText CE Products
AppWorks
Gateway & Platform