All
Accepted answers
Robert Cemper · Apr 4, 2021 go to post

Instead of the double $ZF()  I'd suggest using the query in Library class %File.

 
set rs=##class(%ResultSet).%New("%File:FileSet")
do rs.Execute(SDIR,"*",1)
while rs.Next() { write !,rs.GetData(1) }
Robert Cemper · Apr 4, 2021 go to post

Let's analyze this:    [leaving aside that $Zf(-1 .. is Deprecated)

  1. You run the same sequence in 2 Caché/IRIS  processes in parallel.
  2. There is no synchronization between them
  3. Both run in the same namespace ==> therefore the same default directory where SDIR_"DIRLIST.TXT" is located ???? " .. but from a different folder... "    is SDIR different ????? if not :
  4. Both processes spawn 2 times a sub-process for each $ZF(-1)
  5. let's name the sub processes fg1, fg2 and bg1,bg2
  6. The sub_process from foreground is not synchronized to the sub-process of background and your construct has no control on the sequence they are running.
  7. If the run sequence  fg, fg1,fg2,bg,bg1,bg2  there should be no problem. But that's rater unlikely
  8. it looks like fg,bg,fg1,fg2,bg1 (partially deleting results of fg2), bg2  

To verify what's really happening run fg,fg1,fg1,bg (JOB ), bg1,bg2   
So they both can't influence each other.

Robert Cemper · Apr 2, 2021 go to post

When I was Software Support Manager @ Digital Equipment Corp. in a former life
in the previous millennium we had 2 basic rules graved in stone:

  1. The (contracted) customer is right.
  2. IF NOT, Rule #1 has to be applied.

I won with my team the annual European customer survey competition
over several years in sequence.

Robert Cemper · Apr 2, 2021 go to post

@Vic.Sun. your reaction is - to formulate it politely - a disappointment.
But no surprise to me.

Robert Cemper · Apr 2, 2021 go to post

I understood that 14 licenses + Grace Period are your biggest pains.
In past, I  decreased the risk by moving ALL (suspicious) connections to a dedicated  User
observing his maxConnection limit using %CSP.Session.Login() especially this explanation:

Login with this username and password, returns a status code to show if it worked or not. This method also trades license units at the same time so this CSP session will be logged in as a named user. If you pass type=1 then this will trade licenses only and not login as this user.

This can't avoid a DDoS attack but it limits the initial impact and allows you to
protect some emergency licenses.

[As I know the inventor of Grace Period since it was rolled out with a lot of pain for customers
I'm not willing to discuss this "feature" in public again]

Robert Cemper · Apr 1, 2021 go to post

Yes, pls. share!
I always liked to see not just the ONE and ONLY solution but a choice.  
 

Robert Cemper · Mar 31, 2021 go to post

as you are in SMP anyhow just try to do an insert manually.
Just 1 row.
you seem to require something else <UNDEFINED>
in addition, if your record exists already You may need an 
INSERT OR UPDATE   (if this is possible in postgreSQL)
It could be OK, but I wonder that you insert just 1 value
Eventually, there is something required that is missing?
Check the definition of the generated Caché class,
 

Robert Cemper · Mar 31, 2021 go to post

Seems just a typo in INSERT statement.
Target Columns must be in parenthesis.

INSERT INTO HSIPIData.datos_usarios2 (addresses)
       SELECT Addresses FROM HSPI_Data.patient

Robert Cemper · Mar 30, 2021 go to post

you are mixing up 2 things:

  • one is the class that stores data in Caché
  • the Class generated as LINKED TABLE that stores its data in postgreSQL.'

so the LINKED describes the structure that is used by generated SQL statements
to work on postgreSQL. Take a look at the storage definition of the class
and see the difference.
But your code in Cache acts as if data were local !!
Either by Objects or by Tables. 
if you issue a %Save() on the class it runs a INSERT OR UPDATE undercover.

Robert Cemper · Mar 29, 2021 go to post

a few simple steps.

  • if not existing yet create a table on Postgres with the identic columns that you see in Cache or use an existing target
  • next use SMP > SQL > Wizzard > Link Table
  •  
  • It creates a class /table that reflects all columns /properties of the linked table. + storage method that can read, write, delete, insert over ODBC / JDBC on the postgreSQL table.
Robert Cemper · Mar 25, 2021 go to post

similar to the proposal of @Timothy Leavitt you use a Sub-Select for sorting
and do the output in the outer SELECT 

SELECT Name, SSN, Home_State, Home_Street,  Age, Company, DOB  FROM (
        SELECT top all *  FROM Sample.Person
        order by ID desc
)
 As you use Caché you may try in Namespace SAMPLES

Robert Cemper · Mar 25, 2021 go to post

In a recent example I posted,  I had the need to extend the naming and proposed folder structure
It was obvious if you were reading the downloaded repository. 
The related article was an advertisement and a "heads up" that it just wasn't the default structure as usual.

Robert Cemper · Mar 24, 2021 go to post

So you have to examine this special record in detail in the underlying Global.
Seems to be a broken record that you hit by accident.

Robert Cemper · Mar 23, 2021 go to post

OK. this looks like the first record you find is already in trouble.
I'd expect your data type is something else then stored.
Next omit the conversion %ODBCOUT()  just to see what internal content is and where it starts

  select ID,%INTERNAL(VerzamelDatTijd),
        DateTijdSec from GLPPatTcActie
         where pnr = '27085070017'
         and LTestId->Makey='BLA'
         and glpactieid->makey in ('TAV','TMA') 
The next question is of course which program writes these values? and how? what's the source?

I get more and more the impression that some 'aged' program writes directly into the global.
Probably directly some $HOROLOG which is a combined %DATE,%TIME construct.
 

Robert Cemper · Mar 23, 2021 go to post

OK!
Now I see the problem. Each datatype has its specific conversion from internal format to ODBC format
So if VerzamelDatTijd is %Date or %Time or %Timestamp and the stored content doesn't fit
you get most likely this error.
It could be by principle := it fits never, a design issue
It could be just a glitch in some data record.
I suggest you start the SQL shell from terminal prompt.  USER>do $system.SQL.Shell()
And then run your modified query:
select  ID,%INTERNAL(VerzamelDatTijd),%ODBCout(VerzamelDatTijd),DateTijdSec
from  GLPPatTcActie 
 where pnr = '27085070017' and LTestId->Makey='BLA'
-- and VerzamelDatTijd < '2021-03-04 09:04'
and glpactieid->makey in ('TAV','TMA')
-- order by DateTijdSec desc

ID shows the flaky record
%INTERNAL() shows the raw content
%ODBCOUT() should trigger the error

Once you know the content you should be able to fix it.

Robert Cemper · Mar 23, 2021 go to post

what date time format would you expect this to be ???? 

'2021-03-04-2021 09:04'  

YYYY-MM-DD-YYYY HH:mm

Not really standard! Rather a wrong DateTime in your Query !

Robert Cemper · Mar 19, 2021 go to post

for Caché ODBC/JDBC drivers are part of standard distribution since ever

%installdir%/bin/CacheODBC.dll
%installdir%/bin/CacheODBC64.dl

Or use your Caché distribution kit and run a custom install with ODBC only

Robert Cemper · Mar 19, 2021 go to post

Well,

You can either use default SQL function UPPER() or LOWER() to force a specific case. e.g:

SELECT * FROM erp.Teste2 WHERE 
$TRANSLATE(LOWER(firstname),'áéíóúçâêîôû','aeioucaeiou') like 'fabio'

or 

SELECT * FROM erp.Teste2 WHERE 
$TRANSLATE(UPPER(firstname),'ÁÉÍÓÚÇÂÊÎÔÛ','AEIOUCAEIOU') like UPPER('Fabio')
Robert Cemper · Mar 19, 2021 go to post

Hi @Fábio Campos 
I see your problem. I don't assume that "Portuguese1" does solve this
So I see 3 workarounds to achieve "shaving" of characters.

#1)  quick & dirty
SELECT * FROM erp.Teste2 WHERE 
$TRANSLATE(firstname,'áéíóúÁÉÍÓÚçÇâêîôûÂÊÎÔÛ','aeiouAEIOUcCaeiouAEIOU') like 'fabio'
#2) more  elegant and also for other columns - create a SqlProcedure

ClassMethod shave(par) as %String [SqlProc, SqlName = "shave" ] 
{  quit $TRANSLATE(par,"áéíóúÁÉÍÓÚçÇâêîôûÂÊÎÔÛ","'aeiouAEIOUcCaeiouAEIOU") }


applied as 
SELECT * FROM erp.Teste2 WHERE erp.shave(firstname) like 'fabio'
#3) calculated property in your table - to be available also in index
Property firstshaved as %String [Calculated,SqlComputed,
SqlComputeCode = {set {*}=$TRANSLATE(firstname,"áéíóúÁÉÍÓÚçÇâêîôûÂÊÎÔÛ","aeiouAEIOUcCaeiouAEIOU")} ];
applied as 
SELECT * FROM erp.Teste2 WHERE firstshave like 'fabio'

Robert Cemper · Mar 18, 2021 go to post

@Alexey Maslov 
This doesn't compile if the calling method doesn't have  [ ProcedureBlock = 0 ] 

ERROR #1038: Private variable not allowed : '..Flatten2($name(agg),.summary)'
calling from terminal has no problem as there are all variables in Global Scope
[ I fell also in this trap smiley]