How can I translate this Excel macro to PxPlus?

function main(workbook: ExcelScript.Workbook) {
let sheet1 = workbook.getWorksheet("sheet1");
// Add a new table at range A1:L10 on sheet1
let newTable = workbook.addTable(sheet1.getRange("A1:L10"), true);
}


Using try-catch allows us to continue program execution but it doesn't provide any useful information about the reason for the exception.

Please take a look at my sample code
begin
try
call pgn+";LABEL1"
catch
print err, " ", err("stno"), " ", err("program")
print "--- error history ---"
maxIndex = prm('EH')
for local i = 1 to maxIndex
print str(i : "##0"),": ", err("err", i, err = *break), " ", err("program", i), " ", err("stno", i)
next
end_try
print "End"
end
LABEL1:
call pgn+";LABEL2"
exit
LABEL2:
call pgn+";LABEL3"
exit
LABEL3:
string2Convert$ = "not a number"
numValue        = num(string2Convert$)
exit

I think it is challenging to provide a complete dump at the point where the error occurs. But it should be possible to provide a small dump of all the variables used in the error statement. That dump could be provided by a new keyword, e.g. err("DUMP").
The information will allow us to find the reason for the exception and apply proper corrections.

We want to disable variables by globbing patterns and by using a generic approach.

suggestions:
dumb disable "%param_*"
dumb disable via varName$

In our application we create a lot of global parameter variables, which don't need to be part of a dump.
Globbing patterns and generics would allow us to exclude these and all nomads variables.

I can't manage to sent a txt-file as an attachment with *web/email (PxPlus 20.10).
The mail doesn't contain the originally attached file. Instead of this there is an attachment called ATT00001.htm containing the body text. I tried to force BASE64 encoding with the ?B suffix, but that didn't change anything. Other file types like png, xlsx, csv and pdf work fine.
*web/webserv.mim contains text/plain. That seems to be correct.
What am I doing wrong?

We use an own implementation of SMTP if the user runs a mail server on his own.
This doesn't work with the MS server, as it requires TLS.
What is best practice for using that server from a Linux host?

I receive an error 15 with

select count(distinct columnName) from table

msg(-1)=37000: [PxPlus][SQL ODBC Driver]Expected lexical element not found: <identifier>

How can I count distinct column values using ODBC 7.0.3?

I don't understand why the code below throws an err 61 sometimes


BUSY_FILE_NAME$ = "[lcl]Busy.txt"
serial BUSY_FILE_NAME$, err = *next
if tcb(2) {
rem in case the file could not be created ...
} else {
busyFile = hfn
open lock (busyFile) BUSY_FILE_NAME$
rem this throws error 61 ???
}


We encounter problems with a newer version of libmariadb.so.3
PxPlus 20.10 is installed on a Linux server.
The program below runs with version 3.1.13 on a Linux server. With version 3.2.7 the result is empty and msg(-1) returns "MYSQL:0:".

begin
dbOpt$ = "SERVER=my_server;USER=my_account;PASSWORD=my_password;DATETIME_CONVERT=N"
dbName$ = "[MYSQL]my_database;;"
channel = hfn
open (channel, OPT = dbOpt$, tim = 10) dbName$
query$ = "SELECT * FROM `myTable` limit 1"
write record (channel)query$
gosub buildPVXIOList
repeat
read record(channel,err=*next)sqlResultItem$
if tcb(2) {
print msg(err)
print msg(-1)
break
}
print ++c,": ",sqlResultItem$
until 0
close( channel)
end
buildPVXIOList:
local sqlIOL$    = ken(channel, ind = 3)
local pvxIOL$    = "IOLIST "
local separator$ = $$
local items      = pos(";" = sqlIOL$, 1, 0)
local counter    = 0
local virtual    = 0
repeat
fieldDefinition$ = arg(sqlIOL$, counter + 1, ";")
read data from fieldDefinition$, sep = "," to fieldName$, fieldType$
fieldName$  = tbl(nul(fieldName$), fieldName$, "_VIRTUAL" + str(++virtual))
pvxIOL$    += separator$ + fieldName$
if (mid(fieldType$, 1, 3) = "CHR") or (fieldType$ = "DATE") {
pvxIOL$ += "$"
}
separator$ = ","
until (++counter > items)
pvxIOL$ = sub(pvxIOL$, "-", "_")
dim sqlResultItem$ : cpl(pvxIOL$)
return


What needs to be done in order to work with then newer version?

With Windows 11 the scrollbar buttons are only visible, if the mouse hovers over them. This makes the buttons of a spinner control invisible. We like to have constantly visible scrollbars and/or buttons as it used to be. MS-Outlook shows it can be done.

