Createsql in Peoplecode

Createsql in Peoplecode

How to use Createsql in Peoplecode? You can use createsql for dynamic SQL or static sql of each purpose. There are many ways you can create SQL text.

Syntax of Create SQL

CreateSQL([{sqlstring | SQL.SqlName}[, paramlist]])

Where paramlist is an arbitrary-length list of values in the form:

inval1 [, inval2] ..

Use the CreateSQL function to instantiate a SQL object from the SQL class and opens it on the given sqlstring and input values. sqlstring is a PeopleCode string value giving the SQL statement.

Any errors in the SQL processing cause the PeopleCode program to be terminated with an error message.

You can use CreateSQL with no parameters to create an empty SQL object that can be used to assign properties before being populated and executed.

Example 1: You want to select all employee name and blood of deptid and mgrid.

You can create dynamic sql to text in peopolecode such as

&RPT = “select emplid,name,blood form table where deptid = :1 and MGRID=:2”

Local SQL &sql2 = CreateSQL(&RPT, TABLE.DEPTID, TABLE.MGRID);

While (&sql2.Fetch(&emplid,&name,&blood))
&file.WriteLine(&emplid | “‘”|&name | “‘”| &blood);
End-While;

Example 2: Create Sql Text in Message Catalog

You can use PeopleCode function all sql text in a message and pass the value in the message catalog.

&SQL_RPT = MsgGetExplainText(20003, 107, “Message not found.”, TABLE.DEPTID, TABLE.MGRID);

&sql1 = CreateSQL(&SQL_RPT);