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

[Willy Raets]

On Thu, 2013-08-29 at 11:39 +0930, Bruce wrote:
> On Thu, 2013-08-29 at 01:56 +0200, Willy Raets wrote:
> > On Thu, 2013-08-29 at 09:17 +0930, Bruce wrote:
> > > On Thu, 2013-08-29 at 01:25 +0200, Willy Raets wrote:
> > > > 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
> > > > 
> > > 
> > > Another untested suggestion:
> > > 
> > > rData = ConMyData.Exec("SELECT &1 FROM IC", DB.Quote("Actueel IC"))
> > > 
> > > hth
> > > Bruce
> > 
> > Hi Bruce,
> > 
> > It returns a "Cannot execute request  Out of bounds" as well :(
> > 
> > But thank for thinking along.
> > \
> 
> OK Willy, I have some info.
> 
> 1) The identifer quote char for mysql is ` i.e. a "back tick".
> 
> 2) What happens with something like the following:
> 
>           Dim Conn As New Connection
>           Dim hRlt As Result
>           
>           With Conn
>             .Type = "mysql"
>             .Host = "localhost"
>             .Name = "test"
>             .User = "root"
>           End With
>           If Not Conn.Opened Then Conn.Open
>           
>           Print Conn.Quote("Spaced Name")
>           Print Conn.Subst("WHERE &1=&2", Conn.Quote("Spaced Name"),
>         "Some data")
>           Print
>           
>           hRlt = Conn.Find("testdata", "`Spaced Name` Like \"test%\"")
>           For Each hRlt
>             Print hRlt!id, hRlt!name, hRlt!"Spaced Name"
>           Next
>           Print
> 
> here, Print Conn.Quote("Spaced Name") gives `Spaced Name`
> BUT Print Conn.Subst("WHERE &1=&2", Conn.Quote("Spaced Name"), "Some
> data") gives WHERE '`Spaced Name`'='Some data'
> Note the name is enclosed in ' chars.
> 
> Also, the query hRlt = Conn.Find("testdata", "`Spaced Name` Like \"test%
> \"") runs properly and returns the data for all columns including
> `Spaced Name`.
> 
> But note the way the field has to be used in the Print statement!
> 
> I believe Connection.Find and Connection.Exec use the Subst internal
> code. So, it is incorrectly wrapping the column name in those extra 's.
> 
> hth
> Bruce

Bruce,

That back tick is what did the trick....
I now manage to get all the field names with spaces.

Thanks to all others posting suggestions as well.

-- 
Kind regards,

Willy (aka gbWilly)

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