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

[Randall Morgan]

FYI

It is standard practice to never use spaces in MySQL table and field names.
Replace the spaces with underscores. Also, it's good to use only lower case
field names (especially if you access the database remotely. So your fields
should be:

product_id
initial_ic
actual_ic
 etc..

Using mixed case, special characters, or white space in a dbms is very poor
practice and always leads to trouble.....





On Wed, Aug 28, 2013 at 1:28 PM, Tobias Boege <taboege at ...626...> 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.
>
> Regards,
> Tobi
>
>
> ------------------------------------------------------------------------------
> 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
>



-- 
If you ask me if it can be done. The answer is YES, it can always be done.
The correct questions however are... What will it cost, and how long will
it take?