如何使用.net为DB2定义sql语句的终止字符

发布于 2024-11-17 00:08:58 字数 1598 浏览 0 评论 0原文

我正在尝试执行以下命令，

 public void ExecuteNonQuery(string script) {
        try {
            int returnCode;

            var builder = new DB2ConnectionStringBuilder {
                                                             UserID = Context.Parameters["USERID"],
                                                             Password =Context.Parameters["PASSWORD"],
                                                             Database =  Context.Parameters["DATABASE"],
                CurrentSchema =  Context.Parameters["CURRENTSCHEMA"]
            };

            using (var connection = new DB2Connection(builder.ConnectionString)) {

                using (var command = new DB2Command(script, connection)
                    ) {
                    command.CommandType = CommandType.Text;

                    connection.Open();

                    returnCode = command.ExecuteNonQuery();
                    File.WriteAllText(Context.Parameters["LOGFILE"], "Return Code -1 Successful : " + returnCode);
                }
            }
        }
        catch (Exception ex) {
            Trace.WriteLine(ex.StackTrace);
            throw ex;
        }
    }

我正在调用一个脚本，该脚本具有多个以 ; 结尾的语句，并且在文件末尾包含一个 @ 符号。在 db2 命令行上，我可以使用 db2 -td@ -f 。我想知道如何将 @ 符号定义为语句终止符，以便我可以从 csharp 执行脚本。这是示例 sql 文件：

DROP PROCEDURE fred@

CREATE PROCEDURE fred ( IN name, IN id )
specific fred
language sql
b1: begin
      update thetable
      set  thename = name
      where table_id = id;
end: b1
@

grant execute on procedure inst.fred to user dbuser@

原文

I am tring to execute the following

 public void ExecuteNonQuery(string script) {
        try {
            int returnCode;

            var builder = new DB2ConnectionStringBuilder {
                                                             UserID = Context.Parameters["USERID"],
                                                             Password =Context.Parameters["PASSWORD"],
                                                             Database =  Context.Parameters["DATABASE"],
                CurrentSchema =  Context.Parameters["CURRENTSCHEMA"]
            };

            using (var connection = new DB2Connection(builder.ConnectionString)) {

                using (var command = new DB2Command(script, connection)
                    ) {
                    command.CommandType = CommandType.Text;

                    connection.Open();

                    returnCode = command.ExecuteNonQuery();
                    File.WriteAllText(Context.Parameters["LOGFILE"], "Return Code -1 Successful : " + returnCode);
                }
            }
        }
        catch (Exception ex) {
            Trace.WriteLine(ex.StackTrace);
            throw ex;
        }
    }

I am calling a script that has multiple statements ending in ;'s and at the end of the file it contains an @ symbol. On a db2 command line I could use the db2 -td@ -f . I would like to know how to define the @ symbol as the statement terminator so I could execute the script from csharp.
Here is example sql file :

DROP PROCEDURE fred@

CREATE PROCEDURE fred ( IN name, IN id )
specific fred
language sql
b1: begin
      update thetable
      set  thename = name
      where table_id = id;
end: b1
@

grant execute on procedure inst.fred to user dbuser@

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

清风无影 2024-11-24 00:08:58

我不确定我们如何定义分隔符。但是，您可以尝试将整个文本拆分为 C# 中的单独语句，然后一个接一个地执行语句。这对你有用吗？

回复收藏 0 原文

因为看清所以看轻 2024-11-24 00:08:58

这是一个老问题，但我遇到了类似的问题，这是解决方案：

--#SET TERMINATOR @
create or replace procedure test
begin
  declare line varchar(100);
  set line = 'hello db2 world!';
  call dmbs_output.put_line(line);
end@

然后就是：

$ db2 -f script.sql

它会正常工作。

That's an old question, but I had similar problem and here is the solution:

--#SET TERMINATOR @
create or replace procedure test
begin
  declare line varchar(100);
  set line = 'hello db2 world!';
  call dmbs_output.put_line(line);
end@

and then just: