%DeepSee.Utils:%FormatDate offers this options

Format a date value (in $H format) according to the format in pFormat. The format string can contain:

  • "y" - Year number (4 digits).
  • "q" - Quarter number.
  • "m" - Month number, with no leading zero.
  • "mm" - Month number, with leading zero.
  • "mmm" - Short name of month (using server locale).
  • "mmmm" - Long name of month (using server locale).
  • "d" - Day number, with no leading zero.
  • "dd" - Day number, with leading zero.
  • "ddd" - Short name of day (using server locale).
  • "dddd" - Long name of day (using server locale).
  • "\x" - display character "x"
  • " " - space
  • "/" - "/"
  • "-" - "-"
  • "." - "."

Added mm as Time Format and rebuilt cube. Got 01 instead of JAN.

Removed mm. Turns out, all you need to do is to use locale defaults in Locale Settings

I'd run a BS with SQL inbound adapter called every 86400 seconds. Query is some SELECT Count(*) ...

To get optimal WHERE condition you'll probably need to answer these questions:

  • Are records immutable?
  • Can they be updated?
  • Can records be deleted?
  • Can records be inserted for a past/future dates?

If it's a local table you can run BS without adapter and just use embedded SQL.

There are two use cases here:

  • File is tied to a specific object (for example you have "Document" class and it has "scan" file). In that case you can use %FileBinaryStream property - as before getting the file you would probably open "Document" object first
  • File is not tied to a specific object. In that case you can create a separate table "Files" that stores
    • link to file as a FileBinaryStream
    • hash
    • displayed file name
    • file path
    • user who uploaded the file
    • extension
    • size
    • any other attributes you need

It would always work faster compared to OS search.

Other notes:

  • Files are immutable - if you're building an application where user can edit files, it's usually preferable to have immutable "files" objects and just create new file versions.
  • File size limits - always define and check for maximum size.
  • Extension limits - limit extensions user can upload.
  • Storage - if it's a low volume inserts (<1000/day) store files in a folder = date, otherwise generate a new folder for each new thousand of files. These approaches can be combined: date/1, date/2 ...
  • Hash name - I often store files where their OS name is their hash. This way I can quickly validate that file is valid and also it  solves the problems with non-latin characters.
  • Never store files under names supplied by user. Acceptable filenames are: guid, hash (integer id should also be avoided).
  • GZIP - in some cases using GZIP streams can save on space, especially if it's a text file. For example XML envelopes and such.

Installer manifest can call arbitrary code.

  1. Mirrors can be managed via  ^MIRROR utility.
  2. Can be manipulated via Config.Journal class.
  3. You can configure automatic purging task and set a number of days to keep there. Docs.
  4. Tasks can be created and modified programmatically. Sample. They could also be exported and imported. Community discussion.

What's your current locale? If it's not Chinese you may want to change it. Note that it significantly affects system behavior and you should understand the implications in such a change.

???? often indicates the data loss during encoding conversions consider this Cyrillic  example:

set t = "Привет"
 
USER>zzdump t
 
0000: 041F 0440 0438 0432 0435 0442                           Привет
USER>zzdump $zcvt(t, "I", "UTF8")
 
0000: 3F 3F 3F 3F 3F 3F                                       ??????
USER>zzdump $zcvt(t, "O", "UTF8")
 
0000: D0 9F D1 80 D0 B8 D0 B2 D0 B5 D1 82                     Ð.Ñ.ивеÑ.

Note the difference between question marks and the last output. Last output is just encoding translation, but the first conversion - into questions completely lost the information, so you need to look deeper.

You can try to modify TCP/IP IO table via ^NLS utility in %SYS namespace

do ^NLS
 
 
1) Display current locale
2) Select defaults
3) Change locale
4) Display loaded settings
5) Advanced

NLS option? 2
 
1) Internal tables
2) I/O tables
3) CSP files
4) Date, time and number formats
 
Category of defaults? 2
 
Items marked with (*) represent the locale's original default
 
I/O table              Current default
---------------------  --------------------
 
1) Process             RAW (*)
2) Cache Terminal      UTF8 (*)
3) Other terminal      UTF8 (*)
4) File                UTF8 (*)
5) Magtape             UTF8 (*)
6) TCP/IP              RAW (*)
7) System call         RAW (*)
8) Printer             CP1251 (*)
 
I/O table: 6
 
 1) RAW (*)
 2) UTF8
 3) UnicodeLittle
 4) UnicodeBig
 5) CP1250
 6) CP1251
 7) CP1252
 8) CP1253
 9) CP1255
10) CP866
11) CP874
12) EBCDIC
13) KOI8R
14) Latin2
15) Latin9
16) LatinC
17) LatinG
18) LatinH
19) LatinT
 
Selection for TCP/IP: 1 =>

1. If you replace:

set sc=gc.SetParameter(hstmt,$LB(gc.PutData(hstmt,.temp)),2)

with:

set sc = gc.PutData(hstmt, "TEXT")

or with:

set sc = gc.SetParameter(hstmt,$LB("TEXT"),2)

What do you get?

2. Also in your original 4-parameter generated code the stream is bound via:

 s err=$zf(-5,%SQLGateway,62,QHandle,4,1,1,12,500,0,500)

which is equal to

set sc=gc.BindParameter(hstmt, 4,1,1,12,500,0,500)

In your new code you bind the stream with:

Set sc=gc.BindParameter(hstmt,2,1,1,12,0,0,-2)

Is it from some other generated code?

Connection is not license.  From docs:

  • The Caché licensing system attempts to identify distinct users and to allocate one license unit per user. A user is identified by a license user ID, which can be an IP address, a username, a CSP session ID, or some other identifier depending on how the user connects.
  • Multiple processes started by or for a single user share a license unit up to the maximum number of processes per user. If the number of processes exceeds this maximum, a transition occurs and Caché begins allocating one license unit per process for that user ID. The system assumes that if the number of processes associated with a user ID exceeds the maximum, multiple users are accessing Caché through an intermediary (for example, a firewall system), so additional license units are required. (Processes started by the Job command are counted under the user ID invoking the command.)
  • Even if the number of processes under the user ID drops back under the maximum, Caché continues to allocate one license unit per process for that user ID. Only when all connections by the user ID are closed and there are no more processes under the user ID does license allocation reset to one unit for that user ID.

Maximum number of processes (connections) per user is 12.

Will this affect our own internal tasks?

It shouldn't as that would be one connection and license slot.Still, I'd recommend contacting WRC to clarify the issue completely.

Maybe remove $zf calls?

Here's sample code:

 ClassMethod Test()
{
//Create new Gateway connection object
set gc=##class(%SQLGatewayConnection).%New()
if gc=$$$NULLOREF quit $$$ERROR($$$GeneralError,"Cannot create %SQLGatewayConnection.")
  
//Make connection to target DSN
set pDSN="Cache Samples"
set usr="_system"
set pwd="SYS"
set sc=gc.Connect(pDSN,usr,pwd,0) 
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
if gc.ConnectionHandle="" W !, $$$ERROR($$$GeneralError,"Connection failed") QUIT
  
set sc=gc.AllocateStatement(.hstmt) 
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
  
//Prepare statement for execution
set pQuery= "select Name, DOB from Sample.Person WHERE Name %STARTSWITH ?"
set sc=gc.Prepare(hstmt,pQuery) 
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
//Bind Parameters
set sc=gc.BindParameter(hstmt,1,1,1,12,30,0,30)
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
set var = "A"
set sc=gc.SetParameter(hstmt,$LB(var),1)
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
//Execute statement
set sc=gc.Execute(hstmt)
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
//Get list of columns returned by query
set sc=gc.DescribeColumns(hstmt, .columnlist) 
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
 
//display column headers delimited by ":"
set numcols=$listlength(columnlist)-1 //get number of columns
for colnum=2:1:numcols+1 {
    Write $listget($listget(columnlist,colnum),1),":"
   }
write !
 
//Return first 20 rows 
set sc=gc.Fetch(hstmt)
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
s rownum=1
while((gc.sqlcode'=100) && (rownum<=20)) {
       for ii=1:1:numcols {
       s sc=gc.GetData(hstmt, ii, 1, .val)
       w " "_val
       if $$$ISERR(sc) break
       }
       s rownum=rownum+1
  write !
  set sc=gc.Fetch(hstmt)
if $$$ISERR(sc) break
   }
    
  //Close cursor and then disconnect
set sc=gc.CloseCursor(hstmt)
if $$$ISERR(sc) w !, $SYSTEM.OBJ.DisplayError(sc) QUIT 0
  
set sc=gc.Disconnect()
  
quit sc
}

Check BindParameter method and EnsSQLTypes for SQL types macro definitions. Varchar maybe.

You can also try to call PutData method and similar methods (by passing parts of stream there).