Login Skip Navigation LinksWilsonORMapper > Forums Search
Demo Version Demo Version
Download and try for yourself a fully working demo version, including sample apps and documentation.  The only limitation is that the demo version only works inside the debugger.

PayPal Subscribe
Get It All for $50 USD:
WebPortal, ORMapper,
Source Code, All Updates
PayPal

User Login User Login
Log In
 
 
Reset Password

Wilson ORMapper Forums Wilson ORMapper Forums : Other Databases : PersistChanges fails in VistaDB

Date Post
2/4/2006 6:57:00 AM The PersistChanges method on an object is failing when I used with VistaDB, since the sql query uses the SQL keyword 'EXISTS' which the database doesn't support yet. Has anyone faced this problem? Is there a workaround?
-Vijay
2/4/2006 9:43:56 AM Hi Vijay:

You'll need to send me the details (db file, mappings, code) so that I can troubleshoot it. I actually don't know of any way the PersistChanges method can issue an Exists keyword in the generated sql! Certainly using OPath with relationships will issue an Exists keyword, but I don't know of any way for that to happen from a persistence call since that doesn't execute any OPath selects. So send me what you have so that I can troubleshoot and/or reproduce it.

By the way, the Exists keyword is an Ansi standard keyword, and VistaDB does actually claim to support it! Now I haven't tried to test using it, so maybe they have a bug in their support of it, but it is clearly listed as supported.

Thanks, Paul Wilson
2/6/2006 5:25:17 AM Paul,
Sorry my bad. The PersistChanges doesn't use the EXISTS keyword.
The retrieval queries use it.
VistaDB does not support the 'EXISTS' keyword. Atleast in the latest release v2.1. Its mentioned in their documentation. Here is an excerpt from the help file.

>>
EXISTS predicate is not yet supported. However, if you have a single column, you can use the IN clause. For example:

SELECT O.OrderNo, O.CustNo
FROM Orders O WHERE EXISTS
(SELECT C.CustNo
FROM Customer C
WHERE (C.CustNo = O.CustNo))

can be changed to:

SELECT O.OrderNo, O.CustNo
FROM Orders O WHERE O.CustNo IN
(SELECT C.CustNo
FROM Customer C
WHERE (C.CustNo = O.CustNo))
<<
Its very disappointing. Lets hope they add it in their next release.
-Vijay
2/6/2006 5:48:24 AM I reread their docs and you are correct that they do say they do not yet support it. I simply searched on EXISTS and saw the topic come up with an example -- but I didn't notice they were saying not to use it. :(

This is troubling since EXISTS is an ANSI standard keyword, and it is used for performance reasons which makes the alternative not a very good one. I'm not saying I've never used the alternative myself, as I have, but in general it is a very poor alternative since in a generic library you simply cannot afford to make assumptions about the number of records and how performance might be affected.

So where does this leave you? Options that are available now include (1) creating the suggested alternative yourself since you can use any where clause (use the QueryHelper to help get table and field names) and (2) implementing an IInterceptCommand and modifying the generated sql before its sent to the database (not trivial to do, but possible). You may also be able to avoid the situation entirely once you know VistaDB doesn't support it -- although you know better than I whether that's a viable option for you.

In the meantime, I'll log a request in the VistaDB forums, and we'll see where that goes before I commit to making changes to support non-ANSI sql that is sub-optimal.

Thanks, Paul Wilson
2/6/2006 9:55:28 AM Hi Paul,

I may be having this problem with VistaDb as well. I sent you my test case. I did not know how to determine if the root cause was the use of the EXISTS keyword.

It seems to be a new problem as I rolled-back to ORmapper 2.2 version and PersistChanges worked fine with VistaDb.

Is there a class or method in 4.2 that I could roll back to 2.2 to make it work without the EXISTS functionality? If not, do you know what is the last version before the EXISTS functionality was added?

Thank you for your assistance,

-Ed
2/6/2006 11:26:03 AM First, the other user does have an issue with Exists, but he was mistaken that it had anything to do with PersistChanges. For better or worse, the issues you're experiencing are not related to Exists at all -- but they are real issues nonetheless. First, there is another bug in the latest version of the ORMapper that this helped me find, arising from a lot of refactoring to make things simpler. It didn't affect the mainstream databases, and I did not test VistaDB in this last release -- but it is fixed in the build I'm attaching (others should email me for now if you are using VistaDB).

Next, there are several limitations that VistaDB continues to have, one that I have coded around in this build and others that you'll have to design around. The one that I've handled is that the VistaDB provider does not support BeginTransaction for specific IsolationLevels, which I enabled in this last release. My handling of it will work as long as you let the ORMapper handle transactions, or as long as you only specify ReadCommitted. The other limitations are that VistaDB (or its provider) does not support all of the types -- short and decimal both seem to be problems in the demo for instance. I recall running into these in the past when I tested with VistaDB, and they are a pain, but they are simply limitations of VistaDB, at least at the moment.

Thanks, Paul Wilson
2/7/2006 6:57:09 PM Paul,
VistaDB always uses the snapshot IsolationLevel. Their implementation of BeginTransaction(IsolationLevel level) in the ADO.NET provider throws an exception when the IsolationLevel is set to somethign other than snapshot. I had to comment it out in the source code I received along with VistaDB to make it work with ORMapper.