Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 808
  • Last Modified:

How to "preserve" the ClientDataSet returned by TSQLConnection.Execute ??

What I want to do:
- spname is the name of any stored procedure that returns rows.
- GetData should initialize mycds with the result of this stored procedure

What I get:
- an empty dataset (mycds) and an access violation when I try to access its
data.




procedure TMyClass.GetData(spname: String; var mycds: TCustomClientDataSet);
begin

    // objSQL is a var declared outside the procedure.
    // SqlConn is a TSQLConnection instance. The connection is established during "Create"

    objSQL := TSQL.Create;
    objSQL.SqlConn.Execute('SELECT * FROM ' + spname', nil, @mycds);
end;

procedure TMyClass.CallGetData;
var
    cds: TCustomClientDataSet;
begin
    GetData('some_Select_sp', cds);
    while not cds.eof do begin
      // something;
      next;
    end;
end;

0
hellspawn_9999
Asked:
hellspawn_9999
  • 2
1 Solution
 
mocartsCommented:
objSQL.SqlConn.Execute('SELECT * FROM ' + spname', nil, @mycds);

I'm not sure that you can get stored procedure results form SQLConnection's Execute. You must instead use TSQLStoredProc component.
anyway to get results of stored procedure you shouldn't use SELECT * FROM.. but EXEC or simply call your stored procedure

wbr, mo.
0
 
hellspawn_9999Author Commented:
Hi,

I'm using Interbase at the server side.

If I use EXEC I will be able to retrieve only the first row of the resultset.

I sucessully used the Execute to get my resultset with Kylix 2. The only difference is in that version the "mycds" variable is a local variable (not a parameter), and the return parameter is a String variable that returns the XMLData to the calling procedure/application (see sample below).

I tried to transfer that code to client side in order to improve performance by skipping the Data=>XML conversion, then I got caught in that situation.


(* this code is ok *)

procedure TMyClass.GetData(spname, XMLResult: String);
var
mycds: TCustomClientDataSet;
begin

   objSQL := TSQL.Create;
   objSQL.SqlConn.Execute('SELECT * FROM ' + spname', nil, @mycds);
   XMLResult := mycds.XMLData;
   mycds.Close;
   mycds.Free;
end;

0
 
CleanupPingCommented:
hellspawn_9999:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
hellspawn_9999Author Commented:
Problem was the parameter type (TCustomClientDataSet) was not compatible with the variable returned by Execute (TSQLDataSet).

Please refund.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now