hm.. don't know if it's worth. If you want do reproduce it:

create a new DB / Tabel with some fields and this SP:

SET TERM ^ ;

create or alter procedure ADD_TABLE_FIELD (
TABLE_NAME varchar(50) collate DE_DE,
FIELD_NAME varchar(50) collate DE_DE,
FIELD_DOMAIN varchar(50) collate DE_DE)
as
declare variable VAR_TABLE_REL_ID integer;
declare variable VAR_DOM_TYPE_ID integer;
declare variable VAR_FIELD_ID integer;
declare variable VAR_FIELD_POS integer;
begin
/* find target table */
select RDB$RELATION_ID
from RDB$RELATIONS
where (Upper(RDB$RELATION_NAME) = Upper(:TABLE_NAME))
into :VAR_TABLE_REL_ID;
if (:VAR_TABLE_REL_ID is null) then exit;
/* find domain */
select RDB$FIELD_TYPE
from RDB$FIELDS
where (Upper(RDB$FIELD_NAME) = Upper(:FIELD_DOMAIN))
into :VAR_DOM_TYPE_ID;
if (:VAR_DOM_TYPE_ID is null) then exit;
/* check if field already exists */
select RDB$FIELD_ID
from RDB$RELATION_FIELDS
where (Upper(RDB$FIELD_NAME) = Upper(:FIELD_NAME))
and (Upper(RDB$RELATION_NAME) = Upper(:TABLE_NAME))
into :VAR_FIELD_ID;
if (:VAR_FIELD_ID is not null) then exit;
/* get field position for target table */
select Count(RDB$FIELD_ID)
from RDB$RELATION_FIELDS
where (Upper(RDB$RELATION_NAME) = Upper(:TABLE_NAME))
into :VAR_FIELD_POS;
if (:VAR_FIELD_POS is null) then VAR_FIELD_POS = 1;
else VAR_FIELD_POS = :VAR_FIELD_POS + 1;
/* create field entry / DBMS sets RDB$FIELD_ID and RDB$RELATIONS.RDB$FIELD_ID */
insert into RDB$RELATION_FIELDS (
RDB$FIELD_NAME,
RDB$RELATION_NAME,
RDB$FIELD_SOURCE,
RDB$FIELD_POSITION,
RDB$SYSTEM_FLAG
)
values (
Upper(:FIELD_NAME),
Upper(:TABLE_NAME),
Upper(:FIELD_DOMAIN),
:VAR_FIELD_POS,
0
);
end^

SET TERM ; ^


Execute the SP in a script or in one transaction (means AutoCommit = false):

EXECUTE PROCEDURE ADD_TABLE_FIELD('D_PAT_PATIENT', 'D_TEST1_137', 'DOM_INTEGER');
EXECUTE PROCEDURE ADD_TABLE_FIELD('D_PAT_PATIENT', 'D_TEST1_138', 'DOM_INTEGER');
EXECUTE PROCEDURE ADD_TABLE_FIELD('D_PAT_PATIENT', 'D_TEST1_139', 'DOM_INTEGER');
Commit;


Now look at the System tables:
Select rdb$Field_name, rdb$Field_Position, rdb$Field_ID from rdb$Relation_Fields
where rdb$Relation_Name = 'D_PAT_PATIENT'
order by rdb$Field_Position

and the new fields should have the same position.

Hint: Firebird Server 2.1.7 But I don't think it differs in FB 2.5....

In Firebird 3 the System Tables are read only - so in newer FB-Installations this problem shouldn't show up....

best regards...

Von: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]
Gesendet: Mittwoch, 6. Februar 2019 17:38
An: IBObjects@yahoogroups.com
Betreff: RE: [IBO] IBO 5.9.9 B2784: wrong sequence in Update statement / parameterlist



Oh wow, thank you for giving this description of things.
Ideally, IBO wouldn't get affected and would still work correctly.
I'm wondering if this is worth my time to fix in IBO anyway.
Trouble is, I'm not sure what I would do to fix it....

