Problems returning cursor from stored procedure as ClientDataset

Question

This question directly relates to my Previous Question.

I have a need to create a TClientDataSet on a client from an Oracle 11g cursor contained in a package. I am using Delphi XE2 and DBExpress to connect to the DB and DataSnap to send the data back to the client.

When I configure the TSQLStoredProc to the TClientDataset at design time I can return the cursor as a TClientDataset with no problem and get expected results.

When I try to execute the Stored Procedure at runtime it returns an empty TClientDataset.

Is it possible to configure and execute an Oracle 11g Stored Procedure using TSQLStoredProc at runtime?

DataSnap Server

Design time Data Module code [View as Text]

    object StrProc1: TSQLStoredProc
    SchemaName = 'xxxx'
    MaxBlobSize = -1
    Params = <
      item
        DataType = ftWideString
        Precision = 2000
        Name = 'ABBR'
        ParamType = ptInput
        Value = 'ZZZTOP' 
      end
      item
        DataType = ftCursor
        Precision = 8000
        Name = 'RES'
        ParamType = ptOutput
        Size = 8000
      end>
    PackageName = 'KP_DATASNAPTEST'
    SQLConnection = SQLConnection1
    StoredProcName = 'GETFAXDATA'
    Left = 408
    Top = 72
  end
  object DataSetProvider1: TDataSetProvider
    DataSet = StrProc1
    Left = 408
    Top = 120
  end
  object ClientDataSet1: TClientDataSet
    Aggregates = <>
    Params = <>
    ProviderName = 'DataSetProvider1'
    Left = 408
    Top = 176
  end

function to execute Design Time config

function TKPSnapMethods.getCDS_Data3: OLEVariant;
begin    
 self.ClientDataSet1.Open;
 result:= self.ClientDataSet1.Data;
 self.SQLConnection1.Close;
end;

function to execute Runtime configuration This is the code that returns an empty ClientDataSet. The objective is to connect the pieces, set the value of the parameter, open the CDS and return the CDS.Data

function TKPSnapMethods.getCDS_Data2(schema: String): OleVariant;
var
  cds: TClientDataSet;
  dsp: TDataSetProvider;
  strProc: TSQLStoredProc;
  ProcParams: TList;
begin
  strProc := TSQLStoredProc.Create(self);
  try
    strProc.SQLConnection:= SQLCon;//<--A TSQLConnection    
    dsp := TDataSetProvider.Create(self);
    try
      dsp.DataSet := strProc;

      cds := TClientDataSet.Create(self);
      try
        cds.DisableStringTrim := True;
        cds.ReadOnly := True;
        cds.SetProvider(dsp);

        ProcParams:= TList.Create;    
        try
          //Load Stored Procedure Parameters
          SQLCon.GetProcedureParams('GETFAXDATA','KP_DATASNAPTEST',Schema,ProcParams);
          LoadParamListItems(StrProc.Params,ProcParams);
          
          strProc.ParamByName('ABBR').AsString := 'ZZZTOP';//<--Assign Parms
          strProc.MaxBlobSize := -1;
          strProc.SchemaName:= Schema;
          strproc.PackageName:='KP_DATASNAPTEST';
          strProc.StoredProcName:= 'GETFAXDATA';
          cds.Open;
          Result := cds.Data;
        finally
          FreeProcParams(ProcParams);
        end;
      finally
        FreeAndNil(cds);
      end;
    finally
      FreeAndNil(dsp);
    end;
  finally
    FreeAndNil(strProc);
    self.SQLCon.Close;
  end;
end;

Client Code this is just a test form that creates a connection to the DataSnap Server executes the ServerMethods and displays the results in a string grid.

procedure TForm1.Button1Click(Sender: TObject);
var
 proxy:TKpSnapMethodsClient;
 cds :TClientDataSet;
 field: TField;
 r,c:integer;
begin
  r:=0;
  c:=0;
  SQLConTCPSERV.Connected := True; //TSQLConnection
  proxy:= TKPSnapMethodsClient.Create(SQLConTCPSERV.DBXConnection,false);
  cds:= TClientDataSet.Create(nil);
  try
    //cds.Data:= proxy.getCDS_Data2('TESTTH');//<--Runtime function
    cds.Data:= proxy.getCDS_Data3; //<--Design time function
    if cds <> nil then
    begin
      cds.Open;
      cds.First;
      //String grid to display CDS contents.
      strGrid1.ColCount:= cds.FieldCount; //returns correct #
      strGrid1.RowCount:= cds.RecordCount;

      while not cds.Eof do  //<--runtime wont make it past here
      begin
        for field in cds.fields do //loop fields
        begin
          strgrid1.Cells[c,r]:= field.Text; //display results.
          c:=c+1;
        end;
        c:=0;
        r:=r+1;            
        cds.Next;
      end;
    end
    else showmessage('DataSet is NIL');
  finally
    cds.Free;
    proxy.Free;
    SQLConTCPSERV.Connected := False;
  end;
end;

Once agian I must confess I am new to the Delphi language. I have searched google, code.google, the Embarcadero Developer Network and DBExpress documentation all to no avail. I just don't understand why there would be a difference between design time and runtime.

Answer 1

I've resolved the issue. the problem is in the order of assigning values to the TSQLStoredProc component.

when calling this code:

strproc.PackageName:='KP_DATASNAPTEST';
strProc.StoredProcName:= 'GETFAXDATA';

the parameters are cleared. Below is the code to set the StoredProcName found in Data.SqlExpr

procedure TSQLStoredProc.SetStoredProcName(Value: UnicodeString);
begin
  //if FStoredProcName <> Value then
  //begin
    FStoredProcName := Value;
    SetCommandText(Value);
    if Assigned(FProcParams) then  // free output params if any
      FreeProcParams(FProcParams);
  //end;
end;

As you can see if FProcParams are assigned then FreeProcParams is call which frees the params. Because I was setting the StroredProcName after I was assigning the param values the code was executing with cleared params and returning an empty cursor.

the order that produces correct results at runtime [from getCDS_Data2] is as follows:

strProc.SchemaName:= Schema;
SQLCon.GetProcedureParams('GETFAXDATA','KP_DATASNAPTEST',Schema,ProcParams);
LoadParamListItems(StrProc.Params,ProcParams);

strproc.PackageName:='KP_DATASNAPTEST';
strProc.StoredProcName:= 'GETFAXDATA';
strProc.MaxBlobSize := -1;
strProc.ParamCheck:=true;

strProc.ParamByName('ABBR').AsString := 'ZZZTOP';

cds.Open;