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

ML d4t4full at ...176...
Fri Oct 16 15:39:38 CEST 2015

*On 2015-10-09 16:19, 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.
>>>> 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,

It took some time, but I think I have the real reason why the Wizard 
does not work: It returned to the red rows with error after getting an 
appropriate record count.

The problem seems to be that the SQL query is being add a 
non-SQL-standard clause, "LIMIT 256". This may have something to do with 
the row cache you mentioned.
Unfortunately, the LIMIT clause is a MySQL clause (don't know if other 
servers support it), it does not exist on standard SQL.
There are ways (read "hacks") to get a specific 'subrowset' from a given 
rowset, but they depend on SQL revisions; for example, MSSQL 2005 and 
2008 are the hardest, and is easier in MSSQL 2012.

There's actually no one-solution-fits-all way. I think the approach to 
retrieve a subset of rows from a query should be different, but I don't 
know how that would be feasible on gb.db.form.

So, in short, now ODBC returns a Result.Count if the server supports 
scrollable cursors (most RDMS do), but that does not help the Connection 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.gambas-basic.org/pipermail/devel/attachments/20151016/2430b148/attachment.html>

More information about the Devel mailing list