Creating Schemas

To create a new Statement Builder schema | To delete a schema | To copy tables from an existing schema

A schema is generally a set of tables, views, or stored procedures in a database. In the SQL Statement Builder, a schema is a subset of those tables, views, and procedures available to a user through a JDBC connection. Use the Create Schema wizard in the SQL Statement Builder to define your schemas.

You have to know the name of the data source you are connecting to before you define a schema for that connection. Remember that the connection is created in the Administration utility. After the connection is created, you can create a schema for that connection.

By default, the Statement Builder shows the tables, views, and procedures in the database schema that matches the data source name you specify. For example, if you specify Sample System as your SQL data source, the Statement Builder shows you the tables, views, and procedures defined in the relational database for the Sample System schema.

If you need to access a database schema other than the default, you can view all the schemas on the relational database by:

  1. Clicking the Advanced button.

  2. Selecting the Show All Schemas check box.

When you select this check box and click the Next button in the wizard, the Statement Builder shows all the schemas available on the relational database. After you select a database schema, the wizard walks you through selection screens, allowing you to select the tables, views, and procedures you want to include in the Statement Builder schema you want to create.

IMPORTANT: Not all relational databases provide access to schemas. If your database does not, the Statement Builder shows a list of tables to select from, even if you select the Show All Schemas check box.

After you have created a Statement Builder schema, it appears in the tree on the Schema tab. From there, you can drag tables, views, or stored procedures from a schema into the graphic design area to create a statement. You can create multiple statements for each schema. However, a statement can reference only one schema.

Schemas are folder-based. As a result, you cannot assign security rights to individual schemas. However, you can add tables, views, and procedures to a schema or delete schemas as needed.

NOTE: If the structure of a table on the relational database changes, for example, a column is added, the change is not automatically reflected in the Statement Builder schema. In order for the Statement Builder to reflect this change, you must delete the current schema and recreate it.