为什么我的存储过程接收空参数？

Ok, this is a curly one. I'm working on some Delphi code that I didn't write, and I'm encountering a very strange problem. One of my stored procedures' parameters is coming through as null, even though it's definitely being sent 1.

好吧,这是一个卷曲的。我正在研究一些我没写过的Delphi代码,而且我遇到了一个非常奇怪的问题。我的一个存储过程的参数是null,即使它肯定被发送1。

The Delphi code uses a TADOQuery to execute the stored procedure (anonymized):

Delphi代码使用TADOQuery来执行存储过程(匿名):

 ADOQuery1.SQL.Text := "exec MyStoredProcedure :Foo,:Bar,:Baz,:Qux,:Smang,:Jimmy";
 ADOQuery1.Parameters.ParamByName("Foo").Value := Integer(someFunction()); 
 // other parameters all set similarly
 ADOQuery1.ExecSQL;

Integer(SomeFunction()) currently always returns 1 - I checked with the debugger.

Integer(SomeFunction())当前总是返回1 - 我用调试器检查过。

However, in my stored proc ( altered for debug purposes ):

但是,在我的存储过程中(为了调试目的而改变):

create procedure MyStoredProcedure (
    @Foo int, @Bar int, @Baz int,
    @Qux int, @Smang int, @Jimmy varchar(20) 
) as begin
    -- temp debug
    if ( @Foo is null ) begin
        insert into TempLog values ( "oh crap" )
    end
    -- do the rest of the stuff here..
end

TempLog does indeed end up with "oh crap" in it (side question: there must be a better way of debugging stored procs: what is it?).

TempLog确实最终得到了“哦垃圾”(旁边的问题:必须有更好的方法来调试存储过程:它是什么?)。

Here's an example trace from profiler:

以下是分析器的示例跟踪:

exec [MYDB]..sp_procedure_params_rowset N'MyStoredProcedure',1,NULL,NULL

declare @p3 int
set @p3=NULL
exec sp_executesql 
    N'exec MyStoredProcedure @P1,@P2,@P3,@P4,@P5,@P6',
    N'@P1 int OUTPUT,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int',
    @p3 output,1,1,1,0,200
select @p3

This looks a little strange to me. Notice that it's using @p3 and @P3 - could this be causing my issue?

这看起来有点奇怪。请注意,它正在使用@ p3和@ P3 - 这可能导致我的问题吗?

The other strange thing is that it seems to depend on which TADOConnection I use.

另一个奇怪的事情是,它似乎取决于我使用的是哪个TADOConnection。

The project is a dll which is passed a TADOConnection from another application. It calls all the stored procedures using this connection.

该项目是一个dll,它从另一个应用程序传递一个TADOConnection。它使用此连接调用所有存储过程。

If instead of using this connection, I first do this:

如果不使用此连接,我首先执行此操作:

ConnectionNew := TADOQuery.Create(ConnectionOld.Owner);
ConnectionNew.ConnectionString := ConnectionOld.ConnectionString;
TADOQuery1.Connection := ConnectionNew;

Then the issue does not occur! The trace from this situation is this:

那么问题就不会发生!这种情况的痕迹是这样的:

exec [MYDB]..sp_procedure_params_rowset N'MyStoredProcedure',1,NULL,NULL

declare @p1 int
set @p1=64
exec sp_prepare @p1 output,
    N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 varchar(20)',
    N'exec MyStoredProcedure @P1,@P2,@P3,@P4,@P5,@P6',
    1
select @p1

SET FMTONLY ON exec sp_execute 64,0,0,0,0,0,' ' SET FMTONLY OFF

exec sp_unprepare 64

SET NO_BROWSETABLE OFF

exec sp_executesql 
    N'exec MyStoredProcedure @P1,@P2,@P3,@P4,@P5,@P6',
    N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 varchar(20)',
    1,1,1,3,0,'400.00'

Which is a bit much for lil ol' me to follow, unfortunately. What sort of TADOConnection options could be influencing this?

不幸的是,对于我来说,这有点儿了。什么样的TADOConnection选项可能会影响这个?

Does anyone have any ideas?

