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 : PostgreSQL

Date Post
6/15/2005 11:41:23 AM Hi All,
Has anybody here had success with the Wilson ORMapper using PostgreSQL 7.3? I'm not entirely clear on how to develop my own custom provider as stated on the ormapper homepage. Can I use the ORHelper to generate my classes/config file which are stored in a PostgreSQL DB?

Thanks,
Larry Morroni
6/15/2005 2:08:41 PM Hi Larry:

Here's the code for PostgreSql:

-- PostgreSql
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}";
Global.Manager = new ObjectSpace(mappingFile, connectString, provider);

Note that you can use a different ADO.NET provider in the first line if you have a different one.

As for the ORHelper, you'll need to setup an OleDb connection if you want to use it. Otherwise you'll need to keep a copy of the schema in MS Sql or Access, or see what you can do with CodeSmith, or develop the mappings by hand.

Thanks, Paul Wilson
6/16/2005 12:42:08 AM Please note that you need to provide correct values for the following new settings if you plan to make use of OPath queries:

TrueLiteral
FalseLiteral
InequalityOperator
ConcatenationOperator
ModulusFunction
LengthFunction
SubstringFunction
TrimFunction
LikeFunction


Here is my best guess for PostgreSQL... based on the 8.0 manual:

provider.TrueLiteral = "TRUE";
provider.FalseLiteral = "FALSE";
provider.InequalityOperator = "<>";
provider.ConcatenationOperator = "||";
provider.ModulusFunction = "({0} % {1})";
provider.LengthFunction = "char_length({0})";
provider.SubstringFunction = "SUBSTR({0}, {1}, {2})";
provider.TrimFunction = "TRIM({0})";
provider.LikeFunction = "({0} LIKE {1})";


Thanks,
Jeff Lanning
6/16/2005 11:25:10 AM Paul/Jeff,

Thanks!

Larry
6/17/2005 9:34:08 AM I decided to try the OLEDB route with the ORHelper. I'm running into some issues. I have PostgreSQL 8.0 installed on my machine and I have my PostgreSQL DB all setup. I am using the following connection string in ORHelper:
Provider=PostgreSQL;Addr=127.0.0.1;Database=STORE_SALEM;
Password="notshown";User ID=postgres;

ORHelper throws and error as follows:
FATAL: invalid command-line arguments for server prcoess
HINT: Try "postgre --help" for more information.

I know I am getting through to the DB because if I make the password incorrect, ORHelper throws an error stating so.


Any ideas?
6/17/2005 11:51:39 AM Have you tried to search in the PostgreSql docs, or google -- there seems to be quite a few cases that come up in google, but I have no way to know which might apply to you. I also see some different parameters for the connection string, like User instead of User Id, or ServerName or Host instead of Addr, but again it probably depends on your driver.

Thanks, Paul Wilson
6/17/2005 12:38:32 PM Yes, I saw all those variations on the OLDEB connection string as well. I also tried as many as I could figure out. It is OK because for now, I just generate da SQL verison of the DB and generated the ORMapper form that.

In regards to the custom provider you provided above...I kept getting an error stating that there was an invalid character at "[" whenever I tried using the Manager. I decided to try:
provider.StartDelimiter = "";
provider.EndDelimiter = "";

So far so good! It seemed to fix the custom provider.

Larry
6/17/2005 12:48:41 PM Using no delimiter will work as long as you don't have any table or field names that cause problems -- which hopefully won't occur if you're designing the database schema yourself. But if you're stuck with someone else's schema and you encounter some problems then you'll need to find out what delimiter works with your provider -- it may be the ansi-standard " or the very common `. By the way, this is exactly why I used this configurable CustomProvider instead of a fixed provider -- there are just too many variations with some of these databases to require a single provider with fixed settings.

Thanks, Paul Wilson
10/20/2005 5:13:06 AM I've been getting the following exception with these settings:
(under Postgres 8.0.4 and its bundled npqsql)

Npgsql.NpgsqlException was unhandled
Message="ERROR: 42601: syntax error at or near \"[\""


I have changed the delimiters to:

provider.StartDelimiter = "[";
provider.EndDelimiter = "]";

which seems to have fixed the problem.
10/20/2005 6:41:36 AM I'm not sure I follow how that's different than the recommended settings listed earlier in this thread -- am I missing something or were you originally using different settings?

Thanks, Paul Wilson
10/20/2005 8:37:32 AM
Sorry, my mistake, what I meant to post was...

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

(as the exception complains about the square bracket characters.)
10/20/2005 9:34:04 AM I know MySql has a lot of configuration options, including delimitors to some extent, so I suspect PostgreSql does too -- and that might be what you're running into. I do know that the docs mention [ and ], and that has worked for me and the others I know about, but this is exactly why I leave this configurable instead of hard-coding it like other mappers tend to do.

Thanks, Paul Wilson
10/25/2005 12:38:11 PM Larry,

Did you happen to get this working? If so, would you be willing to share? :-)

Thanks,

Rich
11/2/2005 6:42:34 AM All,

I was wondering if anyone else has resolved these issues for PostgreSQL.

Thanks,

Rich
11/2/2005 9:35:16 AM Hi Rich:

I'm not sure what you feel needs to be resolved. The whole point is that the delimiters are configurable, and it looks like you found what works on your system. As to why your system isn't configured the same in the first place, that I'm not sure about, but the point is that it doesn't matter. These open source databases tend to get configured in several different ways, and there are of course often multiple providers too -- thus I don't hard-code these things and leave it to you to configure.

Thanks, Paul Wilson
11/3/2005 1:46:24 PM Paul,

I believe there is some confusion on your part. I never worked at all with PostgreSQL. I came into the conversation later and clearly labeled my posts with my name. I was wondering if the user who started this thread worked everything out. Hence, the following I posted earlier:
---------------------------------------------------------
Larry,

Did you happen to get this working? If so, would you be willing to share? :-)

Thanks,

Rich
---------------------------------------------------------


I was just wondering if he would be willing to share his "final" findings as an aggregate.

Rich
11/8/2006 10:39:27 AM

Postgresql uses quotes as delimiters. This works for me:

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