[Gambas-user] Query for sorting not work

[gian]

Il 04/09/22 00:00, T Lee Davidson ha scritto:
> 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?
> 
> 

Hi List,

Thank you all for your responses, especially Benoit

'' Way now work well? ;-D
Public Sub GridView1_Sort()

   Dim aArg As String[] = ["uskey", "usnam", "ussur"]
   Dim sTable, sColumn, sSQL As String

   db.Debug = True
   sTable = "tuser"
   sColumn = aArg[$iCurrentColumn]
   sSQL = "SELECT * FROM " & DB.Quote(sTable, True) & " ORDER BY " & 
DB.Quote(sColumn, False)
   If GridView1.Columns.Ascending Then
     $hResult = $hConn.Exec(sSQL & " ASC")
   Else
     $hResult = $hConn.Exec(sSQL & " DESC")
   Endif
   GridView1.Rows.Count = $hResult.Count
   GridView1.Columns.Width = -1

Catch
   Print Error.Text

End

Regards

Gianluigi