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