Calling a stored procedure with output parameter and parameter name binding does not work

Hello NG,

Named parameters are a fine feature, you don't have to obey the parameter
order and can omit parameters that have default values. BUT if you try to
use output parameters in the escaped syntax for jdbc like the following:

{ ? = call myProc ( @MyOutParam = ? ) }

it doesn't seem to work anymore. Or am I simply too stupid? Maybe I have
missed some documentation detail? Maybe someone can point me to the right
direction? I'm really desperate. For implementation details see code snippet
below. Thanks in advance.

With best regards, have a merry christmas and a happy new year,

Daniel Hiller
dbap GmbH
d dot hiller at dbap dot de


Btw.: I have read the posting from Frits van Elvert ( 1997 ) and maybe
there's an answer which I couldn't find?


----------------
Sample code:

import java.util.*;
import java.sql.*;
import com.sybase.jdbc2.jdbc.*;

class JDBCTest {

  private static final String DB_DRIVER_NAME =
"com.sybase.jdbc2.jdbc.SybDriver";

  private static final String user = "xxx";
  private static final String password = "xxx";
  private static final String appName = "JDBCTest";
  private static final String host = "xxx";
  private static final String port = "9999";
  private static final String database = "db";

  private static java.sql.Connection connection;

  public static void main(String[] args)
      throws  java.sql.SQLException ,
              java.lang.ClassNotFoundException ,
              java.lang.InstantiationException,
              java.lang.IllegalAccessException {

    // Register driver
    System.out.println("Registering driver...");
    SybDriver sybDriver = ( SybDriver )
Class.forName(DB_DRIVER_NAME).newInstance();
    DriverManager.registerDriver(sybDriver);
    System.out.println("Registering driver [OK]");

    // Create a Properties instance, to deliver the connection properties
    Properties props = new Properties();
    props.put( "user", user );
    props.put( "password",  password );
    props.put( "APPLICATIONNAME" , appName );

    // Create connection
    System.out.println("Trying to create connection...");
    String string_URL = "jdbc:sybase:Tds:" + host + ":" + port
                      + ( database != null && database.length() > 0 ? "/" +
database : "" );
    connection = DriverManager.getConnection( string_URL ,  props );
    System.out.println("Trying to create connection [OK]");

    // Print out connection properties for debugging purposes
//    DriverPropertyInfo[] propInfo = sybDriver.getPropertyInfo( string_URL
, props );
//    for ( int i = 0 , n = propInfo.length ; i < n ; i++ )
//      System.out.println(
//          "Name: " + propInfo[ i ].name + ( propInfo[ i ].required ? "
(REQUIRED)" : "" )
//          + ( propInfo[ i ].choices != null ? "\n  Choices: " +
propInfo[ i ].choices.toString() : "" )
//          + "\n  Value: " + propInfo[ i ].value
//          + "\n  Description: " + propInfo[ i ].description + "\n"
//      );

