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

[Benoît Minisini]

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 RDBM
> 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 Minisini