[Bill Oliver]

Hi All,

Here’s my problem. My technical problem, I mean. 😀

My proprietary database product *does* support schemas and catalogs. The schema that holds my tables happens to be null.

When I connect via JDBC with the Database Browser, I get a tree widget which has one schema with the description “[unknown]”. If I click on this leaf, I would expect to see my tables. Instead, I don’t see any tables.

I turned on some JDBC tracing, and the JDBC call is:
rs = dbmd.getTables(null,”[unknown]”,”%”, null);

Note that “[unknown]” is the literal being passed to getTables.

In this case, I would have expected a value of NULL to be passed instead, or maybe the empty string “”.

If I write a code snippet and pass down NULL, I get my tables. If I use the empty string I don’t get my tables, but this might be a bug with my proprietary JDBC driver.

I am using MyEclipse 4.1.0 GA, buid 20060122-4.1-GA.

Many thanks in advance, and thanks for a truly great product!!!

bill oliver

[Riyad Kalla]

Bill,
Can you let us know what DBMS this is, or is it a in-house thing? If it’s not something we can test against that makes it tricky to support, although your assessment of this singular issue may be right on, there may lie a whole slew of issues down the road w.r.t. to querying, reverse engineering, etc. if it is an in-house development that we don’t have access to for testing.

[Bill Oliver]

Hello Riyad,

Thanks for the prompt response.

The DBMS in question is an in-house thing. I work at SAS Institute, and we’re working on a new product, SAS Table Server, that will allow you to retrieve SAS data through our new JDBC driver.

I asked our database experts if another RDBMS would return a NULL schema so you could test this with another product, and my co-worker thinks I can reproduce this using the JDBC-ODBC bridge through to MS-Access. I’ll try this and post back when I know.

I’ve discussed this issue with my developers, and it does appear that we have a bug in our software. I think that myEclipse should be making this JDBC call to retrieve tables for a NULL schema:

rs = dbmd.getTables(null,””,”%”, null);

I would like to see MyEclipse use the empty string for the schema in the above call instead of the literal “[unknown]” in cases where the Schema is NULL. That string does seem to be in error. If you could provide me a test jar/file, I would be glad to test against our product.

Unfortunately, this code snippet isn’t working today with our driver when I twiddle our server options a particular way, so that is a bug we have to fix, and are working on currently. I expect we’ll resolve this soon.

This issue aside, I use the MyEclipse product daily, and enjoy the product very much. MyEclipse is a very good JDBC-compliant product, so it’s worked well to test functionality of our driver that might not otherwise come up. I’ve used it internally to verify correct operation of our JDBC driver. I’ve also used it internally to demonstrate to my co-workers our successful 3rd party tool interaction, so I am eager to get this item resolved.

-bill

[Haris Peco]

verbguy,

This is a bug and it has already been filed for this issue.Thank you for notifying us about this problem.
It has been fixed in 4.1.1 (later Februar)

Best

[Haris Peco]

verbguy,

adding :

rs = dbmd.getTables(null,””,”%”, null);

this is good point – we have to set null except ‘unknown’

[Bill Oliver]

Thank you very much for the update. I will look forward to seeing the fix in the next release.

[Bill Oliver]

Hello all!

I downloaded the 4.1.1 version of MyEclipse to see if this problem had been fixed. It was quite exciting, actually, to see my item in the release notes as defect #3345. Thank you for your effort, and the prompt responses!

Unfortunately, I still can’t browse database tables with my proprietary database (SAS Table Server). As I mentioned in a previous post, our software returns “” (empty string) for one of its schemas. When I originally posted, we had a bug in our software too, one which has since been fixed.

