File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Error while inserting data through procedure

 
Sasvathan Sarvanandan
Greenhorn
Posts: 2
Eclipse IDE Java Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2
Eclipse IDE Java Oracle
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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......
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic