I am trying to get the max value of a column in Interbase 7 using this code:

int maxValue = (int)adapterApm.GetScalar(MaterialesFields.Id,null,AggregateFunction.Max,null);

I have made that with a stored proc before, but I want to learn to use it in LLBL, however I have been trying using help, and I get this:

An exception was caught during the execution of a retrieval query: Dynamic SQL Error SQL error code = -104

On the error Detail, the QUERY EXECUTED IS THIS:

"\r\n\tQuery: SELECT FIRST 1 MAX(\"MATERIALES\".\"ID\") AS \"Id\" FROM \"MATERIALES\"\r\n"

After runing that on Interbase

SELECT FIRST 1 MAX (MATERIALES.ID) AS Id FROM MATERIALES

I am getting errors, I use to make it as this:

SELECT MAX (MATERIALES.ID) AS Id 
FROM MATERIALES

I would like to know if I am calling it the wrong way or what may be my error?

I did run the same code for SQL server and worked ok, so I think it may be my driver or something.

Regards

After runing that on Interbase Code: SELECT FIRST 1 MAX (MATERIALES.ID) AS Id FROM MATERIALES

I am getting errors

Please post those errors.

Interbase 7 isn't really supported (we support interbase through the firebird provider, which is a database based on interbase 6). However, the FIRST 1 is the top specification which makes it go haywire. If you upgrade to v2.5, we've fixed this by not emitting the TOP (FIRST 1) specification in a scalar query anymore.

Thanks for your response.

Walaa, the error I get is:

Invalid token. Dynamic SQL Error. SQL error code = -104. Token unknown - line 1, char 13. 1.

Otis: I am using v2.5 (sept 24 final), and I got the query that I posted, is there a way to check if the drivers or something is not ok?

Can I modify something to make it work on Interbase 7.1? Basically everything works except for this and some other things I found in Stored proc but I dealed with them and keep it working.

I am still working ok since I made a stored proc to get the max, but I would like to know if I can make it work for Interbase.

The query that works for IB 7.1 is:

SELECT MAX (TABLE.ID) AS Id FROM TABLE

The scalar query shouldn't emit the top 1 value into the query (which is in firebird the FIRST 1, which isn't an interbase feature). I'll check it out, looks like a bug in the runtime lib of ours.

The change we added in v2.5 to scalar queries not being emitting a limit of 1 was for scalarqueryexpressions in select lists, which did hurt performance and in some cases would cause bad queries on some databases.

GetScalar is a method which does emit the limit of 1 into the query because the set where the GetScalar obtains the scalar from can be many rows. To emit the limit, the data transfered to the client is smaller.

So changing this behavior isn't going to happen in v2.5. However, I can offer you a workaround so you don't have to write a stored proc for every scalar query.

• derive a class from DataAccessAdapter.
• in that derived class, override protected virtual void OnGetScalar(IRetrievalQuery scalarQuery), like the following:

protected override void OnGetScalar(IRetrievalQuery scalarQuery)
{
    if(scalarQuery.Command.CommandText.StartsWith("SELECT FIRST 1"))
    {
        scalarQuery.Command.CommandText = "SELECT " + scalarQuery.Command.CommandText.SubString(13);
    }
    base.OnGetScalar(scalarQuery);
}

(I might have made a mistake with the index to strip out the FIRST 1, but you get the idea).

for your scalar queries, use the derived class as the adapter class instead of the normal DataAccessAdapter class