Using TSQLStoredProc in DBExpress

I need best practices for using calling a TSQLStoredProc in an DCOM application using DBExpress. I can't figure out the best way to call a stored procedure from the application. Where does the TSQLStoredProc go, in the client or appserver? How is it called? Good help would be much appreciated and rewarded.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

direct db connections go on the appserver
I suggest you use a a Query instead
you can call SPs like a query: SELECT * FROM MyProc(:Param1, :Param2) ORDER BY Field2
btw are you using RemoteDataModules with ClientDataSets ?
DARRAHAuthor Commented:
Yes, I am using remotedatamodules. The clientdatasets are on the client side. The datasetprovider and TSQLProc (I hope) is in the remotedatamodule. I'm converting a dbexpress app over to using remotedatamodules. The current app widely uses TSQLProc's throughout (since that wasn't a problem when not using remotedatamodules). Is the big problem passing the values for the params over to the remotedatamodule? That appears the case for me. Even if I use a TSQLQuery, I seem to run into the same problem. How do I initiate it, how do I call the ExecSQL or ExecProc in the remotedatamodule?
no problems if you use it like I wrote
ClientDataSet package's the params and sends them over
also the DataSetProvider should have the ResolveToDataset = false and AllowCommandText = true
that way the storedproc compo is there "just for decoration" :)
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

DARRAHAuthor Commented:
This is what I understand from your advice:

If my storedproc was actually an update command (or anything else, not a straight Select command), I put EXECUTE spTest :param1, :param2 in a TQuery in the remotedatamodule and connect it with a DataProvider. Then in the client side, I place a Clientdataset and connect it to the Provider.

However, in this case, a simple won't do since its not returning a result set. And execsql and execproc won't work with the CDS. So if I understand you wrong, could you clarify. Otherwise, is there a fairly straight conversion practice?

as I said .. you can leave the SQLStoredProc compos on, set DataSetProvider's as described above
then in the client side you can call TClientDataSet.Execute
it's in the help .. sorry I don't have the time to write an example
hope you understand .. otherwise I'll create a small example

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DARRAHAuthor Commented:
One final note/question:

If calling several procedures in a transaction controlled within Delphi, wouldn't this require a different approach so that the transaction would be controlled within the remotedatamodule? Somehow the values would need to be passed, and then the storedprocs would have to be executed from the Server?
AFAIK DataSnap doesn't support transactions
instead you could create a webservice and handle that manually (I do it like this)
... but that would require quite some rewriting
also if I'm not mistaken you can add methods to the RemoteDataModule's interface which you could then call from the client
in the project that has the RDM open the type library - View.Type Library
there in the base interface add methods .. but I'm not sure where to implement them :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.