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

[ML]

*On 10/09/2015 04:19 PM, Benoît Minisini wrote:*
> *Le 09/10/2015 20:50, ML a écrit : *
>> *On 2015-10-05 13:30, Benoît Minisini wrote: *
>>> *Le 05/10/2015 17:25, zxMarce a écrit : *
>>>> Benoît,
>>>> Still working on this, sorry it took so long; real-life got in the way.
>>>> As stated, it works with data-retrieving loops. But the IDE's
>>>> Connection Wizard and at least two data-bound controls (the only
>>>> ones I tried) fail when filling in their data.
>>>> Here's a screen shot of a quick-test I made:
>>>> <http://gambas.8142.n7.nabble.com/file/n53761/ODBC_%28MSSql%29_and_MySQL_data-bound_controls.png>
>>>> Just if you cannot see the image or if it fails to upload, the ODBC
>>>> databound control shows "DataView.TableView_Data.420: Null object"
>>>> in every field of every row.
>>>> I had two connections made in the IDE. One uses ODBC against a
>>>> MSSQL server, the other uses MySQL against a DB I created on localhost.
>>>> Both have the same table definition, but I limited MySQL to 2
>>>> records instead of the 57 MSSQL had (it's even stranger that the
>>>> ODBC-bound controls "knew" the Result had 57 records, but failed to
>>>> show any data at all).
>>>> As you can see, the data-bound control gets the error
>>>> "DataView.TableView_Data.420: Null object", while MySQL works as
>>>> expected.
>>>> I still have to try the MySQL-ODBC connector, but I pre-assume it
>>>> will fail the same way it does with MSSQL.
>>>> Hope this helps make data-bound controls work. As usual, if I can
>>>> help in any way, just say the word. I'll be beautifying ODBC's
>>>> main.c module next.
>>>> TIA,
>>>> zxMarce. 
>>>
>>> gb.db.form controls assume that the Result can be seeked at any index.
>>> They can't deal with move-forward Result, and I guess the error
>>> comes from that.
>>
>> Benoît,
>> I like to think of this as a challenge. And I'll tell you why: If
>> Gambas supports ODBC, then -ideally- there would no more specialized
>> RDBMS interfaces to develop as long as the one you want to connect to
>> supports ODBC!
>> That said, I developed what you would call a hack to get a query's
>> resulting RowCount. It took me some time, research, and now I'm
>> bleeding C...
>>
>> I replaced the call to SQLRowCount with a call to a routine that:
>>
>>    1- Makes sure the result has an associated cursor.
>>    2- Tells ODBC not to retrieve data (lightning faster on big result
>> sets)
>>    3- Remembers the "current row index" (i.e: Which is the 'current
>> data row')
>>    4- Makes the cursor jump to the last row and fetches its RowIndex
>>    5- Jumps the cursor back to the remembered 'current row'
>>    6- Tells ODBC to retrieve data again
>>    7- Returns the RowIndex from step 4
>>    8- If for any reason this cannot be done, the routine returns -1
>> as RowCount, effectively mimicking SQLRowCount()
>>
>> Not tested it thoroughly, but I noticed 2 good things:
>>
>>    1- The red NULL OBJECT errors from the Database Conn Wizard
>> dissappeared, and there were as many rows as needed in its grid.
>> There's no data shown (cells are empty), but the columns and their
>> titles were there.
>>    2- My ODBC test program works as before, no changes noticed, and
>> no errors.
>>
>> Please tell me your thoughts. I'm still researching ODBC to see if I
>> can make it less problematic.
>> Regards,
>> zxMarce.
> The problem is that I don't know what ODBC really does.
> Other database backends get the entire result contents in memory, or
> act as if. This is the reason why Gambas database drivers can get the
> row count and jump to any row from its index without problem.
> Apparently ODBC does not load the entire result in memory (you get
> records one by one), and this has to be simulated somewhere: at the
> Gambas database driver level, at the gb.db component level, or in
> gb.db.form.
> It's what you have done to get the row count. (I don't understand why
> SQLRowCount() does not already do that).
> I guess you compute the row count once and cache it in the
> query_init() function?
> Now you have to make query_fill() return the row data for any index,
> if you want gb.db.form to work out of the box.
> But I don't think it is possible in all cases - maybe some ODBC driver
> are really forward-only.
> In that case, it's the gb.db.form component that must be modified (I
> don't want gb.db to simulate that, because it may be interesting in
> some cases to have forward-only result, it is faster and use less memory).
> As gb.db.form accesses tables by chunk of 256 rows (see the source
> code of DataTable in gb.db.form), maybe he could maintain a cache for
> the 256 rows contents as soon as he knows that the Result is forward-only.
> He would know that for sure if the Result count is -1. So you should
> compute the row count in the ODBC database driver only if you are sure
> that you can access a result row randomly.
> Regards,
Benoît,

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

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,
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/devel/attachments/20151009/20382437/attachment.html>