Issue Details (XML | Word | Printable)

Key: JDBC-260
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Trivial Trivial
Assignee: Mark Rotteveel
Reporter: Account systems Ltd.
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Jaybird JCA/JDBC Driver

Error function findColumn not working with composed field alias

Created: 31/Jul/12 06:44 PM   Updated: 21/Feb/13 08:10 PM
Component/s: JDBC driver
Affects Version/s: Jaybird 2.2
Fix Version/s: Jaybird 2.2.1, Jaybird 3.0

Time Tracking:
Not Specified

Environment: Firebird-2.1.5.18496_0_Win32 JDK1.6 Win7 64 and Linux openSuse 11.4 amd64
Issue Links:
Duplicate
 
Relate


 Description  « Hide
 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());
    }
}



 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Mark Rotteveel added a comment - 04/Aug/12 04:34 PM
This is not a bug, but an intentional change done in JDBC-162 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.

Mark Rotteveel added a comment - 04/Aug/12 04:35 PM
Changed priority to trivial, need to consider whether to change current behavior for sqlname == null

Account systems Ltd. added a comment - 05/Aug/12 08:37 AM
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);
            
        }
     }
 

Account systems Ltd. added a comment - 05/Aug/12 08:55 AM - edited
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 :)




Account systems Ltd. added a comment - 05/Aug/12 09:11 AM - edited
-

Mark Rotteveel added a comment - 05/Aug/12 11:50 AM
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.

Account systems Ltd. added a comment - 05/Aug/12 01:05 PM
Ok, I`m sorry.

Mark Rotteveel added a comment - 11/Aug/12 07:53 AM
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 )

Mark Rotteveel added a comment - 18/Aug/12 07:34 AM
Scheduled for 2.3 and 2.2.1, no final decision made yet

Mark Rotteveel added a comment - 09/Sep/12 09:32 AM
Added connection property columnLabelForName which will make the ResultSetMetaData.getColumnName return the same value as getColumnLabel (note: this is not JDBC-compliant!)

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");