Devart Forums

I'm trying to store a long string (more than 4000 chars) into a LONG type field in an oracle table.

I precise you that we store special chars with accents. (é, ç, etc...), so we need set the unicode property value to True on the object connection.

When I insert a string of 2 chars on the LONG field, I retrieve a string of 4 chars.

For example, the insertion of "AA" gives on return "A\0A\0"

If the unicode property is set to False, the insertion works fine.

With special chars, the length of really inserted informations are not necessarely a multiple of 2...

The problem is on Linux and Windows environnements.

Thanks in advance for your help

I am a current customer of Devart Oracle and finalizing my tests on Devart PostGreSQL. I am going to buy the licence, and wonder how I am going to create an App_licence.dll with both providers.

I noticed that both versions are on the same Devart.Data.dll version, namely 5.0.17, so this should not prevent installing both providers on the same machine. But how am I going to create the licence for both at the same time ? Is it possible ? Do I have to create a licenses.lics with both lines ?

Thanks in advance for your help

Thanks for your reply. I am downloading the 5.35.57 version and will tell you if it works here. Hopefully, this is a former bug that was solver in this new version.

Cheers,

We have reproduced a problem with the Devart connector when accesing an Oracle database, and sending a parameterized request, in which we have a string parameter first and a second integer parameter. This results not in an exception, but the reader sends no data.

Please find attached a sample code to reproduce. Thanks in advance for you help.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace TestApp
{
public partial class TestForm : Form
{
string _Server = "172.16.1.33";
string _Sid = "PROGOSF";
int _Port = 1521;
string _UserId = "TEST";
string _Password = "TEST";

string _CarBind = ":";
string _DefualtStringPram = "TEST";
int _DefaultIntParam = 1;

public TestForm()
{
InitializeComponent();

this.txb_stringParam.Text = _DefualtStringPram;
this.txb_intParam.Text = _DefaultIntParam.ToString();
}

private void bt_Executer_Click(object sender, EventArgs e)
{
IDbConnection conn = null;
IDataReader reader = null;

if (ckb_DirectConnection.Checked)
{
// Create Dervat direct connection
Devart.Data.Oracle.OracleConnection ConnectionDevart = new Devart.Data.Oracle.OracleConnection();
ConnectionDevart.Unicode = true;
ConnectionDevart.Server = _Server;
ConnectionDevart.Sid = _Sid;
ConnectionDevart.Port = _Port;
ConnectionDevart.UserId = _UserId;
ConnectionDevart.Password = _Password;
ConnectionDevart.Direct = true;

conn = ConnectionDevart;
}
else
// Create basic Oracle connetion
conn = new System.Data.OracleClient.OracleConnection("Persist Security Info=False;User ID=\"" + _UserId + "\";Password=\"" + _Password + "\";Data Source=\"" + _Sid+ "\";");

try
{
conn.Open();

// Create the table DEVARTTESTPARAM
string sql = "CREATE TABLE DEVARTTESTPARAM(idtable VARCHAR2(32) NOT NULL PRIMARY KEY, intparam NUMBER(2) DEFAULT 0, stringparam VARCHAR2(32))";

reader = ExecuteRequest(conn, sql, new Dictionary());
if (reader != null) reader.Close();

// Insert a new row with an integer and string value
sql = "INSERT INTO DEVARTTESTPARAM VALUES ('id1', " + _CarBind + "intparam, " + _CarBind + "stringparam)";

Dictionary listParam = new Dictionary(2);
listParam.Add("intparam", int.Parse(this.txb_intParam.Text));
listParam.Add("stringparam", this.txb_stringParam.Text);

reader = ExecuteRequest(conn, sql, listParam);
if (reader != null) reader.Close();

// Test existance of insered row
sql = "SELECT *";
sql += " FROM DEVARTTESTPARAM";
sql += " WHERE stringparam=" + _CarBind + "stringparam";
sql += " AND intparam=" + _CarBind + "intparam";

reader = ExecuteRequest(conn, sql, listParam);

if (reader.Read())
{
object[] listFields = new object[reader.FieldCount];
reader.GetValues(listFields);
StringBuilder message = new StringBuilder("Yes. List Values : ");
foreach (object field in listFields)
message.Append(field.ToString() + "; ");

MessageBox.Show(message.ToString());
}
else
MessageBox.Show("No row returned !!!");

reader.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.Message);
}
finally
{
try { reader = ExecuteRequest(conn, "DROP TABLE DEVARTTESTPARAM", new Dictionary()); }
catch (Exception ex) { MessageBox.Show("Error : " + ex.Message); }
if (reader != null && !reader.IsClosed) reader.Close();
if (conn != null && conn.State != ConnectionState.Closed) conn.Close();
}
}

private IDataReader ExecuteRequest(IDbConnection iConn, string iSql, Dictionary ilistSqlParams)
{

bool isDirectConn = (iConn is Devart.Data.Oracle.OracleConnection);

if (iConn.State != ConnectionState.Open) iConn.Open();

IDbCommand command = isDirectConn ? (IDbCommand)new Devart.Data.Oracle.OracleCommand(iSql, (Devart.Data.Oracle.OracleConnection)iConn) : (IDbCommand)new System.Data.OracleClient.OracleCommand(iSql, (System.Data.OracleClient.OracleConnection)iConn);

command.CommandType = CommandType.Text;
command.CommandTimeout = 90;

foreach (KeyValuePair param in ilistSqlParams)
command.Parameters.Add(isDirectConn ? (IDataParameter)new Devart.Data.Oracle.OracleParameter(param.Key, param.Value) : (IDataParameter)new System.Data.OracleClient.OracleParameter(param.Key, param.Value));

CommandBehavior tmp = CommandBehavior.Default;
return command.ExecuteReader(tmp);
}
}
}

