matlab-游标及查询

接上篇博文

exec创建游标

>> close(myconn)
>> myconn=database('cdcol','mytest','deepfuture','com.mysql.jdbc.Driver','jdbc:mysql://localhost:3306/cdcol')
 
myconn =
 
       Instance: 'cdcol'
       UserName: 'mytest'
         Driver: 'com.mysql.jdbc.Driver'
            URL: 'jdbc:mysql://localhost:3306/cdcol'
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 com.mysql.jdbc.JDBC4Connection]
        TimeOut: 0
     AutoCommit: 'on'
           Type: 'Database Object'

>> mycurs=exec(myconn,'select * from cds')
 
mycurs =
 
        Attributes: []
              Data: 0
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from cds'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: 0

>>

 

 

 help exec
 --- help for database/exec ---

 exec Execute SQL statement and open Cursor
    CURSOR = exec(CONNECT,SQLQUERY,QTIMEOUT) returns a cursor object
    CONNECT is a database object returned by DATABASE. sqlQuery
    is a valid SQL statement. Use FETCH to retrieve data associated
    with CURSOR.
 
    Example:
 
    cursor = exec(connect,'select * from emp')
 
    where:
 
    connect is a valid database object.
 
    'select * from emp' is a valid SQL statement that selects all
    columns from the emp table.
 
    See also fetch.

查询结果

fetch(mycurs)
 
