programing

SQL 예외: 문자열 또는 이진 데이터가 잘립니다.

topblog 2023. 6. 27. 21:37
반응형

SQL 예외: 문자열 또는 이진 데이터가 잘립니다.

나는 많은 삽입문을 일괄 처리하는 C# 코드를 가지고 있습니다.이러한 명령문을 실행하는 동안 "문자열 또는 이진 데이터가 잘립니다" 오류가 발생하고 트랜잭션이 롤백되었습니다.

어떤 insert 문이 원인인지 알아보려면 오류가 발생할 때까지 SQL Server에 하나씩 삽입해야 합니다.

예외 처리를 사용하여 어떤 문과 어떤 필드가 이 문제를 일으켰는지 알아내는 현명한 방법이 있습니까? (SqlException)

일반적으로 오류가 발생한 특정 문을 확인할 수 있는 방법은 없습니다.여러 개를 실행 중인 경우 프로파일러를 보고 마지막으로 완료된 문을 보고 그 이후의 문이 무엇인지 확인할 수 있습니다. 이러한 접근 방식이 실현 가능한지는 모르겠습니다.

어떤 경우에도 매개 변수 변수 중 하나(및 매개 변수 내부의 데이터)가 데이터를 저장하려는 필드에 비해 너무 큽니다.모수 크기를 열 크기와 비교하여 확인하면 해당 필드가 매우 빨리 명확해질 것입니다.

이 유형의 오류는 SQL Server 열의 데이터 유형 길이가 항목 양식에 입력된 데이터 길이보다 작을 때 발생합니다.

이러한 유형의 오류는 일반적으로 데이터베이스 테이블에서 지정한 것보다 많은 문자 또는 값을 입력해야 할 때 발생합니다. 이 경우 transaction_status varchar(10)를 지정하지만 실제로는 19개의 문자를 포함하는 _transaction_status를 저장하려고 합니다.그것이 당신이 이 코드에서 이런 유형의 오류에 직면한 이유입니다.

일반적으로 허용된 최대값보다 큰 값을 삽입하는 것입니다.예를 들어, 데이터 열은 최대 200자까지만 사용할 수 있지만 201자 문자열을 삽입하는 경우

BEGIN TRY
    INSERT INTO YourTable (col1, col2) VALUES (@val1, @val2)
END TRY
BEGIN CATCH
    --print or insert into error log or return param or etc...
    PRINT '@val1='+ISNULL(CONVERT(varchar,@val1),'')
    PRINT '@val2='+ISNULL(CONVERT(varchar,@val2),'')
END CATCH

SQL 2016 SP2 이상의 경우 다음 링크를 따르십시오.

이전 버전의 SQL의 경우 다음을 수행합니다.

  1. 문제의 원인이 되는 쿼리를 가져옵니다(소스가 없는 경우 SQL Profiler를 사용할 수도 있음).
  2. 기본적으로 SELECT 및 FROM 부품만 남을 때까지 모든 WHERE 절 및 기타 중요하지 않은 부품을 제거합니다.
  3. WHERE 0 = 1 추가(테이블 구조만 선택됨)
  4. FROM 절 바로 앞에 INTO [MyTempTable] 추가

당신은 다음과 같은 것으로 끝나야 합니다.

SELECT
 Col1, Col2, ..., [ColN]
INTO [MyTempTable]
FROM
  [Tables etc.]
WHERE 0 = 1

이렇게 하면 DB에 MyTempTable이라는 테이블이 생성되어 대상 테이블 구조와 비교할 수 있습니다. 즉, 두 테이블의 열을 비교하여 서로 다른 위치를 확인할 수 있습니다.이것은 약간의 해결책이지만 제가 찾은 방법 중 가장 빠른 방법입니다.

통화 삽입 방법에 따라 다릅니다.모두 한 통화로 하시겠습니까, 아니면 트랜잭션 내 개별 통화로 하시겠습니까?개별적으로 전화를 걸 경우에는 "예"를 선택합니다(통화를 반복하면서 실패한 전화를 받습니다).한 번의 큰 호출이 있으면 안 됩니다. SQL이 전체 문을 처리하기 때문에 코드의 손에서 벗어나 있습니다.

다음을 통해 위반 필드를 쉽게 찾을 수 있는 방법을 만들었습니다.

  1. 삽입/업데이트할 테이블의 모든 열의 열 너비를 가져옵니다.(데이터베이스에서 이 정보를 직접 가져옵니다.)
  2. 열 너비를 삽입/업데이트하려는 값의 너비와 비교합니다.

