JDBC defines the following terms for parts of a table identifier:
Therefore, to uniquely identify a table, the database (and therefore Sqlch) may require just the name, the schema and the name, the catalog and the name, or all three. Whether or not you only require the name is also dependent on how you have configured your database or how you connect to it. The usage of schema and catalog can depend on whether or not they are specified as part of the connection parameters in the JDBC URL.
In situations where the user is given the option to specify catalog, schema and name, users may still choose to only use the table name even when the others have meaning to the database being used. Sqlch always looks up each table in the database to determine if the information the user has supplied is enough to uniquely identify a table. If it isn't enough it will fail with an error. If the information is sufficiently unique, Sqlch retrieves the catalog and schema (as appropriate for the database) and always uses them internally.
In situations where catalog and schema are important, developers can choose specify one but ignore the other. Not specifying a catalog or a schema is the same as specifying "any". Users can also use the wildcard % to indicate "any". This can be useful if you need to auto-generate Sqlch files using Ant token replacement and you do not know when you write the template whether catalog or schema are important. Developers can then specify % or the actual value when the value for the token is chosen.
Some parts of Sqlch allow you to specify a default schema and default catalog. These are usually specified on the command line or inside an Ant script rather than inside of the Sqlch files themselves. The default values are used when the schema or catalog is not specified directly. E.g. in a dataset file, if only the table name is specified for a table, but a user has specified a default schema, Sqlch will insert the default schema in addition to the table name to try to identify the table. This functionality can be useful as it allows a fixed dataset file to be used by all users, and the user's schema can be specified in a properties file, or similar. If you are dealing with multiple separate catalogs and/or schemas you will usually have to specify their names.
Sqlch defines an additional canonical value that is enough to uniquely identify a table for a given database. This value is a single string called a "SQL ID". This ID is the same as the ID that could be used within a SQL statement to identify a table uniquely for that database.
In order to determine how Sqlch will refer to a table internally with their connection settings, developers can use the DescribeTable tool to describe a particular table. DescribeTable returns, among other things, the SQL ID for the table. See the page on support tools for more information on Sqlch tools.
Sqlch does not assume that a table name is enough to uniquely identify a table. This allows some flexibility for developers - DBAs may choose to have a separate database build per user (each developer logs into the same database, but with a different user id), or each developer may get their own dedicated database instance.
Sqlch uses the database metadata information supplied by the Driver to determine what is required to generate a SQL ID for a table.
Different databases have different rules about how the components are turned into an identifier that can be used in a standard SQL statement. Some databases require quotes (") to indicate the case of a table is important, some do not. Some databases (e.g. MySQL) don't use the quote character (") to delimit a name, they use the "back-tick" (`) character instead. Sqlch determines the quote character by querying the JDBC database metadata and always uses it when generating SQL, regardless of whether it is optional.
Sqlch SourceForge Distribution: 0.1Alpha8 20041026 2208