New post
Question
· Nov 8, 2016

&SQL in Cache Server Page

#CSP #Caché

Hi,

I am using the &sql () tag to retrieve the value from the Cache DB.

below is the piece of code i am using:

script language="cache" method="retrieveDetail" arguments="pVariable" returntype="%String"
&sql( select columnA,
into :tempVariable1
from TABLE
where COLUMNC = pVariable

quit tempVariable1

i have to return the ColumnA value back to the calling procedure.

can i know how it can be done ? since i am getting an javascript error.

Discussion (4)0
Log in or sign up to continue
John Murray · Nov 8, 2016

Does your where clause need a colon in front of pVariable ?

where COLUMNC = :pVariable

If that doesn't fix it, I suggest you first check whether the problem is caused by the &sql(...) or by the way your web page is calling your server-side method.

Remove all your code between the opening &sql( and the closing ) - incidentally, I assume you have a closing ) even though it didn't show in your post.

Make your method end with QUIT pVariable instead of returning tempVariable1.

Do you still get a Javascript error?

2 0
Sean Klingensmith · Nov 8, 2016

Hello Rajath,

In addition to what John said, it is always advisable to check the value of SQLCODE after running an embedded SQL statement. This will tell you if the SQL statement executed successfully, or encountered an error. If it encountered an error, you can get more details from the %msg variable. See the documentation here:

http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

You said that you are getting a JavaScript error. What is the error message? Can you send the JavaScript code you are using to call the retrieveDetail() function?

2 0
Tomas Vaverka · Nov 8, 2016

There is also comma after the ColumnA column, which causes invalid SQL. You should also check value of SQLCODE variable, because ouput host variables are valid only for SQLCODE=0 as described in Caché documentation:
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

"It is good programming practice to check the SQLCODE value immediately after exiting Embedded SQL. Output host variable values should only be used when SQLCODE=0."

So the code should look as follows:

 <script language="cache" method="retrieveDetail" arguments="pVariable" returntype="%String">
 &sql( select ColumnA into :tempVariable1 from  MyTable where  COLUMNC = :pVariable)
 If SQLCODE=0 { QUIT tempVariable1}
 Else {quit ""}
</script>

Will it help?

3 0