sqlread specific columns/variables from a MySQL table

I want to import to Matlab one or a couple specific columns from a table that is stored in MySQL database. sqlread(conn, tablename) would import the whole table. I tried playing around with opts. it didn't work. Is there any way to make this work? Or should I try using sql query script? Thanks!

opts = databaseImportOptions(conn, tablename);
opts = setoptions(opts, 'SelectedVaraibleNames', {'wanted_column'}); % error message

[Jeff Miller]

What was the error message? Note there is a typo in your post--"SelectedVaraibleNames" is mis-spelled. Is it mis-spelled in your code too?

[Simon]

The spelling in my real codes is correct. I misspelled it in this post. The error message I had was

Error using database.options.SQLImportOptions/setoptions

Expected wanted_column options to match one of these values:

'MissingRule', 'Name', 'Type', 'FillValue', 'WhitespaceRule', 'TextCaseRule'

The input, 'SelectedVariableNames', did not match any of the valid values.

[Simon]

@Walter Roberson, @Jeff Miller Thanks for pointing out the misspelling in the codes. After I corrected that, I tried

opts = databaseImportOptions(conn, "myTable")
opts.SelectedVariableNames = {'wanted_cloumn'}

And I got the error message:

------------------------- Error Message --------------------

Error using database.relational.connection/fetch

Unable to use database import options with

'SELECT * from myTable'.

Error in database.mysql.connection/sqlreadHook (line 4)

[T,metadata] = fetch(connect,sqlquery,optsObject,'MaxRows',maxRows);

Error in database.relational.connection/sqlread (line 689)

data = sqlreadHook(connect,query,optsObject,maxRows,preservenames,isvarnamerulespecified);

----------------------------

[Simon]

The correct SQL query should be

SELECT wanted_column FROM DATABASE.myTable;

Matlab had not translated opts.SelectedVariableNames = wanted_column and mysqlread(....., opts) into that sql query. Instead, it translated to Select * from myTable. I guess I didn't understand how the sql input options work at all.

[Simon]

So I ran debugging. In connection.m, I found

 %Construct the query
querybuilder = database.internal.utilities.SQLQueryBuilder;
querybuilder = querybuilder.select("*").from(tablename);
 %.....
querybuilder = dispatcher.dispatch(rowFilter,querybuilder,connect.DatabaseProductName);
query = strtrim(querybuilder.SQLQuery);

Obviously, the querybuilder is not affected by opts.SelectedVariableNames at all. And it seems that, according to the way query is made, it's impossible to select specific columns for sql import.

Answer 1

[Simon]

The answer to my own questiion is fetch. sqlread always reads in the whole table.

sqlquery = 'select wanted_column from myTable';
wanted = fetch(conn, sqlquery)

Answer 2

[Walter Roberson]

opts = setoptions(opts, 'SelectedVariableNames', {'wanted_column'});

You had 'SelectedVaraibleNames' instead of 'SelectedVariableNames'