column aliases not shown with columnames

Is it possilbe to get the column alias names from a query using the Matlab Database toolbox?

I'm running Matlab R2011b and using the Database Toolbox to interface with a MySQL database. The connector I'm using is:

mysql-connector-java-5.1.20-bin.jar

Here's the database object I create:

>> dbh

dbh =

       Instance: 'instance'
       UserName: 'myname'
         Driver: 'com.mysql.jdbc.Driver'
            URL: 'jdbc:mysql://mysql1.m.r.edu/coolops'
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 com.mysql.jdbc.JDBC4Connection]
        TimeOut: 5
     AutoCommit: 'on'
           Type: 'Database Object'

When I execute a query that uses a column alias:

>> sth = exec(dbh, 'SELECT id AS did FROM missions')

and fetch the first result:

>> sth = fetch(sth,1)

sth =

        Attributes: []
              Data: {[2]}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'SELECT id AS did FROM missions'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

Checking the column names:

>> columnnames(sth, true)

ans =

    'id'

The column alias does not show up. Rather it's the actual column name from the table. This becomes a problem when trying to do a join on 2 tables that have the same column name since I don't have the column alias names from the query in the result.

How do you get column aliases from a result set?

Answer 1

[RH]

It’s because that Matlab toolbox use columnName from JDBC.ResultSets as variable names. For some version of JDBC the column alias are stored in columnLabels.

Here is the way to fix it. Assume you set the return data format to table,

curs = exec(conn,sqlstring);
rs = fetch(curs);
res = rs.Data;
numCols = curs.ResultSet.getMetaData().getColumnCount();
colLabels = cell(1,numCols);
for i_col=1:numCols
   colLabels(i_col) = curs.ResultSet.getMetaData().getColumnLabel(i_col);
end    
res.Properties.VariableNames = colLabels;

[Matt Gaidica]

Brilliant. I use it like this to find certain labels in a returned data set. I really want to eliminate having to reference columns as integers when doing a big join that returns data from multiple tables, and when two tables have a 'name' field I can't use columnnames.

function T = fetch2(conn,qry,err)
curs = fetch(exec(conn,qry));
T = curs.Data;
% handle empty return
if ~isempty(err)
    if isempty(T)
        error(err);
    end
end

T.Properties.VariableNames = columnlabels(curs);
close(curs);

This is using the table return format after opening the connection:

setdbprefs('DataReturnFormat','table');

Answer 2

[Jordan]

It's years late, but I had the same problem and nobody online had solved it for me. Try the attached.