Tuesday, December 29, 2009

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;

No comments: