All
Accepted answers
Alexander Koblov · Feb 22, 2018 go to post

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
Alexander Koblov · Feb 5, 2018 go to post

Zwrite prints all control characters. E.g.

USER>set a = "val1" _ $C(1) _ "val2"
 
USER>write a
val1val2
USER>zwrite a
a="val1"_$c(1)_"val2"

Also zzdump:

USER>zzdump a
 
0000: 76 61 6C 31 01 76 61 6C 32                              val1.val2

Generally, I would advise to use $ListBuild to separate records

Alexander Koblov · Jan 25, 2018 go to post

Generally, for long-running queries it makes sense to use external tools.

E.g. DBVisualizer or SQuirrel

Timeout for CSP Gateway to wait response from Caché is in Management Portal -> System Administration -> Configuration -> CSP Gateway Management.

Then Default Parameters -> Server Response Timeout.

Alexander Koblov · Jan 17, 2018 go to post

There is Preferences -> General -> Appearance dialog.

Where you can select Dark theme.

It seem to work OK.

Alexander Koblov · Jan 10, 2018 go to post

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.

Alexander Koblov · Dec 6, 2017 go to post

Hi Tani.

Having said that this seem to work OK on my Atelier 1.1.386 + Caché 2017.2,

you might check Error log and Network Activities for hints:

Window -> Show view -> Other ->

General -> Error Log and Atelier -> Network Activities

Alexander Koblov · Nov 16, 2017 go to post

You might use $ZEOF to check for file end.

It should be enabled first:

do $system.Process.SetZEOF(1)

Then you can read file line-by-line as follows:

  do $system.Process.SetZEOF(1)
  set filename = "c:\temp\qq.txt"
  open filename:"R":2
  if '$Test {
  	write "cannot open file ", filename, !
  	quit
  }
  for  {
  	use filename read str
  	quit:$ZEOF=-1
  	use $Principal write str,!
  }
  close filename
Alexander Koblov · Oct 10, 2017 go to post

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.

Alexander Koblov · Oct 10, 2017 go to post

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.

Alexander Koblov · Sep 5, 2017 go to post

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
}
Alexander Koblov · Sep 4, 2017 go to post

To autofill password fields, Atelier (Eclipse) stores password that developer enters in different wizards. For example password for connection to Caché, password for connection to Git repository, etc. Atelier stores passwords in secure place called secure storage. Passwords are stored in encrypted form.

Access to the secure storage is protected by master password.

So, when you open some wizard with password field first time after Atelier lunch Atelier asks for master password in order to get password from secure storage and autofill corresponding field.

Let us know what exactly you don't understand and I (or maybe someone else) else will try to explain it.

Alexander Koblov · Aug 30, 2017 go to post

Have you looked at $IsValidNum?

ClassMethod AssertNumberEquals(v1, v2) As %Boolean
{
    if '$isvalidnum(v1) quit 0
    if '$isvalidnum(v2) quit 0
    //both are numbers -- let's compare them as numbers
    quit +v1=+v2
}
Alexander Koblov · Aug 25, 2017 go to post

Notice, that in some cases it might produce different results:

USER>write ##class(%Library.Collate).SetLocalName("Cache standard"),!
1

USER>write "0.12345"]]$c(0)
1
USER>write ".12345"]]$c(0) 
0
USER>write ##class(%Library.Collate).SetLocalName("Cache standard string"),!
1

USER>write "0.12345"]]$c(0)                                                  
1
USER>write ".12345"]]$c(0)                                                   
1
Alexander Koblov · Aug 22, 2017 go to post

Hi Mike.

You can open view's popup menu by right-clicking inside view.

Please notice that "Workbench User Guide" is user-guide for Eclipse that is Atelier based on.

For Atelier guide, please see "InterSystems Atelier User Guide" located below "Workbench User Guide".

Alexander Koblov · Aug 21, 2017 go to post

$THIS is object reference (OREF) -- unique identifier of object in memory. Different objects might have the same OREF during process lifetime

And subscript of local/global can be only numeric or a string -- not an object reference.

So, while indeed $GET(a($THIS)) triggers something wrong, the construction itself is not correct.

Timothy's suggestion converts OREF to string:

if $GET(seen(""_$THIS)) quit

making command correct.

Notice, that in workaround you proposed -- you are using OID, that is unique identifier of object on disk. Different objects cannot have the same OID.

Alexander Koblov · Aug 10, 2017 go to post

I don't have any particular recomendations about openssl. I use openssl that icomes with linux I use.

If connection works Ok on 2016.1 (or 2016.2?) you might try to uncheck tls1.1 and tls1.2 in SSL/TLS configuration settings on 2016.1 installation, leaving only tls1.0 and see if connection succeeds. If no -- probably server requires tls1.1 or tls1.2.

Alexander Koblov · Aug 10, 2017 go to post

Can you connect to that server using openssl?

If yes, try to match protocol openssl uses with the protocols enabled in SSL/TLS Configuration.

E.g. if SSL/TLS Configuration have only TLS1 enabled, try to connect with openssl using -tls1

openssl s_client -tls1 -connect server:port

Maybe that server requires tls1.2 or SNI that is not available in Caché 2013.1

Alexander Koblov · Jul 19, 2017 go to post

No. You need to wrap it as you showed in second example.

You can use "C" alias from first sample only in "ORDER BY" clause in the same query, not in WHERE.

Alexander Koblov · Jul 18, 2017 go to post

Please notice, that execution plan for

select * from person where (lastname=? or ? is null) and (age > ? or ? is null)

might be less optimal than

select * from person where lastname=?

So in some sense it's better to generate different queries based on input