FreeTDS Development Group

[Bob Kline <bkline AT rksystems.com>]

On Mon, 30 Apr 2001, Spectron Caribe, Inc. wrote:

> Well I haven't solved the problem, I was just telling you what I saw
> causes the problem. The thing is that I need the exec() in my stored
> procedure. The real procedure I have looks like this:
> 
> CREATE PROCEDURE msim_testProcedure(@clientList text) AS
> begin
>   /* create temporary table */
>   create table #orders (clientId,orderNo number smallint)
>   /* fill temporary table*/
>   exec('insert into #orders select clientId,orderNo from RealOrders where
> clientId in ('+@clientList+')')
>   /* check some things in the temp table */
>   select clientId,orderNo from #orders
>   /* remove table */
>   drop table #orders
> end

Well, I don't know what to make of the syntax in your "create table"
statement in the stored procedure, but if I modify it to resemble
something I recognize then I can invoke the SP successfully from the
freetds JDBC driver:

$ cat msim_testProcedure.sql
CREATE PROCEDURE msim_testProcedure(@clientList text) AS
begin
  /* create temporary table */
  -- create table #orders (clientId,orderNo number smallint)
  create table #orders(clientId int, orderNo int)
  /* fill temporary table*/
  exec('insert into #orders select clientId,orderNo ' +
       'from RealOrders where clientId in ('+@clientList+')')
  /* check some things in the temp table */
  select clientId,orderNo from #orders
  /* remove table */
  drop table #orders
end
$ cat tsp.java
public class tsp
{
   static public void main(String args[])
      throws java.sql.SQLException, 
             java.lang.ClassNotFoundException,
             java.lang.Exception
   {
       java.sql.Connection cx = Common.getConnection();
       java.sql.CallableStatement cs =
           cx.prepareCall("{msim_testProcedure ?}");
       cs.setString(1, "2,3,5");
       java.sql.ResultSet rs = cs.executeQuery();
       while (rs.next()) {
           String clientId = rs.getString(1);
           String orderNo  = rs.getString(2);
           System.out.println("clientId=" + clientId 
                            + " orderNo=" + orderNo);
       }
   }
}
$ java tsp
clientId=2 orderNo=1002
clientId=3 orderNo=1003
clientId=5 orderNo=1005

Looking for differences in our environments, the first obvious
difference is that you're running 6.5, whereas my test was against 7.0
(I no longer have any 6.5 servers).  So if that's the problem, I won't
be able to help you track it down any further.

Another thing to check for (you mentioned you were using a servlet) is
to determine with *absolute* certainty that your add-in is seeing the
version of freeteds_jdbc.jar that you think it is.  If I had to guess,
I'd say the likeliest cause of the problem is that it's not.

You asked where to find ident -- it's a standard part of the RCS and CVS
packages.

I'm running JVM 1.3.0_02 from Sun.

I tried with and without ";TDS=7.0"; both worked.

Good luck!

-- 
Bob Kline
mailto:bkline AT rksystems.com
http://www.rksystems.com