Devart Forums

Hi,

I'm testing the .Net connector Devart.data.postgresql 4.95 with postgresql server 8.4

-App server : OpenSUSE 11.2 64bits, Apache 2.2.13, Mono 2.4.3
-DB Server : Centos 5.5,Enterprisedb 8.4 Bêta Advanced Server


code :
bool InTransac = false;
PgSqlConnection _Connection = null;
IDbCommand command = null;
IDbTransaction _CurrentTransaction = null;
string sql = string.Empty;

StreamWriter log = null;

try
{
log = new StreamWriter("log.txt",false);

string _ConnectionString = "User ID=\"xxxx\";";
_ConnectionString += "Password=\"xxxx\";";
_ConnectionString += "Database=xxxx;";
_ConnectionString += "Server=xxx.xx.xx.xxx;";
_ConnectionString += "Port=xxxx;";

log.WriteLine("Open Connection");
_Connection = new PgSqlConnection(_ConnectionString);
_Connection.Charset = "UTF8";
_Connection.Open();

log.WriteLine("BeginTransaction");
_CurrentTransaction = _Connection.BeginTransaction();
InTransac = true;

log.WriteLine("Deleting");
sql = "DELETE FROM INDYNPARAM WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
command.ExecuteNonQuery();

log.WriteLine("Updating");
sql = "UPDATE INDYNPARAM SET numordre = numordre-1 WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
command.ExecuteNonQuery();

log.WriteLine("CommitTransaction");
_CurrentTransaction.Commit();
_CurrentTransaction.Dispose();
_CurrentTransaction = null;
InTransac = false;

log.WriteLine("Close Connection");
if (_Connection != null && _Connection.State != ConnectionState.Closed)
_Connection.Close();

log.WriteLine("FIN");
log.Close();
}
catch (Exception ex)
{
if (InTransac)
{
log.WriteLine("RollbackTransaction");
_CurrentTransaction.Rollback();
_CurrentTransaction.Dispose();
_CurrentTransaction = null;
log.WriteLine("Close Connection");
if (_Connection != null && _Connection.State != ConnectionState.Closed)
_Connection.Close();
}
log.WriteLine(ex.Message);
log.WriteLine(ex.StackTrace);
log.WriteLine("FIN");
log.Close();
}

These exceptions are thrown :

log#1
Open Connection
BeginTransaction
Read failure
at Devart.Data.PostgreSql.v.a (System.Exception A_0) [0x00000]
at Devart.Common.o.e (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000]
at Devart.Common.j.b (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000]
at Devart.Data.PostgreSql.o.d (System.Byte[] A_0, Int32 A_1, Int32 A_2) [0x00000]
at Devart.Data.PostgreSql.o.o () [0x00000]
at Devart.Data.PostgreSql.i.a (Boolean A_0, Boolean A_1, Char A_2, Boolean A_3) [0x00000]
at Devart.Data.PostgreSql.i.c (Boolean A_0, Boolean A_1) [0x00000]
at Devart.Data.PostgreSql.i.b (Boolean A_0) [0x00000]
at Devart.Data.PostgreSql.r.i () [0x00000]
at Devart.Data.PostgreSql.r.e (System.String A_0) [0x00000]
at Devart.Data.PostgreSql.r.c (Devart.Data.PostgreSql.ad A_0) [0x00000]
FIN


