dongzhou8764 2016-08-25 14:54
浏览 91
已采纳

从PHP执行时SQL Server查询不起作用

I've got a set of triggers in my table in SQL Server and when I execute the queries in SQL Server Management Studio they work fine. But when they're executed from my php files they doesn't take effect. I used SQL Server Profiler and the trigger gets to the last line of execution that is the insert in my log table but then after this I get an Attention error. Here's my trigger for a delete statement:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Batch submitted through debugger: SQLQuery25.sql|7|0|C:\Users\ADMINI~1\AppData\Local\Temp\3\~vsB4EE.sql

ALTER TRIGGER [dbo].[OperationStructureFields_delete]
ON [dbo].[OperationStructureFields]
FOR DELETE
AS
BEGIN
    DECLARE
        @id INT,
        @result varchar(MAX),
        @user varchar(MAX),
        @LoopCounter INT = 1, 
        @MAX INT, 
        @Column NVARCHAR(100),
        @Type NVARCHAR(100),
        @Value NVARCHAR(100),
        @ValueXML xml,
        @Sql NVARCHAR(MAX),
        @Tmp NVARCHAR(MAX),
        @LoopCounter2 INT = 1,
        @MAX2 INT,
        @Message nvarchar(2048)
    SELECT @user = system_user
    SELECT @MAX = MAX(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'OperationStructureFields'
    SELECT @MAX2 = MAX(ID) FROM deleted
    SELECT @LoopCounter2 = MIN(ID) FROM deleted
    Select * into #deleted from deleted
    WHILE(@LoopCounter2 <= @MAX2)
    BEGIN
        SET @LoopCounter = 1
        SET @result = '{'
        WHILE(@LoopCounter <= @MAX)
        BEGIN
           SELECT @Column = COLUMN_NAME, @Type = DATA_TYPE
           FROM INFORMATION_SCHEMA.COLUMNS WHERE ORDINAL_POSITION = @LoopCounter and TABLE_NAME = 'OperationStructureFields'
           if (@Type = 'xml')
           BEGIN
            SET @Sql = 'SELECT @ValueXML = ' + @Column + ' FROM #deleted where ID=' + CONVERT(varchar(200),@LoopCounter2,0)
            exec sp_executesql @Sql, N'@ValueXML xml out', @ValueXML out
            SET @Value = CONVERT(VARCHAR(MAX),@ValueXML,0)
           END
           ELSE
           BEGIN
             SET @Sql = 'SELECT @Value = ' + @Column + ' FROM #deleted where ID=' + CONVERT(varchar(200),@LoopCounter2,0)
             exec sp_executesql @Sql, N'@Value varchar(MAX) out', @Value out 
           END    
           IF (@Value is not null or @Value != '') and  (@Type = 'datetime2' or @Type = 'datetime' or @Type = 'date')
           BEGIN
                IF @result = '{'
                BEGIN
                    SET @result = @result + ' "' + @Column + '":"' + CONVERT(VARCHAR(20),@Value,120) + '"'
                END
                ELSE
                BEGIN
                    SET @result = @result + ', "' + @Column + '":"' + CONVERT(VARCHAR(20),@Value,120) + '"'
                END
           END
           ELSE IF (@Value is not null or @Value != '')
           BEGIN
                IF @result = '{'
                BEGIN
                    SET @result = @result + ' "' + @Column + '":"' + @Value + '"'
                END
                ELSE
                BEGIN
                    SET @result = @result + ', "' + @Column + '":"' + @Value + '"'
                END
           END
           SET @LoopCounter  = @LoopCounter + 1 
        END
        SET @result = @result + '}'
        INSERT INTO sys_logs (username,datahora,tabela,[object_id],[action],oldvalue) values (@user,GETDATE(),'OperationStructureFields',@LoopCounter2,'DELETE',@result)
        delete from #deleted where ID = @LoopCounter2
        select @LoopCounter2 = MIN(ID) from #deleted where ID > @LoopCounter2  
    END
END

And a print of my SQL Server Profiler:

Prt Screen do SQL Server Profiler

The PHP code is:

$res = sqlsrv_query($connection, $_sql, array(), array('Scrollable' => 'buffered'));

and the variable $_sql has the following value:

DELETE FROM OperationStructureFields WHERE ID= '66817'

And it doesn't return any errors.

The PHP version is 5.5.16.

I executed the SELECT @@OPTIONS inside my php file and got the following options:

ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL

The only difference to the SQL Server Management Studio is the option ARITHABORT that is ON in the SQL Server Management Studio.

Do you have any ideia what may be causing this?

UPDATES:

I believe it may be something on my PHP settings. In my phpinfo() I've got the following settings for sqlsrv:

phpinfo();

Also in SQL Server Profiler I've got the following definitions in Audit Login:

-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed
  • 写回答

2条回答 默认 最新

  • douqiangchuai7674 2016-08-31 06:06
    关注

    Messages

    Changed database context to XXXXXXX

    Changed language setting to YYYYYYY

    are not error messages. They are informational messages and should be ignored by applications. PHP can be configured to ignore it.

    Severity Levels: https://msdn.microsoft.com/en-us/library/ms164086.aspx

    PHP Settings:

    1) php.ini changes:

    mssql.min_error_severity = 11
    mssql.min_message_severity = 11
    

    or

    2) by executing

    mssql_min_error_severity(11);
    

    just before execution of mssql_query()

    http://php.net/manual/en/function.mssql-min-error-severity.php

    UPDATE:

    3) The trigger should have

    set nocount on;
    

    at the top of code, just after:

    AS
    BEGIN
    

    This will prevent sending multiple informational messages (like "xx rows affected") to the application.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 seatunnel-web使用SQL组件时候后台报错,无法找到表格
  • ¥15 fpga自动售货机数码管(相关搜索:数字时钟)
  • ¥15 用前端向数据库插入数据,通过debug发现数据能走到后端,但是放行之后就会提示错误
  • ¥30 3天&7天&&15天&销量如何统计同一行
  • ¥30 帮我写一段可以读取LD2450数据并计算距离的Arduino代码
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能