Devart Forums

using - ORACLE 10g Express and Devart 6.50.250.0

I'm using WIN7 x64 on both SQL and developer machine along with VS2010 and .net framework 4.0

CONNECTION STRING:


cmd.Parameters.Add("v1", Devart.Data.Oracle.OracleDbType.Number).Value = 1;
cmd.Parameters.Add("v2", Devart.Data.Oracle.OracleDbType.Number).Value = 2;
cmd.Parameters.Add("v3", Devart.Data.Oracle.OracleDbType.VarChar).Value = "test";

cmd.CommandText = " SELECT :v1 , :v3, :v2 FROM TABLE WHERE ID = 1 "; <<-- THIS SELECT WORKS

cmd.CommandText = " SELECT :v1, :v3, :v1, :v3, :v2 FROM TABLE WHERE ID = 1"; <<-THIS SELECT DOES NOT WORK - it throws
{"ORA-01008: not all variables bound"}

cmd.CommandText = " SELECT :v1 , :v3, :v2 FROM TABLE WHERE ID = 1 "; <<-- THIS SELECT WORKS

cmd.CommandText = " SELECT :v1, :v3, :v1, :v3, :v2 FROM TABLE WHERE ID = 1"; <<-THIS SELECT DOES NOT WORK

I've just upgraded from version 6.0.58.0 to version 6.50.250.0 and most of the application works except for one functionality where now I'm getting this error:"ORA-01008: not all variables bound", see below the code. I have the older version on another machine and the same code is working on that computer. I've debuged both applications and both are having the same generated SQL. I'm using oracle 10g express database. Is there any setting that I have to do ?

CONNECTION STRING:



CODE:
if (DataContext.Connection.State != System.Data.ConnectionState.Open)
DataContext.Connection.Open();

Devart.Data.Oracle.OracleConnection conn = (Devart.Data.Oracle.OracleConnection)((System.Data.EntityClient.EntityConnection)DataContext.Connection).StoreConnection;
LP_PERSON_MP oldPERSON = LoadPERSON(oldDATA_ID, false, false);
decimal PERSONTYPE_ID = GetCurrentPERSONTYPE(oldPERSON).PERSONTYPE_ID;
trans = conn.BeginTransaction();

NewDATA_ID = SQLSequanceManager.GetNextID(typeof(LP_PERSON_MP), conn, trans);
string TMP_FOLDER_NM = string.Format(System.Configuration.ConfigurationManager.AppSettings["TMPPERSONTYPEFOLDERName"], NewDATA_ID);

Devart.Data.Oracle.OracleCommand cmd = new Devart.Data.Oracle.OracleCommand("", conn, trans);

StringBuilder sbCols = new StringBuilder(1000), sbParams = new StringBuilder(100);
Dictionary st = GetCurrentStaticDataDictionaryValues();

foreach (string cname in st.Keys)
sbCols.AppendFormat(cname + ",");

int ParamIndex = 10;
foreach (string cname in st.Keys)
{
sbParams.AppendFormat(" :v{0},", ParamIndex);
cmd.Parameters.Add(string.Format("v{0}", ParamIndex), Devart.Data.Oracle.OracleDbType.Number).Value = st[cname];
ParamIndex++;
}

cmd.Parameters.Add("v1", Devart.Data.Oracle.OracleDbType.Number).Value = NewDATA_ID;
cmd.Parameters.Add("v2", Devart.Data.Oracle.OracleDbType.Number).Value = oldDATA_ID;
cmd.Parameters.Add("v3", Devart.Data.Oracle.OracleDbType.VarChar).Value = NewPERSONName;
cmd.Parameters.Add("v4", Devart.Data.Oracle.OracleDbType.VarChar).Value = authInfo.UserName;
cmd.Parameters.Add("v5", Devart.Data.Oracle.OracleDbType.TimeStamp).Value = DateTime.Now;
cmd.Parameters.Add("v6", Devart.Data.Oracle.OracleDbType.VarChar).Value = TMP_FOLDER_NM;
cmd.Parameters.Add("v7", Devart.Data.Oracle.OracleDbType.Number).Value = (decimal)ePERSONStatus.InProgress;
cmd.Parameters.Add("v8", Devart.Data.Oracle.OracleDbType.Number).Value = PERSONTYPE_ID;

