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

[Bruce]

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?

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

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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: Selection_030.png
Type: image/png
Size: 10356 bytes
Desc: not available
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20140116/1c0c54a8/attachment.png>