Sql存储过程/ Php问题

Question

我在使用PHP调用Sql Server 2008存储过程时遇到了一个问题。当我在sql中执行存储过程时，我得到了结果，所以我知道问题不在于存储过程(如下所示)。Sql存储过程

USE [HRAPPS]
GO
/****** Object:  StoredProcedure [dbo].[sp_GetSecurityAnswer]    Script Date: 10/21/2011 08:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_GetSecurityAnswer]
(
    @ThisContactId INT, @ThisQuestionId INT, @ThisAnswer varchar(255)
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    SELECT 
            a.Cont_id,
            a.QuestionId
    FROM    dbo.sec_answers a
    WHERE   a.cont_id = @ThisContactId and
            a.QuestionId = @ThisQuestionId and
            lower(a.Response) = lower(@ThisAnswer)
END

下面是调用存储过程的PHP代码。注意:与数据库的连接正常，所以我知道这不是问题。

*PHP代码

if(!$dbCon=mssql_connect(DBHOST,DBUSER,DBPASS)) 
{
   $error = "Unable to Connect to Database. Please Try Again Later.";
} 
else 
{
   $stmt = mssql_init("sp_GetSecurityAnswer", $dbCon);
   mssql_bind($stmt, "@ThisContactId", $_SESSION['thisContId'], SQLINT4, false, false, 6);
   mssql_bind($stmt, "@ThisQuestionId", $_SESSION['SecQuestion'], SQLINT4, false, false, 2);
   mssql_bind($stmt, "@ThisAnswer", $_SESSION['ThisAnswer'], SQLVARCHAR, false, false, 255);
   $Security_Verification_Result = mssql_execute($stmt, false);     
   IF(!mssql_num_rows($Security_Verification_Result)) 
   {
     ECHO "You have Incorrectly answered your selected Security Verification Question, Please go back and try again";
    EXIT();
   }
   ELSE
   {        
     header("Location: some_url?userfullname=".$_SESSION['userfullname'] .'&cont_id='.$_SESSION['ThisContId']);
   }
}   
exit();

PHP代码结束

所以我错过了什么男人(还有女孩!)??解析:选D提前感谢

Answer 1

你的代码:

mssql_bind($stmt, "@ThisContactId", $_SESSION['thisContId'], SQLINT4, false, false, 6);
mssql_bind($stmt, "@ThisQuestionId", $_SESSION['SecQuestion'], SQLINT4, false, false, 2);
//        scroll right-->>                                                           ^^

可选的最大长度参数是怎么回事?它们不应该都是-1 (max len)还是不传递它们??这个可选参数只对字符串有效，参见mssql_bind

如果这不起作用，您唯一的其他选择是在PHP中回显参数并从过程中将它们记录到日志表中。通过这种方式，您可以确保在过程中发送和接收正确的值。

编辑
从SQL创建和插入日志很容易，方法如下:

创建表

CREATE TABLE MyLog (LogID int not null identity(1,1) primary key
                   ,LogDate datetime not null default GETDATE()
                   ,LogValue varchar(max) not null
                   )

在SET NOCOUNT ON;之后的程序中添加此

INSERT INTO MyLog 
    (LogValue) 
    VALUES (ISNULL(OBJECT_NAME(@@PROCID), 'unknown')
               +', '+ISNULL('"'+CONVERT(varchar(15),@ThisContactId)+'"','NULL')
               +', '+ISNULL('"'+CONVERT(varchar(15),@ThisQuestionId)+'"','NULL')
               +', '+ISNULL('"'+@ThisAnswer+'"','NULL')
           )

运行应用程序后，使用以下命令查看日志中的内容:

SELECT * FROM MyLog ORDER BY LogID

可以让你更好地了解SQL Server在查询中使用的实际参数