java sql resultSet max size

what driver are you using to access sql server?
what is your sql server version?
check thiese  articles :
http://msdn.microsoft.com/en-us/library/ms378813.aspx
http://msdn.microsoft.com/en-us/library/ms378599.aspx

>>
 for some constraints the returned string is too long and gets cut to something like: "[effctv_dir_angle]>=(0) AND [effctv_dir_angle"
>>

Why aren't you just doing the following?

String checkClause = res1.getString(1);

Select allOpen in new window

>>
@CEHJ
Should make no difference
>>

It will make all the difference ;)

a. you're not reading all the value
b. you're making reading the value unnecessarily complex

Well I'm not sure I understood all this correctly
I tried following thing:
 

Clob b = res1.getClob(1);
//b.getAsciiStream()getCharacterStream()
 //java.io.InputStream ip = res1.getAsciiStream(1);
Reader reader = b.getCharacterStream();
StringBuilder buffer = new StringBuilder();
char[] buff0 = new char[24576];
try {
    while(reader.read() > 0)
    {
        reader.read(buff0);
        buffer.append(new String(buff0, 0, buff0.length));
    }
} catch (IOException ex) {
    Logger.getLogger(ConstraintsReader.class.getName()).log(Level.SEVERE, null, ex);
}

Select allOpen in new window

And come to the conclusion that the driver (jTDs) must be the trouble maker, because the buffer is really large enough and when run in the debugger mode I can see that it has been instantiated in the defined size. Additionally the while statement iterates just once.

What type of column is the first one?

Did you try the suggestion i already made?

Oooh yes
before I wrote this question :-)

And what was the result?

the same as with getObject

In fact - let's short circuit this somewhat: please post the code you tried, along with the result

>>the same as with getObject

You're not using getObject in your code posting - you're using getAsciiStream

Ok
 
Here ist the connection:
Connection conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost/MYDB", "sa", "password");

Try with more properties:
Connection conn = DriverManager.getConnection("jdbc:jtds:sqlserver://localhost/MYDB; bufferMaxMemory=12288; lobBuffer=16384", "sa", "password");

