fetch returns a table full of cells for 'char' values

I use fetch to get data from a MS Azure SQL Database into Matlab. My script worked when I last ran it - approximately 10 days ago. In the meanwhile the DB has been updated - the only change were changes in naming of tables and columns.

However, now I get a table where each field that should contain a character arrays, contains a cell. This makes it hard to work with the character arrays.

I tried to set the DB preferences, but it did not change the output.

setdbprefs('DataReturnFormat', 'table');

I also tried to put my simple SQL statement in a file and use runsqlscript instead of fetch - but I still got the char arrays in cells.

conn = database(datasource, username,password);
runsqlscript(conn,mySQLscript.sql)

This is the code I try to run (with some connetion details changed, of course)

conn = database(datasource, username,password);

sqlTest = 'select * from MyTableOfInterest'

conn = database('MyDB','DB_Reader_user','MyPassword','Vendor','MICROSOFT SQL SERVER','Server','myProjectServer.database.windows.net','PortNumber',1433,'AuthType','Server');
dataDB_ids = fetch(conn,sqlTest);
close(conn)

The result looks like this:

I would like to have is the above picture without the curley brackets.

I know I can do that with using for-loops and changing field per field, but there must be a smarter solution. (Especially since I think the the result looked fine last time I ran the script).

Technical details:

MATLAB Version: 9.7.0.1247435 (R2019b) Update 2

Database Toolbox Version 9.2 (R2019b)

Best regards, Sara

Answer 1

[Harsha Priya Daggubati]

Hi,

Can you check the datatype of the variables present in the table, by making use of the metadata returned by 'fetch'.https://www.mathworks.com/help/database/ug/database.odbc.connection.fetch.html?searchHighlight=fetch&s_tid=doc_srchtitle

You can use setoptions method to specify the type for columns. Refer the link for more details.

https://www.mathworks.com/help/database/ug/database.odbc.connection.fetch.html#bt2sx81-1_sep_mw_c5cd033e-a425-4e83-817b-341618f23237

https://www.mathworks.com/help/database/ug/database.options.sqlimportoptions.html

https://www.mathworks.com/help/database/ug/database.options.sqlimportoptions.databaseimportoptions.html

[Sara Brunner]

Thanks Harsha,

I set the sqlimportoptions VariableTypes to 'char', but I still get {'char'} as a result in my table.

I guess it has something to do with fields containing NULL even if I do not have any in my selection.

However, I implemented a workaround (with for loops) which decreases the performance, but it works.