问题 INSERT失败，因为以下SET选项仅在Perl Sybase中具有不正确的设置

我有以下Perl脚本：

use strict;
use warnings;
use DBI;

my $db_connect = 'dbi:Sybase:server=10.2.2.2\CATDB;charset=utf8;database=Dev';
my $db_username = "*****";
my $db_password = "*****";

my $dbh = DBI->connect($db_connect, $db_username, $db_password,{ RaiseError => 1, 
            PrintError => 1, 
            AutoCommit => 1,
            syb_chained_txn => 0, 
            syb_enable_utf8 => 1  } ) || die "Failed to connect to BORIS database: $DBI::errstr\n";

my $insertContractSQL2 = '
BEGIN
DECLARE @ContractID int
UPDATE dbo.Sequences SET NextContractID = NextContractID + 1
SET @ContractID = (SELECT NextContractID FROM dbo.Sequences)

SET ANSI_NULL_DFLT_ON, ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON
SET ANSI_NULL_DFLT_OFF, ARITHIGNORE, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS, NOCOUNT, NUMERIC_ROUNDABORT, XACT_ABORT OFF

INSERT INTO dbo.CONTRACTS 
            (ContractID
            ,modifieddate
            ,FranchiseID
            ,FamilyID
            ,EducatorID
            ,StartDate
            ,EndDate
            ,ContractTypeID
            ,PayRate1
            ,PayRate2
            ,PayRate3
            ,PayRate1Hours
            ,PayRate2Hours
            ,PayRate3Hours
            ,WageAdminContractorRate
            ,PorseContributionContractorRate
            ,WageAdminAmount
            ,ACCAmount
            ,PorseContributionAmount
            ,WINZSubsidyAmount
            ,WINZSubsidyAmountChildcareOSCAR
            ,ACCInvoicedPerQuarterAmount
            ,FamilyAPAmount
            ,OtherFortnightPayment
            ,OtherFortnightPaymentDesc
            ,ReferralAgencyID
            ,NextAppraisalDate
            ,NextAppraisalTypeID
            ,PendingApproval
            ,Active
            ,modifiedby
            ,BeingEdited
            ,MOENetworkID
            ,NewFlag
            ,ReceivedDate
            ,FreeECEAmount
            ,OptionalChargeRate
            ,OptionalChargeAgreement
            ,TerminationApproved
            ,AgreedDeductions
            ,PayRateEce
            ,PayRateEceHours
            ,PreECEClarity
            ,TotalOptionalCharges
            ,NonChildPorseContributionAmount
            ,FreeECETopup
            ,Donation
            ,NonChildWinzChildcareAmount
            ,ManuallyTerminated
            ,ContractDuplicatedFlag
            ,CreateDate
            ,RosteredContractID)
            VALUES (
             @ContractID
            ,GETDATE()
            ,63,22901,9134,\'2014-06-03 00:00:00.0\',\'2014-06-28 00:00:00.0\',2,0,0,0,5,0,0,4.75,0,0,0,0,0,0,0,0,0,null,null,null,null,0,1,\'admin\',1,null,0,\'2014-06-10 00:00:00.0\',0,0,0,0,null,0,0,0,0,0,0,0,0,0,0,\'2014-06-03 15:30:15.037\',4)

END  
';

 $dbh->do($insertContractSQL2);

当它运行我得到：

/usr/bin/perl test.pl
DBD::Sybase::db do failed: Server message number=1934 severity=16 state=1 
    line=10 server=BOBSTER\BOBSTER1 text=INSERT failed because the
    following SET options have incorrect settings: 'ANSI_NULLS, 
    CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that 
    SET options are correct for use with indexed views and/or indexes on 
    computed columns and/or filtered indexes and/or query notifications 
    and/or XML data type methods and/or spatial index operations.  
    at test.pl line 89.

现在这是一个我知道的糟糕问题。但我通过三个不同的GUI为SQL Server运行相同的查询，我没有得到这个错误。我已经浏览了谷歌搜索结果的前3或4页，并且无处可去。任何信息将不胜感激。

注意： 我假设因为查询在其他工具中运行，设置选项是正确的

请参考这个链接您可能需要设置顺序以创建具有持久计算列的表，必须启用以下连接设置：

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT ON
SET QUOTED_IDENTIFIER ON

您可以使用$ sth-> do（）方法或使用ISQL进行设置。在执行“SELECT”，“UPDATE”或任何其他命令之前，需要在连接到DB之后首先执行它们。

我以这种令人讨厌的方式解决了它：

EXEC('
    IF EXISTS(SELECT * FROM sysindexes WHERE Name = ''IX_GPS_XY'') 
        DROP INDEX [IX_GPS_XY] ON [dbo].[Cities];
')
EXEC('
    INSERT INTO dbo.Cities(Name, CountyID, GPSXY) 
    VALUES...
')
EXEC('
    IF NOT EXISTS(SELECT * FROM sysindexes WHERE Name = ''IX_GPS_XY'') BEGIN
        SET ARITHABORT ON;
        ...
        CREATE SPATIAL INDEX...
')

谢谢，是的，它应该如何工作。然而，这没有帮助。 - Dan Walmsley

如果直接在db上执行insertContractSQL2语句，你会收到任何错误吗？ - Chankey Pathak

不，我没有在那里得到任何错误。这个缺失的阴影是该集合必须是第一件被执行的东西。首先将$ boris_dbh-> do（'SET ANSI_NULL_DFLT_ON，ANSI_NULLS，ANSI_PADDING，ANSI_WARNINGS，ARITHABORT，CONCAT_NULL_YIELDS_NULL，QUOTED_IDENTIFIER设置为ANSI_NULL_DFLT_OFF，ARITHIGNORE，CURSOR_CLOSE_ON_COMMIT，IMPLICIT_TRANSACTIONS，NOCOUNT，XACT_ABORT，NUMERIC_ROUNDABORT OFF'）;修复。 - Dan Walmsley

啊，我明白了。是的，SET应该是连接到DB后的第一件事。 - Chankey Pathak

现在我有一个简单的例子。但是当我尝试在真正的代码中做同样的事情时，它会破坏。这不是我的日子...... - Dan Walmsley

问题 INSERT失败，因为以下SET选项仅在Perl Sybase中具有不正确的设置

答案:

热门问题