(JDBC Connectors)
Synopsis
This operator reads an ExampleSet from a Microsoft Access database.Description
The Read Access operator is used for reading an ExampleSet from the specified Microsoft Access database (.mdb or .accdb extension). You need to have at least basic understanding of databases, database connections and queries in order to use this operator properly. Go through the parameters and Example Process to understand the flow of this operator.
Input
file (IOObject)The input file for this parameter (replaces the database file parameter, if used).
Output
- output (Data table)
This port delivers the result of the query on database in tabular form along with the meta data. This output is similar to the output of the Retrieve operator.
Parameters
- usernameThis parameter is used to specify the username of the database (if any).
- passwordThis parameter is used to specify the password of the database (if any).
- define queryQuery is a statement that is used to select required data from the database. This parameter specifies whether the database query should be defined directly, through a file or implicitly by a given table name. The SQL query can be auto generated giving a table name, passed via a parameter or, in case of long SQL statements, in a separate file. The desired behavior can be chosen using the define query parameter. Please note that column names are often case sensitive and might need quoting.
- queryThis parameter is only available when the define query parameter is set to 'query'. This parameter is used to define the SQL query to select desired data from the specified database.
- query fileThis parameter is only available when the define query parameter is set to 'query file'. This parameter is used to select a file that contains the SQL query to select desired data from the specified database. Long queries are usually stored in files. Storing queries in files can also enhance reusability.
- table nameThis parameter is only available when the define query parameter is set to 'table name'. This parameter is used to select the required table from the specified database.
- database fileThis parameter specifies the path of the Access database i.e. the mdb or accdb file.
Tutorial Processes
Writing and then reading data from an Access database
The 'Golf' data set is loaded using the Retrieve operator. The Write Access operator is used for writing this ExampleSet into the golf table of the 'golf_db.mdb' database. The database file parameter is provided with the path of the database file 'golf_db.mdb' and the name of the desired table is specified in the table name parameter ( i.e. it is set to 'golf'). A breakpoint is inserted here. No results are visible at this stage but you can see that at this point of the execution the database has been created and the golf table has been filled with the examples of the 'Golf' data set.
Now the Read Access operator is used for reading the golf table from the 'golf_db.mdb' database. The database file parameter is provided with the path of the database file 'golf_db.mdb'. The define query parameter is set to 'table name'. The table name parameter is set to 'golf' which is the name of the required table. Continue the process, you will see the entire golf table in the Results Workspace. The define query parameter is set to 'table name' if you want to read an entire table from the database. You can also read a selected portion of the database by using queries. Set the define query parameter to 'query' and specify a query in the query parameter.
