DevForce Community Forum : ParameterizedSql in PassthruRdbQuery http://www.ideablade.com/forum/ This is an XML content feed of; DevForce Community Forum : DevForce Classic : ParameterizedSql in PassthruRdbQuery Thu, 11 Jun 2026 01:37:24 -700 Tue, 24 Jun 2008 15:51:30 -700 http://blogs.law.harvard.edu/tech/rss Web Wiz Forums 9.69 360 www.ideablade.com/forum/RSS_post_feed.asp?TID=394 DevForce Community Forum http://www.ideablade.com/forum/forum_images/IdeaBlade_logo_tm.png http://www.ideablade.com/forum/ ParameterizedSql in PassthruRdbQuery : Nevermind, I figured it out. ... http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=3119#3119 Author: jeffdoolittle
Subject: 394
Posted: 24-Jun-2008 at 3:51pm

Nevermind, I figured it out.
]]> Tue, 24 Jun 2008 15:51:30 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=3119#3119 ParameterizedSql in PassthruRdbQuery : Kim,I'm trying to follow... http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=3118#3118 Author: jeffdoolittle
Subject: 394
Posted: 24-Jun-2008 at 3:38pm

Kim,

I'm trying to follow the example above in order to create a parameterized query, but I get a persistence server exception.

When I call this method:

string parmName = key.AdoHelper.FormatParameterName("pc");

it doesn't seem to do anything to the parameter name.  When I inspect the parameterized sql, I see a clause that says "PostalCode=pc" but apparent the the variable "pc" isn't being replaced properly with the parameter value when the query is executed.

The exception detail follows:

IdeaBlade.Persistence.PersistenceServerException : Invalid column name 'pc'.
  ----> System.Data.OleDb.OleDbException : Invalid column name 'pc'.
    IdeaBlade.Persistence.PersistenceManager.HandlePersistenceServerException(Exception pException, Boolean pTryToHandle, PersistenceOperation pOperation)
    IdeaBlade.Persistence.PersistenceManager.XFetchDataSet(IEntityQuery pEntityQuery)
    IdeaBlade.Persistence.PersistenceManager.XFetch(IEntityFinder pEntityFinder, WorkState pWorkState)
    IdeaBlade.Persistence.PersistenceManager.XFetch(IEntityQuery pEntityQuery, QueryStrategy pQueryStrategy, WorkState pWorkState)
    IdeaBlade.Persistence.PersistenceManager.XGetEntities(IEntityQuery pEntityQuery, QueryStrategy pQueryStrategy, WorkState pWorkState)
    IdeaBlade.Persistence.PersistenceManager.GetEntities[T](IEntityQuery pEntityQuery, QueryStrategy pQueryStrategy)
    IdeaBlade.Persistence.PersistenceManager.GetEntities[T](IEntityQuery pEntityQuery)
    C:\Projects\Lotpath\Source\Lotpath.Model\Search\SearchHelper.cs(37,0): at Lotpath.Model.SearchHelper.GetSearchResult()
    C:\Projects\Lotpath\Source\Lotpath.Model\Search\SearchHelper.cs(24,0): at Lotpath.Model.SearchHelper.GetResult(SearchFilter searchFilter)
    C:\Projects\Lotpath\Tests\Lotpath.Model.Tests\SearchHelperTest.cs(125,0): at Lotpath.Model.Tests.SearchHelperTest.GS1AttributeFilteredSearchWithLocationFilterTest()

    IdeaBlade.Persistence.RemotingPersistenceServerProxy.CheckConnection(Exception pException)
    IdeaBlade.Persistence.PersistenceServerProxy.Fetch(SessionBundle pBundle, IEntityQuery pQuery)
    IdeaBlade.Persistence.PersistenceManager.XFetchDataSet(IEntityQuery pEntityQuery)
]]> Tue, 24 Jun 2008 15:38:51 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=3118#3118 ParameterizedSql in PassthruRdbQuery : I think the Passthru query is... http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=1031#1031 Author: kimj
Subject: 394
Posted: 29-Aug-2007 at 3:45pm

I think the Passthru query is the IdeaBlade way of doing what you want here.  The only other alternative would be to write a wrapper stored proc, and then use a StoredProcRdbQuery.  I don't really know what the benefits vs. drawbacks would be for the different approaches, but it really comes down to a matter of choice and which you find fits in best with your application.]]> Wed, 29 Aug 2007 15:45:52 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=1031#1031 ParameterizedSql in PassthruRdbQuery : zahidAccording to the documentation... http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=1030#1030 Author: -ook
Subject: 394
Posted: 29-Aug-2007 at 2:42pm

