[Gambas-user] A Gambas-MySql question -> Query the result of a query and a table

[Willy Raets]

On Thu, 2014-01-16 at 00:45 +1030, Bruce wrote:
> Hi Willy,
> I have been thinking about this for the last day (amongst 20,000 other
> things) and I really don't have an easy answer. 
> 
> On Tue, 2014-01-14 at 10:20 +0100, Willy Raets wrote:
> > Hi all,
> > 
> > In MS Access it was possible to make a query based on a query. I am in
> > the middle of porting all MS Access clients to Gambas 3 and all tables
> > have been migrated to MySql.
> > 
> I do sort of remember this. We used to make a lot of use of it. But I
> can't remember how it was done. Could you post a simple example?

Do you mean a MS Access example of a query based on a query and some
tables?

> 
> > Is it possible in Gambas to do a query and next do another query on the
> > result of the previous query combined with some extra tables?
> 
> (Without the example, the answer is) Well not really, but there are a
> lot of options.
> > 
> > Or, do I need to do the query, store the result in a table and next
> > query this table combined with the extra tables.
> 
> No, you need not go this far!
> A Gambas "result" is not a "cursor" in the true sense of the word. The
> best way I can explain it is that it is sort of a live collection of
> rows. You can iterate through it using the short form of the For Each
> method, but you cant refer to it in another query as the dbms has no
> idea what you could be talking about.
> 
> There are a few options that leap to mind without remembering how this
> stuff was done, but they all involve using direct SQL calls rather than
> the clever stuff Benout has provided. Also be very aware that you will
> be writing dbms specific code and so lose the database "agnosticity"
> that normal use of gb.db provides. (And as you seem intent on using that
> dog of a dbms, MySQL, I can't really be entirely specific as to how to
> go about it.)
> 
> Generally, it works like this. The SQL standard requires a SELECT
> statement to refer in the FROM clause to either:
> - real tables
> - views
> - embedded queries.
> 
> So we can 
> 	SELECT (blah..)
> 	FROM aTable as A, aView as B, (SELECT Martians FROM
> 					somewhere WHERE etc) AS C
> 	WHERE A.Joe= b.Mary AND B.Sausages = C.Martians;
> (Note, I am cleverly avoiding JOINS here :-) )
> 
> This is standard (and I mean Standard!) SQL. 
> 
> But you can't do that using the normal Gambas db stuff, you have to do a
> Connection.Exec(query) call and that is 
> a) dbms specific and
> b) not updatable.

I am using Connection.Exec(query) often in the application as there are
so many specific things that need to be done (a lot of INNER, LEFT and
RIGHT JOINS) that the normal db stuff can't handle (I think).
That means a lot of SQL calls in Gambas code, so a bit harder to
maintain. But that is okay, as I made a separate file in the Gambas
project documenting where in the code I use what SQL calls, so I have
some overview of what is going on for maintenance purposed later on.

> 
> So to cut a long story short, consider this approach:
> 1) Convert your Access queries to views and store them in your MySQL
> (Ack, spit) database, but only store the Keying information
> 2) Use those views, which are "visible" in gambas as Tables
> 3) Use the view result as a list of keys in your gambas app to do real
> table updates.
> 
> We do this every day, let me check todays stats... pic attached
> hmm a pretty slow day really.

I'll have a look at how to make these views in MySql (so back to
research) and see what I can get working. The queries in question are
again quite extensive and with all kinds of JOINS.

Thanks for sharing your suggestions and insight.
> 
> 
> > 
> > I know this last option would work, but I am just wondering if there are
> > any better ways of doing this, without having to create a table with the
> > query result.
> > 
> > Suggestions are appreciated.
> > 
> 
> hth
> Bruce

-- 
Kind regards,

Willy (aka gbWilly)

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