One last thing : trying with PGBouncer works fine, so all the problems described here happen only with the conjunction of Devart PGSQL provider and PGPool.

Using the native NPGSQL with PGPool works fine (except of course for the low stability of the provider).

Using the Devart provider with PGBouncer or without pooling system works fine.

The CONNECTION FAILURE seems to come from the remanent connections. If I wait for a few minutes before launching the benchmark again, I do not get the problem. So, let us forget it for now.

As for the transaction problem, I have been looking for the configuration in PGPOOL to see if it could come from there, but with no success for now. Did you notice anything that could sort the problem in the traces I sent you ?

Thanks in advance

Here are the results of my benchmarks :

First, the CONNECTION FAILURE only appears from time to time. It looks like there is already an entry in the forum for this. It is dated back to 2007, but apparently not solved... I have the same behaviour, though, meaning that if I wait a few minutes, it goes back again. I am going to investigate further in PGPOOL, since it may be this component that is the cause of the trouble.

Second, and more annoying : impossible to start a transaction when PGPOOL is active. The following code works fine :

string connStr = "User ID=\"testcomplete\";Password=\"*******\";Database=postgresql_test_jpg;Server=172.16.1.112;Port=9999;";

PgSqlConnection pgConnection = new PgSqlConnection(connStr);
pgConnection.Charset = "UTF8";
pgConnection.Open();
PgSqlCommand pgCommand = null;

pgCommand = new PgSqlCommand("SELECT nomtab, nomcol FROM INCOLTAB_TEST", pgConnection);
pgCommand.UnpreparedExecute = true; // Sinon, on part en timeout quand on est en mode PGPOOL !!!
IDataReader Reader = pgCommand.ExecuteReader(CommandBehavior.CloseConnection);
if (Reader.Read())
Console.WriteLine("Value : " + Reader.GetString(0));
Reader.Close();
Reader.Dispose();

So, reading values is not a problem. But the following program raises an exception on the line that creates the transaction :

string connStr = "User ID=\"testcomplete\";Password=\"*******\";Database=postgresql_test_jpg;Server=172.16.1.112;Port=9999;";

PgSqlConnection pgConnection = new PgSqlConnection(connStr);
pgConnection.Charset = "UTF8";
pgConnection.Open();
PgSqlCommand pgCommand = null;

