?
Solved

DatabaseMetaData failing to return primary keys

Posted on 2006-03-22
57
Medium Priority
?
351 Views
Last Modified: 2009-07-29
Dear Experts,

I have existing db-metadata code that just stopped working.  This is it:

  DatabaseMetaData dbmd = conn.getMetaData();
  tableName = "FS_VENDOR_ACCT";
  ResultSet rs = dbmd.getPrimaryKeys(null, null, tableName);
  while (rs.next())
  {
    String keyName = rs.getString("COLUMN_NAME");
    System.out.println("Is a key:" + keyName);
  }

I know for sure that:
  The connection (conn) works (it correctly gets TABLE metadata next)
  The correct database is being accessed (see above..)
  The table exists (I have tried prepending the database name and owner name to the table name, with no luck)
  The table has a primary key (consisting of three fields)

Two big things have changed since this last worked:

1.  I switched to Eclipse (from JBuilder)
2.  I put this class in a (non-default) package.

What returns is nothing:  It doesn't blow up, it just returns an empty result set.  To test, I tried putting garbage in the tablename, and that return the same empty result set.

HELP!  Any guesses?

Thanks,
BrianMc1958
 
0
Comment
Question by:BrianMc1958
  • 24
  • 22
  • 6
  • +3
57 Comments
 
LVL 14

Expert Comment

by:StillUnAware
ID: 16261728
When changing from JBuilder to Eclipse, You must have 'silently' changed the JDBC Driver for a database.

You should download the last available version of a driver, and add it to Your classpath and/or project
0
 

Author Comment

by:BrianMc1958
ID: 16261827
Dear StillUnAware:

That's a great theory that would explain everything.  I'm hard-coding the driver in my connection statement, which is here:

  public Connection getGenericDatabaseConnection()
  {
    Connection conn = null;
    String s;

    try
    {
      Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
      s = "jdbc:microsoft:sqlserver://" +
          dbLocation +
          ";databasename=" +
          dbName;
      conn = DriverManager.getConnection(s, user, pswd);
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
    return (conn);
  }

In my build path libraries, I have the Microsoft jar that contains com.microsoft.jdbc.sqlserver.SQLServerDriver, and that jar is above the JRE System Library (jre1.5...)  Am I missing something else?  Does Eclipse load from somewhere other than the build path libraries first?  How do I check?  If you fix this, I'll come over and mow your lawn every week this summer.

--BrianMc1958
0
 

Author Comment

by:BrianMc1958
ID: 16261880
The JRE has gone from 1.4... to 1.5 something.  That might be it.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:StillUnAware
ID: 16261950
Can You still check whether everything works in JBuilder?

And I still sugest You double check the classpath. To see it from jre standalone do that from command line:

run java debugger
  jdb
run an imaginative session
  run some_text
get the classpath
  classpath

that's the only way I found, how to check current classpath, but remember, this shows the classpath only for jre, it won't show whether eclipse is including some other libraries.
0
 
LVL 30

Expert Comment

by:Mayank S
ID: 16266556
>> The JRE has gone from 1.4... to 1.5 something.  That might be it.

Maybe. Try setting the JRE to 1.4 by going to your project-properties (if you have 1.4 installed and Eclipse does not recognize it, you will need to add it in Eclipse).
0
 

Author Comment

by:BrianMc1958
ID: 16269807
Well...

I think I have now eliminated both the Java version and Eclipse as suspects, but it still isn't working.  I'm now thinking there might have been a change to the MS SQL database itself, maybe an update.  

To test your ideas, I created a new single class containing all the needed code, and compiled that under Java 1.4, entirely outside of Eclipse.  (I also removed the new package, and used the default, as I had been doing.) Then I ran the resulting class from DOS, again using Java 1.4.  I get the same result--the resultset is empty.

If anyone has any more suggestions, that would be greatly appreciated.  Below is the entire new class:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.*;

public class TestMetaData2
{
       private String dbLocation = "MyMachine:1433";
       private String dbName = "MyDB";
      private String user = "MyUser";
      private String pswd = "MyPassword";

      public static void main(String args[])
      {
            TestMetaData2 me = new TestMetaData2();
            me.doMain();
      }

      private void doMain()
      {
            Connection conn = getGenericDatabaseConnection();
            getDatabaseMetaData(conn);
      }

      private void getDatabaseMetaData(Connection conn)
      {
            int count = 0;
            try
            {
                  DatabaseMetaData dbmd = conn.getMetaData();
                  String tableName = "FS_Vendor_Acct";
                  ResultSet rs = dbmd.getPrimaryKeys(null, null, tableName);
                  while (rs.next())
                  {
                        count++;
                        String keyName = rs.getString("COLUMN_NAME");
                        sysout("Col:" + keyName);
                  }
                  rs.close();
                  if (count == 0)
                        sysout("NO KEYS FOUND");
            }
            catch (SQLException ex)
            {
                  ex.printStackTrace();
            }
      }

      public Connection getGenericDatabaseConnection()
      {
            Connection conn = null;
            String s;
            try
            {
                  Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
                  s = "jdbc:microsoft:sqlserver://" + dbLocation + ";databasename=" + dbName;
                  conn = DriverManager.getConnection(s, user, pswd);
            }
            catch (Exception e)
            {
                  e.printStackTrace();
            }
            return (conn);
      }

      private void sysout(String s)
      {
            System.out.println(s);
      }
}

Thanks again,
BrianMc1958
 
0
 
LVL 12

Expert Comment

by:Giant2
ID: 16270643
Try to use jtds driver instead of MS driver.
https://sourceforge.net/project/showfiles.php?group_id=33291&package_id=25350

Hope this could solve your problem.
Bye, Giant.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16271098
What output *are* you getting?
0
 

Author Comment

by:BrianMc1958
ID: 16271450
In my live code, I have a println immediately before the "while (rs.next())", just to verify I'm getting there.  I see that println, but then nothing from within the loop.  Just in case, I put a counter in the loop, and check it afterward.  It doesn't get there.  The resultset is always empty.

I have since done more testing.  I have taken the above class, along with a copy of Java 1.4, and the three MS SQL jars, and copied them all to two other machines.  Then, I renamed "java.exe" to "javaZZZ.exe", just to be certain it was using the 1.4 java.  Then, I ran the above class from DOS on the new machines.  In both cases, I still get nothing.  

Either I am missing something in the class itself--which does not seem to have changed--or the database itself has changed.

However, I have two different databases on two different machines I am testing against, and NEITHER returns anything now.

HELP!

--BrianMc1958  
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16271472
Check the privileges of the database user.

That might have changed disallowing you the earlier privileges.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16271478
Can you retrieve anything in your sql console, say along the lines of

select * from SYS_TABLES

(or the equivalent)?
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16271529
The new privileges could not  allow you to access the  primary keys of the table.

Try the code with a user that has super(all, APP_OWNER) privileges. If the code works, then the problem is with the User privileges.
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16271571
And check that the table actually has a primary key. Maybe the primary keys were removed from the table ;). A very wild guess.
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16271589
The other parameters, schema name and catalog name , have they changed. Try passing "" for both.
"","", table name
null,"",table name
"",null,table name
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16271659
Try running this query directly to get the Primary Keys for SQL Server

