AllAccepted answers
Julius Kavay · Aug 24, 2019 

Class community.counter Extends %RegisteredObject
{
/// Example:
/// set ^x(1)=111
/// set ^x(3,5)=222
/// set ^x(3,7)=333
/// 
/// The above global has 5 nodes:
/// ^x without a value
/// ^x(1) with value
/// ^x(3) without a value
/// ^x(3,5) with value
/// ^x(3,7) with value
/// 
/// write ##class(community.counter).CountQ($name(^x)) --> 3
/// write ##class(community.counter).CountR($name(^x)) --> 3
/// 
/// Using your example:
/// write ##class(community.counter).CountQ($name(^Locations)) --> 5
/// write ##class(community.counter).CountQ($name(^Locations("USA")) --> 3
/// 
/// 
/// N.B.
/// Recursion is a tricky thing!
/// It helps one to get a clearly laid out solution
/// but you should take care about runtimes.
/// 
/// CountQ(...) is about 4-5 times faster then CountR(...)
/// 
/// --------------------------------------------------------
/// 
/// Return the count of nodes of a global- or a local variable
/// which have a value, using $QUERY() function
/// 
/// node:
/// a local or global variable, example: $na(^myGloabl), $na(abc)
/// or a local or global reference example: $na(^myGlobal(1,2))
/// 
ClassMethod CountQ(node) As %Integer
{
 if $data(@node)#10 { set sum=1 } else { set sum=0 }
 while 1 { set node=$query(@node) quit:node=""  if $increment(sum) }
 quit sum
}

/// Return the count of nodes of a global- or a local variable
/// which have a value, using recursion, using recursion
/// 
/// node:
/// a local or global variable, example: $na(^myGlobal), $na(abc)
/// or a local or global reference example: $na(^myGlobal(1,2))
///       
ClassMethod CountR(node) As %Integer
{
 set sum=0
 do ..nodeCnt($name(@node), .sum)
 quit sum
}

ClassMethod nodeCnt(ref, ByRef sum) As %Integer [ Internal, Private ]
{
 if $data(@ref)#10, $increment(sum)
 set i=""
 while 1 { set i=$order(@ref@(i)) quit:i=""  do ..nodeCnt($na(@ref@(i)),.sum) }
}

}
Julius Kavay · Aug 19, 2019

Some hints/questions:

  • did you installed the correct driver? I mean, do have Cache/Iris and the
    driver the same architecture (32 vs. 64 bit)?

  • I'm not an MySQL expert (I do not even have an MySQL DB), so I ask, is
    there any problem with the character size (1 byte vs. 2 bytes/unicode), if
    this applies?

  • with a DB-Tool, like WinSQL, try to make an SELECT (as suggested by Eduard Lebedyuk) statement. What is the error message?

Julius Kavay · Aug 16, 2019

HELP: what are the markdown characters?

In the above answer, I put some words between angle braces, now all they are lost!
OK, I try once again with an apostrophe.

1) set stat=conn.Connect("odbcname", "username" ,"password")
"username" is a MySQL user.

5) If the content of the variable "variableX" is an alphanumeric value then put a single quote char (') around the value. Beforehand remove (or replace) all single quotes (with something else) from "variableX".

Julius Kavay · Aug 16, 2019

Some thoughts/hints to your problem:

1) set stat=conn.Connect(, ,
)
is a MySQL user.
Does this user have the rights for UPDATE and INSERT?

2) What are the status codes after
- set sc=conn.Prepare(...) and
- set sc=conn.Execute(...)?

3) Your query-string should have blanks after the table name and also before and after the VALUES keyword.

4) If the variables value1, value2, ... valueN CAN CONATIN a backslash character then you should either duplicate them
set valueX = $replace(valueX, "\", "\") or switch the escaping off at the start your query-string :
set pQuery="SET sql_mode='NO_BACKSLASH_ESCAPES'; INSERT INTO ..."
see also: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html

5) If the content of the variable is an alphanumeric value then put a single quote char (') around the value. Beforehand remove (or replace) all single quotes (with something else) from .

Julius Kavay · Aug 15, 2019

To make a connection to a MySQL Database via ODBC is easy.
In the next steps I assume you are on a Windows system (Linux/Unix is similar):

1) download (https://dev.mysql.com/downloads/connector/odbc/) and install the proper ODBC driver
2) go to ControlPanel-->AdministrativeTools-->DataSources(ODBC)
3) select the SystemDNS-Tab, click Add
- give a unique name to this datasource (and remember it for step 4)
- fill in the necessary fields as desired
4) use the %SQLGatewayConnection class to get/put your data from/into MySQL,
something like:

 set gtw=##class(%SQLGatewayConnection).%New()
 if gtw.Connect(datasourceName, user, pass) {
    // do your work
 } else {
    // can't connect
 }
