Thread: crystal reports varchar = string length 0 in jdbc but correct when jdbc-odbc bridge
crystal reports varchar = string length 0 in jdbc but correct when jdbc-odbc bridge
From
Klint Gore
Date:
I've just started with crystal reports 10 and java based connections.
My problem is that when I use the postgres jdbc driver, my varchar's all
come out as string fields of length 0. If I use the sun jdbc-odbc
bridge, then they come out the correct length. int4, int2, timestamp,
date all come out correct on either driver.
Anyone got any suggestions? Using java to walk the table writing values
can read the values ok (code below). Has anyone got a class to show the
metadata for a table?
Using the JDBC driver, the javabean looks like this (the things in []
have been changed from their values) which appears to be correct as it
works for non string types. I switch between the 2 connection methods
by commenting out the appropriate forName and getConnection.
import java.sql.*;
public class PostreSQLDataSourceBean {
private ResultSet resultSet = null;
private Connection con = null;
private String urlj = "jdbc:postgresql://[ip]/[dbname]";
private String urlo = "jdbc:odbc:[dsnname]";
private String JDBCBridge = "sun.jdbc.odbc.JdbcOdbcDriver";
private String JDBCConName = "org.postgresql.Driver";
private String sqlQuery = "select * from pg_class";
public PostreSQLDataSourceBean() {
try {
//Create a connection to database JDBC->ODBC
Class.forName(JDBCBridge);
con = DriverManager.getConnection(urlo, [user], [pass]);
//Create a connection to database JDBC
// Class.forName(JDBCConName);
// con = DriverManager.getConnection(urlj, [user], [pass]);
}
catch (ClassNotFoundException e) {
System.err.println( "Driver not found: " + e + "\n" + e.getMessage() );
}
catch (SQLException e) {
System.out.println("SQL Exception #" +
e.getErrorCode() + " : " + e.getLocalizedMessage());
e.printStackTrace();
}
}
public ResultSet getResultSet() throws
java.sql.SQLException {
//Create an SQL statement to execute
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
//Execute the select statement to populate the ResultSet
resultSet = stmt.executeQuery(sqlQuery);
return resultSet;
}
}
I checked the data in java with the following code
int numCols = resultSet.getMetaData().getColumnCount();
// print out the contents of the ResultSet
while (resultSet.next()) {
for (int j=1; j <= numCols; j++)
System.out.print(resultSet.getString(j) + " ");
System.out.println();
}
versions
jre j2re1.4.2_03
jsdk j2sdk1.4.2_03
jdbc pg74jdbc3.jar (downloaded yesterday 12th feb)
postgres 7.4 (not 7.4.1)
all the java is on my local w2k, the pg server is network rh 7.2 linux.
klint.
+---------------------------------------+-----------------+
: Klint Gore : "Non rhyming :
: EMail : kg@kgb.une.edu.au : slang - the :
: Snail : A.B.R.I. : possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---------------------------------------+-----------------+
On Fri, 13 Feb 2004, Klint Gore wrote: > I've just started with crystal reports 10 and java based connections. > My problem is that when I use the postgres jdbc driver, my varchar's all > come out as string fields of length 0. If I use the sun jdbc-odbc > bridge, then they come out the correct length. int4, int2, timestamp, > date all come out correct on either driver. > > Anyone got any suggestions? Using java to walk the table writing values > can read the values ok (code below). Has anyone got a class to show the > metadata for a table? See DatabaseMetaData.getColumns > > Using the JDBC driver, the javabean looks like this (the things in [] > have been changed from their values) which appears to be correct as it > works for non string types. I switch between the 2 connection methods > by commenting out the appropriate forName and getConnection. All the code you've showed us does is create a connection, open a result set and print it. You also note that this code works. The interesting code would be: the table definitions, the sql query, and the actual printing code used. Depending on how crystal reports works these may not be easy things to get to, but you'll have to give us something more to work with. Kris Jurka
On Thu, 12 Feb 2004 22:15:12 -0500 (EST), Kris Jurka <books@ejurka.com> wrote: [snip] > > See DatabaseMetaData.getColumns > [snip] > > All the code you've showed us does is create a connection, open a result > set and print it. You also note that this code works. The > interesting code would be: the table definitions, the sql query, and > the actual printing code used. Depending on how crystal reports works > these may not be easy things to get to, but you'll have to give us > something more to work with. That's why I wanted to know if anyone had a class to write out the meta data. Anyway I wrote my own and it turns out to be precision. The jdbc-odbc bridge returns the width of text fields in the precision property (resultSet.getMetaData().getPrecision(n)) were the postgresql jdbc driver returns 0. Also the date field returns the displaywidth in precision in the bridge but postgresql jdbc driver returns 0. crystal recognizes the date and deals with them properly though. klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
On Fri, 13 Feb 2004, Klint Gore wrote: > That's why I wanted to know if anyone had a class to write out the meta > data. Anyway I wrote my own and it turns out to be precision. The > jdbc-odbc bridge returns the width of text fields in the precision > property (resultSet.getMetaData().getPrecision(n)) were the postgresql > jdbc driver returns 0. That seems like a bug in crystal reports if it is using getPrecision, that seems like it should only work for numbers (as it does in pg). I would have thought it would use getColumnDisplaySize. Kris Jurka