0
Good morning,
I’m new to JAVA and I’m having trouble maintaining a legacy system... I use the Eclipse IDE and in it the code is OK, without criticism. I’m reading a form on a web system, picking up some fields and entering it into the database. This insertion is in a table where the system has never written (it already writes in other tables in the database). In the same class there are other insertions in the bank (working correctly), so I made my code snippet exactly as the others. This is an example that works:
// Insere itens do pedido
StringBuilder insertItensPedido = new StringBuilder();
insertItensPedido.append(" INSERT INTO INT_SUPD ( ");
insertItensPedido.append(" EMP_FIL, ESTAB, DEPOSITO, NR_PEDIDO, LINHA_PED, SEQ_REGISTRO, TIPO_MANUT, REG_EXP_IMP, ORIGEM_REG, PROC_ORIGEM, ");
insertItensPedido.append(" C_PROD, FORNEC, NR_COTACAO, NR_REQUIS, LINHA_REQ, OS, STA_REG, PRIORIDADE, DT_PEDIDO, DT_ENTREG, ");
insertItensPedido.append(" DT_ENT_PREV, DT_ENT_ORIG, DT_ULT_ENT, PORC_ICM, VEZES_ENT, LISTA, QT_ENTREG, QTDE, UNIDADE, BASE_UNIT, ");
insertItensPedido.append(" VR_UNIT, PRECO_TOT, PERC_DESC, PERC_IPI, VLR_DESC, VLR_IPI, VR_ENTR, SEQ_ALT, COD_TRIB, TRAT_FISC, ");
insertItensPedido.append(" COD_GEN, CONTA, DG_CONTA, C_CUSTO, DG_CCUSTO, CONTA2, DG_CONTA2, C_CUSTO2, DG_CCUSTO2, GERA_FLUXO, ");
insertItensPedido.append(" LIVRE, BATCH_PROG, BATCH_DATA, BATCH_HORA, ESTAGIO, DT_INIC_EXEC, PERIODO, PROJETO_OBRA, PROJETO_ETAPA, APROVADOR_PROX, ");
insertItensPedido.append(" APROV_AVISADO, DT_APROVACAO, DT_VALIDADE, SOLICITANTE, UNID_FORN, FATOR_UN_FORN, CUSTO_QT_SOLIC, CUSTO_QT_APROV, COND_PAGTO, TIPO_DESPESA, ");
insertItensPedido.append(" LIVRE_1, LIVRE_2, LIVRE_3, LIVRE_4, LIVRE_5, CAMPO_USUA1, CAMPO_USUA2, CAMPO_USUA3, CAMPO_USUA4, CAMPO_USUA5, ");
insertItensPedido.append(" STATUS_REG, MSG_ERRO, OPERADOR, DATA_ALTER, HORA_ALTER, TIME_STAMP");
insertItensPedido.append(" ) VALUES ( ");
insertItensPedido.append(" '31940001', '1', '1', ?, ?, '1', 'INC', 'IMP', 'LECOM', 'REQ COMPRAS', ");
insertItensPedido.append(" ?, ?, '0', ?, '0', '0', 'L', '0', ?, ?, ");
insertItensPedido.append(" ?, NULL, NULL, '0.0000', '0', '0', '0.000000', ?, ?, 'U', ");
insertItensPedido.append(" ?, ?, '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '0', '0', '0', ");
insertItensPedido.append(" '0', ?, ?, ?, ?, ' ', '0', ' ', '0', '0', ");
insertItensPedido.append(" '0', ' ', NULL, '0', 'AV', ?, '110', ' ', ' ', ' ', ");
insertItensPedido.append(" ' ', NULL, NULL, ?, ' ', '0.000000', '0.000000', '0.000000', ?, '0', ");
insertItensPedido.append(" '0', '0', '0.000000', ' ', NULL, ' ', ' ', ' ', ' ', ' ', ");
insertItensPedido.append(" 'N', ' ', ?, ?, '0', '1' ");
insertItensPedido.append(" ) ");
int seqLinhas = 1;
List<Map<String, Object>> dadosGridItensRC = integracaoVO.getDadosModeloGrid("ITENS_RC");
for (Map<String, Object> dadosLinha : dadosGridItensRC) {
// String item = Funcoes.nulo( dadosLinha.get("ITEM").toString(), "");
// String qtd = Funcoes.nulo( dadosLinha.get("QTD").toString(), "");
BigDecimal ItemQtd = new BigDecimal(0);
BigDecimal itemVlrUnit = new BigDecimal(0);
BigDecimal itemVlrTot = new BigDecimal(0);
String auxItemQtd = Funcoes.nulo( dadosLinha.get("ITEM_QTDE").toString(), "").trim();
String auxItemVlrUnit = Funcoes.nulo( dadosLinha.get("VALOR_UNIT").toString(), "").trim();
String auxItemVlrTot = Funcoes.nulo( dadosLinha.get("VALOR_TOT").toString(), "").trim();
if( !"".equals(auxItemQtd) ) {
ItemQtd = new BigDecimal(auxItemQtd);
}
if( !"".equals(auxItemVlrUnit) ) {
auxItemVlrUnit=auxItemVlrUnit.replace(".", "");
auxItemVlrUnit=auxItemVlrUnit.replace(".", "");
auxItemVlrUnit=auxItemVlrUnit.replace(",", ".");
itemVlrUnit = new BigDecimal(auxItemVlrUnit);
}
if( !"".equals(auxItemVlrTot) ) {
auxItemVlrTot=auxItemVlrTot.replace(".", "");
auxItemVlrTot=auxItemVlrTot.replace(".", "");
auxItemVlrTot=auxItemVlrTot.replace(",", ".");
itemVlrTot = new BigDecimal(auxItemVlrTot);
}
try( PreparedStatement pstAux = cnOmega.prepareStatement(insertItensPedido.toString()) ){
logger.info("INSERT INTO INT_SUPD: codPedidoRC=" + codPedidoRC + ", seqLinhas=" + seqLinhas + ", ITEM_COD=" + dadosLinha.get("ITEM_COD").toString().trim() + ", fornCod=" + fornCod + ", dataInsertRC=" + dataInsertRC.format(formatador) + ", ItemQtd=" + ItemQtd.setScale(6).toString() + ", ITEM_UM=" + dadosLinha.get("ITEM_UM").toString().trim() + ", itemVlrUnit=" + itemVlrUnit.setScale(6).toString() + ", itemVlrTot=" + itemVlrTot.setScale(4).toString());
int param = 1;
pstAux.setString(param++, codPedidoRC); // NR_PEDIDO
pstAux.setInt(param++, seqLinhas); // LINHA_PED
pstAux.setString(param++, dadosLinha.get("ITEM_COD").toString().trim()); // C_PROD
pstAux.setString(param++, fornCod); // FORNEC
pstAux.setString(param++, integracaoVO.getCodProcesso()); // NR_REQUIS
pstAux.setString(param++, dataInsertRC.format(formatador)); // DT_PEDIDO
pstAux.setString(param++, prazoEnt); // DT_ENTREG
pstAux.setString(param++, prazoEnt); // DT_ENT_PREV
pstAux.setString(param++, ItemQtd.setScale(6).toString()); // QTDE
pstAux.setString(param++, dadosLinha.get("ITEM_UM").toString().trim()); // UNIDADE
pstAux.setString(param++, itemVlrUnit.setScale(6).toString()); // VR_UNIT
pstAux.setString(param++, itemVlrTot.setScale(4).toString()); // PRECO_TOT
pstAux.setString(param++, conta); // CONTA
pstAux.setString(param++, digitoConta); // DG_CONTA
pstAux.setString(param++, centroCusto); // C_CUSTO
pstAux.setString(param++, digitoCentroCusto); // DG_CCUSTO
pstAux.setString(param++, dataInsertRC.format(formatador)); // DT_INIC_EXEC
pstAux.setString(param++, solicitante); // SOLICITANTE
pstAux.setString(param++, codnPagto); // COND_PAGTO
pstAux.setString(param++, operador); // OPERADOR
pstAux.setString(param++, dataInsertRC.format(formatador)); // DATA_ALTER
int valor = pstAux.executeUpdate();
logger.info("executeUpdate: " + valor);
}
seqLinhas++;
}
Here is my code snippet (where the error occurs, always in the first pstAux line)
// Insere Descrição Alternativa
int seqLinhas1 = 1;
StringBuilder insertDescAlter = new StringBuilder();
for (Map<String, Object> dadosLinha : dadosGridItensRC) {
String auxItemDescAlt = Funcoes.nulo( dadosLinha.get("ITEM_DESC_ALT").toString(), "").trim();
String codItem = Funcoes.nulo( dadosLinha.get("ITEM_COD").toString(), "").trim();
if(!"".equals(auxItemDescAlt)) {
insertItensPedido.append(" INSERT INTO LITE ( ");
insertItensPedido.append(" EMP_FIL, DATA_SET, ESTAB, DEPOS, C_PROD, C_PROD_COMPON, DESCRICAO, ETAPA, DT_LITE, LINHA_PED, ");
insertItensPedido.append(" ENTREGA, NUMERO, ORDEM, SEQ, SUB_SEQ, TIPO, ESTAB_C, DEPOS_C, OPERADOR, DATA_ALTER, ");
insertItensPedido.append(" HORA_ALTER, QTDE, TIME_STAMP ");
insertItensPedido.append(" ) VALUES ( ");
insertItensPedido.append(" ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ");
insertItensPedido.append(" ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ");
insertItensPedido.append(" ?, ?, ? ");
insertItensPedido.append(" ) ");
try( PreparedStatement pstAux = cnOmega.prepareStatement(insertDescAlter.toString()) ){
logger.info("INSERT INTO LITE: codPedidoRC=" + codPedidoRC + ", seqLinhas1=" + seqLinhas1 + ", codItem=" + codItem + ", auxItemDescAlt=" + "Descrição Complementar" + ", OPERADOR=" + operador + ", dataInsertRC=" + dataInsertRC.format(formatador));
int param = 1;
pstAux.setString(param, "31940001"); //EMP_FIL
pstAux.setString(param++, "SUPD"); //DATA_SET
pstAux.setInt(param++, 1); //ESTAB
pstAux.setString(param++, "1"); //DEPOS
pstAux.setString(param++, dadosLinha.get("ITEM_COD").toString().trim()); //C_PROD
pstAux.setString(param++, " "); //C_PROD_COMPON
pstAux.setString(param++, dadosLinha.get("ITEM_DESC_ALT").toString().trim()); //DESCRICAO
pstAux.setString(param++, " "); //ETAPA
pstAux.setString(param++, "0"); //DT_LITE
pstAux.setInt(param++, seqLinhas1); //LINHA_PED
pstAux.setString(param++, "0"); //ENTREGA
pstAux.setString(param++, codPedidoRC); //NUMERO
pstAux.setString(param++, "1"); //ORDEM
pstAux.setString(param++, "0"); //SEQ
pstAux.setString(param++, "0"); //SUB_SEQ
pstAux.setString(param++, "0"); //TIPO
pstAux.setString(param++, "0"); //ESTAB_C
pstAux.setString(param++, "0"); //DEPOS_C
pstAux.setString(param++, operador); //OPERADOR
pstAux.setString(param++, "dataInsertRC.format(formatador)"); //DATA_ALTER
pstAux.setString(param++, "0"); //HORA_ALTER
pstAux.setString(param++, null ); //QTDE
pstAux.setString(param++, "1"); //TIME_STAMP
System.out.println(pstAux);
int valor = pstAux.executeUpdate();
logger.info("executeUpdate: " + valor);
}
}
seqLinhas1++;
}
I changed recent to pass the fixed parameters in pstAux. as an attempt, before I was passing the fixed in insertItensPedido.append, but I did not succeed in any of the versions. I also switched the param variable to fixed numbers 1, 2, 3... tbm didn’t work. Note that the log indicates that you recorded the first excerpt (Legacy) successfully and gave error in mine, but took the form fields and mounted the Insert... I have tested the direct Insert in SQL and it works, the database did not criticize any of the data.
[default - ] - 2020-08-20 15:14:42,349 INFO [RCInserePedidoV1] INSERT INTO INT_SUPD: codPedidoRC=300136, seqLinhas=2, ITEM_COD=60020237, fornCod=55707140008, dataInsertRC=2020-08-20 03:14:42, ItemQtd=10.000000, ITEM_UM=UN, itemVlrUnit=50.000000, itemVlrTot=500.0000
[default - ] - 2020-08-20 15:14:42,401 INFO [RCInserePedidoV1] executeUpdate: 1
[default - ] - 2020-08-20 15:14:42,452 INFO [RCInserePedidoV1] INSERT INTO LITE: codPedidoRC=300136, seqLinhas1=1, codItem=99020131, auxItemDescAlt=Descrição Complementar, OPERADOR=adm, dataInsertRC=2020-08-20 03:14:42
[default - ] - 2020-08-20 15:14:42,553 ERROR [RCInserePedidoV1] ERROR :
com.microsoft.sqlserver.jdbc.SQLServerException: The index 0 is out of range.
I couldn’t think of any more alternatives to solve, so I ran here, if you could point me or suggest a direction, I’d be grateful.
The problem I’m seeing is that you’re not incrementing
paramon the first call. Since you are using post-increment, the variable is only incremented after being passed to the method, which means that in both the first and second call of the methodsetString, you are passing the same value.– Andre
Even incrementing the error occurs on the same line.
int param = 1; pstAux.setString(param++, "31940001"); //EMP_FIL pstAux.setString(param++, "SUPD"); //DATA_SET– ebotarelli
@ebotarelli does not need to put this whole variable "param" and nor increment it just you put the Indice of the Insert which is 1,2,3,4 and so on what you are identifying is the Indice of the Insert that is fixed as I said have to put the numbers according to the Qtde of interrogation or columns 1,2,3 etc
– Jacob de Oliveira
@Jacobdeoliveira Thank you, but it did not roll.. I made this attempt the error is the same `The index 1 is out of range'
– ebotarelli
@ebotarelli You know what the problem is you’re getting the wrong variable right is the "insertItensPedido" and not the insertDescAlter so he’s giving this error he doesn’t find this entry because the string is empty on this line here : Try( Preparedstatement pstAux = cnOmega.prepareStatement(insertDescAlter.toString()) ){
– Jacob de Oliveira
@Jacobdeoliveira well noted, that’s right. Solved this part, now I don’t know why you’re accusing
The value is not set for the parameter number 24I’m only passing 23 values.– ebotarelli
tries to place this instance here inside the for Stringbuilder insertItensPedido= new Stringbuilder()
– Jacob de Oliveira
It worked @Jacobdeoliveira, what I had to do was clear the variable
insertPedido. Apparently the same came with the data of the previous Insert of this class, so he understood that there were more records than the 23 that were on my list.– ebotarelli
Blz, congratulations are in order ;-)
– Jacob de Oliveira