如何调用存储过程xp_cmdshell，以及如何将得到的返回值附值给变量a。谢谢，up有分。

45 个解决方案

#1

#2

在数据窗口里有一个是专门针对存储过程的。

#3

#4

#5

关注！！！

#6

string ls_retu
DECLARE myproc procedure for xp_cmdshell
( @ls_retu= :retu_var);
EXECUTE myproc ;

#7

#8

string ls_retu
DECLARE myproc PROCEDURE FOR xp_cmdshell @ls_retu= :retu_var out USING SQLCA;
EXECUTE myproc ;
FETCH myproc INTO :ls_retu;

#9

string a
DECLARE myproc PROCEDURE FOR xp_cmdshell @ls_retu= :a out USING SQLCA;
EXECUTE myproc ;
FETCH myproc INTO :a;

ls_retu为存储过程中的返回变量

#10

关注！
UP

#11

那如何通过xp_cmdshell执行dir c:\命令，并将返回值附值给变量a呀？谢谢。

#12

我比较笨，请大家写的详细些，谢谢您。

#13

#14

string ls_cmd
int li_rtn
DECLARE myproc PROCEDURE FOR xp_cmdshell @ls_retu= :ls_cmd output USING SQLCA;
ls_cmd = "dir c:\"
EXECUTE myproc ;
FETCH myproc INTO :li_rtn;

#15

DECLARE PROCEDURE
Example 1

Assume a stored procedure proc1 is defined as:

CREATE PROCEDURE proc1 AS

SELECT emp_name FROM employee

To declare that procedure for processing within PowerBuilder, enter:

DECLARE emp_proc PROCEDURE FOR proc1;

Note that this declaration is a nonexecutable statement, just like a cursor declaration. Where cursors have an OPEN statement, procedures have an EXECUTE statement.

When an EXECUTE statement executes, the procedure is invoked. The EXECUTE refers to the logical procedure name:

EXECUTE emp_proc;

Example 2

To declare a procedure with input and output parameters, enter:

DECLARE sp_duration PROCEDURE FOR pr_date_diff_prd_ken

@var_date_1 = :ad_start,
@var_date_2 = :ad_end,
@rtn_diff_prd = :ls_duration OUTPUT;

Fetch
Example 1

FETCH emp_proc INTO :emp_name_var;

You can use this FETCH statement only to access values produced with a SELECT statement in a database stored procedure. You cannot use the FETCH statement to access computed rows.

Database stored procedures can return multiple result sets. Assume you define a database stored procedure proc2 as follows:

CREATE PROCEDURE proc2 AS

SELECT emp_name FROM employee
SELECT part_name FROM parts

PowerBuilder provides access to both result sets:

// Declare the procedure.

DECLARE emp_proc2 PROCEDURE FOR proc2;

// Declare some variables to hold results.

string emp_name_var
string part_name_var

// Execute the stored procedure.

EXECUTE emp_proc2;

// Fetch the first row from the first result

// set.
FETCH emp_proc2 INTO :emp_name_var;

// Loop through all rows in the first result

// set.
DO WHILE sqlca.sqlcode = 0

// Fetch the next row from the first result set.

FETCH emp_proc2 INTO :emp_name_var;
LOOP

// At this point we have exhausted the first

// result set. After this occurs,
// PowerBuilder notes that there is another
// result set and internally shifts result sets.
// The next FETCH executed will retrieve the
// first row from the second result set.
// Fetch the first row from the second result
// set.
FETCH emp_proc2 INTO :part_name_var;

// Loop through all rows in the second result

// set.
DO WHILE sqlca.sqlcode = 0

// Fetch the next row from the second result

// set.
FETCH emp_proc2 INTO :part_name_var;
LOOP

The result sets that will be returned when a database stored procedure executes cannot be determined at compile time. Therefore, you must code FETCH statements that exactly match the format of a result set returned by the stored procedure when it executes.

Example 2

