Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

The NULLABLE and IS_NULLABLE information of a column returned by FBDatabaseMetaData.getColumns() is wrong, if the columns type is a domain. [JDBC100] #138

Closed
firebird-automations opened this issue Jun 17, 2007 · 3 comments
Closed

The NULLABLE and IS_NULLABLE information of a column returned by FBDatabaseMetaData.getColumns() is wrong, if the columns type is a domain. [JDBC100] #138

firebird-automations opened this issue Jun 17, 2007 · 3 comments
Labels
affect-version: Jaybird 2.1 component: jdbc driver fix-version: Jaybird 2.1 fix-version: Jaybird 2.2 priority: major resolution: fixed type: bug

Comments

@firebird-automations
Copy link

Submitted by: Jens Kieselbach (chukku)

Assigned to: Roman Rokytskyy (rrokytskyy)

When using the FBDatabaseMetaData.getColumns() method to check wether a column is nullable or not, the method will return wrong values for columns, wich are defined by a domain.

I looked in the source code and i think a found the problem:

The getColumns method of FBDatabaseMetaData reads out the RDB$NULL_FLAG field of the RDB$RELATION_FIELDS table to get the columns nullable information. If the columns type is a domain the RDB$NULL_FLAG field will be null, but the domain itself could defined as not null. So this method should also check the RDB$NULL_FLAG of the RDB$FIELDS table.

I compiled the driver with the followings changes and it seems to work fine for me now. It would be nice, if similar changes are made in the offical release.

Thanks
Jens Kieselbach

[...]

// the changes are marked with a comment

private static final String GET\_COLUMNS\_START = "select " \+
    " RF\.RDB$RELATION\_NAME as RELATION\_NAME," \+
    " RF\.RDB$FIELD\_NAME as FIELD\_NAME," \+
    " F\.RDB$FIELD\_TYPE as FIELD\_TYPE," \+
    " F\.RDB$FIELD\_SUB\_TYPE as FIELD\_SUB\_TYPE," \+
    " F\.RDB$FIELD\_PRECISION as FIELD\_PRECISION," \+
    " F\.RDB$FIELD\_SCALE as FIELD\_SCALE," \+
    " F\.RDB$FIELD\_LENGTH as FIELD\_LENGTH," \+
    " F\.RDB$CHARACTER\_LENGTH as CHAR\_LEN," \+
    " RF\.RDB$DESCRIPTION AS REMARKS," \+
    " RF\.RDB$DEFAULT\_SOURCE as DEFAULT\_SOURCE," \+
    " RF\.RDB$FIELD\_POSITION as FIELD\_POSITION, " \+
    " RF\.RDB$NULL\_FLAG as NULL\_FLAG, " \+
    " F\.RDB$NULL\_FLAG as SOURCE\_NULL\_FLAG " \+   // <\-\- change
    "from" \+
    " RDB$RELATION\_FIELDS RF," \+
    " RDB$FIELDS F " \+
    "where ";

public static final String GET\_COLUMNS\_END = " RF\.RDB$FIELD\_SOURCE = F\.RDB$FIELD\_NAME " \+
    "order by 1, 11";

