JDBC - ODBC getBinaryStream returns only 255 characters from MS Access Memo field

Hi,

I am trying to get a memo field value from MS Access using a getBinaryStream. At the moment it will only return 255 characters - no more than a standard getString.
The memo field value has 260 characters. Basically, the code returns the first 255 then returns -1.
I am using JDK1.3 with Jbuilder7. MS Access 2000 database format. The JDBC to ODBC driver. Windows XP Pro. Latest ODBC as per MS site.

The code is ...

try{

    InputStream dbin = rs3.getBinaryStream("DetailedAlertDescription" );
    for( int xread; ( xread = dbin.read() ) != -1; )
    {
        String C = String.valueOf(xread);
        LogSession.OutputLog_Message( C );
    }
}catch(Exception e) {e.toString(); };

Help...
stevieaj99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CEHJCommented:
Why are you not using getString()

?
0
stevieaj99Author Commented:
The real length of data I want to store in this memo field is > 4K. The Test data was set to 260 to prove it would not work. getString according to the JDBC spec will only return < 255 characters.
0
CEHJCommented:
OK. Try the following:

try {
      final int FIELD_SIZE = 1 << 10 << 3; //8KiB size
      StringBuffer sb = new StringBuffer(FIELD_SIZE);
      Reader in = getCharacterStream("DetailedAlertDescription");
      int c = -1;
      while ((c = in.read()) > -1) {
            sb.append((char)c);
      }
      in.close();
      String fieldVal = sb.toString();
}
catch(Exception e) {
      e.printStackTrace();
}
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

stevieaj99Author Commented:
OK - tried the code. Had to change getChracterStream line to

Reader in = rs3.getCharacterStream("DetailedAlertDescription");

And the same thing happens. Only 254 characters are returned from the memo field (this has 260).

?
0
CEHJCommented:
Are you *certain* that it's a memo field and not a normal string?
0
stevieaj99Author Commented:
I have just double checked. It is a memo field. It has 260 characters in the first record.

0
CEHJCommented:
Just to humour me, please post


System.out.println(rs3.getMetaData().getColumnTypeName(n);

(where 'n' is the 1-indexed index of "DetailedAlertDescription")

0
stevieaj99Author Commented:
OK.

This is the query for the result set.

QueryAlertDesc = "SELECT DISTINCT SITEALERTS.SiteID, SITEALERTS.AlertCode, SITEALERTS.AlertSubCode, ALERTS.AlertDescription, ALERTS.DetailedAlertDescription, SITEALERTS.LocalAlertDescription, SITEALERTS.DetailedLocalAlertDescription, ALERTS.PhraseNo FROM ALERTS INNER JOIN SITEALERTS ON (ALERTS.AlertSubCode = SITEALERTS.AlertSubCode) AND (ALERTS.AlertCode = SITEALERTS.AlertCode) WHERE (((SITEALERTS.AlertCode)='" + AlertCode + "') AND ((SITEALERTS.AlertSubCode)='" + AlertSubCode + "') AND ((SITEALERTS.SiteID)='" + SiteID + "'));";

The metadata values returned are ...

VARCHAR = SiteID
VARCHAR = AlertCode
VARCHAR = AlertSubCode
VARCHAR = AlertDescription
LONGCHAR = DetailedAlertDescription
VARCHAR = LocalAlertDescription
LONGCHAR = DetailedLocalAlertDescription

0
CEHJCommented:
I'lll have to think about this ..;-)
0
objectsCommented:
Try using getAsciiStream()
0
objectsCommented:
Also test it with a simple query initially:

select DetailedAlertDescription from SITEALERTS
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
stevieaj99Author Commented:
OK this is what I did.

Tried getAsciiStream first and it didn't work.

Simplified query and I was able to use a getString to get all the data.

I was also able to use the getAsciiStream, getCharacterStream and getBinaryStream!

Does any one know why?
0
objectsCommented:
The problems with Access by the sounds, and its not handling the query correctly.
Try slowly building up your query until you find what specificqalluy is causing the problem.
0
CEHJCommented:
Do you get any indications of anything amiss when you try this outside Java?
0
stevieaj99Author Commented:
I have broken this down into a complex query without DISTINCT and JOIN - still doesn't work.
I have removed DISTINCT and the JOIN on their own - still doesn't work.
In order words keep it simple stupid is the overall principle (thats aimed at me not you).

Tried this in access and its ok as a complex query - no surprise there.

Not tried it in VS 2003/2005 under VB.NET or C#.Net.  Next step would be a proper JDBC type 3 driver as the app is growing by the day and its time I upgraded - just need to find a good one in the next week or so.

0
CEHJCommented:
>>.  Next step would be a proper JDBC type 3 driver

That would be interesting
0
objectsCommented:
Or use a decent db :)

0
objectsCommented:
using a type 3 driver would very likely fix the problem, but there aren't many around for access.
0
stevieaj99Author Commented:
Yep...been putting off upgrading to SQL and a good JDBC but I need to face the facts.
0
CEHJCommented:
8-)
0
objectsCommented:
Glad I could help you find the cause of your problem :)
Let me know if you need any further help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.