Otis, This is a quote from our other SQL Functions thread, which is getting very long and complex - so I broke this thread out.

4) In SQL Server, function calls are broken up into two groups - one returns Scalar values and the other returns a table/resultset. Given ones that return scalar values, is there a way to get the values from those functions without putting the result in a DataTable? For example, lets say I want this query: SELECT GETDATE()

There is no table involved in this and it returns a one row, one column result, perfect for a ExecuteScalar call. Current code to do such a query would be:

Code: private DateTime GetSQLServerDate() { ResultsetFields fields = new ResultsetFields(1); fields.DefineField(WhatTableFieldIndexHere.FieldName, 0, "TheDate"); //????? fields[0].ExpressionToApply = new DbFunctionCall("GETDATE", null); TypedListDAO dao = new TypedListDAO(); //dao.ExecuteScalarQuery() DataTable results = new DataTable(); dao.GetMultiAsDataTable(fields, results, 1, null, null, null, true, null, null, 0, 0); if (results.Rows.Count > 0) return (DateTime)results.Rows[0][0]; return null; }

what goes in the second line for the field we are defining

Use: fields.DefineField(new EntityField("TheDate", new DbFunctionCall(...)));

What you gave me for using DefineField - does not work. It generates the following SQL code:

select GETDATE() as TheDate FROM []

which of course results in a SQL error...The correct syntax should be:

select GETDATE() as TheDate

Currently, I am using this code which works...

        ResultsetFields fields = new ResultsetFields(1);
        fields.DefineField(AnyEntityInProject.AnyFieldName, 0, "TheDate"); //?????
        fields[0].ExpressionToApply = new DbFunctionCall("GETDATE", null);

That should result in the same data... hmm.

The difference seems to be, there is not a 'Table' for the 'From' clause if you dont use one of the fields created from the factory. So, when it generates the SQL, the table name is blank.

What I'm puzzling with is that you say the FROM clause isn't generated in your last snippet. I don't see how that can be possible (but have to try)

Just to be clear -

If you want to call GetDate() function in MSSQL you can use the following syntax

select GETDATE() as TheDate

Using the field definition you gave me, your product generates:

select GETDATE() as TheDate FROM []

Note the empty brackets [] at the end.

In this case we need the FROM clause to not be generated. Your product always generates the FROM clause - which in this case is a problem!

yes I know it generates always the FROM clause. However, in which situation do you want to execute such a scalar query?

I have UDF's. One such example is:

GetPriceForAPolicy(various parameters)

I call this functions from queries and other such stuff. I also have a button on a webform, when users click calculate - it calls this function to get the price.

so, I need a DQE sql statement like:

select GetPriceForAPolicy(various parameters) as Price

In your DQE code, if there the FROM part is blank, then can you just leave it all out? If you cannot determine the FROM clause anyway - it is probably invalid. Just like the WHERE - if you dont have a WHERE part, you leave out the WHERE keyword.

Like I said, it is easy to workaround - I just put ANY Enitity.EntityFieldName in for the field. (Only problem is my general 'GetScalar(IExpression expressionToApply)' function cannot be properly generated - since it has to include an entity.entityfieldname - does not work across projects...So it is a big issue long term, but short term easy workaround)

In Oracle, you use:

select sysdate from dual

In DB2, you use:

select user from sysibm.sysdumm1

and of course in MSSQL, you just leave off the FROM clause...

Ah now I see why that random entityfield approach works, because it will generate a from clause.

I've modified the SQLServer DQE code a bit, and it seems to work fine. I'll add this for oracle, db2 and sqlserver, and I've to check if other db types support this kind of function calling. I suspect postgresql does too, however I'm not sure.

It's btw SYSIBM.SYSDUMMY1 (for the occasion you're looking at the horrific error db2 will spit out and you wonder #(*&$@($@( is wrong!)

(edit) turns out, all databases support this in one way or the other (simply SELECT something or SELECT something FROM systemobjectwhichmimicsatable)

I've modified the SQLServer DQE code a bit, and it seems to work fine

Excellent. Let me know when that build is posted. Thanks.

It's now available.

Thanks and it works.

BTW, I like your new download section with the release notes of what was fixed and such - very nice.

It still does not work in Oracle.

            
ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(new EntityField2("NOW", new DbFunctionCall("SYSDATE", null)), 0);

using (DataAccessAdapter adapter = new DataAccessAdapter())
{
     DataTable table = new DataTable();
     adapter.FetchTypedList(fields, table, null);
}

The code generates the following sql:

SELECT SYSDATE() AS "NOW" FROM DUAL

which fails with "ORA-00923: FROM keyword not found where expected" exception.

There must not be parenthesises after SYSDATE.

sysdate isn't a function, it's a variable. try calling a scalar udf. The intention for this feature is to be able to call scalar functions. If a variable has to be selected, that's indeed unfortunate, but not possible, or you have to create your own DbFunctionCall like class, which emits the variable call without the parentheses.

@wayne: thanks The changelogs were available for some time though BUt it's now much easier to see the link I think.