使用MATLAB从MySQL数据库检索Blob字段 [英] Retrieve blob field from mySQL database with MATLAB

问题描述

我正在使用JDBC和 mySQL Java连接器访问公共mySQL数据库. exonCount是int(10)，exonStarts和exonEnds是 longblob 字段.

I'm accessing public mySQL database using JDBC and mySQL java connector. exonCount is int(10), exonStarts and exonEnds are longblob fields.

javaaddpath('mysql-connector-java-5.1.12-bin.jar')
host = 'genome-mysql.cse.ucsc.edu';
user = 'genome';
password = '';
dbName = 'hg18'; 
jdbcString = sprintf('jdbc:mysql://%s/%s', host, dbName);
jdbcDriver = 'com.mysql.jdbc.Driver';
dbConn = database(dbName, user , password, jdbcDriver, jdbcString);
gene.Symb = 'CDKN2B';
% Check to make sure that we successfully connected
if isconnection(dbConn)
    qry = sprintf('SELECT exonCount, exonStarts, exonEnds FROM refFlat WHERE geneName=''%s''',gene.Symb);
    result = get(fetch(exec(dbConn, qry)), 'Data');
    fprintf('Connection failed: %s\n', dbConn.Message);
end

这是结果:

result = 
    [2]    [18x1 int8]    [18x1 int8]
    [2]    [18x1 int8]    [18x1 int8]

result{1,2}'
ans =
   50   49   57   57   50   57   48   49   44   50   49   57   57   56   54   55   51   44

这是错误的.第2列和第3列中向量的长度应与第1列中的数字匹配.

This is wrong. The length of vectors in 2nd and 3rd columns should match the numbers in the 1st column.

例如，第一个Blob应该为[21992901; 21998673].我该如何转换呢?

The 1st blob, for example, should be [21992901; 21998673]. How I can convert it?

更新:

提交此问题后，我认为它可能是字符串的十六进制表示形式. 并确认:

Just after submitting this question I thought it might be hex representation of a string. And it was confirmed:

>> char(result{1,2}')
ans =
21992901,21998673,

所以现在我需要将所有Blob十六进制数据转换为数值向量.由于行数可能很大，因此仍在考虑以矢量化的方式进行操作.

So now I need to convert all blobs hex data into numeric vectors. Still thinking to do it in a vectorized way, since number of rows can be large.

推荐答案

这会将您的字符数据转换为除result中第一列数据以外的所有数据的数字矢量，并将结果放回适当的单元格中:

This will convert your character data to numeric vectors for all except the first column of data in result, placing the results back into the appropriate cells:

result(:,2:end) = cellfun(@(x) str2num(char(x'))',...  %# Apply fcn to each cell
                          result(:,2:end),...          %# Input cells
                          'UniformOutput',false);      %# Output as a cell array

这篇关于使用MATLAB从MySQL数据库检索Blob字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！