DatabaseMetaData failing to return primary keys

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
 
BrianMc1958Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

StillUnAwareCommented:
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
BrianMc1958Author Commented:
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
BrianMc1958Author Commented:
The JRE has gone from 1.4... to 1.5 something.  That might be it.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

StillUnAwareCommented:
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
Mayank SAssociate Director - Product EngineeringCommented:
>> 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
BrianMc1958Author Commented:
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
Giant2Commented:
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
CEHJCommented:
What output *are* you getting?
0
BrianMc1958Author Commented:
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
mrigankCommented:
Check the privileges of the database user.

That might have changed disallowing you the earlier privileges.
0
CEHJCommented:
Can you retrieve anything in your sql console, say along the lines of

select * from SYS_TABLES

(or the equivalent)?
0
mrigankCommented:
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
mrigankCommented:
And check that the table actually has a primary key. Maybe the primary keys were removed from the table ;). A very wild guess.
0
mrigankCommented:
The other parameters, schema name and catalog name , have they changed. Try passing "" for both.
"","", table name
null,"",table name
"",null,table name
0
mrigankCommented:
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
BrianMc1958Author Commented:
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
BrianMc1958Author Commented:
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
BrianMc1958Author Commented:
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
CEHJCommented:
>>it says "Invalid object name 'SYS_TABLES'."

Well i did say to alter that appropriately ;-) mrigank has saved you the trouble
0
mrigankCommented:
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
CEHJCommented:
Sometimes keys/indexes are dropped to allow faster build of tables. Could be that's happened but they haven't been reapplied
0
BrianMc1958Author Commented:
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
mrigankCommented:
Run the query by appending the schema name/user name in front of the table name

'Mydb.MyID.Tablename'
0
mrigankCommented:
run select * from      INFORMATION_SCHEMA.TABLE_CONSTRAINTS

check the results for table_name = 'your table'

do you get the primary key constraints ???
0
BrianMc1958Author Commented:
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
BrianMc1958Author Commented:
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
mrigankCommented:
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
mrigankCommented:
>> 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
BrianMc1958Author Commented:
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
mrigankCommented:
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
mrigankCommented:
Change the table name to FS_VENDOR_CUST in the code  ;).
0
BrianMc1958Author Commented:
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
mrigankCommented:
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
BrianMc1958Author Commented:
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
mrigankCommented:
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
mrigankCommented:
Try this command

USE YourDBname
GO
SELECT * FROM INFORMATION_SCHEMA.Key_COLUMN_USAGE
GO


Now what do you get ??
      
0
BrianMc1958Author Commented:
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
BrianMc1958Author Commented:
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
mrigankCommented:
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
mrigankCommented:
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
BrianMc1958Author Commented:
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
mrigankCommented:
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
mrigankCommented:
"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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrigankCommented:
Try putting the table inside the dbo schema and running
0
mrigankCommented:
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
BrianMc1958Author Commented:
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
BrianMc1958Author Commented:
What is the database schema name?  The owner name?
0
mrigankCommented:
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
BrianMc1958Author Commented:
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
BrianMc1958Author Commented:
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
mrigankCommented:
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
Giant2Commented:
>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
CEHJCommented:
>>
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
BrianMc1958Author Commented:
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
mrigankCommented:
What happens to my lawn ;).

Do not worry, I do not have one right now :).
0
BrianMc1958Author Commented:
When you get one, I'll rush right over.
0
CEHJCommented:
:-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.