[FIREBIRD] Problem when i migrate 3.2.2 to 3.4.0

[sdne...@gmail.com]

Hi,

my database is firebird and now when i doing that the insert don't work :

record = getDsl().newRecord(MYTABLE);

record.setState(0);

record.store();

no warning, no error but nothing is inserted and i have a primary key on MYTABLE.

it works fine on 3.2.2.

any sugestions ?

[sdne...@gmail.com]

I have debug a little bit and it try to do an update (i don't know why for moment)

[sdne...@gmail.com]

i think the problem is in the mapping of table the definition of my primary key is :

IDMYTABLE VARCHAR(22) NOT NULL via a domain

but field.getDataType().nullable => returns true and field size is 88 ????

[sdne...@gmail.com]

I post the DDL of the table and i attache table result

/******************************************************************************/

/****                                Tables                                ****/

/******************************************************************************/

CREATE TABLE BUPDATERLINKS (

    IDBUPDATERLINKS  IDENT /* IDENT = VARCHAR(22) NOT NULL */,

    BULREFPARENT     REFERENCE /* REFERENCE = VARCHAR(22) DEFAULT '-1' */,

    BULTYPE          ENTIER /* ENTIER = INTEGER DEFAULT 0 */,

    BULREFBYTYPE     REFERENCE /* REFERENCE = VARCHAR(22) DEFAULT '-1' */,

    BULDEST          LIB500 /* LIB500 = VARCHAR(500) */,

    BULJSON          BLOBTEXT /* BLOBTEXT = BLOB SUB_TYPE 1 SEGMENT SIZE 80 */,

    BULSTATE         ENTIER /* ENTIER = INTEGER DEFAULT 0 */,

    BULDATEACTION    DATECOMPLETE /* DATECOMPLETE = TIMESTAMP */,

    BULACTIF         ENTIER /* ENTIER = INTEGER DEFAULT 0 */,

    FTPDATE          DATECOMPLETE2 /* DATECOMPLETE2 = TIMESTAMP DEFAULT 'now' NOT NULL */,

    CREATEDATE       DATECOMPLETE2 /* DATECOMPLETE2 = TIMESTAMP DEFAULT 'now' NOT NULL */

);

/******************************************************************************/

/****                             Primary Keys                             ****/

/******************************************************************************/

ALTER TABLE BUPDATERLINKS ADD CONSTRAINT PK_BUPDATERLINKS PRIMARY KEY (IDBUPDATERLINKS);

/******************************************************************************/

/****                               Triggers                               ****/

/******************************************************************************/

SET TERM ^ ;

/******************************************************************************/

/****                         Triggers for tables                          ****/

/******************************************************************************/

/* Trigger: BUPDATERLINKS_BI */

CREATE OR ALTER TRIGGER BUPDATERLINKS_BI FOR BUPDATERLINKS

ACTIVE BEFORE INSERT POSITION 0

as

begin

  IF (NEW.idbupdaterlinks IS NULL) THEN

  BEGIN

    EXECUTE PROCEDURE SYS_GET_UUID RETURNING_VALUES NEW.idbupdaterlinks;

  END

end

^

[sdne...@gmail.com]

I have finish my debug party to see where is the difference between 3.3.0 and 3.4.0 ant this is result :

1) the code generator is the same with some error in nullable and lenght on varchar.

2) the difference is in UpdatableRecordImpl::store0()

[in 3.3.0]

at   // If any primary key value is null or changed, execute an insert

                if (getValue(field) == null || getValue0(field).isChanged()) {

                    executeUpdate = false;

                    break;

                }

becomes in 3.4.0

// If any primary key value is null or changed
                if (changed(field) ||

                // [#3237] or if a NOT NULL primary key value is null, then execute an INSERT
                   (field.getDataType().nullable() == false && getValue(field) == null)) {
                    executeUpdate = false;
                    break;
                }

[Lukas Eder]

Hello,

