Page 1 of 1
Can't insert empty PgSqlArray
Posted: Tue 25 May 2010 13:35
by Tiago_G
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
Posted: Tue 25 May 2010 16:58
by StanislavK
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.
Posted: Wed 26 May 2010 15:18
by Tiago_G
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
Posted: Wed 26 May 2010 17:00
by StanislavK
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?
Posted: Wed 26 May 2010 17:31
by Tiago_G
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
Posted: Fri 28 May 2010 16:50
by StanislavK
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.
Posted: Mon 31 May 2010 09:23
by Tiago_G
I will.
Thank you for your help!
Regards,
Tiago
Posted: Thu 29 Jul 2010 13:09
by StanislavK
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/postgr ... nload.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