java + oracle + merge + bind = fejl
Hej,jeg ønsker bruge merge til at indsætte data i en oracle database ved hjælp af prepared statements for at undgå for mange hardparses og laches, men jeg kan ikke få det til at virke.
jeg kan sagtens få det hele til at virke i PHP, problemet er at få det til at virke i java.
jeg paster lige hele min metode til at skrive til database (jeg er HELT ny til java, så bær over med mig):
public void writeData() throws SQLException{
String sql = "MERGE INTO masterproduct dest " +
"USING (SELECT ? as chainid, ? as sku, ? as name, ? as price, ? as description, ? as url, ? as picture, ? as stock, ? as internetprice FROM DUAL) src " +
"ON (src.sku = dest.sku AND src.chainid = dest.chainid) " +
"WHEN MATCHED THEN " +
"UPDATE SET " +
"dest.price = src.price," +
"dest.description = src.description," +
"dest.url = src.url," +
"dest.picture = src.picture," +
"dest.stock = src.stock," +
"dest.internetprice = src.internetprice," +
"dest.lastinstock = (SELECT CASE WHEN 1 = (src.stock) THEN SYSDATE END lastinstock FROM DUAL)," +
"dest.latestextractiondate = SYSDATE," +
"dest.extractedinlatestrun = 1 " +
"WHEN NOT MATCHED THEN " +
"INSERT (" +
"dest.chainid," +
"dest.sku," +
"dest.name," +
"dest.price," +
"dest.description," +
"dest.url," +
"dest.picture," +
"dest.stock," +
"dest.lastinstock," +
"dest.internetprice" +
") " +
"VALUES (" +
"src.chainid," +
"src.sku," +
"src.name," +
"src.price," +
"src.description," +
"src.url," +
"src.picture" +
"src.stock," +
"(SELECT CASE WHEN 1 = (src.stock) THEN SYSDATE ELSE null END lastinstock FROM DUAL)," +
"src.internetprice" +
")";
PreparedStatement stmt = this.conn.prepareStatement(sql);
try{
this.conn.setAutoCommit(false);
for (int i =0; i< this.size(); i++){
MasterProduct mp = this.get(i);
stmt.setInt(1, mp.getChainid());
stmt.setString(2, mp.getId());
stmt.setString(3, mp.getName());
stmt.setDouble(4, mp.getPrice());
stmt.setCharacterStream(5, new StringReader(mp.getDesc()),mp.getDesc().length());
stmt.setString(6, mp.getUrl());
stmt.setString(7, mp.getPicture());
stmt.setInt(8, mp.isStock() ? 1 : 0);
stmt.setInt(9, mp.isInternetprice() ? 1 : 0);
stmt.addBatch();
}
stmt.executeBatch();
this.conn.commit();
this.clear();
}
catch (SQLException e){e.printStackTrace();}
finally{stmt.close();}
}
fejlen jeg får ved dette er som flg:
java.sql.BatchUpdateException: ORA-00947: not enough values
når jeg så forsøger at at køre en lille test får jeg så en anden fejl som jeg håber/tror også er det der udløser fejlen i mit metode længere oppe.
conn.setAutoCommit(false);
String query = "SELECT ? title, ? author, ? body FROM dual";
PreparedStatement stmt = conn.prepareStatement(query);
stmt.setString(1, "bibo");
stmt.setString(2, "lala");
stmt.setString(3, "lulu");
ResultSet rset = stmt.executeQuery(query);
System.out.println (rset.getRow());
while (rset.next()) {
System.out.println (rset.getString(1));
}
stmt.close();
her får jeg en anden fejl:
Exception in thread "main" java.sql.SQLException: ORA-03115: unsupported network datatype or representation
det får mig frem til at egentlige spørgsmål her på experten:
Hvordan binder man variabler når man bare vil lave en select fra DUAL? Jeg skal jo have mine variabler ind i nogle felter på en eller anden måde...
For lige at gøre det klart for alle, jeg SKAL bruge et prepared statement, ellers går performance på serveren lige lukt til h***vede - så det duer IKKE at paste dem ind i sql strengen.
Det må kunne lade sig gøre i java...
