[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

java sql resultSet max size

Hi all
I have following code:

 
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);
            ResultSet res1 = st1.executeQuery("SELECT CHECK_CLAUSE, CONSTRAINT_SCHEMA "
                    + "FROM INFORMATION_SCHEMA.CHECK_CONSTRAINTS "
                    + "WHERE CONSTRAINT_NAME='"+cName+"'");
            while(res1.next())
            {
                
//                java.io.InputStream ip = res1.getAsciiStream(1);
//                byte[] buff = new byte[1024];
//                String data = null;
//                for (;;) {
//                    try {
//                        int size = ip.read(buff);
//                        if (size == -1)
//                            break;
//                        data = new String(buff, 0, size);
//                    } catch (IOException ex) {
//                        Logger.getLogger(ConstraintsReader.class.getName()).log(Level.SEVERE, null, ex);
//                    }
//                }
//                 parse(data, (String)res1.getObject(2));
                parse((String)res1.getObject(1), (String)res1.getObject(2));
            }
        }
    }

Open in new window


When retrieving the constraints (res1)  I get something like: "([effctv_dir_angle]>=(0) AND [effctv_dir_angle]<=(359.9999))"  for some constraints the returned string is too long and gets cut to something like: "[effctv_dir_angle]>=(0) AND [effctv_dir_angle"

As you can see I tried to enlarge the ResultSet by setting "st1.setMaxFieldSize(0x4000)" and tried to have the response streamed without luck

Any body an idea about what I could do to resolve this problem

Thanks for helping out
th*
0
thomasbau65
Asked:
thomasbau65
  • 17
  • 13
  • +1
3 Solutions
 
Eugene ZCommented:
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
0
 
CEHJCommented:
>>
 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);

Open in new window

0
 
thomasbau65Author Commented:

@:EugeneZ
Microsoft SQL Server Management Studio Express            9.00.2047.00
Microsoft Data Access Components (MDAC)                  6.1.7600.16385 (win7_rtm.090713-1255)
Microsoft MSXML                                                    3.0 4.0 6.0
Microsoft Internet Explorer                                    9.0.8112.16421
Microsoft .NET Framework                                    2.0.50727.4961
Operating System                                                    6.1.7600

I will inquire about those links and come back
Thanks

@CEHJ
Should make no difference
0
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!

 
CEHJCommented:
>>
@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
0
 
thomasbau65Author Commented:
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);
}

Open 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.
0
 
CEHJCommented:
What type of column is the first one?
0
 
thomasbau65Author Commented:
I presume VARCHAR
0
 
CEHJCommented:
Did you try the suggestion i already made?
0
 
thomasbau65Author Commented:
Oooh yes
before I wrote this question :-)
0
 
CEHJCommented:
And what was the result?
0
 
thomasbau65Author Commented:
the same as with getObject
0
 
CEHJCommented:
In fact - let's short circuit this somewhat: please post the code you tried, along with the result
0
 
CEHJCommented:
>>the same as with getObject

You're not using getObject in your code posting - you're using getAsciiStream
0
 
thomasbau65Author Commented:
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));
            }
        }
    }

Open in new window


0
 
gordon_vt02Commented:
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));
    }

Open 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.
0
 
CEHJCommented:
What's the error with that code? You probably need to post the parse method too
0
 
CEHJCommented:
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
0
 
thomasbau65Author Commented:
@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


0
 
CEHJCommented:
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 */
            }
        }
    }

Open in new window

0
 
thomasbau65Author Commented:
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
0
 
CEHJCommented:
What value did you give for the parameter?
0
 
thomasbau65Author Commented:
I tried both the table name as well as the constraints script-id
0
 
CEHJCommented:
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?
0
 
thomasbau65Author Commented:
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 */
            }
        }
    }

Open 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
0
 
CEHJCommented:
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);
            }

Open in new window

0
 
CEHJCommented:
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();

Open in new window

0
 
thomasbau65Author Commented:
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  
0
 
CEHJCommented:
Could be, yes
0
 
thomasbau65Author Commented:
:-(
It is not !!
nor is the IOUtils class  helping

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

0
 
CEHJCommented:
So what happened when you ran http:#36917405 ?
0
 
CEHJCommented:
(obviously it's going to be at its most instructive if you restrict the query to a row of known sizes)
0
 
thomasbau65Author Commented:
non of the expert could help
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 17
  • 13
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now