> your error indicates that you're trying to do update or delete

No, this was a plain and simple select.

RBS

> I just ran the queries using DB Workbench against one of my existing
> databases. However, your error indicates that you're trying to do update
> or delete, and I only tried a SELECT...
>
> Set
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of RB Smissaert
> Sent: 29. september 2008 14:08
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] No current record for fetch operation
>
> Thanks; will have a good look at that.
>
> I did check the produced data and it seemed OK, but will check again.
>
> How did you run the first query? I can't run that due to the mentioned
> error.
>
>
>
> In FlameRobin:
>
>
>
> Message: isc_dsql_execute failed
>
>
>
> SQL Message : -508
>
> The cursor identified in the update or delete statement is not positioned
> on a row.
>
>
>
> Engine Code : 335544348
>
> Engine Message :
>
> no current record for fetch operation
>
>
>
> Execute time: 00:00:00.
>
>
>
>
>
> RBS
>
> _____
>
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
> Sent: 29 September 2008 12:38
> To: firebird-support@yahoogroups.com
> Subject: RE: [firebird-support] No current record for fetch operation
>
>
>
> I was confused by this mail, and a tiny bit of testing made me even more
> confused. On a Firebird 1.5.4 database,
>
> 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'
> order by 1
>
> returns 260 rows, all with R.RDB$RELATION_ID 135. Changing the order of
> the
> JOINs (no change in content, and hence, it should make no difference to
> anything except possibly the plan the optimizer uses) to
>
> 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
> INNER JOIN RDB$RELATIONS R ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
> 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
> WHERE T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
> order by 1
>
> I still get 260 rows, but now the value of R.RDB$RELATION_ID varies. Doing
>
> 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
> INNER JOIN RDB$RELATIONS R ON RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME
> INNER 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
> WHERE T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
> order by 1
>
> seems to produce the same result as the above select.
>
> Hence, I suspect your original select produce an incorrect result in
> Firebird 1.5.4!!! Sounds like something worth looking for in the bug
> tracker
> or release notes for Firebird 1.5.5 and report if not found there.
> Unfortunately, I doubt I'll have time to do this today,
> Set
>
> -----Original Message-----
> From: firebird-support@ <mailto:firebird-support%40yahoogroups.com>
> yahoogroups.com [mailto:firebird-support@
> <mailto:firebird-support%40yahoogroups.com> yahoogroups.com] On Behalf Of
> RB
> Smissaert
> Sent: 28. september 2008 21:38
> To: firebird-support@ <mailto:firebird-support%40yahoogroups.com>
> yahoogroups.com
> Subject: RE: [firebird-support] No current record for fetch operation
>
> I had a look at this:
>
>> I think that makes the LEFT JOIN actually be an INNER JOIN
>
> When I change the left join into an inner join I only get the unique field
> names and miss out on the duplicate field names
>
> due to one field being in more than one index. So, I think I need the SQL
> as
> it is and I can see no way to work it round
>
> so that the left join comes last.
>
> Maybe somebody can prove me wrong.
>
> Does the SQL run in FB 2.x is?
>
> RBS
>
> _____
>
> From: firebird-support@ <mailto:firebird-support%40yahoogroups.com>
> yahoogroups.com
> [mailto:firebird-support@ <mailto:firebird-support%40yahoogroups.com>
> yahoogroups.com] On Behalf Of Svein Erling Tysvaer
> Sent: 28 September 2008 18:44
> To: firebird-support@ <mailto:firebird-support%40yahoogroups.com>
> yahoogroups.com
> Subject: Re: [firebird-support] No current record for fetch operation
>
> Hi!
>
> I agree that Firebird (at least 1.5) poorly handles situations where
> LEFT JOIN is written before INNER JOIN. However, I find your particular
> SQL a bit contradictory:
>
> When doing INNER JOIN RDB$TYPES T ON (F.RDB$FIELD_TYPE = T.RDB$TYPE),
> you basically say that there must be a record in both F and T, whereas
> you in the line before has said that F may not exist. I think that makes
> the LEFT JOIN actually be an INNER JOIN and in general I'd say it is
> wrong to use the right table of a LEFT JOIN in an INNER JOIN (*).
>
> That being said, the error message you report doesn't sound like a
> sensible error message for the situation and I think Firebird ought to
> be able to execute your query anyway (and I don't know whether this is
> what causes the error message or not). Though I don't know what causes
> the error, if it has to do with the dialect or something else.
>
> If your third line had contained a reference to RF and not F (thereby
> making the whole statement more sensible), I'd say it could be an error
> worth looking more into (note, I haven't checked any bug tracker, so it
> may already be a known issue).
>
> Set
>
> (*) I sometimes do similar things myself, but then when I know a record
> must exist in one of several tables:
>
> SELECT D.* FROM A
> LEFT JOIN B on A.A = B.A
> LEFT JOIN C on A.A = C.A
> INNER JOIN D on D.B = coalesce(B.B, C.B)
>
> RB Smissaert 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.
>> There is no such problem with IB 5.6.
>>
>> It is not a major problem as I can do multiple selects in the
>> application
>> 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?
>>
>>
>> RBS
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebird <http://www.firebirdsql.org> sql.org and click
> the
> Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoeni
> <http://www.ibphoenix.com> x.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>
>