가정/제한 사항:

  1. 데이터베이스에 있는 테이블의 열 이름이 C# 도면요소 필드와 일치합니다.예: 데이터베이스에 다음과 같은 열이 있는 경우:

    엔티티의 열 이름이 동일해야 합니다.

    public class SomeTable
{
   // Other fields
   public string SourceData { get; set; }
}

  2. 한 번에 하나의 엔티티를 삽입/업데이트하는 중입니다.아래 데모 코드에서 더 명확해질 것입니다.대량 삽입/업데이트를 수행하는 경우 수정하거나 다른 솔루션을 사용할 수 있습니다.

1단계:

모든 열의 열 너비를 데이터베이스에서 직접 가져옵니다.

// For this, I took help from Microsoft docs website:
// https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.getschema?view=netframework-4.7.2#System_Data_SqlClient_SqlConnection_GetSchema_System_String_System_String___
private static Dictionary<string, int> GetColumnSizesOfTableFromDatabase(string tableName, string connectionString)
{
    var columnSizes = new Dictionary<string, int>();
            
    using (var connection = new SqlConnection(connectionString))
    {
        // Connect to the database then retrieve the schema information.  
        connection.Open();

        // You can specify the Catalog, Schema, Table Name, Column Name to get the specified column(s).
        // You can use four restrictions for Column, so you should create a 4 members array.
        String[] columnRestrictions = new String[4];

        // For the array, 0-member represents Catalog; 1-member represents Schema;
        // 2-member represents Table Name; 3-member represents Column Name.
        // Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.
        columnRestrictions[2] = tableName;

        DataTable allColumnsSchemaTable = connection.GetSchema("Columns", columnRestrictions);

        foreach (DataRow row in allColumnsSchemaTable.Rows)
        {
            var columnName = row.Field<string>("COLUMN_NAME");
            //var dataType = row.Field<string>("DATA_TYPE");
            var characterMaxLength = row.Field<int?>("CHARACTER_MAXIMUM_LENGTH");

            // I'm only capturing columns whose Datatype is "varchar" or "char", i.e. their CHARACTER_MAXIMUM_LENGTH won't be null.
            if(characterMaxLength != null)
            {
                columnSizes.Add(columnName, characterMaxLength.Value);
            }
        }

        connection.Close();
    }

    return columnSizes;
}

2단계:

열 너비를 삽입/업데이트하려는 값의 너비와 비교합니다.

public static Dictionary<string, string> FindLongBinaryOrStringFields<T>(T entity, string connectionString)
{
    var tableName = typeof(T).Name;
    Dictionary<string, string> longFields = new Dictionary<string, string>();
    var objectProperties = GetProperties(entity);
    //var fieldNames = objectProperties.Select(p => p.Name).ToList();

    var actualDatabaseColumnSizes = GetColumnSizesOfTableFromDatabase(tableName, connectionString);
            
    foreach (var dbColumn in actualDatabaseColumnSizes)
    {
        var maxLengthOfThisColumn = dbColumn.Value;
        var currentValueOfThisField = objectProperties.Where(f => f.Name == dbColumn.Key).First()?.GetValue(entity, null)?.ToString();

        if (!string.IsNullOrEmpty(currentValueOfThisField) && currentValueOfThisField.Length > maxLengthOfThisColumn)
        {
            longFields.Add(dbColumn.Key, $"'{dbColumn.Key}' column cannot take the value of '{currentValueOfThisField}' because the max length it can take is {maxLengthOfThisColumn}.");
        }
    }

    return longFields;
}

public static List<PropertyInfo> GetProperties<T>(T entity)
{
    //The DeclaredOnly flag makes sure you only get properties of the object, not from the classes it derives from.
    var properties = entity.GetType()
                            .GetProperties(System.Reflection.BindingFlags.Public
                            | System.Reflection.BindingFlags.Instance
                            | System.Reflection.BindingFlags.DeclaredOnly)
                            .ToList();

    return properties;
}

데모:

를 들어 예들어, 우가그을을 삽입하려고 someTableEntitySomeTable다음과 같이 우리 앱에서 모델링된 클래스:

public class SomeTable
{
    [Key]
    public long TicketID { get; set; }
    public string SourceData { get; set; }
}

그리고 그것은 우리의 내부에 있습니다.SomeDbContext 예:

public class SomeDbContext : DbContext
{
    public DbSet<SomeTable> SomeTables { get; set; }
}

DB의 이 테이블은 다음과 같습니다.SourceData로서 필드에.varchar(16) 이와같이:

이제 이 필드에 16자보다 긴 값을 삽입하고 다음 정보를 캡처합니다.

public void SaveSomeTableEntity()
{
    var connectionString = "server=SERVER_NAME;database=DB_NAME;User ID=SOME_ID;Password=SOME_PASSWORD;Connection Timeout=200";
        
    using (var context = new SomeDbContext(connectionString))
    {
        var someTableEntity = new SomeTable()
        {
            SourceData = "Blah-Blah-Blah-Blah-Blah-Blah"
        };
        
        context.SomeTables.Add(someTableEntity);
        
        try
        {
            context.SaveChanges();
        }
        catch (Exception ex)
        {
            if (ex.GetBaseException().Message == "String or binary data would be truncated.\r\nThe statement has been terminated.")
            {
                var badFieldsReport = "";
                List<string> badFields = new List<string>();
                
                // YOU GOT YOUR FIELDS RIGHT HERE:
                var longFields = FindLongBinaryOrStringFields(someTableEntity, connectionString);

                foreach (var longField in longFields)
                {
                    badFields.Add(longField.Key);
                    badFieldsReport += longField.Value + "\n";
                }
            }
            else
                throw;
        }
    }
}

그badFieldsReport값은 다음과 같습니다.

'SourceData' 열은 사용할 수 있는 최대 길이가 16이므로 'Blah-Blah-Blah-Blah-Blah-Blah' 값을 사용할 수 없습니다.

그것은 또한 당신이 그것을 넣으려고 노력하기 때문일 수 있습니다.null값을 데이터베이스에 다시 입력합니다.따라서 트랜잭션 중 하나에 null이 있을 수 있습니다.

여기서 대부분의 답변은 데이터베이스에 정의된 열의 길이가 전달하려는 데이터보다 작지 않은지 확인하는 것입니다.

SQL Management Studio에서 간단한 작업을 하는 것에 여러 번 시달렸습니다.

sp_help 'mytable'

그리고 문제의 열이 nvarchar라는 것을 깨닫기까지 몇 분 동안 혼란스러웠습니다. 즉, sp_help가 보고한 길이는 이중 바이트(유니코드) 데이터 유형이기 때문에 지원되는 실제 길이의 두 배입니다.

즉, sp_help가 nvarchar Length 40을 보고하는 경우 최대 20자를 저장할 수 있습니다.

이 요점을 확인하십시오.https://gist.github.com/mrameezraja/9f15ad624e2cba8ac24066cdf271453b .

public Dictionary<string, string> GetEvilFields(string tableName, object instance)
    {
        Dictionary<string, string> result = new Dictionary<string, string>();

        var tableType = this.Model.GetEntityTypes().First(c => c.GetTableName().Contains(tableName));

        if (tableType != null)
        {
           int i = 0;

           foreach (var property in tableType.GetProperties())
           {
               var maxlength = property.GetMaxLength();
               var prop = instance.GetType().GetProperties().FirstOrDefault(_ => _.Name == property.Name);

               if (prop != null)
               {
                   var length = prop.GetValue(instance)?.ToString()?.Length;

                   if (length > maxlength)
                   {
                        result.Add($"{i}.Evil.Property", prop.Name);
                        result.Add($"{i}.Evil.Value", prop.GetValue(instance)?.ToString());
                        result.Add($"{i}.Evil.Value.Length", length?.ToString());
                        result.Add($"{i}.Evil.Db.MaxLength", maxlength?.ToString());
                        i++;
                    }
               }
            }
       }

       return result;
    }

Linkq To SQL을 사용하여 컨텍스트를 기록하여 디버깅했습니다. Context.Log = Console.Out그런 다음 SQL을 스캔하여 명백한 오류가 있는지 확인합니다. 두 가지가 있습니다.

-- @p46: Input Char (Size = -1; Prec = 0; Scale = 0) [some long text value1]
-- @p8: Input Char (Size = -1; Prec = 0; Scale = 0) [some long text value2]

값을 기준으로 테이블 스키마를 스캔하여 찾은 마지막 하나, 필드는 nvarchar(20)였지만 값은 22자였습니다.

-- @p41: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [1234567890123456789012]

우리의 경우, 나는 프론트 엔드에서 게시된 총 문자보다 작은 sql 테이블 허용 문자 또는 필드 크기를 늘립니다.따라서 문제가 해결됩니다.

간단히 사용:메시지 상자.쇼(cmd4).명령 텍스트.ToString(); c#.net에서 기본 쿼리, 복사하여 데이터베이스에서 실행합니다.

언급URL : https://stackoverflow.com/questions/779082/sqlexception-string-or-binary-data-would-be-truncated

반응형

'programing' 카테고리의 다른 글

Pandas DataFrame에서 Parquet 파일을 읽는 방법은 무엇입니까?  (0) 2023.06.27
Ruby에서 추상 클래스를 구현하는 방법  (0) 2023.06.27
Python mysqldb: 라이브러리가 로드되지 않음: libmysqlclient.18.dylib  (0) 2023.06.27
Excel 하이퍼링크 대량 업데이트  (0) 2023.06.27
구체화된 보기의 원본 스크립트를 보는 방법은 무엇입니까?  (0) 2023.06.27

'programing'의 다른글

  • 현재글SQL 예외: 문자열 또는 이진 데이터가 잘립니다.

관련글

댓글

티스토리툴바