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

[ML]

On 10/09/2015 10:31 PM, Benoît Minisini wrote:
> 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,

So, long story short, we both agree.

Neither do I know why the function name (SQLRowsChanged would have been
more useful)! All I know is -for example- *this
<http://www-01.ibm.com/support/knowledgecenter/SSEPEK_11.0.0/com.ibm.db2z11.doc.odbc/src/tpc/db2z_fnrowcount.dita>*
(IBM/DB2) and *this* <http://odbc.org/support/kb/kb00939.html> (Easysoft
ODBC). There are others (like Microsoft), but they say the same.
Easysoft helps more by saying "Most ODBC drivers only return a
meaningful number from SQLRowCount when you execute row changing SQL
e.g. updates, deletes, inserts. For selects most ODBC drivers return -1".

I also was lost and confused by the function call name when I saw it in
the Gambas driver, but I remembered the experience from MSADO in VB6.
Under MSADO, row counts were mostly -1. Only forward-only static cursors
made VB's Recordsets (Gambas' Results) return a meaningful count.

Lastly, I think I did not make myself clear regarding concurrent
changes. I wanted to say that if somebody else adds a row, you will not
see it (so, we DO agree), but at the same time, strictly speaking, your
row count will be one less than the current, absolute, row count in the
database.
On the other hand, I don't know what would happen if you seek to a row
that has been deleted. Either an informative message or error, I presume.

Regards,
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/devel/attachments/20151010/4d58bb93/attachment.html>