Generated query by DBOutputFormat.constructQuery - Problem with Oracle

[Steinmaurer Thomas <Th...@scch.at>]

Hello,

 

there seems to be problem when writing data in a reducer to an Oracle
table. The reason is, that the generated INSERT INTO statement in
DBOutputFormat.constructQuery(...) appends a ";" at the end of the
INSERT INTO statement, which obviously fails with the Oracle JDBC driver
due to PreparedStatement.executeBatch() call in DBRecordWriter.close. I
get a "ORA-00911: invalid character" exception here. The Oracle JDBC
driver (or JDBC in general?) doesn't like the ";" at the end of a
statement when executed in context of a batch.

 

Any comments?

 

Thanks,

Thomas

 

RE: Generated query by DBOutputFormat.constructQuery - Problem with Oracle

[Steinmaurer Thomas <Th...@scch.at>]

Ok, the following worked out:

Based on the source code of DBOutputFormat, I wrote my own
OracleDBOutputFormat class as:

public class OracleDBOutputFormat<K  extends DBWritable, V> extends
DBOutputFormat<K,V> {
...
}

My class basically includes the following methods:

@Override
public String constructQuery(String table, String[] fieldNames) {
...
}

with the adjusted code for generating the INSERT INTO statement and the
various static setOutput implementations.


In the job configuration code, I then call:

OracleDBOutputFormat.setOutput(...)


This seems to work, although I don't know if there is a better way to
overcome the problem as described in my initial post.


Regards,
Thomas
 

-----Original Message-----
From: Steinmaurer Thomas [mailto:Thomas.Steinmaurer@scch.at] 
Sent: Dienstag, 20. September 2011 13:47
To: user@hbase.apache.org
Subject: Generated query by DBOutputFormat.constructQuery - Problem with
Oracle

Hello,

 

there seems to be problem when writing data in a reducer to an Oracle
table. The reason is, that the generated INSERT INTO statement in
DBOutputFormat.constructQuery(...) appends a ";" at the end of the
INSERT INTO statement, which obviously fails with the Oracle JDBC driver
due to PreparedStatement.executeBatch() call in DBRecordWriter.close. I
get a "ORA-00911: invalid character" exception here. The Oracle JDBC
driver (or JDBC in general?) doesn't like the ";" at the end of a
statement when executed in context of a batch.

 

Any comments?

 

Thanks,

Thomas