Strings in resultset limited in length?

[Ryan Kostrzewa]

Hey all! First time poster here.

I'm pulling Strings to populate a dropdown menu from an Oracle database. The catch is, the Strings in the database are kind of long (~30-40 characters). This doesn't seem like a problematic length to me. Anyway, when I run a SQL query on the database outside of the my web app, it returns the strings as I expect them, but when I use a preparedStatement and execute it in my web application, the strings in the resultSet are truncated down to 20 characters. Is there an inherent limitation in the resultSet data type that does this? Could it be something with the JDBC driver? Does this question even make sense?

Example:

In Oracle database (stored as varchar2)

ID | Name
-------------------
1 | Once upon a time there were some small worms
2 | who got very very annoyed and decided to
3 | go to arms in order to wipe out their vicious
4 | enemy counterparts. they developed some really


ResultSet:

"Once upon a time the"
"who got very very an"
"go to arms in order "
"enemy counterparts. "

Much thanks!

[James Boswell]

Can you show us the code that generated this result set?

[Ryan Kostrzewa]

Sure, here's the code

	public Map<String, String> getDdVals(String menuName) {
                                Map<String, String> map = new HashMap<String, String>();
		Connection conn = null;
		PreparedStatement ps = null;
		try{
			conn = getDRAConnection();
			ps = conn.prepareStatement("SELECT VALUE,MENU_NAME FROM KATET_DROPDOWN_VALUES dv,KATET_DROPDOWN_MENUS dm Where dm.MENU_ID = dv.MENU_ID and dm.MENU_NAME = ?");
			ps.setString(1,menuName);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				String value = rs.getString("VALUE");
				System.out.println(value);
				map.put(value, value);
			}
		} catch (Exception e) {
		} finally {
			try{
				if(ps != null){
					ps.close();
				}
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
			
			}
		}

		return map;

The values printed by that System.out.println statement are:

Mission Critical Tie
Mission Critical Tie
Essential Tier 3&4 (
Essential Tier 5 (72
Required Tier 6&7 (1
Deferrable (>30 days

and the original database values can be seen in the attached screenshot. (Edit: As noted before, these are varchar2 datatype in the database.

Thanks!

[Jeanne Boyarsky]

Ryan,
Welcome to CodeRanch!

That's really weird. JDBC doesn't cut off String values. (At least not at 30-40 characters. Strings larger than 255 characters might need to use a CLOB depending on the database/driver.) You aren't anywhere near that though.

In the spirit of gathering more data, can you create a column that is 10 characters longer with the same fields. Knowing whether it cuts off at the same point or a different point might be a clue. This is really puzzling so any clue could be helpful.

[Ryan Kostrzewa]

Thanks Jeanne!

Well, today has certainly been interesting.

I added another column, TEST, and populated it with the same values and a string of 10 "a" characters appended at the end. I performed an exact copy of the SQL query, changing only the column name and compared the output. For the TEST column, System.out.println produced the entire string. For the original column, it was still truncated.

I was browsing through the code and for kicks, decided to null out the ResultSet and declare it at the top with the Connection and the PreparedStatement. When I rebuilt and ran, I got a SQL exception saying there weren't enough data connections in the pool, so I went into Weblogic and added a higher maximum capacity on the Connection Pool (although I'm concerned about how it reached the maximum in the first place...another problem for another day). When I ran it again...my outputs in the ResultSet were magically fixed.

All in all, I have no idea what I did, but I'm inclined to think there was something screwy going on in the Data Source connection through the web server. My only regret is that I can't post a definitive "this fixed my problem".

Thanks for the help, ya'll!

[Jeanne Boyarsky]

Wow. That *is* weird. I'm glad you got passed it. And I'm extra glad you posted the "solution." Even though it isn't 100% on the cause, it suggests something others can try if they run into the sample problem. And for that, you get a cow.

[Ryan Kostrzewa]

My first cow!

[Dave Tolls]

I know this is a few days late, but your catch blocks are empty.
You should log any exception you are getting.
For example, if there's a problem closing the PreparedStatement you won't know about it, along with the side effect of the Connection never being returned to the pool.
In essence, there could be something going on under the hood that you have no idea about as there's no logging.

[chris webster]

You might want to look at using try-with-resources for your DB stuff as well.

[Ryan Kostrzewa]

Late reply here, but thanks Chris and Dave for the tips. Will do. I haven't had much formal teaching in this domain; I'm almost entirely self taught, so there's some significant gaps in my knowledge (i.e. error handling, as you've just mentioned). Thanks for the pointers