All
Accepted answers
Alexander Koblov · Sep 9, 2019 go to post

I'm not sure for how much this is efficient, but you can use XSLT to do the transformation.

Class CMT.XmlToCsv [ Abstract ]
{

ClassMethod transform(
	infile As %String,
	outfile As %String) As %Status
{
	Set tXSL=##class(%Dictionary.CompiledXData).%OpenId(..%ClassName(1)_"||XmlToCsv").Data
	Set tSC=##class(%XML.XSLT.CompiledStyleSheet).CreateFromStream(tXSL,.tCompiledStyleSheet)
    If $$$ISERR(tSC) Quit tSC
	quit ##class(%XML.XSLT.Transformer).TransformFileWithCompiledXSL(infile,tCompiledStyleSheet,outfile)
}

XData XmlToCsv
{



    
    
    

    
        Col1,Col2,Col3
        
        
            
            
            
            
            
            
        
    

}

}

And then call it from terminal:

set p=##class(CMT.XmlToCsv).transform("c:\temp\input.xml","c:\temp\output.txt") 
zw p

I took XSLT from https://stackoverflow.com/a/46176699/82675

Alexander Koblov · Sep 6, 2019 go to post

CASE statement expects expression after THEN. DESC or ASC are not expressions. That's why you are getting syntax error.

So you need to supply some expression, ordering by which would mean reverse ordering by FirstName.

I don't know how to do this.

I would do sorting on the client or use dynamic SQL to create the query. As below for example.

Important! Nowhere I concatenate parameters of the stored procedure with the query to avoid SQL injections. Only SortingField is concatenated after checking that it has approved value.

ClassMethod Search(
    Name As %String = "",
    SSN As %String = "",
    Title As %String = "",
    SortingField As %String = "",
    StartIndex As %String = "") As %Integer [ SqlName = ExternalUsersSearch, SqlProc ]
{
 set query = "select Name, Title, SSN from Sample.Employee WHERE 1=1 "
 kill args
 
 if Name'="" {
     set args($I(args)) = Name
     set query = query _ "AND Name like ? "
 }
 
 if SSN'="" {
     set args($I(args)) = SSN
     set query = query _ "AND SSN like ? "
 }
 
 if Title'="" {
     set args($I(args)) = Title
     set query = query _ "AND Title like ? "
 }
 
 set AllowedFieldsToOrderBy = $LB("Name", "SSN", "Title")
 if $ListFind(AllowedFieldsToOrderBy, SortingField) {
    set query = query _ " ORDER BY " _ SortingField
    
    if StartIndex = 1 {
        set query = query _ " DESC"
    } else {
        set query = query _ " ASC"
    }
 }

 set rs = ##class(%SQL.Statement).%ExecDirect(,query,args...)

 #dim %sqlcontext As %SQLProcContext
 if rs.%SQLCODE >=0 {
     do %sqlcontext.AddResultSet(rs)
 } else { // pass errors to the caller
     set %sqlcontext.%SQLCODE = rs.%SQLCODE
     set %sqlcontext.%Message = rs.%Message
 }
 quit 1
}
Alexander Koblov · Sep 6, 2019 go to post

Not possible to do this in Query. You need to use dynamic SQL.

Symbol ":" is used to indicate host variables. Host variables are treated as expressions, not as identifiers.

During query compilation host variables are replaced with placeholders.

Consider query:

SELECT FirstName, MiddleName, LastName, Email, UserType 
FROM DB.ExternalUsers
WHERE FirstName like :objSearch.FirstName
ORDER BY :objSearch.SortingField

This query is compiled as:

SELECT FirstName, MiddleName, LastName, Email, UserType 
FROM DB.ExternalUsers
WHERE FirstName like ?
ORDER BY ?

Then during runtime you supply values as follows: objSearch.FirstName = 'A%' objSearch.SortingField = 'FirstName'

And query is executed as follows:

SELECT FirstName, MiddleName, LastName, Email, UserType 
FROM DB.ExternalUsers
WHERE FirstName like 'A%'
ORDER BY 'FirstName'

Notice 'FirstName' is in quotes in ORDER BY. So you sort by literal string. That is doing nothing.

What you can do is to use expression like:

 Order by CASE :objSearch.SortingField
          WHEN 'FirstName' THEN FirstName
          WHEN 'MiddleName' THEN MiddleName
          ...
          END

Although such generic queries makes SQL Query Analyzer unable to reason what plan is better to use for this query.

Alexander Koblov · Sep 2, 2019 go to post

I like usage of parameter, as it is computed once -- at compile time. And after you only fetch value in cycle.

You can also use $listfromstring via the same technique.

Alexander Koblov · Sep 2, 2019 go to post

If you have index.csp and it is specified as login page and you are seeing 404 error, try following:

a) Enable Audit b) Enable Protect event in Audit c) Reproduce the problem. e) Check Audit records if any Protect errors were logged

Alexander Koblov · Aug 30, 2019 go to post
My login page:
Name:
Password:
Set tMsg = $Get(%request.Data("Error:ErrorCode",1)) If ((tMsg'="")&&($SYSTEM.Status.GetErrorCodes(tMsg)'[$$$ERRORCODE($$$RequireAuthentication))) { &html<
> write "Auth failed!" &html<
> }
Alexander Koblov · Aug 30, 2019 go to post

You can use $system.OBJ.Export to export LUT document to file:

ENSDEMO>w $system.OBJ.Export("AlertTable.LUT","c:\temp\qq.lut")
 
Exporting to XML started on 08/30/2019 12:03:14
Exporting type : AlertTable.LUT
Export finished successfully.
1
ENSDEMO>w $system.OBJ.Load("c:\temp\qq.lut")
 
Load started on 08/30/2019 12:03:26
Loading file c:\temp\qq.lut as xml
Imported document: AlertTable.LUT
Load finished successfully.
1
Alexander Koblov · Aug 30, 2019 go to post

Hi Salma.

Check for files with extension LUT. They represent Lookup Tables. You can add these files to Studio Export.

Hope this helps, Alexander.

Alexander Koblov · Aug 29, 2019 go to post

Hi Matthias.

Try adding [Identity] to the attributes of property id in generated class.

Something like follows:

Property id As %Integer(EXTERNALSQLNAME = "id", EXTERNALSQLTYPE = 4) [ Identity, SqlColumnNumber = 2, SqlFieldName = ID ];

Hope this helps, Alexander.

Alexander Koblov · Aug 27, 2019 go to post

I understand that get data method only needs to return the Y-Axis values

Yes!

Do you think it is good to try and store values for x and y-Axes in the same data array?

No, keeping values in getChartData and labels for X axis in getAxisTime is fine.

Alexander Koblov · Aug 22, 2019 go to post

Good idea in such cases -- enable Audit and audit events LoginFailure and Protect. Reproduce the problem and then check Audit log.

Alexander Koblov · Aug 14, 2019 go to post

Hi Alexey.

I'm not aware of such event handler.

However, I wonder why do you need it at all? If you provide use case, perhaps we can advice some other way to achieve it.

Alexander Koblov · Aug 9, 2019 go to post

Hi Luis-Ángel.

Generally, if you can connect successfully then configuration is fine.

Does this happen with every query? If you try some simple query, like 'select 1', will this work ?

If yes, does your query work from Management Portal ?

Also, try enabling Audit and event Protect and check if this event is logged into Audit when this happens.

Alexander Koblov · Aug 5, 2019 go to post

Daniel,

you don't have DeepSee Model and DeepSee Analyze enabled in "Extended feature codes". That's why these menu options are grayed out.

Check with InterSystems Sales to get a license with these bits enabled.

Alexander Koblov · Jul 31, 2019 go to post

Not the smallest, but shortest -- 42 characters. Based on Robert's answer.

x $zwunpack("⁦㵩㨱㨱〱‰㩷⍩㴳‰䘢穩≺眠椺㔣&#x303d;∠畂空•㩷␧⁸⁩⁷‡")
Alexander Koblov · Jul 30, 2019 go to post

Seems to work. Please provide small standalone example, so that we can reproduce

C:\InterSystems\ENSEMBLE2017x2x2\bin>set AA=22

C:\InterSystems\ENSEMBLE2017x2x2\bin>echo %AA%
22

C:\InterSystems\ENSEMBLE2017x2x2\bin>cache -s ..\mgr

Узел: ru-akoblovW10VM, Экземпляр: ENSEMBLE2017X2X2

USER>w $system.Util.GetEnviron("AA")
22
Alexander Koblov · Jul 30, 2019 go to post

To get environment variable you can use $System.Util.GetEnviron().

And to get current folder -- $system.Process.CurrentDirectory().