matlab-数据库连接

1、连接数据库

1)相关参数

help database
  Database Toolbox
  Version 3.7 (R2010a) 25-Jan-2010
 
   General functions:
 
   chkprops      - Database object properties.
   logintimeout  - Set or get time allowed to establish database connection.
   setdbprefs    - Set preferences for database actions for handling null values.
 
   Database Access functions:
 
   database/catalogs           - Get database catalog names.
   database/clearwarnings      - Clear warnings for database connection.
   database/close              - Close database connection.
   database/columns            - Get database table column names.
   database/commit             - Make database changes permanent.
   database/database           - Connect to database.
   database/exec               - Execute SQL statement and open cursor.
   database/fetch              - Import data into MATLAB using connection handle.
   database/get                - Get database property.
   database/insert             - Export MATLAB cell array data to database table.
   database/isconnection       - Detect if database connection is valid.
   database/isreadonly         - Detect if database connection is read-only.
   database/ping               - Get status information about database connection.
   database/rollback           - Undo database changes.
   database/runstoredprocedure - Stored procedures with input and output parameters.
   database/schemas            - Get database schema names.
   database/set                - Set properties for database connection.
   database/sql2native         - Convert JDBC SQL grammar into system's native SQL grammar.
   database/tables             - Get database table names.
   database/update             - Replace data in database table with data from MATLAB cell array.
 
   Database Cursor Access functions:
 
   cursor/attr         - Get attributes of columns in fetched data set.
   cursor/close        - Close cursor.
   cursor/cols         - Get number of columns in fetched data set.
   cursor/columnnames  - Get names of columns in fetched data set.
   cursor/fetch        - Import data into MATLAB.
   cursor/get          - Get property of cursor object.
   cursor/querytimeout - Get time allowed for a database SQL query to succeed.
   cursor/rows         - Get number of rows in fetched data set.
   cursor/set          - Set RowLimit for cursor fetch.
   cursor/width        - Get width of column in fetched data set.
 
   Database Toolbox Object functions:
 
   dbtbx/dbtbx      - Construct Database Toolbox object.
   dbtbx/display    - Database Toolbox object display method.
   /dbtbx/subsasgn   - Subscripted assignment for Database Toolbox object.
   dbtbx/subsref    - Subscripted reference for Database Toolbox object.
 
   Database Toolbox Database MetaData functions:
 
   dmd/bestrowid         - Get database table unique row identifier.
   dmd/columnprivileges  - Get database column privileges.
   dmd/columns           - Get database table column names.
   dmd/crossreference    - Get information about primary and foreign keys.
   dmd/dmd               - Construct database metadata object.
   dmd/exportedkeys      - Get information about exported foreign keys.
   dmd/get               - Get database metadata properties.
   dmd/importedkeys      - Get information about imported foreign keys.
   dmd/indexinfo         - Get indices and statistics for database table.
   dmd/primarykeys       - Get primary key information for database table or schema.
   dmd/procedurecolumns  - Get catalog's stored procedure parameters and result columns.
   dmd/procedures        - Get catalog's stored procedures.
   dmd/supports          - Detect if property is supported by database metadata.
   dmd/tableprivileges   - Get database table privileges.
   dmd/tables            - Get database table names.
   dmd/versioncolumns    - Get automatically updated table columns.
 
   Database Toolbox Driver functions:
 
   driver/driver     - Construct database driver object.
   driver/get        - Get database driver properties.
   driver/isdriver   - Detect if driver is a valid JDBC driver object.
   driver/isjdbc     - Detect if driver is JDBC-compliant.
   driver/isurl      - Detect if database URL is valid.
   driver/register   - Load database driver.
   driver/unregister - Unload database driver.
 
   Database Toolbox Drivermanager functions:
 
   drivermanager/drivermanager  - Construct database drivermanager object.
   drivermanager/get            - Get database drivermanager properties.
   drivermanager/set            - Set database drivermanager properties.
 
   Database Toolbox Resultset functions:
 
   resultset/clearwarnings  - Clear the warnings for the resultset.
   resultset/close          - Close resultset object.
   resultset/get            - Get resultset properties.
   resultset/isnullcolumn   - Detect if last record read in resultset was null.
   resultset/namecolumn     - Map resultset column name to resultset column index.
   resultset/resultset      - Construct resultset object.
 
   Database Toolbox Resultset MetaData functions:
 
   rsmd/rsmd  - Construct resultset metadata object.
   rsmd/get   - Get resultset metadata properties.
 
   Database Toolbox bulk insert examples:
 
   dbdemos/mssqlserverbulkinsert  - MS SQL Server bulk insert example.
   dbdemos/mysqlbulkinsert  - MySQL bulk insert example.
   dbdemos/oraclebulkinsert - Oracle bulk insert example.
 
   Visual Query Builder functions:
 
   vqb/confds              - Configure data source (UNIX only).
   vqb/getdatasources      - Return valid data sources on system.
   vqb/loginconnect        - Datasource connection.
   vqb/parsebinary         - Write binary object to disk.
   vqb/qbhelp              - Query Builder help string.
   vqb/querybuilder        - Start visual SQL query builder.
   vqb/showdata            - Display data in interactive window.
   vqb/showdatacallbacks   - Visual Query Builder data display callbacks.
   vqb/vqbdemo             - Visual Query Builder demonstrations.

