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?
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] []