Hi All,
I am using JDeveloper11.1.1.4,
My Scenario is I have one table with no primary-keys. I have to insert more than 20 rows in that table when user perform save actions.
But before insert the record I have to check whether the row is existing in the table or not. I tried to with "NOT EXIST" statement in ADF.
Not exist statement is working fine in SQL Developer but the same statement is not working in ADF as method type.
My Codes are,
public int insertCustomers(String Customer_code, String Customer_name, String Customer_location)
{
DBTransaction transaction = getDBTransaction();
int count = 0;
String insertQuery =
"insert INTO customer_table(Customer_code,Customer_name,Customer_location )(SELECT ?,?,? from DUAL WHERE NOT EXISTS (SELECT * FROM customer_table WHERE Customer_code = ? AND Customer_name = ? AND Customer_location =?))";
PreparedStatement statement =transaction.createPreparedStatement(insertQuery, 0);
try
{
statement.setObject(1, Customer_code);
statement.setObject(2, Customer_name);
statement.setObject(3, Customer_location);
statement.setObject(4, Customer_code);
statement.setObject(5, Customer_name);
statement.setObject(6, Customer_location);
count = statement.executeUpdate();
}
transaction.commit();
return count;
}
catch(SQLException ex)
{
transaction.rollback();
throw new JboException(ex);
}
finally
{
try
{
if(statement != null)
{
statement.close();
}
}
catch(SQLException ex)
{
throw new JboException(ex);
}
}
}
Thanks,
David.