database is both a directory and a function.

 DATABASE Connect to database.
    CONNECT = DATABASE(INSTANCE,USERNAME,PASSWORD,DRIVER,DATABASEURL)
    returns a database connection object. INSTANCE is the name of the
    database, USERNAME is the user name. PASSWORD is the password
    that permits access to the database. DRIVER is a JDBC driver name and
    DATABASEURL is the URL for the database. The latter arguments are
    optional but both must be used in conjunction with each other.
 
    Use LOGINTIMEOUT before DATABASE to set the maximum time for a
    connection attempt.
 
    Example:
 
    JDBC-ODBC connection:
 
    conn=database('oracle','scott','tiger')
 
    where:
 
    'oracle' is the ODBC datasource name for an ORACLE database.
    'scott'  is the user name.
    'tiger'  is the password.
 
 
    JDBC connection:
 
    conn=database('oracle','scott','tiger',
                 'oracle.jdbc.driver.OracleDriver','jdbc:oracle:oci7:')
 
    where:
 
    'oracle' is the database name.
    'scott'  is the user name.
    'tiger'  is the password.
    'oracle.jdbc.driver.OracleDriver' is the JDBC driver to be used
                                      to make the connection.
    'jdbc:oracle:oci7:' is the URL as defined by the Driver vendor
                        to establish a connection with the database.
   
    See also close.

 

 

JDBC的driver 例子 

IBM Informix

JDBC driver: com.informix.jdbc.IfxDriver

Database URL: jdbc:informix-sqli://161.144.202.206:3000:
INFORMIXSERVER=stars

MySQL

JDBC driver: twz1.jdbc.mysql.jdbcMysqlDriver

Database URL: jdbc:z1MySQL://natasha:3306/metrics

JDBC driver: com.mysql.jdbc.Driver

Database URL: jdbc:mysql://devmetrics.mrkps.com/testing

Oracle oci7 drivers

JDBC driver: oracle.jdbc.driver.OracleDriver

Database URL: jdbc:oracle:oci7:@rex

Oracle oci8 drivers

JDBC driver: oracle.jdbc.driver.OracleDriver

Database URL: jdbc:oracle:oci8:@111.222.333.44:1521:

Database URL: jdbc:oracle:oci8:@frug

Oracle thin drivers

JDBC driver: oracle.jdbc.driver.OracleDriver

Database URL: jdbc:oracle:thin:@144.212.123.24:1822:

Oracle 10 connections with JDBC (thin drivers)

JDBC driver: oracle.jdbc.driver.OracleDriver
Database URL: jdbc:oracle:thin: (do not specify the target name and port)

In this example, the target machine on which the database server resides is 144.212.123.24 and the port number is 1822.

PostgreSQL

JDBC driver: org.postgresql.Driver

Database URL: jdbc:postgresql://masd/MOSE

 

PostgreSQL with SSL connection

JDBC driver: org.postgresql.Driver

Database URL: jdbc:postgresql:servername:dbname:ssl=
true&sslfactory=org.postgresql.ssl.NonValidatingFactory& (the trailing & is required)

Microsoft SQL Server

JDBC driver: com.microsoft.jdbc.sqlserver.SQLServerDriver

Database URL: jdbc:microsoft:sqlserver://localhost:port;database=databasename

Note   For MS SQL Server 2005, the Driver and URL syntax has changed to:

JDBC driver: com.microsoft.sqlserver.jdbc.SQLServerDriver

Database URL: jdbc:sqlserver://localhost:port;database=databasename

Sybase SQL Server and Sybase SQL Anywhere

JDBC driver: com.sybase.jdbc.SybDriver

Database URL: jdbc:sybase:Tds:yourhostname:yourportnumber/

 

 

JDBC连接数据库的参数:

 

conn = database('databasename','username',...
'password','driver','databaseurl') connects a MATLAB software session to a database and assigns the returned connection object to conn. The arguments passed to this function are as follows:

databasename: The name of the database to which you connect.

driver: The name of your JDBC driver.

Note   The JDBC driver is sometimes referred to as the class that implements the Sun Java SQL driver for your database.

username and password: The user name and password required to connect to the database. If a user name or password are not required to connect to your database, specify empty strings for these arguments.

Find the correct driver name

databaseurl: A JDBC URL object of the form jdbc:subprotocol:subname. subprotocol is a database type, such as Oracle. subname may contain other information used by driver, such as the location of the database and/or a port number. subname may take the form //hostname:port/databasename.

If database establishes a database connection, it returns information about the connection object, as shown in the following example:
       Instance: 'SampleDB'
       UserName: ''
         Driver: []
            URL: []
    Constructor: [1x1 com.mathworks.toolbox.database.databaseConnect]
        Message: []
         Handle: [1x1 sun.jdbc.odbc.JdbcOdbcConnection]
        TimeOut: 0
     AutoCommit: 'off'
           Type: 'Database Object'

 

2)连接mysql

a)如果是测试开发用，可以下载安装xampp

http://www.apachefriends.org/zh_cn/xampp-windows.html

b)安装JDBC驱动

http://dev.mysql.com/downloads/mirror.php?id=408558

安装mysql驱动程序包，详细步骤如下：
Step 1: 将mysql-connector-java-5.1.21-bin.jar文件拷贝到......MATLAB\R2011b\java\jar\toolbox

Step 2: 到......\MATLAB\R2009a\toolbox\local目录下，找到classpath.txt文件，打开，并添加用来加载mysql的jdbc驱动语句：
$matlabroot/java/jar/toolbox/mysql-connector-java-5.1.21-bin.jar

Step 3:重新打开MATLAB即可

c)编写代码连接

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

>> ping(myconn)

ans =

       DatabaseProductName: 'MySQL'
    DatabaseProductVersion: '5.5.25a'
            JDBCDriverName: 'MySQL-AB JDBC Driver'
         JDBCDriverVersion: [1x60 char]
    MaxDatabaseConnections: 0
           CurrentUserName: 'root@localhost'
               DatabaseURL: 'jdbc:mysql://localhost:3306/test'
    AutoCommitTransactions: 'True'

设置连接超时时限
>> logintimeout('com.mysql.jdbc.Driver',10)

ans =

    10

>>