cURL / JSON array outbound from RocketD3

114 views
Skip to first unread message

Peter Lettieri

unread,
Mar 30, 2022, 3:17:38 PMMar 30
to Pick and MultiValue Databases
Looking to see if anyone has seen a subroutine that would put together a JSON array based on the Pick data passed to it.

I then want to send this payload out to a restful endpoint using a cURL script or something similar.

We're running a pick application on Rocket D3 and I'm integrating with our dashboards, and e-commerce platform.

Thanks,

Pete

Brian Speirs

unread,
Mar 30, 2022, 5:11:34 PMMar 30
to Pick and MultiValue Databases
You need to think a little bit about the JSON structure that you want to build - because you can build EQUIVALENT data structures in quite different ways.

At the simplest level, you might create an object containing an array of arrays.However, if you have "mixed" data (numbers, strings), then you might need an object containing an array of objects.

My approach is to define an associated array that contains 3 pieces of information about the data to build into JSON (or XML): there is a data "path", a data type, and the data value. The path and type effectively determine the structure of the JSON object that is built.

Contact me offline and I will send you some of the subroutines that I use.

Cheers,

Brian
brian at rushflat dot co dot nz
brian dot speirs at beeflambnz dot com

KOSDAY SOLUTIONS

unread,
Mar 31, 2022, 4:14:42 AMMar 31
to Pick and MultiValue Databases
In addition to Brian's option...
[AD] I think Linkar can help you in your task. Linkar provides structured json results. and allows you to save to database using json as data source.
As an example let's execute a read of a record from any environment, be it .net, java, php, nodejs, Rest api, ...
    Linkar can return the result in different formats. In Json there are three output possibilities, depending on the desired level of complexity and the quality of your dictionaries:
        1.- JSON output format. This is the basic format. As you will see in the example, attributes that have multivalues are returned as a string.
            {
              "TOTAL_RECORDS": "1",
              "RECORDS": [
                {
                  "LKITEMID": "2",
                  "CUSTOMER": "14",
                  "DATE": "17218",
                  "ITEM": "101885ý101102ý101894",
                  "QTY": "402ý451ý837",
                  "PRICE": "874.18ý50.62ý127.67",
                  "DELIVERYDATE": "ý17269ü17351ý",
                  "QTYPARTIAL": "ý225ü226ý",
                  "ICUSTOMERNAME": "CUSTOMER 14",
                  "IITEMDESCRIPTION": "SCREW 101885ýPLUG 101102ýSCREW 101894",
                  "IITEMSTOCK": "232ý41ý847",
                  "ITOTALLINE": "351420.36ý22829.62ý106859.79",
                  "ITOTALORDER": "481109.77"
                }
              ],
              "ERRORS": []
            }
        2.- JSON_DICT output format. In this format arrays of multivalue attributes are created. You have to use in your dictionaries the field 4 of structure: c;attr#;attr#;... d;attr#
            {
              "TOTAL_RECORDS": "1",
              "RECORDS": [
                {
                  "LKITEMID": "2",
                  "CUSTOMER": "14",
                  "DATE": "17218",
                  "LstItems": [
                    {
                      "ITEM": "101885",
                      "QTY": "402",
                      "PRICE": "874.18",
                      "DELIVERYDATE": "",
                      "QTYPARTIAL": "",
                      "IITEMDESCRIPTION": "SCREW 101885",
                      "IITEMSTOCK": "232",
                      "ITOTALLINE": "351420.36"
                    },
                    {
                      "ITEM": "101102",
                      "QTY": "451",
                      "PRICE": "50.62",
                      "DELIVERYDATE": "17269ü17351",
                      "QTYPARTIAL": "225ü226",
                      "IITEMDESCRIPTION": "PLUG 101102",
                      "IITEMSTOCK": "41",
                      "ITOTALLINE": "22829.62"
                    },
                    {
                      "ITEM": "101894",
                      "QTY": "837",
                      "PRICE": "127.67",
                      "DELIVERYDATE": "",
                      "QTYPARTIAL": "",
                      "IITEMDESCRIPTION": "SCREW 101894",
                      "IITEMSTOCK": "847",
                      "ITOTALLINE": "106859.79"
                    }
                  ],
                  "ICUSTOMERNAME": "CUSTOMER 14",
                  "ITOTALORDER": "481109.77"
                }
              ],
              "ERRORS": []
            }
        3.- JSON_SCH output format. This format allows you to exploit also the subvalues. To do so, you must define your schema in Linkar Schemas.
            {
              "TOTAL_RECORDS": "1",
              "RECORDS": [
                {
                  "LKITEMID": "2",
                  "CUSTOMER": "14",
                  "DATE": "17218",
                  "MvItems": [
                    {
                      "ITEM": "101885",
                      "QTY": "402",
                      "PRICE": "874.18",
                      "SvDeliveryDates": [
                        {
                          "DELIVERYDATE": "",
                          "QTYPARTIAL": ""
                        }
                      ],
                      "IITEMDESCRIPTION": "SCREW 101885",
                      "IITEMSTOCK": "232",
                      "ITOTALLINE": "351420.36"
                    },
                    {
                      "ITEM": "101102",
                      "QTY": "451",
                      "PRICE": "50.62",
                      "SvDeliveryDates": [
                        {
                          "DELIVERYDATE": "17269",
                          "QTYPARTIAL": "225"
                        },
                        {
                          "DELIVERYDATE": "17351",
                          "QTYPARTIAL": "226"
                        }
                      ],
                      "IITEMDESCRIPTION": "PLUG 101102",
                      "IITEMSTOCK": "41",
                      "ITOTALLINE": "22829.62"
                    },
                    {
                      "ITEM": "101894",
                      "QTY": "837",
                      "PRICE": "127.67",
                      "SvDeliveryDates": [
                        {
                          "DELIVERYDATE": "",
                          "QTYPARTIAL": ""
                        }
                      ],
                      "IITEMDESCRIPTION": "SCREW 101894",
                      "IITEMSTOCK": "847",
                      "ITOTALLINE": "106859.79"
                    }
                  ],
                  "ICUSTOMERNAME": "CUSTOMER 14",
                  "ITOTALORDER": "481109.77"
                }
              ],
              "ERRORS": []
            }
    These tests I have done with a utility called Linkar Tester, once Linkar is installed and configured you can test all the functions.
    And if you download Linkar Trainer, in addition to making these same tests, you can see the source code in different environments used to execute the operation,
    for example the read used as an example is programmed with the following code
            1.- with .NET
                using Linkar;
                using Linkar.Functions.Direct.JSON;

                class Test {
                   
                    public string MyRead()
                    {
                        string result = "";
                        try{
                            CredentialOptions credentials = new CredentialOptions("192.168.100.100", "MyEntryPoint", 11300, "myuser", "mypass");
                           
                            result = Functions.Read(credentials, "LK.ORDERS","{  \"RECORDS\": [    {      \"LKITEMID\": \"2\"    }  ]}", "",null, JSON_FORMAT.JSON_DICT);            
                        }
                        catch (Exception ex)
                        {
                            string error = ex.Message;
                            // Do something
                        }
                        return result;
                    }
                }
            2.- with java
                import linkar.*;
                import linkar.functions.*;
                import linkar.functions.direct.json.*;

                public class Test {
                   
                    public String MyRead()
                    {
                        String result = "";
                        try{
                            CredentialOptions credentials = new CredentialOptions("192.168.100.100", "MyEntryPoint", 11300, "myuser", "mypass");
                           
                            result = Functions.Read(credentials, "LK.ORDERS","{  \"RECORDS\": [    {      \"LKITEMID\": \"2\"    }  ]}", "",null, JSON_FORMAT.JSON_DICT);            
                        }
                        catch (Exception ex)
                        {
                            String error = ex.getMessage();
                            // Do something
                        }
                        return result;
                    }
                }
            3.- with php
                include_once 'Linkar/Linkar.php';
                include_once 'Linkar.Functions.Persistent.JSON/Functions.php';

                public function MyRead()
                {
                    $credentials = new CredentialOptions("192.168.100.100", "QMWINQ", 11300, "admin", "1234");
                   
                    $result = DirectCommands::Read($credentials, "LK.ORDERS","{  \"RECORDS\": [    {      \"LKITEMID\": \"2\"    }  ]}", "",null, JSON_FORMAT.JSON_DICT);
                    return $result;
                }            
            4.- also with VB, Node JS, Python...
   
    And finally, we can use Linkar REST API to make REST calls. The call using curl would be:
            curl -X 'POST' \
              'http://192.168.100.100:11201/api/Read' \
              -H 'accept: */*' \
              -H 'Content-Type: application/json' \
              -d '{
              "APIKEY": "myapikey",
              "DATA": {
                "FILE_NAME": "LK.ORDERS",
                "RECORDS": [
                  {
                    "LKITEMID": "2"
                  }
                ],
                "DICT_CLAUSE": "",
                "CALCULATED": "False",
                "CONVERSION": "False",
                "FORMAT_SPEC": "False",
                "ORIGINAL_RECORDS": "False",
                "CUSTOM_VARS": "",
                "OUTPUT_FORMAT": "JSON"
              },
              "LANGUAGE": "",
              "FREETEXT": ""
            }'
    You can test this from the Swagger that you have in Linkar Manager. And we will soon be launching Linkar Web Services, a professional API that implements OpenID and OAuth 2.0 standards, providing a comprehensive solution for user authentication and authorization in the API.
    
As you can see there are multiple connection options to your D3 machine. And the price is not prohibitive.
   
Anything you know...
Angel

George Gallen

unread,
Mar 31, 2022, 8:26:07 AMMar 31
to mvd...@googlegroups.com
an alternative method but uses external applications.  Create a php program (from PICK) that loads a StdClass Object configured
how you need your JSON object to look, then run json_encode() against it and echo the JSON results.

open "/tmp" in PICK
create a dynamic array of the php code and write to the /tmp file
execute/perform a 'php /tmp/phpcode.php' capturing JSON Code
delete /tmp php file

you could potentially include the cURL coding also for sending the JSON to the API

guess it could be php, python, perl - as long as it has an intrinsic Object to JSON function

if you only need a 1 or 2x use

George


From: mvd...@googlegroups.com <mvd...@googlegroups.com> on behalf of Peter Lettieri <pe...@glassdef.com>
Sent: Tuesday, March 29, 2022 1:50 PM
To: Pick and MultiValue Databases <mvd...@googlegroups.com>
Subject: [mvdbms] cURL / JSON array outbound from RocketD3
 

Doug Averch

unread,
Mar 31, 2022, 4:05:07 PMMar 31
to Pick and MultiValue Databases
I wrote this routine for D3 which I later converted to Universe/Unidata.

* x8UD     $BASICTYPE "U"                                             ; * x8UD
     $OPTIONS INFORMATION                                           ; * x8UV
     SUBROUTINE XLr8PropertySub(PARAM1,PARAM2,PARAM3,PARAM4,RETURN.ID,METHOD)      
* © 2012-2022, U2logic, Inc. All Right Reserved.
*******************************************************************************
* PARAM1 - JSON format or @FM data to be parsed in JSON
* PARAM2 - RECORD.ITEM for JSON_ALL or Data or Max values
* PARAM3 - Table Names
* PARAM4 - getProperty value or css values for setPropertyTable
* PARAM5 - toolTip values for setPropertyTable
* PARAM6 - METHOD
*******************************************************************************
     JSON = "" ; ADD.IT = @FALSE
     MAX.FM = DCOUNT(PARAM1,@FM)
     CONVERT '"' TO '' IN PARAM2                                     ; * No double quotes allowed in JSON data
     FOR X = 1 TO MAX.FM
        BEGIN CASE
           CASE INDEX(PARAM2<X>,@VM,1) OR PARAM4 = "JSONARRAY"
              MAX.VM = DCOUNT(PARAM2<X>,@VM) ; ARRAY = ""                    
              IF MAX.VM = 0 THEN MAX.VM = 1
              FOR Y = 1 TO MAX.VM
                 IF ARRAY THEN ARRAY := \,\
                 ARRAY := \"\:PARAM2<X,Y>:\"\
              NEXT Y
              IF ADD.IT THEN JSON := \,\
              JSON := \"\:PARAM1<X>:\":[\:ARRAY:\]\
              IF X # MAX.VM THEN ADD.IT = @TRUE
           CASE 1
              IF PARAM1<X> THEN  
                 IF ADD.IT THEN JSON := \,\
                 JSON := \"\:PARAM1<X>:\":"\:PARAM2<X>:\"\          
                 ADD.IT = @TRUE
              END
        END CASE
     NEXT X
     RETURN.ID = "{":JSON:"}"    
     RETURN
END

Kevin King

unread,
Apr 2, 2022, 11:32:29 AMApr 2
to mvd...@googlegroups.com
Building JSON in BASIC is remarkably easy but like others have said, simply converting the Pick record into JSON may not be an optimal solution.  It may be better to group associated multivalued elements and output them as an array of embedded objects.  That said, writing BASIC to do that is trivial.

--
You received this message because you are subscribed to
the "Pick and MultiValue Databases" group.
To post, email to: mvd...@googlegroups.com
To unsubscribe, email to: mvdbms+un...@googlegroups.com
For more options, visit http://groups.google.com/group/mvdbms
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/bbd18ec6-8c94-404c-9ac1-027cfc6964b7n%40googlegroups.com.

Bruce Decker

unread,
Apr 3, 2022, 1:44:53 PMApr 3
to Pick and MultiValue Databases
While I agree with all that hacking together strings to make JSON is pretty easy in any dialect of Pick BASIC, there are some dialects, such as JBASE and OpenQM BASIC that allow structures like this to be created and manipulated in an oop way that is much more like other modern language and therefore more familiar to some younger programmers that may expect this sort of functionality  in their language.

Here's a crude example, not using jBASE's classes or fancy iterators to make it more relatable to traditional PICK BASIC programmers, in jBASE's Pick BASIC with object oriented extensions (jabba) turned on:

#option jabba
EQU pretty TO 1
* declare an object called "invoice"
invoice = new object
*assigned some properties in the invoice object
invoice->customer_id = "12345"
invoice->customer_name = "ABC Company"
* let's build 3 parts into this invoice object
* first, declare an array called "lines"
lines = new array
* now use a normal BASIC for loop from 1 to 3 (for 3 parts)
FOR i = 1 TO 3
   * Let's make a new object each time we loop for storing one part line
   line = new object
   * Let's set some properties for this line object
   line->lineNo = i               ; * just toss the loop variable into a property
   line->partNo = "part ":i       ; * concat the string 'part ' with the loop variable as a phony part #
   line->qty    = i*10            ; * let's just say that the qty is 10x the loop variable.. just because
   * now, use the inbuilt $append method to append the 'line' object we just built adding it to the
   * lines array (the array store our repeating lines
   lines->$append(line)
NEXT i
* Now that we have the repeats built, just pop the repeating array into another property of the invoice object
invoice->lines=lines
* Now let's use the inbuilt $tojson() method and turn the object into a JSON string in 'pretty' format
* and use the good ole BASIC CRT statement to output the string to the terminal
CRT invoice->$tojson(pretty)

The program above would output this:

{
        "customer_id":"12345",
        "customer_name":"ABC Company",
        "lines":[
                {
                        "lineNo":1,
                        "partNo":"part 1",
                        "qty":10
                },
                {
                        "lineNo":2,
                        "partNo":"part 2",
                        "qty":20
                },
                {
                        "lineNo":3,
                        "partNo":"part 3",
                        "qty":30
                }
        ]
}

Reply all
Reply to author
Forward
0 new messages