Once you have connected to the database, you can get a list of available tables or find a specific table.

Find Available Tables

To find out which tables are available, use the GetTables method of the database object:

tables = objDB->GetTables()

Note: The GetTables method is not available with all drivers.

The result is an array of IDL structures containing information about the available tables. See IDLdbDatabase::GetTables for more information.

Find Specific Tables

To find out if a specific table is available, inspect the list of tables returned by the GetTables method. The following IDL commands check to see if “mytable” is listed in the array of tables, and if so, print the word “Yes” to the IDL command log:

index = WHERE(tables.name EQ 'mytable', nmatch)
IF(nmatch ge 1) THEN PRINT, 'Yes'

You are now ready to connect to the table and retrieve data.

Connect to a Table

To connect a table and retrieving its data, you will:

  • Create a Recordset object
  • Specify the table from which the information is being retrieved

The Recordset object contains a database table or a selection based on criteria you specify in an SQL query. This object allows you to programmatically manipulate the data in the database. To create this object, a valid database object is required.

In the following example, a new Recordset object is being created for a table called “mydata.”

objRS = OBJ_NEW('IDLDBRecordset', objDB, table='mydata')

Once you have connected to a table, you can use IDL DataMiner methods to manipulate the data in several ways as depicted in the examples provided in the next section.

Note: When a table is selected, the entire data contained in the table is not automatically imported into IDL. This preserves memory. You can retrieve the desired data in a recordset by moving the cursor to the desired record via the MoveCursor method and then importing that data into IDL using the GetField or GetRecord method.

Working with Table Data

Once you have created the Recordset object and connected to a table, you can use IDL DataMiner methods to retrieve and manipulate the data in several ways. For example, you can:

  • Find out if a table is “ReadOnly”

  • Get properties of the recordset

  • Move the cursor

  • Add records

  • Delete records

  • Retrieve fields

  • Set fields

  • Find the current row number in a recordset

  • Find the number of fields in a recordset

  • Get an array of field information structures, one for each field in the recordset

You can also obtain information about a database or recordset for the following:

  • Number of table fields

  • Name of DBMS associated with a database object

  • DBMS version

  • List of available drivers

  • ODBC driver level

  • ODBC driver version

  • Maximum number of connections

Moving Through a Recordset

Moving through recordsets is based on the cursor. The cursor is the current row, or record, in the recordset. When you refer to fields in a Recordset, you obtain values from the current record, and only the current record can be modified.

You can use the IDLdbRecordset::MoveCursor method to navigate through the records in a recordset. Keywords to the MoveCursor method allow you to specify new cursor locations.

In the following example, the MoveCursor method and FIRST keyword move to the first record.

status = objRS->MoveCursor(/FIRST)

In the following example, the MoveCursor method and NEXT keyword move to the next record.

status = objRS->MoveCursor(/NEXT)