And here the method reading the resultset
 

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();
            st1.setMaxFieldSize(0x4000);
            String sql = "SELECT CHECK_CLAUSE, CONSTRAINT_SCHEMA "
                    + "FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS "
                    + "WHERE CONSTRAINT_NAME='"+cName+"'";
            st1.setCursorName(sql);
            ResultSet res1 = st1.executeQuery(sql);
            
            while(res1.next())
            {
                /*
                 * Try Blob and Clob
                 */
                Clob b = res1.getClob(1);
                Reader reader = b.getCharacterStream();
                StringBuilder buffer = new StringBuilder();
                char[] buff0 = new char[24576];
                try {
                    while(reader.read() > 0)
                    {
                        reader.read(buff0);
                        buffer.append(new String(buff0, 0, buff0.length));
                    }
                } catch (IOException ex) {
                    Logger.getLogger(ConstraintsReader.class.getName()).log(Level.SEVERE, null, ex);
                } 
                parse(buffer.toString(), res1.getString(2));
                /*
                 * Try with ascii stream
                 */
                java.io.InputStream ip = res1.getAsciiStream(1);
                byte[] buff1 = new byte[12288];
                String data = null;
                for (;;) {
                    try {
                        int size = ip.read(buff1);
                        if (size == -1)
                            break;
                        data = new String(buff1, 0, size);
                    } catch (IOException ex) {
                        Logger.getLogger(ConstraintsReader.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
                parse(data, res1.getString(2));
                /*
                 * Try with object
                 */
                parse((String)res1.getObject(1), (String)res1.getObject(2));
                /*
                 * Try with String
                 */
                parse(res1.getString(1), res1.getString(2));
            }
        }
    }

Select allOpen in new window

The code you posted with the CLOB will skip characters in the stream.  Every time your while conditional (reader.read() > 0) is checked, you lose one character.  That loop should be:

int readCount;
while((readCount = reader.read(buff0)) > 0)
    {
        buffer.append(new String(buff0, 0, readCount));
    }

Select allOpen in new window

Your append was also incorrect as it would always append the full character buffer to your output string regardless of how many characters you actually read in.  Not sure that will help with your original problem, but it will be a better test of the CLOB.

What's the error with that code? You probably need to post the parse method too

Yes of course, gordon is right - which is what i meant earlier by the fact you're not reading the whole field (in two different way and two different places). Also

a. you should be using a join
b. you should be closing your db resources
c. you should be using getString (mentioned earlier) - no reason that i can see for getting clobs or streams

@gordon_vt02
I corrected the while statement
Still the returned string is incomplete

@CEHJ
The parse method is ok and working for all other CHECK_CLAUSE
Adding this would only add noise and will not contribute to a solution finding

Yes the JOIN would be a better way, still running the statement
"SELECT CHECK_CLAUSE, CONSTRAINT_SCHEMA "
                    + "FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS "
                    + "WHERE CONSTRAINT_NAME=constraintID'"
 alone (without the primary one) is not resolving the problem. I still receive only a small part of the 111k Bytes I should receive

Try the following, and if it's still incomplete, it can be tweaked:

public void getConstraints(String tableName) throws SQLException {
        PreparedStatement st = null;
        ResultSet res = null;
        String sql = "select" + "CC.CHECK_CLAUSE, CC.CONSTRAINT_SCHEMA" +
            "from INFORMATION_SCHEMA.TABLE_CONSTRAINTS C" + "inner join" +
            "INFORMATION_SCHEMA.CHECK_CONSTRAINTS CC" + "" +
            "on C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME" +
            "where C.TABLE_NAME = ?";

        try {
            st = conn.prepareStatement(sql);
            st.setString(1, tableName);
            res = st.executeQuery();

            while (res.next()) {
                parse(res.getString(1), res.getString(2));
            }
        } finally {
            try {
                res.close();
            } catch (Exception e) { /* ignore */
            }

            try {
                st.close();
            } catch (Exception e) { /* ignore */
            }
        }
    }

Select allOpen in new window

I tried it but the sql statement

"select CC.CHECK_CLAUSE, CC.CONSTRAINT_SCHEMA " +
            "from INFORMATION_SCHEMA.TABLE_CONSTRAINTS C inner join " +
            "INFORMATION_SCHEMA.CHECK_CONSTRAINTS CC " +
            "on C.CONSTRAINT_NAME = CC.CONSTRAINT_NAME " +
            "where C.TABLE_NAME = ?";

dose not retrieve any values from my db

What value did you give for the parameter?

I tried both the table name as well as the constraints script-id

Well i can't see anything wrong with the sql, but i can't test it as i don't have Windows. Maybe someone else can?

Lets just concentrate on the real problem, which is

running following sql-statement=

"SELECT CHECK_CLAUSE, CONSTRAINT_SCHEMA "
                    + "FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS "
                    + "WHERE CONSTRAINT_NAME=constraintID'"
 
I still receive only a small part of the 111k Bytes I should receive

I can also state that the parser code as well as the connection inclusive closing of resultset an statement is ok

running this cosde
 

public void getConstraints(String tableName) throws SQLException {
        tName = tableName;
        PreparedStatement st = null;
        ResultSet res = null;
        
        String sql = "SELECT CHECK_CLAUSE, CONSTRAINT_SCHEMA "
                        + "FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS "
                        + "WHERE CONSTRAINT_NAME=?";

        try {
            st = conn.prepareStatement(sql);
            st.setString(1, tableName);
            res = st.executeQuery();

            while (res.next()) {
                parse(res.getString(1), res.getString(2));
            }
        } finally {
            try {
                res.close();
            } catch (Exception e) { /* ignore */
            }

            try {
                st.close();
            } catch (Exception e) { /* ignore */
            }
        }
    }

Select allOpen in new window

will crash the parser because of splitting on "[" and "]" and since the string is cut of and some point the parse method fail

OK - good idea. So

>>will crash the parser

you mean it DOES crash the parser?

Can you tell me what this prints?

while (res.next()) {
                String s1 = res.getString(1);
                String s2 = res.getString(2);
                System.out.printf("s1 length = %d, s2 length = %d\n", s1.length(), s2.length());
                parse(s1, s2);
            }

Select allOpen in new window

Once it's been established definitely that even the simple code above fails to read the value(s) completely, you can try the following: (see http://technojeeves.com/joomla/index.php/free/51-copying-streams for IOUtils source)

java.io.StringWriter out = new java.io.StringWriter();
	net.proteanit.io.IOUtils.copyReader(res.getCharacterStream(1), out);
        String s1 = out.toString();

Select allOpen in new window

The error of the parse method has nothing to do with the problem as such.
The parse error comes from the fact that the data retrieving is not proper. (I'm expecting pair of brackets and at some point only get one)
I can see that when debugging
The problem lies on the driver side or beyond
I guess I will try the mssql driver instead of jtds  

Could be, yes

:-(
It is not !!
nor is the IOUtils class  helping

may be I have to file a bug report to microsoft :-)

So what happened when you ran http:#36917405 ?

non of the expert could help