Thank you, Robert. I've updated it.

Sure! Let %Stream.FileCharacter do its job.

Nael,

I think you need to use 4th argument of $zconvert:

Set file=##class(%File).%New(..LocalFileName)
Do file.Open("R")
Set handle=""
While 'file.AtEnd { 
    Set Line=$ZCVT(file.Read() , "I", "UTF8", handle)
   // do something with Line
}
Do file.Close()

Handle "contains the remaining portion of string that could not be converted at the end of $ZCONVERT, and supplies this remaining portion to the next invocation of $ZCONVERT."

Please see reference for $zconvert

Thank you for sharing Mike.

Please notice that Dynamic SQL looks for the host variable in the global scope.

For example, consider following method:

Class Sample.Sqlbindtest [ Abstract ]
{

ClassMethod test()
{
     set minage = 80
 set myquery = 3
 set tStatement = ##class(%SQL.Statement).%New()
 set myquery(1) = "SELECT top 10 %ID AS id, Age , Name, %ODBCOUT(DOB) DOB, Home_State"
 set myquery(2) = "FROM Sample.Person WHERE Age > :minage"
 set myquery(3) = "ORDER BY 2"
 set qStatus = tStatement.%Prepare(.myquery)
 set tResult = tStatement.%Execute()
 do tResult.%Display()
}

}

The query will refer to minage variable defined at the variable scope outside of method itself:

SAMPLES>d ##class(Sample.Sqlbindtest).test()
id      Age     Name    DOB     Home_State

0 Rows(s) Affected

SAMPLES>set minage=20 // now we define minage

SAMPLES>d ##class(Sample.Sqlbindtest).test()
id      Age     Name    DOB     Home_State
41      21      Beatty,Emily I. 1997-01-21      ID
163     21      Mastrolito,David X.     1996-04-14      AL
32      22      Adam,Sophia V.  1995-12-12      CO
33      22      Xiang,Laura L.  1995-03-02      MN
87      22      Paladino,Violet P.      1995-09-14      MN
139     22      Goncharuk,Stavros D.    1996-01-24      RI
173     22      Zucherro,Dmitry R.      1995-03-28      AK
56      23      Zubik,Quigley N.        1994-03-26      ID
46      24      Rogers,Mo D.    1994-02-16      OR
142     24      Orwell,Zelda M. 1993-07-12      AL

10 Rows(s) Affected

I'm aware only of Edit -> Advanced item "Expand Commands" (Ctrl-E).

It is visible only for routines, but shortcut also works for classes.

Select some code, hit Ctrl-E. All commands, functions will be expanded to full name.

I usually do Ctrl-A, Ctrl-E.

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

Hi Chris.

As I understand %VID implies not the page number, but amount of rows to skip

So if you have pages with 10 records on each page and you need to show 3rd page then you should use

SELECT TOP (10) ...
 ...
WHERE %VID > 20 -- that is page number 2 multiplied by 10 

More info about %VID:
http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...

Hi Francisco.

Besides defining "/myapi" application in IIS you also need to add mapping of "*" extension to CSPms, as in "Registering Additional File Types with CSP" section of documentation. When adding this mapping ensure that "Invoke handler only if request is mapped to" is unchecked.

Hope this helps,
Alexander.

You might use %NOFLATTEN keyword.

Also, you might check if extentsize for the tables and selectivities for the columns are correct (http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=...).

 select $Translate('Mm/Dd/YEAR','YEARMmDd','20171116')

I would suggest you to run the query in terminal in $system.SQL.Shell() or using external tools via JDBC (DBVisualizer, Squirrel). By default Management Portal timeouts when query takes longer than 60 seconds.

Also, see Caché SQL Optimization Guide, especially section "Interpreting an SQL Query Plan". Maybe the chosen plan is not optimal.

Documentation says following:

For a table containing more than 1 million records, a bitmap index is less efficient than a standard index when the number of unique values exceeds 10,000. Therefore, for a large table it is recommended that you avoid using a bitmap index for any field that is contains (or is likely to contain) more than 10,000 unique values; for a table of any size, avoid using a bitmap index for any field that is likely to contain more than 20,000 unique values. These are general approximations, not exact numbers.

Notice that if you put space after ':' then it compiles OK:

Query T() As %SQLQuery [ SqlProc ]
{
SELECT TOP 3 JSON_OBJECT('lit': 'Employee from','t':%TABLENAME,'name':Name,'num':SSN) FROM Sample.Employee
}