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

ML d4t4full at ...176...
Fri Oct 16 19:20:06 CEST 2015


*On 2015-10-16 13:35, Benoît Minisini wrote:*
> *Le 16/10/2015 18:26, Benoît Minisini a écrit :*
>> *Le 16/10/2015 15:39, ML a écrit :*
>>> Benoĩt,
>>> 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 Wizard.
>>> Regards,
>>> zxMarce.
>>
>> LIMIT exists in MySQL, PostgreSQL, and SQLite. With, of course, some 
>> differences in the syntax.
>> There is no such thing like real "standard SQL clause", or "standard 
>> SQL" in the real word. There is as many differences between two SQL 
>> database servers than between the HTML implementation of two web 
>> browsers!
>> SQL sucks. :-)
>> But I think that feature must be present in all SQL database systems.
>> The gb.db component has a standard way for specifying that you want 
>> to limit your result set:
>> DB.Limit(256).Exec(...)
>> That way, it's possible to adapt that feature to gb.db.odbc. But we 
>> must find a way for the ODBC driver to know how to specify the result 
>> limit.
>> Do you think it's possible with ODBC to make the difference, 
>> following that: 
>> https://en.wikipedia.org/wiki/Select_%28SQL%29#Result_limits ?
>> Regards,
>
> ...it should be possible if you can know with ODBC which database 
> server you connect to, and its version.
> Then you will be able to do the job ODBC should do for you 
> transparently if ODBC has been something useful and intelligently 
> designed. :-)
> If you can tell me how to detect that, I will modify gb.db and write 
> the big switch code to use the good "limit" syntax according to the 
> database server in use.
> Regards,

Benoît,

Well, I was speaking about SQL-92 and the like...

ODBC has a way to fetch a block of rows. You can set how many. Then, it 
will retrieve (up to) that many rows from the rowset.
You can, IIRC, also specify the starting offset. All this, of course, as 
long as the Driver or RDBM provides scrollable cursors.
Mind you, I never tried a row-block fetch, and it will be a demanding 
'challenge' to overcome. My guess is that it will take some time.

Please tell me how can gb.db.odbc know whether someone used the 
DB.Limit() property, so I can make the next fetch a row-block fetch, and 
how to recognize a "go back to retrieving 1 row at a time" mode change.
Related to that, one of the things that still escape me is which calls 
in gb.db map to which (block of) calls in gb.db.odbc. That, and which 
high level Gambas calls map to which gb.db (block of) calls.

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


More information about the Devel mailing list