I am going to be attending our yearly trade show, SUGI (http://support.sas.com/usergroups/sugi/sugi31/index.html) this year at the end of March. I really was hoping to demonstrate MyEclipse there to show how our product is interoperable with leading Java development tools. Once you verify this is a problem, is there any way I can get a test jar before the next maintenance release to verify the fix?

The symptom of the problem is that when I connect, I get this error message:
“Error while trying to login to database :
Attempt to get or create node with key of zero length”

This is because the first schema comes back as “”. MyEclipse evidently doesn’t think that a schema with an empty string should ever be returned from the database.

The JDBC API permits passing an empty string in for the schema. See here:
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/DatabaseMetaData.html

The API for getTables() says that you 3 choices for the schema parameter: NULL – ignore, “” retrieves those without a schema, and “string” – match this schema.

Here is the JDBC trace log, which shows the “” schema being retrieved from the call to getSchemas().

13:56:46.630 drv2.connect()
0.000 con2
13:56:46.833 con2.getAutoCommit()
0.000 true
13:56:46.895 con2.getMetaData()
0.000 dbmd8
13:56:46.895 dbmd8.getDatabaseProductName()
0.000 “FIREBIRD”
13:56:46.895 con2.getMetaData()
0.000 dbmd9
13:56:46.895 dbmd9.supportsCatalogsInTableDefinitions()
0.000 false
13:56:46.895 con2.getMetaData()
0.000 dbmd10
13:56:46.895 dbmd10.supportsCatalogInDataManipulation()
0.000 false
13:56:46.895 con2.getMetaData()
0.000 dbmd11
13:56:46.895 dbmd11.supportsCatalogsInProcedureCalls()
0.000 false
13:56:46.895 con2.getMetaData()
0.000 dbmd12
13:56:46.895 dbmd12.supportsSchemasInDataManipulation()
0.016 true
13:56:46.911 con2.getMetaData()
0.000 dbmd13
13:56:46.911 dbmd13.getSchemas()
0.031 rs2
13:56:46.942 rs2.getMetaData()
0.016 rsmd1
13:56:46.958 rsmd1.getColumnCount()
0.000 1
13:56:46.958 rs2.next()
0.000 true
13:56:46.958 rsmd1.getColumnType(1)
0.000 12
13:56:46.958 rs2.getString(1)
0.000 “”
13:56:46.958 rs2.next()
0.000 true
13:56:46.958 rsmd1.getColumnType(1)
0.000 12
13:56:46.958 rs2.getString(1)
0.000 “INTERNAL”
13:56:46.958 rs2.next()
0.000 true
13:56:46.958 rsmd1.getColumnType(1)
0.000 12
13:56:46.958 rs2.getString(1)
0.000 “PUBLIC”
13:56:46.958 rs2.next()
0.000 false
13:56:46.958 rs2.close()
0.015
13:56:46.973 rs2.getStatement()
0.000 s1
13:56:46.973 s1.close()

[Haris Peco]

Bill,

Your driver is broken, probably
What return next DatabaseMetadata methods :
supportsCatalogsInDataManipulation()
supportsCatalogsInTableDefinitions()
supportsCatalogsInProcedureCalls()
supportsCatalogsInIndexDefinitions()
supportsCatalogsInPrivilegeDefinitions()
supportsSchemasInDataManipulation()
supportsSchemasInIndexDefinitions()
supportsSchemasInPrivilegeDefinitions()
supportsSchemasInProcedureCalls()
supportsSchemasInTableDefinitions();
It have to be false if your database doesn’t support schemas and catalogs

The API for getTables() says that you 3 choices for the schema parameter: NULL – ignore, “” retrieves those without a schema, and “string” – match this schema.

this is input to getTables and driver have to return null if doesn’t support schemas and catalogs.However, this is later – if your jdbc driver return supportCatalogxxx and supportSchemaxxx correct (false) MyEclipse wiil
make ‘Default Schema’ node and handle tables correct

Best

[Bill Oliver]

OK, thanks for the post. We do (really) support catalogs. We do (really) support schemas. For my connection, I have 3 schemas (see log) : “INTERNAL”, “PUBLIC”, and “”.

The “” schema is the one causing the problem. My developer says this is OK to have a schema whose name is empty string, and the JDBC API reference seems to back this up. What this really means is that my database does support catalogs and schemas, but tables in the “” schema don’t really have a schema.

I tried to google for a more authoratative link, the best I could come up with was this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqltables.asp

The interesting snippet is “SCHEMANAME : String search pattern for schema names. If a driver supports schemas for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string (“”) denotes those tables that do not have schemas. ”

That is exactly what is going on here.

[Haris Peco]

The interesting snippet is “SCHEMANAME : String search pattern for schema names. If a driver supports schemas for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string (“”) denotes those tables that do not have schemas. ”

first, this is odbc, no jdbc specification and second it claim that ‘different DBMSs’ can support or doesn’t support schemas.

suportSchemaxx/Catalogxx methods in jdbc are boolean and it can be true or false for one DBMS – it can’t be both

Best

[Bill Oliver]

Per your suggestion, I went over our JDBC driver in great detail yesterday, and compared it to a reference implementation.

Turns out you were correct, there was a bug in our implementation of supportsCatalogsInTableDefinitions() and supportsCatalogInDataManipulation(). These methods were returning false incorrectly.

I think this issue is now closed. Thanks again for the assistance.

[Author]

Posts