GetOLEDBData

×V2.1Ø

Syntax -

GetOLEDBData <List Name> <Connection String> <Query>

       Where:

       <List Name> is a literal string or a variable that contains a literal string.

       <Connection String> is a literal string or a variable that contains a literal string.

       <Query> is a literal string or a variable that contains a literal string.

Description:

This command is used to add data to an established list for use within the script that is the result of a database query.  If the list already has members, then this data is added to the list, using the current sorting options.  <List Name> identifies the list to be used to store the data.  <Connection String> defines a valid OLE DB connection string that specifies the data source along with all necessary connection information. <Query> is a query statement that is valid for the specified data source and returns tabular data.

Note: The <Connection String> can be a direct reference to a data source, or it can utilize a DSN (Data Source Name) that is already installed on the EMUE PC.  A valid OLEDB connection string can be used to retrieve data from Microsoft Access, Excel, SQL Server, and most other commercial database formats.

The entire query is retrieved at once and stored as a list.  The memory capacity of the PC where EMUE is loaded must be large enough to store the results of the query. It is recommended that the query results include only what is necessary for your script.

To insure that the list is empty before using it, the DisposeList command can be used to remove the list from memory and the CreateList command can be used to re-create the list.

Sample:

       This script shows how a list of PatientIDs and Names can be pulled from an access database.   The values are then extracted, one row at a time, and logged to the output file.

REM Allocate and fill the list – note that the GetOLEDBData command wrapped over two lines here, but

REM should be placed on one line in a script

CreateList PATIENTS

GetOLEDBData PATIENTS "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\PTDB\PTDB.MDB;" "SELECT PTID, [Last Name], [First Name] FROM [PatientList];"

REM Make sure the list pointer is on the first record

SetListPosition PATIENTS 1 Set

:RepeatGet

REM If we have reached the end of the list, then exit

If EndOfList PATIENTS GoTo End

REM Update the progress status

UpdateStatus PATIENTS

REM Retrieve the current values (GetListMember automatically increments the list pointer)

GetListMember PATIENTS PTID LastName FirstName

REM The values are now in variables, so we can do what we want with them.

REM Let’s log them out in a fixed with format.

LogLine PTID:-10 LastName:20 FirstName:20

GoTo RepeatGet

:End

Exit