SQL-J Language Reference
Page 3 of 121

SQL-J Identifiers

An identifier is the representation within the language of items created by the user, as opposed to language keywords or commands. SQL-J has two kinds of identifiers:

  • Some identifiers stand for dictionary objects, which are the objects you create--such as tables, views, indexes, columns, constraints, and stored prepared statements--that are stored in a database. They are called dictionary objects because Cloudscape stores information about them in the system tables, sometimes known as a data dictionary. SQL-92 also defines ways to alias these objects within certain statements.
  • Other identifiers stand for user constructs recognized by a JVM, such as Java classes, methods, packages, and fields.

Each kind of identifier must conform to a different set of rules. Identifiers representing dictionary objects must conform to SQL-92 identifier rules and are thus called SQL92Identifiers. Identifiers representing Java constructs must conform to rules that enable them to be recognized by both Cloudscape and the JVM and are thus called JavaIdentifiers.

Rules for Both SQL92Identifiers and JavaIdentifiers

Non-delimited identifiers are identifiers not surrounded by double quotation marks. Delimited identifiers are identifiers surrounded by double quotation marks.

A non-delimited identifier must begin with a letter and contain only letters, underscore characters (_), and digits. The permitted letters and digits include all Unicode letters and digits, but Cloudscape does not attempt to ensure that the characters in identifiers are valid in the database's locale.

A delimited identifier can contain any characters within the quotation marks. The enclosing quotation marks are not part of the identifier; they serve only to mark its beginning and end. Spaces at the end of a delimited identifier are insignificant (truncated). Cloudscape translates two consecutive double quotation marks within a delimited identifier as one double quotation mark--that is, the "translated" double quotation mark becomes a character in the delimited identifier.

Periods within delimited identifiers are not separators but are part of the identifier (the name of the dictionary object or Java construct being represented).

So, in the following example:

"A.B"

is a dictionary object or Java construct, while

"A"."B"

is a dictionary object qualified by another dictionary object (such as a column named "B" within the table "A") or a Java construct qualified by another Java construct (Java class "B" within the Java package "A").

SQL92Identifier

An SQL92Identifier is a dictionary object identifier that conforms to the rules of SQL-92. SQL-92 states that identifiers for dictionary objects are limited to 128 characters and are case-insensitive (unless delimited by double quotes), because they are automatically translated into uppercase by the system. You cannot use reserved words as identifiers for dictionary objects unless they are delimited. If you attempt to use a name longer than 128 characters, SQLException X0X11 is raised.

Cloudscape defines keywords beyond those specified by the SQL-92 standard (see Chapter 2, "SQL-J Keywords and Reserved Words").

Example

-- the view name is stored in the
-- system catalogs as ANIDENTIFIER
CREATE VIEW AnIdentifier AS VALUES 1

-- the view name is stored in the system
-- catalogs with case intact
CREATE VIEW "ACaseSensitiveIdentifier" AS VALUES 1

This section describes the rules for using SQL92Identifiers to represent the following dictionary objects:

Qualifying Dictionary Objects

Since some dictionary objects can be contained within other objects, you can qualify those dictionary object names. Each component is separated from the next by a period. An SQL92Identifier is "dot-separated." You qualify a dictionary object name in order to avoid ambiguity.

SchemaName

A SchemaName represents a schema. Schemas contain other dictionary objects, such as tables and indexes. Schemas provide a way to name a subset of tables and other dictionary objects within a database.

You can explicitly create or drop a schema. The default user schema is the APP schema (if no user name is specified at connection time). You are not allowed to create dictionary objects in the SYS schema.

Thus, you can qualify references to tables with the schema name. When a schema name is not specified, the default schema name is implicitly inserted. System tables are placed in the SYS schema. You must qualify all references to system tables with the SYS schema identifier. (For more information about system tables, see Chapter 4, "Cloudscape System Tables".)

A schema is hierarchically the highest level of dictionary object, so you cannot qualify a schema name.

Syntax

  SQL92Identifier

Example

-- APP.CustomizedTours is a TableName qualified by a SchemaName
SELECT COUNT(*) FROM APP.CustomizedTours

-- You must qualify system catalog names with their schema, SYS
SELECT COUNT(*) FROM SYS.SysColumns

TableName

A TableName represents a table. You can qualify a TableName with a SchemaName.

Syntax

[ SchemaName. ] SQL92Identifier

Example

-- APP.FlightBookings is a TableName that includes a SchemaName
SELECT COUNT(*) FROM APP.FlightBookings

ViewName

A ViewName represents a table or a view. You can qualify a ViewName with a SchemaName.

Syntax

[ SchemaName. ] SQL92Identifier

Example

-- This is a View qualified by a SchemaName
SELECT COUNT(*) FROM APP.Segments_SeatBookings

CorrelationName

A CorrelationName is given to a table expression in a FROM clause as a new name or alias for that table. You do not qualify a CorrelationName with a SchemaName.

Syntax

SQL92Identifier

Example

-- C is a CorrelationName
SELECT C.region
FROM APP.Countries C

ColumnName

In many places in the SQL-J syntax, you can represent the name of a column by qualifying it with a TableName or CorrelationName.

In some situations, you cannot qualify a ColumnName with a TableName or a CorrelationName, but must use a SimpleColumnName instead. Those situations are:

Syntax

[ { TableName | CorrelationName } . ] SQL92Identifier

Example

-- C.Country is a ColumnName qualified with a
-- CorrelationName.
SELECT C.Country
FROM APP.Countries C

SimpleColumnName

A SimpleColumnName is used to represent a column when it cannot be qualified by a TableName or CorrelationName. This is the case when the qualification is fixed, as it is in a column definition within a CREATE TABLE statement, and in ORDER BY clauses.

Syntax

SQL92Identifier

Example

-- country is a SimpleColumnName

CREATE TABLE Countries
    (country VARCHAR(26) PRIMARY KEY,
    country_ISO_code CHAR(2),
    region VARCHAR(26))

IndexName

An IndexName represents an index. Indexes live in schemas, so you can qualify their names with SchemaNames. Indexes on system tables are in the SYS schema.

Syntax

[ SchemaName . ] SQL92Identifier

Example

DROP INDEX APP.OrigIndex

-- OrigIndex is an IndexName without a SchemaName
CREATE BTREE INDEX OrigIndex ON Flights(orig_airport)

ConstraintName

A ConstraintName represents a constraint. Constraints live in schemas, so you can qualify their names with SchemaNames. There are no system constraints.

Syntax

[ SchemaName . ] SQL92Identifier

Example

-- country_foregn_key2 is a constraint name
CREATE TABLE maps (country_ISO_code CHAR(2)
    CONSTRAINT country_foreign_key2 REFERENCES Countries
    map SERIALIZE(Picture))

CursorName

A CursorName refers to a cursor. No SQL-J language command exists to assign a name to a cursor. Instead, you use the JDBC API to assign names to cursors or to retrieve system-generated names. For more information, see Naming or Accessing the Name of a Cursor in the Cloudscape Developer's Guide. If you assign a name to a cursor, you can refer to that name from within SQL-J statements.

You cannot qualify a CursorName.

Syntax

SQL92Identifier

Example

-- FAROWSCURSOR is a cursor name
UPDATE FlightAvailability
SET economy_seats_taken = economy_seats_taken + 2
WHERE CURRENT OF FAROWSCURSOR

MethodAlias

A MethodAlias is used to refer to a static Java method. It allows you to execute the method without having to enter the full class and method name. Before using a method alias, you must explicitly define it in the current database with a CREATE METHOD ALIAS statement. Method aliases are SQL92Identifiers, so they are case-insensitive. Method aliases do not live in schemas, so they are not qualified with schema names. See CREATE METHOD ALIAS statement for more information.

Syntax

SQL92Identifier

Example

-- abs is a user-defined method alias for java.lang.Math.abs
SELECT abs(running_total)
FROM Groups

-- Method aliases are case-insensitive
SELECT ABS(running_total)
FROM Groups

ClassAlias

Cloudscape allows you to create a ClassAlias for a Java class. Once you create an alias for a class, you do not have to use the complete package and class name for the class, you use the alias instead. In addition, you do not have to use the CLASS keyword in the situations when it would be required for a JavaClassName.

Syntax

SQL92Identifier

Example

-- HotelStay is an alias for JBMSTours.serializabletypes.HotelStay
-- if it werenot an alias, you would have to
-- call the method like this:
-- CALL (CLASS JBMSTours.serializabletypes.HotelStay).archiveRecordsVTI etc...
CALL HotelStay.archiveRecordsVTI(
    getCurrentConnection(), CURRENT_DATE)

AggregateName

Cloudscape allows you to create user-defined aggregates. These are used in the same places as SQL-J's built-in aggregates such as MIN and MAX.

Syntax

SQL92Identifier

Example

-- MaxButOne is a user-defined aggregate
SELECT MAXBUTOne(miles) FROM Flights

StatementName

A StatementName refers to a stored prepared statement named by a user. Stored prepared statements can be qualified with schema names. See CREATE STATEMENT statement for more information about stored prepared statements.

Syntax

[ SchemaName . ] SQL92Identifier

Example

-- create a stored prepared statement in the current schema
CREATE STATEMENT queryFlights
AS SELECT * FROM Flights WHERE orig_airport = ?

TriggerName

A TriggerName refers to a trigger created by a user.

Syntax

[ SchemaName . ] SQL92Identifier

Example

DROP TRIGGER HotelsCascadingDelete

AuthorizationIdentifier

User names within the Cloudscape system are known as authorization identifiers. The authorization identifier represents the name of the user, if one has been provided in the connection request. If a schema name equal to the authorization identifier exists, the user defaults to that schema. User names may be case-sensitive within the authentication system, but they are always case-insensitive within Cloudscape's authorization system unless they are delimited. For more information, see Users and Authorization Identifiers in the Cloudscape Developer's Guide.

Syntax

SQL92Identifier

CALL PropertyInfo.setDatabaseProperty(
    'cloudscape.database.fullAccessUsers', '"!Amber",FRED')

JarName

When you store application logic in a jar file within a Cloudscape database, you give it a JarName within the Cloudscape system. You qualify jar names with schema names.

CALL PropertyInfo.setDatabaseProperty(
    'cloudscape.database.classpath',
    'APP.ToursLogic:APP.AccountingLogic')

JavaIdentifier

A JavaIdentifier represents a Java class, object, method, field name, or property.

JavaIdentifiers are case-sensitive whether they are delimited identifiers or not. They are not automatically translated to uppercase.

There is no upper limit to the length of a JavaIdentifier.

Example

-- these two identifiers are equivalent
java.lang.String
"java"."lang"."String"

This section details the rules for the ways JavaIdentifiers represent the following Java constructs:

JavaClassName

A JavaClassName refers to a Java class or interface. The name must be fully qualified with the package, even if it is in the java.lang package.

The Java class must be declared to be public and must be in the class path of the JVM in which Cloudscape is running (see Deploying Java Classes for Use as Java Data Types in the Cloudscape Developer's Guide).

Syntax

[ JavaIdentifier . ]* JavaIdentifier

JavaClassName Examples

VALUES NEW java.lang.Integer('3')

CREATE TABLE Cities
    (city SERIALIZE(JBMSTours.serializabletypes.City))

CALL (CLASS JBMSTours.serializabletypes.HotelStay).archiveRecordsVTI(
    getCurrentConnection(), current_date)

JavaMethodName

A JavaMethodName refers to a Java method. It is attached to an expression identifying the Java class or object on which the method exists.

The Java method must be declared to be public and its class and must be in the class path of the JVM in which Cloudscape is running (see Deploying Java Classes for Use as Java Data Types in the Cloudscape Developer's Guide). The class defining the method need not itself be declared public; i.e., the usual Java accessibility rules apply.

The method can be final or non-final. The expression in which the method is used determines whether the method can be static or non-static.

Syntax

JavaIdentifier

Example

SELECT DISTINCT city.getName()
FROM Cities
WHERE city->city_id = 5

JavaFieldName

A JavaFieldName refers to a Java field. It is attached to an expression identifying the Java class or object on which the field exists.

The Java field must be declared to be public and its class must be in the class path of the JVM in which Cloudscape is running (see Deploying Java Classes for Use as Java Data Types in the Cloudscape Developer's Guide). The class defining the field need not itself be declared public; that is, the usual Java accessibility rules apply.

The field can be final or non-final. The expression in which the field is used determines whether the field can be static or non-static.

Syntax

JavaIdentifier

Example

-- name is a JavaFieldName
SELECT City->name
FROM Cities