📚 SAP Business One SDK Help

Recordset Object
See Also  Members  Example

Description

Recordset is a raw data access object that enables you to select data from the database, navigate through the result set, and manipulate user tables, which are not exposed by the DI API. The main method of this object is DoQuery that enables you to run SQL queries with any DML action in its query string.

Object Model






Remarks

Browsing Mechanisms

The Recordset object includes two browsing mechanisms: the first mechanism applies to result sets that contain only one row (by the use of Select Top), which will retrieve only one record each time. Otherwise, the browsing will be performed on existing result set only.

DML Operations on Database Tables

The Recordset object allows the following Data Manipulation Language (DML) operations: UPDATE, INSERT, and DELETE.

DML operations are acceptable with the Recordset object for user tables only. For other business objects use only the relevant DI objects and not the Recordset object. Any DML operations on system tables pose a high risk for data corruption, and will not be supported. Use at your own risk.

Before the Recordset object executes an SQL query, it validates the user permission (same user permission as in SAP Business One). Otherwise, the SQL query is blocked.

Blocking DDL Actions

The following Data Definition Language (DDL) actions are blocked: CREATE, DROP, ALTER, and TRUNCATE. The reason for that is, when upgrading the SAP Business One application, it ignores user tables that are not created using the DI meta data objects (UserTablesMD and UserFieldsMD).

Limitation

Only one expression can be specified in the select list when the sub-query is not introduced with EXISTS.

Related Topics

Selecting Data Using the Recordset Object

Retrieving a Field of a Recordset

Example

Recordset Sample (Visual Basic)Copy Code
Private Sub RecordsetOperations()

    Dim oRecordSet As SAPbobsCOM.Recordset

    '// Get an initialized Recordset object
    Set oRecordSet = oCompany.GetBusinessObject(BoRecordset)

    '// Perform the Select statement
    '// The query result will be loaded
    '// into the Recordset object
    '// This is the most effective way to retrieve data
    oRecordSet.DoQuery ("Select cardcode, cardname from ocrd")

    '// Access the data

    '// The Select statement retrieves a record set with two fields
    '// Place the fields' values in the text boxes
    '// look at the field object in the Help file
    '// for more field properties (such as name etc.)
    '// in this case Field(0).Name will be 'CardCode'

    oRecordSet.Fields.Item(0).Value
    oRecordSet.Fields.Item(0).Name

    '// Get the next record

    If oRecordSet.EOF = False Then
        oRecordSet.MoveNext
    End If

    '// Get the previous record

    If oRecordSet.BoF = False Then
        oRecordSet.MovePrevious
    End If

End Sub




Example

See Also