Colon usage in OpenRoad Ingres

>I *KNOW*  this is a simple question, so please bear with me.
>
>I've been working in OpenROAD for about 2 months now.  One thing that
>I've not been able to figure out is, when do you use a colon (":") in
>a script, and when don't you?
>
>Rod


OK, here's everything you always wanted to know about colons but were afraid
to ask:

Use of colons is about as inconsistent as you would ever want.  The general
rule of thumb is: the colon is optional in most places, with a few exceptions.

Exception number 1:  the colon is required on variables that are contained in
an SQL statement, but only in cases where the OpenROAD compiler or the Ingres
server cannot distinguish between variables and columnames.  Otherwise use
of the colon in an SQL statement variable is optional.

An example of where the compiler needs the colon is:
UPDATE table SET column = value
WHERE columnintable = :array[i].attribute;
In this case the compiler gives a syntax error because the array[i] syntax
is not SQL syntax.

An example of where the SQL statement needs the colon is:
UPDATE table SET column = :simplevariable
Here the compiler doesn't complain, but when the SQL statement executes, the
SQL would expect to find a column called 'simplevariable' if the colon was
not used.

Exception number 2: the colon is not appropriate on an event block.  For
example, specifying ON ENTRY :fieldname is not allowed.

Exception number 3: the colon is required when you want the variable to be
translated and the contents used as a name.  For example, you can do the
following:
   frame_name VARCHAR(32) NOT NULL
   ...
   frame_name = 'MyFrame';
   CALLFRAME :frame_name;

In this case, the variable frame_name is translated and the contents used as
the name of the frame to call.  Without the colon, OpenROAD would try to call
a frame named 'frame_name'.  This works for CALLFRAME, OPENFRAME, GOTOFRAME,
and CALLPROC.

Variation of exception number 3: This can be done on pieces of SQL statements.
For example, I could say
   tablename = VARCHAR(24) NOT NULL
   ...
   tablename = 'MyTable';
   SELECT column AS :variable
   FROM :tablename
   etc.
The SQL would use MyTable in the FROM list at runtime.  Again, the colon is
required to tell the SQL statement to translate the variable and use its
contents as the tablename.

Exception number 4: the colon cannot be placed on the control variable of a
FOR statement.  For example, FOR :i = 1 TO 10 generates a compiler error.  I
have reported this as a bug.

Those are all that come to mind.  There may be others that our listeners may
contribute.

Fun stuff, huh?
-- 

Steve Caswell           |   (404) 448-7727    |  "The opinions expressed are my
Principal Consultant    |   sfc@tpghq.com     |   own.  They may not be perfect,
The Palmer Group        |   uunet!tpghq!sfc   |   but they're all I've got."
Ingres Q & A
To William's Home Page
Email William