Wilson ORMapper Forums : Other Databases

Postgresql connection issues

Tue, 14 Aug 2007 23:41:48 GMT

Can someone provide me the oledb connection string to connect to Postgresql db?...I tried different combinations but none seems to work.

VistaDb 2.1 working with WilsonORMapper.DotNetV2

Fri, 01 Jun 2007 15:06:36 GMT

In case any VistaDb 2.1 users may happen upon this website, I just tried the latest .NET2.0 ORmapper 4.2.2.0 and it works fine with VistaDb 2.1.

Using:

VistaDB20.dll version 2.1.8.2

Net 2.0 provider VistaDB.Provider.dll version 2.20.26.0

ORmapper 4.2.2.0

Previously I had diffculties using VistaDb and ORM 4.2 under .NET1.1 when mapping BLOB's and GUID's and I ended up rolling back to ORM 2.2. The problem could have originally been a configuration problem on my end in the first place. Anyway, I am happy to report things are well.

Thank you Paul for the great software!

-Ed

Firebird embedded

Sat, 16 Dec 2006 06:53:59 GMT

I'm having problems trying to figure out how to set up a project to use Firebird embedded database. Has anyone dones this before? or can perhaps point me to some resources online?

Many thanks,
Dany.

PostgreSQL

Wed, 08 Nov 2006 10:39:27 GMT

Postgresql uses quotes as delimiters. This works for me:

provider.StartDelimiter = "\"";
provider.EndDelimiter = "\"";

FETCH Support

Wed, 06 Sep 2006 13:05:56 GMT

Hi Paul,

Yeap, that sql puzzle works just fine :P

Thanks a lot !!

Informix

Wed, 06 Sep 2006 12:43:06 GMT

See this wiki entry for how to setup custom providers. I don't have an example for Informix, but you'll see this concept and should be able to get it to work. Alternatively you can use regular Odbc or OleDb, but it of course is always preferable to use a native .NET provider, plus that will allow you to use paging and "identities" if you can configure it appropriately.

Thanks, Paul Wilson

Informix

Wed, 06 Sep 2006 12:38:38 GMT

We have an Informix back end DB that we will need to be able to work with. Will this framework support it through normal ADO operations with ODBC?

thx!

FETCH Support

Wed, 06 Sep 2006 12:38:19 GMT

I like a good sql puzzle -- the following sql should work:

SELECT TOP 1 *
FROM page_Content
WHERE PageId=@PageId
ORDER BY PageVersion DESC

This should be easy to create with the ORMapper using ObjectQuery for the first "page" of size one in a database-independent manner. You can use the QueryHelper to help build the where-clause, but OPath won't work since it doesn't currently support paging (not yet anyhow).

Thanks, Paul Wilson

SQL Server vs. MySQL vs. PostgreSQL (Compatibility)

Wed, 06 Sep 2006 12:10:38 GMT

See this wiki enty about setting up custom providers for MySql, PostgreSql, and others. The ORMapper will generate the database-specific sql for paging and "identities" for the provider you specify, and you get to provide the details for custom providers. By the way, the reason I don't hard-code MySql and the others is because they have multiple possible ADO.NET providers, as well as multiple configurations, not to mention this allows the ORMapper to support more databases than any other. The ORMapper also has a simple QueryHelper that you can use to help build your expressions in a database-independent manner, and OPath gives you more options.

Thanks, Paul Wilson

SQL Server vs. MySQL vs. PostgreSQL (Compatibility)

Wed, 06 Sep 2006 10:11:05 GMT

I am building one web application and using the ORMapper. I want that my application work with several databases (SQL Server,MySQL,PostgreSQL, Access)

This post is to know how WilsonORMapper "work" behind the scenes and if I have to make some precaution about the queries I made using ObjectQuery, OpathQuery ans so on…

For example:

-Using MsSQL provider

int templateId = 1;
string where = "TemplateId=" + templateId;
ObjectQuery<MyTable> query = new ObjectQuery<MyTable>(where, string.Empty, 1, 1);

Using ORMapper I will get:
SELECT TOP 1 templateId FROM [MyTable] WHERE TemplateId=1

-Lets switched the provider to MySQL

