[Gambas-user] Query for sorting not work

[Benoit Minisini]

Le 04/09/2022 à 17:06, gian via User a écrit :
> 
> Hi Benoit,
> 
> I get it, you thought "why don't you try it and stop bothering".
> 
> I tried it and it works with the three databases I'm interested in (to 
> be honest I'm only interested in SQLite and PostgreSQL but I tried MySQL 
> as well), as you can see from the console response:
> 
> The Database is open = True
> 2022-09-04 15:59:40.284 gb.db.sqlite3: 0x55d0b05a6088: SELECT * FROM 
> "tuser" ORDER BY "uskey" DESC
> 2022-09-04 15:59:41.403 gb.db.sqlite3: 0x55d0b05a6088: SELECT * FROM 
> "tuser" ORDER BY "uskey" ASC
> The Database is open = False
> 
> The Database is open = True
> 2022-09-04 16:34:30.268 gb.db.postgresql: 0x5636531c4220: SELECT * FROM 
> "tuser" ORDER BY "usnam" ASC
> 2022-09-04 16:34:31.341 gb.db.postgresql: 0x5636531c4220: SELECT * FROM 
> "tuser" ORDER BY "usnam" DESC
> The Database is open = False
> 
> The Database is open = True
> 2022-09-04 15:56:03.732 gb.db.mysql: 0x55aa5d13fe20: SELECT * FROM 
> `tuser` ORDER BY `ussur` ASC
> 2022-09-04 15:56:04.787 gb.db.mysql: 0x55aa5d13fe20: SELECT * FROM 
> `tuser` ORDER BY `ussur` DESC
> The Database is open = False
> 
> @Lee,
> 
> what do you think, is it possible to complete the Sorted wiki with this 
> new knowledge?
> 
> :-)
> 
> Regards
> Gianluigi
> 

Yes, the problem of quoting should have been explain explicitely in the 
methods that build SQL requests.

I'm currently thinking, maybe I could add new substitution syntaxes to 
tell Gambas if we want to substitute a value, a column name or a table name.

Something like that:

DB.Exec("SELECT * FROM [&1] WHERE column = &2 SORT BY '&3'", sTableName, 
sValue, sSortColumn)

Because I don't think Gambas will be clever enough to guess which 
substitution is a value, a table or a column only by analyzing the 
syntax. SQL syntax is too complex.

Regards,

-- 
Benoît Minisini.