// Output on our test system:
//    Name: SERVICENAME
//      Value: null
//      Description: interfaces entry corresponding to the backend which a
DirectCONNECT gateway serves
//
//    Name: HOSTNAME
//      Value: null
//      Description: name of current host
//
//    Name: HOSTPROC
//      Value: null
//      Description: identify application's process on this host
//
//    Name: user (REQUIRED)
//      Value: xxx
//      Description: user name
//
//    Name: password (REQUIRED)
//      Value: xxx
//      Description: password
//
//    Name: APPLICATIONNAME
//      Value: JDBCTest
//      Description: application name
//
//    Name: USE_METADATA
//      Value: true
//      Description: Use JDBC metadata information installed on the
database - if false, do not provide metadata
//
//    Name: LANGUAGE
//      Value: null
//      Description: language for messages from server
//
//    Name: CHARSET
//      Value: null
//      Description: character set for strings
//
//    Name: REMOTEPWD
//      Value: null
//      Description: remote-server password
//
//    Name: VERSIONSTRING
//      Value: jConnect (TM) for JDBC(TM)/5.2(Build
20765)/P/EBF9109/JDK12/Fri Jun 16  9:31:01 2000
//
//    Confidential property of Sybase, Inc.
//    Copyright 1997-1999
//    Sybase, Inc.  All rights reserved.
//    Unpublished rights reserved under U.S. copyright laws.
//    This software contains confidential and trade secret information of
Sybase,
//    Inc.  Use, duplication or disclosure of the software and documentation
by
//    the U.S. Government is subject to restrictions set forth in a license
//    agreement between the Government and Sybase, Inc. or other written
//    agreement specifying the Government's rights to use the software and
any
//    applicable FAR provisions, for example, FAR 52.227-19.
//
//    Sybase, Inc. 6475 Christie Avenue, Emeryville, CA 94608, USA
//
//      Description: jdbc driver version string
//
//    Name: EXPIRESTRING
//      Value:
//      Description: jdbc driver expiration string, retreived from Key
//
//    Name: PACKETSIZE
//      Value: 512
//      Description: Size (in bytes) for network packets
//
//    Name: STREAM_CACHE_SIZE
//      Value: -1
//      Description: Maximum pre-fetch size of cache per connection
//
//    Name: REPEAT_READ
//      Value: true
//      Description: Allow repeated use of getXXX for columns in any order -
if false, read once from first to last only
//
//    Name: LITERAL_PARAMS
//      Value: false
//      Description: if set, parameter values are sent literally in the
query
//
//    Name: CURSOR_ROWS
//      Value: 1
//      Description: Number of rows cursor should fetch at a time
//
//    Name: proxy
//      Value: null
//      Description: Address of the proxy gateway for the server
//
//    Name: SQLInitString
//      Value: null
//      Description: A SQL Batch which will be executed at connection time
//
//    Name: SESSION_TIMEOUT
//      Value: -1
//      Description: The number of seconds an http tunnelled session will be
kept alive while idle
//
//    Name: SESSION_ID
//      Value: null
//      Description: The identifier of an existing session in the
TdsTunnelling gateway to re-attach to.
//
//    Name: CHARSET_CONVERTER_CLASS
//      Value: com.sybase.jdbc2.utils.PureConverter
//      Description: the name of a class the driver should use to convert
between tds/sybcharset and UCS-2 Unicode on this connection.
//
//    Name: JCONNECT_VERSION
//      Value: 5
//      Description: The jConnect release version that this connection
should emulate.
//
//    Name: CANCEL_ALL
//      Value: false
//      Description: Allow statements to cancel all pending results on the
connection.
//
//    Name: PROTOCOL_CAPTURE
//      Value: null
//      Description: If set, raw TDS will be written to this file.
//
//    Name: DYNAMIC_PREPARE
//      Value: false
//      Description: If true PreparedStatements will attempt to use dynamic
prepared statement on the database.
//
//    Name: CONNECTION_FAILOVER
//      Value: true
//      Description: Try connecting to the next server address if the first
attempt fails.
//
//    Name: LANGUAGE_CURSOR
//      Value: false
//      Description: Use language cursors to process JDBC 1.0 cursor API -
otherwise use more efficient protocol cursors.
//
//    Name: SEND_LONG_PARAMS_REGARDLESS_OF_CAPABILITIES
//      Value: false
//      Description: Do not use this property.
//
//    Name: SERIALIZE_REQUESTS
//      Value: false
//      Description: Do not send a new request on a connection until all
responses from previous requests  have been completely received.
//
//    Name: LSB_BYTE_ORDER
//      Value: false
//      Description: Set LSB_BYTE_ORDER == true in order to swap the bytes
being sent to and received from the server. By default this property is
false.
//
//    Name: SYBSOCKET_FACTORY
//      Value: null
//      Description: Name of factory class to use for custom sockets, or
DEFAULT for  a default socket: new Socket();
//
//    Name: IGNORE_DONE_IN_PROC
//      Value: false
//      Description: Set to true if you do not want updateCount() values
returned for intermediate operations done within stored procedures or
triggers on the database,  but only the counts of rows actually affected
//
//    Name: SELECT_OPENS_CURSOR
//      Value: false
//      Description: calls to getCursorName() will return a unique name,
even if a cursor has not been opened.  Default is false, getCursorName()
returns null.
//
//    Name: REQUEST_HA_SESSION
//      Value: false
//      Description: Set REQUEST_HA_SESSION true, if you are connecting to a
Sybase server that is configured for HA failover, and you want this
connection to be fault tolerant.
//
//    Name: ELIMINATE_010SM
//      Value: false
//      Description: Avoid the double-connect performance penalty and 010SM
warning for older ASE databases.
//
//    Name: IS_CLOSED_TEST
//      Value: null
//      Description: Allow the user to specify the query that they would
like to execute against the backend database when Connection.isClosed() is
called.
//
//    Name: CLASS_LOADER
//      Value: null
//      Description: Reference to the class loader to be used on this
connection.
//
//    Name: PRELOAD_JARS
//      Value: null
//      Description: Comma-seperated list of jar names to be preloaded.
//






// -------------------------------------------------------------------------
---
    // === INTERESTING CODE SEGMENT ===




