problem with "getAsciiStream" method

[tal]

Hi!
I am trying to retrieve some data from a db using the "getAsciiStream"
method of the ResultSet class of java language .

My code is like this:

rs = stmt.executeQuery(queryText);
InputStream in = rs.getAsciiStream(1);
boolean eof = false;
while(!eof)
i = in.read();
.
.
.
Keep reading the bytes

But the value of i is -1 and when I look at the inputStream, he is
empty!
I have tried all sorts of other versions for this code which I saw at
the internet but all the time the value of i is -1.
Can anyone help me with this?
I can't use getString method because it too slow...

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

I am trying to retrieve some data from a db using the "getAsciiStream"
method of the ResultSet class of java language .

My code is like this:

rs = stmt.executeQuery(queryText);
InputStream in = rs.getAsciiStream(1);
boolean eof = false;
while(!eof)
i = in.read();
.
.
.
Keep reading the bytes

But the value of i is -1 and when I look at the inputStream, he is
empty!
I have tried all sorts of other versions for this code which I saw at
the internet but all the time the value of i is -1.
Can anyone help me with this?
I can't use getString method because it too slow...

What database type ?

I would expect getAsciiStream only to work with
CLOB and similar database types.

I can really not see why a stream and a loop should
be faster than getString.

(since you are reading one byte at a time, then I
am rather sure that the stream and loop are slower)

If it is slow then the problem must be elsewhere.

It must be a 1000 times faster to move some bytes
(no matter how you do it) than reading data up
from a disk on the database server and send it over
the network to the application.

Arne

 

[tal]

The database is the database of the perforce,I don't know exactly
what's the type.
The getString method is very slow because it allocates every time a
very big string, and a new one for every call .In java, String
allocation is very expensive.
I don't understand why this code doesn't work.It's the same as other
codes I saw in some places in the internet.If you have an idea or other
code which should work it would be great.
When I tried to use Clob object i get an IOException on the definition
line :

Clob clobObj = rs.getClob("file");

or:

Clob clobObj = rs.getClob(1);

When rs is the ResultSet of the query (in my previous message).

Do you have any idea/example?
(the rs is not null and it's working fine with the getString method,but
still - very slow)

Thank's,
Tal.

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

The database is the database of the perforce,I don't know exactly
what's the type.

Then it is rather difficult to troubleshoot.

What JDBC driver are you using ? Something from P4 ?

The getString method is very slow because it allocates every time a
very big string, and a new one for every call .In java, String
allocation is very expensive.

It is faster to allocate a String than an InputStream.

You can create millions og Strings per second.

If you have a performance problem, then the string allocation
in getString is not the cause of it.

Arne

 

[Chris Uppal]

tal wrote:

rs = stmt.executeQuery(queryText);
InputStream in = rs.getAsciiStream(1);
boolean eof = false;
while(!eof)
i = in.read();
.
.
.
Keep reading the bytes

But the value of i is -1 and when I look at the inputStream, he is
empty!

Are you /certain/ that that column/row does actually contain a non-empty string
of bytes ? Similarly, are you /certain/ that your SQL is valid and selects
what you think it selects ?

What happens if you try getBinaryStream(), or even getCharacterStream(),
instead ?

-- chris

 

[tal]

Then it is rather difficult to troubleshoot.

What JDBC driver are you using ? Something from P4 ?


It is faster to allocate a String than an InputStream.

You can create millions og Strings per second.

If you have a performance problem, then the string allocation
in getString is not the cause of it.

Arne

I am using JdbcOdbc driver to connect to Perforce.

I can't explain it but the fact is that the getString method causing
the problem:
When I removed that line and used constant String , the process was
very fast.When I removed anything else but the getString method, i.e
for every row in the ResultSet just get the string and do nothing,it
took about 10 seconds for something like 550 rows in the ResultSet.
So I think this is the problem.

I think that inside of that method there are several big allocations
which cause this,where in InputBuffer I need to allocate memory at the
start and use the same memory at the rest of the loop,un-like getString
which allocates new String (and I think alot bigger then what I need)
every time it called.

