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]