[Gambas-user] SQL query for DB.Exec() question

[Willy Raets]

On Wed, 2013-08-28 at 22:28 +0200, Tobias Boege wrote:
> 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.

Tobi, thanks for the reply, but this was what I tried as very first
before adding the single quotes.

It returns a "Cannot execute request  Out of bounds" !!

> 
> Regards,
> Tobi

-- 
Kind regards,

Willy (aka gbWilly)

http://gambasshowcase.org/
http://howtogambas.org
http://gambos.org