This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
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


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
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
 
Similar Threads
Problem with stored procedure table parameter
Weblogic 8.1 SP5 server got shutdown due to Connection leak and java.lang.OutofMemory
Receiving ORA-21700 Error most of the time
java.sql.SQLIntegrityConstraintViolationException: ORA-02291: integrity constraint
A question about PreparedStatement and ORA-03115 error