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.  

How does it connect - via ODBC/JDBC or is there another connection mechanism?

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. 

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.

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=...

SIMPLEST?  %String, definitely.  You can even use the ['whatsup@doc.com' syntax to search if you have to.  Using List of %String is a messy way to do anything in the relational world.  

If you do need to index them later, I'm pretty sure you can use iFind to do this without too much trouble (although you would need a new license, potentially).

Cache 2010.2 does not support Java 7:

http://docs.intersystems.com/cache20102/csp/docbook/DocBook.UI.Page.cls?...

You'll need to use an older version of Java or a newer version of Caché.

Note: Java 7 was released in July 2011, so obviously we couldn't support it a year before its release.

P.S. Sorry it took you so long to get an answer!  If ever you need something more quickly, please feel free to contact the WRC at support@intersystems.com.  

Example!

Class:

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

Class Jiri.RegisteredObject extends %RegisteredObject {

Property p1;

Property p2;

}

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

Terminal:

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

SAMPLES>s j=##class(Jiri.RegisteredObject).%New()

SAMPLES>s j.p1="test"

SAMPLES>s j.p2="json"

SAMPLES>w ##class(%ZEN.Auxiliary.jsonProvider).%WriteJSONStreamFromObject(.stream,j)

1

SAMPLES>w stream.Read()

{

"_class":"Jiri.RegisteredObject",

"p1":"test",

"p2":"json"

}

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

Do your CSP Files show up in the Server Explorer portion of Atelier?  Also, what is your Atelier version (Atelier->About Atelier)?

P.S. I think that was the most I've ever written "Atelier" in a short time.

Caché does NOT support SQL-92 in its full implementation, so please refrain from saying so.  Example:

SELECT 3+3*3

Result: 18

Clearly, by not following order of operations we are violating the standard.  Moreover, despite not conforming perfectly to SQL-92 we do support plenty of extensions, such as Table-Valued Functions.

That said, Caché does not support WITH in the way you describe.  However, you can accomplish the same thing by using subqueries, VIEWs, or multiple queries.  You can also JOIN each record with its parent and filter on that.  If you give some more information I'd be happy to point you in the right direction.

Sure - you can try to merge it off before deleting it:

m ^KMB = ^%sqlcq("NSP","SMPQueryHistory")
 

And then you have your query history in a different global, which you can reference when needed.