Thanks for the quick reply.

> I don't get this, but I don't think I need to...

No, this is just a non-SQL, application solution to the problem. Run
separate queries without the joins, move the data

to SQLite and do the joining in there.

> Just move the inner joins up so that they are the first and second

streams.
Tried that, but couldn't figure out how to do it with the same results.

> Also, prevent the late filtering of RDB$TYPES (after the inclusion of

nulls in the output stream) etc.

Good idea and thanks for the tip.

> If you could be patient a day or two, until Dmitry returns from Italy,

Will do.





RBS







_____

From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Helen Borrie
Sent: 28 September 2008 12:27
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] No current record for fetch operation



At 20:54 28/09/2008, you wrote:

>Using FB 1.5 on a converted IB 5.6 dialect 1 database.
>I connect with the ODBC driver from IBPhoenix and this is with ADO in VB6.
>
>When I run this SQL:
>
>SELECT
>R.RDB$RELATION_ID,
>RF.RDB$RELATION_NAME,
>RF.RDB$FIELD_NAME,
>T.RDB$TYPE_NAME,
>RF.RDB$FIELD_POSITION,
>F.RDB$FIELD_LENGTH,
>RF.RDB$SYSTEM_FLAG
>FROM
>RDB$RELATION_FIELDS RF
>LEFT JOIN RDB$FIELDS F ON (RF.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME)
>INNER JOIN RDB$TYPES T ON (F.RDB$FIELD_TYPE = T.RDB$TYPE)
>INNER JOIN RDB$RELATIONS R ON (RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME)
>WHERE
>T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
>
>I get the error message:
>
>[ODBC Firebird driver][Firebird] No current record for fetch operation
>
>And I understand that this is caused by the left join before the 2 inner
>joins. Indeed if I change the left join to an inner join it runs fine.

You don't need to change the left join to an inner join. Just move the inner
joins up so that they are the first and second streams.

Also, prevent the late filtering of RDB$TYPES (after the inclusion of nulls
in the output stream) by moving the "match" criterion up into the JOIN
between it and RDB$FIELDS:

INNER JOIN RDB$TYPES T ON (F.RDB$FIELD_TYPE = T.RDB$TYPE
and T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE')
INNER JOIN RDB$RELATIONS R ON (RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME)

You might find that this alone will solve the problem, without having to
modify the stream sequence....or not...I don't recall exactly its effect in
1.5.x

>There is no such problem with IB 5.6.

It's a different database engine. You can do a lot of things in IB 5.6 that
shouldn't work (and that actually don't work in any sense except that they
return a set when they should not!)

>It is not a major problem as I can do multiple selects in the application
>and join in SQLite,

I don't get this, but I don't think I need to...

>but I just wonder if this has been fixed in FB 2.0 or 2.1 or has this to do

with the database dialect?

It has to do with the way the optimizer works. There were major optimizer
changes in Fb 2.0 but whether this specific behaviour was altered I could
not guess.

If you could be patient a day or two, until Dmitry returns from Italy, it is
very likely he would explain exactly what happens under these conditions.

./heLen





[Non-text portions of this message have been removed]