Hi Roman,

Thank you for the fast reply.
We load the drivers dynamically, therefore we cannot do any cast to database specific classes in our code.
It is more easy to have the queries ( for extracting procedures, execution plan, and check constraints ).
For constraints we would need only the check constraints and table related constraints, the pk's, fk's and indexes we read via JDBC.
Thank you for the test database, I succeed to import it in DbSchema.
When I will have the procedures, triggers, etc. queries I will try again.

Best regards,
Dragos Pruteanu





________________________________
From: Roman Rokytskyy <roman@...>
To: Firebird-Java@yahoogroups.com
Sent: Saturday, November 8, 2008 8:01:02 AM
Subject: Re: [Firebird-Java] system tables


Hi,

> For further integrating Firebird with DbSchema, I would like to ask:
> I need some queries to read:
> 1. for views: view_name, view_source_ query
> 2. for procedures, triggers and functions: name, source_query

Cast the DatabaseMetaData object to FirebirdDatabaseMet aData and use
methods getProcedureSourceC ode, getTriggerSourceCod e, getViewSourceCode
calls - it is the easiest way.

If you want to avoid importing Firebird packages, tell me, I will
provide you queries

> 3. is the explain plan working with 'explain plan ....' ? Which is the correct way of getting the execution plan ?

Cast Statement or PreparedStatement to FirebirdStatement or
FirebirdPreparedSta tement respectively and use methods:

FirebirdStatement. getLastExecution Plan()
FirebirdPreparedSta tement.getExecut ionPlan()
FirebirdResultSet. getExecutionPlan ()

There is no SQL statement to return the execution plan, works only via API.

> 4. for table constraints and check constraints, the table_name, constraint_name, constraint_body

Are primary and foreign keys and unique constraints not available via
DatabaseMetaData? What does not fit your requirements?

A bit different query for check contraint, but you see what you need
(copying them from other place)

Check constraints per table:

private static final String GET_CHECK_CONSTRAIN TS = ""
+ "SELECT "
+ " rc.rdb$constraint_ name AS CHECK_CONSTRAINT_ NAME "
+ ", tr.rdb$trigger_ source AS CHECK_CONSTRAINT_ SOURCE "
+ "FROM "
+ " rdb$triggers tr "
+ "INNER JOIN "
+ " rdb$check_constrain ts cc "
+ "ON "
+ " cc.rdb$trigger_ name = tr.rdb$trigger_ name "
+ "INNER JOIN "
+ " rdb$relation_ constraints rc "
+ "ON "
+ " cc.rdb$constraint_ name = rc.rdb$constraint_ name "
+ "WHERE "
+ " rc.rdb$constraint_ type = 'CHECK' "
+ "AND "
+ " rc.rdb$relation_ name = ?";

> 5. for sequences the sequence_name

Sequences:

query = "SELECT rdb$generator_ name AS " + SEQUENCE_NAME + " " +
"FROM rdb$generators " +
"WHERE rdb$system_flag = " + (systemSequences ? '1' : '0');

> Do you have any test database on your site or in the installation package ? We would like to point in the documentation to such a database.

It is EMPLOYEE.FDB in the Firebird installation directory
($FIREBIRD/examples /empbuild) .

> The the first-time-users would be able to connect to this one only in few steps.
> It would be nice if the database may have few more tables, with some data records, and also views, procedures, triggers, etc.

Yes, this is exactly the case. It might happen that in the next release
we have new one, but this one is quite ok.

Thanks for your efforts in this direction!

Roman





[Non-text portions of this message have been removed]