Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 How do I represent an SQL procedure in JDBC & SQLJ

Author  Topic 

ChrisjBrown
Starting Member

8 Posts

Posted - 2014-07-30 : 06:31:49
Hi, I have a procedure for a hypothetical university. For a given course, this returns the number of assignments with a pass grade and a fail grade.

CREATE PROCEDURE results (IN
p_course_code
CHAR(2),
OUT p_no_of_passes INTEGER,
OUT p_no_of_fails INTEGER)
BEGIN
SET p_no_of_passes = 0;
SET p_no_of_fails = 0
;
FOR my_loop AS pass_fail CURSOR FOR
SELECT mark
FROM assignment
WHERE course_code = p_course_code
DO
IF mark >= 40 THEN SET p_no_of_passes = p_no_of_passes + 1;
ELSE SET p_no_of_fails = p_no_of_fails
+ 1;
END IF;
END FOR;
END

I need to represent this procedure via JDBC and again via SQLJ. No JAVA code is required, just the JDBC and SQLJ statements to represent this sql procedure but I feel like I am at a total loss! I have the following JDBC, but I don't know if its even on the right tracks:

Import java.sql.*;
Class.forName(“sum.jdbc.odbc.Jdbc0dbcDriver”);
universityConnection = DriverManager.getConnection (
“jdbc:odbc:UniversityDSN”, “username”, “password”);
CallableStatement call = con.prepareCall(“call results(?)
Call.setInt(1.24);
Call.registerOutParameter(1, type, INTEGER)
Call.execute();
System.out.printIN(“Course code is “+call.getInt(2));

Can anyone out there help in any way? :)
   

- Advertisement -