Insert values into Database with Java

Question

Hello I have a Jtable and a JFormattedTextField where I input the values to insert into the database. I used PreparedStatement but the output gives an error [SQL0418] Utilização de marcador de parâmetro ou NULL não válida.

try {
            Connection con = DriverManager.getConnection("jdbc:as400://" + host, user, pwd);
            // create new statement from connection
            //Statement stmt = con.createStatement();
            ResultSet rs; 
            // int rows=table.getRowCount();

             //for(int row = 0; row<rows; row++)
            // {   
                String PTCDCT = (String)table.getValueAt(0, 0);
                String OFNUPK = (String) table.getValueAt(0, 1);
                String TFCCMP = (String)table.getValueAt(0, 2);
                String TFCPAI = (String)table.getValueAt(0, 3);
                String TPTEMP = (String)table.getValueAt(0, 4);
                int OFNUP = Integer.parseInt(OFNUPK);
                double TPTEM = Double.parseDouble(TPTEMP);
                
       
            String querq = " INSERT INTO SICGA00F55.FTEMPO(TPRNPT, TPNUOF, TPLV01, TPTEMP, TPPZIV, TPRNOP)"
             + "SELECT t.PTRNPP, f.OFNUPK, concat(p.PTSEC, '00000', a.TFNRLC), ?, convert(datetime, ?, 111), MAX(p.TPRNOP) + 1 "
             + "FROM SICGA00F55.TPOSTO t, SICGA00F55.TPOSTCM p, SICGA00F55.FORFAB f, $$CLI00F55.FOFFAN a, SICGA00F55.FTEMPO p"
             + "WHERE t.PTCDCT= ? AND t.PTCDCT= p.PTCDCT AND f.OFNUPK= ? AND "
             + "f.OFNUPK= a.TFSNOF AND a.TFCCMP= ? AND a.TFCPAI= ?";
                
          
                PreparedStatement preparedStmt = con.prepareStatement(querq);
                preparedStmt.setDouble(1, TPTEM);
                preparedStmt.setString(2, textId.getText());
                preparedStmt.setString(3, PTCDCT);
                preparedStmt.setInt(4, OFNUP);
                preparedStmt.setString(5, TFCCMP);
                preparedStmt.setString(6, TFCPAI);
                preparedStmt.executeUpdate(querq);
               
                //ResultSet rs = stmt.executeQuery(querq);
 
          
        }catch (SQLException ex) {
            System.err.println("Got an exception! "); 
            JOptionPane.showMessageDialog(null, "Algum valor não está correto!", "Error", JOptionPane.ERROR_MESSAGE);
            System.err.println(ex.getMessage()); 
        }  

I dont know what i did wrong. Its my first post if you have any questions about what im asking please tell me.

Answer 1

when you run your code, does anything run on the AS400?

When I troubleshoot these sorts of things I like to start at the basics. Here is an SQL procedure that sends a message to the QSYSOPR message queue:

CREATE or replace PROCEDURE qgpl/testHello(                         
 )                                                                  
LANGUAGE    SQL                                                     
BEGIN                                                               
                                                                    
declare     vCmd char(512) default ' ' ;                            
declare     vCmdLgth decimal(15,5) default 0 ;                      
                                                                    
set         vCmd = 'SNDMSG MSG(''test hello'') TOUSR(*SYSOPR)' ;    
set         vCmdLgth = 80 ;                                         
call    qcmdexc( vCmd, vCmdLgth ) ;                                 
                                                                    
end                                                                 

use the STRSQL command prompt to create the procedure and then run it. Then DSPMSG QSYSOPR to see the message in QSYSOPR message queue.

Next, change your java code to "call qgpl/testHello" instead of the "insert into ..." code. When you run the java code you should see the message in QSYSOPR.