How do I get at the sql statement that caused an SQLException using the Postgres JDBC driver in Java?

Background

In my current project - a server product with no GUI front-end, I'm trying to write in better error handling support. Errors currently are outputted to the logs and are typically not read by users.

We use PostgreSQL as our database backend and we access it using direct JDBC calls and DAOs via a database pooler. Most database related exceptions are wrapped in a generic DatabaseException class that implements RuntimeException and attempts to pull out debugging and state information from the exception it was passed. In our particular case, it will access the underlying PostgreSQL database driver - PSQLException. So far this approach has worked well for getting more verbose information about what caused the database error, with the notable exception described below.

Furthermore, since we have very specific performance and legacy support requirements we have a lot of custom SQL magic that makes the following the stack trace back a bit more time intensive but not impossible or difficult.

Problem Described

I have noticed that when we get a SQLException as a result of a faulty SQL statement, the driver's implementation does not return the SQL statement that caused the error. After doing a little searching, I found out that there is a way to drop the PostgreSQL driver into a debug mode on startup and have it display properties about its internal query. However, it is undesirable for us to run the driver in debug mode in our production environment (and honestly I haven't been able to figure out how to get it into the freakin mode!).

Question

Has anyone else dealt with this same issue before and found a solution? If not, is there some OOP pattern out there for storing query information before execution and then assigning that information to the exception thrown? Or do most developers just feel that they don't need the full query to troubleshoot database issues? Honestly, I don't need it because I have the full stack trace and I can look up the invoking query, but it definitely speeds up my debugging by having it be the first thing that I see in the error logs.

From stackoverflow

• The stacktrace can point you to the DAO method that caused the problem, isn't that the case?

  Edit after comment: If your SQL query is complex and dynamically generated from previous parts of the code then you could log (level TRACE or DEBUG) these statements before executing them. In the logging configuration you could enable logs only for the DAO(s).

  Elijah : For the case of DAO methods that approach works fairly well. However, in the case of some of our more complex SQL statements invoked via JDBC it is a little more complicated because we get a trace to the execute method and we then have to work are we back to where the statement/prepare was defined.

