| Subject | Re: [firebird-support] No current record for fetch operation |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-09-28T11:26:34Z |
At 20:54 28/09/2008, you wrote:
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
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
>Using FB 1.5 on a converted IB 5.6 dialect 1 database.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.
>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.
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 applicationI don't get this, but I don't think I need to...
>and join in SQLite,
>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