Dear Fabien, I'm using Gambas 3.3.0. Under database manager you mean the connection manager? I just tried it out, see attached screenshot. The query doesn't return any results, just pops up a "OK".
All the best, M. On Thu, Oct 18, 2012 at 9:19 AM, Fabien Bodard <gambas...@gmail.com> wrote: > Have you try your sql request in the gambas3 IDE database manager? > Le 18 oct. 2012 02:54, "Markus Schatten" <markus.schat...@foi.hr> a écrit : > >> 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 >> > ------------------------------------------------------------------------------ > 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 -- 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
<<attachment: with_query.png>>
------------------------------------------------------------------------------ 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