• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2737
  • Last Modified:

Returning Database objects schema for a Firebird/Interbase database in VB.NET

Hi,

If I need to obtain the list of tables, fields for a given table, list of views and list of stored procedures for an Access database using an OleDb connection, I use the following functions in VB.NET 2003:

'to return a table containing all the tables with a number of fields providing details on each table

 Public Function GetTables(ByVal conn As OleDbConnection) As DataTable
        conn.Open()
        Dim schemaTable As New DataTable
        schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                                                                New Object() {Nothing, Nothing, Nothing, "TABLE"})
        conn.Close()
      Return schemaTable

End Function

'to return a table containing all the fields of a given table with a number of details on each fields such as name, datatype etc.

 Public Function GetFields(ByVal conn As OleDbConnection) As DataTable

        conn.Open()

        'On filtre sur le nom de la table pris dans Combo1
        Dim TableFields As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
                                                                New Object() {Nothing, Nothing, MyTableName, Nothing})
        conn.Close()

Return TableFields

End Function

'to return a table containing all the views with a number of fields providing details on each view

Public Function GetViews(ByVal conn As OleDbConnection) As DataTable

        conn.Open()
        Dim schemaView As New DataTable
        schemaView = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, _
                                                                New Object() {Nothing, Nothing, Nothing, "VIEW"})
        conn.Close()
        Return schemaView

End Function

'to return a table containing all the stored procedures with a number of fields providing details on each stored procedure

Public Function GetProcedures(ByVal conn As OleDbConnection) As DataTable

        Dim schemaProcedure As New DataTable
        conn.Open()
        schemaProcedure = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, _
                                                                New Object() {Nothing, Nothing, Nothing, Nothing})
        conn.Close()
        Return schemaProcedure
  End Function

I'm now in the process of learning how to use Firebird/Interbase databases but don't know how to get the information above i.e. list of tables, fields for a given table, list of views and list of stored procedures for an Firebird/Interbase  database using an FbConnection, in VB.NET 2003 or 2005.

'conn is a FbConnection
conn.GetSchema returns only a table with three columns that do not include the above information.

Any help shall be greatly appreciated.

0
Ghanisen
Asked:
Ghanisen
  • 2
1 Solution
 
NickUpsonSenior Network EngineerCommented:
an sql statement is required for each question, e.g. to retrieve information about columns (fields) within a table

SELECT RF.RDB$FIELD_NAME AS FieldName,
  T.RDB$TYPE_NAME AS DataType,
  F.RDB$FIELD_LENGTH AS FieldLength,
  RF.RDB$NULL_FLAG AS AllowNulls,
  CS.RDB$DEFAULT_COLLATE_NAME AS CharacterSet,
  RF.RDB$DEFAULT_SOURCE AS DefaultValue,
  F.RDB$COMPUTED_SOURCE AS ComputedSource,
  F.RDB$FIELD_SUB_TYPE AS SubType,
  F.RDB$FIELD_PRECISION AS FieldPrecision,
  F.RDB$DESCRIPTION AS Description
FROM RDB$RELATION_FIELDS RF
  LEFT JOIN RDB$FIELDS F ON (F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE)
  LEFT JOIN RDB$TYPES T ON (T.RDB$TYPE = F.RDB$FIELD_TYPE)
  LEFT JOIN RDB$CHARACTER_SETS CS ON (CS.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID)
WHERE RF.RDB$RELATION_NAME = 'EMPLOYEE' AND
  T.RDB$FIELD_NAME = 'RDB$FIELD_TYPE'
ORDER BY RF.RDB$FIELD_POSITION;

a simple list of tables

SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$SYSTEM_FLAG = 0

if you need to get into this level of detail I STRONGLY suggest you get this book
http://www.ibphoenix.com/main.nfs?a=ibphoenix&s=1148642609:1758899&page=ibp_firebird_book
0
 
GhanisenAuthor Commented:
Hi Nick,

Your code does work. Thanks.

I'll order the book you recommended but it'll take 3-4 weeks. Meanwile can you please add the SQL statements to get the list and other informations concerning views, stored procedures and queries.

Thanks

0
 
GhanisenAuthor Commented:
Hi Nick,

I finally found that the GetSchema() method of FbConnection accepts arguments that allow to get information on all the firebird database objects:

'=================
To get information on tables in a database:

       Dim MyView As New System.Data.DataView

        oCnDB() 'Open FbConnection

        MyView .Table = cnDB.GetSchema("Tables")

       fCnDB() 'Close FbConnection

        MyView .RowFilter = "TABLE_TYPE = 'TABLE'"
        MyDataGrid.DataSource = MyView

'=================
To get information on fields (columns) for a given table (MyTableName)
       
        Dim MyView As New System.Data.DataView

        oCnDB() 'Open FbConnection

        MyView .Table = cnDB.GetSchema("Columns")

       fCnDB() 'Close FbConnection

        MyView .RowFilter = "TABLE_NAME = ' & MyTableName & '"
        MyDataGrid.DataSource = MyView

'=================
To get information on views
       
        Dim MyView As New System.Data.DataView

        oCnDB() 'Open FbConnection

        MyView .Table = cnDB.GetSchema("Views")

       fCnDB() 'Close FbConnection

       MyDataGrid.DataSource = MyView

'=================
 To get information on stored procedures
       
        Dim MyView As New System.Data.DataView

        oCnDB() 'Open FbConnection

        MyView .Table = cnDB.GetSchema("Procedures")

       fCnDB() 'Close FbConnection

       MyDataGrid.DataSource = MyView

'=================
 To get information on the parameters of stored procedures
       
        Dim MyView As New System.Data.DataView

        oCnDB() 'Open FbConnection

        MyView .Table = cnDB.GetSchema("ProcedureParameters")

       fCnDB() 'Close FbConnection

       MyDataGrid.DataSource = MyView

I hope this will be helpful for fellow VB.NET developpers using firebird databases.

Thanks

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now