Link to home
Start Free TrialLog in
Avatar of mrb121212
mrb121212

asked on

problem with ResultSet.getString() ?

Hi fellows,
IS there a specific reason why the following code would return a wrong value ?:
testStatement =
testConn.prepareStatement(
" SELECT distinct blob " +
" FROM blobtable where  blob2=

testStatement.setString(1, globalVariable1);
resultset = testStatement.executeQuery();

while(resultset.next())
{
   globalVariable2 = rs.getString(1);
}

// when I debug for globalVariable2, i get something like: CorrectString   IncorrectString
i.e. BBALL   LE
where the IncorrectString is a certain portion of globalVariable1


any thoughts? is there an unknown bug in here or is it a synch. issue?

appreciate any help you guys can provide.

mrb
Avatar of girionis
girionis
Flag of Greece image

You shouldn't have any problems. What happens if you run the code in a SQL client? Do you get the expected result?
Avatar of mrb121212
mrb121212

ASKER

Yes , i do get the expected result. there seems to be a problem with the statement resultset.getString(1); it returns a faulty string in Java.
u r saying that it is dintict blob in select statement.

s that blob object or string??
if it is bob object
follow the example below

try {
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT col_blob FROM mysql_all_table");
   
        if (rs.next()) {
            // Get the BLOB from the result set
            Blob blob = rs.getBlob("col_blob");
   
            // Get the number bytes in the BLOB
            long blobLength = blob.length();
   
            // Get bytes from the BLOB in a byte array
            int pos = 1;   // position is 1-based
            int len = 10;
            byte[] bytes = blob.getBytes(pos, len);
   
            // Get bytes from the BLOB using a stream
            InputStream is = blob.getBinaryStream();
            int b = is.read();
        }
    } catch (IOException e) {
    } catch (SQLException e) {
    }

Regards
Sudhakar
Oh sorry, i didn't think of BLOBs; no, it is JUST a String. not BLOB object. any old String.
So column blob is a char(8) column. hence resultset.getString(1).
Thanks  for bringing that up.

Can you give us a bit more info?

What value do you pass as globalVariable1?
What is the expected output?
What is the value of globalVariable2 after you read the result set?

I am a bit confused with all these IncorrectString and CorrectString.
Is there possibly a null byte embedded in your db column?

That may cause it to prematurley shorten the expected value when it is a String type.
beermequik,
thanks for the suggestion however,
it doesn't prematurely shorten the expected value. It gives me exactly what I need plus part of some other resultset.getstring.
girionis,
globalVariable1 value i pass is a string say "abcdef"
the output of the resultset.getstring(1) would be not  anything like the "abcdef" that globalVariable1 is.
after I read the result set in java, globalVariable2 is like  "blob    ef"
                 if globalVariable1 was "12345"
                    globalVariable2 would have been like "try   5"
Does that make more sense?
I realize it is a stupid error to have.. but it happens...
oops forgot  to say : expectedoutput for first example should be "blob"
second example should be "try"
> if globalVariable1 was "12345"
>                     globalVariable2 would have been like "try   5"

and what is it instead?
read previous comment, i forgot to add it. but i entered it right after i posted, sorry.
"try    5" is still the wrong output. correct output is "try"
Ok what happens if you try to get the string by using the column's name?

resultset.getString(<column's name that holds the result>);
Same incorrect thing.... i get "try    5" kind of result, instead of "try"
Weird... Can you post a bit more code? Also can you make sure that you read the correct value?
Unfortunately i can't post any more code because it is part of a very complex system with a lot of connections to the database and alot of object classes.
I do read the correct value from the database as I triple checked the query in the dbms-provided-command prompt for accessing the database. ... it is frustrating. the dmbs itself returns the correct value. it seems like the driver is not returning the correct value when resultset.getstring is used in this case...
all i can say about the code is that it is pretty much how long the method is and  resultset variable is a local var. this method gets called from another class to generate a result to be used by another class.
I appreciate the help you provided in trying to resolve the issue.
What DB are you using.....(Might be a silly question, but I catch myself on this one every once in a while.....Are you trying to read an uncommitted change?   Looking at thr right database in your code?)

Can you tell us what database and driver you are using?
database is db2 , driver jdbc driver for db2 i believe.
Is it possible to try another driver and see if you get the same results? ot sure if thsi wil lhelp but it will be good to eliminate the "driver's fault" option.
beermequik, yes I double and triple checked the database, i m looking at the correct one. the thing that bothers me is that the incorrect part of the output value is part of a previous result set. which is also the globalVariable1.
> the thing that bothers me is that the incorrect part of the output
> value is part of a previous result set. which is also the globalVariable1.

Can you elaborate on that?
>> the thing that bothers me is that the incorrect part of the output
>> value is part of a previous result set. which is also the globalVariable1.

>Can you elaborate on that?
globalVariable1 = "12345"
globalVariable2 incorrectly is "try   5" instead of just "try" globalVariable1 gets set via interaction with the DB before this function is called. in that previous function globalVariable1 is assigned with a result of another resultset.getstring(x) x being whatever number it maybe.
if globalVariable1 were ="abcdef"
then globalVariable2 is incorrectly "try    ef" instead of just "try".
if globalVariable1 were ="shoot"
then globalVariable2 is incorrectly "try    t" instead of just "try"
This is weird. Does the value "try   5" exist somewhere in the database at all or is it just thrown there somehow?
"try    5" does NOT exist in database anywhere ... maybe just in the metatable that the result set is creating. I dont know if thats in the actual database or in memory.
yup, this is a very stupid error to have...
I am really stumped... Have you tried using a different driver?
Is there any manual that says what is the recommended corresponding java data type for char(n) db2 datat type? This link says it is a String indeed :http://publib.boulder.ibm.com/infocenter/db2v8luw/index.jsp?topic=/com.ibm.db2.udb.doc/ad/rjvjdata.htm but better check it again.
hi mrb121212,

Apparently the new values overwrite the older values. So your new string is at the same memory location as your older one. The one space in between indicates a terminating character over there.

Are you sure that you are closing any open resultsets and open preparedstatements before you use new ones???
Try closing everything(Even the connection if above doesn't work, although it should) before you again query thru jdbc.
Strings are immutable they shouldn't be overriding other values.

> The one space in between indicates a terminating character over there.

I noticed that too but there isn't just one space but several of them.
Hope you are not using native code with some pointers.. and if there are some native libs' please make sure your ptrs' are well allocated and deallocated.

Which Java version are you using ?
kumvjuec, i have been tending to think the same thing since this morning when I realized the problem. I looked and looked but didnt see any explanation of how getString and setString work memory-wise.
girionis, i think he means in the memory not in reality. if the space the setString is used , and 2 lines after another method is called by rule of locality, same or a close memory location will be allocated for the new routine's use.
as far as driver's are concerned, i don't think i can use a different driver. the whole system, over 1TB of data, has been working normally before we added this new entries of rows into the system.
vishalbhando,
using java sdk 1.3.1
>> Are you sure that you are closing any open resultsets and open preparedstatements before you use new ones???

I am going thru all the 2000+ result sets to make sure; that is one of my other concerns.
> f the space the setString is used , and 2 lines after another method
> is called by rule of locality, same or a close memory location will be
> allocated for the new routine's use.

So this is a good point to look at. Can you take a look and see if you set the same String in the same prepared statement a few lines below the code (or anywhere actually if you do not close the prepared statement)? I do not think this is the problem but we loose nothing to check it.
we close the result set promptly after.
i do check the rs.getString right before the result set is closed and it returns the incorrect string still in the debugging.
Well I am pretty much stumped. I'd say change driver and try again.
well, it would be much better if you post some code before and after your troubling code..
alright, thanks for trying to help though. i really appreciate it.
kumvjuec,
that is the essence of the method that has trouble. i do not know where it gets called from , that is basically the whole code in 1 method ...
It shouldbe called from somewhere. Debug it and you will see who is calling it. Also can you psot gaain the code that fails? I cannot make loads of things from your original code since it is missing a few things.
oh yea, the code is missing the rest of the query for some reason from my question statement up top.
// method begin
// initialize local vars
// resultset, etc
// ...
         testStatement =
         testConn.prepareStatement(
         " SELECT distinct col1 " +
         " FROM table1 where col2 = ?");
         testStatement.setString(1, globalVar1);
         tempString=new String("");
         resultset = testStatement.executeQuery();

         tempString = rs.getString(1).trim();
         isFound = resultset.next();
//       debug code to file
         if (isFound)
         {
            tempString = resultset.getString(1);
            setGlobalVar1(tempString);
         }
//     debug code to file
         resultset.close();
         testStatement.close();
//endof method.
Can you tel lus the output of this:

testStatement = testConn.prepareStatement(" SELECT distinct col1 " + " FROM table1 where col2 = ?");
System.out.println("globalVar1: " + globalVar1);
testStatement.setString(1, globalVar1);
tempString=new String("");
System.out.println("1st tempString: " + tempString);
resultset = testStatement.executeQuery();

         tempString = rs.getString(1).trim();
System.out.println("2nd tempString: " + tempString);
         isFound = resultset.next();
//       debug code to file
         if (isFound)
         {
            tempString = resultset.getString(1);
            System.out.println("3rd tempString: " + tempString);
            setGlobalVar1(tempString);
         }
//     debug code to file
         resultset.close();
         testStatement.close();
please ignore the line above System.out.println("2nd tempString: " + tempString);
that is a mistake, i was shuffling around the code, forgot to take that out. good ol' runtime error made me take it out in a hurry.
so ignoring that one :
1st tempString: ""  -obviously, i m putting the "" s in.
3rd tempString: "try    5"
the culprit is rs.getString(1) - as i believe. cuz i print out rs.getString(1) to our log file and it is "try    5".
SOLUTION
Avatar of girionis
girionis
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
same thing... it IS weird.
Hi mrb121212,
Why don't you try to use a Statement and not a PreparedStatement? It seems like you are setting the ? to a global variable that you don't change. This would eliminate the problem with principle of locality if indeed that is the case with the .setString(1) and .getString(1).
please elaborate.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thats a working work around for the issue. i ll increase points and split it between girionis & oytun12. still no clue why it happens though. thanks guys ,esp. girionis who has been in this since yesterday morning.
Finally!!! That was a weird problem, glad it is solved :) (and I was always being told to use a PreparedStatement instead of Statement hehe).
yeah it is probably a WEIRD bug in the driver code that only occurs when weird scenarios are created.