Oracle JDBC SAXParseException/Character encoding issue

[por]

Hi,

We're evaluating Jitterbit (by using the community edition). The system seems quite promising, but we've bumped into quite a problem with Oracle backend database.

First, we tried using Oracle JDBC Driver. It connects fine, and is able to download table listing. But when trying to download column information, we get the following error:

Code: Select all

An error occurred when downloading the column information: Error on the server: ; nested exception is:
   org.xml.sax.SAXParseException: Premature end of file.

There does not seem to be a way around this. Here's some additional details:
Server: Oracle9i Release 9.2.0.5.0
JDBC Driver: ojdbc14.jar (9.2.0.5.0) and ojdbc6.jar (11.1.0.7.0-Production)
Java: Java 1.6.0_18

Secondly, we tried using Oracle Instant Client ODBC Driver. It works fine, and is able to download column listing, and we actually managed to create a transformation with it. But the problem with it is that the character encodings are messed up. Our simple transformation fetches information from Oracle database and outputs it to a text file. In the result all occurences of å appear as ?.

Has anyone had similar problems and was there any way around them?

-Pasi

[davidq]

Hi Pasi,

There've been a few posts about encoding problems. Don't know if this one's relevant:-

http://jitterbit.com/PhpBB/viewtopic.php?f=7&t=98&start=15&hilit=norwegian

Hope it helps.

Regards,

David Q

[por]

Thank you David! The thread you linked pushed me in to the right direction, and ODBC is now working properly. I also posted my notes on the thread.

Thanks!

-Pasi

[por]

My enthusiasm was a bit premature, it seems. As mentioned, the characters appear properly now, but... RegExMatch() won't work with strings containing ÖÄÅöäå characters. It seems it doesn't find a match even though the strings are the same. For example, if the following snippet has $s = "60320 Seinäjoki" and SCHEMA:TABLE.ZIPCODE = "60320" and SCHEMA:TABLE.SCHEMA:ZIPCODE$CITY = "SEINÄJOKI", $s gets appended to $addr_line even though it should not be.

Code: Select all

If( RegExMatch( ToLower( $s ),
                ToLower( ".*(" + Trim( SCHEMA:TABLE.ZIPCODE ) + "\\s+" + Trim( SCHEMA:TABLE.SCHEMA:ZIPCODE$CITY ) + ").*" ) ),
                "",
                If( RegExMatch( ToLower( $s ),
                                ".*(" + ToLower( Trim( SCHEMA:CUSTTABLE.SCHEMA:COUNTRY$NAME ) ) + ").*" ),
                                "",
                                $addr_line = $addr_line + " " + $s
                );
);


I'd guess that with JDBC Driver all encoding issues would be history, but unfortunately it won't work.

[emanuel]

Character encoding has always been an issue for us, sometimes it works and sometimes you can work around it. Internally we use Latin-1 encoding (on windows we use the windows code page which is basically the same) but if external systems use a different encoding scheme you may have problems. In many databases you can specify the client encoding you want to use and you seem to have figured out how to do it with Oracle.

There is also a bug that may cause this particular issue:

https://sourceforge.net/tracker/?func=d ... tid=797458

So the question is where your $s variable comes from? If it's entered in a formula like $s="ÖÄÅöäå" at some point in your mappings and you are running on Linux, then yes this is your problem. You may be able to work around it by reading your data from a database or from a file instead of coding it in your script.

I don't know why you are having issues using the JDBC driver, the ojdbc6.jar one has always worked fine for us and many other users. Maybe it's not working well with Oracle 9. You could check if there are any Oracle 9 specific JDBC drivers available. I guess most people are using Oracle 10/11 now.

/Cheers
Emanuel

[por]

Thank you for you response, Emanuel.

$s actually comes from the same database. It's just a multi-line field split and iterated through, so I guess the bug mentioned doesn't apply. The code which resides above the one previously posted:

Code: Select all

$i = 1;
$addr = Split( SCHEMA:TABLE.ADDRESS, "\n" );
$addr_line = "";
While( $i < Length( $addr ),
       $s = Trim( Get( $addr, $i ) );
       If( RegExMatch( ToLower( $s ),
           ...


About Oracle JDBC Driver - I've used both the 9i one and the latest. Same exception with both. This is just very strange.

[por]

One thing crossed my mind... My Oracle database contains a field that has ASCII Start of Text (ASCII code 0x02, chr( 2 ) in Oracle) as a value. I wonder if that could cause the SAXParseException..? Are there any Oracle users around? Could you please creating a table with varchar/char field, and insert chr( 2 ) as the field value, and see if you are able to create a transformation from that table in Jitterbit.

Thanks!

-Pasi

[por]

Just to let you know that the same problems affect Jitterbit Enterprise (server version 3.1.0.23, client version 3.1.0.40).

[emanuel]

This issue is caused by this bug:

http://sourceforge.net/tracker/?func=de ... tid=797458

/Emanuel