select       c.COLUMN_NAME
      from       INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
      where       pk.TABLE_NAME = @TableName
      and      CONSTRAINT_TYPE = 'PRIMARY KEY'
      and      c.TABLE_NAME = pk.TABLE_NAME
      and      c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
0
 

Author Comment

by:BrianMc1958
ID: 16271702
To mrigank:  I have checked the privileges, and I have all privileges.  Thanks for the disappearing keys guess--I HAVE seen that actually happen here, but not now.  I tried your empty-string idea, but no luck.

To  CEHJ:  I CANNOT run "select * from SYS_TABLES" from within SQL Query analyzer.  Is that what you meant?  I select the "master" db, and it says "Invalid object name 'SYS_TABLES'."

To EVERYBODY:  I would very much appreciate ANY further ideas.  This is turning into a very serious problem for me. THANKS!
--BrianMc1958
0
 

Author Comment

by:BrianMc1958
ID: 16271751
To mrigank:  I modified your query to include my actual table name:

select      c.COLUMN_NAME
     from      INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
          INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
     where      pk.TABLE_NAME = 'FS_Vendor_Cust'
     and     CONSTRAINT_TYPE = 'PRIMARY KEY'
     and     c.TABLE_NAME = pk.TABLE_NAME
     and     c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

It does run, but it returns an EMPTY window in the "grids" window, with a header "COLUMN_NAME" at the top.  I don't know much about SQL.  Did I do it right?

--BrianMc1958  
0
 

Author Comment

by:BrianMc1958
ID: 16271781
I tried it both in "master" database, and in the database in which my table is located.  Do I need to specify the db and/or owner in the table name itself?  ('Mydb.MyID.Tablename'?)
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16271787
>>it says "Invalid object name 'SYS_TABLES'."

Well i did say to alter that appropriately ;-) mrigank has saved you the trouble
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16271866
The SQL query returns the primary keys for the table.
The query is returning nothing, the same that your code is doing.
Are you sure you have the primary keys right.

