How to format a DB query result in textarea

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?

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

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

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

[quote=344669:@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 [/quote]

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

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

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

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

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

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

just set the listbox.columncount=rs.fieldcount

Yep, that was my suggestion, too :slight_smile:

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

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.

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

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.

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.

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