Getting database table variable names and type

Hi,

For a project, we do the following to to get data from a database:

connection = database( '', '', '', 'org.sqlite.JDBC', url );
curs = exec( connection, sqlQuery );
queryOut = fetch( curs );
data = queryOut.Data;

However in some cases, it can return no data. In this case, we want to have an empty table with variable names and types being the same as the ones as in the database. For now we have implemented the following:

opts = databaseImportOptions( connection, sqlQuery );
data = table( 'Size' , [ 0, numel( opts.VariableNames ) ], ...
              'VariableTypes', opts.VariableTypes, ...
              'VariableNames', opts.SelectedVariableNames );

It works but I was wondering if there might be something more elegant to fetch varia types and names from the database. Since I am far from being well versed in using database, jdbc etc. in Matlab, it is likely that I could have mised something.

Best

Answer 1

[Vatsal]

Hi @frlby,

I understand that you want a more elegant way to fetch variable types and names from the database. The implementation you have provided using “databaseImportOptions” to fetch variable names and types from the database and create an empty table is already a good approach this is another way you can do the same:

sqlquery = "SELECT * FROM table_name";
[results,metadata] = fetch(conn,sqlquery);


variableNames = metadata.Properties.RowNames;
variableTypes = metadata.VariableType;

data = table('Size', [0, numel(variableNames)], ...
             'VariableTypes', variableTypes, ...
             'VariableNames', variableNames);

You can also refer to the MATLAB documentation for "fetch" to obtain more information on its usage and syntax. The link is provided below: -

https://in.mathworks.com/help/releases/R2021b/database/ug/database.odbc.connection.fetch.html

I hope this works!

[frlby]

Hi,

Thanks a lot for the feedback. However, is it possible that this answer was for an older version of MATLAB? Looking at the documentation on fetch, it seems that it should be:

[ results, metadata ] = fetch( connection, sqlQuery );

This works for me while:

curs = fetch(curs, 'MetaOnly');

Does not.

However, I am still having some issue in the particular case where the SQL query starts with a common table expressions using WITH. In this particular case, my initial approach returns an error because there it does not find any fields and the second one with metadata returns an empty table. Is there some way for MATLAB to properly interpret that?

[Vatsal]

Hi @frlby,

I have updated my answer and can you please provide the SQL query in which you are getting error and also can you please tell where in your intial approach it is throwing the error ?

[frlby]

Hi @Vatsal,

I apologize for the late reply. The error that I now have with your answer is "JDBC Driver Error: Error parsing date". I used the exact same query as you did exept that I used the table name from my db.

In my initial approach, the error is on:

opts = databaseImportOptions( connection, sqlQuery );

Error using database.options.internal.getQueryMetaData (line 20)

Java exception occurred:

java.lang.NullPointerException

at com.mathworks.toolbox.database.DatabaseResultsetMetaData.<init>(DatabaseResultsetMetaData.java:22)

Error in database.options.SQLImportOptions (line 579)

metadata = database.options.internal.getQueryMetaData(connect,opts.Query);

Error in databaseImportOptions (line 50)

opts = database.options.SQLImportOptions(connect,source,varargin{:});

Thanks again for your help.