Single-Row Query - Settings
Use the Single-Row Query - Settings window to specify the database and SQL statement you want the Single-Row Query part to use.
If the SQL statement you want to use does not exist, you will need to create it or import it. You can also use this window to edit, export, or delete an SQL statement.
Note:
An SQL statement can only reference the tables, views, and columns contained in the database you specify. The SQL statement will be validated against the database when you select OK or when you select Edit from the Query pull-down menu.
Fields
Connection alias
From the Connection alias drop-down list, specify the connection alias. You can use the Connection Specs push button to specify database connection information.
Access set
From the Access set combo box, specify the access set that holds the query specification that you want to use.
Access sets can contain query, stored procedure, and connection specifications that developers can share.
You must define an access set. If you have not already defined an access set, VA Smalltalk prompts you to create one.
Query name
From the Query name drop-down list, specify the query specification name you want to use. The drop-down list shows the query specifications that were created or imported from the access set you specified in the Access set field.
If the query specification you want to use does not exist, you must either create it or import it.
If you are creating or importing a query specification, you do not need to specify a query specification name.
The query specification must specify an SQL statement that references the tables, views, and columns contained in the database you specified in the Database name field. The SQL statement will be validated against the database when you select Apply or when you select Edit from the Query pull-down menu.
If you are exporting or deleting a query specification, you can specify any query specification name.
Description
The Description field shows the description of the SQL statement you have specified in the Query name field.
The description cannot be edited in this field.
Show SQL
Select Show SQL to display the SQL statement you have specified in the Query name field.
If the SQL statement is already displayed, Show SQL is not available.
Menu-bar choices
Use the Query menu to work with an SQL statement:
Create
Select Create to create an SQL statement interactively.
Menu summary
UPDATE
Select UPDATE to create an SQL UPDATE statement interactively.
The UPDATE statement can reference any of the tables, views, and columns contained in the database you have specified in the Alias field.
If a database is not specified in the Alias field or an access set is not specified in the Access set field, UPDATE is not available.
INSERT
Select INSERT to create an SQL INSERT statement interactively.
The INSERT statement can reference any of the tables, views, and columns contained in the database you have specified in the Alias field.
If a database is not specified in the Alias field or an access set is not specified in the Access set field, INSERT is not available.
DELETE
Select DELETE to create an SQL DELETE statement interactively.
The DELETE statement can reference any of the tables, views, and columns contained in the database you have specified in the Alias field.
If a database is not specified in the Alias field or an access set is not specified in the Access set field, DELETE is not available.
Edit
Select Edit to interactively edit the SQL statement you have specified in the Query name field.
The SQL statement can only reference the tables, views, and columns contained in the database you have specified in the Alias field. If you select Edit, the SQL statement is validated against the database.
Note:
Only SQL statements that have been created interactively and have not been manually edited can be edited interactively.
If a database is not specified in the Alias field, an access set is not specified in the Access set field, or an SQL statement is not specified in the Query name field, Edit is not available. Also, if the SQL statement you have specified in the Query name field was imported from a file, manually created, or manually edited, Edit is not available.
Import
Select Import to import an SQL statement from a file.
The file you import must only contain the text of the SQL statement you want to import. The SQL statement can be on one or more lines.
If you import an SQL statement, a new SQL statement is created with the imported text and its name and description are specified for you.
Note:
SQL statements that are imported can only be edited manually.
If an access set is not specified in the Access set field, Import is not available.
Export
Select Export to export an SQL statement to a file. The SQL statement exported is the one you have specified in the Query name field.
The file will only contain the text of the SQL statement you want to export.
If an access set is not specified in the Access set field or an SQL statement is not specified in the Query name field, Export is not available.
Manual create
Select Manual create to manually create an SQL statement.
The SQL statement can be any one that is valid for the database manager that will process the SQL statement.
Note:
SQL statements that are manually created can only be edited manually.
If an access set is not specified in the Access set field, Manual create is not available.
Manual edit
Select Manual edit to manually edit the SQL statement you have specified in the Query name field.
You can manually edit SQL statements that have been created interactively, created manually, or imported from a file.
Note:
SQL statements that are manually edited can only be edited manually later, even if it was created interactively.
If an access set is not specified in the Access set field or an SQL statement is not specified in the Query name field, Manual edit is not available.
Host variables
Select Host variables to display the Host variables for window. You can use this window to display all host variables defined in the selected query.
To edit the data type for a host variable, select one from the Host variables for window and then select the Edit push button.
If the current query has no host variables, this menu item is not available.
Delete
Select Delete to delete the SQL statement you have specified in the Query name field.
If an access set is not specified in the Access set field or an SQL statement is not specified in the Query name field, Delete is not available.
Select Options to specify SQL statement options.
High-level qualifiers
Indicate whether to include high-level qualifiers in new SQL statements
Select High-level qualifiers to specify whether or not you want to include high-level qualifiers in new SQL statements you create. High-level qualifiers will be included when High-level qualifiers is checked.
Note:
Selecting High-level qualifiers does not change existing SQL statements.
Including high-level qualifiers causes table names to be prefixed with a high-level qualifier appropriate for the database manager you specified. For example, when using IBM DB2, the table names are prefixed with the current database logon userid.
High-level qualifiers are only necessary in certain SQL statements. For example, say two different userids created tables in the same database with the same name, using IBM DB2. The only way to uniquely identify one of the tables, when not logged on as the userid that created it, is to prefix the table name with the userid that created it.
If high-level qualifiers are not necessary for a SQL statement, you may want to avoid using them so the table names are not unnecessarily long.
You can restrict the number of table names that appear in the SQL editors by setting the high level qualifier preference on the Database page of the preferences notebook.
Push buttons
o Select OK to accept and validate the values, and close the window.
o Select Show SQL to display the SQL statement you have specified.