CODEWITHSUNDEEP
c#odbcamazon-redshift
Kesty
Kesty

Reputation: 630

Redshift ODBC Driver error: String data right truncation on data from data source: String data is too big for the driver's data buffer

I am using the RedShift ODBC driver for .NET to insert rows into a RedShift database table.

The table contains a column called 'email' and defined as VARCHAR(MAX) The rows are all inserted without issue until a row with a non-standard character (e.g. the 3/4 character or a French e with the accent mark) is inserted. At that point I get the following error:

"String data right truncation on data from data source: String data is too big for the driver's data buffer."

Here is the connection string (using string.Format to replace some of the information):

Driver={{Amazon Redshift (x64)}};Server={0};Database={1};UID={2};PWD={3};Port={4};SSL=true;Sslmode=Require;MaxVarchar=1000;UseUnicode=1"

I added the last two parameters in order to see if they would help, but without them it also doesn't work.

Additional info: When I insert the row using a client program like "Aginity", the rows are inserted property, leading me to believe that the problem is not with the table definition.

Here is the insert that works in the client program (the email is intentionally not a legal email):

insert into summary.member values ('59D6687E-078F-45EA-8736-9A08AA85AD4D', 'shrew8@gmail¾comdde')

Here is the table definition:

CREATE TABLE summary.member ( member_id VARCHAR(255) DISTKEY, member_email VARCHAR(255) ) SORTKEY ( member_id, member_email );

Here is the code which opens the connection:

private static OdbcConnection CreateAndOpenOdbcConnection() { const string server = "someserverinfo.redshift.amazonaws.com"; const string port = "5439"; const string masterUsername = "myusername"; const string masterUserPassword = "mypassword"; const string dbName = "databasename";

        var connectionString =
            string.Format(
                "Driver={{Amazon Redshift (x64)}};Server={0};Database={1};UID={2};PWD={3};Port={4};SSL=true;Sslmode=Require;MaxVarchar=1000;UseUnicode=1",
                server, dbName, masterUsername, masterUserPassword, port);
        var odbcConnection = new OdbcConnection(connectionString);
        odbcConnection.Open();
        return odbcConnection;
    }

Here is the code that does the insert:

   private int InsertMember(Guid column1data, string column2data)
    {
        var command = new OdbcCommand(string.Format("INSERT INTO database.table (column1, column2, column3) values(?, ?, ?)"), Connection);
        command.Parameters.AddWithValue("@column1", column1data.ToString());
        command.Parameters.AddWithValue("@column2", column2data);
        command.Parameters.AddWithValue("@column3", "column3data");
        return command.ExecuteNonQuery();
    }

Upvotes: 0

Views: 2553

Answers (2)

jamesd
jamesd

Reputation: 45

Amazon has released an update to their ODBC driver to resolve this issue: http://docs.aws.amazon.com/redshift/latest/mgmt/install-odbc-driver-windows.html

Upvotes: 1

Kesty
Kesty

Reputation: 630

The workaround to this issue is to use the PostgreSQL UNICODE driver instead of the RedShift one. It seems there might be a bug in the RedShift driver which causes this behavior. I've informed AWS support and they are looking into it.

Upvotes: 2

Related Questions