log#2 (other app serveur)
Open Connection
BeginTransaction
Transport channel is closed.
à Devart.Common.ab.d(Exception A_0)
à Devart.Data.PostgreSql.v.d(Exception A_0)
à Devart.Common.o.d(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Common.j.a(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Data.PostgreSql.o.a(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Data.PostgreSql.o.k()
à Devart.Data.PostgreSql.r.i()
à Devart.Data.PostgreSql.r.h(String A_0)
à Devart.Data.PostgreSql.r.c(ad A_0)
à Devart.Data.PostgreSql.ad.af()
à Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
à System.Data.Common.DbCommand.ExecuteReader()
à Devart.Common.DbCommandBase.ExecuteNonQuery()
à Devart.Data.PostgreSql.PgSqlTransaction..ctor(PgSqlConnection A_0, IsolationLevel A_1)
à Devart.Data.PostgreSql.ae.a(IsolationLevel A_0)
à Devart.Common.DbConnectionBase.BeginDbTransaction(IsolationLevel isolationLevel)
à System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLevel)
à Devart.Data.PostgreSql.PgSqlConnection.BeginTransaction(IsolationLevel il)
à Devart.Data.PostgreSql.PgSqlConnection.BeginTransaction()
à TestsDevartPosgresql.Program.Main(String[] args)
FIN


Do you reproduce these exceptions ? Is there any Workaround possible ?


Thanks

Apparently, the problem occurs during starting the transaction. The most probable reason of the issue is that there are some problems with connection to PostgreSQL server. Could you please check under debug whether the connection is opened when you are trying to begin the transaction? Also, are you able to perform any simple select query with this connection (without starting a transaction)?

The sample works properly in our environment.

Hi,

(db server EnterpriseDB Advanced Server 8.4 )

I did another test, apparently, the problem occurs during opening the connection, I'm getting this message :

Open Connection
Read less bytes than expected.
à Devart.Data.PostgreSql.ae.ab()
à Devart.Data.PostgreSql.ae.a()
à Devart.Common.DbConnectionFactory.a(DbConnectionBase A_0)
à Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
à Devart.Common.DbConnectionBase.Open()
à Devart.Data.PostgreSql.PgSqlConnection.Open()
à TestsDevartPosgresql.Program.Main(String[] args)
FIN

code :
bool InTransac = false;
PgSqlConnection _Connection = null;
IDbCommand command = null;
IDbTransaction _CurrentTransaction = null;
string sql = string.Empty;

StreamWriter log = null;

try
{
log = new StreamWriter("log.txt",false);

string _ConnectionString = "User ID=\"****\";";
_ConnectionString += "Password=\"****\";";
_ConnectionString += "Database=****;";
_ConnectionString += "Server=****;";
_ConnectionString += "Port=****;";

log.WriteLine("Open Connection");
_Connection = new PgSqlConnection(_ConnectionString);
_Connection.Charset = "UTF8";
_Connection.Open();

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("Deleting");
sql = "DELETE FROM INDYNPARAM WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
command.ExecuteNonQuery();

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("Updating");
sql = "UPDATE INDYNPARAM SET numordre = numordre-1 WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
command.ExecuteNonQuery();

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("BeginTransaction");
_CurrentTransaction = _Connection.BeginTransaction();
InTransac = true;

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("Deleting");
sql = "DELETE FROM INDYNPARAM WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
command.ExecuteNonQuery();

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("Updating");
sql = "UPDATE INDYNPARAM SET numordre = numordre-1 WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
command.ExecuteNonQuery();

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("CommitTransaction");
_CurrentTransaction.Commit();
_CurrentTransaction.Dispose();
_CurrentTransaction = null;
InTransac = false;

log.WriteLine("Connection state :" + _Connection.State.ToString());

log.WriteLine("Close Connection");
if (_Connection != null && _Connection.State != ConnectionState.Closed)
_Connection.Close();

log.WriteLine("FIN");
log.Close();
}
catch (Exception ex)
{
if (InTransac)
{
log.WriteLine("RollbackTransaction");
_CurrentTransaction.Rollback();
_CurrentTransaction.Dispose();
_CurrentTransaction = null;
log.WriteLine("Close Connection");
if (_Connection != null && _Connection.State != ConnectionState.Closed)
_Connection.Close();
}
log.WriteLine(ex.Message);
log.WriteLine(ex.StackTrace);
log.WriteLine("FIN");
log.Close();
}

Thanks for your help

Hi again,

It seems that "Read less bytes than expected" error was due to security problem on pgbouncer

new log after changes on pgbouncer security :

Open Connection
Connection state :Open
Deleting
Transport channel is closed.
à Devart.Common.ab.d(Exception A_0)
à Devart.Data.PostgreSql.v.d(Exception A_0)
à Devart.Common.o.d(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Common.j.a(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Data.PostgreSql.o.a(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Data.PostgreSql.o.k()
à Devart.Data.PostgreSql.r.i()
à Devart.Data.PostgreSql.r.h(String A_0)
à Devart.Data.PostgreSql.r.c(ad A_0)
à Devart.Data.PostgreSql.ad.af()
à Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
à System.Data.Common.DbCommand.ExecuteReader()
à Devart.Common.DbCommandBase.ExecuteNonQuery()
à TestsDevartPosgresql.Program.Main(String[] args)
FIN

Thanks

Could you please check the following:
1) if the same delete command can be executed properly with a standard tool like PgAdmin;
2) if you are getting the same error when executing some simple select statement.

Also, please try setting the UnpreparedExecute property of your PgSqlCommand object to true before executing it and notify us if this helps.

We cannot reproduce the problem in our environment, the above information can help our investigation greatly.

Hi,

1) the same command works fine with Pgadmin or when i'm using EDBDataProvider

2) Select statement works fine

3) I can't set UnpreparedExecute property of this command, the exception is thrown before, on _Connection.BeginTransaction()

new log (after replacing first delete and update command with select command):
Open Connection
Connection state :Open
Select (ExecuteNonQuery)
Connection state :Open
Select (ExecuteReader)
Connection state :Open
BeginTransaction
Transport channel is closed.
à Devart.Data.PostgreSql.v.d(Exception A_0)
à Devart.Common.o.d(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Data.PostgreSql.o.a(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Data.PostgreSql.o.k()
à Devart.Data.PostgreSql.r.i()
à Devart.Data.PostgreSql.r.c(ad A_0)
à Devart.Data.PostgreSql.ad.af()
à Devart.Data.PostgreSql.PgSqlCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
à Devart.Common.DbCommandBase.ExecuteNonQuery()
à Devart.Data.PostgreSql.PgSqlTransaction..ctor(PgSqlConnection A_0, IsolationLevel A_1)
à Devart.Data.PostgreSql.ae.a(IsolationLevel A_0)
à Devart.Data.PostgreSql.PgSqlConnection.BeginTransaction(IsolationLevel il)
à TestsDevartPosgresql.Program.Main(String[] args)
FIN

Hi again

3) I tried to set UpreparedExecute property on delete command without transaction, it works

log.WriteLine("Connection state :" + _Connection.State.ToString());
sql = "DELETE FROM INDYNPARAM WHERE 1=0";
command = new PgSqlCommand(sql, _Connection);
((PgSqlCommand)command).UnpreparedExecute = true;
command.ExecuteNonQuery();
log.WriteLine("Connection state :" + _Connection.State.ToString());

=>
Connection state :Open
Deleting ExecuteNonQuery() UnpreparedExecute = true
Connection state :Open

In general, we recommend using the 2.0 protocol or the UnpreparedExecute mode under Mono. The protocol may be set using the 'Protocol' property of connection string.

Please tell us if using the 2.0 protocol helps.