sybase - INSERT 失败，因为以下 SET 选项仅在 Perl Sybase 中设置不正确

Question

我有以下 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 *** 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
            ,ContributionContractorRate
            ,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
            ,NonChildContributionAmount
            ,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= 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.

我知道这是一个糟糕的问题。但我通过三个不同的 SQL Server GUI 运行了相同的查询，但没有收到此错误。我浏览了谷歌搜索结果的前三四页，却一无所获。任何信息将不胜感激。

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

Answer 1

请引用this link您可能需要设置顺序来创建具有持久计算列的表，必须启用以下连接设置:

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”或任何其他命令。