zahid

According to the documentation the PassThruQuery can be just as efficient as an Entity Query depending on the way you have written the statement. There are disadvantages in disconnected applications. Thanks for the string append method, but I did need the parameters due to SQL Server formatting my datetime variable to the US date format instead of Aussie.

Kimj

Thanks for the example, my parameters are not keys but dates (that I am using in a SQL user defined function in order to keep the same logic between SQL Server Reports and this IdeaBlade application).  However you have given me the correct syntax and advice I need to make it work, again thanks.

Hmmmm ... but maybe there is an IdeaBlade way of doing what I want. Perhaps it should be in a different thread. Below is the SQL Code I'm using in Reporting Services for my reports. I want the same statement in IdeaBlade without having to redo the UDF as a class (because then I have to maintain two different sets of code that do the same thing). Possible or is a Passthru the only option?

SELECT *
FROM vwPallets
WHERE DateTimeStacked between dbo.fnShiftStart(@StartDate,@StartType) AND dbo.fnShiftEnd(@EndDate,@EndType)

The SQL UDF's work out the correct date and times that should be included for a shift range.

Thanks



Edited by -ook - 29-Aug-2007 at 2:42pm]]> Wed, 29 Aug 2007 14:42:16 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=1030#1030 ParameterizedSql in PassthruRdbQuery : Here's one example of how... http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=1025#1025 Author: kimj
Subject: 394
Posted: 29-Aug-2007 at 10:22am

Here's one example of how you can pass parameterized SQL through the PassthruRdbQuery:
 
// We're retrieving the RdbKey here so that the parameter name can be
//formatted correctly. If you already know your data provider and
//parameter format you can skip this step and hardcode the parameter name.
 
RdbKey key = (RdbKey) PM.GetDataSourceKey(typeof(Employee));
string parmName = key.AdoHelper.FormatParameterName("id");
 
// Build select statement
string passThruSelect = string.Format("select * from Employee where id = {0}", parmName);
 
// Build the parameters - be sure to use RdbParameter (esp. if using a BOS)
RdbParameter parm = new RdbParameter(parmName, DbType.Int32);
parm.Value = 1;
 
// Now create a ParameterizedSql instance
ParameterizedSql sql = new ParameterizedSql(passThruSelect, parm);
 
// And finally setup the passthru query and execute it
PassthruRdbQuery passThruQuery = new PassthruRdbQuery(typeof(Employee), sql);
Employee emp = PM.GetEntity<Employee>(passThruQuery);
]]> Wed, 29 Aug 2007 10:22:44 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=1025#1025 ParameterizedSql in PassthruRdbQuery : I am not sure about the performance... http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=1016#1016 Author: owais,zahid
Subject: 394
Posted: 29-Aug-2007 at 5:53am

I am not sure about the performance penalty of PassthruRdbQuery, but i have used in like
 
PassthruRdbQuery aQuery = new PassthruRdbQuery(typeof(Payment),
                "select * from dbo.APPayment as vp where " +
                "vp.IsUnposted = 'False' and vp.VendorId = '" + pvendorId.ToString() + "' and vp.PacketId IN (select Id from dbo.APPacket as ap where " +
                "ap.IsPosted = 'True')");
 
Hope this works for u.
 
-> Can any one tell me about the performance issues with it?
]]> Wed, 29 Aug 2007 05:53:13 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=1016#1016 ParameterizedSql in PassthruRdbQuery : HelloI want to create a simple... http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=1015#1015 Author: -ook
Subject: 394
Posted: 29-Aug-2007 at 12:23am

Hello

I want to create a simple PassthruRdbQuery that contains a few parameters (yes,yes I know DevForce don't necessarily advocate the use of this - but too bad I'm using it!). I want to include some parameters within the SQL Statement. However the supplied documentation and tutorials neglect to show how the ParameterizedSql is created - one simple example in the Help system would have been enough for me.

So, can someone please provide me with an example of a Passthru query with parameters?

Thanks
]]> Wed, 29 Aug 2007 00:23:51 -700 http://www.ideablade.com/forum/forum_posts.asp?TID=394&PID=1015#1015