GetSQL function

Syntax

GetSQL(SQL.sqlname [, paramlist])

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

inval1 [, inval2] ...

Description

Use the GetSQL function to instantiate a SQL object and associates it with the SQL definition specified by sqlname. The SQL definition must already exist, either created using Application Designer or the StoreSQL function.

Processing of the SQL definition is the same as for a SQL statement created by the CreateSQL function.

Parameters

Parameter Description

SQL.sqlname

Specify the name of a SQL definition.

paramlist

Specify input values for the SQL string.

Returns

A SQL object.

Example

The following code creates and opens an SQL object on the SQL definition stored as ABCD_XY (for the current market, database type and as of date). It binds the given input values, and executes the statement. If the SQL.ABCD is a SELECT, this should be followed by a series of Fetch method calls.

&SQL = GetSQL(SQL.ABCD_XY, ABSENCE_HIST, &EMPLID);

The following is a generic function that can be called from multiple places to retrieve a specific record using the SQL Objects.

Local SQL &SQL; 
Local string &SETID, &TEMPLATE; 
Local date &EFFDT; 
Function FTP_GET_TEMPLATE(&REC As Record) Returns Boolean ; 
   &TEMPLATE = FTP_RULE_TEMPLATE; 
   &EFFDT = EFFDT; 
   &SETID = SETID; 
   &SQL = GetSQL(SQL.FTP_TEMPLATE_SELECT, &SETID, &TEMPLATE, &EFFDT); 
   If &SQL.Status = 0 Then 
      If &SQL.Fetch(&REC) Then 
         &SQL.Close(); 
         Return True; 
      End-If; 
   Else 
      &TITLE = MsgGet(10640, 24, "SQL Error"); 
      MessageBox(64, &TITLE, 10640, 23, "SQL Object Not Found in SQL", SQL.FTP_⇒
TEMPLATE_SELECT); 
   End-If; 
   &SQL.Close(); 
   Return False; 
End-Function;

The SQL definition FTP_TEMPLATE_SELECT has the following code. Note that it uses the %List and %EFFDTCHECK meta-SQL statements. This makes the code easier to maintain: if there are any changes to the underlying record structure, this SQL definition won’t have to change:

SELECT %List(FIELD_LIST,FTP_DEFAULT_TBL A)   
FROM PS_FTP_TEMPLATE_TBL A   
WHERE A.SETID = :1  AND A.FTP_RULE_TEMPLATE = :2   
AND %EFFDTCHECK(FTP_DEFAULT_TBL A1,A,:3)  AND A.EFF_STATUS = 'A'

Setting Data Fields to Null

This function does not set Component Processor data buffer fields to NULL after a row not found fetching error. However, it does set fields that aren’t part of the Component Processor data buffers to NULL.

Using Arrays With paramlist

You can use a parameter of type "Array of Any" in place of a list of bind values. This is primarily used when you do not know the number of values being supplied until the code runs.

For example, suppose you had a SQL definition INSERT_TEST, that had PeopleCode that dynamically (that is, at runtime) generated the following SQL statement:

"INSERT INTO PS_TESTREC (TESTF1, TESTF2, TESTF3, TESTF4, . . .TESTN) VALUES (:1, :⇒
2, %DateTimeIn(:3), %TextIn(:4). . .N)";

Suppose you have placed the values to be inserted into an Array of Any, say &AAny:

&AAny = CreateArrayAny("a", 1, %DateTime, "abcdefg", . . .N);

You can execute the insert by:

GetSQL(SQL.INSERT_TEST, &AAny);

Because the Array of Any promotes to absorb any remaining select columns, it must be the last parameter for the SQL object Fetch method or (for results) SQLExec. For binding, it must be the only bind parameter, as it is expected to supply all the bind values needed.

Related Topics