Exception is thrown when inserting a row with a PK of nvarchar

TRuhland

Exception is thrown when inserting a row with a PK of nvarchar

Hi,

I'm using Databases.dll version 1.5.1.0.

Supposed you have a sql table like this (my PK is a nvarchar):

CREATE TABLE [dbo].[__languageAssignment](
    [keyName] [nvarchar](50) NOT NULL PRIMARY KEY
    [de] [nvarchar](max) NULL,
    [en] [nvarchar](max) NULL,
 )

... and you try to insert a new row, you will get the sql error "String or binary data would be truncated".

The reason for this the dynamic created sql command:

exec sp_executesql N'DECLARE @T TABLE ( insert_id nvarchar ); 
INSERT INTO  __languageAssignment  ( keyName, en, de ) OUTPUT INSERTED.keyName as insert_id INTO @T VALUES (  @keyName,  @en,  @de ); 
SELECT insert_id FROM @T',N'@keyName nvarchar(9),@en nvarchar(3),@de nvarchar(2)',@keyName=N'myKeyName',@en=N'Foo',@de=N'Ba'

As you can see, there is no data length behind the ...(insert_id nvarchar ...

This is because the _Prepare() method of the Query.cs does not ask for the data length:

pkeyCmd.CommandText = @"
    SELECT
      INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME as column_name,
      INFORMATION_SCHEMA.COLUMNS.DATA_TYPE as data_type
    FROM
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE ...

So I changed this part to ...

pkeyCmd.CommandText = @"
    SELECT
      INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME as column_name,
      INFORMATION_SCHEMA.COLUMNS.DATA_TYPE as data_type,
        INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH as data_length
    FROM
        INFORMATION_SCHEMA.KEY_COLUMN_USAGE ...

... and later in the code I changed this line ...

sql = "DECLARE @T TABLE ( insert_id " + dr["data_type"] + " ); " + sql;

... to:

if (dr["data_length"] != DBNull.Value)

{
    sql = "DECLARE @T TABLE ( insert_id " + dr["data_type"] + " (" + dr["data_length"] + ") ); " + sql;
}
else
{
    sql = "DECLARE @T TABLE ( insert_id " + dr["data_type"] + " ); " + sql;
}

Now the sql statement works as expected:

exec sp_executesql N'DECLARE @T TABLE ( insert_id nvarchar (50) ); 
INSERT INTO  __languageAssignment  ( keyName, en, de ) OUTPUT INSERTED.keyName as insert_id INTO @T VALUES (  @keyName,  @en,  @de ); 
SELECT insert_id FROM @T',N'@keyName nvarchar(9),@en nvarchar(3),@de nvarchar(2)',@keyName=N'myKeyName',@en=N'Foo',@de=N'Ba'

Regards,

Thilo

allan

Hi Thilo,

Thanks so much for your analysis and fix. This will be included in Editor 1.5.2!

Regards,
Allan