Solved

JPA read mssql table constraints

Posted on 2011-09-29
3
529 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
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…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
The viewer will learn how to implement Singleton Design Pattern in Java.

778 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