insert of fixed sized string field fails

insert of fixed sized string field fails

Postby bozr » Tue 20 Jul 2010 16:51

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
 
Posts: 17
Joined: Mon 21 Jun 2010 12:39

Postby bozr » Tue 20 Jul 2010 17:11

Ah, when using DbType.String, everything works fine ...
bozr
 
Posts: 17
Joined: Mon 21 Jun 2010 12:39

Postby StanislavK » Wed 21 Jul 2010 14:52

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;
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby bozr » Wed 21 Jul 2010 16:10

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';
bozr
 
Posts: 17
Joined: Mon 21 Jun 2010 12:39

Postby StanislavK » Thu 22 Jul 2010 16:19

Thank you for your assistance, we've reproduced the problem. We will investigate it and inform you about the results.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Fri 24 Sep 2010 16:46

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/postgresql/download.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
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for PostgreSQL