Querydb: Difference between revisions

From Eigenvector Research Documentation Wiki
Jump to navigation Jump to search
imported>Scott
imported>Jeremy
No edit summary
 
(14 intermediate revisions by one other user not shown)
Line 5: Line 5:
===Synopsis===
===Synopsis===


:out = querydb(connstr,sqlstr,''options'');
: dso = querydb(connstr, sqlstr);
: cellarray = querydb(connstr, sqlstr, options);


===Description===
===Description===
'''NOTE''': As of version 6 of PLS_Toolbox the [[evridb]] object should be used in place of this function. See this [[evridb_examples | page ]] for an example of connecting to an Access database.


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 Matlab Database Toolbox.


JDBC connections require that the jdbc driver ".jar" file be added to the Matlab java classpath.  See the documentation for the Matlab commands 'javaaddpath' and 'javaclasspath' for more information. For example, using the MySQL Connector/J 3.1 driver you'll need to add the "mysql-connector-java-3.1.12-bin.jar" file to your java class path.
JDBC connections require that the jdbc driver ".jar" file be added to the Matlab java classpath.  See the documentation for the Matlab commands 'javaaddpath' and 'javaclasspath' for more information. For example, using the MySQL Connector/J 3.1 driver you'll need to add the "mysql-connector-java-3.1.12-bin.jar" file to your java class path. Check extensions/javatools folder for available connection drivers.
 
'''NOTE for using JDBC with Older versions of Matlab''':
* The .jar file may need to be added to the Matlab static class path. To do this, add the file path to classpath.txt then restart Matlab.
* The option 'conntype' must be set to jdbc.


====Inputs====
====Inputs====
Line 19: Line 25:
* '''sqlstr''' : A SQL statement to be executed on the connection. The SQL statement must be of proper syntax or it will fail. Default behavior is geared toward SELECT statements that return values. If attempting to execute a SQL command that doesn't return a value (e.g., CREATE TABLE) set the 'rtype' option to 'none'.  
* '''sqlstr''' : A SQL statement to be executed on the connection. The SQL statement must be of proper syntax or it will fail. Default behavior is geared toward SELECT statements that return values. If attempting to execute a SQL command that doesn't return a value (e.g., CREATE TABLE) set the 'rtype' option to 'none'.  


'''NOTE''': Use a seperate program like Microsoft Access to formulate the SQL statement. Access queries can require some small changes in syntax.
'''NOTE''': Use a separate program like Microsoft Access to formulate the SQL statement visually. Access queries can require some small changes in syntax.


====Outputs====
====Outputs====
Line 27: Line 33:
===Options===
===Options===


* '''rtype''' : [{'dso'} | 'cell' | 'none'] Return type, default is return SQL recordset as a DataSet Object using parsemixed.m to parse data in. If 'cell' then a cell array is returned with all values. If 'insert' then function will execute an "INSERT" type query and attempt to return the Auto Number ID (as a scalar) of the row created. If 'none' function will execute query and return an empty.
* '''rtype''' : [{'dso'} | 'cell' | 'insert' | 'none'] Return type, default is return SQL recordset as a DataSet Object using parsemixed.m to parse data in. If 'cell' then a cell array is returned with all values. If 'insert' then function will execute an "INSERT" type query and attempt to return the Auto Number ID (as a scalar) of the row created. If 'none' function will execute query and return an empty.


* '''varlabels''' : [ {'none'} | 'fieldnames' ] Defines what should be used as variable labels on output DataSet Object (only used when rtype is 'dso'). 'fieldnames' uses the SQL field names for variable labels.  
* '''varlabels''' : [ {'none'} | 'fieldnames' ] Defines what should be used as variable labels on output DataSet Object (only used when rtype is 'dso'). 'fieldnames' uses the SQL field names for variable labels.  
Line 34: Line 40:


* '''getaccesstables''' :  [ 'on' | {'off'} ] Short circuit to retrieve list of tables in Access database, similar to SHOW TABLES query in MySQL. Input 'sqlstr' will not be called when option is 'on'.
* '''getaccesstables''' :  [ 'on' | {'off'} ] Short circuit to retrieve list of tables in Access database, similar to SHOW TABLES query in MySQL. Input 'sqlstr' will not be called when option is 'on'.
* '''getaccessfieldnames''' : [''] Short circuit to retrieve list of tables fieldnames. Input should be valid table name (string).


