Admin: 1 версия

2010-06-01T05:06:25Z

1 версия

в 17:44, 31 мая 2010

2010-05-31T17:44:27Z

Новая страница

== Call a function with one IN/OUT parameter; the function returns a VARCHAR ==



<source lang="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "com.jnetdirect.jsql.JSQLDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1433";
String mydatabase = serverName + ":" + portNumber;
String url = "jdbc:JSQLConnect://" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
CallableStatement cs = connection.prepareCall("{? = call myfuncinout(?)}");

// Register the types of the return value and OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);
cs.registerOutParameter(2, Types.VARCHAR);
// Set the value for the IN/OUT parameter
cs.setString(2, "a string");
// Execute and retrieve the returned values
cs.execute();
String retValue = cs.getString(1); // return value
String outParam = cs.getString(2); // IN/OUT parameter
}
}</source>



== Call a function with one IN parameter; the function returns a VARCHAR ==



<source lang="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "com.jnetdirect.jsql.JSQLDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1433";
String mydatabase = serverName + ":" + portNumber;
String url = "jdbc:JSQLConnect://" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
CallableStatement cs = connection.prepareCall("{? = call myfuncin(?)}");
cs.registerOutParameter(1, Types.VARCHAR);
// Set the value for the IN parameter
cs.setString(2, "a string");
// Execute and retrieve the returned value
cs.execute();
String retValue = cs.getString(1);
}
}</source>



== Call a function with one OUT parameter; the function returns a VARCHAR ==



<source lang="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "com.jnetdirect.jsql.JSQLDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1433";
String mydatabase = serverName + ":" + portNumber;
String url = "jdbc:JSQLConnect://" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
CallableStatement cs = connection.prepareCall("{? = call myfuncout(?)}");
// Register the types of the return value and OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);
cs.registerOutParameter(2, Types.VARCHAR);
// Execute and retrieve the returned values
cs.execute();
String retValue = cs.getString(1); // return value
String outParam = cs.getString(2); // OUT parameter
}
}</source>



== Call a procedure with one IN/OUT parameter ==



<source lang="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "com.jnetdirect.jsql.JSQLDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1433";
String mydatabase = serverName + ":" + portNumber;
String url = "jdbc:JSQLConnect://" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
CallableStatement cs = connection.prepareCall("{call myprocinout(?)}");

// Register the type of the IN/OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);
// Set the value for the IN/OUT parameter
cs.setString(1, "a string");
// Execute the stored procedure and retrieve the IN/OUT value
cs.execute();
String outParam = cs.getString(1); // OUT parameter
System.out.println(outParam);
}
}</source>



== Call a procedure with one IN parameter ==



<source lang="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "com.jnetdirect.jsql.JSQLDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1433";
String mydatabase = serverName + ":" + portNumber;
String url = "jdbc:JSQLConnect://" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
// Call a procedure with no parameters
CallableStatement cs = connection.prepareCall("{call myprocin(?)}");

// Set the value for the IN parameter
cs.setString(1, "a string");
cs.execute();
}
}</source>



== Call a procedure with one OUT parameter ==



<source lang="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;
public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "com.jnetdirect.jsql.JSQLDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1433";
String mydatabase = serverName + ":" + portNumber;
String url = "jdbc:JSQLConnect://" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
CallableStatement cs = connection.prepareCall("{call myprocout(?)}");

// Register the type of the OUT parameter
cs.registerOutParameter(1, Types.VARCHAR);
cs.execute();
String outParam = cs.getString(1); // OUT parameter
System.out.println(outParam);
}
}</source>



== Calling a Function in a Database: call functions with IN, OUT, and IN/OUT parameters. ==



<source lang="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "com.jnetdirect.jsql.JSQLDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1433";
String mydatabase = serverName + ":" + portNumber;
String url = "jdbc:JSQLConnect://" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
CallableStatement cs = connection.prepareCall("{? = call myfunc}");
// Register the type of the return value
int intValue = 0;
cs.registerOutParameter(1, intValue);
// Execute and retrieve the returned value
cs.execute();
String retValue = cs.getString(1);
}
}</source>



== Getting the Stored Procedure Names in a Database: retrieves the names of all stored procedures in a database. ==



<source lang="java">
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Main {
public static void main(String[] argv) throws Exception {
String driverName = "com.jnetdirect.jsql.JSQLDriver";
Class.forName(driverName);
String serverName = "127.0.0.1";
String portNumber = "1433";
String mydatabase = serverName + ":" + portNumber;
String url = "jdbc:JSQLConnect://" + mydatabase;
String username = "username";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);
DatabaseMetaData dbmd = connection.getMetaData();
// Get all stored procedures in any schema and catalog
ResultSet resultSet = dbmd.getProcedures(null, null, "%");
// Get stored procedure names from the result set
while (resultSet.next()) {
String procName = resultSet.getString(3);
System.out.println(procName);
}
}
}</source>



== Stored procedure with Input/Output parms and a ResultSet ==



<source lang="java">
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
public class Main {
public static void main(String[] argv) throws Exception {
}
public static int storedProcWithResultSet() throws Exception {
Connection conn = null;
CallableStatement cs = conn.prepareCall("{? = call proc (?,?,?,?,?,?,?)}");
// register input parameters
cs.setString(2, "");
cs.setString(3, "");
cs.setString(4, "123");
// regsiter ouput parameters
cs.registerOutParameter(5, java.sql.Types.CHAR);
cs.registerOutParameter(6, java.sql.Types.CHAR);
cs.registerOutParameter(7, java.sql.Types.CHAR);
// Procedure execution
ResultSet rs = cs.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int nbCol = rsmd.getColumnCount();
while (rs.next()) {
for (int i = 1; i <= nbCol; i++) {
System.out.println(rs.getString(i));
System.out.println(rs.getString(i));
}
}
// OUTPUT parameters
System.out.println("return code of Stored procedure = : " + cs.getInt(1));
for (int i = 5; i <= 7; i++)
System.out.println("parameter " + i + " : " + cs.getString(i));
return cs.getInt(1);
}
}</source>

← Предыдущая	Версия 05:06, 1 июня 2010
(нет различий)

Java Tutorial/Database/StoredProcedure - История изменений

Admin: 1 версия

в 17:44, 31 мая 2010