Solved

Prepared Statement Problem

Posted on 2004-10-28
659 Views
Last Modified: 2012-06-27
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.
0
Question by:Guitarman316
    6 Comments
     
    LVL 22

    Expert Comment

    by:earth man2
    What is .Net remoting ?
    0
     
    LVL 22

    Assisted Solution

    by:earth man2
    OK .Net Remoting is DCE/DCOM in $Soft new .net framework.  Need more detail of your implementation to replicate this problem.
    0
     

    Author Comment

    by:Guitarman316
    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.
    0
     
    LVL 9

    Accepted Solution

    by:
    If you think it's a PostgreSQL problem, I'm sure they'd appreciate a bug report.

    Could it be that the driver you're using is creating the prepared statement behind the scenes? Maybe it's a driver issue.
    0
     

    Author Comment

    by:Guitarman316
    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.
    0
     
    LVL 22

    Expert Comment

    by:earth man2
    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.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
    Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
    Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
    This video Micro Tutorial is the second in a two-part series that shows how to create and use custom scanning profiles in Nuance's PaperPort 14.5 (http://www.experts-exchange.com/articles/17490/). But the ability to create custom scanning profiles a…

    934 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now