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

Error function findColumn not working with composed field alias [JDBC260] #307

Closed
firebird-automations opened this issue Jul 31, 2012 · 26 comments

Comments

@firebird-automations
Copy link

Submitted by: Account systems Ltd. (nmx)

Is related to JDBC162
Is duplicated by JDBC278
Relate to JDBC298
Is duplicated by JDBC356

in module FBResultSetMetaData

has function:

public String getColumnName(int column) throws SQLException
{
if (getXsqlvar(column).sqlname == null)
return getColumnLabel(column);
else
return getXsqlvar(column).sqlname;
}

i this that mast:

public String getColumnName(int column) throws SQLException
{
if (getXsqlvar(column).sqlname == null || getXsqlvar(column).sqlname.trim().equals("") )
return getColumnLabel(column);
else
return getXsqlvar(column).sqlname;
}

becase trce wirh SQL code "select ID, cast(ID as varchar(31)) AS TXT from myProc"

RowSetMD.getColumnName(2) no return null and no return "TXT" but returning "" (empty initialisation String)

private int getColIdxByName(String name) throws SQLException
{
ResultSetMetaData RowSetMD = this.getMetaData();
int cols = RowSetMD.getColumnCount();

    for \(int i = 1; i <= cols; \+\+i\)
    \{
        String colName  = RowSetMD\.getColumnName\(i\);
        String colLabel = RowSetMD\.getColumnLabel\(i\);

        if \(colName \!= null\)
            if \(name\.equalsIgnoreCase\(colName\) \|\| name\.equalsIgnoreCase\(colLabel\)\)
            \{
                if \(name\.equalsIgnoreCase\(colLabel\)\)
                    nmxFunc\.trace\( colLabel, colName\);

                return \(i\);
            \}
            else
                continue;
    \}
    throw new SQLException\(resBundle\.handleGetObject\("cachedrowsetimpl\.invalcolnm"\)\.toString\(\)\);
\}

}

Commits: b50d1a1 3a9ee1b

@firebird-automations
Copy link
Author

Modified by: Account systems Ltd. (nmx)

environment: Firebird-2.1.5.18496_0_Win32 Win7 64

@firebird-automations
Copy link
Author

Modified by: Account systems Ltd. (nmx)

description: in module FBResultSetMetaData

has function:

public String getColumnName(int column) throws SQLException
{
if (getXsqlvar(column).sqlname == null)
return getColumnLabel(column);
else
return getXsqlvar(column).sqlname;
}

i this that mast:

public String getColumnName(int column) throws SQLException
{
if (getXsqlvar(column).sqlname == null || getXsqlvar(column).sqlname.trim().equals("") )
return getColumnLabel(column);
else
return getXsqlvar(column).sqlname;
}

becase trce wirh SQL code "select ID, cast(ID as varchar(31)) AS TXT from myProc"

RowSetMD.getColumnName(2) returning "" not null and nor "TXT"

private int getColIdxByName(String name) throws SQLException
{
ResultSetMetaData RowSetMD = this.getMetaData();
int cols = RowSetMD.getColumnCount();

    for \(int i = 1; i <= cols; \+\+i\)
    \{
        String colName  = RowSetMD\.getColumnName\(i\);
        String colLabel = RowSetMD\.getColumnLabel\(i\);

        if \(colName \!= null\)
            if \(name\.equalsIgnoreCase\(colName\) \|\| name\.equalsIgnoreCase\(colLabel\)\)
            \{
                if \(name\.equalsIgnoreCase\(colLabel\)\)
                    nmxFunc\.trace\( colLabel, colName\);

                return \(i\);
            \}
            else
                continue;
    \}
    throw new SQLException\(resBundle\.handleGetObject\("cachedrowsetimpl\.invalcolnm"\)\.toString\(\)\);
\}

}

=>

in module FBResultSetMetaData

has function:

public String getColumnName(int column) throws SQLException
{
if (getXsqlvar(column).sqlname == null)
return getColumnLabel(column);
else
return getXsqlvar(column).sqlname;
}

i this that mast:

public String getColumnName(int column) throws SQLException
{
if (getXsqlvar(column).sqlname == null || getXsqlvar(column).sqlname.trim().equals("") )
return getColumnLabel(column);
else
return getXsqlvar(column).sqlname;
}

becase trce wirh SQL code "select ID, cast(ID as varchar(31)) AS TXT from myProc"

RowSetMD.getColumnName(2) no return null and no return "TXT" but returning "" (empty initialisation String)

