User comments | InterSystems Developer Community

Robert Cemper · Jan 31, 2025 go to post

this fails with <ILLEGAL VALUE> if time part is negative (before 3AM)

Robert Cemper · Jan 28, 2025 go to post

I think SQL is correct.
The. problem is caused by INSERT or UPDATE

for UPDATE NVL takes an existing field from a record and handles NULL
for INSERT there is just no existing record to take a field from

NVL() is not a synonym for $GET() we know from ObjectScript

Robert Cemper · Jan 23, 2025 go to post

Take a look into OpenExchange there are 669 packages using Docker
Which means at least 90% of them are running on the latest free Community Versions down loaded.
I bet there are enough examples that fit your needs

Robert Cemper · Jan 22, 2025 go to post

something like this ?

| COL    | VAL |
| ------ | --- |
| codRep |        401,       428,       428,       464,       472 |
|  Abril |     100000,    180000,    160000,         0,         0 |
| Agosto |     100000,    350000,    200000,     90000,         0 |

then this is the SQL statement

SELECT 'codRep' "COL", list($JUSTIFY(codRepresentante,10)) "VAL"
   FROM Ped.MetasRepresen where ano=2024
Union All
SELECT ' Abril', list($JUSTIFY(vendasAbril,10)) 
   FROM Ped.MetasRepresen where ano=2024
Union All
SELECT 'Agosto', list($JUSTIFY(vendasAgosto,10))
   FROM Ped.MetasRepresen where ano=2024

Robert Cemper · Jan 22, 2025 go to post

I understand that you want to have full control of your version
Increment and Decrement eventually also more than just +1,-1
so VERSIONPROPERTY is a dead herring.
BUT: You can achieve this in combination with a little SQL method.

Property RowVer As %Integer [
   SqlComputeCode = { if $i({*},$g(%IncDec)) },
   SqlComputed,
   SqlComputeOnChange = (%%INSERT, %%UPDATE) ];
ClassMethod IncDec(step As %Integer = 0) As %Boolean [
      SqlName = IncDec, SqlProc ]
{
    set %IncDec=step quit 1
}

Now you can set the increment to any %Integer of your choice.
e.g. -1 decrement by 1, 1 increment by 1, 0 leave it

How to use it:

INSERT OR UPDATE pck.myTable
    SET name='Omer'
    WHERE pck.IncDec(-2)=1
    AND .... any other conditions ....

the IncDec SQLmethod is used as a static method
it doesn't reference any row dependency
So it is executed once before any row related processing.
if you omit it then row_version is not changed

Robert Cemper · Jan 22, 2025 go to post

the posted link is limited to HS licensed users only

HERE is an official public link: VERSIONPROPERTY
It does basically the same as my previous SqlComputeOnChange example
hidden and with less typing

Robert Cemper · Jan 21, 2025 go to post

I feel deeply honored and congratulate all other winning contributors!
🌟🌟🌟🌟🌟🌟🌟

Robert Cemper · Jan 21, 2025 go to post

what you see as \x.... is the hex image of a $LB("SERVERA.FOO.BAR.ORG/STAGE", ......)
try ZZDUMP of any $LB() and you see length + type + content
\t is the misinterpretation of length x\09
my guess it's the hex_dump of some object

Robert Cemper · Jan 21, 2025 go to post

Calculated doesn't do it.
BUT;
[ SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ]

Robert Cemper · Jan 20, 2025 go to post

Yes i! I'm quite sure!
DSTIME was introduced with or short after release of DeepSee more
than a decade back and didn't change.

Robert Cemper · Jan 20, 2025 go to post

you have to add you own RowVersion property.
my example only increases the version if there was a any change in the row.

Property RowVer As %Integer [ SqlComputeCode = { if $i({*}) }, 
       SqlComputed, SqlComputeOnChange = (%%INSERT, %%UPDATE) ];

works for SQL- and Object-mode

Robert Cemper · Jan 20, 2025 go to post

As you refer to CSP combined with a Steam I assume
you have some similar sequence in you CSP page

<p align="center">
<!-- The trick is the use the encrypted oid of the stream as the STREAMOID parameter to the stream server -->
<image src="%25CSP.StreamServer.cls?STREAMOID=#(..Encrypt(oid))#">
</p>

Docs for StreamServer

That's where your stream is dumped to browser

Robert Cemper · Jan 17, 2025 go to post

Almost 6 years back I wrote and article on data synchronization
Using DSTIME and a related example in OEX.
It is focussed on detecting and optimizing insert, change, delete of specified Tables/Classes
and it takes care of processing cycles to avoid duplications.
So the output might be minimized.
Transmission speed to PostgreSQL is not part of the example

Robert Cemper · Jan 14, 2025 go to post

like this:

