[Gambas-devel] gb.odbc probably won't work correctly

[Benoit Minisini]

On Thursday 13 October 2005 21:24, danielcampos at ...45... wrote:
> (Gambas-1.9.20 / gb.db / gb.db.odbc / Informix)
>
> Hi:
>
> I've been testing gb.db.odbc with Informix, and it does not work. The
> problem is that gb.db tries to get the row count of a "SELECT" query, and
> that value is not reported by the major part of ODBC drivers (well, MySQL
> does, but it has not very much sense having a native driver). For example
> you can look at the IB M documentation about ODBC:
>
> http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com
>.ibm.db2.doc.odbc/bjnemstr111.htm
>
>
> ...or the Perl-ODBC documentation (I have not the link now,sorry)
>
> The major part of ODBC drivers report the row count only for INSERT, UPDATE
> or DELETE queries, and return -1 for SELECT queries, when using the
> SQLRowCount() method.
>
> I've made a hack for me, that repeats twice the query, the first one to
> count the number or rows, and the second one to fill the data. It works OK,
> but I remember an old mail from Benoit saying that this is "horrible",
> however is the only thing I can do from the driver currently. It seems
> there's a commented code in the gb.db part about this, however it is
> disabled...

At the moment, the behaviour of gb.db is the following:
- If the query_init() driver function returns a row count >= 0, then this 
value is used for testing the row index when extracting a row.
- If query_init() returns a count of -1, then no test is done, and the 
query_fill() driver function can get any value in its pos argument.

This behaviour is not a problem, if there wasn't the second one you describe 
after (that I wasn't aware of)...

>
> The second porblem is that "FetchScroll()" method does not work for
> Informix, and it seems it will not work for DB2, according to IBM
> documentation, that says nothing about that method, so, in my hack, I've
> replaced it by "Fetch()"...

FetchScroll() exists from ODBC 3.0. ODBC seems to be as well designed as the 
SQL pseudo-standard... :-(

It seems that Andrea replaced the use of Fetch() by FetchScroll(). I don't 
know why, if he could explain us?

Anyway, if there is no way to move to any row in the result set, and if there 
is no way to rewind the recordset to its beginning, then the driver must 
raise an error.

What to do?

1) Find a function to get the ODBC version implemented by the ODBC driver.

2) If ODBC >= 3.0, then use FetchScroll(). Otherwise raise an error when this 
function must be called.

>
> Once I stabilize my hacks trying to make it work correctly, I will release
> it as an "odbc2" or something like that unnoficial driver, as I need these
> features for Informix, and may be It will help other developers.

If we need one ODBC gambas driver for each database system, then ODBC is 
useless!

I suggest you make a gb.db.odbc.informix driver by copying the gb.db.odbc 
source code first, if you need it.

But it will be cool if you can do in gb.db.odbc what I described just before: 
getting ODBC version implemented by the underlying driver, and raise an error 
if FetchScroll() is impossible.

>
> However, I think something more  clean should be done with that, may be a
> complete component for ODBC with a different interface, or modify the
> current gb.db component so it can handle drivers having not row counting

gb.db is not as cleanly designed as I'd like, but it actually handle drivers 
having not row counting. It is just that Andrea didn't imagine that 
FetchScroll() could be not implemented in some ODBC drivers!

> and a forward-only cursor (I think MoveFirst, MovePrevious, etc won't work
> correctly with the current interface). 

Maybe, for performance reasons. But raising an error when something is 
impossible can be sufficient now.

> It is important to support DB2 and 
> Informix!

And it will be cool if they could do correct ODBC drivers for these 
databases :-) I'm very surprised that there is no support for FetchScroll() 
in ODBC drivers of big databases...

Anyway, I continue thinking that:
- ODBC is a pain.
- Direct drivers are better. After all, almost all databases are doing about 
the same things, and SQL is absolutely not standard.
- ODBTP is a good and more efficient way to use ODBC from a Linux box: 
http://odbtp.sourceforge.net

Regards,

-- 
Benoit Minisini