Builddbstr

Purpose

Builds a database connection string.

Synopsis

connectionstring = builddbstr(dbstruct,options)

struct = builddbstr('access'); %Retrieve default structure for named database type

Description

This function is unsupported and is meant as a "simple" database connection tool. For more sophisticated connection tools and full support please see the evridb object.

It is generally recommended that one use a Microsoft DSN (Data Source Name) to establish connection on Window platforms. These types of connections tend to be easier to maintain and more secure. For more information on DSN, see the Windows help entry for "ODBC". Unix platforms should use JDBC, JDBC with MySQL is a "predefined" method and is known to work with the MySQL JDBC 3.51 Driver.

Input (dbstruct) can be:

1) A structure containing necessary information to construct one of the predefined connections listed below. The output will be a properly formatted connection string.

2) A string indicating a predefined structure to return. The output will be a structure containing predefined values along with empty fields that may need to be filled in. Fill in the EMPTY fields as needed and the connection should work. The 'user' and 'pw' fields are always present but may not be needed. This structure can be passed directly to querydb.m.

3) A structure with additional arg.value substructure fields necessary for a connection to a non-predefined database connection. The output will be a properly formatted connection string.

Inputs

dbstruct = A structure containing one of more of the following fields:

• provider : only used by ADODB object so this will always be 'MSDASQL'.
• driver : driver to be used for connection (these must be currently installed on the machine, use the ODBC Manager from Administrative Tools to view currently available drivers on your machine. JDBC must have driver installed on Matlab class path
• dbname : database name (or service name).
• user : user to connect in as, if empty not used.
• pw : password for user, if empty not used.
• location : File location on local system (e.g. c:\temp\mydb.mdb). Used for connecting to local Access databases.
• server : IP address for database (default location is 'localhost').
• dsn : Data Source Name (set up on local computer using ODBC Manager from Administrative Tools). If the database connection remains static, this can be a simple way to manage the connection. See the "ODBC" topic in Windows help for more information on DSN.

• arg.name : sub structure of additional arguments. This value must be a sting of exactly what is required in the database connection string.
• arg.value : sub structure of additional arguments. This value must be a sting of exactly what is required in the database connection string.

Example

cnn.arg(1).name  = 'PORT';
cnn.arg(1).value = '3306';
cnn.arg(2).name  = 'SOCKET';
cnn.arg(2).value = '123';

Predefined Database Connections

1) Microsoft Access : 'access' Uses standard connection provided with windows (Microsoft Access Driver (\*.mdb)) and doesn't require UserID or PW if database doesn't have them defined.

2) Microsoft SQL Server : 'mssql' Not tested.

3) MySQL : 'mysql' Uses (MySQL ODBC 3.51 Driver) from mysql website. Must be downloaded and installed before making connection.

4) Data Source Name : 'dsn' Uses a Data Source Name defined in Windows ODBC Data Source Administrator dialog box. Although 'user' and 'pw' are returned in the structure they are generally not needed for DSN connections, this information is usually resides in the DSN itself.

5) MySQL(JDBC) : 'jmysql' Uses (MySQL JDBC 3.51 Driver) form mysql website. Must be downloaded and installed before making connection. The driver jar file must be added to the Matlab java classpath.

6) All : 'all' Show all available fields.

Outputs

• connectionstring = Database connection string. If input was a string containing predefined connection then connectionstring will be a structure.

Options

• isodbc: [{ 1 } | 0 ] Use ODBC connection string formatting. This should be set to 0 if using JDBC.

Examples

Examples of building connection strings on a Windows machine for use with the querydb function. For Oracle and other database connections, try using DSN.

Microsoft Access on local machine:

>> cnstr = builddbstr('access')
cnstr = 
    provider: 'MSDASQL'
      driver: '{Microsoft Access Driver (\*.mdb)}'
    location: ''
        user: ''
          pw: ''
>> cnstr.location = 'c:\temp\mydb.mdb';

MySQL on remote machine:

>> cnstr = builddbstr('mysql')
cnstr = 
    provider: 'MSDASQL'
      driver: 'MySQL ODBC 3.51 Driver'
      server: ''
      dbname: ''
        user: ''
          pw: ''
>> cnstr.server = 'mydatabase.mywebsite.com';
>> cnstr.dbname = 'mydatabase';
>> cnstr.user = 'myname';
>> cnstr.pw = 'mypw';

MySQL on remote machine (JDBC on Windows):

>> cnstr = builddbstr('jmysql')
cnstr = 
    driver: 'com.mysql.jdbc.Driver'
    server: ''
    dbname: ''
      user: ''
        pw: ''
>> cnstr.server = 'mydatabase.mywebsite.com';
>> cnstr.dbname = 'mydatabase';
>> cnstr.user = 'myname';
>> cnstr.pw = 'mypw';

DSN (Data Source Name):

>> cnstr = builddbstr('dsn')
cnstr = 
    provider: 'MSDASQL'
         dsn: ''
        user: ''
          pw: ''
>> cnstr.dsn = 'dsnname';

See Also

evridb, querydb, parsemixed