public ResultSet getColumns\(String catalog, String schemaPattern,
    String tableNamePattern, String columnNamePattern\) throws SQLException \{
    checkCatalogAndSchema\(catalog, schemaPattern\);
    Clause tableClause = new Clause\("RF\.RDB$RELATION\_NAME", tableNamePattern\);
    Clause columnClause = new Clause\("RF\.RDB$FIELD\_NAME", columnNamePattern\);
    String sql = GET\_COLUMNS\_START;
    sql \+= tableClause\.getCondition\(\);
    sql \+= columnClause\.getCondition\(\);
    sql \+= GET\_COLUMNS\_END;
    ArrayList params = new ArrayList\(\);
    if \(\!tableClause\.getCondition\(\)\.equals\(""\)\) \{
        params\.add\(tableClause\.getValue\(\)\);
    \}
    if \(\!columnClause\.getCondition\(\)\.equals\(""\)\) \{
        params\.add\(columnClause\.getValue\(\)\);
    \}

    ResultSet rs = doQuery\(sql, params\);

    XSQLVAR\[\] xsqlvars = new XSQLVAR\[18\];

    xsqlvars\[0\] = new XSQLVAR\(\);
    xsqlvars\[0\]\.sqltype = ISCConstants\.SQL\_VARYING;
    xsqlvars\[0\]\.sqllen = 31;
    xsqlvars\[0\]\.sqlname = "TABLE\_CAT";
    xsqlvars\[0\]\.relname = "COLUMNINFO";

    xsqlvars\[1\] = new XSQLVAR\(\);
    xsqlvars\[1\]\.sqltype = ISCConstants\.SQL\_VARYING;
    xsqlvars\[1\]\.sqllen = 31;
    xsqlvars\[1\]\.sqlname = "TABLE\_SCHEM";
    xsqlvars\[1\]\.relname = "COLUMNINFO";

    xsqlvars\[2\] = new XSQLVAR\(\);
    xsqlvars\[2\]\.sqltype = ISCConstants\.SQL\_VARYING;
    xsqlvars\[2\]\.sqllen = 31;
    xsqlvars\[2\]\.sqlname = "TABLE\_NAME";
    xsqlvars\[2\]\.relname = "COLUMNINFO";

    xsqlvars\[3\] = new XSQLVAR\(\);
    xsqlvars\[3\]\.sqltype = ISCConstants\.SQL\_VARYING;
    xsqlvars\[3\]\.sqllen = 31;
    xsqlvars\[3\]\.sqlname = "COLUMN\_NAME";
    xsqlvars\[3\]\.relname = "COLUMNINFO";

    xsqlvars\[4\] = new XSQLVAR\(\);
    xsqlvars\[4\]\.sqltype = ISCConstants\.SQL\_SHORT;
    xsqlvars\[4\]\.sqlname = "DATA\_TYPE";
    xsqlvars\[4\]\.relname = "COLUMNINFO";

    xsqlvars\[5\] = new XSQLVAR\(\);
    xsqlvars\[5\]\.sqltype = ISCConstants\.SQL\_VARYING \| 1;
    xsqlvars\[5\]\.sqllen = 31;
    xsqlvars\[5\]\.sqlname = "TYPE\_NAME";
    xsqlvars\[5\]\.relname = "COLUMNINFO";

    xsqlvars\[6\] = new XSQLVAR\(\);
    xsqlvars\[6\]\.sqltype = ISCConstants\.SQL\_LONG;
    xsqlvars\[6\]\.sqlname = "COLUMN\_SIZE";
    xsqlvars\[6\]\.relname = "COLUMNINFO";

    xsqlvars\[7\] = new XSQLVAR\(\);
    xsqlvars\[7\]\.sqltype = ISCConstants\.SQL\_SHORT;
    xsqlvars\[7\]\.sqlname = "BUFFER\_LENGTH";
    xsqlvars\[7\]\.relname = "COLUMNINFO";

    xsqlvars\[8\] = new XSQLVAR\(\);
    xsqlvars\[8\]\.sqltype = ISCConstants\.SQL\_LONG;
    xsqlvars\[8\]\.sqlname = "DECIMAL\_DIGITS";
    xsqlvars\[8\]\.relname = "COLUMNINFO";

    xsqlvars\[9\] = new XSQLVAR\(\);
    xsqlvars\[9\]\.sqltype = ISCConstants\.SQL\_LONG;
    xsqlvars\[9\]\.sqlname = "NUM\_PREC\_RADIX";
    xsqlvars\[9\]\.relname = "COLUMNINFO";

    xsqlvars\[10\] = new XSQLVAR\(\);
    xsqlvars\[10\]\.sqltype = ISCConstants\.SQL\_LONG;
    xsqlvars\[10\]\.sqlname = "NULLABLE";
    xsqlvars\[10\]\.relname = "COLUMNINFO";

    xsqlvars\[11\] = new XSQLVAR\(\);
    xsqlvars\[11\]\.sqltype = ISCConstants\.SQL\_VARYING \| 1;
    xsqlvars\[11\]\.sqllen = 80;
    xsqlvars\[11\]\.sqlname = "REMARKS";
    xsqlvars\[11\]\.relname = "COLUMNINFO";

    xsqlvars\[12\] = new XSQLVAR\(\);
    xsqlvars\[12\]\.sqltype = ISCConstants\.SQL\_VARYING \| 1;
    xsqlvars\[12\]\.sqllen = 31;
    xsqlvars\[12\]\.sqlname = "COLUMN\_DEF";
    xsqlvars\[12\]\.relname = "COLUMNINFO";

    xsqlvars\[13\] = new XSQLVAR\(\);
    xsqlvars\[13\]\.sqltype = ISCConstants\.SQL\_LONG;
    xsqlvars\[13\]\.sqlname = "SQL\_DATA\_TYPE";
    xsqlvars\[13\]\.relname = "COLUMNINFO";

    xsqlvars\[14\] = new XSQLVAR\(\);
    xsqlvars\[14\]\.sqltype = ISCConstants\.SQL\_LONG;
    xsqlvars\[14\]\.sqlname = "SQL\_DATETIME\_SUB";
    xsqlvars\[14\]\.relname = "COLUMNINFO";

    xsqlvars\[15\] = new XSQLVAR\(\);
    xsqlvars\[15\]\.sqltype = ISCConstants\.SQL\_LONG;
    xsqlvars\[15\]\.sqlname = "CHAR\_OCTET\_LENGTH";
    xsqlvars\[15\]\.relname = "COLUMNINFO";

    xsqlvars\[16\] = new XSQLVAR\(\);
    xsqlvars\[16\]\.sqltype = ISCConstants\.SQL\_LONG;
    xsqlvars\[16\]\.sqlname = "ORDINAL\_POSITION";
    xsqlvars\[16\]\.relname = "COLUMNINFO";

    xsqlvars\[17\] = new XSQLVAR\(\);
    xsqlvars\[17\]\.sqltype = ISCConstants\.SQL\_VARYING;
    xsqlvars\[17\]\.sqllen = 3;
    xsqlvars\[17\]\.sqlname = "IS\_NULLABLE";
    xsqlvars\[17\]\.relname = "COLUMNINFO";

    ArrayList rows = new ArrayList\(\);
    while \(rs\.next\(\)\) \{
        byte\[\]\[\] row = new byte\[18\]\[\];
        row\[0\] = null;
        row\[1\] = null;
        row\[2\] = getBytes\(rs\.getString\("RELATION\_NAME"\)\.trim\(\)\);
        row\[3\] = getBytes\(rs\.getString\("FIELD\_NAME"\)\.trim\(\)\);

        short fieldType = rs\.getShort\("FIELD\_TYPE"\);
        short fieldSubType = rs\.getShort\("FIELD\_SUB\_TYPE"\);
        short fieldScale = rs\.getShort\("FIELD\_SCALE"\);
        int dataType = getDataType\(fieldType, fieldSubType, fieldScale\);

        row\[4\] = xsqlvars\[0\]\.encodeShort\(\(short\) dataType\);
        row\[5\] = getBytes\(getDataTypeName\(fieldType, fieldSubType, fieldScale\)\);

        switch \(dataType\)\{
            case Types\.DECIMAL:
            case Types\.NUMERIC:
               row\[6\] = xsqlvars\[0\]\.encodeInt\(rs\.getShort\("FIELD\_PRECISION"\)\);
               row\[8\] = xsqlvars\[0\]\.encodeInt\(fieldScale \* \(\-1\)\);
               row\[15\] = xsqlvars\[0\]\.encodeInt\(0\);
               break;
            case Types\.CHAR:
            case Types\.VARCHAR:
               row\[6\] = xsqlvars\[0\]\.encodeInt\(rs\.getShort\("CHAR\_LEN"\)\);
               row\[8\] = xsqlvars\[0\]\.encodeInt\(fieldScale \* \(\-1\)\);
               row\[15\] = xsqlvars\[0\]\.encodeInt\(rs\.getShort\("FIELD\_LENGTH"\)\);
               break;
            case Types\.FLOAT:
               row\[6\] = xsqlvars\[0\]\.encodeInt\(7\);
               row\[8\] = xsqlvars\[0\]\.encodeInt\(7\);
               row\[15\] = xsqlvars\[0\]\.encodeInt\(0\);
               break;
            case Types\.DOUBLE:
               row\[6\] = xsqlvars\[0\]\.encodeInt\(15\);
               row\[8\] = xsqlvars\[0\]\.encodeInt\(15\);
               row\[15\] = xsqlvars\[0\]\.encodeInt\(0\);
               break;
            case Types\.INTEGER:
               row\[6\] = xsqlvars\[0\]\.encodeInt\(10\);
               row\[8\] = xsqlvars\[0\]\.encodeInt\(fieldScale \* \(\-1\)\);
               row\[15\] = xsqlvars\[0\]\.encodeInt\(0\);
               break;
            case Types\.SMALLINT:
               row\[6\] = xsqlvars\[0\]\.encodeInt\(5\);
               row\[8\] = xsqlvars\[0\]\.encodeInt\(fieldScale \* \(\-1\)\);
               row\[15\] = xsqlvars\[0\]\.encodeInt\(0\);
               break;
            case Types\.DATE:
               row\[6\] = xsqlvars\[0\]\.encodeInt\(10\);
               row\[8\] = xsqlvars\[0\]\.encodeInt\(fieldScale \* \(\-1\)\);
               row\[15\] = xsqlvars\[0\]\.encodeInt\(0\);
               break;
            case Types\.TIME:
               row\[6\] = xsqlvars\[0\]\.encodeInt\(8\);
               row\[8\] = xsqlvars\[0\]\.encodeInt\(fieldScale \* \(\-1\)\);
               row\[15\] = xsqlvars\[0\]\.encodeInt\(0\);
               break;
            case Types\.TIMESTAMP:
               row\[6\] = xsqlvars\[0\]\.encodeInt\(19\);
               row\[8\] = xsqlvars\[0\]\.encodeInt\(fieldScale \* \(\-1\)\);
               row\[15\] = xsqlvars\[0\]\.encodeInt\(0\);
               break;
            default:
               row\[6\] = xsqlvars\[0\]\.encodeInt\(0\);
               row\[8\] = xsqlvars\[0\]\.encodeInt\(fieldScale \* \(\-1\)\);
               row\[15\] = xsqlvars\[0\]\.encodeInt\(0\);
           \}

        row\[7\] = xsqlvars\[0\]\.encodeShort\(\(short\) 0\);
        row\[9\] = xsqlvars\[0\]\.encodeInt\(10\);

        short nullFlag = rs\.getShort\("NULL\_FLAG"\);
    short sourceNullFlag = rs\.getShort\("SOURCE\_NULL\_FLAG"\); // <\-\- change
    
        row\[10\] = \(nullFlag == 1 \|\| sourceNullFlag == 1\) ? xsqlvars\[0\]\.encodeInt\(columnNoNulls\) :
                                    xsqlvars\[0\]\.encodeInt\(columnNullable\); // <\-\- change

        String remarks = rs\.getString\("REMARKS"\);  
        row\[11\] = getBytes\(remarks\);             
        if \(remarks \!= null && remarks\.length\(\) \> xsqlvars\[11\]\.sqllen\)               
            xsqlvars\[11\]\.sqllen = remarks\.length\(\);
        
        String column\_def = rs\.getString\("DEFAULT\_SOURCE"\);
        if \(column\_def\!=null\) \{
            String defaultValue = column\_def\.trim\(\);
            
            int defaultPos = defaultValue\.toUpperCase\(\)\.indexOf\("DEFAULT"\);
            if \(defaultPos \>= 0\)
                defaultValue = defaultValue\.substring\(7\)\.trim\(\);
            
        	row\[12\] = getBytes\(defaultValue\);
        \} else
        	row\[12\] = null;
        
        row\[13\] = null;
        row\[14\] = null;
        row\[16\] = xsqlvars\[0\]\.encodeInt\(rs\.getShort\("FIELD\_POSITION"\) \+ 1\);
        row\[17\] = \(nullFlag == 1 \|\| sourceNullFlag == 1\) ? getBytes\("NO"\) : getBytes\("YES"\); // <\-\- change

        rows\.add\(row\);
    \}
    return new FBResultSet\(xsqlvars, rows\);
\}

[...]

Commits: 7434d15 88da735
@firebird-automations
Copy link
Author

Commented by: Roman Rokytskyy (rrokytskyy)

Done. Thanks for the tip.

@firebird-automations
Copy link
Author

Modified by: Roman Rokytskyy (rrokytskyy)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: Jaybird 2.2 [ 10053 ]

Fix Version: Jaybird 2.1 [ 10050 ]

@firebird-automations
Copy link
Author

Modified by: Roman Rokytskyy (rrokytskyy)

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
affect-version: Jaybird 2.1 component: jdbc driver fix-version: Jaybird 2.1 fix-version: Jaybird 2.2 priority: major resolution: fixed type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@firebird-automations