ODBC Data Query – Select Data

Once you have chosen an ODBC database using the ODBC Select Data Source dialog, you are presented with a list of tables and columns within the current table found in the database. To get data from the database, select the table from the dropdown list of tables then transfer the required columns to the Selected columns list. Clicking OK will query the database for data matching you selection and place this in a spreadsheet or directly into the server if you are using the Data Load menu item.

This simple selection of columns from a table creates a SQL SELECT statement. The SQL statement can be expanded if you know how to write SQL statements and chose the Edit SQL option below. For further help on SQL statements see SQL Statements.

Table

This dropdown list contains all tables in the ODBC database, sorted by type and then name. The type information is displayed before the column name. The main table types are TABLE – standard/basic SQL table containing entered data, VIEW – virtual table constructed by an internal SQL query within the database (this may be formed from data from multiple tables within the database), SYNONYM or ALIAS – an alternative name for a table in the database and SYSTEM TABLE – non user defined database information.

When a new table is selected, its columns will be displayed in the available column list. The names of some tables in SQL statements may require an extra section in two tier databases such as Oracle which can group tables together into SCHEMA.

Available columns

A list of the columns in the current table. The column names are prefixed with a single letter giving their type: C – text, N – Numeric and D – Date. Date values are read in as text as ODBC databases do not have a standard method of storing date values. These text dates can be converted to Genstat 8 byte date values using the Convert menu.
Single columns can be transferred to the selected column list by double clicking them. Otherwise the selected columns can be transferred by clicking the button. You can transfer multiple selections from Available columns by holding the Ctrl key on your keyboard while selecting items, then click to move them all across in one action.

Selected columns

An editable list of columns selected for retrieval from the currently selected table.

Select all

Add all available columns into the selected column list.

Clear

Clear all columns from the selected column list.

Edit resulting SQL statement

Selecting this option will result in the ODBC: View or Edit SQL Statement window popping up before the database query is run. This allows you to edit and add to the SQL query generated by selecting a set of columns from a single table. For example if you wanted data from the resulting INNER JOIN of two tables, you could add in the SQL code to select columns from a second table joined to the first one you had selected. You can also specify other SQL options such as SORTED BY and WHERE etc.

Save retrieval in file for re-use

If you select this option, you will be prompted for the name of a Genstat Stored ODBC retrieval (.GDB) file after you query has run correctly. The current ODBC database specification and SQL statement will be stored in this file, and may be rerun at a later date by just specifying this file in the File | Open dialog. This file is a standard text file and can be edited using any text editor.

Next

Opens the ODBC Data Query – Filter Data Using Where Statement dialog. A Where clause in an SQL statement restricts (or filters) the rows which are returned by SQL query. The Clause is in the form of a logical statement which must be TRUE for the row to be included in the result set.

Finish

Close the dialog and run the SQL query.

Cancel

Close the ODBC dialog without querying the database.

See also

SQL Statements
ODBC Databases
Create New Database Table from Spreadsheet
Insert Spreadsheet into Database
Merge Spreadsheet into Database
Run ODBC Export Link (GLK) file
Options – Date Format
Spreadsheet Export Menu
Spreadsheet Add Data Menu
Spreadsheet New Menu
Using ODBC with 64bit Windows
DBIMPORT procedure
DBEXPORT procedure

Updated on July 11, 2019

Was this article helpful?