• I am assuming that when you make the call to execute the query you have the statement, and you receive the Exception, so at that point you have both. It seems like you could do your analysis there.

  However, maybe you're catching things further up. So, what you might do is on your own custom subclass of Exception, DatabaseException, add a triggeringSQLStatement member with a getter and setter, and then at the place where you attempt to execute the statement, catch the original Exception from PostgreSQL, create a new DatabaseException, set the triggeringSQLStatement to be the statement you just executed, and call initCause() on the DatabaseException to set the Exception caught from PostgreSQL as the cause of your exception; then throw your DatabaseException and the calling code which catches it will have an object that prints out a very decent stack trace of what happened plus provides access to the SQL statement that caused the problem. For more information on this approach, you might want to research Java Exception chaining. Even if you don't use all of what I just described, I think you should definitely be using Java Exception chaining already.

  If there's not a spot anywhere in the code where you have access to both the SQL statement that caused the problem and the Exception that gets thrown, I'd be very curious as to why, and how that is possible. And I'd suggest you redesign your code so that you do have such a spot.

  Edit: Since you're wanting to see the SQL statement first thing in the log, you could probably also override your DatabaseException's toString() method (or other appropriate methods; I'm not sure what gets called when an Exception is printed out) to print out the included SQL statement, assuming you included it as I described above.

• I used to add the SQL query in my custome Exception object when ever there is an SQLException. In the code where ever I am logging the exception details in log file, I used to log the SQL also.

  Adeel Ansari : Its better to log the parameters as well. +1

• Why not add a file logger around all JDBC calls (log4j)?

  Whenever you make a SQL call you log the SQL and how long it took to execute. Simple stuff.

  We do this for any call to an external system E.g. SOAP calls, RMI, Corba, etc. Its proved invaluable almost every day and if it affects performance.. I haven't noticed!

  If you have security concerns i.e. don't want it to go to a log file on a client machine, you could use a SocketAppender and send it to a remote machine for centralised logging purposes. This won't be totally secure but would stop the casual snooper.

• The easiest way to do this I think is to use a third party product like p6spy. It gets in between your jdbc driver and your database and reports the exact queries that are run. It's very easy to run on demand as it's implemented as another JDBC driver that will delegate to your actual JDBC driver. Very powerful tool that I can't imagine working without.

  http://www.p6spy.com/

• Another solution to spy on the queries you are executing is pgFouine which analyses and reports on the logs generated by Postgres

Is managed code is the only way to use SMO in c++???

I have to work with SQLSERVER SMO in C++. Is Managed code is the only way to work with SQLSERVER SMO in C++ ?

I have tried many ways.. but I found that using managed code is the only option. Are there other ways?

From stackoverflow

• Technically, you can expose .NET assemblies to standard COM infrastructure, but that requires changes to the source code (see this). You may try and create a COM-aware wrappers around standard SMO objects and then use them as regular COM classes in C++.

Serializing linq entities with WCF

I am serialzing a linq object through WCF. The dbml is setup for unidirectional serialization.

My objects are pretty simple: Budget has a collection of BudgetLineItems. Each BudgetLineItem has an ItemCateogry.

Budget/BudgetLineItems are serialized fine. ItemCateogry on each BudgetLineItem do not, however. I noticed by default, linq didn't add a [DataMember] on ItemCategory for each BudgetLineItem. I added it manually, and also removed any possible circular reference on the ItemCategory entity with [IgnoreDataMember]. Not luck, however.

questions:

1. Can wcf, by default, serialize many-to-one relationships, or am I just missing something? I know the serialized data would be rather redudant with duplicated ItemCategory data for each BudgetLineItem, but that is fine.

2. Do I need to do a custom DataContractSerializer for this?

** EDIT ** Actually, that did work (adding [DataMember]), I just didn't update the service reference on the client (duh).

NEW QUESTION: Is there a way to tell the linqtosql designer to maintain those [DataMember] and [IgnoreDataMember] fields on an entity that the designer is generating? otherwise I'll need to update them everytime I save the dbml.

From stackoverflow

• you shouldn't send linq entities with WCF. Instead make your own business datamodel and send those over the wire. If you ever change your database you don't want to change every program that connects with your service

  ericvg : agreed, but for the purpose of this particlar item, we're trading that off.

• Wow you successfully serialized your LINQ 2 SQL objects for WCF?

  When I tried this (very reluctantly, see below as to why) it really fell over, there were relationships in my L2S entity pointing to children, and then the child pointing back to the parent and obviously when enumerating for building the WCF object, it could not traverse the tree infinitely in this fashion and [to the best I remember] it resulted in an overflow exception, so what I am basically saying is beware if you go down this road!

  Following up on Michael's post, I would suggest transforming your WCF objects to POCO's (custom model representation). I'm kind glad the above did not work, as I would always prefer to make a custom WCF object which transports a clean subset of data, exactly what the WCF request needs and it then doesn't include a stack of superfluous data to be sent across the data, and I'm sure your L2S entities have lots of it. The actual reason I tried sending my L2S data directly across the wire was because I had to make an engine which employed certain 'rules' these rules were stored in a correlation of about 4 database tables with relationships and it was not viable to upkeep a separate WCF object.

  So in the end what I ended up doing was extending custom code gen classes to include Wcf.cs with [DataContext] and then cloned property for property the L2S classes -> WCF custom classes.

  It worked perfectly in the end and this is the cloning procedure I used with instructions on my blog.

  I am happy with this and don't feel there is bad practice involved. Hope this helps.

  ericvg : Thanks for the info. For successful serialization, we had to add a [DataMemberIgnore] to any prop that was recursive (usually the reference back to the parent), since those won't serialize. That invovled changing the dbml, which of course will be overwritten on every change. It works, but not very seamless. As you know, the serialized objects are _huge_. thankfully, this was only for a proof of concept (SQLRS2008 consuming a WCF layer to our services to rid us of redudant logic)

  GONeale : OK no prob. Well check out my cloning procedure, hopefully that route will help you.

Running multiple SQL statements in the one operation.

I'm trying to use ADO to create several tables at once, into MS Access. Is it possible to do multiple statements in the one operation? For instance:

...
// I have omitted the field details 
CString sQuery = "CREATE TABLE [Table1] (..., PRIMARY KEY ([ID])); \nCREATE TABLE [Table2] (..., PRIMARY KEY ([ID]));";
oRecordset.Open(oDatabase.m_pConnection, sQuery)

This fails due to a "Syntax Error in CREATE TABLE statement", although each of the create statements work on their own perfectly. Is there a way of doing this sort of thing? There will also be statements to add constraints, add indexing, etc., and I'd really like to be able to do it so that I don't have to split up the string into separate parts.

From stackoverflow

• I don't know if ADO is constructed over JET OleDB Engine, which I suppose, if it is this way, The Jet Engine doesn't support execution of multiple statements in one single batch, we tryed separating with ; and with the GO reserved word, but it does not work.

• ADO to MS Access does not support batch SQL statements. You need to run each statement as a separate execution.

• If you're sample set of commands is typical, just do something like this in VBA or the language of your choice:

  public sub ExeuteBatch(BatchString as String)
      var s as string  
      var abatch as array  
      sbatch = replace(sbatch, "\n", "")
      abatch = split(BatchString, ";")
      for each s in abatch
          ** adodb execute s here **
      next s
  end sub
  

  That's off the top of my head, but you should be able to take it from there I hope.

• I think you can run multiple commands in one ADO Command.

  You just need proper line feeds between then. i.e. \n doesn't work.

  Try something like this: (Using VB Syntaxish)

  MyQuery = "Select * from Whatever " & vbLf
  MyQuery = MyString & "Select * from SomethingElse " & vbLF

  oRecordset.Open(oDatabase.m_pConnection, MyQuery )

  onedaywhen : ADO simply passes the SQL code to the engine and the engine in question (ACE/Jet) has no support for multiple SQL statements).

  GordyII : Ah, that would make sense. Thinking about it, I have done this successfully on SQL Server.

