Reading SQL Server 2005 ODBC

[burt4munger]

The attached ctl file shows a connection to an ODBC Sql Server 2005 database. I can't reliably read values from a selected record using db.field, with either a field name or an index value. I've tried different data types, but for the attached example all are type int. Some work, others don't, but one that doesn't work one time will work another time if the read order is changed. All reads the second time fail. I do get a reliable number of records returned. The exact same code using a DSN connected to an Access mdb works fine, although the second read fails there too (not a big issue). The same results for SQL Server driver or SQL Server Native driver. Any idea what's wrong?

ODBCFails.ctl

[AzeoTech]

For each query you must read everything in order. You cannot go backwards. This is because the SQL server ODBC driver is set for a forward-only cursor.

I recommend trying the new QuerytoClass() function. It has been around a while actually, but remains undocumented in our help file for reasons I won't go into but have nothing to do with its reliability. Basically you do this:

private handle
private result
handle = db.Open(....)
result = db.QueryToClass(handle, "Select field1 from mydb")
? result.field1
db.close(handle)

So, everything is largely the same as before, you still open and close the database and use a handle, but the querytoclass() call (which only works with SELECT), returns the actual result of the query rather than a handle to a record set. The result is stored in a class, which you can basically think of as a structure. The structure contains each of the fields from the query. So, in my example, the result has field1 since that is what I asked for, and doing result.field1 gives me an array with all the values of field1. If your select asked for field2 as well, you'd do result.field2 to get its values. You can determine the names of the fields by doing result.FieldNames, which is an array of strings. You can get the number of records in the result by doing result.RecordCount.

This method is much faster than the old way, and allows for random access to your data even if your database only has forward only cursors. Just be careful not to query a million records at once or something that would otherwise overload your memory limits. This is actually true with Query() as well, but less obvious.

[burt4munger]

That makes sense. Thanks! I'll give it a try.