[Gambas-user] Query for sorting not work

[T Lee Davidson]

On 9/3/22 13:19, Benoit Minisini wrote:
> 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.

Thank you for the clarification, Benoît.

The Find() method also obviously substitutes using the Connection.Subst() method which is why I suggested using DB.Exec() with 
the standard Subst() function, which does no quoting, thereby preventing the automatic use of Connection.Subst().


> 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!

Hmmm, so, a better query example would be:

$hResult = $hConn.Exec("select * from tuser ORDER BY & " $hConn.Quote(aArg[$iCurrentColumn]) & " ASC")

Correct?


-- 
Lee