Solved

Changing data type between Provider and Clientdataset

Posted on 2013-01-31
6
713 Views
Last Modified: 2013-02-01
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
Comment
Question by:GNiessen
  • 2
  • 2
  • 2
6 Comments
 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
you mean sorting, not indexing ?
0
 
LVL 25

Expert Comment

by:Sinisa Vuk
Comment Utility
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
 

Author Comment

by:GNiessen
Comment Utility
#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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
 
LVL 25

Accepted Solution

by:
Sinisa Vuk earned 250 total points
Comment Utility
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
 

Author Closing Comment

by:GNiessen
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now