Builddbstr: Difference between revisions

From Eigenvector Research Documentation Wiki
Jump to navigation Jump to search
imported>Jeremy
imported>Jeremy
No edit summary
 
(15 intermediate revisions by 3 users not shown)
Line 1: Line 1:
===Purpose===
===Purpose===
Builds a database connection string.
Builds a database connection string.
===Synopsis===
===Synopsis===
:str = builddbstr(dbstruct,''options'')
 
:connectionstring = builddbstr(dbstruct,''options'')
:struct = builddbstr('access');      %Retrieve default structure for named database type
 
===Description===
===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 Matlab Database Toolbox.  
 
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.
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:
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.
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.  
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.
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.
====Input====
 
A connection will require one of more of the following fields. Empty values are not used.
====Inputs====
*   provider : only used by ADODB object so this will always be 'MSDASQL'.
''dbstruct'' = A structure containing one of more of the following fields:
*     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).
*'''provider''' : only used by ADODB object so this will always be 'MSDASQL'.
*       user : user to connect in as, if empty not used.
*'''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
*         pw : password for user, if empty not used.
*'''dbname''' : database name (or service name).
*   location : File location on local system (e.g. c:\temp\mydb.mdb). Used for connecting to local Access databases.
*'''user''' : user to connect in as, if empty not used.
*     server : IP address for database (default location is 'localhost').
*'''pw''' : password for user, if empty not used.
*         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.
*'''location''' : File location on local system (e.g. c:\temp\mydb.mdb). Used for connecting to local Access databases.
*   arg.name : sub structure of additional arguments. This value must be a sting of exactly what is required in the database connection string.
*'''server''' : IP address for database (default location is 'localhost').
*   arg.value : sub structure of additional arguments. This value must be a sting of exactly what is required in the database connection string. Example below:
*'''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.
                cnn.arg(1).name  = 'PORT';
:*'''arg.name''' : sub structure of additional arguments. This value must be a sting of exactly what is required in the database connection string.
                cnn.arg(1).value = '3306';
:*'''arg.value''' : sub structure of additional arguments. This value must be a sting of exactly what is required in the database connection string.
                cnn.arg(2).name  = 'SOCKET';
::'''Example'''
                cnn.arg(2).value = '123';
<pre>cnn.arg(1).name  = 'PORT';
====Predefined Database Connections====
cnn.arg(1).value = '3306';
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.
cnn.arg(2).name  = 'SOCKET';
2) '''Microsoft SQL Server''' : 'mssql' Not tested.
cnn.arg(2).value = '123';</pre>
3) '''MySQL''' : 'mysql' Uses (MySQL ODBC 3.51 Driver) from mysql website. Must be downloaded and installed before making connection.
'''Predefined Database Connections'''
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.
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.
6) '''All''' : 'all' Show all available fields.
 
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 <tt>connectionstring</tt> will be a structure.


===Options===
===Options===
* isodbc: [{ 1 } | 0 ] Use ODBC connection string formatting. This should be set to 0 if using JDBC.
 
* '''isodbc''': [{ 1 } | 0 ] Use ODBC connection string formatting. This should be set to 0 if using JDBC.
 
===Examples===
===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.
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:'''
'''Microsoft Access on local machine:'''
<pre>
>> cnstr = builddbstr('access')
>> cnstr = builddbstr('access')
cnstr =  
cnstr =  
Line 47: Line 76:
           pw: ''
           pw: ''
>> cnstr.location = 'c:\temp\mydb.mdb';
>> cnstr.location = 'c:\temp\mydb.mdb';
</pre>
'''MySQL on remote machine:'''
'''MySQL on remote machine:'''
<pre>
>> cnstr = builddbstr('mysql')
>> cnstr = builddbstr('mysql')
cnstr =  
cnstr =  
Line 60: Line 91:
>> cnstr.user = 'myname';
>> cnstr.user = 'myname';
>> cnstr.pw = 'mypw';
>> cnstr.pw = 'mypw';
</pre>
'''MySQL on remote machine (JDBC on Windows):'''
'''MySQL on remote machine (JDBC on Windows):'''
<pre>
>> cnstr = builddbstr('jmysql')
>> cnstr = builddbstr('jmysql')
cnstr =  
cnstr =  
Line 72: Line 105:
>> cnstr.user = 'myname';
>> cnstr.user = 'myname';
>> cnstr.pw = 'mypw';
>> cnstr.pw = 'mypw';
</pre>
'''DSN (Data Source Name):'''
'''DSN (Data Source Name):'''
<pre>
>> cnstr = builddbstr('dsn')
>> cnstr = builddbstr('dsn')
cnstr =  
cnstr =  
Line 80: Line 115:
           pw: ''
           pw: ''
>> cnstr.dsn = 'dsnname';
>> cnstr.dsn = 'dsnname';
</pre>
===See Also===
===See Also===
[[querydb]], [[parsemixed]]
 
[[evridb]], [[querydb]], [[parsemixed]]

Latest revision as of 09:19, 2 April 2014

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