Anyway,I still don't understand why my code doesn't work or why I can't
define Clob object - when I do so I get an IOException.

Tal.

 

[Chris Uppal]

It is faster to allocate a String than an InputStream.

You can create millions og Strings per second.

That rather depends on how big the string is. If it is in the millions (or
billions) of characters, then allocating it is going to be slow or impossible.

-- chris

 

[tal]

tal wrote:



Are you /certain/ that that column/row does actually contain a non-empty string
of bytes ? Similarly, are you /certain/ that your SQL is valid and selects
what you think it selects ?

What happens if you try getBinaryStream(), or even getCharacterStream(),
instead ?

-- chris

Hi,

I am sure that everything is fine because it is working o.k if I am
using the getString method to retrieve the data from the ResultSet.(but
working slow)
when I tried getAsciiStrean or getCharacterStream it seems that the
buffer is empty and I get -1 in the i parameter at the line :

i = in.read();

I didn't tried the GetBinaryStream because the data in the database is
VARCHAR.

Tal.

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

I am using JdbcOdbc driver to connect to Perforce.

Hm.

The JDBC-ODBC bridge has a very bad repuation.

Arne

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

That rather depends on how big the string is. If it is in the millions (or
billions) of characters, then allocating it is going to be slow or impossible.

The millions per second will not be achievable for large strings.

But the String allocation / database IO ratio should be the
same for big data.

Arne

 

[tal]

Hm.

The JDBC-ODBC bridge has a very bad repuation.

Arne

So what other options do I have ?
Tal

 

[Chris Uppal]

I didn't tried the GetBinaryStream because the data in the database is
VARCHAR.

You should still be able to try getBinaryStream().

-- chris

 

[Chris Uppal]

But the String allocation / database IO ratio should be the
same for big data.

Certainly the time taken to copy the data into the strings will be in constant
ratio with the IO no matter how big a "gulp" you take at a time[*], but the
allocation overhead itself is not constant in, or proportional to, the size of
the string -- if nothing else it'll fail for large enough strings when a stream
will still be working happily (or not, as in this case ;-)

But it appears that tal's column contains <= 32K in each row (for that column)
so I agree that's unlikely to be the problem here.

-- chris

[*] Assuming it doesn't require multiple re-copying of the input, as with a
StringBuffer, but I don't think it does in this case.

 

[Chris Uppal]

I am using JdbcOdbc driver to connect to Perforce.

Then there are two obvious possibilities. One is that the JDBC/ODBC bridge is
(as Arne suggested) showing one of ways in which it is not suitable for
production use (it isn't /intended/ to be suitable for production use). The
other is that the ODBC driver from Perforce is not up to this -- since Perforce
isn't a database product in any meaningful sense, I would not be surprised to
find that they have given ODBC access little attention beyond what's needed to
make Crystal Reports work.

Taking a look at the source for JDBC/ODBC bridge, it appears that it's own
implementation of getString() uses getAsciiCharacters() internally (at least it
does if the column contains > 32767 bytes of data -- which I assume yours
does, or else there'd be little or nothing to gain by using the stream
interface in the first place). One possible source of oddness, is that if a
stream is used (either internally or because you have asked for it explicitly)
then it'll read the data in binary mode at the ODBC level, and do whatever
conversions are necessary itself. If you do getString() /and/ the amount of
data is small enough to avoid the streaming, then it'll drive IDBC is
character mode -- possibly that is confusing Perforce.

Perforce is not cheap. Presumably the price includes support -- get onto their
support department and have them find the problem.

-- chris

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

I can't explain it but the fact is that the getString method causing
the problem:
When I removed that line and used constant String , the process was
very fast.When I removed anything else but the getString method, i.e
for every row in the ResultSet just get the string and do nothing,it
took about 10 seconds for something like 550 rows in the ResultSet.
So I think this is the problem.

I think that inside of that method there are several big allocations
which cause this,where in InputBuffer I need to allocate memory at the
start and use the same memory at the rest of the loop,un-like getString
which allocates new String (and I think alot bigger then what I need)
every time it called.

Have you tried getBytes and creating the String yourself ?

Arne

 

[tal]

no, I will try it, thanks!
Tal.