private int getColIdxByName(String name) throws SQLException
{
ResultSetMetaData RowSetMD = this.getMetaData();
int cols = RowSetMD.getColumnCount();

    for \(int i = 1; i <= cols; \+\+i\)
    \{
        String colName  = RowSetMD\.getColumnName\(i\);
        String colLabel = RowSetMD\.getColumnLabel\(i\);

        if \(colName \!= null\)
            if \(name\.equalsIgnoreCase\(colName\) \|\| name\.equalsIgnoreCase\(colLabel\)\)
            \{
                if \(name\.equalsIgnoreCase\(colLabel\)\)
                    nmxFunc\.trace\( colLabel, colName\);

                return \(i\);
            \}
            else
                continue;
    \}
    throw new SQLException\(resBundle\.handleGetObject\("cachedrowsetimpl\.invalcolnm"\)\.toString\(\)\);
\}

}

environment: Firebird-2.1.5.18496_0_Win32 Win7 64 => Firebird-2.1.5.18496_0_Win32 Win7 64 and Linux openSuse 11.4 amd64 JDK1.6

@firebird-automations
Copy link
Author

Modified by: Account systems Ltd. (nmx)

environment: Firebird-2.1.5.18496_0_Win32 Win7 64 and Linux openSuse 11.4 amd64 JDK1.6 => Firebird-2.1.5.18496_0_Win32 JDK1.6 Win7 64 and Linux openSuse 11.4 amd64

summary: Error function findColumn not working with comosed field alias => Error function findColumn not working with composed field alias

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

This is not a bug, but an intentional change done in JDBC162 for compliance with the JDBC specification. It is also described in the releasenotes under "Other fixes and changes". For your intended purpose you need to use getColumnLabel as getColumnName is meant to return the original column name (if any or an empty string if the result column did not come from a table column is available).

The JDBC specification makes a distinction between label and name. The label is the name specified by the AS clause, and if no AS clause is specified then it use the name of the column. The name of the column is either the original name of the column in its source table (eg which you can retrieve using getTableName), or an empty string if the resultset column does not come from a table. I do admit that this last part requires a bit of reading between the lines as the JDBC spec does not say this explicitly, but returning the value of the AS clause for columnName is not correct.

The way Jaybird handles it now for sqlname == null is also wrong as well; it should just return an empty string there.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Changed priority to trivial, need to consider whether to change current behavior for sqlname == null

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

priority: Blocker [ 1 ] => Trivial [ 5 ]

@firebird-automations
Copy link
Author

Commented by: Account systems Ltd. (nmx)

ok.

My working path for CachedRowSetImpl is:

@OverRide
public void populate(ResultSet rs) throws SQLException
{
super.populate(rs);
this.pathMetaData(rs.getMetaData());
}
private void pathMetaData(ResultSetMetaData rsmd) throws SQLException
{
int numCols = rsmd.getColumnCount();

    for \(int col = 1; col <= numCols; col\+\+\) 
    \{
        String colName  = rsmd\.getColumnName\(col\);
        String colLabel = rsmd\.getColumnLabel\(col\);
        
        if \(colLabel \!= null && \!colLabel\.trim\(\)\.equals\(""\) && \!colLabel\.equals\(colName\) \)
            \(\(RowSetMetaDataImpl\)this\.getMetaData\(\)\)\.setColumnName\(col, colLabel\);
        
    \}
 \}

@firebird-automations
Copy link
Author

Commented by: Account systems Ltd. (nmx)

P.S.

Could you provide a simple method for use in Fbdriver user-defined classes such as

myFBMetaData
myFBResultset
myFBConnectionRequestInfo
and others

for example when we are used non-ANSI password fo FB my path is monstr:

public class JNMXFBDriver extends org.firebirdsql.jdbc.FBDriver
{
private final Map mcfToDataSourceMap = new HashMap();

@Override
public Connection connect\(String url, Properties originalInfo\)
    throws SQLException //, GDSException, FBResourceException
\{
    final GDSType type = GDSFactory\.getTypeForProtocol\(url\);

    if \(type == null\)
        return null;

    try \{
        if \(originalInfo == null\)
            originalInfo = new Properties\(\);

        Map normalizedInfo = FBDriverPropertyManager\.normalize\(url, originalInfo\);

        int qMarkIndex = url\.indexOf\('?'\);
        if \(qMarkIndex \!= \-1\)
            url = url\.substring\(0, qMarkIndex\);

        JNMXFBManagedConnectionFactory mcf = new JNMXFBManagedConnectionFactory\(type\);

        String databaseURL = GDSFactory\.getDatabasePath\(type, url\);

        mcf\.setDatabase\(databaseURL\);
        for \(Iterator iter = normalizedInfo\.entrySet\(\)\.iterator\(\); iter\.hasNext\(\);\) \{
            Map\.Entry entry = \(Map\.Entry\) iter\.next\(\);

            mcf\.setNonStandardProperty\(\(String\)entry\.getKey\(\), \(String\)entry\.getValue\(\)\);
        \}

        FBConnectionHelper\.processTpbMapping\(mcf\.getGDS\(\), mcf, originalInfo\);

        mcf = \(JNMXFBManagedConnectionFactory\)mcf\.canonicalize\(\);

        FBDataSource dataSource = createDataSource\(mcf\);

        return dataSource\.getConnection\(mcf\.getUserName\(\), mcf\.getPassword\(\)\);

    \}
    catch\(ResourceException resex\)
    \{
        throw new FBSQLException\(resex\);
    \} 
    catch\(GDSException ex\)
    \{
        throw new FBSQLException\(ex\);
    \}
\}
private FBDataSource createDataSource\(FBManagedConnectionFactory mcf\) throws ResourceException 
\{
    FBDataSource dataSource = null;
    synchronized \(mcfToDataSourceMap\)
    \{
        dataSource = \(FBDataSource\)mcfToDataSourceMap\.get\(mcf\);

        if \(dataSource == null\) \{
            dataSource = \(FBDataSource\)mcf\.createConnectionFactory\(\);
            mcfToDataSourceMap\.put\(mcf, dataSource\);
        \}
    \}
    return dataSource;
\}

}
class JNMXFBConnectionRequestInfo extends FBConnectionRequestInfo
{
public JNMXFBConnectionRequestInfo(DatabaseParameterBuffer dpb)
{
super(dpb);
}
@OverRide
public void setPassword(String password)
{
removeArgument(DatabaseParameterBufferExtension.PASSWORD);
if (password != null)
{
try
{
addArgument(DatabaseParameterBufferExtension.PASSWORD, password.getBytes("Cp1251"));
}
catch (UnsupportedEncodingException ex)
{
Logger.getLogger(FBConnectionRequestInfo.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
class JNMXFBManagedConnectionFactory extends FBManagedConnectionFactory
{
// private ConnectionManager defaultCm = new FBStandAloneConnectionManager();
JNMXFBManagedConnectionFactory(GDSType type)
{
super(type);
// this.setDefaultConnectionManager(defaultCm);
}
@OverRide
public FBConnectionRequestInfo getDefaultConnectionRequestInfo() throws ResourceException
{
try
{
return new JNMXFBConnectionRequestInfo(getDatabaseParameterBuffer().deepCopy());
}
catch(SQLException ex)
{
throw new FBResourceException(ex);
}
}
}

but i`m not find easy way to use myFBResultSet :)

@firebird-automations
Copy link
Author

Commented by: Account systems Ltd. (nmx)

-

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

The CachedRowSetImpl thing is a known bug in the Sun/Oracle implementation of the CachedRowSet interface, see the releasenotes section 'Compatibility with com.sun.rowset.*' and http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=7046875 The person that wrote CachedRowSetImpl was - apparently - also confused by the distinction between columnName and columnLabel.

Furthermore do not dump unrelated things into this ticket, please mail to the Firebird-Java mailinglist to discuss things or create a separate ticket for a feature request.

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

assignee: Roman Rokytskyy [ rrokytskyy ] => Mark Rotteveel [ avalanche1979 ]

@firebird-automations
Copy link
Author

Commented by: Account systems Ltd. (nmx)

Ok, I`m sorry.

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Given the confusion this change is causing, I am considering to introduce a property which will report the columLabel for the name as well (as for example Sybase does, see http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc39001.0605/html/prjdbc/prjdbc14.htm property GET_COLUMN_ LABEL_FOR_NAME )

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Scheduled for 2.3 and 2.2.1, no final decision made yet

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Fix Version: Jaybird 2.2.1 [ 10474 ]

Fix Version: Jaybird 2.3 [ 10440 ]

@firebird-automations
Copy link
Author

Commented by: @mrotteveel

Added connection property columnLabelForName which will make the ResultSetMetaData.getColumnName return the same value as getColumnLabel (note: this is not JDBCcompliant!)

This property can be added to the connection properties:
* Through a properties object (with value true)
* In the connection URL (columnLabelForName=true)
* On a DataSource by setting setNonStandardProperty("columnLabelForName", "true") or setNonStandardProperty("columnLabelForName=true");

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

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

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue depends on JDBC162 [ JDBC162 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue depends on JDBC162 [ JDBC162 ] =>

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue is related to JDBC160 [ JDBC160 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue is duplicated by JDBC278 [ JDBC278 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue is related to JDBC160 [ JDBC160 ] =>

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue is related to JDBC162 [ JDBC162 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue relate to JDBC298 [ JDBC298 ]

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

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

@firebird-automations
Copy link
Author

Modified by: @mrotteveel

Link: This issue is duplicated by JDBC356 [ JDBC356 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment