Problem with string + int parameters

[jp.gouigoux]

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);
}
}
}

[StanislavK]

Please specify versions of dotConnect for Oracle and Oracle DBMS you are using.

I've tested your code using Oracle database versions from 9 to 11 and the latest 5.35.57 build of dotConnect for Oracle. It created the table Devarttestparam, inserted a row into it, selected this row, showed it in a message box and then dropped the table.

I can send you a small project so that you'd be able to show the possible inexactitudes in reproducing the issue.

[jp.gouigoux]

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,