13 Replies Latest reply: Jul 10, 2012 4:55 PM by jschellSomeoneStoleMyAlias RSS

    Hot to print the whole query with prepared statement

    LinoPisto

      Hi all

      i'm quite new to java, i have a question about prepared statements

      my company uses something like this:

      String sqlQuery = "select * from my_table where columnA = (?) and columnB = (?)";
      log4Debug.debug( "Query = " + sqlQuery );
      preparedStatement = connection.prepareStatement(sqlQuery);
      preparedStatement.setString(1,"valueA");
      preparedStatement.setString(2,"valueB");

      but in the log i have no trace made by the replacements in the preparedstaments, in fact i will see:
      select * from my_table where columnA = (?) and columnB = (?)

      the solution i need is the one that allows me to print in the log the whole query with the replacements, in other words

      select * from my_table where columnA = 'valueA' and columnB = 'valueB'

      at the moment i have red preparedstatements documentation and other forums but i found no way to achieve that result.

      please help me.

      Thank you all.

        • 1. Re: Hot to print the whole query with prepared statement
          gimbal2
          There is no API way to do it. PERHAPS there is some trace logging support built into the JDBC driver itself, but then you'd have to check the documentation of said driver.
          • 2. Re: Hot to print the whole query with prepared statement
            939520
            I think you might be interested in reading up on log4jdbc (http://code.google.com/p/log4jdbc/). If its not to your liking, the article lists other loggers at the bottom of the article.

            If that doesn't meet your needs, another possibility is using aspectj to wrap the preparedStatement. I suggest searching Google for something like 'java aspectj preparedStatement wrapping logging'. Aspectj is probably too involved for you to create your own solution easily so I suggest finding a group that has already created it. I haven't used these loggers so I can't provide any additional information.
            • 3. Re: Hot to print the whole query with prepared statement
              rp0428
              You could try setting TRACE = true in the Oracle versions of JDBC classes that support it and see if that gives you what you want.

              The OraclePreparedStatement implementation is one of the classes that define this but whether it will provide what you want I don't know.

              You can often find hidden things like this if you are using a GUI with context-sensitive help; for statics just enter the classname followed by a period and see what shows up in the menu of possibilities.

              Keep in mind that there is usually no support for things that are not publicly documented and the functionality may change in new releases or disappear altogether.
              • 4. Re: Hot to print the whole query with prepared statement
                Joe Weinstein-Oracle
                Why not do this:

                String sqlQuery = "select * from my_table where columnA = (?) and columnB = (?)";
                log4Debug.debug( "Query = " + sqlQuery + " with parameters " + "valueA" + " and " + "valueB" );
                preparedStatement = connection.prepareStatement(sqlQuery);
                preparedStatement.setString(1,"valueA");
                preparedStatement.setString(2,"valueB");

                or you could do a little string manipulation to find ans substitute the '?'s for the values...
                standard JDBC won't provide you with the string you want. The prepare string will go the
                DBMS once, before any parameter values. Those will be sent later by themselves for each
                execution of the statement.
                • 5. Re: Hot to print the whole query with prepared statement
                  Kayaman
                  Joe Weinstein wrote:
                  Why not do this:
                  Because I imagine he wants to reduce the amount of dull boilerplate code. Of course it'll work, but it's a dumb and tedious solution.
                  • 6. Re: Hot to print the whole query with prepared statement
                    gimbal2
                    Kayaman wrote:
                    Joe Weinstein wrote:
                    Why not do this:
                    Because I imagine he wants to reduce the amount of dull boilerplate code. Of course it'll work, but it's a dumb and tedious solution.
                    A less tedious version would probably entail a method that does token replacement on the query and accepts a variable argument list to receive the values to replace the tokens with. But why bother, you should not be logging queries by default anyway. Do what Joe suggested only when there is trouble and you want to collect some more information.
                    • 7. Re: Hot to print the whole query with prepared statement
                      LinoPisto
                      yes the best solution would be to create a method that replaces (?) with the values.

                      i think it's a little stupid that the preparedstatement doesn't print the whole query it is executing

                      I thank you all for you opinion
                      • 8. Re: Hot to print the whole query with prepared statement
                        gimbal2
                        LinoPisto wrote:
                        i think it's a little stupid that the preparedstatement doesn't print the whole query it is executing
                        Its more stupid that you blame an API interface for it.
                        • 9. Re: Hot to print the whole query with prepared statement
                          939520
                          By the way, when catching Exceptions, I usually print out the SQL string and just the primaryKey value(s) to the log file rather than all the parameter values. That's often enough to track down the problem. That way, you also avoid sending personal information to the log file, which is a security concern. Also, the primaryKeys are usually something short and simple such as integers. Logging a large String, CLOB or BLOB will get pretty messy.
                          • 10. Re: Hot to print the whole query with prepared statement
                            Mark Williams-Oracle
                            i think it's a little stupid that the preparedstatement doesn't print the whole query it is executing

                            What if the bind variables for the statement were sensitive information such as cash point access codes, credit card numbers, u.s. social security numbers, etc?

                            What if this information was just blindly dumped into a text file accessible by who knows who?
                            • 11. Re: Hot to print the whole query with prepared statement
                              rp0428
                              >
                              i think it's a little stupid that the preparedstatement doesn't print the whole query it is executing
                              >
                              Then you must not have read or understood what Joe (who has written JDBC drivers) said.
                              Read it again several times and if you don't understand what he is saying then ask
                              >
                              standard JDBC won't provide you with the string you want. The prepare string will go the
                              DBMS once, before any parameter values. Those will be sent later by themselves for each
                              execution of the statement.
                              >
                              The preparedstatement never contains 'the whole query it is excecuting'. It does the 'preparation' - not the execution.
                              • 12. Re: Hot to print the whole query with prepared statement
                                jschellSomeoneStoleMyAlias
                                LinoPisto wrote:
                                i think it's a little stupid that the preparedstatement doesn't print the whole query it is executing
                                The problem is that you think that the only possible data source is a database that takes SQL as a string.

                                There are databases that do not take SQL. There are data sources that are not databases.
                                • 13. Re: Hot to print the whole query with prepared statement
                                  jschellSomeoneStoleMyAlias
                                  Kayaman wrote:
                                  Joe Weinstein wrote:
                                  Why not do this:
                                  Because I imagine he wants to reduce the amount of dull boilerplate code. Of course it'll work, but it's a dumb and tedious solution.
                                  However the very expression of normal database layers is "dull boilerplate code", so if one is using something like a code generator to avoid the SQL in the first place then adding a log feature is easy.

                                  I suspect that other wrappers already provide a mechanism for logging it as well.