The MySQL database dont have "TOP", so SELECT TOP 1 * FROM [MyTable], becomes SELECT * FROM [MyTable] LIMIT 1

Does the WilsonORMapper made (behind the scenes) the switched depending of the Provider I choose ?

Do I have to make some precautions about the queries I made?

Thanks

References:
SQL Server vs. MySQL: Syntax differences, similar GUIs
http://searchtechtarget.techtarget.com/originalContent/0,289142,sid91_gci1207215,00.html

Top 3 differences between PostgreSQL and MS SQL
http://www.petefreitag.com/item/5.cfm

FETCH Support

Wed, 06 Sep 2006 08:56:58 GMT

[First approached] :

DECLARE @maxversion int
SET @maxversion = (SELECT MAX(PageVersion) FROM page_Content WHERE PageId=@PageId)
SELECT * FROM page_Content
WHERE PageId=@PageId AND PageVersion=@maxversion

For example, I can also do some simplification like this :

[Second approached] :

SELECT * FROM page_Content
WHERE PageId=@PageId AND PageVersion=(SELECT MAX(PageVersion) FROM page_Content WHERE PageId=@PageId)

Like I have said I don’t want to use any Stored Procedure !! The main idea of using the ORMapper is to make my aplication work with several databases.

If I made the [First approached] I think I have to build two queries in the ORMapper. One to get the (@maxversion) value and other to pass that result to the SELECT statement.

If I made the [Second approached], and I think is the better one, I don’t know how to reproduce this in the WilsonORMapper. Can I do this using OpathQuery ? I don’t have any idea ?!?!

Thanks and Sorry so many replys

FETCH Support

Wed, 06 Sep 2006 08:20:36 GMT

After some tests I think I have found the soluction ( using your initial tip) it was easy to migrate.

I get this :

[First SP]

DECLARE @maxversion int
SET @maxversion = (SELECT MAX(PageVersion) FROM page_Content WHERE PageId=@PageId)

SELECT * FROM page_Content
WHERE PageId=@PageId AND PageVersion=@maxversion

[Second SP]

(...)

DECLARE @maxPageId int
SET @maxPageId = (SELECT MAX(PageId) FROM page_Content) +1

