[Gambas-user] PostgreSQL multiple column problem

Thu Oct 18 21:02:15 CEST 2012

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.fr at ...626...> wrote:
> Have you try your sql request in the gambas3 IDE database manager?
> Le 18 oct. 2012 02:54, "Markus Schatten" <markus.schatten at ...2005...> 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 <ihaywood at ...1979...>
>> 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 at 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 at 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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: with_query.png
Type: image/png
Size: 34894 bytes
Desc: not available
URL: <http://lists.gambas-basic.org/pipermail/user/attachments/20121018/c53626ea/attachment.png>