Friday, 5 May 2017

Insert rows using PeopleCode

When inserting rows using PeopleCode, you can either use the Insert method with a record object or create a SQL Insert statement using the SQL object. If you do a single insert, use the Record Insert method. If you are in a loop and,therefore, calling the insert more than once, use the SQL object. The SQL object uses dedicated cursors and, if the database you are working with supports it, bulk insert.
dedicated cursor means that the SQL gets compiled only once on the database, so PeopleTools looks for the meta-SQL only once. This can increase performance.
For bulk insert, inserted rows are buffered and sent to the database server only when the buffer is full or a commit occurs. This reduces the number of round-trips to the database. Again, this can increase performance.

⇒ The following is an example of using the Record Insert method:
&REC = CreateRecord(Record.GREG); 
&REC.DESCR.Value = "Y";
&REC.EMPLID.Value = "12180014"; 
&REC.Insert();


⇒ Insert()
The Insert method uses the field names of the record and their values to build and execute an Insert SQL statement which adds the given record (row of data) to the SQL table.Because this method results in a database change, it can only be issued in the following events:
  • SavePreChange
  • WorkFlow
  • SavePostChange
Example:
Local record &REC;
&REC = CreateRecord(RECORD.MYRECORD);
&REC.KEYF1.Value = "A";
&REC.KEYF2.Value = "B";
&REC.MYRF3.Value = "X";
&REC.MYRF4.Value = "Y";
&REC.Insert();

⇒The following is an example using a SQL object to insert rows:
&SQL = CreateSQL("%INSERT(:1)"); 
&REC = CreateRecord(Record.GREG); 
&SQL.BulkMode = True; 
For &I = 1 to 10 
   &REC.DESCR.Value = "Y" | &I; 
   &REC.EMPLID.Value = &I; 
   &SQL.Execute(&REC); 
End-For;




HAPPY LEARNING:)

No comments:

Post a Comment

Dynamic view in PeopleSoft

Views are a useful feature of SQL databases, letting us create virtual tables based on SQL select statements. PeopleSoft 8 provides the f...