Solved

JPA read mssql table constraints

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

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
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 covers a step-by-step guide to install VisualVM launcher in eclipse.

734 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