Stored Procedures in Legacy Databases

Stored Procedures in Legacy Databases

  
Hi,

We have lots of stored procedures with complex SQL logic in a legacy SQL Server database. How can we use them in OutSystems?

Lex
Lex -

You have a number of choices (in order from easiest to most difficult):

1. You can use Integration Studio to create an extension for them. I haven't tried this myself, but it looks like it only works with tables, so that may be off the table. That being said, try this FIRST since it will be the smoothest experience.

2. Back to Integration Studio, write .NET or Java code in an extension to interface between the two.

3. Use WCF Data Services to expose the stuff as a SOAP service, then consume it within Service Studio as a Web Reference.

4. Write a custom Web Service to expose the calls as needed, then consume it within Service Studio as a Web Reference.

I may VERY well be missing a better technique here, I'd love to know if I am!

J.Ja
All the options that Justin gave you are correct, or...you could simply use an advanced query to execute the stored procedures as wanted.

you can check this post to help you http://www.outsystems.com/NetworkForums/ViewTopic.aspx?Topic=Executing-sql-server-stored-procedures-help

cheers
RNA

Justin,

Thanks for your suggestions. I looked at option 1 and you are right, looks like it only works with tables. Other suggestions involve maintaining some kind of data access code external to OutSystems. It would be nice to connect to external entities via stored procedures using the data plumbing already built into Ousystems.  

Ricardo,

I looked at the link you had provided. Correct me if I am wrong, but It appears like that solution is tartgeted to stored procedures in the OutSystems database itself.

Lex

> looks like it only works with tables.

Is that just tables, and tables and views?

If so, depending on what's in these stored procs, maybe Views are an easy path.
Frank -

I was thinking the same. I've lucky enough to not have any legacy stuff to work on, all fresh dev.

J.Ja
Hi Lex,

it works with linked servers like the example i gave in the other post. SELECT values_wanted FROM OPENQUERY (YOURLINKEDSERVER,@SQL)

Attention: this is not the performance option. If performance is a must, then you should be using the Microsoft SQL Server Direct Connect or the Oracle Database Direct Connect according to the project needs

cheers,
RNA
Justin - that's pretty lucky.   :)   
Hi Ricardo,

SQL Server Direct Connect with an Advanced Query using the syntax EXEC [database].[schema].[strored procedure] @param1, @param2 did the trick. And of course, the stored proc needs permission for OSRuntime.

Thanks for your help.

Lex 
> And of course, the stored proc needs permission for OSRuntime.

That should be added to the lesson on External Databases.   I discovered that last night as well.
Hi,

Glad to hear. You're welcome.

Cheers
RNA
Last minute code review ...

I was checking an old espace and the solution to this problem depends on the existing of output parameters on the stored procedure.

If your stored procedure has output parameters then you should go for:
  • SELECT values_wanted FROM OPENQUERY (YOURLINKEDSERVER,@SQL)
Else
  • EXEC [database].[schema].[strored procedure] @param1, @param2
In both cases the Stored procedure in the remote server must have the permissions to be executed by the OSRuntime User or the user mapped in the Linked Server.

Cheers, 
RNA