This is a good question - it causes a bit of confusion for those starting out with arrow syntax.  When you use -> you are doing what we call an "implicit join".  If you look at the docs here:

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

You'll see that an implicit join is a LEFT OUTER JOIN.  This is very different from an INNER JOIN as it will return rows from the first table whether or not the ON clause is satisfied.  Now, if your queries were:

1) SELECT Child.Name FROM Mother LEFT OUTER JOIN Child ON Mother.ID = Child.Mother WHERE Mother.Name LIKE 'A%'
2) SELECT Child.Name FROM Child WHERE Child.Mother->Name LIKE 'A%'

Then these queries would be exactly the same, and you should use the one that you find easier to read and work with.

2 additional notes:

1) LIKE kind of sucks when used in this way, because the optimizer isn't given the parameter.  Therefore it code generates assuming the parameter could be '%'.  This leads to poor performance.  If you are looking for the first letters in a string, you should use %STARTSWITH like so:

SELECT Child.Name FROM Mother INNER JOIN Child ON Mother.ID = Child.Mother WHERE Mother.Name %STARTSWITH('A%')

That will allow a ranged read of the Name index (which I assume you have) instead of a full scan.

2) If you set these up as a parent-child relationship in your class definition, you should change it to one-to-many or to use foreign keys.  The reason for this has to do with the storage.  Without going into too much detail, if you have a parent-child relationship they are stored on disk together, with each parent very close to its children.  This causes performance problems if you want to look at just the parent information alone, as you'll have to load in the child information, even if you don't need it.  
 

Currently the only way to give permissions on "Future" tables (that is, tables you haven't created yet) is to grant permissions on the schema and then add tables to that schema.  

Note that when you assign SQL Privileges, you should do so to a Role. The InterSystems security model is such that you grant resources to roles, and roles to users.  SQL Permissions are resources that you should grant to Roles.

You can write a function that returns the timestamp of your JSON array, and then SELECT it out like that.   Here's an example:

 

Class Test.JSONProp extends %Persistent
{

	Property JSON as %String (MAXLEN=1000);
	
	Property Type as %String;
	
ClassMethod GetTimestamp(str as %String) as %TimeStamp [SqlProc]
{
	try{
		s j={}.%FromJSON(str)
	    s ts=j.Biometrics.%Get(0).TimeStamp
	    return ts
	}
	catch err
	{	
		return ""
	}
}
}

The query then becomes:

SELECT ID,Type,Test.JSONProp_GetTimestamp(JSON) as TS 
  FROM TEST.JSONProp 
  ORDER BY TS

Give that a try - you'll need to probably do something different with the JSON depending on your version.

I can get you column 2:

 w stmt.%Metadata.columns.GetAt(2).property.SqlFieldName

but I don't see a way to do this with Implicit JOINS (->).   You can get the table and kind of figure it out with something like this:

w stmt.%Metadata.columns.GetAt(2).property.parent.SqlTableName

w stmt.%Metadata.columns.GetAt(2).property.parent.SqlSchemaName

I think the question here is what are you going for in the bigger picture?  Perhaps there's another angle?

Arockia,

This sounds like something you might want to discuss with the WRC (which is what I do!).  You will need to send over a sample table and some test data showing the problem and we'll be able to sort you out in no time.

Cheers!
Kyle

I'm unsure, exactly, what you're trying to do here.  You could write a Stored Procedure that returns the value of a global pretty easily.  If you need to run code before connecting via ODBC you can use "init code" set up via the $SYSTEM.SQL.SetServerInitCode() API.  I'm not sure exactly what you're looking for, though.

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.

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?

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.

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.