In the preceding example, if instead of coding the second fetch statement as:

FETCH emp_proc2 INTO :part_name_var;

you coded it as:

FETCH emp_proc2

INTO :part_var1,:part_var2,:part_var3;

the statement would compile without errors. But an execution error would occur: the number of columns in the FETCH statement does not match the number of columns in the current result set. The second result set returns values from only one column.

#16

ldy(罗大佑)兄，我试了您给的方法，li_rtn返回值是0呀，而不是我想要的c盘目录呀。谢谢您。

#17

ls_cmd = "dir c:\ > c:\dir.txt"

#18

为何不用RUN()

#19

该怎么用run呀，谢谢。

#20

PB中的函数，看看帮助
Description

Runs the specified application program.

Syntax

Run ( string {, windowstate } )

#21

罗兄："dir c:\ > c:\dir.txt"是什么意思呀。谢谢。

#22

将结果存为文件
这是DOS命令重定向（管道）

#23

c:\dir.txt 的内容可能象这样
驱动器 C 中的卷没有标签。
卷的序列号是 2070-C397

C:\ 的目录

2002-12-14  14:42       <DIR>          ePOAgent
2002-12-14  19:30       <DIR>          gx
2002-11-14  16:25       <DIR>          Inetpub
2002-12-02  11:09       <DIR>          kydoc
2002-11-27  09:50       <DIR>          My Documents
2002-11-29  18:39       <DIR>          temp
2002-12-15  19:42       <DIR>          WINNT
2002-11-22  14:09       <DIR>          WUTemp
               1 个文件              0 字节
               8 个目录  5,345,914,880 可用字节

#24

还是不行呀，我把我的代码贴出来，您帮忙看看吧，谢谢。
SQLCA.AutoCommit = True
string ls_cmd,ls_fanhui
DECLARE myproc PROCEDURE FOR xp_cmdshell
@ls_retu= :ls_cmd output USING SQLCA;
ls_cmd = "dir c:\ > c:\dir.txt"
EXECUTE myproc ;
FETCH myproc INTO :ls_fanhui;
CLOSE myproc;

messagebox("",ls_fanhui)
运行后ls_fanhui是空的。

#25

#26

#27

默认情况下，只有 sysadmin 固定服务器角色的成员才能执行此扩展存储过程。
这个扩展存储过程是在master里
DECLARE myproc PROCEDURE FOR master..xp_cmdshell @ls_retu= :ls_cmd USING SQLCA;

#28

Note When executing xp_cmdshell with the Microsoft® Windows® 95/98 operating system, the return code from xp_cmdshell will not be set to the process exit code of the invoked executable. The return code will always be 0.

#29

http://expert.csdn.net/Expert/topic/1243/1243353.xml?temp=.9053308

#30

看看PB的帮助，很详细的

#31

string ls_cmd = "dir c:\ > c:\dir.txt"
//ls_cmd = "sgdgrg"
int li_rtn
DECLARE myproc PROCEDURE FOR @rtn = master..xp_cmdshell
@cmd= :ls_cmd;
EXECUTE myproc;
fetch myproc into :li_rtn;
MessageBox("返回值",li_rtn)
测试（PB8，SQL 2000）
成功返回NULL

#32

存储过程不能执行，返回错误信息是 cursor is not open

#33

execute immediate "execute master.dbo.xp_cmdshell 'dir c:\>c:\dir.txt'";
if sqlca.sqlcode<>0 then
messagebox("immediate",sqlca.sqlerrtext)
return
end if

#34

#35

#36

#37

#38

up & gz

#39

#40

罗兄，您的意思是，如果成功只能返回null吗？而不是c盘的目录？谢谢。

#41

输出的东西已经到c:\dir.txt中了

#42

需要自己建个dir.txt文件吗？

#43

不用，自动建立

#44

谢谢，罗兄。您看到我给您留的短消息了吗。

#45

看到我回你短消息了吗?
哈哈

#1

#2