Subject Re: [Firebird-Java] jdbc driver issue
Author Pruteanu Dragos
It is fine, we can deal this situation too ( using no catalog and no schema - null for bought ).
I wanted to be sure about how this method works. We will pass null than to each other methods ( getTables(), getColumns(), getExportedKeys, etc. ).

Could you also please review the 'alter statement...' list below ? It would be a great help since you know the best the right commands.

Create View CREATE VIEW {name} AS {text}
Create Primary Key ALTER TABLE {table} ADD CONSTRAINT {name} PRIMARY KEY( {columns} )
Create Unique Index ALTER TABLE {table} ADD CONSTRAINT {name} UNIQUE( {columns} )
Create Index CREATE INDEX {name} ON {table} ( {columns} )
Create Column ALTER TABLE {table} ADD COLUMN {text}
Create Sequence CREATE SEQUENCE {name} START WITH {text}
Create Constraint ALTER TABLE {table} ADD CONSTRAINT {name} ( {text} )
Drop table DROP TABLE {name}
Drop Column ALTER TABLE {table} DROP COLUMN {column}
Drop Index DROP INDEX {name}
Drop view DROP VIEW {name}
Drop Foreign Key ALTER TABLE {table} DROP CONSTRAINT {name}
Drop Constraint ALTER TABLE {table} DROP CONSTRAINT {name}
Drop Sequence DROP SEQUENCE {name}
Change Column data type ALTER TABLE {table} MODIFY {column} {text}
Change Index to Primary Key ALTER INDEX {name} PRIMARY KEY
Change Index to Unique ALTER INDEX {name} UNIQUE
Change Index to non-unique ALTER INDEX {name} NONUNIQUE
Change Foreign Key action ALTER CONSTRAINT {name} ON DELETE {delete_action} ON UPDATE {update_action}
Rename Index RENAME INDEX {name} TO {newname}
Rename Foreign Key RENAME {name} TO {newname}
Rename Constraint ALTER TABLE {table} RENAME CONSTRAINT {name} TO {newname}
Set Table comment COMMENT ON TABLE {table} IS '{comment}'
Set Column comment COMMENT ON COLUMN {table}.{name} IS '{comment}'
Set Column mandatory ALTER TABLE {table} MODIFY {column} NOT NULL
Set Column unsigned ALTER TABLE {table} MODIFY {column} UNSIGNED
Set Column autoincrement ALTER TABLE {table} MODIFY {column} AUTOINCREMENT
Set Column default ALTER TABLE {table} MODIFY {column} DEFAULT {defo}
Set Column check constraint ALTER TABLE {table} MODIFY {column} CONSTRAINT {name} CHECK ( {text} )
Set Column unnamed check constraint ALTER TABLE {table} MODIFY {column} CHECK ( {text} )
Set Column enumeration ALTER TABLE {table} MODIFY {column} ENUMERATION ( {text} )
Unset Column mandatory ALTER TABLE {table} MODIFY {column} NULL
Unset Column unsigned ALTER TABLE {table} MODIFY {column} SIGNED
Unset Column autoincrement ALTER TABLE {table} MODIFY {column} NOAUTOINCREMENT
Unset Column default ALTER TABLE {table} MODIFY {column} DEFAULT null
Unset Column check constraint ALTER TABLE {table} MODIFY {column} DROP CONSTRAINT {table}
Unset Column enumeration ALTER TABLE {table} MODIFY {column} NOENUMERATION

default date function -> sysdate
default time function -> systime
date format -> dd-MMM-yy

When connecting, we got this data types. Is this list correct ?

<type name="BIGINT" java="-5" precision="NONE" />
<type name="BLOB SUB_TYPE 0" java="-4" precision="NONE" />
<type name="BLOB SUB_TYPE 1" java="-1" precision="NONE" />
<type name="CHAR" java="1" precision="LENGTH" />
<type name="NUMERIC" java="2" precision="DECIMAL" />
<type name="DECIMAL" java="3" precision="PRECISION" />
<type name="INTEGER" java="4" precision="NONE" />
<type name="SMALLINT" java="5" precision="NONE" />
<type name="FLOAT" java="6" precision="NONE" />
<type name="DOUBLE PRECISION" java="8" precision="NONE" />
<type name="VARCHAR" java="12" precision="LENGTH" />
<type name="DATE" java="91" precision="NONE" />
<type name="TIME" java="92" precision="NONE" />
<type name="TIMESTAMP" java="93" precision="NONE" />
<type name="BLOB SUB_TYPE <0 " java="2004" precision="NONE" />

----- Original Message ----
From: Roman Rokytskyy <roman@...>
Sent: Tuesday, October 21, 2008 7:10:13 PM
Subject: Re: [Firebird-Java] jdbc driver issue


> Now we are a step further. The software calls now the methods
> DatabaseMetadata md...
> md.getCatalogs( )
> and md.getSchemas( );
> It expects to find at least one entry for at least one of this two methods.
> In our case bought methods returns empty lists. Is this the correct behavior ?

It was my understanding of the JDBC specs when I implemented those
methods. But if you read it differently, please comment.

I had the same issue with Eclipse DTP, which expects at least one
schema. First I tried to workaround the issue by generating fake schema,
but finally was forced to create custom implementation of many parts of
DTP. The issue was that when I return in JDBC driver some fake schema
name, DTP uses it as a prefix to table names in auto-generated SQLs.
Firebird cannot parse such SQL. This issue is known to the Firebird
developers, but schema support is not planned for the next release.

If DbSchema does not prefix columns, it is possible to create a small
plugin that would be used by DbSchema users, which would return those
fake catalog/schema names. (Plugins differ by the JDBC URL, so Type 4,
Type 2 and Embedded are plugins, we have OpenOffice plugin to workaround
the nonconform behavior in autocommit, etc.)

I wonder how other databases without schema/catalogs work...


Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around

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