INSERT INTO page_Content (PageId, PageVersion, bla.. bla..bla
) VALUES (@maxPageId, @PageVersion, bla.. bla..bla

(...)

Question:

Base in your own experience, can you point me the better solution to migrate this to WilsonORMapper ? Using ObjectQuery or OpathQuery ?

For example the first SP, I have to do two separated queries or there is any “tip” to join all in one ?

FETCH Support

Tue, 05 Sep 2006 20:10:19 GMT

Hi Paul,

You are right and thanks for your good support. The main idea is to migrate the only two Stored Procedures that use cursors but I have some dificulties to migrate them.

[First SP]

There is in the page_Content table several pages with the same pageId... the only difference is in each PageVersion they have (we can have several pages with same pageID with diferents PageVersion). The idea here is to get the LastVersion of that page (pageId) but I need to know the MAX(PageVersion) of that particulary page (pageID).

(.....)

DECLARE @maxversion int
DECLARE page_Content CURSOR FOR SELECT MAX(PageVersion) FROM page_Content WHERE PageId=@PageId
OPEN page_Content
FETCH NEXT FROM page_Content INTO @maxversion

IF @@FETCH_STATUS=0
SELECT * FROM page_Content
WHERE PageId=@PageId AND PageVersion=@maxversion
ELSE
SELECT * FROM page_Content
WHERE PageId=-1
GO

[Second SP]

Here when I INSERT one new page, I need to get the last pageId and add 1 value (PageId+1).

(...)

DECLARE max_pageid CURSOR FOR SELECT MAX(PageId) FROM page_Content
OPEN max_pageid
FETCH NEXT FROM max_pageid INTO @PageId
IF @@FETCH_Status=0
SET @PageId=@PageId+1
ELSE
SET @PageId=1

INSERT INTO page_Content (PageId, PageVersion, bla.. bla..bla
) VALUES (@PageId, @PageVersion, bla.. bla..bla

I will avoid all stored procedures because like I have said the main idea of using the ORMapper is to make my aplication work with several databases like SQLServer, MySQL and others. I will also avoid using Stored Procedures :)

Perhaups they are easy to migrate (I am not a DBA)... can you give me some tips to migrate them to simple SQL Statements ?

After migrate them I will delete (all SP) and migrate to ORMapper using ObjectQuery or OpathQuery :P

Thanks again for your support and for your greate tool !!

FETCH Support

Tue, 05 Sep 2006 18:26:06 GMT

Cursors are something that you should always avoid if possible, in any database. So if there is a way to use a regular sql statement then its going to be far better. In your example, it appears that the cursor isn't even necessary since the sql SELECT MAX() will always just return a single row. Maybe there's more you've left out, but if that is it then its not necessary to use a cursor at all in this case, although maybe you need to check for the case where there is no recordset at all. Anyhow, if you must use a cursor, or any stored proc, then the ORMapper does give you ways to work with them, typically with the SelectProcedure object instead of the ObjectQuery object, although you can also use ExecuteCommand if its not going to return anything. But keep in mind that once you go down that path then you're not going to be able to seamlessly support multiple databases, so once again straight sql is better, although for a different reason this time.

Thanks, Paul Wilson

FETCH Support

Tue, 05 Sep 2006 15:21:15 GMT

The SQLServer database has the FETCH Transact.

FETCH
Retrieves a specific row from a Transact-SQL server cursor.

I am using the FETCH Transact in some Stored Procedures, like :

(...)
DECLARE max_pageid CURSOR FOR SELECT MAX(PageId) FROM page_Content
OPEN max_pageid
FETCH NEXT FROM max_pageid INTO @PageId
IF @@FETCH_Status=0
SET @PageId=@PageId+1
ELSE
SET @PageId=1
(...)

The main idea of using the ORMapper is to make my aplication work with several databases like SQLServer, MySQL and others.

Is there any alterantive or option to do that in the ObjectQuery or OpathQuery ?

Thanks

MS Access Error

Wed, 23 Aug 2006 20:26:12 GMT

Google says this may mean a corrupt database file, which is pretty common with Access. It also indicates there are many other possible causes, but the general concensus seems to be that they are due to Access simply not being able to scale / perform well. I haven't experienced this myself, at least not in a very long time, but that's probably because I only use Access for tests and extremely small single-user apps. There's a reason MS has been trying to push MSDE, and now Sql Server Express, and to get rid of Jet.

Thanks, Paul Wilson

MS Access Error

Wed, 23 Aug 2006 20:23:08 GMT

Have you seen this error from MS Access:

'Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.'
Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.

Could it be related to how hard the database is being hit?

WilsonXmlDbClient Example

Mon, 26 Jun 2006 15:26:19 GMT

Hi,

I’m also getting the "XmlDbParameter: Parameter Direction must be Input" exception. Did someone already fixed this problem?

Thanks,

Sander

Oracle output parameters in Stored Procedures

Mon, 22 May 2006 09:26:09 GMT

Hi!
I would like to certify that output parameters can be returned from Oracle stored procedures.

I have tried it but it doesnt seem to work so far and after searching for some time in the forum, I still haven't found a sollution

Thanks in advance,

Stavros

SSH

Tue, 16 May 2006 23:11:39 GMT

I'm wondering if I need to do anything special or if it's possible to use WORM with CoreLab's MySQL .Net Provider connecting Via SSH..

I found a sample on Corelab's site of how to use SSH to connect to a db, but I'm not sure where/how to utilize this with WORM - I generate my code using Welter's templates and CodeSmith.

mySqlConnection.UserId = "dbusername";
mySqlConnection.Password = "dbpassword";
mySqlConnection.Database = "dbname";
mySqlConnection.Port = 3306;
mySqlConnection.Host = "hostname.com";
mySqlConnection.Protocol = MySqlProtocol.Ssh;
mySqlConnection.SshOptions.Host = "hostname.com";
mySqlConnection.SshOptions.Port = 22;
mySqlConnection.SshOptions.User = "sshusername";
mySqlConnection.SshOptions.Password = "sshpassword";

Anybody use WORM with a MySQL SSH connection?

Chad Biggerstaff

VistaDb: How map BLOB's and GUID's?

Fri, 05 May 2006 00:49:04 GMT

I changed my test case to use the older ORMapper 2.2 version and BLOB's and GUID's work with zero code changes. I hate giving up but feel I must. I am stumped on tracing the root cause of the 4.2 exception "Specified cast is not valid." I will miss the defaultNamespace attribute but at least I have a workaround. :-)

-Ed

VistaDb: How map BLOB's and GUID's?

Fri, 05 May 2006 00:48:38 GMT

Are there any VisatDb users out there having success mapping BLOB's and GUID's?

I made a small test case. I have not been able to persist either data type with 4.2, however, with older versions of the ORMapper I had been using BLOB's successfully. One thing I am doing differently, is that I am no longer using the Wilson.ORMapper.IObjectHelper interface. I am using Net 1.0 and the latest VistaDb.

Thanks in advance,

-Ed
deletethisprefix_edsutton99@hotmail.com

Oracle Connection Pooling

Fri, 21 Apr 2006 14:07:04 GMT

Hi Brandon:

The ORMapper uses normal ADO.NET and best practices -- which should (and does in my experience) automatically enable connection pooling, assuming the specific ADO.NET provider supports it (as all major ones do). For the builtin .NET Oracle ADO.NET provider, connection pooling is supported, and is the default, so it should be automatic -- see this MSDN article for more. For the ORMapper and best practices to enable this, that means opening connections as late as possible and closing them as quickly as possible -- this is one best practice that is hard to argue with, and it is what the ORMapper does.

Now here's another spin on what you are seeing. If connection pooling is being used, and if you aren't truly getting simultaneous database hits, then it would make perfect sense that there would only be one actual Oracle connection. On the other hand, if connection pooling is not being used, then its highly unlikely that you would ever see just one actual Oracle connection. So if anything, what you are seeing tends to support that connection pooling is being used, but your load tests may not be enough.

Thanks, Paul Wilson

Oracle Connection Pooling

Fri, 21 Apr 2006 13:51:41 GMT

Hi Paul,

I have searched through the forums and found many threads were you mention connection pooling being handled automatically by the ADO.NET provider. My question is specifically in working with a web application using an Oracle database.

In some preliminary load testing of our web application we are noticing that Oracle only have one session for the aspnet_wp.exe worker process, and this is worrying us as to the performance of the app should a bunch of simultaneous reads or writes start happening and we have users lagging out waiting for the connection to free up....

Could you shed a little light as to the opportunities to implement some formal connection pooling architecture?

Thanks, Brandon

Oracle and SelectProcedure

Sat, 08 Apr 2006 00:24:12 GMT

Thanks -- very useful -- I'll see if I can add this to the main codebase without any issues. Drop me a line if you want your name and/or a link of some type in the code.

Thanks again, Paul Wilson

Oracle and SelectProcedure

Sat, 08 Apr 2006 00:22:40 GMT

Ok, I found a quick workaround/hack to get it to work for me. I'll post it just there's anybody out there as crazy as I am... :-)

After reading this page that microsoft graciously made:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/msdnorsps.asp

I realized all that needed to be done was to add an output parameter of type OracleType.Cursor. But since the IDataParamater.DbType didn't have that type, and I didn't want to change much code, I changed the code in the 'else parameter.Type is null' portion of Wilson.ORMapper.Internals.Connection.GetParameter(...) to this:

if (dbParameter is System.Data.OracleClient.OracleParameter && dbParameter.Direction == ParameterDirection.Output)
{
((System.Data.OracleClient.OracleParameter)dbParameter).OracleType = System.Data.OracleClient.OracleType.Cursor;
}
else
{
dbParameter.DbType = DbType.String;
}

Basically it implies that if you're using oracle, and setting an ouput parameter of an unknown type, use the OracleType.Cursor. Since setting the cursor is all that's necessary for the DataReader to return results, that's all I needed for it to work (besides adding the parameter to the SelectProcedure to specify the name of the cursor variable, which I do only if I'm using Oracle as the provider).

Oracle and SelectProcedure

Fri, 07 Apr 2006 00:35:53 GMT

Nothing has changed -- its on my list to hopefully someday get to, but so far there are very few Oracle users, and even fewer worried about stored procs -- admittedly not a good excuse, but it does drive priorities.

Thanks, Paul Wilson

Oracle and SelectProcedure

Fri, 07 Apr 2006 00:31:54 GMT

I'm starting a new project in Oracle (which I'm very new to) using ORMapper 3.2 (I started using it a while back and we're still using it for other projects).

Everything works perfectly well except calling stored procedures (I'm guessing this is because Oracle requires a cursor output parameter). Is there a way to get this working? Does the new version support SelectProcedures in Oracle?

WORM on MONO?

Mon, 03 Apr 2006 00:44:34 GMT

I still haven't found the time or reason to work with Mono myself, so while the necessary changes may be minor its going to have to be someone else working on this that finds it a priority for them. And please don't take that the wrong way -- its not that I don't think Mono isn't important, its that it simply hasn't been something I've spent any time with, especially since I've moved to .NET v2.0.

Thanks, Paul Wilson

WORM on MONO?

Sun, 02 Apr 2006 19:03:53 GMT

Does this work now? Is there any progress report?

Thank you very much in advance.

Timur Zanagar

Sybase ASA

Tue, 14 Mar 2006 18:35:01 GMT

Yes, it really sucks and I really don't get why Sybase has not implemented named parameters. Anyway, one of the great things about your mapper is that I can easily switch it to use ODBC and I still won't get named parameters but at least I don't have to modify the mapper itself.
Thanks, Dave

Sybase ASA

Tue, 14 Mar 2006 14:59:07 GMT

Hey Dave:

Any chance you can use a different Sybase provider? That's quite a limitation -- whether you're using O/RM or not.

Thanks, Paul Wilson

Sybase ASA

Tue, 14 Mar 2006 10:45:45 GMT

Unfortunately, the Sybase ASA managed provider doesn't support named parameters so what I've been doing is changing the source in CustomProvider.cs:

    internal bool NoNamedParameters {
            get { return (this.parameterPrefix=="~" || (!this.isCustomProvider && (this.providerType == Provider.OleDb || this.providerType == Provider.Odbc))); }
        }

And then setting the ParameterPrefix = "~" when creating the CustomProvider. Hack! It works but of course I forget to make this change whenever a new version comes out.

Is there a better way ?

Thank you
Dave Joyner

DB2 .Net 2.0 Provider

Fri, 10 Mar 2006 18:03:28 GMT

Curious if anyone has been able to get WilsonOR to work with IBM's new .Net 2.0 Provider.

Postgres

Sun, 19 Feb 2006 17:45:40 GMT

Hi Thomas:

The purpose of the delimiters is to automatically handle table and field names that have spaces, reserved words, etc. The ANSI standard for such delimiters is the double-quote, and most databases do support that one way or the other, although it may not be the default, so try setting these to that instead:

provider.StartDelimiter = "\"";
provider.EndDelimiter = "\"";

Its really very hard for me to ever give conclusive instructions on how to setup most databases though, because they are often very configurable, and sometimes the providers change the defaults also. So I give what I have either found to be the default, or what I was able to determined worked for me -- but it may not be the setting of your database install or provider. This is also one reason why I leave this configurable instead of making it hard-coded like many other O/R Mappers have done.

Thanks, Paul Wilson

Postgres

Sun, 19 Feb 2006 15:53:28 GMT

hi paul,

i figured it out, it seemed it was a problem with npgsql, i had an older version in use.

funny stuff is, when i do pass a rather clean provider to the objectspace everything works perfectly (at least for my short test, i dont know how it is when i go deeper)

CustomProvider provider = new CustomProvider("Npgsql", "Npgsql.NpgsqlConnection", "Npgsql.NpgsqlDataAdapter");
ObjectSpace os = new ObjectSpace(mappingFile, connectionString, provider);

works.

however, when i set the properties you suggested in the other postgres-thread, i get an syntax error
ERROR: 42601: syntax error at or near "["

so please tell me, what properties do i have to set exactly to make the customprovider work smoothly for WORM?

by the way, the DRIVER=Postgresql isnt a problem at all.

thank you very much
thomas

Postgres

Sun, 19 Feb 2006 14:47:43 GMT

Hi Thomas:

I don't think the connection string should have the part that specifies "DRIVER={PostgreSQL}". That would be necessary if you were using OleDb, but it should not be when you are using the PostgreSql provider. So my question for you is when you say that connection string has worked, did it work with OleDb or the PostgreSql provider?

Thanks, Paul Wilson

Postgres

Sun, 19 Feb 2006 08:06:09 GMT

hello,

i tried to use postgres and worm but i am not able to connect.
here is a code snippet:

string mappingFile = "mapping.xml";
string connectString = "DRIVER={PostgreSQL};SERVER=localhost;DATABASE=sports;User ID=postgres;Password=bosie;";

CustomProvider provider = new CustomProvider("Npgsql", "Npgsql.NpgsqlConnection", "Npgsql.NpgsqlDataAdapter");
provider.StartDelimiter = "["; provider.EndDelimiter = "]";
provider.ParameterPrefix = ":";
provider.IdentityQuery = "SELECT currval('{1}_{0}_seq')";
provider.SelectPageQuery = "SELECT * LIMIT {0} OFFSET {1}";

ObjectSpace os = new ObjectSpace(mappingFile, connectString, provider);

ObjectSet<Test> _s = os.GetObjectSet<Test>(string.Empty);

it throws an exception that the connectionstring is invalid.
i have used that connectionstring with ado.net succesfully, so how do i setup worm for postgres ?

thank you very much
thomas

VistaDB Mapping.config Generator?

Sat, 18 Feb 2006 14:16:54 GMT

Hi Timur:

You can use an OleDb driver for VistaDb if you can obtain one from them (I think it may be extra but I'm not sure). Otherwise your best bet might be to start with an Access database that you then "upsize" with their wizard to VistaDB. Of course you can also create your mappings by hand -- a lot of that is relatively easy with cut-n-paste.

Thanks, Paul Wilson

VistaDB Mapping.config Generator?

Sat, 18 Feb 2006 10:13:44 GMT

Hi folks,

I use in a new project VistaDB and would like to use WORMs, too, but there is no Generator for a Mapping.config file.

Does somebody know one?

I tried it with Paul Welters CodeSmith Templates but the only one SchemaProvider for VistaDB doesn't work properly.

I hope some of you can help me.

Best regards,

Timur Zanagar

firebird

Fri, 10 Feb 2006 06:24:42 GMT

The current version (v4.2.1) has broken some 3rd party database usage, so check out the next version (v4.2.1.1) when it comes out -- later today hopefully. If that doesn't work then let me know and I'll reinstall Firebird and test it -- I know it used to work, but I haven't tested it myself in the last release.

Thanks, Paul Wilson

firebird

Wed, 08 Feb 2006 12:41:46 GMT

I was trying to run the WilsonORMapperExample2005 using firebird embedded version and having problems.

When I run the winform app it just hangs for few minutes and then throws this error.
"The CLR has been unable to transition from COM context 0x1a3008 to COM context 0x1a3178 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations."

So when I debug the Firebird ADO.NET source, it is throwing an exception in the FbCommand.Prepare method. The exception that I see is
"Attempted to read or write protected memory. This is often an indication that other memory is corrupt."
The same ADO.NET provider works fine when I create a simple .net app without using ORMapper.
Anyone faced this issue before ?

PersistChanges fails in VistaDB

Tue, 07 Feb 2006 18:57:09 GMT

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.

PersistChanges fails in VistaDB

Mon, 06 Feb 2006 11:26:03 GMT

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

PersistChanges fails in VistaDB

Mon, 06 Feb 2006 09:55:28 GMT

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

PersistChanges fails in VistaDB

Mon, 06 Feb 2006 05:48:24 GMT

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

PersistChanges fails in VistaDB

Mon, 06 Feb 2006 05:25:17 GMT

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

PersistChanges fails in VistaDB

Sat, 04 Feb 2006 09:43:56 GMT

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

PersistChanges fails in VistaDB

Sat, 04 Feb 2006 06:57:00 GMT

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

WilsonXmlDbClient Example

Fri, 27 Jan 2006 18:46:57 GMT

By the way, I too am interested in seeing more unit tests. :)

A few people have promised to build some for the mapper in the past, but for some reason they haven't ever came through -- it may be they are just understandably too busy, but it may be that its not as easy as you would hope to unit test generic data access. Now I have seen people with unit tests for their own apps that use the mapper -- that's not too hard, although admittedly I'm still not doing it often enough to make it routine for me thinking yet.

Thanks, Paul Wilson