Schema Modeler and Stored Procedures (again)

Data Abstract
1

Hi,

Revisiting this post I made a couple of years ago, I find I again need to try and get a stored procedure working as a plain table definition. The reason for having to try a stored procedure is that I need “OPTION (RECOMPILE)” on the end of my MSSQL statement and that messes up the query in DA, hence wrapping in a stored procedure.

Using the workaround in the previous topic, the definition works and the stored procedure executes correctly from within schema modeler :-

R1.jpg840×558 131 KB

However, I never bottomed out how to successfully call that table from the client side. My call at the moment is as part of a dataset fill :-

	RemoteDataAdapterR.Fill(cTagDataSet,	['TabRF002','TabRF003','TabRF004','TabRF005','TabRF006','TagAnalysis','TagSales','NodeData'],
													[lReq_TabRF002,lReq_TabRF003,lReq_TabRF004,lReq_TabRF005,lReq_TabRF006,lReq_Gen001,lReq_Gen002,lReq_Gen003]);

Where TabRF005 is defined as the stored procedure. The client side remains unchanged from the situation before I tried wrapping the statement in a stored procedure. The SQL has UNION statements which required it to be custom SQL in the first place within the schema model rather than on the client side.

When I execute the above data adapter fill (using a simple select * from TabRF005 as the SQL behind the TableRequestInfoV6 structure, I get the following :-

R2.jpg871×804 197 KB

Looking at SQL server profiler, no preparation or execution of the SQL is taking place, so I’m assuming this is still buried in the DA architecture. I could probably code around this with a custom method on the server, but I’d prefer not to break my client code to that extent if possible, especially when all I’m effectively trying to do is add “OPTION (RECOMPILE)” onto an existing working SQL statement.

I’m currently using version 8.2.89.1153 of DA. Am I missing something obvious ?

Thanks,

Paul.

2

Hello

As I inderstood you try to execute a parametrized DA SQL statement where the Schema Table mentioned in it has a Statement defined as a stored procedure call.
Unfortunately this scenario is not supported. SP-based tables cannot be called via DA SQL.

3

Thanks for the reply anton.

How would you make use of a stored procedure based table on the client side?
What mechanism would you use to pass the parameters and retrieve the table generated ?
As I said, I can make use of a custom server method to code around, but is there an easier way to get SQL statement level command OPTION (RECOMPILE) in to a DA query ?

I can understand the SP table not being supported as you say, but equally, I could see that the client might be agnostic as to how the table was presented by the server. One of your own blog posts almost (but not quite) says this :-

This the easiest scenario. At the same time, Data Abstract makes it possible to, for example, define a Schema table that exposes data from a complex SQL query involving joins of several tables as a plain table. Given that the needed SQL statements (either stored procedures or plain SQL statements) are defined to handle insert, update and delete operations, the client application will work with the data as if it was a simple plain table. Furthermore, if in the future the data source structure will be changed (for example if a data table containing the needed data will be introduced to the database schema) it would be possible to point the Schema table to this changed data source without changing a line of code in either the server or the client application.

4

Parametrized SELECT statement in the Schema Table Statement. Btw DA SQL will work then for this table.

You can access it via older TableInfoV5 requests. They do not use the DA SQL requests.

5

Thanks Anton

TableRequestInfoV5 was what I needed, along with something close to a sample from TableRequestInfoV5 subselect param

Paul.