Inserting or updating long fields to database

It often errors out when we try to insert or update a record with long fields. Different databases handle the long fields differently and there are often errors and headaches included when dealing with a long field. As a peoplesoft developer, you might have often experienced the problem when dealing with application engine sql actions or sqlexec() function in peoplecode events.

However PeopleSoft has delivered some solution to handle the long fields (although not a full solution). The way PeopleSoft wants us to handle the long fields is via metasql’s. PeopleSoft has delivered two metasql’s towards this intension.

1.       %InsertSelectWithLongs

This metasql can be used in PeopleCode, Application Engine, SQL, Views & Dynamic Views. This metasql is helpful when you are trying to insert a value into the database. 

The syntax of the metasql is as below.

%InsertSelectWithLongs([DISTINCT, ]insert_recname, select_recname [ correlation_id][, select_recname_n [ correlation_id_n]] [, override_field = value]. . .) 

Eg: %InsertSelectWithLongs(DISTINCT, A,B,C)

FROM A,B

WHERE %Join(COMMON_KEYS,B,C)

2.       %TextIn

This metasql can be used in PeopleCode, Application Engine, SQL, Views & Dynamic Views. It can be used when you are writing an update statement as well as insert statement.

The syntax is as below.

%TextIn(BindVariable)

Eg: SqlExec(“Update PS_TABLE SET LONG_FIELD=%TextIn(:1) WHERE CRITERIA_FIELD =’TRUE’”,”My Long Text”);