EXEC-SP

Solved a problem in an elegant manner and want to show off your code? Know a hard-to-find feature? Post it here for the benefit of others. Questions don't belong here.
Post Reply
galexander
Posts: 26
Joined: Fri Jul 25, 2014 8:22 am
Location: Manchester
Has thanked: 1 time
Been thanked: 1 time

EXEC-SP

Post by galexander » Tue Aug 01, 2017 9:14 am

Hi there

Is anyone using this function, and do you have any sample code of how you are using it?

Thanks

steve
Posts: 375
Joined: Wed Nov 30, 2011 10:20 pm
Been thanked: 75 times

Re: EXEC-SP

Post by steve » Tue Aug 01, 2017 10:01 am

Hi,
there is a manual available from Eclipse: "Maths External Database Link Manual".
The connection to the external database used to be via a utility called "DB Connection Maintenance". This is now done through the MIWH database connection configuration utility instead. You will define a database connection Code in this utility that is then used in your Maths code, or alternatively just define your connection string in the Maths itself (less secure).

Sample (pseudo) code below: more detailed examples in the manual:

Code: Select all

#==========
# lets assume we have a simple stored procedure called "idToName" in MSSQL that accepts a clientID as an input, 
# and returns a Client Name as output 
#==========
# let's define a connection to our Accounts database, defined with code="SQL1", and give it a friendly name of "Accountsdb" for this transaction
#==========
EXSQL  ("Accountsdb", "MSSQL", "SQL1")
#==========
# any errors?
#==========
if return-status = FALSE then
   #error with SQL connection
   message (return-value)
else
   #==========
   #connection defined - let's do something
   #NB the input and output parameter names must match EXACTLY your Stored Procedure variable names in MSSQL
   #==========
   #define our input
   #==========
   QUESTION ("Client id?","Number")
   vclient=return-value #todo:error checking for valid input
   #==========
   #build SP parameter string
   #==========
   vinput = "INPUT int @clientID "
   vinput = text(vinput) + text(vclient)
   #==========
   #define our output 
   #==========
   voutput = "OUTPUT nvchar @clientName"
   #==========
   #execute the stored procedure
   #==========
   EXEC-SP ("Accountsdb","idToName",vinput,voutput)
   #==========
   # display the result of the SP:
   #==========
   MESSAGE ("Client name returned from SP: ",return-value)
END

galexander
Posts: 26
Joined: Fri Jul 25, 2014 8:22 am
Location: Manchester
Has thanked: 1 time
Been thanked: 1 time

Re: EXEC-SP

Post by galexander » Tue Aug 01, 2017 10:03 am

Fantastic, thanks Steve - didn't even know that manual existed!

Post Reply