ans =
 
        Attributes: []
              Data: {3x4 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from cds'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

>> a=ans

>> a.Data

ans =

    'Beauty'       'Ryuichi Sakamoto'    [1990]    [1]
    [1x33 char]    'Groove Armada'       [2001]    [4]
    'Glee'         'Bran Van 3000'       [1997]    [5]

 
>> mydata=a.Data

mydata =

    'Beauty'       'Ryuichi Sakamoto'    [1990]    [1]
    [1x33 char]    'Groove Armada'       [2001]    [4]
    'Glee'         'Bran Van 3000'       [1997]    [5]

>> mydata{1,2}

ans =

Ryuichi Sakamoto

>> mydata{1,1}

ans =

Beauty

>> mydata{1,3}

ans =

        1990 

 

查询参数

>> cs='Beauty'

cs =

Beauty

>> mycurs=exec(myconn,['select * from cds where titel =''',cs,''''])
 
mycurs =
 
        Attributes: []
              Data: 0
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from cds where titel ='Beauty''
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: 0

>> myres=fetch(mycurs)
 
myres =
 
        Attributes: []
              Data: {'Beauty'  'Ryuichi Sakamoto'  [1990]  [1]}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from cds where titel ='Beauty''
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

>>

 

格式的配置

>> setdbprefs
 
            DataReturnFormat: 'cellarray'
               ErrorHandling: 'store'
              NullNumberRead: 'NaN'
             NullNumberWrite: 'NaN'
              NullStringRead: 'null'
             NullStringWrite: 'null'
          JDBCDataSourceFile: ''
       UseRegistryForSources: 'yes'
    TempDirForRegistryOutput: 'C:\Users\DEEPFU~1\AppData\Local\Temp'
          DefaultRowPreFetch: '10000'

>>

 

 

>> help setdbprefs
 SETDBPREFS Set preferences for database actions for handling null values.
    SETDBPREFS(P,V) sets the preferences for database actions.  P is the
    list of properties to be set and V is the corresponding value list.
 
    SETDBPREFS(P) returns the property with its current setting.
 
    SETDBPREFS returns the property list with all current values.
 
    The valid properties are NullNumberRead, NullNumberWrite, NullStringRead,
    NullStringWrite, DataReturnFormat, ErrorHandling and JDBCDataSourceFile.
    The value for each property is entered as a string.
 
    For example, the command
 
       setdbprefs('NullStringRead','null')
  
    translates all NULL strings read from the database into the string
    'null'.
 
    The command
 
       setdbprefs({'NullStringRead';'NullStringWrite';'NullNumberRead';'NullNumberWrite'},...
                  {'null';'null';'NaN';'NaN'})
 
    translates NULL strings read into the string 'null', NULL values to NaN.  A NaN in the
    data written to the database is translated to a NULL and a 'null' string is translated to
    NULL.
 
    The command setdbprefs('DataReturnFormat','cellarray') returns the data
    in the cursor Data field as a cell array which is the default behavior.  
    Other values for DataReturnFormat are 'numeric' which returns the data
    as a matrix of doubles and 'structure' which returns the data as a structure
    with the fieldnames corresponding to the fetched fields.
 
    The command setdbprefs('ErrorHandling','store') returns any error messages
    to the object Message field and will cause the next function that uses the
    object to return an error.   This is the default behavior.   Other values
    for ErrorHandling are 'report' which causes any function encountering an error
    to report the error and stop processing and 'empty' which causes the fetch
    command to return the cursor Data field as [] when given a bad cursor object
    resulting from exec.
 
    The command setdbprefs('JDBCDataSourceFile','d:\work\datasource.mat')
    sets the location of the JDBC data source information to the file
    d:\work\datasource.mat.  This enables the Visual Query Builder to use
    both ODBC and JDBC data sources.   The command setdbprefs('JDBCDataSourceFile','')
    specifies that no JDBC data sources are being used by the Visual Query Builder.
 
    A single input can be used if it is a structure with fields corresponding to the valid
    preference names.   Each field should contain a valid preferenc setting.   For example,
 
      p.DataReturnFormat = 'cellarray';
      setdbprefs(p)

 

带限制输入行数的fetch

>> mycurs=exec(myconn,'select * from cds')
 
mycurs =
 
        Attributes: []
              Data: 0
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from cds'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: 0

>> myres=fetch(mycurs,1)
 
myres =
 
        Attributes: []
              Data: {'Beauty'  'Ryuichi Sakamoto'  [1990]  [1]}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from cds'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

 

字段相关属性

>> mycurs=exec(myconn,'select * from cds')
 
mycurs =
 
        Attributes: []
              Data: 0
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from cds'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: 0

>> myres=fetch(mycurs)
 
myres =
 
        Attributes: []
              Data: {3x4 cell}
    DatabaseObject: [1x1 database]
          RowLimit: 0
          SQLQuery: 'select * from cds'
           Message: []
              Type: 'Database Cursor Object'
         ResultSet: [1x1 com.mysql.jdbc.JDBC4ResultSet]
            Cursor: [1x1 com.mathworks.toolbox.database.sqlExec]
         Statement: [1x1 com.mysql.jdbc.StatementImpl]
             Fetch: [1x1 com.mathworks.toolbox.database.fetchTheData]

行数

>> rows(myres)

ans =

     3

列数

>> cols(myres)

ans =

     4

字段名

>> columnnames(myres)

ans =

'titel','interpret','jahr','id'

>>

 

列宽

1-3列的宽度

>> width(myres,1)

ans =

   200

>> width(myres,2)

ans =

   200

>> width(myres,3)

ans =

    11

>>

 

指定列的属性

下面是第2列属性

>> attr(myres,2)

ans =

      fieldName: 'interpret'
       typeName: 'VARCHAR'
      typeValue: 12
    columnWidth: 200
      precision: []
          scale: []
       currency: 'false'
       readOnly: 'false'
       nullable: 'true'
        Message: []

>>

 

或者

>> myattrs=attr(myres)

myattrs =

1x4 struct array with fields:
    fieldName
    typeName
    typeValue
    columnWidth
    precision
    scale
    currency
    readOnly
    nullable
    Message

>> myattrs(1)

ans =

      fieldName: 'titel'
       typeName: 'VARCHAR'
      typeValue: 12
    columnWidth: 200
      precision: []
          scale: []
       currency: 'false'
       readOnly: 'false'
       nullable: 'true'
        Message: []

>>