First off, I'm sorry for the delay. I realise that this looks like a pressing issue that caused quite some time to debug. You're right, the reason for this regression is hidden in UpdatableRecordImpl.store(), where we applied a couple of fixes in jOOQ 3.4.0, e.g. #3237:

https://github.com/jOOQ/jOOQ/issues/3237

As the issue title reads (and as you've discovered with debugging), this fix allows for nullable primary keys, which are particularly useful in SQL Server, for instance. The previous logic did not allow to update records whose primary key is null.

So, I think the problem is really the fact that the code generator did not pick up your domain's NOT NULL constraint. Could you please provide me with the required DDL to produce your table, including all domains? I will add that to the integration test suite, then.

In the mean time, I have registered an issue for this code generation problem:

https://github.com/jOOQ/jOOQ/issues/3382

Cheers

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[sdne...@gmail.com]

ok i attach the entire ddl

[Lukas Eder]

Thank you for the additional information. The query that jOOQ executes to collect column and data type information is roughly this one:

SELECT

    TRIM(

        "r" . "RDB$FIELD_NAME"

    ) ,

    "r" . "RDB$DESCRIPTION" ,

    "r" . "RDB$DEFAULT_VALUE" ,

    CASE

        WHEN "r" . "RDB$NULL_FLAG" IS NOT NULL

        THEN "r" . "RDB$NULL_FLAG"

        ELSE 0

    END ,

    "r" . "RDB$DEFAULT_SOURCE" ,

    "r" . "RDB$FIELD_POSITION" ,

    CASE

        "f" . "RDB$FIELD_TYPE" WHEN 261

        THEN 0

        ELSE "f" . "RDB$FIELD_LENGTH"

    END "FIELD_LENGTH" ,

    "f" . "RDB$FIELD_PRECISION" ,

    -(

        "f" . "RDB$FIELD_SCALE"

    ) "FIELD_SCALE" ,

    CASE

        "f" . "RDB$FIELD_TYPE" WHEN 7

        THEN CASE

            WHEN "f" . "RDB$FIELD_SUB_TYPE" = 1

            THEN 'NUMERIC' WHEN(

                "f" . "RDB$FIELD_SUB_TYPE" = 0

                AND "f" . "RDB$FIELD_SCALE" < 0

            )

            THEN 'NUMERIC' WHEN "f" . "RDB$FIELD_SUB_TYPE" = 2

            THEN 'DECIMAL'

            ELSE 'SMALLINT'

        END WHEN 8

        THEN CASE

            WHEN "f" . "RDB$FIELD_SUB_TYPE" = 1

            THEN 'NUMERIC' WHEN(

                "f" . "RDB$FIELD_SUB_TYPE" = 0

                AND "f" . "RDB$FIELD_SCALE" < 0

            )

            THEN 'NUMERIC' WHEN "f" . "RDB$FIELD_SUB_TYPE" = 2

            THEN 'DECIMAL'

            ELSE 'INTEGER'

        END WHEN 9

        THEN 'QUAD' WHEN 10

        THEN 'FLOAT' WHEN 11

        THEN 'D_FLOAT' WHEN 12

        THEN 'DATE' WHEN 13

        THEN 'TIME' WHEN 14

        THEN 'CHAR' WHEN 16

        THEN CASE

            WHEN "f" . "RDB$FIELD_SUB_TYPE" = 1

            THEN 'NUMERIC' WHEN(

                "f" . "RDB$FIELD_SUB_TYPE" = 0

                AND "f" . "RDB$FIELD_SCALE" < 0

            )

            THEN 'NUMERIC' WHEN "f" . "RDB$FIELD_SUB_TYPE" = 2

            THEN 'DECIMAL'

            ELSE 'BIGINT'

        END WHEN 27

        THEN 'DOUBLE' WHEN 35

        THEN 'TIMESTAMP' WHEN 37

        THEN 'VARCHAR' WHEN 40

        THEN 'CSTRING' WHEN 261

        THEN CASE

            "f" . "RDB$FIELD_SUB_TYPE" WHEN 0

            THEN 'BLOB' WHEN 1

            THEN 'BLOB SUB_TYPE TEXT'

            ELSE 'BLOB'

        END

        ELSE 'UNKNOWN'

    END "FIELD_TYPE" ,

    "f" . "RDB$FIELD_SUB_TYPE"

FROM

    "RDB$RELATION_FIELDS" "r" LEFT OUTER JOIN "RDB$FIELDS" "f"

        ON "r" . "RDB$FIELD_SOURCE" = "f" . "RDB$FIELD_NAME"

WHERE

    "r" . "RDB$RELATION_NAME" = 'BUPDATERLINKS'

ORDER BY

    "r" . "RDB$FIELD_POSITION" ASC

The parts marked in yellow don't seem to be correct in the case of column types originating from domains. The JDBC driver gets this correctly, when executing DatabaseMetaData.getColumns(). I'll see how this can be fixed.

Cheers

Lukas

--

[Lukas Eder]

The JDBC driver checks both RDB$RELATION_FIELDS.RDB$NULL_FLAG as well as RDB$FIELDS.RDB$NULL_FLAG. If either value is 1, then the resulting value is 1.

I have committed a fix for issue #3382, which seems to take care of your problem:

https://github.com/jOOQ/jOOQ/commit/9a74f5c41ff35d5fabe2ea9be2b17b5f7719e2e4

By the end of the week, I'll be publishing patch releases for 3.2, 3.3, and 3.4. This fix has high chances of being included. If you need a fix before Friday, you can build 3.5.0-SNAPSHOT from GitHub.

However, I could not reproduce the part where the VARCHAR type has a reported length of 88. Do you have any specific Firebird setting enabled, related to character encoding?

[sdne...@gmail.com]

Thanks for this patch !

i'm on a firebird 2.5.2 server with a database charset utf8 and collate utf8

[sdne...@gmail.com]

However, I could not reproduce the part where the VARCHAR type has a reported length of 88. Do you have any specific Firebird setting enabled, related to character encoding?

have you managed to reproduce the problem with length of varchar ?

[Lukas Eder]

Hello,

No, I haven't been able to reproduce that problem. What do you get when you execute this query?

[sdne...@gmail.com]

Hi Lukas,

i have understand the problem, in fact in your request we don't take care about the size of a character .

so because i'm in utf8 the lenght is false, so i think the request would be :

SELECT
    TRIM(
        "r" . "RDB$FIELD_NAME"
    ) ,
    "r" . "RDB$DESCRIPTION" ,
    "r" . "RDB$DEFAULT_VALUE" ,
    CASE
        WHEN "r" . "RDB$NULL_FLAG" IS NOT NULL
        THEN "r" . "RDB$NULL_FLAG"
        ELSE 0
    END ,
    "r" . "RDB$DEFAULT_SOURCE" ,
    "r" . "RDB$FIELD_POSITION" ,
    CASE
        "f" . "RDB$FIELD_TYPE" WHEN 261
        THEN 0

        ELSE iif("RDB$BYTES_PER_CHARACTER" is not null,"f" . "RDB$FIELD_LENGTH" / "CH" . "RDB$BYTES_PER_CHARACTER", "f" . "RDB$FIELD_LENGTH")

    LEFT OUTER JOIN RDB$CHARACTER_SETS "CH" ON ("CH"."RDB$CHARACTER_SET_ID" = "f"."RDB$CHARACTER_SET_ID")

[sdne...@gmail.com]

Hi Lukas,

do you think you can integrate this request for ddl generation on firebird database (problem with length) ?

[Lukas Eder]

Hi there,

Yes, thanks for tracking down the right SQL to add to FirebirdDatabase.

I will check whether it is valid and then probably integrate it. I'll get back to you with questions, should I have any.

I've been busy with merging 3.3.3 and 3.2.6 and some other work, but this should be fixed very soon.

Cheers

Lukas

[sdne...@gmail.com]

ok thanks,

as i say to you before if you want all request for firebird ddl (procedure,view, trigger, table) i can send to you (i have done an automatic versionning for firebird db)

if i have time, i will try to implement the procedure generation code for firebird. (i have now a better understanding of JOOQ).

[Lukas Eder]

Hi there,

Hmm, instead of joining the RDB$CHARACTER_SETS relation and doing calculations on our own, why not just replace f.RDB$FIELD_LENGTH by f.RDB$CHARACTER_LENGTH in the first place? Woudl that also produce the right result for you?

[Lukas Eder]

I have fixed and pushed this issue to GitHub master:

https://github.com/jOOQ/jOOQ/issues/3402

Feedback if this solves your character length issue would be appreciated.

as i say to you before if you want all request for firebird ddl (procedure,view, trigger, table) i can send to you (i have done an automatic versionning for firebird db)

I'm not quite sure what you mean by this :)

 

if i have time, i will try to implement the procedure generation code for firebird. (i have now a better understanding of JOOQ).

That would be really great! The relevant issue is here:

https://github.com/jOOQ/jOOQ/issues/2925 

You will have to implement FirebirdDatabase.getRoutines0(). Examples can be seen in other Database implementations in jOOQ-meta. Let me know if you need any help.

Best

Lukas

[sdne...@gmail.com]

I have fixed and pushed this issue to GitHub master:
https://github.com/jOOQ/jOOQ/issues/3402
Feedback if this solves your character length issue would be appreciated.

It seems good for me, and well done for  RDB$CHARACTER_LENGTH

...

[sdne...@gmail.com]

Sorry maybe i have answer too quick :(

i have try to generate code with the last source and i obtain thids error on all tables :

juil. 16, 2014 2:37:39 PM org.jooq.tools.JooqLogger error

Grave: Error while generating table BOMODULESLINKS

org.jooq.exception.DataAccessException: SQL [select trim("r"."RDB$FIELD_NAME"), "r"."RDB$DESCRIPTION", "r"."RDB$DEFAULT_VALUE", bin_or(case when "r"."RDB$NULL_FLAG" is not null then "r"."RDB$NULL_FLAG" else cast(? as smallint) end, case when "f"."RDB$NULL_FLAG" is not null then "f"."RDB$NULL_FLAG" else cast(? as smallint) end) "RDB$NULL_FLAG", "r"."RDB$DEFAULT_SOURCE", "r"."RDB$FIELD_POSITION", case "f"."RDB$FIELD_TYPE" when cast(? as smallint) then cast(? as smallint) else "f"."RDB$CHARACTER_LENGTH" end "CHARACTER_LENGTH", "f"."RDB$FIELD_PRECISION", -("f"."RDB$FIELD_SCALE") "FIELD_SCALE", case "f"."RDB$FIELD_TYPE" when cast(? as smallint) then case when "f"."RDB$FIELD_SUB_TYPE" = cast(? as smallint) then cast(? as varchar(4000)) when ("f"."RDB$FIELD_SUB_TYPE" = cast(? as smallint) and "f"."RDB$FIELD_SCALE" < cast(? as smallint)) then cast(? as varchar(4000)) when "f"."RDB$FIELD_SUB_TYPE" = cast(? as smallint) then cast(? as varchar(4000)) else cast(? as varchar(4000)) end when cast(? as smallint) then case when "f"."RDB$FIELD_SUB_TYPE" = cast(? as smallint) then cast(? as varchar(4000)) when ("f"."RDB$FIELD_SUB_TYPE" = cast(? as smallint) and "f"."RDB$FIELD_SCALE" < cast(? as smallint)) then cast(? as varchar(4000)) when "f"."RDB$FIELD_SUB_TYPE" = cast(? as smallint) then cast(? as varchar(4000)) else cast(? as varchar(4000)) end when cast(? as smallint) then cast(? as varchar(4000)) when cast(? as smallint) then cast(? as varchar(4000)) when cast(? as smallint) then cast(? as varchar(4000)) when cast(? as smallint) then cast(? as varchar(4000)) when cast(? as smallint) then cast(? as varchar(4000)) when cast(? as smallint) then cast(? as varchar(4000)) when cast(? as smallint) then case when "f"."RDB$FIELD_SUB_TYPE" = cast(? as smallint) then cast(? as varchar(4000)) when ("f"."RDB$FIELD_SUB_TYPE" = cast(? as smallint) and "f"."RDB$FIELD_SCALE" < cast(? as smallint)) then cast(? as varchar(4000)) when "f"."RDB$FIELD_SUB_TYPE" = cast(? as smallint) then cast(? as varchar(4000)) else cast(? as varchar(4000)) end when cast(? as smallint) then cast(? as varchar(4000)) when cast(? as smallint) then cast(? as varchar(4000)) when cast(? as smallint) then cast(? as varchar(4000)) when cast(? as smallint) then cast(? as varchar(4000)) when cast(? as smallint) then case "f"."RDB$FIELD_SUB_TYPE" when cast(? as smallint) then cast(? as varchar(4000)) when cast(? as smallint) then cast(? as varchar(4000)) else cast(? as varchar(4000)) end else cast(? as varchar(4000)) end "FIELD_TYPE", "f"."RDB$FIELD_SUB_TYPE" from "RDB$RELATION_FIELDS" "r" left outer join "RDB$FIELDS" "f" on "r"."RDB$FIELD_SOURCE" = "f"."RDB$FIELD_NAME" where "r"."RDB$RELATION_NAME" = cast(? as char) order by "r"."RDB$FIELD_POSITION" asc]; GDS Exception. 335544569. Dynamic SQL Error

SQL error code = -204

Implementation limit exceeded

block size exceeds implementation restriction

at org.jooq.impl.Utils.translate(Utils.java:1435)

at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:505)

at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:340)