Has someone created a Pivot table with an average column so far?
I recorded a macro in MS-Excel

    With ActiveSheet.PivotTables("Pivot1").PivotFields("Percent")
        .Caption = "Average %"
        .Function = xlAverage
    End With


This is my translation to PxPlus

pivotName$ = "Pivot1"
fieldName$ = "Percent"
title$ = "Average %"
piTable = sheet'pivotTables(pivotName$)
piField = piTable'pivotFields(fieldName$)
piTable'addDataField(*piField)
lastItem = piTable'dataPivotField'pivotItems()'count
lastPiField = piTable'dataPivotField'pivotItems(lastItem)
lastPiField'caption$ = title$
piField'function = -4106 ! xlAverage


I always receive an error 88 on the last line.
The macro seems to be wrong, because the caption can only be set on lastPiField instead of piField.
Help is appreceated.

On customer sites we encounter temporary error 11 on ky$=key(channel,kno=0,end=*next). When we try to reproduce the errors on these systems then the errors are gone. A change to "err=*next" is no option, because that would hide a corrupted file.

What is the reason for these errors 11?

In C# there is the possibility to build strings by referencing/including variables.

string name = "Tom";
var date = DateTime.Now;
Console.WriteLine($"Hello, {name}! Today is {date.DayOfWeek}, it's {date:HH:mm} now.");

These strings are much more readable than coding a concatenation of several small strings. E.g. building a SQL-statement becomes more straight forward.
If PxPlus would provide such functionality, we could gain speed, because the interpreter would not need to translate several lines of code.

I'm trying to save the variables of an iolist as a property with a dynamic approach. The purpose is to retrieve extermal parameters only once without the need to adjust anything when new parameters are introduced. So far I had no luck.

Here ist my class
def class "GetIOL" create required
property params$
function whatParams()
print "whatParams()"
print lst(iol(params$))
print sub(params$,sep,"~")
print params.name$
return 1
function end
end def
ON_CREATE:
call "GetIOL.pvc;FETCH_PARAMS", params$
print "ON_CREATE"
print lst(iol(params$))
print sub(params$,sep,"~")
print params.name$
return
FETCH_PARAMS:
enter p$
dim p$:iolist id$,name$,value
p.id$="ABC"
p.name$="XYZ"
p.value=42
exit

No variable arrives inside the object, though the iolist is constantly present.
I also tried static iol=iol(params$) and several other approaches. But everything failed.

This is my test program
begin
g=new("GetIOL")
g'whatParams()
delete object g
end

All suggestions are welcome.

So far we don't use "select record" a lot. I did some tests and found that it is slower than an old style read loop. I'm wondering why. As it knows all the keys I'm interested in including all conditions it should be much faster than any programmed logic.
I tested with a file constisting of 600,000 records and read all records from then second to the last but one using the primary key. All runs with "select record" needed 3 seconds longer than the old style read loop.

In my opinion it should be much faster.

As far as I understand the documentation both file types do the same. So what are the pros and cons? Which one is better when starting to use SQL-tables?

Are there any known issues when running WindX as a Citrix-App?
A customer has 3 out of 300 users where the invocation string of pxplus.exe is truncated by 4 bytes.

Each time we update global functions on a Windows system they don't work any more. PxPlus throws many types of errors depending on how the functions have changed. Here is what we do in order to update/reload the global functions:


bkpPC = prm('PC')
set_param 'PC' = 0
! do update actions
set_param 'PC' = bkpPC
drop pth("globalFunctions.pvx"), err = *next
call "globalFunctions.pvx" ! addr itself


What needs to be done for a successful update?

Each time we update global functions on a Windows system they don't work any more. PxPlus throws many types of errors depending on how the functions have changed. Here is what we do in order to update/reload the global functions:


bkpPC = prm('PC')
set_param 'PC' = 0
! do update actions
set_param 'PC' = bkpPC
drop pth("globalFunctions.pvx"), err = *next
call "globalFunctions.pvx" ! addr itself


What needs to be done for a successful update?

Each time a record with a large text field is written to a file with active journalization, the current journal file becomes corrupted.
As we cannot predict the requested field length, we define such fields with a length of 100 bytes delimited and turn on "extended records". We must always lie about the length in such cases. PxPlus has no problems with file I/O to the data file. So the 100 bytes are a signal for the developer, that there may me much more bytes.
We expect the journal file to be written as properly as the data file. How can we avoid the problem?

I have a listbox containing filenames and want to drag a file from it to another non-PxPlus-application. How can I do that?