asked on

Jdbc insert not inserting.

Hi experts, I have a test class trying to insert data into db. It does not insert when I use jdbc, but only via the SQL commands on sqlplus command line. Here is the test class:

import java.sql.*;
import java.util.Map;
import java.util.*;

public class Test {

private int count;
private static Connection con;
private static String createString;
private static ResultSet rs;
private String query;

/** Creates a new instance of Test */
public Test() {
}

/**
* @param args the command line arguments
*/
public static void main(String[] args) {

Test test= new Test();
com.alindigo.costrecovery.jdbcUtilities.Insert insert = new com.alindigo.costrecovery.jdbcUtilities.Insert();
String url = "jdbc:oracle:thin:@c75pdev003.gpg.gov.za:1521:DEV";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.out.println("ClassNotFoundException:");
System.err.println(e.getMessage());
}

Statement pstmt =null;
try {
con = DriverManager.getConnection(url,"dace", "dace");
pstmt = con.createStatement();
} catch (SQLException se) {
System.out.println("We got an exception while creating a statement:" +
"that probably means we're no longer connected.");
se.printStackTrace();
System.exit(1);
}

int m = 0;

try {
m = pstmt.executeUpdate("insert into daceaccount select Dates, Amount, Transaction, Reference from csvfile WHERE NOT EXISTS (SELECT Amount from daceaccount where daceaccount.Reference=csvfile.Reference and daceaccount.Dates=csvfile.Dates and csvfile.Amount=daceaccount.Amount)");
} catch (SQLException se) {
System.out.println("We got an exception while executing our query:" +
"that probably means our SQL is invalid");
se.printStackTrace();
System.exit(1);
}

System.out.println("Successfully modified " + m + " rows.\n");
}

}

CEHJ

You're not closing the Connection and Statement. Close both in a finally block

Guy Hengel [angelIII / a3]

What is the error message (I assume none, only the insert not inserting any rows)?
Which line does the error occur (if any)?
What database (i assume oracle)?

You migh try to add the column names of daceaccount table if there are more than 3 fields in that table...
For the rest, i don't see any problem?!

ASKER CERTIFIED SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rk_radhakrishna

I am not sure, check once your insert statement syntax:
http://www.1keydata.com/sql/sqlinsert.html

mwari

ASKER

CEHJ, It still does not work it does not throw any exception, other than showing you that the inserted rows are 0.
Here are are my DB tables

describe csvfile;
Name Null? Type
----------------------------------------- -------- ----------------------------
DATES NOT NULL VARCHAR2(50)
AMOUNT NOT NULL NUMBER(10)
TRANSACTION NOT NULL VARCHAR2(50)
REFERENCE NOT NULL VARCHAR2(50)

describe daceaccount;
Name Null? Type
----------------------------------------- -------- ----------------------------
DATES VARCHAR2(20)
AMOUNT NUMBER(20)
TRANSACTION VARCHAR2(50)
REFERENCE VARCHAR2(20)

My ammended code is as follows:

import java.sql.*;
import java.util.Map;
import java.util.*;

public class Test {

private int count;
private static Connection con;
private static String createString;
private static ResultSet rs;
private String query;

/** Creates a new instance of Test */
public Test() {
}

/**
* @param args the command line arguments
*/
public static void main(String[] args) {

Test test= new Test();
com.alindigo.costrecovery.jdbcUtilities.Insert insert = new com.alindigo.costrecovery.jdbcUtilities.Insert();
String url = "jdbc:oracle:thin:@c75pdev003.gpg.gov.za:1521:DEV";

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.out.println("ClassNotFoundException:");
System.err.println(e.getMessage());
}

Statement pstmt =null;
try {
con = DriverManager.getConnection(url,"dace", "dace");
pstmt = con.createStatement();
} catch (SQLException se) {
System.out.println("We got an exception while creating a statement:" +
"that probably means we're no longer connected.");
se.printStackTrace();
System.exit(1);
}

int m = 0;

try {
m = pstmt.executeUpdate("insert into daceaccount select Dates, Amount, Transaction, Reference from csvfile WHERE NOT EXISTS (SELECT daceaccount.Dates, daceaccount.Amount, daceaccount.Transaction, daceaccount.Reference from daceaccount where daceaccount.Reference = csvfile.Reference and daceaccount.Dates = csvfile.Dates and csvfile.Amount = daceaccount.Amount)");
} catch (SQLException se) {
System.out.println("We got an exception while executing our query:" +
"that probably means our SQL is invalid");
se.printStackTrace();
System.exit(1);
} finally {
try { pstmt.close(); } catch(SQLException e) { e.printStackTrace(); }
try { con.close(); } catch(SQLException e) { e.printStackTrace(); }
}

System.out.println("Successfully modified " + m + " rows.\n");
}

}

mwari

ASKER

CEHJ, Your solution works now, for some reason when you open the command to execute sql queries and at the same time try to update the tables from your program, the updates do not take place. I had also left out the field names as you had mentioned, after including these and closing the sql command line. It worked well.

Thanks

CEHJ

:-)

>>I had also left out the field names as you had mentioned

Actually AngelIII mentioned that

mwari

ASKER

AngelIII my apologies. I had not seen that.