at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:336)

at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2256)

at org.jooq.util.firebird.FirebirdTableDefinition.getElements0(FirebirdTableDefinition.java:79)

at org.jooq.util.AbstractElementContainerDefinition.getElements(AbstractElementContainerDefinition.java:90)

at org.jooq.util.AbstractTableDefinition.getColumns(AbstractTableDefinition.java:138)

at org.jooq.util.AbstractTableDefinition.getPrimaryKey(AbstractTableDefinition.java:78)

at org.jooq.util.JavaGenerator.generateTable(JavaGenerator.java:1829)

at org.jooq.util.JavaGenerator.generateTables(JavaGenerator.java:1818)

at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:232)

at org.jooq.util.JavaGenerator.generate(JavaGenerator.java:216)

at org.jooq.util.GenerationTool.run(GenerationTool.java:374)

at org.jooq.util.GenerationTool.main(GenerationTool.java:173)

at org.jooq.util.GenerationTool.main(GenerationTool.java:160)

Caused by: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error

SQL error code = -204

Implementation limit exceeded

block size exceeds implementation restriction

at org.firebirdsql.jdbc.AbstractPreparedStatement.<init>(AbstractPreparedStatement.java:141)

It seems that the generated sql exeed the 64kb limit size (http://www.firebirdfaq.org/faq299/)

Lukas

/****         &nb

...

[Lukas Eder]

Thanks for the update. Now I remember that this was the reason for this fix:

https://github.com/jOOQ/jOOQ/issues/1727

Quite unfortunate :-/

I have reverted that change for now (https://github.com/jOOQ/jOOQ/issues/3398)

[Lukas Eder]

Hi there,

Just a short note to indicate that this issue hasn't been forgotten:

https://github.com/jOOQ/jOOQ/issues/3398

I'm still thinking about the best way to thoroughly fix this - in order to prevent excessive casting of bind values where this is not "really" needed (across all databases).

Will keep you posted

Cheers,

Lukas