Table with with stored procedure as statement

Data Abstract
1

Hi,

creating a default WPF data abstract project I’m trying to use a sql server stored procedure for “Statement”.

The stored procedure is quite simple

create PROCEDURE [dbo].[TEST_SP]
(
@Parm1 varchar(255)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT @Parm1 as F1, 0 as F2, 1 as F3, 2 as F4;
END;

In Schema Modeler I’ve created a NewTable table with the above stored procedure as statement.

Using this line of code in MainWindow

this.fDataModule.DataAdapter.GetTable(new DataParameter { new DataParameter() { Name = “Param1”, Value = “val1” } }).ToList();

an exception raises:

  1. in case I map that SP call as a SQL statement

RemObjects.SDK.Types.ServerException: ‘An exception occurred on the server: Incorrect syntax near the keyword ‘exec’.
Incorrect syntax near ‘)’.’

  1. in case I map that SP call as a Stored Procedure

RemObjects.DataAbstract.DASchemaException: 'An exception occurred on the server: Incompatible Statement type StoredProcedure is used for table ’

What am I doing wrong?

2

It seems that the problem is caused in “PlainTableSqlStatement”.
Initially I tried to use a SQL statement and then I switched to a stored procedure (probably the schema modeler in case of exception restores the previous value, in this case SQL).

Restarting the project from stored procedure I have this error:

image
image.png948×668 29.5 KB

But again…if I switch to SQL >goto [firstpost]

3

Hello

Please take a look at the attached testcase: Testcase15578.zip (115.7 KB)

This code line does the trick:

this.fDataModule.DataAdapter.UseDynamicWhere = true;

This code line puts the data adapter in the mode where it uses DynamicWhere to express data requests.

In this mode one can request data from Delphi servers (that don’t support DA SQL) and from StoredProdecure-based tables.

Limitations are that in this mode queries cannot contain JOIN, GROP-BY or ORDER statements.

Additionally StoredProcedure-based tables also have a very significant limitation:

One cannot use any where conditions in the queries to such tables. This means that this query will be executed successfully:

var query = from x in this.fDataModule.DataAdapter.GetTable<TestTable>
                (new DataParameter[] { new DataParameter() { Name = "Parm1", Value = "val1" } })
                select x;

while this one will fail:

var query = from x in this.fDataModule.DataAdapter.GetTable<TestTable>
                (new DataParameter[] { new DataParameter() { Name = "Parm1", Value = "val1" } })
                where x.F1 == "AAA"
                select x;

The failing metadata request for StoredProcedure statement type is a bug. I’ll log it and will try to fix ASAP.

This is how I added the Schema Table definition:

  1. Open the Schema Modeler by dbl-clicking the .daSchema file

  2. Right-click the Tables node and select Add command in the context menu

  3. Change the table name to TestTable

  4. Expand the table node and right-click the Statements sub-node

  5. Select Add command in the context menu

  6. For the statement added change Statement Type to SQL

  7. Set the SQL statement to

    EXEC [dbo].[TEST_SP]  :Parm1

  8. Switch to the Mappings tab

  9. When asked to recreated parameters confirm the action

  10. Double-check the parameter type (set the correct one if needed)

  11. Go back to the statement SQL statement and press the ‘Validate’ button

  12. Open the ‘Mappings’ tab and press the ‘Recreate Fields’ button

Steps 2-12 can be simplified - just drag the stored procedure form the Connection Manager to the Tables node. This will do essentially the same.

1 Like
4

Thanks, logged as bugs://79631

5

It’s working, thank you.

By the way, where can I find the documentation for this behaviour? Probably I’ve missed something.

6

Seems this part is missing from the documentation. Will be fixed too

7

And what about Remote/LocalAdapter? The previous solution doesn’t work for that.

8

Hello

This is the client code tat uses RemoteDataAdapter:

var adapter = new RemoteDataAdapter("http://localhost:8099/bin");
var table = new DataTable("TestTable");
adapter.Fill(table, new TableRequestInfoV5 { IncludeSchema = true, Parameters = new DataParameter[] { new DataParameter() { Name = "Parm1", Value = "val1" } } }, true);
MessageBox.Show($"{table.Rows.Count} row(s) fetched");

It successfully reads data from the server.

Regards

1 Like
9

bugs://79631 got closed with status fixed.

1 Like