Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.databases > #244

Difference in JDBC result

Show all headers | View raw

  To: comp.lang.java.databases
We're using jTDS to connect to a backend SQL Server 2000 instance. The
connection is working fine.

What we want to do is write a utility that will calculate maximum row
size for each table in a given database. The script we're using looks
something like this:

Connection con = Connector.getConnection(); //this works fine and has
been verified...
String query = "SELECT * FROM " + table; //general query to get
metadata...table is provided as argument...
PreparedStatement st = con.prepareStatement(query);
ResultSet rs = st.executeQuery();
ResultSetMetaData meta = rs.getMetaData();
int cols = meta.getColumnCount();
String query2 = "SELECT MAX(LEN(?)) AS Maximum FROM " + table;
PreparedStatement st2 = con.prepareStatement(query2);
int totalSize = 0;
for (int i = 1; i <= cols; i++) {
	String colName = meta.getColumnName(i);
	st2.setString(1, colName);
	ResultSet rs2 = st2.executeQuery();
	if (rs2.next()) {
		int fieldSize = rs2.getLong("Maximum");
		totalSize += fieldSize;
		out.println("<blockquote>" + colName + ": " + fieldSize + "</
blockquote>");
	}
	rs2.close();
}
st2.close();
rs.close();
st.close();
con.close();
out.println("Maximum total row size = " + (totalSize));

When I run this application against a particular table, I get the
following results:

Getting metadata for table WF_Approval_Methods

id: 2
type: 4
name: 4
description: 11

Maximum total row size = 21

All would appear to be fine, with one big problem...When I run the
following query in Query Analyzer against the same table I get a
different value:

SELECT MAX(LEN(id)) AS Maximum FROM WF_Approval_Methods

returns a value

36.

Every other value is also incorrect. When I run the queries in
succession I get the following values:

id: 36
type: 1
name: 8
description: 34

With a total of 79, much different than my generated total of 21.

Any ideas what I'm doing wrong here?

Thanks.

---
 * Synchronet * The Whitehouse BBS --- whitehouse.hulds.com --- check it out free usenet!
--- Synchronet 3.15a-Win32 NewsLink 1.92
Time Warp of the Future BBS - telnet://time.synchro.net:24

Back to comp.lang.java.databases | Previous | Next — Next in thread | Find similar

Thread

Difference in JDBC result "Tom Cole" <tom.cole@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
  Re: Difference in JDBC re "Martin Gregorie" <martin.gregorie@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
    Re: Difference in JDBC re "Tom Cole" <tom.cole@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
      Re: Difference in JDBC re "Silvio Bierman" <silvio.bierman@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
        Re: Difference in JDBC re "Tom Cole" <tom.cole@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000
  Re: Difference in JDBC re "Martin Gregorie" <martin.gregorie@THRWHITE.remove-dii-this> - 2011-04-27 15:22 +0000

csiph-web