Consultor Eletrônico

Status: Unverified

GOAL:

How to test CACHED_STATEMENTS or -SQLStmtCache startup parameter from JDBC programs?

FACT(s) (Environment):

Progress 9.1D

CAUSE:

The progress adds blank spaces to make different statements, in the next release, V10.0A, the behavior will change.

FIX:

1. prodb test empty
2. set CACHED_STATEMENTS=100
3. Start a server for the database on any port (proserve test -S 8000)
4. Invoke SQL Explorer and create the table tbl1
(sqlexp test -S 8000
create table tbl1 (v1 int, v2 int);
commit;
quit;)
5. Compile following program

import java.sql.*;

public class stmt
{
public static void main(String[] args)
{
if (args.length < 2)
{
System.out.println("Missing arguments - syntax: java stmt dbname
service");
System.exit(1);
}

String username = System.getProperty("user.name");
String password = "";
String dbname = args[0];
String hostname = "localhost"; // Default is localhost
String service = args[1];

int statementCount = 200;

Connection con = null;



String phase = "";

try
{
String driver = "com.progress.sql.jdbc.JdbcProgressDriver";
String URL = "jdbc:jdbcprogress:T:" + hostname + ":" +
service + ":" + dbname;

Class.forName(driver);
phase = "creating connection";
con = DriverManager.getConnection(URL, username, password);
con.setAutoCommit(false);

PreparedStatement[] pst = new PreparedStatement[103];
int iStmtCnt = pst.length -1;

String strFill = "";

for (int i=1; i<= iStmtCnt ; i++)
{
phase = "creating";
String stmtText = "insert " + strFill
+ "into tbl1 values (?, ?)";
pst[i] = con.prepareStatement(stmtText);
strFill += " ";
System.out.println("Created statement " + (i));
}

phase = "rollback";
System.out.println(phase);
con.rollback();

phase = "closing connection";
con.close();
}
catch (SQLException ex)
{
System.out.println("Failed while " + phase);
System.out.println("Message: " + ex.getMessage());
System.out.println("SQLState: " + ex.getSQLState());
System.out.println("Vendor: " + ex.getErrorCode());
}
catch (Exception e)
{
System.out.println("Unexpected exception received");
System.out.println("Unexpected exception received");
e.printStackTrace();
}
}
}

Observe error 9799 being displayed when 100 statements have been prepared