insert of fixed sized string field fails

[bozr]

Hello,

I'm trying to insert a fixed sized string field into postgres 8.4.4 using dotConnect 4.95.146. In my case the field in the database is of type char(6).
I'm using a function (stored procedure) to insert the data. This function is called by the .net code using a parameter of type DbType.StringFixedLength.

When I invoke the function in postgres directly, the data is inserted in the table as expected:

Code: Select all

SELECT fn_InsertLanguage('deu_DE');

However, when I use the dotConnect connector, only the first character is inserted. So in the case described above only 'd' is passed to the function.

Any help would be greatly appreciated.

P.S. I'm using a protocol v2 connection.

[edit] Even when I set the parameter.Size to 6 manually, I still get the same result.

[bozr]

Ah, when using DbType.String, everything works fine ...

[StanislavK]

Could you please specify the exact code and database objects you are using or send us a small test project? I've tried to reproduce the problem with the following sample and failed:

Code: Select all

PgSqlConnection conn = new PgSqlConnection(connectionString);
conn.Open();

PgSqlCommand com = new PgSqlCommand("SELECT char_table_insert(:ch)", conn);
com.Parameters.Add(new PgSqlParameter("ch", PgSqlType.Char, 6));
com.Parameters[0].Value = "deu_DE";
com.ExecuteNonQuery();

The table and function are defined as follows:

Code: Select all

CREATE TABLE "charTable"
(
  ch character(6)
);

CREATE OR REPLACE FUNCTION char_table_insert(ch character)
  RETURNS integer AS
$BODY$
BEGIN
    INSERT INTO "charTable" VALUES(ch);
    RETURN 0;
END
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

[bozr]

Thanks for the quick reply!

The following code sample shows the issue:

Code: Select all

DbProviderFactory providerFactory = DbProviderFactories.GetFactory("Devart.Data.PostgreSql");
DbConnection connection = providerFactory.CreateConnection();
connection.ConnectionString = "Host=localhost;Database=dummy;User Id=me;Protocol=2;Unicode=true;Password=secret";
connection.Open();

DbCommand command = connection.CreateCommand();
command.CommandText = "char_table_insert";
command.CommandType = CommandType.StoredProcedure;

DbParameter parameter = command.CreateParameter();
parameter.ParameterName = "ch";
parameter.DbType = DbType.StringFixedLength; // this only inserts "a" into the table, not the expected "abcdef"
parameter.Size = 6;                          // setting the size doesn't seem to have any effect
//parameter.DbType = DbType.String;          // using this line istead of the above two, resolves the issue
parameter.Value = "abcdef";
command.Parameters.Add(parameter);

int result = (int)command.ExecuteScalar();

And this is the postgres table and function I'm using:

Code: Select all

CREATE TABLE char_table
(
  ch char(6) PRIMARY KEY
);

CREATE OR REPLACE FUNCTION char_table_insert(ch char(6))
  RETURNS integer AS
$$
BEGIN
    INSERT INTO char_table VALUES(ch);
    RETURN 0;
END
$$
LANGUAGE 'plpgsql';

[StanislavK]

Thank you for your assistance, we've reproduced the problem. We will investigate it and inform you about the results.

[StanislavK]

The problem is fixed, the fix is available in the new 4.95.170 build of dotConnect for PostgreSQL. The build can be downloaded from
http://www.devart.com/dotconnect/postgr ... nload.html
(the trial version) or from Registered Users' Area (provided that you have an active subscription):
http://secure.devart.com/

The detailed information about the fixes and improvements implemented in dotConnect for PostgreSQL 4.95.170 is available at
http://www.devart.com/forums/viewtopic.php?t=19070