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

[ML]

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