/// using $ZZFIX() custom function
Class rcc.GetFixZZ Extends %Library.String
{
Parameter LENGTH As %String = 10;
Parameter ALIGN As %String = "LEFT";
Parameter PADCHAR As %String = " ";
Method Get() As %String [ CodeMode = generator, ServerOnly = 1 ]
{
	set code=+%parameter("LENGTH")_","""_$E(%parameter("PADCHAR")_" ",1)_""""
	set code=code_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"
	$$$GENERATE(" quit $ZZFIX(%val,"_code )
	QUIT $$$OK
}
ClassMethod StorageToLogical(%val As %String) As %String [ CodeMode = generator, ServerOnly = 1 ]
{
	set code=+%parameter("LENGTH")_","""_$E(%parameter("PADCHAR")_" ",1)_""""
	set code=code_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"
	$$$GENERATE(" quit $ZZFIX(%val,"_code )
	QUIT $$$OK
} }

Robert Cemper · Jan 14, 2025 go to post

Though, if you need this functionality over multiple namespaces
I'd suggest to use a Language Extension:
#1 an .inc for the the code definition in namespace %SYS

ROUTINE ZZFIX [Type=INC]
/// fix length string + padding + l/r-adjustment
ZZFIX(%val, len , pad = "", right = 0)
        if right quit $e($tr($j("",len)," ",$e(pad_" "))_%val,*+1-len,*)
        quit $e(%val_$tr($j("",len)," ",$E(pad_" ")),1,len)

#2 add this line to %ZLANGF00.mac

#include ZZFIX

after compiling it you may run a test in any namespace

USER>set test="213abc"
USER>write $ZZFIX(test,10,"*")
213abc****
USER>write $ZZFIX(test,10,"*",1)
****213abc
USER>write $zzfix(test,5,"*",1)
13abc
USER>write $zzfix(test,15,"$")
213abc$$$$$$$$$
USER>write $zzfix(test,15,"$",1)
$$$$$$$$$213abc
USER>

If available this could of course also replace the
ClassMethod in the DataType definition.
It's a matter of taste.

Robert Cemper · Jan 14, 2025 go to post

I just felt the need of the last extension to adjust it to reality.

A side effect: because of the implementation as Classmethod

ClassMethod Fix(%val, rcc, rccp = "", rccal = 0) As %String

You can apply it dynamically to any String/Number that you get in hands.
(that's how I tested the core functionality)

Robert Cemper · Jan 14, 2025 go to post

Thank You! That makes it clear.

Robert Cemper · Jan 13, 2025 go to post

Thank you @Jeffrey Drumm !
Yet another idea:
Sometimes you may want a static non-blank padding character.
e.g. ****123 or 345~~~~
this is included as Parameter PADCHAR as %String =" " ; default= blank

/// padding other than " " might be useful in some cases
Class rcc.GetFixSqlPad Extends %Library.String
{

Parameter LENGTH As %String = 10;

Parameter ALIGN As %String = "LEFT";

Parameter PADCHAR As %String = "";

Method Get() As %String [ CodeMode = generator, ServerOnly = 1 ]
{
    set code=+%parameter("LENGTH")_","""_$E(%parameter("PADCHAR")_" ",1)_""""
    set code=code_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"
        
    $$$GENERATE(" quit ##class(rcc.GetFixSqlPad).Fix(%val,"_code )
    QUIT $$$OK
}

ClassMethod StorageToLogical(%val As %String) As %String [ CodeMode = generator, ServerOnly = 1 ]
{
    set code=+%parameter("LENGTH")_","""_$E(%parameter("PADCHAR")_" ",1)_""""
    set code=code_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"
        
    $$$GENERATE(" quit ##class(rcc.GetFixSqlPad).Fix(%val,"_code )
    QUIT $$$OK
}

/// PADCHAR=" "; LEFT=0, RIGHT=1 
ClassMethod Fix(%val, rcc, rccp = "", rccal = 0) As %String
{
    if rccal{
        set %val=$e($tr($j("",rcc)," ",$e(rccp_" "))_%val,*+1-rcc,*)
        }
    else {
        set %val=$e(%val_$tr($j("",rcc)," ",$e(rccp_" ")),1,rcc)
        }	
    quit %val
}

}

Robert Cemper · Jan 12, 2025 go to post

This doesn't work for SQL - Therefore a new version

Class rcc.GetFixSql Extends %Library.String
{

Parameter LENGTH As %String = 10;
Parameter ALIGN As %String = "LEFT";
Method Get() As %String [ CodeMode = generator, ServerOnly = 1 ]
{
    set code=+%parameter("LENGTH")_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"
    $$$GENERATE(" quit ##class(rcc.GetFixSql).Fix(%val,"_code )
    QUIT $$$OK
}

ClassMethod LogicalToDisplay(%val As %String) As %String [ CodeMode = generator, ServerOnly = 0 ]
{
    set code=+%parameter("LENGTH")_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"
    $$$GENERATE(" quit ##class(rcc.GetFixSql).Fix(%val,"_code )
    QUIT $$$OK
}

ClassMethod LogicalToOdbc(%val As %String) As %String [ CodeMode = generator, ServerOnly = 1 ]
{
    set code=+%parameter("LENGTH")_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"
    $$$GENERATE(" quit ##class(rcc.GetFixSql).Fix(%val,"_code )
    QUIT $$$OK
}

// only required for SQL Display Logical mode !
ClassMethod StorageToLogical(%val As %String) As %String [ CodeMode = generator, ServerOnly = 1 ]
{
    set code=+%parameter("LENGTH")_","_("RIGHT"=$zcvt(%parameter("ALIGN"),"U"))_")"
    $$$GENERATE(" quit ##class(rcc.GetFixSql).Fix(%val,"_code )
    QUIT $$$OK
}

/// LEFT=0, RIGHT=1 
ClassMethod Fix(%val, rcc, rccal = 0) As %String
{
    if rccal{
        set %val=$e($j(%val,rcc),*+1-rcc,*)
        }
    else {
        set %val=$e(%val_$j("",rcc),1,rcc)
        }	
    quit %val
}

}

Robert Cemper · Jan 12, 2025 go to post

A more compact data type with LENGTH and ALIGN
For RIGHT alignment I cut off oversized data by LENGHT counted from the end.

Class rcc.GetFixAligned Extends %Library.String
{
Parameter LENGTH As %String = 10;
Parameter ALIGN As %String = "LEFT";

Method Get() As %String [ CodeMode = generator, ServerOnly = 1 ]
{
    set rcc=+%parameter("LENGTH")
    if "RIGHT"=$zcvt(%parameter("ALIGN"),"U") {
        set code="$e($j(%val,"_rcc_"),*+1-"_rcc_",*)"
        }
    else {
        set code="$e(%val_$j("""","_rcc_"),1,"_rcc_")"
        }	
    $$$GENERATE(" Quit "_code)
    QUIT $$$OK
}
}

