FreeTDS Development Group

["Anton van Straaten" <anton AT appsolutions.com>]

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.