[Gambas-user] Query for sorting not work

[Benoit Minisini]

Le 03/09/2022 à 18:26, T Lee Davidson a écrit :
> On 9/3/22 08:26, gian via User wrote:
>> Hi List,
>>
>> Is anyone able to explain to me why the most correct query (I think) 
>> for sorting the columns is the one that is not working?
>> See attached, to see the malfunction you need to comment out the first 
>> sort routine and uncomment the second one.
>>
>> Thank you
>>
>> Gianluigi
> 
> It does not work because SQLite does not understand a single-quoted 
> column name. Data values should be single-quoted but not column names. I 
> think the code misuses the Connection.Find method. Try using the .Exec 
> method with the standard Subst function, eg.:
> 
>    If GridView1.Columns.Ascending Then
>      $hResult = $hConn.Exec(Subst("select * from tuser ORDER BY &1 ASC", 
> aArg[$iCurrentColumn]))
>    Else
>      $hResult = $hConn.Exec(Subst("select * from tuser ORDER BY &1 
> DESC", aArg[$iCurrentColumn]))
>    Endif
> 

Oops, don't do that.

The Exec() method substitutes using the Connection.Subst() method, that 
works for SQL values only.

For SQL column names, you must use the Connection.Quote() function. 
There is even a boolean argument for quoting table names, because on 
some SQLs, you don't quote table names and column names the same way!

<sarcasm>SQL... I love you!</sarcasm>

Regards,

-- 
Benoît Minisini.