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

[Willy Raets]

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

-- 
Kind regards,

Willy (aka gbWilly)

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