[Gambas-devel] Change to gb.db.odbc to use ODBC Connection Strings.

Benoît Minisini gambas at ...1...
Sat Oct 10 03:31:54 CEST 2015

Le 10/10/2015 03:03, ML a écrit :
> Regarding ODBC in general:
> I'm no ODBC expert, but from what I read ODBC (Open DataBase
> Connectivity) is an open ISO/IEC standard for middleware that maps and
> routes standard SQL queries to a freaking wide variety of RDBMS systems.
> Some of these RDBMSs are IBM's DB2, MySQL, MSSql, Oracle and many
> others. It does so by using dynamically loaded drivers. By themselves,
> unixODBC, iODBC, MSODBC, etc are pretty much useless, as they need a
> driver to connect and send/receive data to/from the RDBMS.
> ODBC -in any supported processing platform (Win, Linux, Mac,
> RaspberryPi, etc)- has a Driver Manager. You can also specify the driver
> and parameters to connect to a RDBMS in a variety of ways (user/system
> INI files, connection strings, and even mixed modes), and then ODBC
> presents the application with a standard set of calls to achieve
> practically whatever you need on the RDBMS. Then, ODBC and the selected
> driver map these calls to requests that the RDBMS understand and
> process. Lastly, ODBC and the driver reinterpret what the RDBMS responds
> back to the app in a standardized way.
> The whole idea is, from the app point of view, no (or few) code changes
> to get the same results no matter the RDBMS or processing platform your
> app uses. The only problem you might encounter is an RDBMS that does not
> have feature X and your program relies on that feature. In these cases,
> the ODBC/Driver couple may just respond with an error, or may even go to
> some extent to emulate the feature in software. But even so I think it
> is worth supporting it.
> So, as I said, as long as your preferred RDBMS has an available ODBC
> driver, you should only need to use it through ODBC and if you switch to
> another RDBMS (MySQL, MSSql, Oracle, DB2, etc), you should NOT have to
> touch your app. Just change the driver in the connection string or INI
> file(s), and you're all set.
> Regarding record fetching:
> You're correct assuming ODBC fetches records one at a time. You can also
> fetch a block of (consecutive) rows at any given time.
> Regarding SQLRowCount():
> The issue with this call is that it is documented as returning records
> affected by INSERT, MERGE, DELETE, or UPDATE queries only. Any SELECT
> query will not have a record count. I have a slight idea why (see next
> paragraph on concurrency). And, apparently, the consortium that rules
> ODBC does not like you to have the whole data chunk in memory (my
> guess). ODBC is designed so you get data sequentially, and stop when
> ODBC tells you back "NO (more) DATA".

OK. I thought, because of its name and the way it was used in the 
driver, that SQLRowCount() was returning the number of rows in a query 

> But not all is lost. ODBC gives you cursors (some drivers implement more
> cursor functionality than others, though). Cursors are like C pointers.
> They keep track of rows in a returned rowset. You can use cursors to
> seek rows, which is what I did. Problem is, cursors MAY change as data
> is added/removed (by other users of the RDBMS, although I'm not 100%
> sure of this), and therefore your RowCount, if cached, may not be
> reliable. This may be one of the problems the designers of ODBC had to
> take into account when making SQLRowCount() not return a count for
> SELECTs: Most modern RDBMSs are multiuser, and more than one user may be
> working at the same time in the same table(s); conflicts should be taken
> into account!
> Regarding gb.db.form:
> Wow... This may be over my head, but I think the approach I took is OK
> and row count is reliable, at least for the time the query was run. I'll
> try to see if I can make query_fill() fetch any row.
> Regards,

As soon as a query is issued, the query result cannot change while you 
look at it, even if it is returned row by row. Otherwise it would make 
SQL useless.

In other words, a query must be an instant view of part of a database. 
You won't see record deletions or insertions done after the query.

So if you emit a query, and move the cursor to the last row, the 
computed number of rows must be valid until you release the query result.


Benoît Minisini

More information about the Devel mailing list