Consultor Eletrônico

Status: Verified

GOAL:

SQL-92: How to pass array fields as parameters in a SQL-92 query?

GOAL:

How to pass array columns as parameters in a SQL-92 query using java?

FACT(s) (Environment):

All Supported Operating Systems
Progress 9.1x
OpenEdge 10.x

FIX:

To pass an array element as a parameter in a SQL-92 query, use the PRO_ELEMENT function. For example, the following query passes all the 12 elements of the MonthQuota field as a parameter:
UPDATE
PUB.SalesRep
SET
MonthQuota = PRO_ELEMENT(?,1,12)
WHERE
Region = ? AND
RepName = ? AND
SalesRep = ? ;
The following query passes only the 7th element of the above array column as a parameter:
UPDATE
PUB.SalesRep
SET
MonthQuota = PRO_ELEMENT(?,7,1)
WHERE
Region = ? AND
RepName = ? AND
SalesRep = ? ;
The following code demonstrates how to pass array columns as parameters in a SQL-92 query using java:
import java.sql.*;
public class ArrayParameter {
private static final String DRIVER = "com.ddtek.jdbc.openedge.OpenEdgeDriver";
private static final String DB_URL = "jdbc:datadirect:openedge://localhost:23456;databaseName=W609184129";
private static final String DB_USER = "yshanshi";
private static final String DB_PASS = "progress";
private static final String SQL1 =
"UPDATE " +
"PUB.SalesRep " +
"SET MonthQuota = PRO_ELEMENT(?,1,12) " +
"WHERE Region = ? " +
"AND RepName = ? " +
"AND SalesRep = ? ";
private Connection connection = null;
private Connection getConnection() {
if (connection == null) {
try {
// Load the driver
Class.forName(ArrayParameter.DRIVER);
connection = DriverManager.getConnection(ArrayParameter.DB_URL, ArrayParameter.DB_USER, ArrayParameter.DB_PASS);
DatabaseMetaData meta = connection.getMetaData ();
// Gets driver info:
System.out.println("\n=============\nDatabase Product Name is ... " + meta.getDatabaseProductName());
System.out.println("\nDatabase Product Version is " + meta.getDatabaseProductVersion());
System.out.println("\n=============\nJDBC Driver Name is ........ " + meta.getDriverName());
System.out.println("\nJDBC Driver Version is ..... " + meta.getDriverVersion());
System.out.println("\nJDBC Driver Major Version is " + meta.getDriverMajorVersion());
System.out.println("\nJDBC Driver Minor Version is " + meta.getDriverMinorVersi.on());
System.out.println("\n=============");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
return connection;
}
public void runTests() {
try {
runTest1();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null)
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void runTest1() throws SQLException {
final String TEST_NAME = "PreparedStatement with an extent field";
Connection myConnection = getConnection();
PreparedStatement ps = myConnection.prepareStatement(ArrayParameter.SQL1);
System.out.println("Query:" + ArrayParameter.SQL1);
String siCredHistNbr = "1000;2000;3000;4000;5000;6000;7000;8000;9000;10000;11000;12000";
String srRegion = "Central";
String srRepName = "Pitt , Dirk K.";
String srSalesRep = "DKP";
int i = 0;
ps.setString(++i,siCredHistNbr);
ps.setString(++i,srRegion);
ps.setString(++i,srRepName);
ps.setString(++i,srSalesRep);
ResultSet rs = ps.executeQuery();
rs.close();
ps.close();
}
public static void main(String[] args) {
ArrayParameter o = new ArrayParameter();
o.runTests();
}
}.