MATLAB: Retrieve all data from columns apart from id when using MySQL

msql matlab databases

Hello, I'm new to Matlab and Mysql and need a little help. I have a database which is storing the results of an analysis which repeated 150 times, thus one dataset has 150 results for the specifity etc.
I have managaged to automate it so the data goes into the columns i need, the problem i have is extracting it so it can be displayed in a GUI to later then be graphed.
I was using this to take data out of my database:
smp = exec(conn, 'select sample from smp_table') smp = fetch(smp); get(smp, 'Data')
I know i can switch te "sample" to a "*" to get everything from the table, which is what i want, but I DON't want the primary key and cannot seem to execute this.
This is the code i am using to access all the data i want via command line (terminal) for mysql:
SET @sql = CONCAT("SELECT ", (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), 'idTestTbl,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TestTbl' AND TABLE_SCHEMA = 'MatSch_2'), ' FROM TestTbl');
prepare stmt1 from @sql;
execute stmt1;
Now i have tried something like this in Matlab, but cannot get it to work:
sql = 'SET @sql = CONCAT("SELECT ", (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), "idTestTbl,", '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = "TestTbl" AND TABLE_SCHEMA = "MatSch_2"), "FROM TestTbl"); prepare stml from @sql'
smp = exec(conn, sql) smp = fetch(smp); get(smp, 'Data')
Can anyone point me in the right direction? Ultimatly i just want to extract all the contents of my table excluding the primary key column…would like a small script for this as i have 150 columns
Thank you in advance
P.S on Matlab 7.6.0 R2008A

Best Answer

Hi Gurvinder,
Can I suggest that I think it would be easier for you to simply get all the fields, and then just discard the one you don't want. Here's some MATLAB code that does exactly that:
% Make a simple SQL query
yourTable = 'smp_table';
keyField = 'ID';
SQLstr = sprintf('SELECT * FROM %s',yourTable);
% Get the data
curs = exec(conn, sqlStr);
curs = fetch(curs,0);
myData = curs.Data;
% Get the fieldnames of the data
myFields = cellfun(@char, get(rsmd(resultset(curs)), 'ColumnName'),'Un',0);
% Drop the primary key from the data (and fields in case you need them)
keyFldMask = strcmpi(keyField, myFields);
myData(:,keyFldMask) = [];
myFields(keyFldMask) = [];
The code above is relatively simple compared to the mysql concatenations from the table schema, and achieves the exact same result.
Is that an acceptable solution? I always prefer to muck about with data in MATLAB when SQL gets too messy :)
Related Question