cmd.CommandText = string.Format(@"INSERT INTO MDT.LP_PERSON_MP ( DATA_ID, DG_COLLEAGUE_ID, DG_COMPANY_ID, PERSON_NM, DG_LINE_OF_COVERAGE_ID, ST_STATUS_TYPE_ID, FUTURE_EFFECTIVE_DT, FUTURE_EXPIRATION_DT,
ST_EFF_DATES_CONSISTENT_ID, ST_HIST_DATES_INPUT_TYPE_ID, NO_OF_YEARS_AVAILABLE, NO_OF_YEARS_USED, ST_DRIVERLICENSE_TYPE_ID, RETENTION_AM, DRIVERLICENSE_LIMIT_AM, VALID_DATE_DT, VALID_MONTHS,
ST_VALU_DT_CONSISTENT_ID, INFO_TREND_ASSUMPTION_AM, ST_ALAE_TYPE_ID, ST_HOBBY_TREND_TYPE_ID, ST_HOBBY_ALLOC_TYPE_ID, HOBBY_ALLOC_TYPE_OTHER_TX, ST_HASRENEWAL_YN_ID,
ST_NO_RENEWAL_REASON_ID, ST_GROUNDUPINFO_YN_ID, PROJECTED_HOBBY_AM, COMMENT_TX, REPORT_DT,
{0}
OPTIMISTIC_LOCKING, PERSONTYPE_ID, TMP_FOLDER_NM, TMP_FOLDER_STATUS, AUDIT_INSERT_USER_NAME_TX, AUDIT_INSERT_DT, AUDIT_UPDATE_USER_NAME_TX, AUDIT_UPDATE_DT
)
SELECT :v1, DG_COLLEAGUE_ID, DG_COMPANY_ID, :v3, DG_LINE_OF_COVERAGE_ID, :v7, FUTURE_EFFECTIVE_DT, FUTURE_EXPIRATION_DT, ST_EFF_DATES_CONSISTENT_ID, ST_HIST_DATES_INPUT_TYPE_ID,
NO_OF_YEARS_AVAILABLE, NO_OF_YEARS_USED, ST_DRIVERLICENSE_TYPE_ID, RETENTION_AM, DRIVERLICENSE_LIMIT_AM, VALID_DATE_DT, VALID_MONTHS, ST_VALU_DT_CONSISTENT_ID, INFO_TREND_ASSUMPTION_AM, ST_ALAE_TYPE_ID,
ST_HOBBY_TREND_TYPE_ID, ST_HOBBY_ALLOC_TYPE_ID, HOBBY_ALLOC_TYPE_OTHER_TX, ST_HASRENEWAL_YN_ID, ST_NO_RENEWAL_REASON_ID, ST_GROUNDUPINFO_YN_ID, PROJECTED_HOBBY_AM, COMMENT_TX, REPORT_DT,
{1}
0, :v8, :v6, '.................', :v4, :v5, :v4, :v5
FROM MDT.LP_PERSON_MP
WHERE DATA_ID = :v2 ",
sbCols.ToString(),
sbParams.ToString()
);

cmd.ExecuteNonQuery();

Just installed version 6.50.250.0 and it seems to work

This is the connection string that I use,


This is how I set the connection and the transaction

DataContext.Connection.Open();
Devart.Data.Oracle.OracleConnection conn = (Devart.Data.Oracle.OracleConnection)((System.Data.EntityClient.EntityConnection)DataContext.Connection).StoreConnection;
Devart.Data.Oracle.OracleTransaction trans trans = conn.BeginTransaction();


Devart.Data.Oracle.OracleCommand cmd = new Devart.Data.Oracle.OracleCommand("", conn, trans);

Works with the previous verions - 6.0.58.0, but not with 6.10.141.0

With the latest version - 6.10.141.0
I have this array
DateTime?[] DATEARRAYORA = new DateTime?[NoOfRecords];
Some of the values are null in it.
Than I do this
cmd.Parameters.Add("v3", Devart.Data.Oracle.OracleDbType.TimeStamp).Value = DATEARRAYORA ;

Then cmd.ExecuteArray(NoOfRecords);

And I get this error - please note that this was working fine in the previous version

lid cast from 'System.DateTime' to 'System.Nullable`1[[System.DateTime, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'.
at System.Convert.DefaultToType(IConvertible value, Type targetType, IFormatProvider provider)
at System.DateTime.System.IConvertible.ToType(Type type, IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
at Devart.Data.Oracle.OracleParameter.a(ay& A_0, Boolean A_1, Boolean A_2, OracleCommand A_3, Byte[] A_4, Hashtable A_5, a3 A_6)
at Devart.Data.Oracle.OracleCommand.a(OracleParameterCollection A_0, ay[] A_1, cj A_2, a3 A_3)
at Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, Int32 startRecord, Int32 maxRecords, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Data.Oracle.OracleCommand.ExecuteNonQuery()
at Devart.Data.Oracle.OracleCommand.ExecuteArray(Int32 iters)