really quick multiple inserts can use COPY?

["Jens Schipkowski" <jens(dot)schipkowski(at)apus(dot)co(dot)at>]

Hello!

In our JAVA application we do multiple inserts to a table by data from a  
Hash Map. Due to poor database access implemention - done by another  
company (we got the job to enhance the software) - we cannot use prepared  
statements. (We are not allowed to change code at database access!)
First, we tried to fire one INSERT statement per record to insert. This  
costs 3 ms per row which is to slow because normally we insert 10.000  
records which results in 30.000 ms just for inserts.

for(){
sql = "INSERT INTO tblfoo(foo,bar) VALUES("+it.next()+","+CONST.BAR+");";
}

I was searching for an quicker way - MSSQL offers Array Inserts - at  
PostgreSQL. The only solution seem to be "INSERT INTO foo SELECT" and this  
is really dirty.
I improved the inserts using the subselect with union.

sql = "INSERT INTO tblfoo(foo,bar) ";
for(){
sql += "SELECT "+it.next()+","+CONST.BAR+" UNION " ...
}

This results in a really long INSERT INTO SELECT UNION statement and works  
correct and quick but looks dirty.

When I heard about COPY I thought this will be the right way. But it does  
not work using JDBC. Testing via psql does it perfect but sending the same  
SQL statements via JDBC throws an error.
-> BEGIN
sql = "COPY tblfoo(foo,bar) FROM STDIN;\n1  'foobar'\n2  'foobar'\n\\.";
-> COMMIT
ERROR:  syntax error at or near "1" at character 34

So, my questions:
Is it possible to use COPY FROM STDIN with JDBC?
Will it bring performance improvement compared to SELECT UNION solution?

many thanks in advance,
Jens Schipkowski

--
**
APUS Software GmbH