Hello,

I use firebird 2.0 and dynamic lists.

The stored procedure GETSTOCK has only one output parameter which is named "QTE_STOCK". I'd like to call the proc and use its result in the FROM clause of my Subselect :

SELECT 
ARTICLES.PLU,
ARTICLES.COLOR,
ARTICLES.SIZE,
(
     SELECT QTE_STOCK FROM   GETSTOCK(ARTICLES.PLU)
 ) AS qty

FROM ARTICLES

It looks like it's impossible, can anyone confirm that? Here's a piece of what I've tried (which doesn't work)

            

DbFunctionCall func = new DbFunctionCall("GETSTOCK", new object[]{ ArticlesFields.PLU } );
EntityField2 qte_stock = new EntityField2("qte", func);
ScalarQueryExpression subQuery = new ScalarQueryExpression(qte_stock);

ResultsetFields fields = new ResultsetFields(4);
fields.DefineField(ArticlesFields.Plu , 0);
fields.DefineField(ArticlesFields.Colour , 1);
fields.DefineField(ArticlesFields.Size , 2);
fields.DefineField(QtyStocksFields.Qty, 3);

fields[3].ExpressionToApply = subQuery;

It should works if you create a User DB Function instead of a SProc (you can call Sproc inside DBFunction).

You mean an UDF library?

Yeah... I didn't have used Firebird at all, but I don't found any limitation in LLBLGenPro/Firebird funcions. Here some guy use DBFunctionCall for Firebird: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=9975

It should work with a UDF I think (sorry if I missing something specific about firebird )

But he uses the EXTRACT function which isn't an sproc but a built-in database function just like SUBSTR() or LEFT() are.

My problem is that the query doesn't execute if there is a sproc call directly in the main select clause, An sproc can only act as a table for a select.

I would give up if I had to write UDFs in delphi, I have no experience with that and I don't like the fact that it requires end-user actions to deploy.

Thanks for your help anyway. I hope someone has a solution.

You can't use a stored proc in a select statement, that's the rule the db engines put forward. So either you have to write the proc's SQL in C# code with predicates etc. or you have to create a scalar udf in the db which calls the proc. THere's no way to produce valid sql for the query you want to execute otherwise.

The SQL statement in my first post is valid and executes correctly when it is sent using an fbcommand object. But doesn't matter, it's ok if I have to do it by hand.

So either you have to write the proc's SQL in C# code with predicates etc

Could you please elaborate? I'm curious. Thanks in advance

What we are trying to say if that as SPROC's calls aren't allowed in a SELECT queries, in this case you have two options:

A. Write a UDF, and inside of it you will call your SProc, so UDF will return the same result as your SProc do. Then you can use a LLBLGenPro DBFunctionCall to incluide in your retrieve process:

DbFunctionCall func = new DbFunctionCall("GETSTOCK_UDF", new object[]{ ArticlesFields.PLU } );
EntityField2 qte_stock = new EntityField2("qte", func);
...

B. Construct a C#/LLBLGenPro retrieve routine with PredicateExpressions, Relations, GroupBy objects, etc. If you will take this way, please read LLBLGenPro Help - Using generated code - Adpater - Getting started with filtering. Almost every SQL code can be rewritten in .NET/LLBLGenPro code.

A: Thanks a lot but the way to do it with an UDF library was perfectly clear.

B: I can't figure how I can achieve my goal using PredicateExpressions... Or did you mean that I should try to use standard SQL which could be written using llblgen objects instead of a stored proc ?

stefcl wrote:

Or did you mean that I should try to use standard SQL which could be written using llblgen objects instead of a stored proc ?

Yes I did ... As I said before, would be a good start read LLBLGenPro Help - Using generated code - Adpater - Getting started with filtering, then you can try or let us know to assist you in constructing you resulset in a LLBLGenPro-based way.

Regards

Ok sorry for my misunderstanding. Thank you.