How to format a DB query result in textarea

  1. 4 months ago

    changwon l

    Aug 8 Pre-Release Testers

    I am trying to run a query from Oracle and write the query result into a TextArea.
    As you might know, normally we see formatted query result in SQL*Plus as following.

    OWNER			       TABLE_NAME			PCT_FREE  INI_TRANS
    ------------------------------ ------------------------------ ---------- ----------
    SYS			       ICOL$				       0	  0
    SYS			       CON$				      10	  1
    SYS			       UNDO$				      10	  1
    SYS			       PROXY_ROLE_DATA$ 		      10	  1
    SYS			       FILE$				      10	  1
    

    However, I can't find a proper way to format the query result in Textarea, so tried with the below code.
    The final result doesn't look good as above.

        r=Oracle_SRCConnectionGlobal.MySelectSQL(sqlstatement)
        m=r.getMetaData
        ColumnCount = m.getColumnCount
        If r <> Nil then
          While r.NextRecord
            
            If Not IsHeadingChecked Then
              For i As Integer = 1 To ColumnCount
                RedefArea.AppendText m.getColumnName(i) + chr(9)
              Next
              IsHeadingChecked=True
            End If
            
            For i As Integer = 1 To ColumnCount
              tempGetString = r.getString(m.getColumnName(i))
              RedefArea.AppendText tempGetString + chr(9)
            Next
            
            RedefArea.AppendText EndOfLine
            
          Wend
        End If

    Do you have any idea to format the query result in Textarea?

  2. Jean-Yves P

    Aug 8 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    put the result in a listbox, it will be perfectly aligned.
    otherwise if you still want to use a textarea, use a fixed width font like courier or monaco

  3. Edwin v

    Aug 9 Pre-Release Testers, Xojo Pro The Netherlands

    @Jean-YvesPochez use a fixed width font like courier or monaco

    If you're on a mac, you can use the Fontbook application to see what fixed width fonts you have available on your system.
    On the left, there is a smart collection named "Fixed Width".

  4. Dave S

    Aug 9 San Diego, California USA
    Edited 4 months ago

    If you want to get a list at run-time here is what I use

    Public Sub getFixedFonts()
      Dim i As Integer
      Dim fnt As String
      Dim fontlist() As String
      Dim p as new picture(10,10)
      dim g as graphics=p.Graphics
      g.TextSize=100
      
      For i=0 To FontCount-1
        fnt=Font(i)
        g.textfont=Fnt
        if g.stringWidth("l")=g.stringWidth("W") Then fontlist.append fnt
      Next i
      
      fontlist.sort
    End Sub

    If I am just picking one specific Fixed Font I use

    #If TargetWindows Then 
        Return "LUCIDA CONSOLE"
    #Else
        Return "MENLO"
    #EndIf
  5. Edwin v

    Aug 9 Pre-Release Testers, Xojo Pro The Netherlands

    @Dave S If you want to get a list at run-time here is what I use

    Public Sub getFixedFonts()
      Dim i As Integer
      Dim fnt As String
      Dim fontlist() As String
      Dim p as new picture(10,10)
      dim g as graphics=p.Graphics
      g.TextSize=100
      
      For i=0 To FontCount-1
        fnt=Font(i)
        g.textfont=Fnt
        if g.stringWidth("l")=g.stringWidth("W") Then fontlist.append fnt
      Next i
      
      fontlist.sort
    End Sub

    If I am just picking one specific Fixed Font I use
    #If TargetWindows Then Return "LUCIDA CONSOLE" #Else Return "MENLO" #EndIf

    Dave, I remember I did something like this a long time ago. I kinda forgot about it. the difference is that I used several characters to test the width, and I set the TextSize to 50, or so. Just to be entirely sure that I found differences between the characters.

    Thanks for pointing it out and reminding me. I will include it in my own "Toolbox"
    (the module I called modToolbox, that contains a lot of methods I can reuse. I add them to almost all my projects as an external module.)

  6. changwon l

    Aug 10 Pre-Release Testers

    @ Jean-Yves Pochez

    I already used a 'Courier New' font. It is true that with that font we can expect a aligned format.
    However, thing is that there are several SQL queries in my requirement and the number of columns are different so the idea using a listbox is not proper to this case I guess.

  7. Edwin v

    Aug 10 Pre-Release Testers, Xojo Pro The Netherlands
    Edited 4 months ago

    @changwon l I already used a 'Courier New' font. It is true that with that font we can expect a aligned format.

    And what if you check the longest value of a column. I think you can do that with a simple SQL statement:

    SELECT MAX( LENGTH( myColumn ) ) FROM myTable

    Next, you go through the recordset and take the value from the column, and pad it with spaces, to make the value as long as the longest value. That should make sure you have the values properly aligned, right?

    For multiple columns, you simply change the query:

    SELECT MAX( LENGTH( myColumn1 ) ), MAX( LENGTH( myColumn2 ) ), MAX( LENGTH( myColumn3 ) ) FROM myTable

    The values can be retreived through:

    colSize1 = rs.idxField(1).integerValue
    colSize2 = rs.idxField(2).integerValue
    colSize3 = rs.idxField(3).integerValue

    I haven't tested the queries. So sorry for any typos :)

    And of course, you should make a nice reusable function for this.

  8. Edwin v

    Aug 10 Pre-Release Testers, Xojo Pro The Netherlands
    Edited 4 months ago

    @changwon l the number of columns are different so the idea using a listbox is not proper to this case I guess.

    A listbox can work, though. It will give you better alignment. And the number of columns are not going to be a problem.
    You just have to change the ColumnCount of your listbox, to match the number of columns in your recordset.

    Something like this might do the job:

    // Clear the listbox
    ListBox1.DeleteAllRows
    
    // Match the listbox columncount to the number of columns in the recordset
    Listbox1.ColumnCount = rs.FieldCount
    ListBox.HasHeading = True ' (show headers)
    
    // Change the Column Headers
    Dim c as Integer
    For c = 0 to rs.FieldCount - 1
      ListBox1.Heading( c ) = rs.idxField( c + 1 ).name
    Next
    
    // populate the listbox with the records of your recordset
    Dim Row as Integer
    While not rs.EOF
      ListBox1.AddRow rs.idxField( c + 1 ).StringValue
      Row = ListBox1.LastIndex
      For c = 1 to rs.FieldCount - 1
        ListBox1.Cell( Row, c ) = rs.idxField( c + 1 ).StringValue
      Next
      rs.MoveNext
    Wend
    rs.Close

    Again, sorry for any typos

  9. Edwin v

    Aug 10 Pre-Release Testers, Xojo Pro The Netherlands
    Edited 4 months ago

    A long time ago I wrote a little convenience function that does just that.
    How it works...

    • Just add this function to a public (global) module.
    • ListBox1.AddRecordsetRows rs

    Additionally, you can add a PrimaryKey field. the value of the record's primary key will be used in the RowTag of the listbox. This way you have an easy reference to the record in case you need to.

    Public Sub AddRecordsetRows(extends lb as Listbox, rs as RecordSet, PrimaryKey as String = "", ClearListBox as Boolean = True)
      if lb = nil then Return
      
      if ClearListBox then lb.DeleteAllRows
      
      if rs = nil then Return
      
      lb.ColumnCount = rs.FieldCount
      
      rs.MoveFirst
      dim row, col as Integer
      while not rs.EOF
        
        lb.AddRow ""
        row = lb.LastIndex
        
        for col = 0 to rs.FieldCount-1
          
          // Heading
          if row = 0 and lb.HasHeading then
            lb.Heading( col ) = rs.IdxField( col + 1).Name
          end if
          
          // Record Data
          lb.cell( row, col ) = rs.IdxField( col + 1 ).StringValue
          
          // Primary Key
          if rs.IdxField( col + 1 ).Name.Lowercase.Trim = PrimaryKey.Lowercase.Trim and PrimaryKey.Trim <> "" then
            lb.RowTag( row ) = rs.IdxField( col + 1 ).Value
            ' lb.CellHelpTag( row, 0 ) = rs.IdxField( col + 1 ).value
          end if
          
        next
        
        rs.MoveNext
      wend
      
    End Sub
  10. Jean-Yves P

    Aug 10 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    @changwon l However, thing is that there are several SQL queries in my requirement and the number of columns are different so the idea using a listbox is not proper to this case I guess.

    just set the listbox.columncount=rs.fieldcount

  11. Edwin v

    Aug 10 Pre-Release Testers, Xojo Pro The Netherlands

    @Jean-YvesPochez just set the listbox.columncount=rs.fieldcount

    Yep, that was my suggestion, too :)

  12. Dave S

    Aug 10 San Diego, California USA

    another suggestion.... use HTMLVIEWER
    and write it to a HTML table.... let HTML do all the work

  13. Edwin v

    Aug 10 Pre-Release Testers, Xojo Pro The Netherlands
    Edited 4 months ago

    @Dave S another suggestion.... use HTMLVIEWER

    That would work, too. But only when you want to view the data. When you want to select a record, or in case you want some other form of interaction, the HTML viewer won't be an option, I think.
    The Listbox (or even a TextArea) allows for more user interaction.

  14. changwon l

    Aug 11 Pre-Release Testers

    Thanks for the great suggestion.
    I will update here after implementing ListBox version.

  15. changwon l

    Aug 11 Pre-Release Testers

    By the way,
    There are 5 SQL statements and its number of columns are different, and I should put together with all results into one Listbox (or texture). I think it is not possible.

    Anyway, I will try.

  16. Jean-Yves P

    Aug 11 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    again, you can change the number of columns of a listbox during runtime.
    I set the datas from different databases (local and remote), from different tables and columns
    at runtime, and it is in the SAME listbox.
    so this is definitely possible.

  17. Dave S

    Aug 11 San Diego, California USA

    this has NOT been tested...... so it may have a few errors....(off the top of my head)

    Private Sub RecordSetToListBox(lb as listbox,rs as RecordSet)
      Dim i As Integer
      Dim s As String
      Dim cw(-1) As Integer
      Dim p As New picture(10,10)
      Dim g As graphics
      g=p.Graphics
      g.TextFont=lb.TextFont
      g.TextSize=lb.TextSize
      lb.DeleteAllRows
      lb.ColumnCount=rs.FieldCount
      Redim cw(rs.fieldcount)
      For i=1 To rs.FieldCount
        s=rs.IdxField(i).Name
        cw(i)=g.StringWidth(s)
        lb.Heading(i)=s
      Next i
      While Not rs.eof
        lb.addrow ""
        For i=1 To rs.FieldCount
          s=rs.IdxField(i).StringValue
          cw(i)=Max(cw(i),g.StringWidth(s))
          lb.cell(lb.listcount-1,i-1)=s
        Next i
        rs.MoveNext
      Wend
      s=""
      For i=1 To rs.FieldCount
        If i>1 Then s=s+","
        s=s+Str(cw(i))
      Next i
      lb.ColumnWidths=s
      lb.Invalidatecell(-1,-1)
    End Sub

or Sign Up to reply!