December 19, 2007

Meta-SQL Wrappers

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.

Until next time...