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

[Benoît Minisini]

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 
result.

>
> 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.

Regards,

-- 
Benoît Minisini