[Gambas-user] SQL query for DB.Exec() question
Tobias Boege
taboege at ...626...
Wed Aug 28 22:28:13 CEST 2013
On Wed, 28 Aug 2013, Willy Raets wrote:
> Hello all,
>
> I've been searching the Gambas mailing list archives and documentation
> for a answer to a problem I have. Tried all kinds of suggestions but
> haven't solved the problem yet.
>
> This is the situation simplified:
>
> A MySql database named MyData with a two tables named IC and Products.
> Both tables have a field ProductID with a relation inbetween.
>
> Table IC has fields
> ID -> Primary Key, Indexed
> ProductID -> Indexed
> Initial IC
> Actual IC
> ValueInitial
> ValueActual
>
>
> Table Product has fields:
> ProductID -> Primary Key, Indexed
> Description
>
> In a query I need to retrieve all fields in IC except ProductID and
> Description in Products
> I manage to get the query done except for the fields Initial IC and
> Actual IC (because of the blanc in the field name).
>
> As I understand in MySql I need to query:
> SELECT 'Actual IC' FROM IC to get Actual IC
>
> I have tried it like this in Gambas (rData being a Result and ConMyData
> being the connection)
>
> rData = ConMyData.Exec("SELECT &1 FROM IC", "'Actueel IC'")
>
> But the syntax is not accepted by MySql
>
>
> To make the matter more complicated when querying more than one table
> you need to add the table to the query syntax like this
> SELECT IC.ID, Product.Description FROM IC LEFT JOIN Product ON
> IC.ProductID=Product.ProductID ORDER BY IC.ID DESC
>
> Or in Gambas:
>
> rData = ConMyData.Exec(SELECT IC.ID, Product.Description FROM IC LEFT
> JOIN Product ON IC.ProductID=Product.ProductID ORDER BY IC.ID DESC)
>
> This works perfect, but when I want to add field IC.Actual IC to that
> query in a db.Exec() what is the proper format?
> I tried with:
> IC.'Actual IC'
> 'IC'.'Actual IC'
> 'IC.Actual IC'
> They all give back syntax errors from MySql
>
> Any suggestions welcome
>
Out of pure intuition I would leave the single quotes out in the string you
give to Exec(), i.e. write
rData = ConMyData.Exec("SELECT &1 FROM IC", "Actueel IC")
The Connection class should do the proper quoting itself.
Regards,
Tobi
More information about the User
mailing list