PgSqlTransaction myTrans = pgConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
try
{
pgCommand = new PgSqlCommand();
pgCommand.UnpreparedExecute = true; // Sinon, on part en timeout quand on est en mode PGPOOL !!!
pgCommand.Transaction = myTrans;
pgCommand.Connection = pgConnection;
pgCommand.CommandText = "DELETE FROM INCOLTAB_TEST WHERE nomtab='truc'";
pgCommand.ExecuteNonQuery();

pgCommand = new PgSqlCommand();
pgCommand.UnpreparedExecute = true; // Sinon, on part en timeout quand on est en mode PGPOOL !!!
pgCommand.Transaction = myTrans;
pgCommand.Connection = pgConnection;
pgCommand.CommandText = "INSERT INTO INCOLTAB_TEST (nomtab, nomcol) VALUES ('truc', 'truc')";
pgCommand.ExecuteNonQuery();

myTrans.Commit();
Console.WriteLine("Record is written to database.");
}
catch (Exception e)
{
myTrans.Rollback();
Console.WriteLine(e.ToString());
Console.WriteLine("Neither record was written to database.");
}
finally
{
pgConnection.Close();
pgConnection.Dispose();
pgConnection = null;
}

The detailed exception is as follows :

Exception non gérée : Devart.Data.PostgreSql.PgSqlException: Impossible de lire
les données de la connexion de transport : Une tentative de connexion a échoué c
ar le parti connecté n'a pas répondu convenablement au-delà d'une certaine durée
ou une connexion établie a échoué car l'hôte de connexion n'a pas répondu. --->
System.IO.IOException: Impossible de lire les données de la connexion de transp
ort : Une tentative de connexion a échoué car le parti connecté n'a pas répondu
convenablement au-delà d'une certaine durée ou une connexion établie a échoué ca
r l'hôte de connexion n'a pas répondu. ---> System.Net.Sockets.SocketException:
Une tentative de connexion a échoué car le parti connecté n'a pas répondu conven
ablement au-delà d'une certaine durée ou une connexion établie a échoué car l'hô
te de connexion n'a pas répondu
à System.Net.Sockets.Socket.Receive(Byte[] buffer, Int32 offset, Int32 size,
SocketFlags socketFlags)
à System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 si
ze)
--- Fin de la trace de la pile d'exception interne ---
à System.Net.Sockets.NetworkStream.Read(Byte[] buffer, Int32 offset, Int32 si
ze)
à Devart.Common.ag.a(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Common.f.c(Byte[] A_0, Int32 A_1, Int32 A_2)
à Devart.Common.q.e(Byte[] A_0, Int32 A_1, Int32 A_2)
--- Fin de la trace de la pile d'exception interne ---
à Devart.Data.PostgreSql.PgSqlDataReader.e(Int32 A_0)
à Devart.Data.PostgreSql.PgSqlCommand.InternalExecute(CommandBehavior behavio
r, IDisposable stmt, Int32 startRecord, Int32 maxRecords)
à Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
à System.Data.Common.DbCommand.ExecuteReader()
à Devart.Common.DbCommandBase.ExecuteNonQuery()
à Devart.Data.PostgreSql.PgSqlTransaction..ctor(PgSqlConnection A_0, Isolatio
nLevel A_1)
à Devart.Data.PostgreSql.ae.a(IsolationLevel A_0)
à Devart.Common.DbConnectionBase.BeginDbTransaction(IsolationLevel isolationL
evel)
à System.Data.Common.DbConnection.BeginTransaction(IsolationLevel isolationLe
vel)
à Devart.Data.PostgreSql.PgSqlConnection.BeginTransaction(IsolationLevel il)
à ConsoleTestPgDirect.Program.TestDevart() dans E:\Projets\PostGreSQL\Console
TestPgDirect\Program.cs:ligne 272
à ConsoleTestPgDirect.Program.Main(String[] args) dans E:\Projets\PostGreSQL\
ConsoleTestPgDirect\Program.cs:ligne 31

I am using the latest beta 4.65. Even if the correction takes a lot of time, can you please provide me as sopn as you can with some visibility on the possible dates of correction ? And again, do not hesitate to ask me and test some new betas, as this provider is important for us.

Thanks in advance for your help.

Can you please provide some information

Also, I get the CONNECTION FAILURE from the first time I launch my tests after a restart of pgpool and edb when I add pooling=false to the connection string.

I have tried the beta here, and it works better than the stable version I used previously : I do not have the exception I had previously.

Two initial remarks, though :
- I have an apparent performance problem in some cases with the pool.
- When I launch my benchmarks two times in a row, I receive a CONNECTION FAILURE exception on the second try.

In both cases, I will finish my benchmarks first, to see if it comes from the provider or the pgpool configuration, and will keep you updated in the next day or so.

Thanks for this first correction, that was extremely quick !

I just tried the code you gave us, simply copying and pasting it in a console utility. I only changed the connection string to "User ID=\"testcomplete\";Password=\"********\";Database=postgresql_test_jpg;Server=172.16.1.112;Port=9999;" in order to match my settings, and the only other change I made was to the SQL INSERT command to put it on an existing table.

When running, I obtain the following exception on pgConnection.Open() :

Exception non gérée : Devart.Data.PostgreSql.PgSqlException: Unexpected server r
esponse.
à Devart.Data.PostgreSql.ac.ac()
à Devart.Data.PostgreSql.ac.a()
à Devart.Common.DbConnectionFactory.a(DbConnectionBase A_0)
à Devart.Common.DbConnectionClosed.Open(DbConnectionBase outerConnection)
à Devart.Common.DbConnectionBase.Open()
à Devart.Data.PostgreSql.PgSqlConnection.Open()
à ConsoleTestPgDirect.Program.TestDevart() dans E:\Projets\PostGreSQL\Console
TestPgDirect\Program.cs:ligne 117
à ConsoleTestPgDirect.Program.Main(String[] args) dans E:\Projets\PostGreSQL\
ConsoleTestPgDirect\Program.cs:ligne 31

My bench is as follows :
- PostGreSQL : EnterpriseDB Advanced Server 8.3 R2 (830 106)
- Linux : CentOS 5.4 x32
- Kernel : 2.6.18-164
- PGPool-II : 2.2.5

If I add "Pooling=false;" at the end of the connection string, no change : I get the same exception. If I change the port to 5444 (which is my database without PGPOOL), it works fine.

Please tell me if you need traces or anything else that can help...

Having the same pooling problem here. I tried the solution, but Devart.Data.PostgreSql.PgSqlConnection does not contain any Pooling field or property. I am using the latest version, downloaded a few weeks ago (I am at the end of my trial period, and after buying the Oracle version, we are in the process of validating before buying DotConnect for PostGreSQL).

OK. Thanks for that clarification.

As far as I understand the last part of your message, I think we comply with the licence : we distribute an application that is twofold : a Winforms client (that has not DB connectivity) + a set of ASP.NET web services. This second part of the application is the one that calls the DB. It is composed of several libraries, one of which is called AGLNETDB : this is a library that we use to unify our calls to the different DB (Oracle, SQLServer, ...), and also to ease the job of our developers (he can just call ExecSQL, without bothering about obtaining a connection, opening it, closing, etc.)

This library is only used by us and never directly by our customers. In fact, they do not even use the web services directly, they always do so by using the client we provide them with as part of the solution. I wait for your confirmation on this, but I trust this is OK for the licence.

Now for the debugging part : when server-side developers debug the application, they use a Visual Studio solution with the different libraries used, and in particular this AGLNETDB. If I understand correctly your message, the developer who will have the corresponding project open because he is in fact debugging this particular library, or at least thinking the bug may be in it (and thus need to recompile it to activate the debugger) will need the licence. That is OK. But what about the developer who is debugging another library of our server, that uses a compiled AGLNETDB.dll and its corresponding .pdb ? He is going to compile against the AGLNETDB.dll, but since the licence is not in the AGLNETDB.dll, but in the App_Licence.dll, will he be able to go on and debug ?

That's quite complicated to explain by message. If you need more information, can you please give me a phone number where I can contact you ?

Thanks in advance

The DLL is distributed to our customers. The developers never use it directly, though : the DLL library is a wrapper to all their connections to the database. Since the licence is in the EXE, does this mean that only the developers debugging the library and building the EXE in the same time will need the licence ? What about the developers that will start the EXE containing the licence, but not compile it during the debug ?

Yes, the EXE references the library DLL and the library DLL references Devart.