Julius Kavay · Aug 13, 2019

If you work in Cache-Terminal, go to:
ManagementPortal-->SystemAdmin-->Configurations-->DeviceSettings-->DeviceSubtypes

Select: C-Cache-Terminal, click on Edit
Go to bottom line (Caption: ZUFormFeed) and change the value from $C(27,91,72,27,91,74) to your needs.
For example, if you want to retain the first and the second lines, to: $C(27,91,51,59,48,72,27,91,74).

In case, your device type is not the Cache-Terminal, search for your device in
ManagementPortal-->SystemAdmin-->Configurations-->DeviceSettings-->Devices

Select your device, for example TERM, |TNT| or whatever device type you have.
Click on edit and notice the Sub-Type.
Now, as above, go to the sub-types, and change the ZUFormFeed field.

In case, you wont find your device and/or device sub-type, there is a direct way too,
to set the desired FormFeed (i.e. write #) and the Backspace behavior:

do ##class(%Device).SetFFBS($C(27,91,51,59,48,72,27,91,74),$c(8,32,8))
The first sequence, as the method name implies, is the FormFeed sequence and
the second is the Backspace sequence

Julius Kavay · Aug 13, 2019

If you have your binary or character data in (sequential) global nodes, then:
- create your own stream clas
-in the %OnNew() method fetch the data from your global

and voila, you are ready to use your data with stream methods.

set ^mydata(1)="abcd"
set ^mydata(2)="efgh"

set ^yourdata("id",1)="1234"
set ^yourdata("id",2)="5678"

Class Test.Stream Extends %Stream.TmpBinary
{
Method %OnNew(Global, initval As %String = "") As %Status [ Private ]
{
Set i%%Location=$select(initval="":$$$streamGlobal,1:initval)
do ..Clear()
for set Global=$query(@Global,1,data) quit:Global="" do ..Write(data)
do ..Rewind()
Quit $$$OK
}
}

set stream=##class(Test.Stream).%New($na(^mydata))
while 'stream.AtEnd { write stream.Read(5) }
abcdefgh

set stream=##class(Test.Stream).%New($na(^yourdata("id")))
while 'stream.AtEnd { write stream.Read(5) }
12345678

Julius Kavay · Mar 12, 2019

If you need to reorder a given set of characters (as above, from "12/03/2019" to "2019-03-12") then the $translate(...) function, used in "backward-mode" is your best friend.

set p="12/03/2019"
write $tr("abcd-ef-gh","ef/gh/abcd",p) --> 2019-12-03

Your gain: 50% of speed.

Nota bene

(1) $translate(inputdata, fromChars, toChars) --> outputdata
(2) $translate(toSequence, fromSequence, inputdata) --> outputdata

Where (1) is the Forward-Mode (the regular usage) and (2) the Backward-Mode

Julius Kavay · Mar 1, 2019

The correct way is:

ClassMethod ind() [ PublicList = (active,reactive) ]
{
  new active,reactive

  kill info
 // the above command (kill info) is a kind of NOP (no-operation)
 // at the time of entry into a methode there are no variables,
 // except those in the public list (if defined)

  set active = 1
  set reactive = 2

  for i="active","reactive" {
    set info(i)= @i
  }

  zw info

  break
}

If you want to use name-indirection in a procedure block, then

- variables, used by indirection, must be made public (via PublicList)
- and, depending on the situation, should be saved with the NEW command
Julius Kavay · Jan 17, 2019

"like a centipede with a wooden leg: 99 times tic and 1 toc
the stream is then truncated and still requires extra coding"

That's the whole point!
If we turncate the stream to $$$MaxStringLength, no matter where (in a calculated property or in a GETter method), just to present this (string)value to a variable, a function or whatever, then we can instantly stick to a string.

By the way, it's possible to store $$$MaxStringLength bytes in a local or global variable.
Properties of classes are stored in globals as $listelements, so the maxlength for a single string property depends on things like count and types of other properties in the class and not least, does this class extends %Persistent or extends another class(es) which extends %Persistent - in this case we need some extra bytes for those (extended) class names too.

Julius Kavay · Jan 17, 2019

Supposed, you have a magical datatype of %StringOrMaybeStream and your property would be something like this:

Property TheText As %StringOrMaybeStream

So the next question is, how would your application use this magical property?
The very first question is, how would you put your data into this magical property?

Give me some (hypothetical) examples of use and I will try to give you a solution, if there is any.