go to post Kyle Baxter · Nov 21, 2017 Follow Danny's advice above (or below, no idea where this comment will land) and if it turns out to not be straightforward, feel free to contact Support (support@intersystems.com, 617-621-0700). One of us would be happy to log in with you and help you troubleshoot this. For what it's worth, enabling Auditing (after you change the System events for Protect and LoginFailure to be enabled) should tell you what the problem is pretty clearly.
go to post Kyle Baxter · Nov 16, 2017 The correct way to do this is with TO_DATE:select TO_DATE('19850720','YYYYMMDD')That will convert the text in the first argument (a string) using the format in the second parameter into a date, which will be displayed using the current selectmode. Try this in the Management Portal.
go to post Kyle Baxter · Nov 6, 2017 As Robert C. said, the reason for this is because aggregate functions do not follow the rules of Read Committed mode. Moreover, the way to tell if you have values or not is to check SQLCODE, not the answer. For instance, even if you did:&SQL(SELECT AttrA into :valA FROM Test."Table") You could still find your value (1) in valA. Your next line should ALWAYS be checking SQLCODE. Without that check, you cannot be sure that the value in your variable is good.
go to post Kyle Baxter · Nov 3, 2017 You can use ##class(Ens.DataType.UTC).timeUTCtoLocal() like so: ENSDEMO>s utc=$ZDATETIME($ZTIMESTAMP,3) ENSDEMO>w utc2017-11-03 14:44:15ENSDEMO>w ##class(Ens.DataType.UTC).timeUTCtoLocal(utc)2017-11-03 10:44:15.000
go to post Kyle Baxter · Nov 2, 2017 Jason - do you want to look into this some more? I've tried emailing you a couple times but haven't heard back.
go to post Kyle Baxter · Oct 26, 2017 This works for me. I used this class definition: Class Test.JDBCStream extends %Persistent { Property Content as %Stream.GlobalCharacter; } And the following query form SQuirreL SQL on Mac worked fine: INSERT INTO Test.JDBCStream (Content) VALUES ('A long String') So I'm not sure exactly what you're doing. Do you have code? Examples? As Len stated yesterday, you should open a WRC Issue. Edit: Sorry this post looks bad, the text editor on this site is awful. Moderator: fixed Post Moderator Edit: Still looks bad, lost the syntax highlighting, and being able to edit people's posts kind of ruins the point of an open forum.
go to post Kyle Baxter · Oct 26, 2017 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.
go to post Kyle Baxter · Oct 3, 2017 You have to go the other way:SELECT * FROM Foo JOIN Bar on Foo.MyBars [ Bar.IDNote [ is the ObjectScript "Contains" operator.The typical recommendation is that if you care at all about the relational structures of your tables, you will NOT use lists. You are far better off using Arrays, which project as child tables, or one-to-many relationships, if applicable.
go to post Kyle Baxter · Sep 28, 2017 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.
go to post Kyle Baxter · Sep 27, 2017 Do you understand what a Cross-Origin Resource Sharing (CORS) is? You can allow CORS requests, but it is important that you understand what you're getting into. That is, you need to think about it in the context of your application as it effects the accessibility of your data. So before going too far with this, please think about what changes here might mean. Some reading materials:http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...https://en.wikipedia.org/wiki/Cross-origin_resource_sharingNow, you seem to have this working on one webserver and not another. Do both webservers point to the same HealthShare instance?
go to post Kyle Baxter · Sep 26, 2017 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.
go to post Kyle Baxter · Aug 24, 2017 What is your StayConnected Value? 0? If so I would set it to something like 10 and see if that helps. This is not a problem with the results it's a problem with the connection. Are you calling ..Adapter.Disconnect()?
go to post Kyle Baxter · Aug 16, 2017 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?
go to post Kyle Baxter · Aug 2, 2017 There is by no means enough information here to help you. If this is an urgent problem, you should contact InterSystems Support. Otherwise, we need a lot more information regarding what your problem is.
go to post Kyle Baxter · Jul 24, 2017 Did you open up an issue with the WRC as Brendan recommended? You should do this if you want this error fixed. You can do so by writing an email to support@intersystems.com. Please include your table definitions, including all parent/child references that you have (I suspect this is a grandchild table).
go to post Kyle Baxter · Jul 5, 2017 Works for me: [SQL]SAMPLES>>CALL %SYSTEM.SQL_TableExists('table name') 1. CALL %SYSTEM.SQL_TableExists('table name') statement prepare time(s)/globals/lines/disk: 0.0398s/1847/15479/4ms execute time(s)/globals/lines/disk: 0.0007s/7/159/0ms cached query class: %sqlcq.SAMPLES.cls8 So I don't know what Vitaliy is on about. What's your version?
go to post Kyle Baxter · Jun 20, 2017 In that case, if you EVER want to query the headers (say, you want information on the dates, which would exist in the header) then you will always be loading the children into memory. This is clearly inefficient. You can enforce each Line having a Header by make the property required, and you can use Foreign Keys to cascade deletes as well. The only time to use Parent-Child is if you will NEVER query the parents without the children (or the tables are to be so small as the performance hit you take doesn't matter).
go to post Kyle Baxter · Jun 15, 2017 Parent-Child is not recommended as a solution here. Because children are stored in the same global as the parent, any time you want to look at parent information, you necessarily have to load in the child rows into your global buffers. In the One-To-Many scenario, both the One and Many side have their own tables, so doing SQL lookups should be easy.
go to post Kyle Baxter · Jun 15, 2017 What happens if you run the query in the SQL Shell. From a terminal execute:d $SYSTEM.SQL.Shell()This brings up a prompt which should let you run queries. Run one and see how long it takes. You should make sure your system has plenty of global buffers, and your last startup was OK (use the cconsole.log for this).