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

[Benoît Minisini]

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 Minisini