how to parse raw result of sqlci query with TelnetClient in java

Question

how can i parse raw sqlci query result executed with TelnetClient in java?

I was trying something like this:

        String responeExample = 
                "CLI_IDC        CLI_VRT  CLI_IND_PER_EMP  CLI_TIP  CLI_CIC       CLI_COD_EST\n" +
                "-------------  -------  ---------------  -------  ------------  -----------\n" +
                "CLI_APE_PTN                CLI_APE_MTN                CLI_NOM\n" +
                "-------------------------  -------------------------  -------------------------\n" +
                "CLI_IND_SEX  CLI_COD_ACV  CLI_COD_EJE   CLI_COD_ECV  CLI_FEC_NAC\n" +
                "-----------  -----------  ------------  -----------  -----------\n" +
                "CLI_NOM_FTS                               CLI_COD_OFI  CLI_COD_PLN\n" +
                "----------------------------------------  -----------  -----------\n" +
                "CLI_COD_TIP_BCA  CLI_COD_SOC_ECO  CLI_IND_CNV  CLI_RTA\n" +
                "---------------  ---------------  -----------  ---------------------\n" +
                "CLI_COD_STO  CLI_PDO_STO\n" +
                "-----------  -----------\n" +
                "\n" +
                "005752983      0        P                CL           98707514  VIG\n" +
                "NO TOCAR                   TDM                        CELULA\n" +
                "M            95001        RFERNAS       SOL           1990-01-01\n" +
                "                                          090\n" +
                "PP                                N                            .0000\n" +
                "\n";

        String[] headersAndValues = responeExample.split("\n\n");
        String[] headers = headersAndValues[0]
                .replaceAll("-", "")
                .replace("\n", "")
                .replaceAll(" +", " ")
                .trim()
                .split(" ");
        String values = headersAndValues[1];
        System.out.println("HEADERS: ["+headers.length+"]" + Arrays.toString(headers));
        System.out.println("VALUES: " + values);

with this I was able to parse the response headers in String array, but the response values contain spaces and some values are just a blank space like the last line of values, how can I parse this?

Answer 1

I solved it using splits, trims and replaces, the code tested it with several queries to different tables with different values and all of them parsed correctly.

the Mapper Class:

package utils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class SqlciMapper {

    private final String rawResponse;
    private final List<Map<String, String>> resultList;
    private int results;
    private String[] headers;

    public SqlciMapper(String rawResponse) {
        this.rawResponse = rawResponse;
        this.resultList = new ArrayList<>();
        setResultsQuantity();
        map();
    }

    private void setResultsQuantity() {
        Pattern pattern = Pattern.compile("(?<=--- )(.*)(?= row)");
        Matcher matcher = pattern.matcher(this.rawResponse);
        if (matcher.find()) {
            this.results = Integer.parseInt(matcher.group(1));
        }else {
            this.results = -1;
        }
    }

    private String getRawHeaderRows() {
        String rawHeaders = rawResponse
                .replace("\r", "")
                .replace("\n\n--- (.*) row(.*)", "")
                .split("\n\n", 2)[0];

        setHeaders(rawHeaders);
        return rawHeaders;
    }

    private List<String> getRawResultRows() {
        List<String> rawResponses = new ArrayList<>();

        String rawResponsesStr = rawResponse
                .replace("\r", "")
                .replaceAll("\n\n--- (.*) row(.*)", "")
                .split("\n\n", 2)[1];

        int currentRow = 0;

        String[] rawResponsesArr = rawResponsesStr.split("\n");

        for (int i = 0; i < results; i++) {
            StringBuilder stringBuilder = new StringBuilder();
            int divisor = rawResponsesArr.length / results;
            for (int j = 0; j < divisor; j++) {
                stringBuilder.append(rawResponsesArr[j+currentRow]).append("\n");
            }
            rawResponses.add(stringBuilder.toString());
            currentRow+= divisor;
        }

        return rawResponses;
    }

    private void setHeaders(String rawHeaders) {
        this.headers = rawHeaders
                .replaceAll("-", "")
                .replace("\n", "")
                .replaceAll(" +", " ")
                .trim()
                .split(" ");
    }

    private List<String> getHeaderRows() {
        String rawHeaders = getRawHeaderRows();
        List<String> headerRows = new ArrayList<>(headers.length);
        for (String rawHeader : rawHeaders.replaceAll("-", "").split("\n")) {
            if (rawHeader.trim().equals("\n") || rawHeader.trim().isEmpty()) {
                continue;
            }

            headerRows.add(rawHeader);
        }

        return headerRows;
    }

    private void map() {
        List<String> rawResults = getRawResultRows();
        List<String> headerRows = getHeaderRows();

        rawResults.forEach(rawResult -> {
            List<String> valuesRows = Arrays.asList(rawResult.split("\n", headers.length));

            Map<String, String> resultMap = new HashMap<>();

            int currentIndex = 0;

            while (resultMap.size() != headers.length) {
                String currentHeadersRow = headerRows.get(currentIndex);
                String currentValuesRow = valuesRows.get(currentIndex);

                String[] headersSplit = currentHeadersRow.replaceAll(" +", " ").trim().split(" ");

                for (int i = 0; i < headersSplit.length; i++) {
                    if (i+1 == headersSplit.length) {
                        resultMap.put(headersSplit[i], rightTrim(currentValuesRow));
                        break;
                    }

                    String nextTarget = headersSplit[i+1];
                    int nextTargetIndex = currentHeadersRow.indexOf(nextTarget);

                    if (currentValuesRow.length() < nextTargetIndex) {
                        nextTargetIndex = currentValuesRow.length();
                    }

                    String currentTargetValue = currentValuesRow.substring(0, nextTargetIndex);
                    resultMap.put(headersSplit[i], rightTrim(currentTargetValue));
                    currentHeadersRow = currentHeadersRow.substring(nextTargetIndex);
                    currentValuesRow = currentValuesRow.substring(nextTargetIndex);
                }
                currentIndex++;
            }

            addResultRow(resultMap);

        });
    }

    private void addResultRow(Map<String, String> resultMap) {
        resultList.add(resultMap);
    }

    public List<Map<String, String>> get() {
        return resultList;
    }


    private String rightTrim(String s) {
        int i = s.length()-1;
        while (i > 0 && Character.isWhitespace(s.charAt(i))) {
            i--;
        }
        return s.substring(0,i+1);
    }

}

Obviously it is not the cleanest solution, but it works, it has several things to improve but as an initial version it is fine.

Example of use:

public static void main(String[] args) {
        String rawResponse =
                "\n" +
                        "CLI_IDC        CLI_VRT  CLI_IND_PER_EMP  CLI_TIP  CLI_CIC       CLI_COD_EST\n" +
                        "-------------  -------  ---------------  -------  ------------  -----------\n" +
                        "CLI_APE_PTN                CLI_APE_MTN                CLI_NOM\n" +
                        "-------------------------  -------------------------  -------------------------\n" +
                        "CLI_IND_SEX  CLI_COD_ACV  CLI_COD_EJE   CLI_COD_ECV  CLI_FEC_NAC\n" +
                        "-----------  -----------  ------------  -----------  -----------\n" +
                        "CLI_NOM_FTS                               CLI_COD_OFI  CLI_COD_PLN\n" +
                        "----------------------------------------  -----------  -----------\n" +
                        "CLI_COD_TIP_BCA  CLI_COD_SOC_ECO  CLI_IND_CNV  CLI_RTA\n" +
                        "---------------  ---------------  -----------  ---------------------\n" +
                        "CLI_COD_STO  CLI_PDO_STO\n" +
                        "-----------  -----------\n" +
                        "\n" +
                        "005752983      0        P                CL           98707514  VIG\n" +
                        "NO TOCAR                   TDM                        CELULA\n" +
                        "M            95001        RFERNAS       SOL           1990-01-01\n" +
                        "                                          090\n" +
                        "PRE                               N                            .0000\n" +
                        "\n" +
                        "\n" +
                        "--- 1 row";

        List<Map<String, String>> results = new SqlciMapper(rawResponse).get();

        results.forEach(resultMap -> {
            System.out.println("----------");
            resultMap.forEach((k, v) -> System.out.println("["+k+"] ["+v+"]"));
        });
}

Output:

----------
[CLI_IND_SEX] [M]
[CLI_RTA] [                .0000]
[CLI_IND_PER_EMP] [P]
[CLI_COD_ECV] [SOL]
[CLI_NOM_FTS] [ ]
[CLI_IDC] [005752983]
[CLI_IND_CNV] [N]
[CLI_COD_EJE] [RFERNAS]
[CLI_APE_PTN] [NO TOCAR]
[CLI_NOM] [CELULA]
[CLI_APE_MTN] [TDM]
[CLI_COD_SOC_ECO] [ ]
[CLI_COD_EST] [VIG]
[CLI_COD_TIP_BCA] [PRE]
[CLI_PDO_STO] []
[CLI_COD_ACV] [95001]
[CLI_VRT] [0]
[CLI_COD_OFI] [090]
[CLI_COD_PLN] []
[CLI_CIC] [    98707514]
[CLI_FEC_NAC] [ 1990-01-01]
[CLI_TIP] [CL]
[CLI_COD_STO] []