Solved

JPA read mssql table constraints

Posted on 2011-09-29
3
541 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

624 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