Dear Fabien and Ian, thank you for your reply. I was kind of hoping that there is a way to index the results. I started of to write a query rewriter, but got soon into trouble with lots of possible ways of writing a query. I would probably need a grammar parser to do that ;-)
Anyways, after looking up the SELECT syntax, I found another way. I wasn't aware of the WITH clause in a select statement which allows you (at least in PostgreSQL) to create an alias for a subquery with specified column names. I used this to create a rewrite. The idea is to construct a query like: WITH query(a0, a1) AS (SELECT * FROM test t1, test t2) SELECT * FROM query which replaces the initial column names with a0 and a1. Afterwards I put the original column names into the TableView header (datasource + dataview doesn't work since the table attribute doesn't seem to accept WITH queries - is there a reason for that, or is that a bug?). Here is the solution if anyone might need it: Public con As New Connection Public original As New String[] Public Sub _new() With con .Type = "postgresql" .Name = "db" .User = "markus" .Password = "secret" .Open End With execute(rewrite_query("SELECT * FROM test t1, test t2")) End Public Sub execute(query As String) ' execute the query and show the results Dim res As Result Dim rfield As ResultField Dim col_counter, row_counter, i As Integer Dim context As New Collection res = con.Exec(query) TableView1.Rows.Count = res.Count TableView1.Columns.Count = res.Fields.Count For i = 0 To original.Max TableView1.Columns[i].Title = original[i] Next row_counter = 0 For Each res col_counter = 0 context["res"] = res TableView1.Row = row_counter For Each rfield In res.Fields TableView1.Column = col_counter TableView1.Current.Text = Eval("res!" & rfield.Name, context) col_counter += 1 Next row_counter += 1 Next End Public Function find_all(subj As String, pattern As String, Optional submatchindex As Integer = 0) As String[] ' findall matches of a given regex in a given string, return only submatches if submatchindex is specified Dim re As Regexp Dim matches As New String[] re = New Regexp(subj, pattern) Do While re.offset >= 0 And subj <> "" If submatchindex = 0 Then matches.push(re.Text) Else matches.push(re.SubMatches[submatchindex].Text) End If If Len(subj) > Len(re.text) Then subj = Mid(subj, re.offset + Len(re.text) + 1) Else subj = "" End If If subj <> "" Then re.exec(subj) Loop Return matches End Public Function rewrite_query(query As String) As String ' rewrite the query to return distinct column names Dim has_asterisk As String[] Dim defined_limit As String[] Dim replace_limit As String Dim query_copy As String Dim res As Result Dim rfield As ResultField Dim counter As Collection Dim with_string As String Dim i As Integer has_asterisk = find_all(query, "(?i)select (.*?[\\*].*?) from", 1) If has_asterisk.Count > 0 Then query_copy = query defined_limit = find_all(query, "(?i)(limit +[0-9]+)", 1) If defined_limit.Count > 0 Then For Each replace_limit In defined_limit query = Replace(query, replace_limit, "LIMIT 1") Next Else query &= " LIMIT 1" Endif res = con.Exec(query) For Each res counter = New Collection For Each rfield In res.Fields original.Push(rfield.name) Next Next with_string = "WITH query(" For i = 0 To original.Max with_string &= "a" & Str(i) & ", " Next with_string = Left$(with_string, -2) & ") AS (" & query_copy & ") SELECT * FROM query" Print with_string Return with_string Else Return query Endif End On Wed, Oct 17, 2012 at 10:33 AM, Ian Haywood <ihayw...@iinet.net.au> wrote: > No, I think its your best solution > Maybe your users can just type in the "where" part of the query and > then you add the rest? The app is a database development environment, so users have to enter the whole query. I will try to find solutions for other dbms' (I'm planning to support SQLite and MySQL as well) so if anyone has another idea on how to approach the matter, I'll be happy to see a solution. All the best, M. -- Markus Schatten, PhD Assistant professor University of Zagreb Faculty of Organization and Informatics Pavlinska 2, 42000 Varazdin, Croatia http://www.foi.hr/nastavnici/schatten.markus/index.html http://www.researchgate.net/profile/Markus_Schatten1 ------------------------------------------------------------------------------ Everyone hates slow websites. So do we. Make your web apps faster with AppDynamics Download AppDynamics Lite for free today: http://p.sf.net/sfu/appdyn_sfd2d_oct _______________________________________________ Gambas-user mailing list Gambas-user@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/gambas-user