// -------------------------------------------------------------------------
---
    // No name binding feature, but works properly
    // This way is NOT the best, as you have to obey the parameter order

    String sql = "{ ? = call mi_GetUserInfo ( ? ) }";

    java.sql.CallableStatement statement = connection.prepareCall( sql );

    statement.registerOutParameter( 1 , java.sql.Types.INTEGER );
    statement.registerOutParameter( 2 , java.sql.Types.SMALLINT );

// Output:
//    Registering driver...
//    Registering driver [OK]
//    Trying to create connection...
//    Trying to create connection [OK]
//    Trying to execute '{ ? = call mi_GetUserInfo ( ? ) }'...
//    Trying to execute '{ ? = call mi_GetUserInfo ( ? ) }' [OK]
//    Returncode: (1) 0
//    OUT Parameter: (2) 1






// -------------------------------------------------------------------------
---
    // This would be my desired form but doesn't work:
    // Btw: doesn't work omitting return code request either

//    String sql = "{ ? = call mi_GetUserInfo ( @UserID = ? ) }";
//
//    java.sql.CallableStatement statement = connection.prepareCall( sql );
//
//    statement.registerOutParameter( 1 , java.sql.Types.INTEGER );
//    statement.registerOutParameter( 2 , java.sql.Types.SMALLINT );

// Output:
//    Registering driver...
//    Registering driver [OK]
//    Trying to create connection...
//    Trying to create connection [OK]
//    Trying to execute '{ ? = call mi_GetUserInfo ( @UserID = ? ) }'...
//    Trying to execute '{ ? = call mi_GetUserInfo ( @UserID = ? ) }' [OK]
//    Returncode: (1) 0
//    java.sql.SQLException: JZ0SG: Ein Callable Statement gab nicht so
viele Ausgabeparameter zur�ck, wie die Anwendung f�r sie registriert hat.
//            at
com.sybase.jdbc2.jdbc.ErrorMessage.raiseError(ErrorMessage.java:439)
//            at
com.sybase.jdbc2.jdbc.ParamManager.nextResult(ParamManager.java:554)
//            at
com.sybase.jdbc2.jdbc.ParamManager.getOutValueAt(ParamManager.java:402)
//            at
com.sybase.jdbc2.jdbc.SybCallableStatement.getShort(SybCallableStatement.jav
a:265)
//            at JDBCTest.main(JDBCTest.java:136)
//    Exception in thread "main"






// -------------------------------------------------------------------------
---
    // This doesn't work either, doesn't matter if long or short form of OUT
is used:

//    String sql = "{ ? = call mi_GetUserInfo ( @UserID = ? OUTPUT ) }";
//    String sql = "{ ? = call mi_GetUserInfo ( @UserID = ? OUT ) }";
//
//    java.sql.CallableStatement statement = connection.prepareCall( sql );
//
//    statement.registerOutParameter( 1 , java.sql.Types.INTEGER );
//    statement.registerOutParameter( 2 , java.sql.Types.SMALLINT );

// Output:
//    Registering driver...
//    Registering driver [OK]
//    Trying to create connection...
//    Trying to create connection [OK]
//    Trying to execute '{ ? = call mi_GetUserInfo ( @UserID = ?
OUTPUT ) }'...
//    com.sybase.jdbc2.jdbc.SybSQLException: Can't use the OUTPUT option
when passing a constant to a stored procedure.
//
//            at com.sybase.jdbc2.tds.Tds.processEed(Tds.java:2429)
//            at com.sybase.jdbc2.tds.Tds.nextResult(Tds.java:1865)
//            at
com.sybase.jdbc2.jdbc.ResultGetter.nextResult(ResultGetter.java:69)
//            at
com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:201)
//            at
com.sybase.jdbc2.jdbc.SybStatement.nextResult(SybStatement.java:182)
//            at
com.sybase.jdbc2.jdbc.SybStatement.executeLoop(SybStatement.java:1535)
//            at
com.sybase.jdbc2.jdbc.SybStatement.execute(SybStatement.java:1527)
//            at
com.sybase.jdbc2.jdbc.SybCallableStatement.execute(SybCallableStatement.java
:129)
//            at JDBCTest.main(JDBCTest.java:152)
//    Exception in thread "main"






// -------------------------------------------------------------------------
---
    // This desperate try doesn't work at all, of course:

//    String sql = "{ ? = call mi_GetUserInfo ( @UserID = @UserID
OUTPUT ) }";
//
//    java.sql.CallableStatement statement = connection.prepareCall( sql );
//
//    statement.registerOutParameter( 1 , java.sql.Types.INTEGER );
//    statement.registerOutParameter( 2 , java.sql.Types.SMALLINT );

