Showplan output?

[Michael Peppler]

Could someone tell me how I can get showplan output in a jConnect app?

In C I'd code an appropriate server callback, but JDBC (and java in
general) is rather foreign to me...

Thanks
--
Michael Peppler Data Migrations, Inc.
mpep...@peppler.org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html

[Paul]

Hi Michael,

I will post something here -

The showplan output returns as messages and in jCOnnect you need to collect
them
as SQLWarnings (or use of SybMessageHandler - similar to the callback
mechanism
this is documented in the prog ref manual)

Anyway let me put together a quick example and I will post it here.

Paul

"Michael Peppler" <mpep...@peppler.org> wrote in message
news:pan.2004.05.12....@peppler.org...

[Paul]

Hi Michael,

I am not sure how your code looks but here is a simple demo to handle output
from showplan.
In fact I am using your sample with the ESCAPE clause to help figure out if
you are hitting some ase
or jconn bug/problem.

// ShowPlan.java
// Test code to demo showplan output

import com.sybase.jdbcx.*;
import java.sql.*;
import java.util.Properties;
import java.math.*;

public class ShowPlan
{
public static void main(String args[])
{
java.sql.Connection con = null;
String showPlan = "set SHOWPLAN ON";
String prepStmt = "Select * from ado_table (index ado_index_c1) where c2
like ? escape '\'";
String url = "jdbc:sybase:Tds:pvero-xp:12500/odbc";

// Register jdbc driver
try
{
Class.forName("com.sybase.jdbc2.jdbc.SybDriver").newInstance();
}

catch (Exception e)
{
System.out.println(e.getMessage());
}

// Using properties in case I want to add stuff
// catch SQL Exception if you can
// typical url is jdbc:sybase:Tds:loki:1212/jdbc
try
{
Properties props = new Properties();
//props.put("PROTOCOL_CAPTURE","tds_data");
props.put("user","sa");
props.put("password","");
con = DriverManager.getConnection(url, props);

// connected
System.out.println("Connection made.");

// First set showplan on
Statement stmt = con.createStatement();
stmt.executeUpdate(showPlan);

// Now for the PrepNull statement
PreparedStatement ps = null;
ResultSet rs = null;
SQLWarning sqw = null;

ps = con.prepareStatement(prepStmt);
ps.setString(1, "XXX%");

rs = ps.executeQuery();
System.out.println("Stmt: " + prepStmt);
// Check for warnings here on stmt
sqw = ps.getWarnings();
while (sqw != null)
{
System.out.println("warning sqw:" + sqw );
sqw = sqw.getNextWarning();
}

rs = ps.getResultSet();

while( rs.next() ) {
System.out.println("Sql output : " + rs.getString(1));
}

// check for more results...
while ( ps.getMoreResults() ) {
rs = ps.getResultSet();
while (rs.next() ) {
System.out.println("Sql output : " +
rs.getString(1));
}
}

// close result set and statement
rs.close();
ps.close();

System.out.println("Closing connection.");
//con.close();

}
catch (com.sybase.jdbc2.jdbc.SybSQLException sqe)
{
System.out.println("\nThis is a SybSQLException\n");
System.out.println("getStatus=" + sqe.getStatus());
System.out.println("getLineNumber=" + sqe.getLineNumber());
System.out.println("getProcedureName=" + sqe.getProcedureName());
System.out.println("getServerName=" + sqe.getServerName());
System.out.println("getSeverity=" + sqe.getSeverity());
System.out.println("getState=" + sqe.getState());
System.out.println("getTranState=" + sqe.getTranState());
System.out.println("getTranState=" + sqe.getMessage());
//sqe.printStackTrace();
} catch (Exception e)
{
e.printStackTrace();
//System.out.println(e);
System.exit(1);
}
finally // How do I get this to work on con object?
{
try
{
con.close();
System.out.println("Connection closed");
}
catch (Exception e)
{
System.out.println("Exception on closing cstmt or conn:");
e.printStackTrace();
}
}
} // main
} // PrepGen

"Michael Peppler" <mpep...@peppler.org> wrote in message
news:pan.2004.05.12....@peppler.org...