I'm open to ideas or we can just let it be.

Thanks,
Jason Wharton
www.ibobjects.com


-----Original Message-----
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]
Sent: Wednesday, February 06, 2019 9:20 AM
To: IBObjects@yahoogroups.com
Subject: AW: [IBO] IBO 5.9.9 B2784: wrong sequence in Update statement /
parameterlist

Sorry Jason, it's not a bug in IBO

My nice fellows think it's good to add new fields to a table with alter the
system tables.
Yes I'm guilty that I failed to abolish this practice in recent years :-(
God I hope we can move as fast as possible to FB 3....

So what happen:

We have a stored procedure with
create or alter procedure ADD_TABLE_FIELD (
TABLE_NAME varchar(50) collate DE_DE,
FIELD_NAME varchar(50) collate DE_DE,
FIELD_DOMAIN varchar(50) collate DE_DE)

which checks if the field is already available and if not, it add it to the
table. In this procedure is this code:

/* get field position for target table */
select Count(RDB$FIELD_ID)
from RDB$RELATION_FIELDS
where (Upper(RDB$RELATION_NAME) = Upper(:TABLE_NAME))
into :VAR_FIELD_POS;
if (:VAR_FIELD_POS is null) then VAR_FIELD_POS = 1;
else VAR_FIELD_POS = :VAR_FIELD_POS + 1;

If you execute this procedure more than one time in a transaction, it adds
the fields with the same Field_position

[cid:image004.png@01D4BE3C.B2E69C80]

I think IBO get the fields per "order by rdb$field_position - so the order
could be different.

Good news: with a alter table...... position X the values get a correct
value and the update SQL is correct...

Best regards and sorry for the mistake to think it's an iBO error...

Wolfgang

Von: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]
Gesendet: Mittwoch, 6. Februar 2019 15:51
An: IBObjects@yahoogroups.com
Betreff: RE: [IBO] IBO 5.9.9 B2784: wrong sequence in Update statement /
parameterlist

Ah, that does look like a rather serious problem. I haven't seen this so
I'm not sure what is going on.

Will you please provide a sample app demonstrating this problem so that I
can debug it and fix it?

You can override the SQL by using the EditSQL property and you may use named
parameters.

Thanks,
Jason Wharton
www.ibobjects.com<http://www.ibobjects.com>

________________________________
From: IBObjects@yahoogroups.com [mailto:IBObjects@yahoogroups.com]
Sent: Wednesday, February 06, 2019 3:00 AM
To: IBObjects@yahoogroups.com
Subject: [IBO] IBO 5.9.9 B2784: wrong sequence in Update statement /
parameterlist

