[Gambas-user] Issue 612 in gambas: BUG Cannot browse more of 128 records on tables "No current connection"

[Benoît Minisini]

Le 28/03/2015 19:09, Benoît Minisini a écrit :
> Le 28/03/2015 18:58, Lewis Balentine a écrit :
>> I have over three decades of dealing with SQL and I can still not
>> understand how this myth got propagated.
>>
>> There is NO valid SQL reason why a table should be required to have a
>> unique primary key or any predefined key for that matter. One can always
>> use the row number (record number) if such a key is required for one's
>> application.
>>
>
> The row number does not exist in all database systems (more precisely,
> it is not accessible to the outside), so I can't rely on that feature to
> identify a row uniquely.
>
> Maybe the row id is not standard SQL, if "standard SQL" has any meaning.
>
> Consequently, I need a unique index, usually the primary key.
>
> If you can tell me how to get the row id of a row in MySQL, PostgreSQL
> and SQLite (mabe it has changed since the last time I looked at it), I
> will reconsider my position. :-)
>
> Regards,
>

More explanations...

I said "row number", I wanted to say "row id".

MySQL has no row id concept apparently. PostgreSQL has something like 
that, but apparently not useful. SQLite has.

The answer is always the same: you need a "row id"? Add a unique primary 
key based on a serial/auto-increment integer field.

Now as for as "row number".

I could use the "LIMIT / OFFSET" syntax to return the different part of 
a request, instead of using "LIMIT" + a criteria on the primary key.

Alas PostgreSQL (for example), tells us that two identical "LIMIT / 
OFFSET" on the same request does not necessarily return the same 
records, unless you specify an predictible "ORDER BY" clause.

Moreover "OFFSET" is not optimized on PostgreSQL.

So I don't see a better solution than the current one.

-- 
Benoît Minisini