Why does Database Toolbox return the result of a query in a 1-by-1 structure?

I use the Database Toolbox to read data from a database into a MATLAB structure as follows:

curs = exec(conn, 'sqlquery');  % where sqlquery is a stored SQL procedure.

setdbprefs('DataReturnFormat','structure')

curs = fetch(curs);

where "conn" is the name of my database connection, and it contains N rows of some multicolumn data. The data is returned in "curs.data". I expect "curs.data" to be a 1-by-N array of structures, each of which has fields corresponding to my column names. However, "curs.data" is returned as a 1-by-1 structure whose fields are the columns of the database, for example, "Name" and "Billing". Then "curs.data.Name" and "curs.data.Billing" are each a 1xN cell arrays. Therefore, to access the information for the first row, one would have to execute:

curs.data.Name(1)

curs.data.Billing(1)

It would be more convenient if "curs.data" was in fact a 1xN structure array, so I could do

curs.data(1)

and that would give me the entry/record for the first row.

Answer 1

[MathWorks Support Team]

The ability to return the data as a 1-by-N array of structures rather than a 1-by-1 structure is not available in the Database Toolbox.

The current format was chosen because the 1-by-1 structure with each field being a matrix or cell array of the data is much less memory intensive than the 1-by-N array of structures. This may not be a problem for small data sets; however, large data sets will become much more cumbersome as 1-by-N arrays.

One possible workaround for this issue is to read the data as a cell array, and then convert it to a MATLAB structure. This approach is outlined below:

First, change your query as follows:

curs = exec(conn, 'sqlquery');  % where sqlquery is a stored SQL procedure.

setdbprefs('DataReturnFormat','cellarray')  % This is the default setting

curs = fetch(curs);

Now "curs.data" will be returned as an N-by-M cell array where M is the number of columns in the database. You can then read the column names in the database by using the COLUMNNAMES function:

colNames = columnnames(curs);

The column names are returned in a single string as quoted strings separated by commas. A simple function can be constructed to take the cell array along with the column names and return a 1-by-N structure array. One such function is shown below:

function s = myCell2Struct (C, fieldNames)

      rem = regexprep(fieldNames,'''','');  % to remove the single-quotes

      i=1;

      while ~isempty(rem)

          [f{i} rem] = strtok(rem,', ');    % returns the first word in the string

          i = i+1;

      end

      s = cell2struct(C,f,2);

You can use this function with your original data to convert it to a structure:

myDataStruct = myCell2Struct (curs.data, colNames);

"myDataStruct" will be a 1-by-N array of structures. Each structure will have fields whose names correspond to the column names in the data base.

Note that the code given here is simply an outline, and should only be used to help you construct your own routines that will work in your particular case.