// Output:
//    Registering driver...
//    Registering driver [OK]
//    Trying to create connection...
//    Trying to create connection [OK]
//    java.sql.SQLException: JZ0SB: Anzahl der Parameter ist au�erhalb des
g�ltigen Bereichs: 2.
//            at
com.sybase.jdbc2.jdbc.ErrorMessage.raiseError(ErrorMessage.java:426)
//            at
com.sybase.jdbc2.jdbc.ParamManager.checkIndex(ParamManager.java:255)
//            at
com.sybase.jdbc2.jdbc.ParamManager.registerParam(ParamManager.java:359)
//            at
com.sybase.jdbc2.jdbc.SybCallableStatement.registerOutParameter(SybCallableS
tatement.java:194)
//            at JDBCTest.main(JDBCTest.java:317)
//    Exception in thread "main"





// -------------------------------------------------------------------------
---

    System.out.println( "Trying to execute '" + sql + "'..." );

    System.out.println("Result of execution: " + statement.execute() );

    System.out.println( "Trying to execute '" + sql + "' [OK]" );

    System.out.println( "Returncode: (1) " + statement.getLong( 1 ) );
    System.out.println( "OUT Parameter: (2) " + statement.getShort( 2 ) );

  }
}


0
Daniel
12/23/2002 4:09:19 PM
sybase.jconnect 798 articles. 0 followers. Follow

2 Replies
1841 Views

Similar Articles

[PageSpeed] 1

The JDBC specs. don't have a concept of "Name binding" procedure 
parameters, nor the follow-on concept of "optional parameters". Sybase 
added an extension, com.sybase.jdbx.SybCallableStatement.setParameterName 
method to allow name-binding, but it is an **extension** not a part of the 
standard.

You can code something like :

  String sql = "{ ? = call mi_GetUserInfo ( ? ) }";

  java.sql.CallableStatement statement = connection.prepareCall(
  sql );
  SybCallableStatement sybstmt = (SybCallableStatement)statement;

      statement.registerOutParameter( 1 , java.sql.Types.INTEGER );
      statement.registerOutParameter( 2 , java.sql.Types.SMALLINT );
      sybstmt.setParameterName(2, "@UserId");


Jay

0
Jay_Anderson
1/3/2003 11:09:53 PM
Hello Jay,

<Jay_Anderson> wrote in News Entry
news:F1377B7752A07DA7007F3F2085256CA3.005DF23385256C98@webforums...
> The JDBC specs. don't have a concept of "Name binding" procedure
> parameters, nor the follow-on concept of "optional parameters".

I know that ;-)

> Sybase added an extension,
com.sybase.jdbx.SybCallableStatement.setParameterName
> method to allow name-binding, but it is an **extension** not a part of the
> standard.
> [ ... ]

That's exactly what I needed, many many thanks.

BTW - a note to all that also want to use this Sybase-specific feature:

If you have a procedure that looks like the following:

"CREATE PROCEDURE someProcedure ( @a integer, @b integer out, @c datetime
out, @d money out, @e bit = 1 ) AS ..."

make sure you name the OUTPUT-Parameters first, i.e. like the following:

<CODE>

    String sql = "{ ? = call someProcedure ( ? , ? , ? , ? ) }";
    java.sql.CallableStatement callableStatement = connection.prepareCall(
sql );
    SybCallableStatement sybStatement = ( SybCallableStatement )
callableStatement;

    // Return code
    sybStatement .registerOutParameter( 1 , java.sql.Types.INTEGER );

    // *** OUT Parameters FIRST , order within OUTPUT parameters doesn't
matter ***
    sybStatement .registerOutParameter( 2 , java.sql.Types.INTEGER );
    sybStatement .setParameterName( 2 , "@b" );
    //sybStatement.setNull( 2 , java.sql.Types.DATE );
    sybStatement .registerOutParameter( 3 , java.sql.Types.DATE );
    sybStatement .setParameterName( 3 , "@c" );
    //sybStatement.setNull( 3 , java.sql.Types.INTEGER );
    sybStatement .registerOutParameter( 4 , java.sql.Types.NUMERIC );
    sybStatement .setParameterName( 4 , "@d" );
    //sybStatement.setNull( 4 , java.sql.Types.NUMERIC );

    // *** INPUT parameters LAST, order within INPUT parameters doesn't
matter ***
    sybStatement.setParameterName( 5 , "@a" );
    sybStatement.setLong( 5 , 1 );
    // following omitted because it's optional
    //sybStatement.setParameterName( 6 , "@e" );
    //sybStatement.setBoolean( 6 , false );


    // Remainder omitted ...

</CODE>

This works at least when using Sybase JConnect 5.5, I didn't try any earlier
versions.

I hope someone can use this...

Bye and thanks again.

Daniel


0
Daniel
1/7/2003 5:02:50 PM
Reply: