Table description of firebird

[ztminhas]

Hello

This question is an extension of the the question posted on StackOverFlow @
delphi - How can I get the table description (fields and types) from Firebird with dbExpress - Stack Overflow

I have used his SQL to build a query in embedded firebird:

SELECT
  RF.RDB$RELATION_NAME,
  RF.RDB$FIELD_NAME FIELD_NAME,
  RF.RDB$FIELD_POSITION FIELD_POSITION,
  CASE F.RDB$FIELD_TYPE
    WHEN 7 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'SMALLINT'
        WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 8 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'INTEGER'
        WHEN 1 THEN 'NUMERIC('  || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 9 THEN 'QUAD'
    WHEN 10 THEN 'FLOAT'
    WHEN 12 THEN 'DATE'
    WHEN 13 THEN 'TIME'
    WHEN 14 THEN 'CHAR(' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ') '
    WHEN 16 THEN
      CASE F.RDB$FIELD_SUB_TYPE
        WHEN 0 THEN 'BIGINT'
        WHEN 1 THEN 'NUMERIC(' || F.RDB$FIELD_PRECISION || ', ' || (-F.RDB$FIELD_SCALE) || ')'
        WHEN 2 THEN 'DECIMAL'
      END
    WHEN 27 THEN 'DOUBLE'
    WHEN 35 THEN 'TIMESTAMP'
    WHEN 37 THEN
     IIF (COALESCE(f.RDB$COMPUTED_SOURCE,'')<>'',
      'COMPUTED BY ' || CAST(f.RDB$COMPUTED_SOURCE AS VARCHAR(250)),
      'VARCHAR(' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')')
    WHEN 40 THEN 'CSTRING' || (TRUNC(F.RDB$FIELD_LENGTH / CH.RDB$BYTES_PER_CHARACTER)) || ')'
    WHEN 45 THEN 'BLOB_ID'
    WHEN 261 THEN 'BLOB SUB_TYPE ' || F.RDB$FIELD_SUB_TYPE
    ELSE 'RDB$FIELD_TYPE: ' || F.RDB$FIELD_TYPE || '?'
  END FIELD_TYPE,
  IIF(COALESCE(RF.RDB$NULL_FLAG, 0) = 0, NULL, 'NOT NULL') FIELD_NULL,
  CH.RDB$CHARACTER_SET_NAME FIELD_CHARSET,
  DCO.RDB$COLLATION_NAME FIELD_COLLATION,
  COALESCE(RF.RDB$DEFAULT_SOURCE, F.RDB$DEFAULT_SOURCE) FIELD_DEFAULT,
  F.RDB$VALIDATION_SOURCE FIELD_CHECK,
  RF.RDB$DESCRIPTION FIELD_DESCRIPTION
FROM RDB$RELATION_FIELDS RF
JOIN RDB$FIELDS F ON (F.RDB$FIELD_NAME = RF.RDB$FIELD_SOURCE)
LEFT OUTER JOIN RDB$CHARACTER_SETS CH ON (CH.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID)
LEFT OUTER JOIN RDB$COLLATIONS DCO ON ((DCO.RDB$COLLATION_ID = F.RDB$COLLATION_ID) AND (DCO.RDB$CHARACTER_SET_ID = F.RDB$CHARACTER_SET_ID))
WHERE (COALESCE(RF.RDB$SYSTEM_FLAG, 0) = 0)
ORDER BY RF.RDB$RELATION_NAME,RF.RDB$FIELD_POSITION;

However i would like to extend the SQL to include column Descriptions as shown here:

Is there a way to do this? Thank you again for all of your assistance.

[Ratslinger]

Hello,

While this may have worked in the past to some extent in some DB’s ( see Base: MySQL table field comments: description vs column description ), current ‘quick’ tests of MySQL, PostgreSQL and Firebird embedded shows this not working.

The SQL you have does have provisions for displaying a description but not from the GUI. Tested using Firebird comment and it was included in the query:

[ztminhas]

This might be the solution I need. Thank you.

[ztminhas]

I will go the suggested rout for now, but I would still like to know where is the description stored when it is entered through the interface. Thank you.

[Ratslinger]

It is stored in a file within the Base .odb - content.xml:

Base:

Content.xml:

<db:columns><db:column db:name="id" db:help-message="Line1"/><db:column db:name="name" db:help-message="Line2"/><db:column db:name="series_link" db:help-message="Line3"/></db:columns

.
Edit:
.
Did find access to this using MRI:
.

.
As this is not a part of the database, SQL could not access this. Macros could (image has code generated by MRI).

[ztminhas]

@Ratslinger

Did find access to this using MRI:

This looks like a better solution. Thank you yet again.

[ztminhas]

This is just a followup on the presented solution. I tried to write code to automate this process but to no avail. Here is what I did:

Option Explicit
Global dbConn As com.sun.star.sdbc.XConnection
Global stmt As com.sun.star.sdbc.XStatement
Global rst As com.sun.star.sdbc.XResultSet

Sub describe
	getFbConnection(ThisDatabaseDocument)
	oDataSource =ThisComponent.DataSource 
	oTables=oDataSource.getTables() 
	oobj1 =oTables.getByName( "SAMPLE_TABLE_NAME") 
	oColumns =oobj1.getColumns() 
	For Each oColumn In oColumns
		sql = "comment on column SAMPLE_TABLE_NAME." & oColumn.Name  & " is ?"
                stmt = dbConn.prepareStatement(sql)
                stmt.setString(1, oColumn.HelpText)
                stmt.execute()
                stmt.clearParameters()
                stmt.close()
                stmt=nothing
	Next oColumn
End Sub

public sub getFbConnection(fdb as com.sun.star.sdb.XOfficeDatabaseDocument) As com.sun.star.sdbc.XConnection
Dim oDataSource as Object
	set oDataSource=fdb.CurrentController
	With oDataSource
		IF NOT (.isConnected()) THEN .connect()
		set dbConn= .ActiveConnection()
	End With
end Sub

I get an error when i try to execute the prepared statement; however, when I run this through the SQL window,.it works without a problem. This is true whether i run the SQL command directly or otherwise:

So while it is correct and I accepted the solution of using the comment, I would like to go one step further to make this work through a prepared statement or some kind of automation. Thank you again for all of your help.

[Ratslinger]

@ztminhas
This is not a proper method for further help. This portion of the question is buried and may not be found by others. Also as a mater of general practice, the original question was about SQL and this is about a macro. Even though related, the two are very different regarding the background one must have to answer. In the future ask this as a new question. Thank You.
.
Have tested this and it works:

option explicit
Sub add_description
    Dim oStatement As Object
    Dim oDataSource As Object
    Dim oColumns As Object
    Dim oColumn As Object
    Dim oTables As Object
    Dim oTable As Object
    Dim sSQL As String
    if IsNull(Thisdatabasedocument.CurrentController.ActiveConnection) then
       Thisdatabasedocument.CurrentController.connect
    endif
    oStatement = ThisDatabaseDocument.CurrentController.ActiveConnection.createStatement()
	oDataSource =ThisDatabaseDocument.DataSource 
	oTables=oDataSource.getTables() 
	oTable =oTables.getByName( "book") 
	oColumns =oTable.getColumns() 
	For Each oColumn In oColumns
        sSQL = "comment on column ""book"".""" & oColumn.Name  & """ is '" & oColumn.HelpText & "'"
        oStatement.executeQuery(sSQL)
	Next oColumn
End Sub

.
result:

.
Used same table of mine (book) as tested previously.

[Ratslinger]

Please note. Although this can be done here as well as other databases, it is not a standard.

[ztminhas]

Thank you again for your help