Solved

JPA read mssql table constraints

Posted on 2011-09-29
3
524 Views
Last Modified: 2012-08-13
Hi
I have a MSSQL-DB which I connect with JPA (EclipseLink)
I 'd like to write data to one table which has (when using "MS SQL Server Manager Express" found in the folder Constraints/SomeID) constrains.
Is ist possible to read, using JPA, the values or the SQL-statement stored in the file "SomeID"?
Is there a way to retrieve information from the client side regarding table constraints?
Thanks for helping out
th*
0
Comment
Question by:thomasbau65
  • 2
3 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 36816186
you should be able to query for information in the Information_schema tables for information on constraints etc...

  e.g. select * from information_schema.check_constraints
           where table_schema='dbo' and table_name = 'xyz'

here is a list of the available table names


CHECK_CONSTRAINTS
REFERENTIAL_CONSTRAINTS
COLUMN_DOMAIN_USAGE
ROUTINES
COLUMN_PRIVILEGES
ROUTINE_COLUMNS
COLUMNS
SCHEMATA
CONSTRAINT_COLUMN_USAGE
TABLE_CONSTRAINTS
CONSTRAINT_TABLE_USAGE
TABLE_PRIVILEGES
DOMAIN_CONSTRAINTS
TABLES
DOMAINS
VIEW_COLUMN_USAGE
KEY_COLUMN_USAGE
VIEW_TABLE_USAGE
PARAMETERS
VIEWS
0
 
LVL 1

Author Comment

by:thomasbau65
ID: 36915312
Hi
your statement did not work for me, but gave me the hint to where I had to look for a solution
(table_name dose not exist in  "information_schema.check_constraints" ) on my mssql-server

here my solution:

 
public void getConstrains() throws SQLException
    {
        Statement st0 = conn.createStatement();
        ResultSet res0 = st0.executeQuery("SELECT CONSTRAINT_NAME "
                    + "FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS "
                    + "WHERE TABLE_NAME = '"+tName+"'");
        while(res0.next()){
            String cName = (String)res0.getObject(1);
            
            Statement st1 = conn.createStatement();
            ResultSet res1 = st1.executeQuery("SELECT CHECK_CLAUSE, CONSTRAINT_SCHEMA "
                    + "FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS "
                    + "WHERE CONSTRAINT_NAME='"+cName+"'");
            while(res1.next())
            {
                
                String const = res1.getString(1)
		String schema= res1.getString(2);
            }
        }
    }

Open in new window

0
 
LVL 1

Author Closing Comment

by:thomasbau65
ID: 36915321
The statement is not correct:
table_name
dose not exist in
information_schema.check_constraints
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This article is about some of the basic and important steps to be used to improve the performance in web-sphere commerce application development. 1) Always leverage the Dyna-caching facility provided by the product 2) Remove the unwanted code …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now