FreeTDS Development Group

["Raul" <spopa AT tiscalinet.it>]

Gupta,
I've had the same problem only few months ago but I solved in the
following way:
I succeeded in working with FreeTds 0.51 and SQLServer2000 with a small
patch in Statement class code, getMoreResults method .
Without this patch the the very first time i run a statement I
caught a java.sql.SQLException:Protocol confusion. Got a 0x79 packet
I found that if the statement is cached in the system table
syscacheobjects of the Master databases this runs succesfully, otherwise I
catch the Exception. This, of course, doesn't occur in SQL 7.0. I tried
also to low the compatibilty of the Database as SQL7 but without any
results.
By examining the code I found that in the getMoreResults method there's no
reference to the 0x79 packet otherwise declared as TDS_RET_STAT_TOKEN. I
patched the code as made in ResultSet_base class, next method, by adding
the following statement:

*******************************************************************
Original method
*******************************************************************
public boolean getMoreResults() throws SQLException
{
SQLException exception = null;
if (tds == null)
{
throw new SQLException("Statement is closed");
}

updateCount = -1; // Do we need this global variable?

if (!tds.moreResults()) 
{
return false;
}

closeResults(); // Reset all internal variables (why is this done
here?)

try
{
tds.isResultSet();

// Keep eating garbage and warnings until we reach the next
result
while (!tds.isResultSet() && !tds.isEndOfResults()) 
{
if (tds.isProcId()) 
{
tds.processSubPacket();
} 
else if (tds.isDoneInProc())
{
PacketDoneInProcResult tmp =
(PacketDoneInProcResult)tds.processSubPacket();
}
else if (tds.isTextUpdate())
{
PacketResult tmp1 =
(PacketResult)tds.processSubPacket();
}
else if (tds.isMessagePacket() || tds.isErrorPacket())
{
PacketMsgResult tmp =
(PacketMsgResult)tds.processSubPacket();
exception = warningChain.addOrReturn(tmp);
}
else 
{
throw new SQLException(";Protocol confusion. "
+ "Got a 0x"
+ Integer.toHexString((tds.peek() &
0xff))
+ " packet");
}
} // end while

if (exception != null)
{
try
{
tds.discardResultSet(null);
}
catch(java.io.IOException e)
{
throw new SQLException("Error discarding result set while
processing sql error- " +
exception.getMessage() +
"\nIOException was " +
e.getMessage());
}
catch(com.internetcds.jdbc.tds.TdsException e)
{
throw new SQLException("Error discarding result set while
processing sql error- " +
exception.getMessage() +
"\nIOException was " +
e.getMessage());
}
throw exception;
}

if (tds.isEndOfResults()) 
{
PacketEndTokenResult end =
(PacketEndTokenResult)tds.processSubPacket();
updateCount = end.getRowCount();
return false;
} 
else if (tds.isResultSet())
{
return true;
}
else
{
throw new SQLException(";Protocol confusion. "
+ "Got a 0x"
+ Integer.toHexString((tds.peek() &
0xff))
+ " packet");
}
}

catch(java.io.IOException e)
{
throw new SQLException("Network error- " + e.getMessage());
}
catch(com.internetcds.jdbc.tds.TdsException e)
{
throw new SQLException("TDS error- " + e.getMessage());
}
}
********************************************************************
Patched Method
********************************************************************
public boolean getMoreResults() throws SQLException
{
SQLException exception = null;
if (tds == null)
{
throw new SQLException("Statement is closed");
}

updateCount = -1; // Do we need this global variable?

if (!tds.moreResults()) 
{
return false;
}

closeResults(); // Reset all internal variables (why is this done
here?)

try
{
tds.isResultSet();

// Keep eating garbage and warnings until we reach the next
result
while (!tds.isResultSet() && !tds.isEndOfResults()) 
{
if (tds.isProcId()) 
{
tds.processSubPacket();
}
//********************************************************* 
//**********************BEGIN PATCH*********************** 
else if (tds.peek() == Tds.TDS_RET_STAT_TOKEN)
{
tds.processSubPacket();
}
//********************************************************
//**********************END PATCH************************
else if (tds.isDoneInProc())
{
PacketDoneInProcResult tmp =
(PacketDoneInProcResult)tds.processSubPacket();
}
else if (tds.isTextUpdate())
{
PacketResult tmp1 =
(PacketResult)tds.processSubPacket();
}
else if (tds.isMessagePacket() || tds.isErrorPacket())
{
PacketMsgResult tmp =
(PacketMsgResult)tds.processSubPacket();
exception = warningChain.addOrReturn(tmp);
}
else 
{
throw new SQLException(";Protocol confusion. "
+ "Got a 0x"
+ Integer.toHexString((tds.peek() &
0xff))
+ " packet");
}
} // end while

if (exception != null)
{
try
{
tds.discardResultSet(null);
}
catch(java.io.IOException e)
{
throw new SQLException("Error discarding result set while
processing sql error- " +
exception.getMessage() +
"\nIOException was " +
e.getMessage());
}
catch(com.internetcds.jdbc.tds.TdsException e)
{
throw new SQLException("Error discarding result set while
processing sql error- " +
exception.getMessage() +
"\nIOException was " +
e.getMessage());
}
throw exception;
}

if (tds.isEndOfResults()) 
{
PacketEndTokenResult end =
(PacketEndTokenResult)tds.processSubPacket();
updateCount = end.getRowCount();
return false;
} 
else if (tds.isResultSet())
{
return true;
}
else
{
throw new SQLException(";Protocol confusion. "
+ "Got a 0x"
+ Integer.toHexString((tds.peek() &
0xff))
+ " packet");
}
}

catch(java.io.IOException e)
{
throw new SQLException("Network error- " + e.getMessage());
}
catch(com.internetcds.jdbc.tds.TdsException e)
{
throw new SQLException("TDS error- " + e.getMessage());
}
}