ODBC from x++ example code

public client static void processMDBData(str _StrDSN, str _StrTableName, date _DateFrom, date _DateTo)
{
    #AviFiles
    SysOperationProgress    progress = new SysOperationProgress();
    OdbcConnection          C;
    Statement               S;
    LoginProperty           LP = new LoginProperty();
    ResultSet               RS;
    HcmPersonnelNumberId    _EmplID;
    TimeHour24              _Time;
    str                     strquery,strDSN,tableName;
    Date                    _Date;
    int                     Idx;
    HRSTMAttendanceTemp     attDataTemp;

    ;

    try
    {   
        strDSN    = _StrDSN;
        tableName = _StrTableName;

        ttsbegin;
        LP.setDSN(_strDSN);
        LP.setServer("S-AOS1TEST");

        C   = new OdbcConnection(LP);
        S   = C.createStatement();


        strquery    = strFmt("SELECT * FROM %1 where dateLog >= #%2# AND dateLog <= #%3#",tableName,_DateFrom,_DateTo);
        RS  = S.executeQuery(strquery);
        while (RS.next())
        {
            //Progress.incCount();
            _EmplID = Rs.getString(1);
            _Date   = RS.getDate(2);
            _Time   = str2time(RS.getString(3));
            Idx     = str2int(rs.getString(4));
            
            //insert to temp table
            if(_EmplID == '') continue;
            select forupdate attDataTemp
            where attDataTemp.PersonnelNumber == _EmplID && attDataTemp.Time == _Time
                                  && attDataTemp.TransDate == _Date;
            if(!attDataTemp)
            {
                attDataTemp.clear();
                attDataTemp.PersonnelNumber      = _EmplID;
                attDataTemp.Time        = _Time;
                attDataTemp.TransDate   = _Date;
                attDataTemp.Idx         = Idx;
                attDataTemp.insert();
            }
            else
            {
                attDataTemp.Idx         = Idx;
                attDataTemp.ClockId     = '';
                attDataTemp.update();
            }

            Progress.setText(strfmt("Upload Id %1 - %2 %3", _EmplID, _date, time2str(_time, 1, 1)));
        }
        ttsCommit;

   }
   catch
   {
       ttsAbort;
       return;
   }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s