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

[Randall Morgan]

Can you give us a dump of the table structure of all tables involved in the
query, perhaps from something like phpmyadmin or a similar tool?


On Wed, Aug 28, 2013 at 4:56 PM, Willy Raets <willy at ...2734...>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.IDDESC)
> > > > >
> > > > > 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.
>
> --
> 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
>



-- 
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?