使用Java向数据库插入值

huangapple go评论83阅读模式
英文:

Insert values into Database with Java

问题

try {
    Connection con = DriverManager.getConnection("jdbc:as400://" + host, user, pwd);
    
    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 query = "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(query);
    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();
    
} 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());
}

Please note that I've made the necessary corrections to the code for proper translation and readability. If you have any further questions, feel free to ask.

英文:

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.

答案1

得分: 0

当您运行您的代码时,AS400 上是否有任何内容运行?

在我排除这类问题时,我喜欢从基础开始。以下是一个发送消息到 QSYSOPR 消息队列的 SQL 存储过程:

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                                                                 

使用 STRSQL 命令提示符创建该存储过程,然后运行它。然后使用 DSPMSG QSYSOPR 命令查看 QSYSOPR 消息队列中的消息。

接下来,将您的 Java 代码更改为 "call qgpl/testHello",而不是 "insert into ..." 的代码。当您运行 Java 代码时,您应该在 QSYSOPR 中看到该消息。

英文:

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.

huangapple
  • 本文由 发表于 2020年10月27日 21:49:37
  • 转载请务必保留本文链接:https://go.coder-hub.com/64555908.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定