New post
AllAccepted answers
Kyle Baxter · Jun 5, 2017

So the problem here was likely that you either didn't have the method as an Expression or you weren't QUITting a value.  If you have a method that you intend to use as a Webmethod (SOAP method) then you must return a value.  Your options are:

Method AddInteger(Arg1 As %Integer, Arg2 As %Integer) As %Integer [ WebMethod, Codemode=Expression ]
{
Arg1 + Arg2
}

or

Method AddInteger(Arg1 As %Integer, Arg2 As %Integer) As %Integer [ WebMethod ]
{
      QUIT Arg1 + Arg2
}

I'd guess you weren't quitting out the value.

Kyle Baxter · Jun 2, 2017

Wellll technically NULL is a valid value for all datatypes.  I think what you want to do is to interrogate the metadata information which you can do like so:

SAMPLES>s stmt=##class(%SQL.Statement).%New()

SAMPLES>w stmt.%Prepare("SELECT ID,Name FROM Sample.Person")
1
SAMPLES>w stmt.%Metadata.%Display()
(You'll get lots of output)

Note that you can do this even without running the query!  You can look at %SQL.StatementMetadata in the Class Reference for more methods and properties that can help you.  

Note 2 - Sean's method is also perfectly valid.  The only advantage to mine is that you don't have to run the query.

Kyle Baxter · May 10, 2017

While I'm not entirely sure what you are trying to do here as MLLP is a bit foreign to me, the CSP Gateway is just a module added onto a web server.  If you can get the setup you want with a webserver, you can use the CSP Gateway to talk to Caché/Ensemble/HealthShare .  What exactly are you trying to accomplish here?  What's your goal?

Kyle Baxter · May 10, 2017

If by the 'Web Services testing part' you mean a page like this:

http://localhost:57772/csp/samples/SOAP.Demo.cls

That doesn't even use SOAP, it uses an HTML form submission to run the method directly.  What that page does is test the logic of your webservice methods in a simple way, to make sure your desired inputs give the desired outputs.  You should use something like SoapUI if you want to more completely test your SOAP methods.

Kyle Baxter · May 10, 2017

This is not documented because we don't want new development using this kind of storage (which we call "SQL Strorage").  We'd much prefer you to use the standard storage strategy that you get for free when creating classes.   If you look at Brendan's 5th (of 3) articles, you'll see a link with the text "Get the Examples here" where you can download some examples (if that works, the link will be here as well). 

Again, if you can avoid doing this, I recommend using standard storage.

If you run into any problems or if you have any questions, I strongly suggest you contact the WRC.  

Kyle Baxter · Apr 20, 2017

Using the macros is a bad idea.  The changes to the JSON, especially in this case, are simple.   Any source control system worth its salt is going to be able to make nearly all of the changes for you automatically.  As far as I know the only change that can't be caught by find-and-replace is the one requiring COS expressions embedded within JSON to have parentheses.  In this case, the new syntax is supported in 2016.1 JSON, so you wouldn't even need to change that.  Because of the ease of change, the fact that the change only needs to happen once, and the code obfuscation that occurs when adding  macros, I do not think using them is a good idea for anyone planning to use Caché's native JSON implementation.

 

Edit: It occurs to me that the macros would be useful if you have to switch back and forth between 2016.1 and later versions over and over again, but to do the conversion once is too simple to bother with macros.

Kyle Baxter · Apr 1, 2017

There is no such command.  I would recommend moving the index global as well.  However, if this is not an option then you probably want to do something like:

USER>s sql="select Name from %Dictionary.CompiledClass where system=0 and NOT(Name %Startswith '%') AND SUPER [ 'Persistent'"     


USER>s rs=##class(%SQL.Statement).%ExecDirect(,sql)                                                                               

USER>while rs.%Next() { s sc= $classmethod(rs.%GetData(1),"%BuildIndices") w "Built "_rs.%GetData(1)_" with return code = "_sc,! }

Obviously this is kind of hacked together and you'll want to clean it up, but the outline is there.  I had to make some assumptions like you don't have any other classes in your application that have 'Persistent' in them.  If you do then you'll want (Super [ '%Library.Persistent' OR Super [ '%Persistent').  Since classes are case sensitive this should be fine. 

Kyle Baxter · Mar 30, 2017

Triggers are fired after data normalization and validation.  Do you think you can/should use your own datatypes, overriding the Normalize or LogicalToDisplay/DisplayToLogical functions.  If that doesn't make sense you could have a field calculated off of the stored data?  There are lots of options here, the best one is going to depend strongly on the goals you have for the project.

Kyle Baxter · Mar 23, 2017

These answers are all sufficient.  However, perhaps a more natural solution is to make the field of type %Date.  Then you can use the SelectMode to determine how the date is represented in a query.  For instance, look at the DOB field in Sample.Person (in the SAMPLES) namespace.  The storage looks like this:

^Sample.PersonD(1)=$lb(","Waterman,Brenda Z.","437-96-2023",36088,...)

Here we see the ID (1) the Name ("Waterman,Brenda Z.") the SSN (437-96-2023), and the DOB (36088).  If you want to see the data you can do the following:

--------------------------------------------------------------

SELECT Name,DOB FROM Sample.Person WHERE ID=1

Name                                     DOB

Waterman,Brenda Z.      36088

--------------------------------------------------------------

If we wanted to see that in a readable format, we can change the selectmode and get:

--------------------------------------------------------------

Name                                       DOB

Waterman,Brenda Z.   1939-10-22

--------------------------------------------------------------

Moreover, you can use this in the WHERE clause very naturally:

--------------------------------------------------------------

SELECT Name,DOB FROM Sample.Person WHERE DOB='1939-10-22'

Name                                           DOB

Waterman,Brenda Z.     1939-10-22

Underman,Jane V.          1939-10-22

Bush,Susan E.                   1939-10-22

--------------------------------------------------------------

The way to change the selectmode is going to depend on how you are running your query.  All queries that come through xDBC are in ODBC mode, so that's easy.  If you are running a dynamic SQL statement you can do:

s stmt.%SelectMode=1  

Possible values are:

  • 0 for LOGICAL mode.
  • 1 for ODBC mode.
  • 2 for DISPLAY mode.

If you are using embedded SQL, you can use the precompiler directive #SQLCompile select=<value> as outlined here:

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

Kyle Baxter · Mar 18, 2017

Eduard is right, you should use GREATEST in this case.  CASE statements are kind of sloppy, and while they are fine to use, I like to avoid them in order to keep the query as readable as possible.

More to your other points, you can find a list of valid SQL functions here:

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

Note that all $-functions are not SQL Standard and will only work with Caché (most likely), so if you are interested in database agnosticism then try to use one of the more standard functions.  Good luck! 

-Kyle

Kyle Baxter · Feb 24, 2017

Yup!

DELETE FROM Sample.Person where (ID # 2) = 0   --Deletes all even numbered rows (so you know I'm not cheating and using ID numbers)

select * from (select id,name,ssn from sample.person) where %vid between 11 and 20 

%VID is a special variable in Cache SQL (VIEW ID, I think?) which allows you limit your rows without too much trouble.  And alternative is the following:

select TOP 10 ID,Name,SSN from Sample.Person where ID NOT IN (SELECT TOP 10 ID FROM Sample.Person)

The first TOP gives you your page size, and the subquery's TOP gives you where to start (in this case, we start at 11, because we're ignoring the first 10).  You can dynamically put in the sizes for TOP.