Was this helpful?
Names that identify database objects can be uppercase, lowercase, or mixed case. In an Ingres Star database, the case of the object’s name as stored in the Ingres Star catalogs depends on:
The case‑translation semantics established when the database was created
How the user specifies the name of the object when it is created
Whether or not the user delimits the name of the object
Mixed-case names are allowed if the distributed database has been created specifying mixed-case delimited identifiers. For examples, see Naming Registration Examples. In addition, an object name must be delimited to preserve case.
Mixed-case names are not allowed if delimited identifiers are not used when registering an object. The case of the name will be uppercase or lowercase, depending on the case of regular identifiers specified at installation time.
In general, Ingres Star performs case translation in order to map the name of a registered object into the name of a local object. However, there are some restrictions on incorporating objects from mixed-case local databases or Enterprise Access products into a Ingres Star database that has been created without mixed-case delimited identifiers. For more information on delimited identifiers and Ingres Star, see Installation Options.
Installation Options
Ingres Star object names can be case-significant, depending on an option specified during installation. The option specifies the rules that the local Ingres DBMS follows in translating identifiers supplied by the user. (An identifier is the syntactic component of an SQL statement that represents the name of a database object.) There are two types of identifiers:
A regular identifier is a name or word.
A delimited identifier is a word or words delimited by double quotes. It allows extended characters and mixed case.
Option Name
Regular Identifiers
Delimited Identifiers
Lowercase option (Ingres setting)
Convert to lowercase
Convert to lowercase
ISO Entry SQL92 standard
Convert to uppercase
Retain case, even if mixed
With the lowercase option (Ingres setting), createdb creates local and distributed databases whose translation rule is to put into lowercase both regular and delimited identifiers. To create an object with a mixed-case name, you must use the ISO Entry SQL92 standard option, and you must use a delimited identifier to specify the object name.
For a further description of identifiers, see the SQL Reference Guide.
Naming Registration Examples
An Ingres Star database can consist of objects registered from remote databases whose case‑translation rules differ from those of the distributed database. These examples illustrate those situations. (These naming examples use the register as link statement. For details on this statement, see Register as Link Statement--Define Database Objects to Ingres Star.
Example: Default DDB and ISO-Compliant LDB
Assume that the distributed database was created with the lowercase option (Ingres setting), which puts both regular and delimited identifiers in lowercase. Assume that the local database is ISO Entry SQL92-compliant. The local database, iso_db, was created with case-translation rules of uppercase regular identifiers and mixed-case delimited identifiers.
In this example iso_db contains a table named PARTS, and the user wishes to register that table into the distributed database. The following statement creates a distributed object named parts. It will correctly map all queries against parts to the table PARTS in the iso_db database:
register parts as link
  with node=node_A, database=iso_db;
Assume that table PARTS has columns named PART_NO and PRICE. At registration time, the Star Server translates the names of these columns before adding them to the Ingres Star catalog iicolumns as columns part_no and price. In addition, the following user’s query against the distributed database will correctly map price and part_no to uppercase names before querying the PARTS table:
select price from parts where part_no=40754;
Example: Mixed-Case Names
Ingres Star employs the mapping rules described in Example: Default DDB and ISO-Compliant LDB if the local database names are mixed case. There is, however, a restriction on registering local database tables with mixed-case names into a distributed database that does not support mixed-case names. Assume that you wish to register the Low Budgets table from the iso_db database into your default distributed database. Mixed-case names can be specified only using a delimited identifier. But the following statement produces an error:
register low_budgets as link from "Low Budgets"
  with node=node_A, database=iso_db;
Because the distributed database translates all identifiers to lowercase, the Star Server attempts to look up the table low budgets in the catalogs of the iso_db database. It does not find that table because the mixed-case property of the table’s name has already been lost. Therefore, Ingres Star rejects any register statement that delimits the name of the local database object or owner if the distributed database does not support mixed-case delimited identifiers but the local database does.
As a workaround to this restriction, the from clause on the register statement allows you to specify the local owner name and the local table name as a string constant. The Star Server does not apply case-translation rules to string constants. Thus the following statement would succeed in registering the table Low Budgets from a case-sensitive local database into a case-insensitive distributed database:
register low_budgets as link from “Low Budgets”
  with node=node_A, database=iso_db;
Example: DDB with Mixed-Case Delimited Identifiers
There is no restriction if the distributed database has been created to support mixed-case delimited identifiers, whether or not the local database supports them.
Assume that you have a table named corp_dept in a local database lower_db that supports only lowercase names. Assume that your distributed database supports mixed-case names and that you wish to register corp_dept as Corp_Dept in the distributed database. You issue the statement:
register "Corp_Dept" as link
  with node=node_A, database=lower_db;
Ingres Star maps the distributed database name Corp_Dept into corp_dept when it queries the lower_db database. If corp_dept has columns dno, name and budget, Ingres Star records these in the distributed database’s iicolumns catalog using the case rules of regular identifiers. If the distributed database was created specifying uppercase translation for regular identifiers, the column names of Corp_Dept will appear as DNO, NAME and BUDGET in the distributed database.
If you wish the registration to have mixed-case column names, you can specify them in the register statement:
register "Corp_Dept" ("Dno", "Name", "Budget") as link
  with node=node_A, database=lower_db;
Last modified date: 01/30/2023