Dear Jason,
we finally upgrade from IBO 4.8 to IBO 5.9.9 in an two day coding session.
At the tests bevor I get sometimes conversion errors from firebird which I
can't explain. The only soluition I found was to set an explicit
Update-Statement for the "Select x from Foo for Update".
Today I search again on another part of the project with a same problem and
I was shocked to see this:
extract from the automatic created UpdateSQL:
, D_PAT_PATIENT.D_PAT_RCFG_DIAGNOSE_SICHER_KZ = ?/*
NEW.D_PAT_PATIENT..D_PAT_RCFG_DIAGNOSE_SICHER_KZ */
, D_PAT_PATIENT.D_PAT_RCFG_DIAGNOSE_KLARTEXT_KZ = ?/*
NEW.D_PAT_PATIENT.D_PAT_RCFG_DIAGNOSE_KLARTEXT_KZ */
, D_PAT_PATIENT.D_PAT_RCFG_BETRIFFT_TYP = ?/*
NEW.D_PAT_PATIENT.D_PAT_RCFG_BETRIFFT_TYP */
, D_PAT_PATIENT.D_PAT_RCFG_STEUER_NR_KZ = ?/*
NEW.D_PAT_PATIENT.D_PAT_RCFG_STEUER_NR_KZ */
, D_PAT_PATIENT.D_PAT_RCFG_SONST_ADR_ID = ?/*
NEW.D_PAT_PATIENT.D_PAT_RCFG_SONST_ADR_ID */
, D_PAT_PATIENT.D_PAT_RCFG_ZWEITES_KONTO_KZ = ?/*
NEW.D_PAT_PATIENT.D_PAT_RCFG_ZWEITES_KONTO_KZ */
, D_PAT_PATIENT.D_PAT_RCFG_SONST_ANSP_ID = ?/*
NEW.D_PAT_PATIENT.D_PAT_RCFG_SONST_ANSP_ID */
, D_PAT_PATIENT.D_PAT_RCFG_BG_B = ?/* NEW.D_PAT_PATIENT.D_PAT_RCFG_BG_B
*/ and here the extract from parameters:
D_PAT_PATIENT.D_PAT_RCFG_DIAGNOSE_SICHER_KZ = <NULL> < TEXT SubType:
1557 Len: 1 Scale: 0 Data Offset: 3324 >
D_PAT_PATIENT.D_PAT_RCFG_DIAGNOSE_KLARTEXT_KZ = <NULL> < TEXT SubType:
1557 Len: 1 Scale: 0 Data Offset: 3327 >
D_PAT_PATIENT.D_PAT_RCFG_STEUER_NR_KZ = <NULL> < TEXT SubType: 1557
Len: 1 Scale: 0 Data Offset: 3330 >
D_PAT_PATIENT.D_PAT_RCFG_ZWEITES_KONTO_KZ = <NULL> < TEXT SubType: 1557
Len: 1 Scale: 0 Data Offset: 3333 >
D_PAT_PATIENT.D_PAT_RCFG_SONST_ADR_ID = <NULL> < LONG SubType: 0 Len: 4
Scale: 0 Data Offset: 3336 >
D_PAT_PATIENT.D_PAT_RCFG_BETRIFFT_TYP = 1 < LONG SubType: 0 Len: 4
Scale: 0 Data Offset: 3342 >
D_PAT_PATIENT.D_PAT_RCFG_SONST_ANSP_ID = <NULL> < LONG SubType: 0 Len:
4 Scale: 0 Data Offset: 3348 >
D_PAT_PATIENT.D_PAT_RCFG_BG_B = <NULL> < TEXT SubType: 1557 Len: 1
Scale: 0 Data Offset: 3354 >
the bold fields are mixed - they are not in the same order. Have you any
idea how this can happen? Can we force IBObjects to use DML SQLs with named
parameters?
Freundliche Grüße
Wolfgang Lemmermeyer
--------------------------------------------
Psyprax GmbH
80687 München, Landsberger Str. 308, Germany
Fon: +49 89 546800 0 - Fax: +49 89 546800 29
Email: lemmermeyer@...
Web: www.psyprax.de
Geschäftsführung:
Thomas Flohrschütz, Dorothea Bergmann
HRB 155217, Amtsgericht München
UID: DE 240 463020
--------------------------------------------
Gemäß Artikel 13 und 14 EU DSGVO sind wir verpflichtet Ihnen mitzuteilen,
wie wir Ihre personenbezogenen Daten verarbeiten.
Alle Informationen zu den einzelnen Verfahren können Sie über unsere
Homepage einsehen, dazu rufen Sie bitte nachfolgenden Link auf:
https://www.psyprax.de/datenschutz/

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

------------------------------------
Posted by: "Wolfgang Lemmermeyer [PsyPrax GmbH]" <lemmermeyer@...>
------------------------------------

__________________________________________________________
IB Objects - direct, complete, custom connectivity to Firebird or InterBase
without the need for BDE, ODBC or any other layer.
__________________________________________________________
http://www.ibobjects.com - your IBO community resource for Tech Info papers,
keyword-searchable FAQ, community code contributions and more !
http://tracker.ibobjects.com - your portal to submit and monitor bug reports
http://community.ibobjects.com - your portal to purchase and upgrade
------------------------------------

Yahoo Groups Links



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