File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Other Java Products and Servers and the fly likes Error while inserting data through procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Products » Other Java Products and Servers
Bookmark "Error while inserting data through procedure" Watch "Error while inserting data through procedure" New topic
Author

Error while inserting data through procedure

Sasvathan Sarvanandan
Greenhorn

Joined: Nov 22, 2011
Posts: 2

Hello every one,

Im just a beginner to java and programming. I wrote this code for my school assignment, however it doesn't work. I created a procedure and tried to insert data through the procedure into a table, but it throws exception. I don't know y. Please some body tell what is the problem in my code....??? My code is bellow.



MY code:
----------
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
//import java.sql.CallableStatement;
//import java.sql.Statement;

import javax.swing.JOptionPane;

public class DBControler {

static int StdID=1;

public static Connection geConnection() throws Exception{

String drive="oracle.jdbc.driver.OracleDriver";//get the driver
String url= "jdbcracle:thin:@localhost:1521rcl";//get the url

Class.forName(drive);//load driver
Connection conn= DriverManager.getConnection(url, userName, password);

return conn;
}

public static void createInsertProc(Connection conn) throws SQLException{

try{
Statement stmt = conn.createStatement();

String procedure =
"CREATE OR REPLACE PROCEDURE std_insertProc( procStdID int, procfName varChar2,procLName Varchar2,procAddress VarChar2,procCity VarChar2,procprovince Varchar2,procpostalCode varchar2) AS "
+ "BEGIN "
+ "INSERT INTO Students VALUES(procStdID, procfName,procLName, procAddress, procCity, procprovince, procpostalCode );"
+ "END;";
stmt.executeUpdate(procedure);
//JOptionPane.showMessageDialog(null, "Success");

}
catch (Exception e) {
System.out.print(e.getMessage());
}finally{

conn.close();
}
}


public static void insertStdInfo(Connection conn) throws SQLException{

try {
PreparedStatement cs=conn.prepareStatement("{std_insertProc(?,?,?,?,?,?,?)}");
cs.setInt(1, 1);
cs.setString(2, "Sasvathan");
cs.setString(3, "Sarvanandna");
cs.setString(4, "120");
cs.setString(5, "asdf");
cs.setString(6, "Ona");
cs.setString(7, "m1v3g5");

int val=cs.executeUpdate();


} catch (SQLException e) {

e.printStackTrace();
}
finally
{
conn.close();

}

}

}


-----------------------------------------------

eclipse shows this Error message
---------------------------------------------

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3530)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:1350)
at DBControler.insertStdInfo(DBControler.java:64)
at Test.main(Test.java:15)[/color]



This is my student table
---------------------------
Create Table Students(
StudentID Int Primary Key,
FirstName Varchar2(20),
LastName Varchar2(20),
Address VarChar2(50),
City VarChar2(20),
Province Varchar2(20),
PostalCode varchar2(6)
);



Sasvathan Sarvanandan
Greenhorn

Joined: Nov 22, 2011
Posts: 2

CallableStatement cs=conn.prepareCall("{call std_insertProc(?,?,?,?,?,?,?)}");
cs.setInt(1, 1);
cs.setString(2, "Sasvathan");
cs.setString(3, "Sarvanandna");
cs.setString(4, "120");
cs.setString(5, "asdf");
cs.setString(6, "Ona");
cs.setString(7, "m1v3g5");

cs.execute();

This is the solution. I found some how......
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Error while inserting data through procedure