1 | function tbread(table_tbread,varargin) |
---|
2 | % TBREAD Read from a MySQL table |
---|
3 | % INPUTS : TABLE - table name, string |
---|
4 | % VECS - list of output vectors, (m x 1) or (1 x m) cell array |
---|
5 | % (optional, COLS converted to lower case if VECS = {} or omitted |
---|
6 | % but COLS is specified; names of all TABLE columns, converted to |
---|
7 | % lower case, if VECS = {} or omitted and COLS = {} or omitted) |
---|
8 | % COLS - list of input columns, (m x 1) or (1 x m) cell array |
---|
9 | % (optional, all columns retrieved if COLS = {} or omitted) |
---|
10 | % STR - string containing a combination of WHERE, ORDER BY, GROUP BY, |
---|
11 | % HAVING or LIMIT clauses, passed within a SELECT query (optional) |
---|
12 | % Examples: 'where name = ''John''', 'order by x', 'limit 100' |
---|
13 | % OUTPUTS : None |
---|
14 | % EXAMPLE : cols = {'customer','date','price'}; |
---|
15 | % global customer date price |
---|
16 | % tbread('orders',{},cols,'where date > ''1995-01-31'' and price > 100') |
---|
17 | % |
---|
18 | % cols = {'my_numeric','my_char'}; |
---|
19 | % vecs = {'x','y'}; |
---|
20 | % global x y |
---|
21 | % tbread('xytable',vecs,cols) |
---|
22 | % |
---|
23 | % global N |
---|
24 | % N = nan(tbsize('ntable',1),10); |
---|
25 | % vecs = cellstr(strcat('N(:,', int2str((1:10)'), ')')); |
---|
26 | % tbread('ntable',vecs) |
---|
27 | % |
---|
28 | % NOTES : 1. Variables referenced in VECS must be declared global before TBREAD is |
---|
29 | % invoked. |
---|
30 | % 2. VECS elements are case-sensitive. COLS elements are case-insensitive |
---|
31 | % if MySQL runs on Windows, but case-sensitive under Unix. Leading and |
---|
32 | % trailing blanks are removed from strings in both arrays. |
---|
33 | % 3. Use mym.m to work with BLOB columns. |
---|
34 | % 4. Use DBASE.TABLE syntax to refer to tables not in current database. |
---|
35 | % 5. If the number of columns to retrieve is small, data can be read more |
---|
36 | % conveniently with mym.m: |
---|
37 | % |
---|
38 | % [customer,date,price] = mym('select customer,date,price from orders') |
---|
39 | % |
---|
40 | % [x,y] = mym('select my_numeric, my_char from xytable'); |
---|
41 | % |
---|
42 | % AUTHOR : Dimitri Shvorob, dimitri.shvorob@vanderbilt.edu, 8/7/06 |
---|
43 | error(nargchk(1,4,nargin)) |
---|
44 | if ~mycheck |
---|
45 | error('No MySQL instance detected. Use MYOPEN to connect.') |
---|
46 | end |
---|
47 | try |
---|
48 | [a,b,c,d,e,f] = mym(['describe ' table_tbread]); %#ok |
---|
49 | catch |
---|
50 | error(['Table ' table_tbread ' not found. Use TBLIST to list available tables.']) |
---|
51 | end |
---|
52 | if nargin > 3 |
---|
53 | str_tbread = varargin{3}; |
---|
54 | else |
---|
55 | str_tbread = ''; |
---|
56 | end |
---|
57 | allcols_tbread = tbattr(table_tbread); |
---|
58 | if nargin > 2 |
---|
59 | cols_tbread = strtrim(varargin{2}); |
---|
60 | if isempty(cols_tbread) | cellfun('isempty',cols_tbread) %#ok |
---|
61 | cols_tbread = allcols_tbread; |
---|
62 | end |
---|
63 | else |
---|
64 | cols_tbread = allcols_tbread; |
---|
65 | end |
---|
66 | if nargin > 1 |
---|
67 | vecs_tbread = strtrim(varargin{1}); |
---|
68 | if isempty(vecs_tbread) | cellfun('isempty',vecs_tbread) %#ok |
---|
69 | vecs_tbread = lower(allcols_tbread); |
---|
70 | end |
---|
71 | else |
---|
72 | vecs_tbread = lower(allcols_tbread); |
---|
73 | end |
---|
74 | if ~iscell(vecs_tbread) |
---|
75 | error('VECS must be a cell array.') |
---|
76 | end |
---|
77 | if ~iscell(cols_tbread) |
---|
78 | error('COLS must be a cell array.') |
---|
79 | end |
---|
80 | if ~ischar(str_tbread) |
---|
81 | error('STR must be a string.') |
---|
82 | end |
---|
83 | if ~isvector(vecs_tbread) |
---|
84 | error('VECS must be a cell vector.') |
---|
85 | end |
---|
86 | if ~isvector(cols_tbread) |
---|
87 | error('COLS must be a cell vector.') |
---|
88 | end |
---|
89 | K_tbread = length(cols_tbread); |
---|
90 | if K_tbread ~= length(vecs_tbread) |
---|
91 | error('COLS and VECS have different lengths.') |
---|
92 | end |
---|
93 | if any(cellfun('isempty',cols_tbread)) |
---|
94 | error('COLS contains empty cells.') |
---|
95 | end |
---|
96 | if any(cellfun('isempty',vecs_tbread)) |
---|
97 | error('VECS contains empty cells.') |
---|
98 | end |
---|
99 | mym(['lock tables ' table_tbread ' write']) |
---|
100 | for k_tbread = 1:K_tbread |
---|
101 | col_tbread = cols_tbread{k_tbread}; |
---|
102 | if ~any(strcmpi(allcols_tbread,col_tbread)) |
---|
103 | error(['Column ' col_tbread ' not found in table ' table_tbread '. Use TBATTR to list available columns.']) |
---|
104 | else |
---|
105 | vec_tbread = vecs_tbread{k_tbread}; |
---|
106 | par_tbread = findstr(vec_tbread,'('); |
---|
107 | if par_tbread |
---|
108 | vec_reduced_tbread = vec_tbread(1:par_tbread-1); |
---|
109 | else |
---|
110 | vec_reduced_tbread = vec_tbread; |
---|
111 | end |
---|
112 | warning('off','MATLAB:declareGlobalBeforeUse') |
---|
113 | try |
---|
114 | eval(['global ' vec_reduced_tbread '; ' vec_tbread ' = mym('' select ' cols_tbread{k_tbread} ' from ' table_tbread ' ' str_tbread ' '');']) |
---|
115 | catch |
---|
116 | cleanup |
---|
117 | error('Read failed. Check if VECS contains valid variable names.') |
---|
118 | end |
---|
119 | end |
---|
120 | end |
---|
121 | cleanup |
---|
122 | |
---|
123 | function cleanup |
---|
124 | mym('unlock tables') |
---|
125 | warning('on','MATLAB:declareGlobalBeforeUse') |
---|