Saturday, February 18, 2012

Devart Error : "Not all parameters bound" while passing parameter in large and complex queries

Problem
In some cases when executing a query via Conext.ExecuteStoreCommand through Devar Edmx provide then it is not able to bind all the instances of parameters in query. For me it happens only when I used large and complex queries with multiple parameters

Sample Code 
string entitySQL = "A Large and somplex ORACLE query";
Devart.Data.Oracle.OracleParameter[] parameters =
{
   new Devart.Data.Oracle.OracleParameter("LANG_CD", language.ToUpper()),
   new Devart.Data.Oracle.OracleParameter("CNTRY_CD", country.ToUpper())
};

ObjectResult<MyEntity> result = this.CurrentDataSource.ExecuteStoreQuery<MyEntity>(entitySQL, parameters);

Root Cause 
Root cause could not be investigated but it seem that it is a problem with Devart Entity Data Provider for Oracle and it not able to parse complex queries efficiently. In my one project it worked all the queries except few that ware quite large involving complex joins of more that 10-15 table.

Solution:
As of now I am not able to identify any amicable solution except hard find replace.

entitySQL = entitySQL.Replace(":LANG_CD",MyLanguage);
entitySQL = entitySQL.Replace(":CNTRY_CD",MyCountry); 

// Do not pass parameter collection.
ObjectResult<MyEntity> result = this.CurrentDataSource.ExecuteStoreQuery<MyEntity>(entitySQL);

Warning
If parameters are being received from external world (like end user) them must validate them against unexpected values. Preferably put a strict length limit on parameters.Otherwise application will be under threat of SQL Injection Attack however depending upon nature of application and parameters defiance strategy may vary check out more details at msdn.

No comments:

Post a Comment