Using the Record Class

What if we can avoid the use of SQLEXEC in coding?  Improved efficiency of the system.

With the Record class you can build and execute a SQL statement by using the following methods: (Avoiding the use of SQLExec)

      
Delete

      
nsert

      
SelectByKey

      
Update

Example 1

In the following example, the existing code selects all fields into one record then copies that information to another record. The existing code used SQLExec. The rewritten code uses a record object method SelectByKey.

Existing Code

&MYKEY = "001";

SQLExec("select %dateout(msdate1), %dateout(msdate2), %timeout(mstime1),

%timeout(mstime2), %timeout(mstime3), %datetimeout(msdttm1),

%datetimeout(msdttm2), %datetimeout(msdttm3) from ps_xmstbl1 where mskey1 = :1",

&MYKEY, &MYDATE1, &MYDATE2, &MYTIME1, &MYTIME2, &MYTIME3, &MYDTTM1, &MYDTTM2,

&MYDTTM3);

SQLExec("delete from ps_xms_out1 where mskey1 = :1", &MYKEY);

SQLExec("insert into ps_xms_out1

(mskey1,msdateout1,msdateout2,mstimeout1,mstimeout2,mstimeout3,msdttmout1,msdttm

out2,msdttmout3)values(:1,%datein(:2),%datein(:3),%timein(:4),%timein(:5),%timei

n(:6),%datetimein(:7),%datetimein(:8),%datetimein(:9))", &MYKEY, &MYDATE1,

&MYDATE2, &MYTIME1, &MYTIME2, &MYTIME3, &MYDTTM1, &MYDTTM2, &MYDTTM3);

Re-Written Code

SelectByKey works by using the keys you've already assigned values for. It returns successfully if you assign enough key values to return a unique record. In this example, the record has a single key, so only that key value is set before executing SelectByKey. If your record has several keys, you must set enough of those key values to return a unique record.

Local record &REC, REC2;

&REC = CreateRecord(RECORD.XMSTBL1);

&REC.MSKEY1 = "001";

&REC.SelectByKey();

&REC2 = CreateRecord(RECORD.XMS_OUT1);

&REC.CopyFieldsTo(&REC2);

&REC2.Delete();

&REC2.Insert();

Example 2

Existing Code

If None(&EXISTS) Then

SQLExec("insert into ps_rt_rate_tbl (rt_rate_index, term, from_cur, to_cur,

rt_type, effdt, rate_mult, rate_div) values (:1, :2, :3, :4, :5, %DateIn(:6),

:7, :8)", RT_RATE_INDEX, TERM, TO_CUR, FROM_CUR, RT_TYPE, EFFDT, RATE_DIV,

RATE_MULT);

SQLExec("select 'x' from ps_rt_rate_def_tbl where rt_rate_index = :1 and

term = :2 and from_cur = :3 and to_cur = :4", RT_RATE_INDEX, TERM, TO_CUR,

FROM_CUR, &DEFEXISTS);

If None(&DEFEXISTS) Then

SQLExec("insert into ps_rt_rate_def_tbl (rt_rate_index, term, from_cur,

to_cur, max_variance, error_type, int_basis) values (:1, :2, :3, :4, :5, :6,

:7)", RT_RATE_INDEX, TERM, TO_CUR, FROM_CUR, RT_RATE_DEF_TBL.MAX_VARIANCE,

RT_RATE_DEF_TBL.ERROR_TYPE, RT_RATE_DEF_TBL.INT_BASIS);

End-If;

Else

SQLExec("update ps_rt_rate_tbl set rate_mult = :7, rate_div = :8 where

rt_rate_index = :1 and term = :2 and from_cur = :3 and to_cur = :4 and rt_type =

:5 and effdt = %DateIn(:6)", RT_RATE_INDEX, TERM, TO_CUR, FROM_CUR, RT_TYPE,

EFFDT, RATE_DIV, RATE_MULT);

End-If;

Re-Written Code

Local record &RT_RATE_TBL, &RT_RATE_DEF_TBL;

.

.

.

If None(&EXISTS) Then

&RT_RATE_TBL = CreateRecord(RT_RATE_TBL);

&RT_RATE_DEF_TBL = CreateRecord(RT_RATE_DEF_TBL);

&RT_RATE_TBL.Insert();

&RT_RATE_DEF_TBL.SelectByKey();

If None(&DEFEXISTS) Then

&RT_RATE_DEF_TBL.Insert();

End-If;

Else

&RT_RATE_TBL.Update();

End-If;