[Gambas-user] Getting data from table

[Caveat]

In java you'd use ResultSetMetaData, but in Gambas I adopted a slightly 
different approach using the describe function...of course it's not as 
portable...but it will give a list of columns for a given table in MySQL 
(which I think was your original question)...

Private Function getColumns(tableName As String) As Collection

   Dim coll As New Collection
   Dim tmpRes As Result
   Dim sql As String
   Dim column As DbColumn

   coll = New Collection
   sql = "describe " & tableName
   tmpRes = DataAccess.executeFetch(sql)
   For Each tmpRes
     column = New DbColumn
     column.setName(tmpRes["Field"])
     column.setType(tmpRes["Type"])
     column.setNullable(tmpRes["Null"])
     column.setKey(tmpRes["Key"])
     column.setDefault(tmpRes["Default"])
     column.setExtra(tmpRes["Extra"])
     coll.Add(column, column.getName())
   Next
   Return coll

Catch
   message("Got error: " & Error.Text & " @ " & Error.Where)

End


Much of this code is a few years old, so there may be new features in 
Gambas that let you access the RSMD (or even old features that I just 
missed at the time!).

Kind regards,
Caveat

P.S.  In case you can't manage without it, here's my original DBColumn 
class too (yes, it tries to make the Gambas experience more java-like... 
so sue me :-) ):
' Gambas class file
Private fieldValues As Collection
Private fields As String[]
Public Const NAME_INDEX As Integer = 0
Public Const TARGET_NAME_INDEX As Integer = 1
Public Const TYPE_INDEX As Integer = 2
Public Const NULLABLE_INDEX As Integer = 3
Public Const KEY_INDEX As Integer = 4
Public Const DEFAULT_VAL_INDEX As Integer = 5
Public Const EXTRA_INDEX As Integer = 6

Public Function getFieldByIndex(fieldIndex As Integer) As String

   If fieldIndex < 0 Or fieldIndex > getFieldCount() - 1 Then
     Return ""
   Else
     Return getFieldValues()[getFields()[fieldIndex]]
   End If

End

Public Function getFields() As String[]

   If fields = Null Then
     fields = New String[]
     fields.Add("Name", NAME_INDEX)
     fields.Add("Target Name", TARGET_NAME_INDEX)
     fields.Add("Type", TYPE_INDEX)
     fields.Add("Nullable", NULLABLE_INDEX)
     fields.Add("Key", KEY_INDEX)
     fields.Add("Default Value", DEFAULT_VAL_INDEX)
     fields.Add("Extra", EXTRA_INDEX)
   End If
   Return fields

End

Private Function getFieldByName(fieldName As String) As String

   Return getFieldValues()[fieldName]

End

Private Sub setFieldByName(fieldName As String, fieldValue As String)

   getFieldValues()[fieldName] = fieldValue

End

Private Function getFieldValues() As Collection

   If fieldValues = Null Then
     fieldValues = New Collection
   End If
   Return fieldValues

End

Public Sub setName(newName As String)

   setFieldByName("Name", newName)

End

Public Function getName() As String

   Return getFieldByName("Name")

End

Public Sub setTargetName(newName As String)

   setFieldByName("Target Name", newName)

End

Public Function getTargetName() As String

   Return getFieldByName("Target Name")

End

Public Sub setType(newType As String)

   setFieldByName("Type", newType)

End

Public Function getType() As String

   Return getFieldByName("Type")

End

Public Sub setNullable(newNullable As String)

   setFieldByName("Nullable", newNullable)

End

Public Function getNullable() As String

   Return getFieldByName("Nullable")

End

Public Sub setKey(newKey As String)

   setFieldByName("Key", newKey)

End

Public Function getKey() As String

   Return getFieldByName("Key")

End

Public Sub setDefault(newDefault As String)

   setFieldByName("Default Value", newDefault)

End

Public Function getDefault() As String

   Return getFieldByName("Default Value")

End

Public Sub setExtra(newExtra As String)

   setFieldByName("Extra", newExtra)

End

Public Function getExtra() As String

   Return getFieldByName("Extra")

End

Public Function isPrimaryKey() As Boolean

   If getKey() = "PRI" Then
     Logger.logMessage("Returning TRUE from isPrimaryKey()")
     Return True
   Else
     Logger.logMessage("Returning FALSE from isPrimaryKey()")
     Return False
   End If

End

Public Function getFieldCount() As Integer

   Return getFields().Count

End


On 10/08/12 08:27, Shane wrote:
> On 10/08/12 15:13, rocko wrote:
>> I've read the help files and seen a lot of examples on how
>> to add data to a table but I can' find any examples on how
>> to get data from a table.
>>
>> I need to get total number of fields from a table. I've looked at:
>> 'Property Read Count As Integer' in the Help file.
>> Can i simply put this at the top of my class and then do a
>> Print Count ??
>>
>> I need the Count so i can iterate thru the fields and extract data,
>> something like:
>> For Each Count
>> 	get specific field
>> 	do something with field
>> Next
>> I suppose I could use the primary key ID as the For Each loop
>> But not sure about that.
>>    
>>
> Try this
>
> myResult = dbcon.find(myTable, "id = &1",ID)
>       for each myResult
>           TextBox1.text = myResult!myField1
>           TextBox2.text = myResult!myField2
>       next
>
>>
>> ------------------------------------------------------------------------------
>> Live Security Virtual Conference
>> Exclusive live event will cover all the ways today's security and
>> threat landscape has changed and how IT managers can respond. Discussions
>> will include endpoint security, mobile security and the latest in malware
>> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>> _______________________________________________
>> Gambas-user mailing list
>> Gambas-user at lists.sourceforge.net
>> https://lists.sourceforge.net/lists/listinfo/gambas-user
>>
>>
>>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Gambas-user mailing list
> Gambas-user at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/gambas-user
>