有没有人有任何想法?

Edit: Update below (didn't want to make this question any longer :P)

编辑:下面更新(不想再提出这个问题:P)

8 个解决方案

#1

In my programs, I have lots of code very similar to your first snippet, and I haven't encountered this problem.

在我的程序中,我有很多代码与你的第一个代码段很相似,我没有遇到过这个问题。

Is that actually your code, or is that how you've represented the problem for us to understand? Is the text for the SQL stored in your DFM or populated dynamically?

这实际上是您的代码,还是您如何代表我们理解的问题? SQL的文本是存储在DFM中还是动态填充的?

I was wondering if perhaps somehow the Params property of the query had already got a list of parameters defined/cached, in the IDE, and that might explain why P1 was being seen as output (which is almost certainly causing your NULL problem).

我想知道是否可能以某种方式查询的Params属性已经在IDE中获得了定义/缓存的参数列表,这可能解释了为什么P1被视为输出(这几乎肯定会导致您的NULL问题)。

Just before you set the ParamByName.Value, try

在您设置ParamByName.Value之前,请尝试

ParamByName("Foo").ParamType=ptInput;

I'm not sure why you changing the connection string would also fix this, unless it's resetting the internal sense of the parameters for that query.

我不确定为什么你改变连接字符串也会解决这个问题,除非它重置了该查询的参数的内部意义。

Under TSQLQuery, the Params property of a query gets reset/recreated whenever the SQL.Text value is changed (I'm not sure if that's true for a TADOQuery mind you), so that first snippet of yours ought to have caused any existing Params information to have been dropped.

在TSQLQuery下,只要SQL.Text值发生变化,我就会重置/重新创建查询的Params属性(我不确定TADOQuery是否适合你),这样你的第一个片段就应该导致任何现有的Params信息已被删除。

If the 'ParamByname.ParamType' suggestion above does fix it for you, then surely there's something happening to the query elsewhere (at create-time? on the form?) that is causing it to think Foo is an output parameter...

如果上面的'ParamByname.ParamType'建议确实为你解决了,那么肯定会在其他地方(在表单上的创建时?)查询发生的事情导致它认为Foo是一个输出参数......

does that help at all? :-)

这些帮助有用? :-)

#2

caveat: i don't know delphi, but this issue rings a faint bell and so i'm interested in it

警告:我不知道德尔福,但这个问题响起了一个微弱的钟声,所以我对它很感兴趣

do you get the same result if you use a TADOStoredProc instead of a TADOQuery? see delphi 5 developers guide

如果你使用TADOStoredProc而不是TADOQuery,你会得到相同的结果吗?请参阅delphi 5开发人员指南

also, it looks like the first trace does no prepare call and thinks @P1 is an output paramer in the execute, while the second trace does a prepare call with @P1 as an output but does not show @P1 as an output in the execute step - is this significant? it does seem odd, and so may be a clue

此外,看起来第一个跟踪没有准备调用,并且认为@ P1是执行中的输出参数,而第二个跟踪执行使用@ P1作为输出的准备调用,但是在执行中不显示@ P1作为输出步骤 - 这是重要的吗?它似乎很奇怪,所以可能是一个线索

you might also try replacing the function call with a constant 1

您也可以尝试用常量1替换函数调用

good luck, and please let us know what you find out!

祝你好运,请让我们知道你发现了什么!

#3

I suspect you have some parameters mismatch left over from the previous use of your ADOQuery.

我怀疑你上次使用ADOQuery时遗留了一些参数不匹配。

Have you tried to reset your parameters after changing the SQL.Text:

您是否尝试在更改SQL.Text后重置参数:

  ADOQuery1.Parameters.Refresh;

Also you could try to clear the parameters and explicitly recreate them:

您还可以尝试清除参数并显式重新创建它们:

  ADOQuery1.Parameters.Clear;
  ADOQuery1.Parameters.CreateParameter('Foo', ftInteger, pdInput, 0, 1);
  [...]

I think changing the connection actually forces an InternalRefresh of the parameters.

我认为改变连接实际上会强制参数的InternalRefresh。

