SQLBase version 8.5 supports SQL99 ANSI join syntax. This syntax uses several keywords that were previously not considered keywords in SQLBase. For this reason, it's possible that your existing databases contain one or more of these keywords used as object identifiers. For example, you may have a database column named NATURAL. Since NATURAL is now a SQL99 keyword, such an identifier would cause syntax errors when used in SQLBase 8.5 with the SQL99 ANSI join syntax option activated.
Note that SQL99 syntax support is an option. You decide, at the time you install SQLBase 8.5, whether you want to use it. If you do not use it, there will be no keyword conflicts or syntax errors. But there will be no SQL99 syntax or multiple outer joins, either. You can install SQLBase 8.5 without choosing this option, then activate the option at some later time by running Connectivity Administrator for your server, clicking on the Server tab, your server name, the Properties button, then clicking the Advanced tab, locating the keyword "ansijoinsyntax", and setting its value to "1". The option applies to an entire server; all databases on that server will use the same option value.
To help you locate potential conflicts, Gupta provides Keyword Check, a simple application which scans all vulnerable objects in one or more databases on a specific server, looking for instances where the new keywords might be in use as identifiers. Once such instances are found, you decide whether to remove the conflict by renaming the database objects in conflict, or by using quoted identifiers for the conflicting names.
Keyword Check (file joinkeywordcheck.exe) is a client application. It must reside in the same directory as your other database client applications, with access to SQL.INI and client executable files.
From that directory, execute joinkeywordcheck.exe
Keyword Check is a console application. It accepts command-line arguments to change its behavior. When run without any command-line arguments, you get the default behavior shown in the image. At a minimum, Keyword Check needs a server name to use for its search, so it stops and prompts you for that name. Then it examines all vulnerable objects in all datbases on that server and reports its findings to the console screen.
It's likely that you'll choose to change the default behavior in some ways. For
example, you might wish to send the output to a file instead of to the screen,
and perhaps you only wish to examine two databases on the server. In such cases you
would create a command line to run Keyword Check. From the Windows Start menu,
choose Run, then type a command line like the one below:
"C:\Program Files\SQLBase 8.5\JoinKeywordCheck.exe" /OUTFILE:keyword.txt
/DB:island/sysadm/sysadm /DB:testdb
(Your program folder name may differ from the one shown in this example.)
Separate each argument by a space. All arguments are optional. If Keyword Check needs a value that is not supplied in an argument, it will prompt for it.
Server name
Example: /SERVER:New85
Server name is the only argument that is absolutely required for Keyword Check
to run. If it is not specified in the command line, Keyword Check will pause
and prompt you for the name. Keyword Check can run against only one server at
a time.
User ID
Example: /USER:sysadm
Specify the ID used to connect to the server. If no value is specified, "sysadm"
is the default.
Database names
Example: /DB:island/sysadm/sysadm
Specify database names to analyze. If no value is specified, all databases on
the server are analyzed. Note the three parts of the argument value, separated
by slashes. The first part is the database name; the second is the user ID for
that specific database, and the third is the password for that user ID. If the
user ID or password is left blank, SQLBase defaults will be used. Keyword Check
evaluates arguments in left-to-right sequence, and it caches any user IDs and
passwords that it finds. This means that if you place more than one /DB argument
in your command line, and all the databases have the same user ID and password,
you only need to specify user ID and password in the leftmost /DB argument.
/DB is the only argument that can occur more than once in the command line (one argument per database name).
Object types
Example: /OBJECTS:tvp
Specify the database object types that Keyword Check should evaluate. Enter
a character string composed of one or more of the following case-sensitive characters:
tvsifpcCT
Each character in that string represents one of the database object types that can
be analyzed by Keyword Check. Here are their meanings:
t - Tables, and their columns
v - Views, and their columns
s - Synonyms
i - Indexes
f – External functions
p – Stored procedures
c – Stored commands
C – Constraints
T – Triggers
Prompt for values
Example: /PROMPT
If this argument is specified, Keyword Check will prompt for needed database
user IDs and passwords when the default values do not work.
Detailed messages
Example: /VERBOSE
If this argument is specified, Keyword Check will output a message for every
database object it analyzes, whether or not it discovers any potential conflicts. If
this argument is not specified, only those objects with potential conflicts will
appear in the output.
Output to file
Example: /OUTFILE:keyword.txt
If this argument is specified, Keyword Check will output messages to a text
file with a name and location that you specify. Otherwise, messages are output
to the console.
Here's a quick look at the output of Keyword Check when run against database ISLAND.
JoinKeywordCheck [Version 1.0] - SQL99 ANSI Join Keyword Check
Report Date: 05/13/2003 at 07:02:11 AM
Report File: keyword.txt
Database: ISLAND
SQLBase Version: 8.5.0
Checking TABLES and COLUMNS:
Checking VIEWS and COLUMNS:
Checking SYNONYMS:
Checking INDEXES:
Checking CONSTRAINTS:
Checking STORED PROCEDURES:
Checking STORED COMMANDS:
Checking TRIGGERS:
SYSADM.INV_UPDATE
- Object potentially contains invalid use of keyword "ON"
Encountered one or more illegal references to reserved keywords in trigger
command text. All references to reserved keywords should follow proper
quoting rules.
Checking EXTERNAL FUNCTIONS:
Keyword Check issues a message for each database object that may have a potential conflict with ANSI join keywords. Notice the warning message for trigger SYSADM.INV_UPDATE in the example above. Let's look at the text of that trigger to see what the problem might be:
CREATE TRIGGER SYSADM.INV_UPDATE After Update of QUANTITY ON SYSADM.INVOICE_ITEM Referencing Old as INVOICE_ITEM_OLD New as INVOICE_ITEM_NEW(Execute SYSADM.QUANTITY_UPDATE(INVOICE_ITEM.STYLE_ID, INVOICE_ITEM_OLD.QUANTITY, INVOICE_ITEM_NEW.QUANTITY )) For Each Row
In this case, the word ON in the first line of the trigger is a potential conflict with the new ANSI join keyword ON. Your task is to examine the text of the trigger and determine whether the word ON is actually inside a SQL query or not. In this case, it is not, so no changes need to be done to this trigger.
If you find cases where your SQL statements are clearly using the new keywords in a way that will cause syntax errors, you have three choices. Obviously, you can rename database identifiers that may cause such errors. This might involve a lot of testing, application modifications, or data migration, but it is the more certain way of fixing the problem.
Another possibility is to use quotes around identifiers that might conflict with keywords. For example, the following two queries are functionally identical:
SELECT * FROM BLONDES ORDER BY NATURAL SELECT * FROM "BLONDES" ORDER BY "NATURAL"
But the first query will cause a syntax error in SQLBase 8.5 when the SQL99 ANSI join syntax is active. The second query will not. The hazard in using this approach is that you must be certain that all client applications querying this database server are also using quoted identifiers. If some third-party query or reporting tool does not use quoted identifiers, the syntax error will still occur. Also, quoted identifiers require case-sensitive names for identifiers, whereas unquoted identifiers are not case-sensitive.
And finally, you can also choose not to use the SQL99 ANSI join syntax feature in SQLBase 8.5. If you installed 8.5 and chose the new feature, you can turn it off by running Connectivity Administrator for your server, clicking on the Server tab, your server name, the Properties button, then clicking the Advanced tab, locating the keyword "ansijoinsyntax", and setting its value to "0".
Copyright © Gupta Technologies LLC. Gupta, the Gupta logo, Centura, and all Gupta products are licensed or registered trademarks of Gupta Technologies, LLC., All other products are trademarks or registered trademarks of their respective owners. Copyright © 2003 Gupta Technologies