[Gambas-user] PRB:Connection.Exec()

[ron]

In the code I use:

  sql = "SELECT DISTINCT `kind` FROM `devices` WHERE `enabled`=true ORDER BY `kind` ASC"
  hResult = Global.Conn2.Exec(sql)


I got a error "Query failed: Unknown column 'true' in 'where clause'"

With or without the backticks did not solve the problem.
Single quote arround true ( `enabled`=true) gives no error and no records.
Double quote (`enabled`="true") 
 Got error 'Unexpected TRUE at line 101 in Form2.class', stupid me
must be `enabled`=\"true\" :(
No error and no records as expected.
 
The strange thing is MySQL get/find true as column name ?

I have used the database-manager to create a table
In the table is a field 'enabled' set as boolean.
As far I know boolean is 'true' or 'false', they are the 
only options I got in the database manager.

Using phpmyadmin I see it is stored as tinyint(1) and value of 0 or 1.

Now I remember why I stopped using 'Enabled = True', 
and changed over to 'Disbaled = False' or 'Enable = NOT False'.

Some use +1 as true and +0 as false and other 
use -1 (all bits set) as and 0(zero) (all bits reset) as false.

Resulting in False = 0(zero) and True is not 0 (zero) and so the 
only thing common to all systems for True/False is the value for False=0

WHERE `enabled` = True
WHERE `enabled` = NOT False
WHERE `enabled` = NOT(FALSE)
All are valid SQL but fails in gambas with unknown column 'True' or 'False'
The Connection.Exec("select * from table where cond") is not passing the query to MySQL.

So I did go reading the docs again and for Connection.Exec() it say's:

  FUNCTION Exec ( Request AS String, Arguments AS , ... ) AS Result
    where 
    Request is a SQL WHERE clause used for filtering the table. 
    Arguments are quoted as needed by the SQL syntax, and substituted inside the Request string


public sub mysub()
  DIM hResult AS Result
  DIM sql AS String
  DIM Conn2 as Connection
  dim hResult2 as Result

  Conn2 = NEW Connection
  SetConnection(Conn2)
  
  ' this works only if I use `enabled`=1
  'sql = "SELECT DISTINCT `kind` FROM `devices` WHERE `enabled`=1 ORDER BY `kind` ASC"
  'hResult = Global.Conn2.Exec(sql)

  ' but after reading the doc again I changed it to

  hResult = Conn2.Create("devices")
  hResult.Connection.Exec("enabled", "1") '<--  MySQL error 'near enable = 1'

mysql: 0x818bea8: show columns from `devices` like 'id'
mysql: 0x818bea8: show columns from `devices` like 'enabled'
mysql: 0x818bea8: show columns from `devices` like 'author'
mysql: 0x818bea8: show columns from `devices` like 'date'
mysql: 0x818bea8: show columns from `devices` like 'kind'
mysql: 0x818bea8: show columns from `devices` like 'device'
mysql: 0x818bea8: show columns from `devices` like 'description'
mysql: 0x818bea8: enabled = 1

And this should be as doc say ??? quoted or not for [Arguments AS,...]
Beside of that, I need only 1 field but the result set has all fields 
(and retrive all the data?) when I move from record to record.

Can you confirm the doc is wrong at
http://gambasdoc.org/help/comp/gb.db/connection/exec


Ron