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
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,
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
You shouldn't have any problems. What happens if you run the code in a SQL client? Do you get the expected result?
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??
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
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
ASKER
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.
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.
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.
That may cause it to prematurley shorten the expected value when it is a String type.
ASKER
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.
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.
ASKER
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...
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...
ASKER
oops forgot to say : expectedoutput for first example should be "blob"
second example should be "try"
second example should be "try"
> if globalVariable1 was "12345"
> globalVariable2 would have been like "try 5"
and what is it instead?
> globalVariable2 would have been like "try 5"
and what is it instead?
ASKER
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"
"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(<colum n's name that holds the result>);
resultset.getString(<colum
ASKER
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?
ASKER
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.
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?
ASKER
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.
ASKER
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?
> value is part of a previous result set. which is also the globalVariable1.
Can you elaborate on that?
ASKER
>> 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"
>> 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?
ASKER
"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...
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.
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.
> 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 ?
Which Java version are you using ?
ASKER
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.
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.
ASKER
vishalbhando,
using java sdk 1.3.1
using java sdk 1.3.1
ASKER
>> 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.
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.
> 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.
ASKER
we close the result set promptly after.
ASKER
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..
ASKER
alright, thanks for trying to help though. i really appreciate it.
ASKER
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 ...
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.
ASKER
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.
// method begin
// initialize local vars
// resultset, etc
// ...
testStatement =
testConn.prepareStatement(
" SELECT distinct col1 " +
" FROM table1 where col2 = ?");
testStatement.setString(1,
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("global Var1: " + 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();
testStatement = testConn.prepareStatement(
System.out.println("global
testStatement.setString(1,
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();
ASKER
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".
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).
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).
ASKER
please elaborate.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).
ASKER
yeah it is probably a WEIRD bug in the driver code that only occurs when weird scenarios are created.
:)