Try inserting a duplicate row into the table.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16271896
Sometimes keys/indexes are dropped to allow faster build of tables. Could be that's happened but they haven't been reapplied
0
 

Author Comment

by:BrianMc1958
ID: 16271906
When I try this in SQL Query Analyzer:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

I see the constraint for FS_Vendor_Cust.  It is listed as PRIMARY KEY.  

When I go into Enterprise Manager, under Properties > Indexes/Keys, I see the same constraint listed as the Primary Key.

--BrianMc1958
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16271912
Run the query by appending the schema name/user name in front of the table name

'Mydb.MyID.Tablename'
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16272019
run select * from      INFORMATION_SCHEMA.TABLE_CONSTRAINTS

check the results for table_name = 'your table'

do you get the primary key constraints ???
0
 

Author Comment

by:BrianMc1958
ID: 16272030
To mrigank:  I tried that.  It still returns nothing.

To CEHJ:  Yours is a real possibility.  Another fairly recent change has been to use BULK INSERT.  In doing so, I have been changing RECOVERY from SIMPLE to BULK-LOGGING, and then back.  I don't know if that would have such an effect.  To test, I just did:

DBCC DBREINDEX(MyID.MyTable',' ',90)

which I believe would rebuild keys.  Unfortunately, that also had no affect.

Any more ideas?
0
 

Author Comment

by:BrianMc1958
ID: 16272077
To mrigank:  I DO see the primary key constraints for my table using that command.  Does that definitely mean that the keys are there?

--BrianMc1958
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16272099
When you run the query "SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS"

you get the pk constraint

can you tell the values of these columns :

table_name
constraint_name
constraint_type

for  your table
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16272111
>> Does that definitely mean that the keys are there?

It should mean that.

If you can insert data in the  table, try inserting a duplicate row. That will tell  you if the table actually has a primary key ;).
0
 

Author Comment

by:BrianMc1958
ID: 16272145
table_name = FS_Vendor_Cust
constraint_name = PK__FS_Vendor_Cust__03FB8544
constraint_type = PRIMARY KEY

("FS_Vendor_Cust" is the real name of "MyTable".  I always change names for clarity...)

--BrianMc1958
 
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16272163
hey

your original question had this table name : FS_VENDOR_ACCT

and then the subsequent discussion had this  : FS_Vendor_Cust

Is that the problem ?
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16272173
Change the table name to FS_VENDOR_CUST in the code  ;).
0
 

Author Comment

by:BrianMc1958
ID: 16272184
Sorry.  I did switch tables, because the FS_Vendor_Cust has indexes as well as a primary key, and I'm trying to check for both.

--BrianMc1958
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16272214
Now run this query

Select *  from  INFORMATION_SCHEMA.KEY_COLUMN_USAGE

What values do you get for

table_name
constraint_name
constraint_type

for the same table
0
 

Author Comment

by:BrianMc1958
ID: 16272397
I don't see the table anymore with that SQL.  I only get two rows back, and the table_name for both is dtproperties.  I ran your query exactly as you typed it.  Was I supposed to specify by table name?

--BrianMc1958
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16272532
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
Informational schema view located in the master database (SQL Server 2000) or all databases (SQL Server 7.0) containing one row for each column, in the current database, that is constrained as a key.

Which version of SQL server are you using ?

Did your database undergo an upgrade recently ?
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16272596
Try this command

USE YourDBname
GO
SELECT * FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
GO


Now what do you get ??
      
0
 

Author Comment

by:BrianMc1958
ID: 16272606
On the main machine, SQL Server 2000.  I am also getting the same (null) results on another box that is on 2003.

I don't believe the db had a recent upgrade, but I'm checking on that.

Could you tell me any more about what conclusions you might be drawing?  Did the most recent test indicate that keys somehow are NOT being found by SQL Server itself?

Thanks,
BrianMc1958
0
 

Author Comment

by:BrianMc1958
ID: 16272686
For:

USE YourDBname
GO
SELECT * FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
GO

I get the same two rows returned.  I tried running under my db, and under "master".
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16272735
This is my last try :)

Try logging in as the System Admin and run the query.

Does that give the same results ?

Under which user was the table created ?
Try running the query with the same User.
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16272774
This is the assumption that I am drawing

a) The query that I have posted( the first one) is the one used to fetch the rpimary keys.
b) I have read at a few websites that the INFORMATION_SCHEMA views have privilege issues. they show only what the current user has access to. They show different results for System Admins and guest users.

Hence my last suggestion. try the query with the system admin user  or the user that has created the table.
0
 

Author Comment

