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

[nando]

I see spaces in the field 'Initial IC' and 'Actual IC'.
If you're using a space in the field name - DONT!!!
Use _ underscore instead. 
Don't use a spaces in any names.
You complicate life with them and make you want to pull your hair out.

This should work

myString = "SELECT IC.ID, Product.Description "
myString.= "FROM IC, Product "
myString.= "WHERE IC.ProductID = Product.ProductID "
myString.= "ORDER BY IC.ID DESC" 
rData = ConMyData.Exec(myString)


This is not correct:
rData = ConMyData.Exec("SELECT &1 FROM IC", "'Actueel IC'")

Should be:
rData = ConMyData.Exec("SELECT `Actual IC` FROM IC")

Again, PLEASE don't use spaces embedded in the field.



---------- Original Message -----------
From: Willy Raets <willy at ...2734...>
To: mailing list for gambas users <gambas-user at lists.sourceforge.net>
Sent: Wed, 28 Aug 2013 22:18:52 +0200
Subject: [Gambas-user] SQL query for DB.Exec() question

> 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
> 
> ------------------------------------------------------------------------------
> Learn the latest--Visual Studio 2012, SharePoint 2013, SQL 2012, more!
> Discover the easy way to master current and previous Microsoft technologies
> and advance your career. Get an incredible 1,500+ hours of step-by-step
> tutorial videos with LearnDevNow. Subscribe today and save!
> http://pubads.g.doubleclick.net/gampad/clk?id=58040911&iu=/4140/ostg.clktrk
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
------- End of Original Message -------