===Examples===
===Examples===


Assuming there is a connection string named 'mydbconn' already created using the builddbstr command. To return a DSO:
Assuming there is a connection string named 'mydbconn' already created using the [[builddbstr]] command. To return a DSO:


>> sqlstr = 'SELECT \* FROM myTable';
<pre>>> sqlstr = 'SELECT * FROM myTable';


>> mydso = querydb(mydbconn,sqlstr);
>> mydso = querydb(mydbconn,sqlstr);</pre>


To return a cell array:
To return a cell array:


>> opts = querydb('options');
<pre>>> opts = querydb('options');


>> opts.rtype = 'cell';
>> opts.rtype = 'cell';


>> mycell = querydb(mydbconn,sqlstr,opts);
>> mycell = querydb(mydbconn,sqlstr,opts);</pre>


===See Also===
===See Also===


[[builddbstr]], [[parsemixed]]
[[builddbstr]], [[evridb]], [[parsemixed]]

Latest revision as of 09:18, 2 April 2014

Purpose

Executes a query on a database defined by connection string.

Synopsis

dso = querydb(connstr, sqlstr);
cellarray = querydb(connstr, sqlstr, options);

Description

NOTE: As of version 6 of PLS_Toolbox the evridb object should be used in place of this function. See this page for an example of connecting to an Access database.

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.

JDBC connections require that the jdbc driver ".jar" file be added to the Matlab java classpath. See the documentation for the Matlab commands 'javaaddpath' and 'javaclasspath' for more information. For example, using the MySQL Connector/J 3.1 driver you'll need to add the "mysql-connector-java-3.1.12-bin.jar" file to your java class path. Check extensions/javatools folder for available connection drivers.

NOTE for using JDBC with Older versions of Matlab:

  • The .jar file may need to be added to the Matlab static class path. To do this, add the file path to classpath.txt then restart Matlab.
  • The option 'conntype' must be set to jdbc.

Inputs

  • connstr : A connection string or a structure created using builddbstr. See BUILDDBSTR for more information.
  • sqlstr : A SQL statement to be executed on the connection. The SQL statement must be of proper syntax or it will fail. Default behavior is geared toward SELECT statements that return values. If attempting to execute a SQL command that doesn't return a value (e.g., CREATE TABLE) set the 'rtype' option to 'none'.

NOTE: Use a separate program like Microsoft Access to formulate the SQL statement visually. Access queries can require some small changes in syntax.

Outputs

  • out : DataSet Object, Cell Array, or Scalar depending on 'rtype'.

Options

  • rtype : [{'dso'} | 'cell' | 'insert' | 'none'] Return type, default is return SQL recordset as a DataSet Object using parsemixed.m to parse data in. If 'cell' then a cell array is returned with all values. If 'insert' then function will execute an "INSERT" type query and attempt to return the Auto Number ID (as a scalar) of the row created. If 'none' function will execute query and return an empty.
  • varlabels : [ {'none'} | 'fieldnames' ] Defines what should be used as variable labels on output DataSet Object (only used when rtype is 'dso'). 'fieldnames' uses the SQL field names for variable labels.
  • conntype : [ 'jdbc' | {'odbc'} ] Determines type of connection. ODBC uses a Windows ADO with Matlab (descibed above). JDBC connections only work when jdbc class files are on static java path.
  • getaccesstables : [ 'on' | {'off'} ] Short circuit to retrieve list of tables in Access database, similar to SHOW TABLES query in MySQL. Input 'sqlstr' will not be called when option is 'on'.
  • getaccessfieldnames : [] Short circuit to retrieve list of tables fieldnames. Input should be valid table name (string).

Examples

Assuming there is a connection string named 'mydbconn' already created using the builddbstr command. To return a DSO:

>> sqlstr = 'SELECT * FROM myTable';

>> mydso = querydb(mydbconn,sqlstr);

To return a cell array:

>> opts = querydb('options');

>> opts.rtype = 'cell';

>> mycell = querydb(mydbconn,sqlstr,opts);

See Also

builddbstr, evridb, parsemixed