Message-ID: From: "mshajarrazip (@mshajarrazip)" To: "pgjdbc/pgjdbc" Date: Wed, 24 Jul 2019 01:46:00 +0000 Subject: Re: [pgjdbc/pgjdbc] issue #1136: Show SQL text (and/or binds) in SQLException message In-Reply-To: References: List-Id: X-GitHub-Author-Login: mshajarrazip X-GitHub-Comment-Id: 514445812 X-GitHub-Comment-Type: issue_comment X-GitHub-Edited-At: 2019-07-24T02:23:33Z X-GitHub-Issue: 1136 X-GitHub-Repo: pgjdbc/pgjdbc X-GitHub-Type: comment X-GitHub-Url: https://github.com/pgjdbc/pgjdbc/issues/1136#issuecomment-514445812 Content-Type: text/plain; charset=utf-8 The error message is parsed in `org/posgresql/util/ServerErrorMessage.java:146` I wanted to include the SQL text somewhere after line 183 ``` message = mesgParts.get(POSITION); if (message != null) { totalMessage.append("\n ").append("SQL text here"); //here totalMessage.append("\n ").append("Pointer here ^"); //here totalMessage.append("\n ").append(GT.tr("Position: {0}", message)); } ``` so it will print the SQL text with the position, like this: ``` org.postgresql.util.PSQLException: ERROR: testsyntax error at or near "x" SQL text here Pointer here ^ Position: 19 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2468) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2211) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:309) at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:446) at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:370) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:311) at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:297) at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:274) at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:225) at org.postgresql.test.jdbc2.ClientEncodingTest.checkConnectionSanity(ClientEncodingTest.java:73) at org.postgresql.test.jdbc2.ClientEncodingTest.setEncodingAscii(ClientEncodingTest.java:68) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78) at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.junit.runners.Suite.runChild(Suite.java:128) at org.junit.runners.Suite.runChild(Suite.java:27) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268) at org.junit.runners.ParentRunner.run(ParentRunner.java:363) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:89) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:41) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:541) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:763) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:463) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:209) ``` The server error message looks like this: Received at `org/postgresql/core/v3/QueryExecutorImpl.java:2461` : ```` EncodingPredictor.DecodeResult totalMessage = pgStream.receiveErrorString(elen - 4); ServerErrorMessage errorMsg = new ServerErrorMessage(totalMessage); ```` `totalMessage` is ``` SERROR VERROR C42601 Msyntax error at or near "x" P19 Fscan.I L1134 Rscanner_yyerror ``` in `errorMsg`, the value becomes ``` {P=19, R=scanner_yyerror, S=ERROR, C=42601, V=ERROR, F=scan.l, L=1134, M=syntax error at or near "x"} ``` Other details that the error message can include are as follows, in org/posgresql/util/ServerErrorMessage.java:20; ``` private static final Character SEVERITY = 'S'; private static final Character MESSAGE = 'M'; private static final Character DETAIL = 'D'; private static final Character HINT = 'H'; private static final Character POSITION = 'P'; private static final Character WHERE = 'W'; private static final Character FILE = 'F'; private static final Character LINE = 'L'; private static final Character ROUTINE = 'R'; private static final Character SQLSTATE = 'C'; private static final Character INTERNAL_POSITION = 'p'; private static final Character INTERNAL_QUERY = 'q'; private static final Character SCHEMA = 's'; private static final Character TABLE = 't'; private static final Character COLUMN = 'c'; private static final Character DATATYPE = 'd'; private static final Character CONSTRAINT = 'n'; ``` **Are any of these mapped to the SQL text in question?** Also, it says in `org/posgresql/util/ServerErrorMessage.java:146`: ``` public String toString() { // Now construct the message from what the server sent // The general format is: // SEVERITY: Message \n // Detail: \n // Hint: \n // Position: \n // Where: \n // Internal Query: \n // Internal Position: \n // Location: File:Line:Routine \n // SQLState: \n // // Normally only the message and detail is included. // If INFO level logging is enabled then detail, hint, position and where are // included. If DEBUG level logging is enabled then all information // is included. ``` I've set the `log_min_error_statement` and `log_min_messages` in `postgresql.conf` to `debug5`, and further set `loggerLevel` property to `DEBUG` in `logging.properties` and set `java.util.logging.config.file` to the `logging.properties` file, but the server error message is still `{P=19, R=scanner_yyerror, S=ERROR, C=42601, V=ERROR, F=scan.l, L=1134, M=syntax error at or near "x"}`. In this case, are more details not possible to be retrieved from the server? Am I missing some steps?