• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 793
  • Last Modified:

Changing data type between Provider and Clientdataset

I have a stored procedure (sp_who2) that is returning a varchar containing a number (CPUTime).  I am pulling the dataset into a ClientDataSet for use in my program, but would like the column to be an integer so that indexing on it will be in the correct order.  

How can I best do that?
0
GNiessen
Asked:
GNiessen
  • 2
  • 2
  • 2
1 Solution
 
Geert GruwezOracle dbaCommented:
you mean sorting, not indexing ?
0
 
Sinisa VukCommented:
You can do this on server side by casting filed:

select cast(CPUTime as int) as CPUTime, Field2, Field3 from table1....

Open in new window

0
 
GNiessenAuthor Commented:
#Geert  The ClientDataSet has Indexes for sorting.

#sinesav  You can't select from a stored procedure.  If it were a view or a table it would be easy.  And as this is a system stored procedure I can't modify it to run the way I want.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Geert GruwezOracle dbaCommented:
you could  (or ask to) add a sql zone to your Q with request attention

have you tried with a wrapper procedure ?
create a procedure which puts the output of the system proc in a temp table,
then run your select from that temp table

it's been over a decade since i did mssql but here goes ...
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/61dfc83d-65a7-4df0-9410-400e70b77f9c/

if you put a select statement last in your proc it will behave as a query in delphi
0
 
Sinisa VukCommented:
I missed "stored procedure". You cannot change field type on the fly because it allocate memory as string. But...

1. add all field defs + one additional 'ID' field defined as Integer and must be InternalCalc of kind.

http://edn.embarcadero.com/article/28959

2. Add procedure SortCustomClientDataSet from page: http://edn.embarcadero.com/article/29056 ... and add following code with new OnCalcField event on dataset (where you convert string to integer on the fly):

procedure TForm1.Button1Click(Sender: TObject);
begin
  ClientDataSet1.Close;
  ClientDataSet1.Open;
  SortCustomClientDataSet(ClientDataSet1, 'ID');
end;

procedure TForm1.ClientDataSet1CalcFields(DataSet: TDataSet);
begin
  ClientDataSet1.FieldByName('ID').AsInteger :=
    StrToIntDef(ClientDataSet1.FieldByName('CPUTime').AsString, 0);
end;

Open in new window

0
 
GNiessenAuthor Commented:
Setting up the an internal calc field worked and I was able to index on it.  I had hoped there was a way to control more the data transformation between the stored procedure and the Clientdataset, but it is working good enough for this purpose.

Thanks all.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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