Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Problem with CallableStatement

2 posts in JDBC Connect (product renamed to JConnect) Last posting was on 1997-09-11 20:05:32.0Z
Terran Vigil Posted on 1997-09-11 17:01:36.0Z
From: "Terran Vigil" <terranv@digitalthink.com>
Subject: Problem with CallableStatement
Organization: DigitalThink
Message-ID: <01bcbede$7355ef00$362218d1@roquefort>
X-Newsreader: Microsoft Internet News 4.70.1162
Newsgroups: sybase.public.jdbcconnect
Date: Thu, 11 Sep 1997 13:01:36 -0400
Lines: 13
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:296
Article PK: 252049

I'm executing the following code against a Sybase SQLServer.
The stored procedure is a simple UPDATE table SET x = y WHERE A = B.
rowsAffected is set to 0 even though the update affects 1 row.
Executing "spMyStoredProcedure y" in ISQL returns a @@rowcount of 1.

Any ideas?

Please respond to terranv@digitalthink.com.

String sql = "{call spMyStoredProcedure ?}";
CallableStatement cstmt = conn.prepareCall(sql);
cstmt.setInt(1, pMyId);
int rowsAffected = cstmt.executeUpdate();


Lance Andersen Posted on 1997-09-11 20:05:32.0Z
Message-ID: <34184F0C.162F@sybase.com>
Date: Thu, 11 Sep 1997 16:05:32 -0400
From: Lance Andersen <lancea@sybase.com>
X-Mailer: Mozilla 3.01Gold (X11; I; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
To: Terran Vigil <terranv@digitalthink.com>
Subject: Re: Problem with CallableStatement
References: <01bcbede$7355ef00$362218d1@roquefort>
Content-Type: multipart/mixed; boundary="------------37A912797F07"
Newsgroups: sybase.public.jdbcconnect
Lines: 273
Path: forums-1-dub!forums-master.sybase.com!forums.powersoft.com
Xref: forums-1-dub sybase.public.jdbcconnect:295
Article PK: 252047

Terran,

I tried this on the 8-97 download from www.sybase.com and encountered
the same issue as you have:

alder% java MyCallable
Sybase jConnect (TM) for JDBC (TM) 2.1 Development and Unsupported
Version
It is an unsupported product and is not intended for deployment. It
does not include free technical support. You can purchase technical
support on an annual or pay-per-issue basis. Discussion Newsgroup is
available on www.sybase.com. For additional information on services or
run-time license, please check Sybase website or call 1-800-8-SYBASE or
+1-510-922-3500 outside of the USA and Canada.

Copyright 1997 Sybase, Inc.
All Rights Reserved
Executing: create table #foo(f1 int)
Number of rows affected= 0
Executing: insert #foo values(2)
Number of rows affected= 1
Executing: create proc foobar (@p1 int) as update #foo set f1=1
return
Number of rows affected= 0
Executing: {call foobar(?)}
Results= 0
Executing: drop proc foobar
Number of rows affected= 0


I just tried this with the latest soon to be available 2.2 driver
and this appears to work fine:

alder% java MyCallable
Sybase jConnect (TM) for JDBC (TM) 2.2 Development and Unsupported
Version
It is an unsupported product and is not intended for deployment. It
does not include free technical support. You can purchase technical
support on an annual or pay-per-issue basis. Discussion Newsgroup is
available on www.sybase.com. For additional information on services or
run-time license, please check Sybase website or call 1-800-8-SYBASE or
+1-510-922-3500 outside of the USA and Canada.

Copyright 1997 Sybase, Inc.
All Rights Reserved
Executing: create table #foo(f1 int)
Number of rows affected= 0
Executing: insert #foo values(2)
Number of rows affected= 1
Executing: create proc foobar (@p1 int) as update #foo set f1=1
return
Number of rows affected= 0
Executing: {call foobar(?)}
Results= 1
Executing: drop proc foobar
Number of rows affected= 0

Terran Vigil wrote:
>
> I'm executing the following code against a Sybase SQLServer.
> The stored procedure is a simple UPDATE table SET x = y WHERE A = B.
> rowsAffected is set to 0 even though the update affects 1 row.
> Executing "spMyStoredProcedure y" in ISQL returns a @@rowcount of 1.
>
> Any ideas?
>
> Please respond to terranv@digitalthink.com.
>
> String sql = "{call spMyStoredProcedure ?}";
> CallableStatement cstmt = conn.prepareCall(sql);
> cstmt.setInt(1, pMyId);
> int rowsAffected = cstmt.executeUpdate();

--
===============================================================================
Lance J. Andersen Email: lancea@sybase.com
Sybase Technical Support Phone:(617) 564-6336
77 South Bedford Street Fax: (617) 564-6148
Burlington, MA 01803

The Dark Knight Returns!!! Let's Go Penguins!!!
===============================================================================

/* MyCallable.java Sybase Product Support group, 06/01/97
* Copyright (c) 1997, Sybase., Emeryville, CA 94608
* All Rights Reserved
*
* TITLE: MyCallable.java
*
* START-HISTORY:
*
* 01 Jun 97 edit 0 - Lance Andersen.
* Initial coding.
*
* END-HISTORY
*
* START-DESCRIPTION:
*
* MyCallable class demonstrates how to use a CallableStatement
*
* MyCallable may be invoked with the optional parameters:
* -U username
* -P password
* -D debuglibraries
* -S server
*
*
* END-DESCRIPTION
*/

import java.io.*;
import java.sql.*;
import java.util.*;
import com.sybase.utils.Debug;

class MyCallable {

static public final int MAXROWS = 5;
static String _user = "sa";
static String _password = "";
static String _url = "jdbc:sybase:Tds:alder:6689/pubs2";


public static void main (String args[]) {


// Parse the command line

if (!processCommandline(args))
{
System.out.println(
"Syntax:\n" +
"\tMyCallable [-U <username>] [-P <password>] " +
" [-S <servername>]\n\t\t [-D <debug-class-list>]\n");
System.exit(1);
}


try {

// Load the Sybase Driver

Class.forName("com.sybase.jdbc.SybDriver");


// Attempt to connect to a driver.

Connection con = DriverManager.getConnection(_url, _user, _password);

// If we were unable to connect, an exception
// would have been thrown. So, if we get here,
// we are successfully connected to the URL

// Check for, and display and warnings generated
// by the connect.

checkForWarning (con.getWarnings ());



String sproc = "{call foobar(?)}";
String createProc =
" create proc foobar (@p1 int) as " +
" update #foo set f1=1 " +
"return ";

execDDL(con, "create table #foo(f1 int)");
execDDL(con, "insert #foo values(2)");
execDDL(con, createProc);

// Now execute the sproc
CallableStatement cstmt = con.prepareCall(sproc);
System.out.println("Executing: " + sproc);

// Declare the IN Params. Note, you must skip the Return Status
cstmt.setInt(1, 1961);

int results = cstmt.executeUpdate ();
System.out.println("Results= " + results);

cstmt.close();

execDDL(con, "drop proc foobar");

// Close the connection

con.close();

}
catch (SQLException ex) {

// A SQLException was generated. Catch it and
// display the error information. Note that there
// could be multiple error objects chained
// together

System.out.println ("\n*** SQLException caught ***\n");

while (ex != null) {
System.out.println ("SQLState: " + ex.getSQLState ());
System.out.println ("Message: " + ex.getMessage ());
System.out.println ("Vendor: " + ex.getErrorCode ());
ex = ex.getNextException ();
System.out.println ("");
}
}
catch (java.lang.Exception ex) {

// Got some other type of exception. Dump it.

ex.printStackTrace ();
}
}


/*
* checkForWarning
* Checks for and displays warnings. Returns true if a warning
* existed
*/

private static boolean checkForWarning (SQLWarning warn) throws SQLException
{
boolean rc = false;

// If a SQLWarning object was given, display the
// warning messages. Note that there could be
// multiple warnings chained together

if (warn != null) {
System.out.println ("\n *** Warning ***\n");
rc = true;
while (warn != null) {
System.out.println ("SQLState: " + warn.getSQLState ());
System.out.println ("Message: " + warn.getMessage ());
System.out.println ("Vendor: " + warn.getErrorCode ());
System.out.println ("");
warn = warn.getNextWarning ();
}
}
return rc;
}


/*
* execDDL
* Execute a DDL or a DML statement that does not return a ResultSet
*/

private static void execDDL( Connection con, String cmd) throws SQLException
{
System.out.println("Executing: " + cmd);
Statement statement = con.createStatement();
int numrows = statement.executeUpdate(cmd);
System.out.println("Number of rows affected= " + numrows);
statement.close();
}


/*
* processCommandline
* Parse the Command Line and set the appropriate options
*/

static private boolean processCommandline(String args[])
{
//* DONE
String arg;
int errorCount = 0;
for (int i = 0; i < args.length; i++)
{
arg = args[i];
if (arg.regionMatches(0, "-", 0, 1))
{
try
{
switch(arg.charAt(1))
{
case 'D':
i++;
try
{
Debug.debug(true, args[i]);
}
catch (IOException ioe)
{
System.out.println(
"Error turning on debugging " + ioe);
}
break;
case 'U':
i++;
_user = args[i];
break;
case 'P':
i++;
_password = args[i];
break;
case 'S':
i++;
_url = args[i];
break;
default:
System.out.println("Invalid command line option: " + arg);
errorCount++;
break;
}
}
catch (ArrayIndexOutOfBoundsException aioobe)
{
System.out.println("missing option argument");
errorCount++;
}
}
else
{
// The syntax has no non "-" arguments
errorCount++;
}
}

return(errorCount == 0);
}


/*
* dispResultSet
* Process a ResultSet displaying all of the rows and columns. Also
* use ResultSetMetaData to obtain the column headers
*/

private static void dispResultSet (ResultSet rs) throws SQLException
{
int i;

// Get the ResultSetMetaData. This will be used for
// the column headings

ResultSetMetaData rsmd = rs.getMetaData ();

// Get the number of columns in the result set

int numCols = rsmd.getColumnCount ();

// Display column headings

for (i=1; i<=numCols; i++) {
if (i > 1) System.out.print("\t\t");
System.out.print(rsmd.getColumnLabel(i));
}
System.out.println("");

// Display data, fetching until end of the result set

while (rs.next ()) {

// Loop through each column, getting the
// column data and displaying

for (i=1; i<=numCols; i++) {
if (i > 1) System.out.print("\t\t");
System.out.print(rs.getString(i));
}
System.out.println("");

// Fetch the next result set row

}
}
}