Robert Cemper · Jan 12, 2025 go to post

Sorry, no idea! It was the first time I met this suspicious parameter.
My personal style is quite traditional.

Robert Cemper · Jan 11, 2025 go to post

by extending %RegisteredObject you lost the basic data type
and all other parameters of %String
this should do it:

Class rcc.FixStr Extends (%Library.String, rcc.FixStr.Props)
{
/// Fill value according to LENGTH parameter
Method Get() As %String [ CodeMode = generator, ServerOnly = 1 ]
{
	set tCode="$e(%val_"""_$j("",+%parameter("LENGTH"))
	set tCode=tCode_""",1,"_+%parameter("LENGTH")_")"
	$$$GENERATE( "  Quit "_tCode)
	QUIT $$$OK
}
}

Robert Cemper · Jan 8, 2025 go to post

MySQL doesn't like TOP. Use LIMIT instead
You have an error in your SQL syntax

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

found here: https://www.w3schools.com/sql/sql_top.asp

Robert Cemper · Jan 3, 2025 go to post

My personal congratulations to the winners to complete the challenge in time
👍👏😎

Robert Cemper · Jan 2, 2025 go to post

different approach:

SELECT LI.id from FDRD_Com.List LI
JOIN FDRD_Com.Prod PR
ON LI.vehicle = PR.Car 
WHERE PE.ProductLineName='Toyota'
AND PR.Car is not null  -- might be obsolete

Robert Cemper · Jan 2, 2025 go to post

IN is expecting a list.

Did you ever try

select id from FDRD_Com.List where vehicle IN
(select LIST(Car) from FDRD_Com.Prod where ProductLineName='Toyota' and Car is not null)

on the sub select ?

Robert Cemper · Dec 31, 2024 go to post

As an active participant you know the practical case.
Though most readers that never touched AOC might be confused.
The original intention was to exclude parts not solved in ObjectScript:
(e.g Java, C# JS, ....)
It turned out this was not possible to implement with acceptable effort.

Robert Cemper · Dec 31, 2024 go to post

Big THANK YOU @Jani Hurskainen for sharing your story.
I'm impressed by the progress you made on ObjectScript
after only 1,5 years maybe 30% of working time,

AoC examples are definitely different from the target cases ObjectScript was designed for.
Its top target is the underlaying database, string manipulation and less math functionality.

My best wishes for Health and Success for the New Year
Robert

Robert Cemper · Dec 29, 2024 go to post

In 2021 I published my article IRIS easy ECP workbench
with a related a Docker based demo on OEX.
All you need is the ECP enabled license for containers or Platform Independent
Community version is not ECP enabled

Robert Cemper · Dec 27, 2024 go to post

Only 2 completed Packages from GitHub >
installed and verified with 2 test sets fulfill requirements.
You have to provide your test data set manually.

@Abdul Manan : see reply
@Yuval Golan : https://github.com/uvg/AdventOfCode2024Uvg

The contest is closed.

User statistics