by:BrianMc1958
ID: 16272877
Yes.  That gives the same results.

The table was created under my ID (FTG_Total_User).  All of my previous queries have been run under that ID.

I am very grateful that you would take so much time to help me.  Any parting shots?  Do you think there might be a problem with my SQL Server?

--BrianMc1958
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16273915
It might be that the Priary Key data is not consistent.

Try deleting the Primary Key Constraint and then recreating it.

If  it doesnt work then
Try creating a fresh new table(test table) with a primary key and then running the query/your code.
It should at least give the constraints.
0
 
LVL 5

Accepted Solution

by:
mrigank earned 1600 total points
ID: 16274057
"Actually, there is a bug in MSSQL 2000 in the INFORMATION_SCHEMA.KEY_COLUMN_USAGE view. Sometimes it does not list the columns for primary keys and unique constraints. Actually, this occurs when the table is outside "dbo" schema "

from
http://forums.databasejournal.com/showthread.php?s=614c26e2a6db74c1f68238a833912ba7&postid=88245
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16274063
Try putting the table inside the dbo schema and running
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16274127
In the get PrimayKeys method of the DbMeteData, pass the database schema name and see the results instead os passing a null


This is all I can think  of right now.
0
 

Author Comment

by:BrianMc1958
ID: 16274281
I tried deleting and recreating Primary Key.  No luck.

I tried copying in another version of the table from another db, with "dbo" as the owner.  That didn't work either.  Is that what you meant by "inside the dbo schema"?

BTW:  Three comments ago was supposed to be your last comment.  (Thanks)
0
 

Author Comment

by:BrianMc1958
ID: 16274292
What is the database schema name?  The owner name?
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16275000
I think so.


Phew. I think this could be a problem/bug with SQL server as some of the sites I checked say. Not sure  though.
You sure that that the SQL servr wasnt upgraded. Even the service pack version.
0
 

Author Comment

by:BrianMc1958
ID: 16275193
I'm still not sure if we had an upgrade.  Trying to get hold of the service guy for that.

I have made significant progress, though.

I found a Microsoft site using getPrimaryKeys, and copied it pretty much verbatim.  It's directed at the old "Northwind" db, with "sa" and "" as user and pswd.  That DOES work.  One thing I think this eliminates is the possibility that the problem is with the JDBC driver.  Their example uses the exact SQLServer and JDBC strings as mine, and it does work.

I'm now looking at ownership of the db.  "sa" owns "Northwind".  I found that my db is NOT owned by the user ID I have been using (FTG_Total_User), although supposedly I can do anything with it anyway.  Also, "Northwind" shows "dbo" in the "owner" column.  

So, that's the latest.  At least that's significant progress.  Having one working example helps a lot.

Any more ideas?  I think I'll be mowing your lawn for several years for all this.

Thanks,
BrianMc1958
0
 

Author Comment

by:BrianMc1958
ID: 16275295
More progress.

Changed ownership of the database to "FTG_Total_User", and copied the table as the same name with "dbo" owner.  Then it works.

It's just not OK for me to keep "dbo" as the owner.  Is that "normal" to always have "dbo" as the owner?

--BrianMc1958
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16275340
Phew. It works at least in some ways.

>>Is that "normal" to always have "dbo" as the owner?

I am not very sure on that. A DBA could probably answer that.
0
 
LVL 12

Assisted Solution

by:Giant2
Giant2 earned 200 total points
ID: 16278025
>Is that "normal" to always have "dbo" as the owner?
Any table could be assigned to any user. So if you want to change the ownership you can do, but even you must change something in your program.
0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 200 total points
ID: 16278248
>>
and copied the table as the same name with "dbo" owner.  Then it works.

It's just not OK for me to keep "dbo" as the owner.
>>

You're right - that's a poor state of affairs. That suggests, as we were thinking earlier, that it's a matter of rights. Sounds like you need to investigate the rights and permissions more and grant the correct ones
0
 

Author Comment

by:BrianMc1958
ID: 16281808
To mrigank:  I've been off in the MS SQL area this morning...  Thank you once again for all your help on this.  I do think I at least know what the problem is now.  You not only helped me get there, but you may have saved me from jumping off a bridge.

To CEHJ:  I'm still looking into the permissions issue, but I think there is some sort of MS SQL bug here also...

To Giant2:  Thank you 2 !

--BrianMc1958
0
 
LVL 5

Expert Comment

by:mrigank
ID: 16281946
What happens to my lawn ;).

Do not worry, I do not have one right now :).
0
 

Author Comment

by:BrianMc1958
ID: 16282152
When you get one, I'll rush right over.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16282214
:-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question