2.2.4.46.62 Worksheet::ReadRecordset

ReadRecordset

1 Description
2 Syntax
3 Parameters
4 Return
5 Examples
6 Remark
7 See Also
8 Header to Include

Description

Read data from recordset object into worksheet.

Syntax

int ReadRecordset( Object & object, DWORD dwCntrl = LAYWKGETRECORDSET_BY_COLUMN_INDEX_WITH_COLUMN_RENAMING, int nRowBegin = 0, int nNumRows = -1, int nColBegin = 0, POSTREADRS postReadRS = NULL )

Parameters

object: [input] The recordset.

dwCntrl: [input] Options from LAYWKGETRECORDSET_BY_COLUMN_INDEX , etc (OC_const.h). // beginning with the starting column nColBegin retrieve all fields (will add columns as necessary). default is LAYWKGETRECORDSET_BY_COLUMN_INDEX_WITH_COLUMN_RENAMING

nRowBegin: [input] The starting row in the worksheet. default is 0.

nNumRows: [input] Total number of records to retrieve,or < 0 to retrieve all available. default is -1.

nColBegin: [input] Applies only to the options LAYWKGETRECORDSET_BY_COLUMN_INDEX with or without LAYWKGETRECORDSET_BY_COLUMN_INDEX and LAYWKGETRECORDSET_SET_COLUMN_NAME bits the column in worksheet from which to begin. default is 0.

postReadRS: [input,optional] (8.1SR2) if supplied, postReadRS->nCodePage holds the code page to be used when converting string values from database. If postReadRS->nCodePage > 0, the code page value postReadRS->nCodePage is used. If postReadRS->nCodePage < 0, the current code page used in Origin for various string manipulations (which might be different from OS code page) is used. If postReadRS->nCodePage == 0 (same as postReadRS == NULL), then the current OS code page is used.

Return

error message, 0 if no error

Examples

EX1

#define DB_SETUP_STR            "Provider=Microsoft.Jet.OLEDB.4.0;Password="";User ID=Admin;Data Source="
#define DB_FILE_FULLPATH        GetAppPath(TRUE) + "Samples\Import and Export\stars.mdb"
#define TABLE_NAME                 "Stars"
 
//function to retrieve data and put into a worksheet
int ReadRecordset_ex1()
{    
    //create the ADODB.Recorset object
    Object ocrs = CreateObject("ADODB.Recordset");    
    if( !ocrs )
    {
        out_str(" ADO init error!");
        return -1;
    }
 
    // prepare and open database recordset
    string strConn = DB_SETUP_STR + DB_FILE_FULLPATH + ";";
    string strQuery = "select * from "+ TABLE_NAME + ";";

    //make the recordset ReadOnly (adUseClient) for faster open
    ocrs.CursorLocation = 3;//3=adUseClient(enum added in Origin 9)

    ocrs.open(strQuery, strConn, 1, 3);    
 
    //prepare worksheet
    Worksheet wks;
    wks.Create("origin");
 
    //read data into the worksheet.
    int nRet = wks.ReadRecordset(ocrs);
    if ( nRet )
        return nRet;
    if (ocrs.State == 1 ) //adStateOpen
         ocrs.Close();
    return 0;
}

Remark

The performance depends on how the recordset is opened, as controlled by CursorLocation (adUseClient, or adUseServer, see in query_utils.h). For reading into worksheet, usually set CursorLocation as adUseCient is enough, which will cause CursorType be set as adOpenStatic, which means that any other application's update into source database will not affect this recordset once it is open. It is like the recordset is open as read-only. See CursorType, CursorLocation for more details.