![]() |
SQL-J Language Reference
|
Reference Manual |
SQL-J IdentifiersAn 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:
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 JavaIdentifiersNon-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). "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"). SQL92IdentifierAn 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
-- the view name is stored in the system This section describes the rules for using SQL92Identifiers to represent the following dictionary objects:
Qualifying Dictionary ObjectsSince 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. SchemaNameA 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. SyntaxSQL92Identifier Example
-- APP.CustomizedTours is a TableName qualified by a SchemaName
-- You must qualify system catalog names with their schema, SYS TableNameA 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 ViewNameA 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 CorrelationNameA 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. SyntaxExample
-- C is a CorrelationName ColumnNameIn 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 SimpleColumnNameA 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. SyntaxExample-- country is a SimpleColumnName
CREATE TABLE Countries IndexNameAn 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
-- OrigIndex is an IndexName without a SchemaName ConstraintNameA 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 CursorNameA 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. SyntaxExample-- FAROWSCURSOR is a cursor name MethodAliasA 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. SyntaxExample
-- abs is a user-defined method alias for java.lang.Math.abs
-- Method aliases are case-insensitive ClassAliasCloudscape 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. SyntaxExample
-- HotelStay is an alias for JBMSTours.serializabletypes.HotelStay AggregateNameCloudscape 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. SyntaxExample
-- MaxButOne is a user-defined aggregate StatementNameA 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 TriggerNameA TriggerName refers to a trigger created by a user. Syntax[ SchemaName . ] SQL92Identifier ExampleDROP TRIGGER HotelsCascadingDelete AuthorizationIdentifierUser 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
CALL PropertyInfo.setDatabaseProperty( JarNameWhen 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( JavaIdentifierA 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 This section details the rules for the ways JavaIdentifiers represent the following Java constructs: JavaClassNameA 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 ExamplesVALUES NEW java.lang.Integer('3')
CREATE TABLE Cities
CALL (CLASS JBMSTours.serializabletypes.HotelStay).archiveRecordsVTI( JavaMethodNameA 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. SyntaxExample
SELECT DISTINCT city.getName() JavaFieldNameA 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. SyntaxExample |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |