DAQFactory also supports querying from an ODBC database. To allow for the most flexibility, this is done entirely with scripting, similar to the direct file access functions. You can also add records and do many other SQL commands. All functions begin with DB.:
Note: you must use a 32 bit ODBC driver with DAQFactory. As a 32 bit app, DAQFactory cannot connect to 64 bit ODBC drivers.
Open(DSN, [username], [password]): opens the given data source using the given credentials (or none if not supplied). All parameters should be strings. Returns a handle which is used for functions described below. Note that some ODBC drivers (for example the newer MySQL ones) require you to provide the username and password in the connection even if you have provided it in the data source.
OpenEx(ConnectString): same as above, but allows you to provide your own connection string. This gives more flexibility but requires the user to understand the format of connection strings, which is often database specific. For example, we have found that MSSQL often requires openex() because the normal Open() connection string includes "ODBC;". So you would probably want db.OpenEx("DSN=mysdn;UID=myuser;PWD=mypass").
The other big advantage of using connection strings is that you can connect to databases without creating a data source. You still, of course, have to have the appropriate 32 bit ODBC driver, but you can specify this in the connection string. For example, to connect to a local database using the MySQL 8.0 driver you would use the connection string similar to: "Driver={MySQL ODBC 8.0 ANSI Driver};server=localhost;database=mydatabase;UID=myuser;PWD=mypass;".
Close(dshandle): closes the data source with the given handle. DAQFactory will clean up if you forget, but you can run into memory leaks if you repetitively do Open() on the same data source without closing, as each open creates a new internal object.
Execute(dshandle, SQL String): use this function to perform any SQL command except on that returns records like SELECT. So, this function can be used for adding records, updating records, and performing all other database tasks other than queries. This function returns the number of records affected.
QueryToClass(dshandle, SQL String, [class instance], [don't optimize]): this function performs the given query on the dataset opened with Open() or OpenEx(). The SQL string should be a SELECT command. If a class instance is not provided, the function returns a custom object containing the complete query results. In the class member variables are created for each field name in the query results, plus two extra variables: FieldNames, which contains an array of strings listing all the fields of the query, and RecordCount, which contains the number of records returned from the query, which could be 0. So, for example, if you did:
global myset = QueryToClass(dshandle, "SELECT Name, Address FROM myTable")
then myset.FieldNames would equal {"Name","Address"}, myset.Name would contain an array containing the all the rows for the field Name, etc.
For more advanced users using classes, you can provide an existing class instance (object) instead of having QueryToClass() create one for you. This allows you to use classes with member functions that presumably could work on the fields queried. So:
myObject = new(MyClassType)
QueryToClass(dshandle, "SELECT Name, Address FROM myTable", myObject)
will create the same member variables (if necessary) as the other version of QueryToClass(), but will put them into myObject. Note that we provided the myObject instance and not the MyClassType class definition. The object must be instantiated already.
QueryToClass optimizes it memory allocation (and thus its speed) by modifying your query to include a Count(*) to determine the number of resulting records before doing your actual query. Certain more complicated queries won't work well with this optimization so the final, optional, Don't Optimize parameter can be set to 1 so that DAQFactory does not do this optimization.
QueryToClassArray(dsHandle, SQL String, [not used], [don't optimize]): this function is identical to QueryToClass() except that instead of getting one object with an array for each field in the result, you will get a single array of objects where each object is a record from the result. Because of this, you won't get fieldNames or RecordCount members, and you can't specify the desired class instance.
For example: if your database had two fields, "Name" and "ZipCode" (both strings), and your query resulted in two records, "Fred":"90210" and "George": "37188", QueryToClass() will return a single object (shown in json format):
{
RecordCount : 2,
FieldNames : {"Name", "ZipCode"},
Name : {"Fred", "George"},
ZipCode : {"90210", "37188"}
}
But if you did the same query using QueryToClassArray you would get:
[
{
Name : "Fred",
ZipCode : "90210"
},
{
Name : "George",
ZipCode : "37188"
}
]
Which you use really depends on how you plan to process the data. Typically QueryToClass() is best for retrieving datasets where you want to process (or trend) data across multiple records, while QueryToClassArray() is better for settings and really most anything else where there is a strong correlation between fields.
RecordSet Based Querying:
QueryToClass() is the prefered method for querying databases as it is quite fast. But, some queries aren't supported or actually less efficient. Aggregate functions like Count(*) are a good example. So, as an alternative, you can query using recordsets which return one record at a time.
Query(dshandle,SQL String): performs the given SQL command on the given data source. No semi-colon is required at the end of the SQL string. SELECT statements work best and is what we have tested, though you may be able to use other SQL statements as well. You will have to experiment. This function returns a recordset handle used for the functions below. In most cases you should consider using QueryToClass() instead.
CloseQuery(rshandle): closes the given recordset created by the query. Again, DAQFactory will clean up, but repetitive queries without close will use up your memory.
Field(rshandle, field): retrieves the given field value from the given record set handle. The field parameter can either be a string with the field name, or an index of the desired field within the record.
MoveFirst, MoveLast, MovePrevious, MoveNext, IsBOF, IsEOF(rshandle): these are standard record scanning functions. Use this to move among the records returned by the query and to determine if you are at the beginning or end of the table.
Note: that there are two different handles, dshandle, which is the data source handle returned by open and rshandle which is a record set handle returned by a query.
Here is some sample code. It opens a table, pulls out 4 fields where Operator_ID = "kjljkn" and fills arrays with these values. Obviously your script will be different, but this should give you a good template:
global dbase
global qr
global count
global thetime
global batch
global start
global strTime
dbase = db.Open("daqtest")
qr = db.Query(dbase,"select * from logfile1 where Operator_ID = 'kjljkn'")
global counter
counter = 0
while (!db.IsEOF(qr))
thetime[counter] = db.Field(qr,"TheTime")
batch[counter] = db.Field(qr,"Batch_Number")
start[counter] = db.Field(qr,"Start_Date")
strTime[counter] = db.Field(qr,"Start_Time")
counter++
db.MoveNext(qr)
endwhile
db.CloseQuery(qr)
db.Close(dbase)
