Guitarman316
asked on
Prepared Statement Problem
Hello, I'm having a problem where PostgreSQL tells that a prepared statement already exists. The trouble is that I don't use prepared statements. Basically I'm developing a 3 tier system. It uses .Net remoting. I keep getting the error Prepared Statement already exists until I shut down the remote component. That makes since seeing as the clear when the session closes.
Why am I getting this error when I'm not using prepared statements, and how can I stop it?
Thanks in advance.
Why am I getting this error when I'm not using prepared statements, and how can I stop it?
Thanks in advance.
earth man2
What is .Net remoting ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, Before I discuss the implementation let me say, I found Friday that if I add something random to the end of my fields like Select *, TimeStamp as Random from table where whatever that the problem mysteriously goes away. It seems as though PostgreSQL is trying to cache the statement. I also found that when I got the error "Prepared Statement 'PRSTMT1182451640138' Already Exists" That if I trapped for it, stripped off everything except the statement name and then tried to deallocate that statement it would say that the statement wasn't found. The versions of PostgreSQL I'm using are 7.4.5 and 8.0 beta2. The problem happens on both of them.
As far as the remoting goes here's basically the way it is set up.
1) There are 3 PC's involved. 1 for client code 1 for remote component (middle tier) and 1 for database server.
2) The remote component is setup as SingleCall using MarshalByRefObject and a TCP channel for performance.
3) In addition to the remote components dll I have a dll that handles security. This is the first place it bombs. If security class is removed it will bomb elsewhere with same issue.
4) The remote component is setup such that each method requires security validation before any work is done. The component is targeted potentially at the internet. For this reason sometimes multiple calls occur where the sql statement looks the same. The remote component uses Core Labs PostgreSQL dataadapter.
5) The remote component is then insantiated from the client and calls to the db occur from PC number 2 with datasets or affected row counts being returned to the client pc.
The only way to clear the problem once it has occured is to physically stop the remote components host and restart it.
If you need any further information please let me know.
Thanks for the help.
As far as the remoting goes here's basically the way it is set up.
1) There are 3 PC's involved. 1 for client code 1 for remote component (middle tier) and 1 for database server.
2) The remote component is setup as SingleCall using MarshalByRefObject and a TCP channel for performance.
3) In addition to the remote components dll I have a dll that handles security. This is the first place it bombs. If security class is removed it will bomb elsewhere with same issue.
4) The remote component is setup such that each method requires security validation before any work is done. The component is targeted potentially at the internet. For this reason sometimes multiple calls occur where the sql statement looks the same. The remote component uses Core Labs PostgreSQL dataadapter.
5) The remote component is then insantiated from the client and calls to the db occur from PC number 2 with datasets or affected row counts being returned to the client pc.
The only way to clear the problem once it has occured is to physically stop the remote components host and restart it.
If you need any further information please let me know.
Thanks for the help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I just recieved an email from Core Labs to try a new driver. It appears that that has fixed the problem. I didn't want to send in a bug report on PostgreSQL until I was sure that it was the problem. I know I'm not to fond of unfounded bug reports. Also I tried to duplicate it this morning with PgAdmin3. I ran the exact same statement over and over and never got the error.
Thanks for the help.
Thanks for the help.
My gut feeling is it must be a Core Labs Issue.
What is the content of your postgres database log file. If it is a database problem the log file should show the error.
What is the content of your postgres database log file. If it is a database problem the log file should show the error.