matlab淡化游标,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: []

>>