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

[nigel at ...38...]

Ron,
I believe that the documentation is incorrect and that the request should just be a standard SQL statement, the only different thing is that you can parameterize some of the fields and then pass them as arguments. So your first attempt is the correct syntax. Have you tried where enabled = `true`.

Setting DB.Debug = True before the exec statement may give you information on what is being passed to the database.

Regards

Nigel

> Message Received: Jun 03 2006, 07:36 AM
> From: "ron" <ronstk at ...239...>
> To: gambas-user at lists.sourceforge.net
> Cc: 
> Subject: [Gambas-user] PRB:Connection.Exec()
> 
> 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
> 
> 
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
> 
>