1 2 Previous Next 15 Replies Latest reply on May 24, 2012 9:35 AM by rhauch

    JDBC query - NPE

    bwallis42

      I'm trying some JDBC queries from a client using the REST interface. The code is:

       

      {code}

        // Load the database driver

                                    Class.forName("org.modeshape.jdbc.JcrDriver");

       

        // Get a connection to the database

                                    Connection conn = DriverManager.getConnection("jdbc:jcr:http://localhost:8080/modeshape-rest?repositoryName=DocumentStore&workspace=default&user=admin&password=admin");

       

        // Get a statement from the connection

                                    Statement stmt = conn.createStatement();

       

        // Execute the query

                                    ResultSet rs = stmt.executeQuery("SELECT * FROM [inf:documentGroup]");

       

      {code}

       

      the stmt.executeQuery() statement is line 39 (in the stack trace below).

       

      the debug output includes the http traffic and the raw json result looks like what I was expecting but then the JDBC code gets a NPE.

       

      {noformat}

      139 [main] DEBUG org.apache.http.impl.conn.DefaultClientConnection  - Sending request: POST /modeshape-rest/DocumentStore/default/query HTTP/1.1

      139 [main] DEBUG org.apache.http.wire  - >> "POST /modeshape-rest/DocumentStore/default/query HTTP/1.1[\r][\n]"

      139 [main] DEBUG org.apache.http.wire  - >> "Content-Type: application/jcr+sql2[\r][\n]"

      139 [main] DEBUG org.apache.http.wire  - >> "Content-Length: 33[\r][\n]"

      139 [main] DEBUG org.apache.http.wire  - >> "Host: localhost:8080[\r][\n]"

      139 [main] DEBUG org.apache.http.wire  - >> "Connection: Keep-Alive[\r][\n]"

      139 [main] DEBUG org.apache.http.wire  - >> "User-Agent: Apache-HttpClient/4.1.2 (java 1.5)[\r][\n]"

      139 [main] DEBUG org.apache.http.wire  - >> "Authorization: Basic YWRtaW46YWRtaW4=[\r][\n]"

      139 [main] DEBUG org.apache.http.wire  - >> "[\r][\n]"

      139 [main] DEBUG org.apache.http.headers  - >> POST /modeshape-rest/DocumentStore/default/query HTTP/1.1

      139 [main] DEBUG org.apache.http.headers  - >> Content-Type: application/jcr+sql2

      139 [main] DEBUG org.apache.http.headers  - >> Content-Length: 33

      139 [main] DEBUG org.apache.http.headers  - >> Host: localhost:8080

      139 [main] DEBUG org.apache.http.headers  - >> Connection: Keep-Alive

      140 [main] DEBUG org.apache.http.headers  - >> User-Agent: Apache-HttpClient/4.1.2 (java 1.5)

      140 [main] DEBUG org.apache.http.headers  - >> Authorization: Basic YWRtaW46YWRtaW4=

      140 [main] DEBUG org.apache.http.wire  - >> "SELECT * FROM [inf:documentGroup]"

      217 [main] DEBUG org.apache.http.wire  - << "HTTP/1.1 200 OK[\r][\n]"

      217 [main] DEBUG org.apache.http.wire  - << "Server: Apache-Coyote/1.1[\r][\n]"

      217 [main] DEBUG org.apache.http.wire  - << "X-Powered-By: Servlet/3.0; JBossAS-6[\r][\n]"

      218 [main] DEBUG org.apache.http.wire  - << "Content-Type: */*[\r][\n]"

      218 [main] DEBUG org.apache.http.wire  - << "Content-Length: 841[\r][\n]"

      218 [main] DEBUG org.apache.http.wire  - << "Date: Wed, 16 May 2012 10:18:57 GMT[\r][\n]"

      218 [main] DEBUG org.apache.http.wire  - << "[\r][\n]"

      218 [main] DEBUG org.apache.http.impl.conn.DefaultClientConnection  - Receiving response: HTTP/1.1 200 OK

      218 [main] DEBUG org.apache.http.headers  - << HTTP/1.1 200 OK

      218 [main] DEBUG org.apache.http.headers  - << Server: Apache-Coyote/1.1

      218 [main] DEBUG org.apache.http.headers  - << X-Powered-By: Servlet/3.0; JBossAS-6

      218 [main] DEBUG org.apache.http.headers  - << Content-Type: */*

      218 [main] DEBUG org.apache.http.headers  - << Content-Length: 841

      218 [main] DEBUG org.apache.http.headers  - << Date: Wed, 16 May 2012 10:18:57 GMT

      218 [main] DEBUG org.apache.http.client.protocol.ResponseAuthCache  - Caching 'basic' auth scheme for http://localhost:8080

      218 [main] DEBUG org.apache.http.impl.client.DefaultHttpClient  - Connection can be kept alive indefinitely

      218 [main] DEBUG org.apache.http.wire  - << "{"types":{"jcr:uuid":"STRING","jcr:primaryType":"STRING","jcr:created":"DATE","jcr:createdBy":"STRING","jcr:path":"STRING","jcr:name":"STRING","jcr:score":"DOUBLE","mode:localName":"STRING","mode:depth":"LONG"},"rows":[{"jcr:uuid":"c0f27e80-339f-4b0b-9484-699f37f74ebc","jcr:primaryType":"inf:documentGroup","jcr:created":"2012-05-15T14:52:48.764+10:00","jcr:createdBy":"username","jcr:path":"\/inf:documentGroup","jcr:name":"inf:documentGroup","jcr:score":"4.355734825134277","mode:localName":"documentGroup","mode:depth":"1"},{"jcr:uuid":"58d09ead-d87f-4841-a4d0-a7c151ff174c","jcr:primaryType":"inf:documentGroup","jcr:created":"2012-05-15T15:23:02.907+10:00","jcr:createdBy":"username","jcr:path":"\/inf:documentGroup[2]","jcr:name":"inf:documentGroup","jcr:score":"4.355734825134277","mode:localName":"documentGroup","mode:depth":"1"}]}"

      219 [main] DEBUG org.apache.http.impl.conn.SingleClientConnManager  - Releasing connection org.apache.http.impl.conn.SingleClientConnManager$ConnAdapter@3bc0f2e5

      220 [main] DEBUG org.apache.http.impl.conn.DefaultClientConnection  - Connection shut down

      java.lang.NullPointerException

                at org.modeshape.jdbc.JcrResultSetMetaData.getColumnCount(JcrResultSetMetaData.java:82)

                at org.modeshape.jdbc.JcrResultSet.<init>(JcrResultSet.java:100)

                at org.modeshape.jdbc.JcrStatement.execute(JcrStatement.java:430)

                at org.modeshape.jdbc.JcrStatement.executeQuery(JcrStatement.java:479)

                at au.com.infomedix.harvey.documentstore.jcr.JDBCTest.main(JDBCTest.java:39)

      {noformat}

       

      On examination of the code in JcrStatement it seems that the result returned by the execute function is null. Looking at HttpRepositoryDelegate.execute() it seems that it will always return a null. This code just looks like it is incomplete.  Is there a way to get this to work?

       

      thanks,

        • 1. Re: JDBC query - NPE
          rhauch

          ModeShape's JDBC driver cannot be used remotely to query content; this code was simply never completed because it was a lower priority. It can be used to remotely access JDBC metadata. If it's important, you could implement the missing code (which I think involves processing the JSON results from the REST client), and use your "new" driver on the client.

           

          We've been trying to correct the documentation to make this more clear. Did we miss something?

          • 2. Re: JDBC query - NPE
            bwallis42

            Hi Randall,

              Nope, that wasn't obvious to me from what I have read. The JcrDriver javadoc page doesn't seem to mention that. I'm not sure I see how access to the metadata only via JDBC is particularly useful so I don't think I would pick this up unless it was quite explicitly stated.

             

            The application I'm currently working on that was using jackrabbit was being switched to modeshape to enable decent access via JDBC since the jackrabbit JDBC driver is not very good. Looks like I might have a bit more work to do.

             

            It sounds simple enough, any quick pointers to adding this to the implementation? I'll give it a go.

             

            thanks

            • 3. Re: JDBC query - NPE
              rhauch

              Probably the easiest to is debug it (or temporarily add print statements) to see the structure of the JSON response. It should map pretty well to a result set, since that's what's being processed in the REST service. The code for the service starts here, which then calls this code that actually issues the query and builds the JSON response.

               

              Let me know if you have any questions when you get into it.

              • 4. Re: JDBC query - NPE
                bwallis42

                I have a working(ish) jdbc driver now which I can run simple examples through using a java app.

                 

                I've tried to use it from a DB client such as SQuirreLSQL or DBVisualize and have come across a problem. There seems to be no way to use a ":" character in a table or column name from these tools as they both interpret the ":" as a way to specify a parameter to a parameterised query. Similar syntax is used in hibernate named queries. I have tried to escape the ":" but cannot find a way to do that that works for either tool. The queries work fine from the java app that uses the jdbc interface directly, it just seems to be a tool thing.

                 

                eg: If I try the query

                 

                {noformat}

                select * from "nt:base"

                {noformat}

                or

                 

                {noformat}

                select * from [nt:base]

                {noformat}

                then I get prompted to enter a parameter value for ":base"

                 

                So, I was thinking of adding an extra parameter to the driver, passed in via the connection URL, to provide an alternative character to use for the namespace/name separator. ie:

                 

                {noformat}

                jdbc:jcr:http://localhost:8080/modeshape-rest?repositoryName=DocumentStore&workspace=default&user=admin&password=admin&separator=|

                {noformat}

                 

                This would also have to modify the names returned for metadata queries

                 

                {noformat}

                conn.getMetaData().getTables(null, null, "%", null) // or getColumns

                {noformat}

                As the tools use the names from the metadata in some situations (table content tab for example).

                 

                Any comments on this? It seems that the use of the ":" in the table and column names is going to be awkward for existing DB tools.

                • 5. Re: JDBC query - NPE
                  rhauch

                  You might want to check that our driver's DatabaseMetaData implementation is still returning the double quote character as the identifier quote character. We expose the double-quote character in metadata because it is far more common in SQL dialects and thus should be more; ModeShape actually support queries that use single- or double-quote characters or square brackets, as long as the quotes are closed with the matching character.

                   

                  Also, do you have the sqlparam plugin for SQuirreL installed? Apparently it prompts you for a parameter whenever it sees the ":<label>" pattern in a query string. Can you try it with that disabled. (One of my co-workers just tried SQuirreL against another database, and verified that the following query is accepted properly:

                   

                       SELECT * FROM "test:table"

                   

                  Using another character for the namespace/name delimiter will actually be quite complex, because in order to do this properly the driver would have to completely parse the query to know which parts are identifiers, and to prevent incorrectly replacing the separator character in other parts of the query. Either that, or ModeShape has to support the different separator, and that would have its own complexities.

                  • 6. Re: JDBC query - NPE
                    bwallis42

                    You might want to check that our driver's DatabaseMetaData implementation is still returning the double quote character as the identifier quote character. We expose the double-quote character in metadata because it is far more common in SQL dialects and thus should be more; ModeShape actually support queries that use single- or double-quote characters or square brackets, as long as the quotes are closed with the matching character.

                    Just checked. Yes, it is returning the double quote character.

                     

                    Also, do you have the sqlparam plugin for SQuirreL installed? Apparently it prompts you for a parameter whenever it sees the ":<label>" pattern in a query string. Can you try it with that disabled. (One of my co-workers just tried SQuirreL against another database, and verified that the following query is accepted properly:

                     

                         SELECT * FROM "test:table"

                    I did, so I have disabled it and now I don't get the popup dialog asking for another parameter. Instead I get an error

                     

                    {noformat}

                    java.lang.IllegalArgumentException: 0 parameters supplied, but 1 parameter required: "Executing query: {0}select * from "nt:base"" => "Executing query: {0}"

                    {noformat}

                    I also tried creating and querying a table called xxx:fred in a mysql database (using the backtick (`) character for a quote as is common for mysql) and this did work so it seems the : character is not inherently bad but it still doesn't work with modeshape. I'll have to look a bit closer to see where this error is coming from.

                    • 7. Re: JDBC query - NPE
                      bwallis42

                      I've come across another problem.

                       

                      I'm actually doing this so that I can use jasper reports against the repository so that is my main test client. One of the calls it makes against the JDBC interface on the ResultSetMetadata is getTableName(int column) for which the doco says

                       

                      {noformat}

                      String getTableName(int column)

                                          throws SQLException

                      Gets the designated column's table name.

                       

                      Parameters:

                          column - the first column is 1, the second is 2, ...

                      Returns:

                          table name or "" if not applicable

                      {noformat}

                       

                      For a simple query against a single table this is just that table name, for a more complex query joining multiple tables this will be the name of the table that the column came from (or the empty string if the value was calculated I suppose).

                       

                      This is information that we don't have in the result of executing the query, it is not in the json reply string.

                       

                      Is there a way of getting this info included in the response from the server?

                      • 8. Re: JDBC query - NPE
                        bwallis42

                        OK, I have been doing some more reading and playing with iReports.

                         

                        This isn't going to work.

                         

                        • No GroupBy support which makes a lot of reporting queries difficult
                        • no support for count(*) - iReport uses this
                        • no support for prepareStatement - iReport uses this.
                        • The tools in iReport do some statement parsing and it isn't happy with JCR-SQL2 syntax. It seems to be trying to get field names from the SQL expression and fails, haven't chased this down yet.
                        • many other things in the jdbc driver that throw the not supported exception that I haven't hit yet.

                         

                        I need to think more about this. I need a reporting solution and I really would like one that can use existing tools such as JasperReports or Crystal Reports as that is the sort of tools the users are familiar with. Puting a large amount of data into a JCR repository and then not having a reporting solution for that data is not something I really want to do.

                         

                        Will the new 3.0 release have better support for JDBC? Difficult to see how given the above limitations in JCR-SQL2. I haven't had a look at the JCR-JQOM but I don't think it will add much.

                         

                        (I've been using the log4jdbc proxy driver to investigate a lot of this, a very useful little tool!)

                        • 9. Re: JDBC query - NPE
                          rhauch
                          java.lang.IllegalArgumentException: 0 parameters supplied, but 1 parameter required: "Executing query: {0}select * from "nt:base"" => "Executing query: {0}"
                          

                           

                          I also tried creating and querying a table called xxx:fred in a mysql database (using the backtick (`) character for a quote as is common for mysql) and this did work so it seems the : character is not inherently bad but it still doesn't work with modeshape. I'll have to look a bit closer to see where this error is coming from.

                          This looks an awful lot like one of our I18n error messages - the '{0}' is what makes me think this. Any chance you get a stack trace for this?

                          • 10. Re: JDBC query - NPE
                            rhauch

                            Well, crap. I was hoping Jasper or Crystal would work our JDBC driver.

                             

                             

                            • No GroupBy support which makes a lot of reporting queries difficult
                            • no support for count(*) - iReport uses this
                            • no support for prepareStatement - iReport uses this.
                            • The tools in iReport do some statement parsing and it isn't happy with JCR-SQL2 syntax. It seems to be trying to get field names from the SQL expression and fails, haven't chased this down yet.
                            • many other things in the jdbc driver that throw the not supported exception that I haven't hit yet.

                             

                            I need to think more about this. I need a reporting solution and I really would like one that can use existing tools such as JasperReports or Crystal Reports as that is the sort of tools the users are familiar with. Puting a large amount of data into a JCR repository and then not having a reporting solution for that data is not something I really want to do.

                             

                            Will the new 3.0 release have better support for JDBC? Difficult to see how given the above limitations in JCR-SQL2. I haven't had a look at the JCR-JQOM but I don't think it will add much.

                             

                            I think we can pretty easily support prepared statements; that's just in the JDBC driver and should be relatively straightfoward to implement. I would also like to support aggregates (at least some forms of COUNT), which also means GROUP BY; would you be willing to log a feature request to add support for these?

                             

                            As for the other errors, let us know if there's anything we can do with the result set column names (or something else, like implement additional methods in the JDBC driver) to make iReport work better. Perhaps we should create another feature enhancement to cover JDBC improvements to support use by Crystal and/or Jasper, with as much detail as we can capture about what needs to be done. We can then start prioritizing and breaking into smaller, more manageable chunks.

                             

                            We're making significant improvements in the query engine in 3.0, but that doesn't really affect the usability or "completeness" of the JDBC driver. However, we can definitely improve the driver, though making it JDBC 4 (or 4.1) compliant will be a fair amount of work. I'd love to do that, but we only have so many people working on it. (I hate to say it again, but contributions are welcome from anyone that wants to help here.)

                             

                            (I've been using the log4jdbc proxy driver to investigate a lot of this, a very useful little tool!)

                            Thanks for the tip! I wasn't aware of it.

                            • 11. Re: JDBC query - NPE
                              rhauch

                              I should have thought about this earlier.

                               

                              While it was important that JDBC clients in general could use ModeShape's JDBC driver, in truth the impetus to create ModeShape's JDBC driver was so that the Teiid data virtualization engine could use a ModeShape repository as one of many sources. Teiid is a pretty fascinating bit of technology that can create "virtual databases" that are each backed by multiple other (relational and non-relational) stores. To JDBC and ODBC clients, a Teiid virtual databae just looks like a regular data source, but under the covers all data is really stored in those other stores and accessed/updated in real time only when needed. Teiid is a really powerful engine with all kinds of capabilities, and actually works really well as a data source for Jasper Reports and Crystal Reports.

                               

                              Teiid 7 and ModeShape 2 can be deployed into a JBoss AS 5/6 server, or Teiid 8 and ModeShape 3 can be deployed into JBoss AS7.1.

                               

                              Perhaps that's something to consider. It's obviously not as lightweight as just using ModeShape, but Teiid has some incredible features on its own that you might find interesting.

                              1 of 1 people found this helpful
                              • 12. Re: JDBC query - NPE
                                bwallis42

                                Randall Hauch wrote:

                                 

                                 

                                This looks an awful lot like one of our I18n error messages - the '{0}' is what makes me think this. Any chance you get a stack trace for this?

                                 

                                It comes from line 108 of HttpRepositoryDelegate.execute(String query, String language)

                                 

                                {code}

                                    LOGGER.trace("Executing query: {0}" + query);

                                {code}

                                 

                                Since the body of this method is not completely implemented this would not be tested as such (it executes the query but doesn't return any results).

                                 

                                I've added the code to return a QueryResult but didn't notice the "+" instead of a "," in the logging.

                                 

                                With that fixed I have got squirrelsql access working (mostly, sort of :-)

                                • 13. Re: JDBC query - NPE
                                  bwallis42

                                  Teiid looks like an interesting beast. Just starting to read about it.

                                   

                                  I assume it would have to run in the same appserver process as modeshape so that it could use the local jdbc driver.

                                   

                                  Makes things a little difficult at the moment as we are using modeshape 2.8.1 in jboss 6.1 and Teiid doesn't support jboss 6 (out of the box). Teiid 7.7 might be able to be deployed into as 6.1 but is really only supported on jboss 5.1, Teiid 8 is only supported in jboss 7.1. C'est la vie.

                                   

                                  Will have a play and see what works. Might just go with Modeshape 3 in jboss as 7.1 for the moment.

                                  • 14. Re: JDBC query - NPE
                                    bwallis42

                                    Randall Hauch wrote:

                                     

                                    I think we can pretty easily support prepared statements; that's just in the JDBC driver and should be relatively straightfoward to implement. I would also like to support aggregates (at least some forms of COUNT), which also means GROUP BY; would you be willing to log a feature request to add support for these?

                                     

                                    As for the other errors, let us know if there's anything we can do with the result set column names (or something else, like implement additional methods in the JDBC driver) to make iReport work better. Perhaps we should create another feature enhancement to cover JDBC improvements to support use by Crystal and/or Jasper, with as much detail as we can capture about what needs to be done. We can then start prioritizing and breaking into smaller, more manageable chunks.

                                    I'll try to put together a list of what I know is required and/or doesn't work. As I mentioned earlier I have it mostly working with SQuirreLSQL now. Some of the changes required were just to change not implemented exceptions to return empty strings or array for capabilities (in most cases I think the empty return value is quite valid).

                                     

                                    I will see if I can get the changes I already have into a state that could be used (it is a bit of a mess at the moment). As far as testing is involved, I would like to have a test case that exercised the various JDBC interfaces but that would require some sort of backend when running the tests. Do you have something like this supported during your builds? Unit tests for this sort of thing are difficult since it is just an interface to a server and stubbing that out can be a lot of work and hard to maintain.

                                    1 2 Previous Next