Question
· Feb 18

Using a SQL Server stored procedure in a CSP page

Hi Community, 

I have a problem I am hoping someone can help with - I have created a front-end with HTML/CSS/JS in a CSP page in Iris Studio. I am trying to use objectscript on the back end to connect to a SQL Server Database (with valid credentials), and execute a stored procedure.

I have a fileList variable that is a stored as a comma separated string through a user input textBoxContainer. This is to be used as a parameter in the stored procedure called @Docs

When I run the stored procedure from SSMS and enter the fileList manually on executing, it runs as expected, however when I run from the CSP Page, it doesn't execute. I have put the code snippet below in hopes someone has done this before and can provide an example. For an example fileList, you can assume - fileList = "111,222,333"

 set gc=##class(%SQLGatewayConnection).%New()

set CredentialObj=##Class(Ens.Config.Credentials).%OpenId("DatabaseCred")

 Set pDSN="DB-DSN"

set sc=gc.Connect(Assume DSN and User&Pass Credentials Here,0)

  If $$$ISERR(sc) {
  &js< alert(#(..QuoteJS("DB connection Failed: ", $system.Status.GetErrorText(sc)))#);>
  Quit
  else {
  &js< alert(#(..QuoteJS("Connected To DB"))#);>
  }

 If sc '= 1 {
       &js< alert(#(..QuoteJS("Error executing stored procedure: ", gc.%SQLCODE))#);>
       
    ElseIf scriptName = "procedure1" {
        Set sc = gc.Execute("EXEC ExternalDB.DB.StoredProcName @Docs = ?", fileList)

}

 

Thank in advance. Also, just in case this is relevant, the stored procedure is in one DB and updates the data on a different DB, however, I don't think it would be an issue as it connects to DB as expected from the CSP page, it is just the SP that doesn't work.

Kind Regards,

Dan

Product version: IRIS 2022.1
Discussion (1)2
Log in or sign up to continue