FreeTDS Development Group

[PMoore AT axa.com.au]

firstly i would have to ask the obvious question:
Does it run under the SQL Server Query Analyzer tool?
(i added it to my SQL 2000 database and apart from calling my temp table
#tmp2 i left your code intact and verfied that your SQL works)


i would recommend executing the sp_who procedure in your code as that is a
known working procedure. this will at least show you whether or not stored
procedure execution is working in your JDBC/TDS implementation.

another thing to try - i would not use the word "Result" as the name of the
column you are returning in case there is an issue with it being a reserved
word.
cheers
peter

Peter Moore
Contractor,
ITeG, Axa Australia






"Anton van Straaten" <anton AT appsolutions.com>@franklin.oit.unc.edu on
16/08/2001 10:57:37 AM

Please respond to "TDS Development Group" <freetds AT franklin.oit.unc.edu>

Sent by:  bounce-freetds-132908 AT franklin.oit.unc.edu


To:   "TDS Development Group" <freetds AT franklin.oit.unc.edu>
cc:
Subject:  [freetds] Why can't this stored procedure be called?
      (FreeTDS_JDBC and jtds)


Can anyone give me any insight as to why calling the stored procedure below
fails, no matter which version of the FreeTDS  JDBC driver I use (e.g.
freetds_jdbc.snapshot.jar.2_0, jtds-0.1.1.jar, or Curt Hagenlocher's
version?)

I've included the required environment & version information at the end of
this message.  Executive summary is: client running JVM 1.3.1 on either NT
4.0 SP6a or Red Hat Linux 7.0; connecting to server running MS SQL 6.5 with
SP5a, on NT 4.0 with SP6a.

The problem stored procedure is defined as follows (stripped down to a
minimal example which triggers the problem):

     create procedure spTestExec as
          create table #tmp ( Result varchar(50) )
          insert #tmp execute spTestExec2
          select * from #tmp

This depends on the following procedure:

     create procedure spTestExec2 as
          select 'Doesn't work!' as Result

Executing this with any of the abovementioned versions of FreeTDS fails,
with "java.sql.SQLException: Protocol confusion.  Found a
com.internetcds.jdbc.tds.PacketResult (packet type 0x7c)".  I can't get any
variation of the above to work, i.e. anything that populates a temporary
table declared in the outer procedure, using data from the called
procedure,
fails in the same way.

I get the impression that the problem may have to do with multiple
resultsets: although the above should only return a single resultset, it
seems as though the presence of an execute statement in the stored
procedure
that doesn't produce a resultset for the client is what results in the
confusion (a semi-educated guess).  Whether I call the procedure using
CallableStatement.execute or CallableStatement.executeQuery (or various
other means) doesn't make any difference.

Does anyone have any insight as to what's occurring here?  Am I on the
right
track in thinking that the presence of an EXECUTE which doesn't return a
resultset may be putting a marker into the protocol stream which FreeTDS
doesn't recognize?  I'd be quite willing to try tracking this down myself
and fixing it if I can, but the nature of the problem goes beyond my
knowledge of the TDS protocol.

Environment and version information follows:

1. Versions of freetds_jdbc:
     freetds_jdbc.snapshot.jar.2_0 - EscapeProcessor.java modified
2001/03/08
     jtds-0.1.1.jar - no id keywords for ident; latest file date
2001/08/13, all
files
     Curt Hagenlocher's version - no id keywords for ident; latest file
date
2001/06/07, Tds.class

2. Protocol: TDS 4.2

3. JVM: 1.3.1

4. DBMS: Microsoft SQL Server 6.5, SP5a, running on Windows NT Server 4.0,
SP6a.  Previously tried with earlier service pack versions for both SQL
Server and NT Server, same result.

5. Client platforms: Windows NT Server 4.0, SP4 and SP6a; Red Hat Linux
7.0,
latest Red Hat patches applied.

6. Problem appears to be present in all available versions including latest
snapshot.

7. No other layers present: problem occurs in standalone Java program,
which
can be supplied upon request but doesn't do anything special.


---
You are currently subscribed to freetds as: [PMoore AT axa.com.au]
To unsubscribe, forward this message to
$subst('Email.Unsub')










**********************************************************************
Important Note
This email (including any attachments) contains information which is 
confidential and may be subject to legal privilege.  If you are not 
the intended recipient you must not use, distribute or copy this 
email.  If you have received this email in error please notify the 
sender immediately and delete this email. Any views expressed in this 
email are not necessarily the views of AXA.   Thank you.
**********************************************************************