在Google Apps脚本中执行存储过程 [英] execute stored procedure in google apps script

问题描述

我在Google Apps脚本中使用以下代码将员工记录从Google表格插入到mysql数据库中. 存储过程[dbo].[insertemployee]接受"@empname"参数.

I am using the below code in google apps script to insert employee record from a Google Sheet into a mysql database. The stored procedure [dbo].[insertemployee] accepts "@empname" parameter.

  var empname = 'test';
  var mysqldbconn = Jdbc.getConnection(url,username,password);
  var mysqlquery = mysqldbconn.createStatement(); 
  callstoredprocedure = "{call [dbo].[testemployee](?)}";
  mysqlquery = mysqldbconn.prepareCall(callstoredprocedure);
  
  mysqlquery.setString(1, empname);
  mysqlquery.executeUpdate();

这给了我这个错误必须声明标量变量"@empname"".

This gives me this error "Must declare the scalar variable "@empname"".

推荐答案

也许阅读以下内容可能会对您有所帮助: 从JDBC调用MySQL存储过程

Perhaps reading this might help you: Calling MySQL Stored Procedures from JDBC

这是您出问题的地方:

mysqlquery = mysqldbconn.prepareCall(vstrSQLStatement);

您不使用声明的"callstoredprocedure".

You don't use the declared 'callstoredprocedure'.

阅读下面的示例:

public static void getSkills(int candidateId) {
    // 
    String query = "{ call get_candidate_skill(?) }";
    ResultSet rs;

    try (Connection conn = MySQLJDBCUtil.getConnection();
            CallableStatement stmt = conn.prepareCall(query)) {

        stmt.setInt(1, candidateId);

        rs = stmt.executeQuery();
        while (rs.next()) {
            System.out.println(String.format("%s - %s",
                    rs.getString("first_name") + " "
                    + rs.getString("last_name"),
                    rs.getString("skill")));
        }
    } catch (SQLException ex) {
        System.out.println(ex.getMessage());
    }
}

让我解释一下: 该查询已定义，类似于您的呼叫接受1个不返回值的参数.然后在conn.prepareCall()中使用它，然后将参数设置为语句，然后执行该语句.

Let me explain: The query is defined, similar to your call accepting 1 parameter returning no value. Then it is used in the: conn.prepareCall() , and then the parameter is set to the statement and then it is executed.

正如我提到的，您应该这样做:

As I mentioned, you should do this:

mysqlquery = mysqldbconn.prepareCall(callstoredprocedure);

如果这解决了您的问题，请告诉我.

Please let me know, if this fixed your problem.

这篇关于在Google Apps脚本中执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！