Dynamic SQL in App Engine

Dynamic SQL in App Engine

Some time we need to run a dynamic sql in App Enigne.

Some would prefer creating the entire query in a string and run it in PeopleCode.

But SQL is much faster than PeoleCode.

How do we achieve this functionality in SQL?

We could have an AET record field hold the value and use it in SQL.

How to implement this?

We need to push the required condition in the AET record field value and use %SQL and %BIND to implement this.

Though your SQL might be complicated, done with many if, else, case, etc,  Lets say the SQL is a simple condition.

If condition Then

   AET_REC.SQL_FIELD= "AND T1.FLD1 = T2.FLD1 AND T1.FLD2 = T3.FLD2";

Else

   AET_REC.SQL_FIELD.Value = ":

End-If;

Now in AE Action SQL we can use as follows

UPDATE TBL4

SET FLDX = 'VAL'

WHERE FLDX IS NULL

%SQL(%Bind(SQL_FIELD),NOQUOTES)


Why do we need NOQUOTES?
%Bind assumes the content of the variable to be a string and put quotes around it. To avoid it we must use NOQUOTES.

How to write spaces instead of null into a file while using CSV FileLayout and PeopleCode?

How to write spaces instead of null into a file while using CSV FileLayout and PeopleCode?

It is some times necessary to write spaces instead of nulls into CSV files.

By default CSV FileLayout writes "" (no content) instead of " " (space) where " is qualifier.

To avoid this we can use the property UseSpaceForNull.

Local File &MYFILE;

&MYFILE.SetFileLayout(FileLayout.FileLayoutName);

&FILE1.UseSpaceForNull = True;

This is False by default.