#4

 ADOQuery1.Parameters.ParamByName("Foo").Value = Integer(someFunction()); 

Don't they use := for assignment in Object Pascal?

他们不使用:=在Object Pascal中进行赋值?

#5

@Constantin

It must be a typo from the Author of the question.

它一定是来自问题作者的拼写错误。

@Blorgbeard

Hmmm... When you change SQL of a TADOQuery, is good use to clear the parameters and recreate then using CreateParameter. I would not rely on ParamCheck in runtime - since it leaves the parameters' properties mostly undefined. I've had such type of problem when relying on ParamCheck to autofill the parameters - is rare but occurs. Ah, if you go the CreateParameter route, create as first parameter the @RETURN_VALUE one, since it'll catch the returned value of the MSSQL SP.

嗯...当您更改TADOQuery的SQL时,最好使用清除参数并使用CreateParameter重新创建。我不会在运行时依赖ParamCheck - 因为它使参数的属性大多未定义。当依靠ParamCheck自动填充参数时,我遇到了这种类型的问题 - 很少见,但却发生了。啊,如果你去CreateParameter路由,创建@RETURN_VALUE的第一个参数,因为它将捕获MSSQL SP的返回值。

#6

The only time I've had a problem like this was when the DB Provider couldn't distinguish between Output (always sets it to null) and InputOutput (uses what you provide) parameters.

我遇到这样的问题的唯一一次是DB Provider无法区分Output(始终将其设置为null)和InputOutput(使用您提供的)参数。

#7

Ok, progress is made.. sort of.

好的,取得了进展。

@Robsoft was correct, setting the parameter direction to pdInput fixed the issue.

@Robsoft是正确的,将参数方向设置为pdInput修复了问题。

I traced into the VCL code, and it came down to TParameters.InternalRefresh.RefreshFromOleDB. This function is being called when I set the SQL.Text. Here's the (abridged) code:

我追溯到VCL代码,它归结为TParameters.InternalRefresh.RefreshFromOleDB。在设置SQL.Text时调用此函数。这是(删节)代码:

function TParameters.InternalRefresh: Boolean;
  procedure RefreshFromOleDB;
    // ..
        if OLEDBParameters.GetParameterInfo(ParamCount, PDBPARAMINFO(ParamInfo), @NamesBuffer) = S_OK then
          for I := 0 to ParamCount - 1 do
            with ParamInfo[I] do
            begin
              // ..
              Direction := dwFlags and $F;       // here's where the wrong value comes from
              // ..
            end;
     // ..
  end;
  // ..
end;

So, OLEDBParameters.GetParameterInfo is returning the wrong flags for some reason.

因此,OLEDBParameters.GetParameterInfo由于某种原因返回错误的标志。

I've verified that with the original connection, (dwFlags and $F) is 2 (DBPARAMFLAGS_ISOUTPUT), and with the new connection, it's 1 (DBPARAMFLAGS_ISINPUT).

我已经验证了原始连接(dwFlags和$ F)是2(DBPARAMFLAGS_ISOUTPUT),并且使用新连接时,它是1(DBPARAMFLAGS_ISINPUT)。

I'm not really sure I want to dig any deeper than that, for now at least.

至少现在我还不确定我是否想要深入挖掘这一点。

Until I have more time and inclination, I'll just make sure all parameters are set to pdInput before I open the query.
Unless anyone has any more bright ideas now..?

在我有更多时间和倾向之前,我将确保在打开查询之前将所有参数都设置为pdInput。除非现在有人有更好的想法..?

Anyway, thanks everyone for your suggestions so far.

无论如何,感谢大家到目前为止的建议。

#8

I was having a very similar issue using TADOQuery to retrieve some LDAP info, there is a bug in the TParameter.InternalRefresh function that causes an access violation even if your query has no parameters.

我在使用TADOQuery检索一些LDAP信息时遇到了一个非常类似的问题,TParameter.InternalRefresh函数中存在一个错误,即使您的查询没有参数也会导致访问冲突。

To solve this, simply set TADOQuery.ParamCheck to false.

要解决此问题,只需将TADOQuery.ParamCheck设置为false即可。