Can't insert empty PgSqlArray

Can't insert empty PgSqlArray

Postby Tiago_G » Tue 25 May 2010 13:35

Hello,

I'm trying to insert an empty array into a table.
I tried
PgSqlArray a = new PgSqlArray(PgSqlType.Real, 1, 0) and
PgSqlArray a = new PgSqlArray(new object[] {}, PgSqlType.Real, 1, 0)
and both give me a ArgumentOutOfRangeException.

using PgSqlArray a = new PgSqlArray() constructs the object but when i try to insert i get: PgSqlException : "null value in column "e" violates not-null constraint".

I've tried and PostgreSQL itself doesn't seem to have any problem with empty arrays.

Is there any other way to do it?
I'm using 4.90.124

Regards,
Tiago
Tiago_G
 
Posts: 8
Joined: Wed 14 Apr 2010 09:26

Postby StanislavK » Tue 25 May 2010 16:58

We have reproduced the problem with the PgSqlArray constructor. We will investigate it and notify you about the results.

As for inserting an empty array ( new PgSqlArray() ), I was able to perform it with the latest 4.95.140 version of dotConnect for PostgreSQL. If the problem occurs with the latest version, please send us a small test project with which it can be reproduced.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby Tiago_G » Wed 26 May 2010 15:18

Hello,


I tried with the new version and using " new PgSqlArray() " still gives me the same problem on insert (null value in column "e" violates not-null constraint).
Also PgSqlArray has "IsNull" set to "true"

Are you sure it actually inserted an empty array instead of NULL in your test?


Regards,
Tiago
Tiago_G
 
Posts: 8
Joined: Wed 14 Apr 2010 09:26

Postby StanislavK » Wed 26 May 2010 17:00

I've tried the following code and succeeded:
Code: Select all
PgSqlCommand com = new PgSqlCommand("Insert Into \"TestArray\" values (:id, :arr)", connection);
com.Parameters.Add("id", PgSqlType.Int);
com.Parameters.Add("arr", PgSqlType.Array);

PgSqlArray array = new PgSqlArray();
com.Parameters["id"].Value = 10;
com.Parameters["arr"].Value = array;

com.ExecuteNonQuery();

The table is created as
Code: Select all
CREATE TABLE "TestArray"
(
  id integer,
  arr integer[]
)

Please tell us what should be changed in this sample to reproduce the problem. Also, what version of PostgreSQL server are you using?
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby Tiago_G » Wed 26 May 2010 17:31

Hello,

I would suggest trying:

Code: Select all
CREATE TABLE "TestArray"
(
  id integer,
  arr integer[] NOT NULL
)


however, if you still have the table contents, you can just check if it really inserted an empty array or NULL.

I'm using "PostgreSQL 8.4.1, compiled by Visual C++ build 1400, 32-bit"

Thanks you for your help!

Best regards,
Tiago
Tiago_G
 
Posts: 8
Joined: Wed 14 Apr 2010 09:26

Postby StanislavK » Fri 28 May 2010 16:50

You are right: the inserted value was null.

We will fix the problem with the PgSqlArray constructor with zero dimensions, please look forward to the nearest build.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby Tiago_G » Mon 31 May 2010 09:23

I will.

Thank you for your help!

Regards,
Tiago
Tiago_G
 
Posts: 8
Joined: Wed 14 Apr 2010 09:26

Postby StanislavK » Thu 29 Jul 2010 13:09

We've added the functionality for creating and using empty PgSqlArrays. The PgSqlArray class has now the static CreateEmptyArray method which can be used to create an empty array, and the IsEmpty property which specifies whether current array is empty.

The new 4.95.152 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/

For more information on improvements and fixes available in the 4.95.152 version, please refer to
http://www.devart.com/forums/viewtopic.php?t=18591
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for PostgreSQL