• People who think you can send multiple SQL statements to Jet in a batch just aren't thinking.

  Jet is a file-server database engine -- there is no centralized server process controlling interaction between clients and the actual data store. Instead, clients are all running individual instances of Jet and cooperatively editing a file in a way that is controlled by the Jet locking file (LDB). Without a centralized process to serialize and prioritize the SQL statements, you wouldn't want Jet to be able to process multiple statements in a batch.

  Those who are offering the suggestion of using ADO and separating the statements with a CrLf should code it up and give it a try and then get back to us about how useful their speculative advice actually is.

• ADO isn't the issue: the ACE/Jet engine simply does not support multiple SQL statements within a single operation. In other words, ACE/JET SQL lacks procedural syntax found in most 'industrial-strength' SQL products.

  You will need to issue a Connection.Execute for each CREATE TABLE statement i.e. client side procedural code.

href error in .jsp

i had this code in my .jsp file:

<li><a href="${pageContext.request.contextPath}/pages/client_home.jsp" id="home">Home</a></li>
  <li><a href="${pageContext.request.contextPath}/pages/client_about.jsp" id="about">About</a></li>

and when i click the link, i got this error:

HTTP Status 500 -

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: javax.servlet.ServletException: File "/1920.14042009/pages/client_navbar.jsp" not found org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:522) org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:398) org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:337) org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266) javax.servlet.http.HttpServlet.service(HttpServlet.java:803) ...

i also tried replacing ${pageContext.request.contextPath}... with http://localhost:8080/1920.14042009/pages/client_about.jsp.

error still persists.

help pls...

From stackoverflow

• The problem is not with the two lines of code or using ${pageContext.request.contextPath}

  The file client_navbar.jsp is not available in the context, but is referred by the jsp file that the link points to. Please check...

  bgreen1989 : no, client_navbar.jsp exists..and even if i tried to comment the jsp include of it, error is still exists.

  bgreen1989 : in this project, we've been using .jsp - java servlet - service - dao architechture..

