| Meta-SQL Wrappers | KEVIN RESCHENBERG
12-19-2007 |
In Updating Long Character Data I
showed a kludgy workaround for a problem in updating values longer
than approximately 254 characters. This was specifically an SQR-related issue, but I said
that the same ugly technique could be used in PeopleCode as well.
Well, Fernando had this problem in PeopleCode and after doing
some web searches, he checked PeopleBooks (hey, isn't that cheating?) and
found the %TextIn wrapper, as in:
SqlExec("INSERT INTO PS_TABLE (KEY,LONG_COL) VALUES (1, %TextIn(:1))", &Data);
PeopleBooks says that "If you don't use this meta-SQL wrapper,
this type of operation fails on Sybase and Informix."
"Meta-SQL" refers to constructs that can be used in SQL statements but aren't
actual SQL. Instead, they expand into something else. One major use for meta-SQL is
to allow one piece of code to be executed on different SQL platforms, as in
the example above. Meta-SQL is also
used for convenience—for example, to list all of the columns defined for a
table or to add effective date conditions. There are a large number of meta-SQL
elements and they are documented in the PeopleCode Language Reference.
There is also meta-SQL for SQR, although the list is far smaller than the
PeopleCode elements. Look in SETENV.SQC.
Would you ever need to use these, especially in SQR? I've rarely seen them in
client SQR programs. (If you are moving from one RDBMS to another, the fact that
your SQR SQL uses "+" instead of "||" for concatenation is probably the least of
your problems and is easily corrected. Hopefully adopting a new RDBMS is a rare occurrence.)
PeopleCode meta-SQL, however, provides much more than database independence.
But when using it for convenience, be careful not to cross the line into unreadability.
|