C# subtring parse using regular expression (ATL >> NET)?

What is the C# & .NET Regex Pattern that I need to use to get "bar" out of this url?

http://www.foo.com/bar/123/abc

In ATL regular expressions, the pattern would have been

http://www\.foo\.com/{[a-z]+}/123/abc

From stackoverflow

• Simply: #http://www\.foo\.com/([a-z]+)/123/abc#

  use parenthesis instead of brackets.

  You will need to use a character on the front and the end of the regular expression to make it work too.

  Nick Berardi : actually this is wrong because the "." is considered any char? So yours also evaluates fine for http://wwwxfood.com/bar/123/abc

  Daniel Brückner : The dot is escaped with a backslash - so it works.

  Nick Berardi : # is not a valid character in ASP.NET, I think you mean ^ and $

  Nick Berardi : Also it wasn't escaped when I posted the comment.

  Erick : @Nick Actually it was excaped but for some reason you have to double escape your text on SO ^_^ , as for the #s don't you need a delimiter usually for your REGEX ... ?

• Pretty much the same thing

      http://www\.foo\.com/([a-z]+)/123/abc
  

• This will almost work - just a tiny modification - change brackets to parenthesis.

  http://www\.foo\.com/([a-z]+)/123/abc

  But I consider this regex of not much use because it includes almost the whole string. Would it not be better to match the first path element independently from the whole rest?

  ^http://[^/]*/([^/]*).*$

  Jonathan C Dickinson : Maybe ^http://([^/])*/([^/]*).*$

  Jonathan C Dickinson : Ignore the semicolon, SO added it for some reason.

• Here is a solution that breaks the url up into component parts; protocol, site and part. The protocol group is not required so you could give the expression 'www.foo.com/bar/123/abc'. The part group can contain multiple sub groups representing the folders and file under the site.

  ^(?<protocol>.+://)?(?<site>[^/]+)/(?:(?<part>[^/]+)/?)*$
  

  You would use the expression as follows to get 'foo'

  string s = Regex.Match(@"http://www.foo.com/bar/123/abc", @"^(?<protocol>.+://)?(?<site>[^/]+)/(?:(?<part>[^/]+)/?)*$").Groups["part"].Captures[0].Value;
  

  The breakdown of the expression results are as follows

  protocol: http://
  site: www.foo.com
  part[0]: bar
  part[1]: 123
  part[2]: abc

Freeing up a TCP/IP port?

netstat -tulnap shows me what ports are in use. How to free up a port, in Linux?

From stackoverflow

• Kill the process that is listening to the port in question. I believe netstat shows you process ids.

• I think the only way will be to stop the process which has opened the port.

• Stop/kill the process attached to it.

• The "netstat --programs" command will give you the process information, assuming you're root. Then you will have to kill the "offending" process which may well start up again just to annoy you :-).

  What are you actually trying to achieve here? Solutions will vary based on the processes holding those ports.

• As the others have said, you'll have to kill all processes that are listening on that port. The easiest way to do that would be to use the fuser(1) command. For example, to see all of the processes listening for http requests on port 80 (run as root or use sudo):

  # fuser 80/tcp
  

  If you want to kill them, then just add the -k option.

• I can write the below code to kill the process kill pidnumber

  But it does not kill immediately it takes 1 minute to release the port. So how to release the port immediately?

  Blank Xavier : I find your question disturbing. It is wrong to be killing processes to get hold of ports. You should be disabling their startup or modifying their config such that they use an alternative port.

  ephemient : Use `setsockopt(..., SOL_SOCKET, SO_REUSEADDR, ...)` if you want to bind to the port as soon as possible.

• If you really want to kill a process immediately, you send it a KILL signal instead of a TERM signal (the latter a request to stop, the first will take effect immediately without any cleanup). It is easy to do:

  kill -KILL <pid>
  

  Be aware however that depending on the program you are stopping, its state may get badly corrupted when doing so. You normally only want to send a KILL signal when normal termination does not work. I'm wondering what the underlying problem is that you try to solve and whether killing is the right solution.