Here's another possibility that might reveal something with a little less effort:
Install the P6Spy jdbc driver and point it at your Mysql driver. This pass-through driver will log all traffic to and from the database at the JDBC level. http://www.p6spy.com/ Note that p6spy hasn't been maintained (as far as I know) for quite some time. The binaries typically work, but you might have to figure out how to compile it from source. I think I had to do that the last time I used it. On Sun, Nov 7, 2010 at 8:41 PM, Mark Fischer <[email protected]> wrote: > Thanks for putting up with me. If I have time I will run the debug > tonight. We will see if I am smart enough to figure it out. > On Nov 7, 2010 6:04 PM, "Andrus Adamchik" <[email protected]> wrote: >> Hi Mark, >> >> Your test indicates that JDBC part is ok, but still the Modeler somehow > ignores your tables... I am a bit at a loss what to recommend now. It all > works on my own tests. The only way to gather insight into what's really > going on is running the Modeler in Eclipse debugger and stepping through > org.apache.cayenne.access.DbLoader.loadDataMapFromDB(..) method. This setup > took me just a few minutes, but if you haven't done it before, this may be > more challenging: >> >> 1. Get the right version of the source code via SVN and import > 'cayenne-jdk1.5-unpublished' in Eclipse: > http://svn.apache.org/repos/asf/cayenne/main/tags/3.0.1/framework/cayenne-jdk1.5-unpublished/ >> >> 2. Run the Windows or generic Modeler from the command line in debug mode: >> >> java -Xdebug -Xrunjdwp:server=y,transport=dt_socket,address=4142,suspend=n > -jar CayenneModeler.jar >> >> 3. In Eclipse go to "Run > Debug Configurations", add a new "Remote Java > Application" setting the project and port as shown on the screenshot, click > "Debug": >> >> http://people.apache.org/~aadamchik/debug/screen1.png >> >> 4. Open org.apache.cayenne.access.DbLoader class and add a breakpoint on > line 795: >> >> http://people.apache.org/~aadamchik/debug/screen2.png >> >> (doubleclick on the left of the code editor to do that). >> >> 5. Start reverse engineering in the Modeler and when it hits the > breakpoint, go through it line by line to see what tables it gets and why > they are excluded. >> >> Let us know if you stumble on any of the steps. >> >> Cheers, >> Andrus >> >> On Nov 5, 2010, at 12:38 PM, Mark Fischer wrote: >>> Okay I setup and ran this code that I found thru google: >>> >>> * >>> >>> public* *void* displayDbProperties(){ >>> >>> java.sql.DatabaseMetaData dm = *null*; >>> >>> java.sql.ResultSet rs = *null*; >>> >>> *try*{ >>> >>> con= *this*.getConnection(); >>> >>> *if*(con!=*null*){ >>> >>> dm = con.getMetaData(); >>> >>> System.*out*.println("Driver Information"); >>> >>> System.*out*.println("\tDriver Name: "+ dm.getDriverName()); >>> >>> System.*out*.println("\tDriver Version: "+ dm.getDriverVersion ()); >>> >>> System.*out*.println("\nDatabase Information "); >>> >>> System.*out*.println("\tDatabase Name: "+ dm.getDatabaseProductName()); >>> >>> System.*out*.println("\tDatabase Version: "+ >>> dm.getDatabaseProductVersion()); >>> >>> System.*out*.println("Avalilable Catalogs "); >>> >>> rs = dm.getCatalogs(); >>> >>> *while*(rs.next()){ >>> >>> System.*out*.println("\tcatalog: "+ rs.getString(1)); >>> } >>> >>> >>> String[] types = {"TABLE", "VIEW"}; >>> >>> rstables = dm.getTables(*null*, "%", "%", types); >>> >>> *while*(rstables.next()){ >>> >>> System.*out*.println("\ttables: " + rstables.getString(3) + "\t" + >>> rstables.getString(2) + "\t" + rstables.getString(1) + "\t" + >>> rstables.getString(4) ); >>> >>> } >>> >>> rs.close(); >>> >>> rs = *null*; >>> >>> closeConnection(); >>> >>> }*else* System.*out*.println("Error: No active Connection"); >>> >>> }*catch*(Exception e){ >>> >>> e.printStackTrace(); >>> >>> } >>> >>> dm=*null*; >>> >>> } >>> This took some googling for me as I am a Visual Studio Developer and >>> Eclispe, Java, jdbc, Cayenne, etc are all new to me but I got it to run. > I >>> got these results using the same connection string that I copy and pasted >>> out of Cayenne: >>> >>> >>> Connection Successful! >>> >>> Driver Information >>> >>> Driver Name: Microsoft SQL Server JDBC Driver 3.0 >>> >>> Driver Version: 3.0.1301.101 >>> >>> Database Information >>> >>> Database Name: Microsoft SQL Server >>> >>> Database Version: 10.00.1600 >>> >>> Avalilable Catalogs >>> >>> ***Here I get a list of all the catalogs and then I get a list of all of > the >>> tables in all of the catalogs*** >>> Is this what you were asking me to test? What do I try next?? >>> >>> >>> On Wed, Nov 3, 2010 at 5:36 PM, Andrus Adamchik <[email protected] >>wrote: >>> >>>> At the end reverse engineering in Cayenne is based on JDBC API. More >>>> specifically it invokes DatabaseMetaData.getTables(..) method: >>>> >>>> >>>> > http://download.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.html#getTables%28java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String[]%29 >>>> >>>> with catalog parameter set to NULL, schema and table patterns set to > your >>>> selections, and "types" set to { "TABLE", "VIEW" }. Then it processes >>>> whatever is returned by this method. >>>> >>>> I guess one way to debug it is to write a simple JDBC test to see if the >>>> above returns any data. >>>> >>>> Andrus >>>> >>>> >>>> >>>> On Nov 3, 2010, at 6:19 PM, Mark Fischer wrote: >>>> >>>>> I tried working this a different way around. I created a test database >>>> and >>>>> table by using Cayenne to map things out and then generate the table. >>>> Then >>>>> I opened Cayenne with a new project and tried to reverse engineer the >>>> table >>>>> that Cayenne created. I still get an empty usermap. Is there a log file >>>> I >>>>> could look in for an error of some explination of what is going wrong? >>>> The >>>>> show console log shows nothing helpful as far as I am able to see. I >>>> wish >>>>> it would at least give me something to go on. >>>>> On Wed, Nov 3, 2010 at 2:50 PM, Mark Fischer <[email protected]> >>>> wrote: >>>>> >>>>>> I have tried checking that I don't have a permissions problem. I have >>>>>> tried loggin into SQL Server with SQL Server Management Studio using > the >>>>>> same username and password and I can see all of the tables. What >>>>>> permissions is Cayenne looking for from the database. I'm not sure > what >>>> I >>>>>> am checking for. >>>>>> >>>>>> Thanks, >>>>>> Mark >>>>>> >>>>>> On Tue, Nov 2, 2010 at 9:17 PM, Andrus Adamchik < >>>> [email protected]>wrote: >>>>>> >>>>>>> Hi Mark, >>>>>>> >>>>>>> I can only think of two reasons - a given schema has no tables or >>>> views, >>>>>>> or permissions for a given user won't let him see those tables. I am >>>> not a >>>>>>> SQL Server expert to comment on the specific schema though... >>>>>>> >>>>>>> Andrus >>>>>>> >>>>>>> >>>>>>> On Nov 2, 2010, at 6:16 PM, Mark Fischer wrote: >>>>>>> >>>>>>>> I am running windows XP with cayenne-modeler connecting to a local > MS >>>>>>> SQL >>>>>>>> Server 2008 Express db. I set up my local datasource and when I >>>> clicked >>>>>>>> test it says successful. >>>>>>>> >>>>>>>> I create a node and have the DataSource >>>>>>>> Factory org.apache.cayenne.conf.DriverDataSourceFactory selected. >>>>>>>> >>>>>>>> With the node selected I go to Tools -> Reengineer Database Schema. >>>>>>>> >>>>>>>> Cayenne asks me which connection to use and I choose the same one as >>>>>>>> selected in the node. Then it asks me which schema and I leave it at >>>>>>>> db_accessadmin. >>>>>>>> >>>>>>>> Cayenne thinks for a second and then generates UntitledMap which > does >>>>>>> not >>>>>>>> seem to contain any of the tables from my database. I was following >>>> the >>>>>>>> example at >>>> http://cayenne.apache.org/doc/reverse-engineer-database.html. >>>>>>>> >>>>>>>> I just started trying cayenne two weeks ago and have been thrashing >>>> with >>>>>>>> this for two weeks I have searched but have not found anything > useful >>>>>>>> through google. >>>>>>>> Being new I am not sure what else to try. >>>>>>>> >>>>>>>> Here is the text in the console while I use the Reeingineer > menuitem: >>>>>>>> >>>>>>>> Nov 2, 2010 5:14:47 PM >>>>>>>> INFO: --- will run 2 queries. >>>>>>>> Nov 2, 2010 5:14:47 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:47 PM >>>>>>>> INFO: UPDATE component_geometry SET y = ?, x = ? WHERE id = ? >>>>>>>> Nov 2, 2010 5:14:47 PM >>>>>>>> INFO: [bind: 1->y:0, 2->x:0, 3->id:202] >>>>>>>> Nov 2, 2010 5:14:47 PM >>>>>>>> INFO: === updated 1 row. >>>>>>>> Nov 2, 2010 5:14:47 PM >>>>>>>> INFO: UPDATE domain_preference SET key_value_pairs = ? WHERE id = ? >>>>>>>> Nov 2, 2010 5:14:47 PM >>>>>>>> INFO: [bind: 1->key_value_pairs:'#Tue Nov 02 17:14:32 CDT 2010 >>>>>>>> ...', 2->id:201] >>>>>>>> Nov 2, 2010 5:14:47 PM >>>>>>>> INFO: === updated 1 row. >>>>>>>> Nov 2, 2010 5:14:47 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: --- will run 1 query. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: SELECT t0.key_value_pairs, t0.domain_id, t0.key, t0.id FROM >>>>>>>> domain_preference t0 JOIN domain t1 ON (t0.domain_id = t1.id) WHERE >>>>>>> (t0.key >>>>>>>> = ?) AND (t0.domain_id = ?) ORDER BY t1.level DESC LIMIT 1 OFFSET 0 >>>>>>> [bind: >>>>>>>> 1->key:'EmploymentApplication', 2->domain_id:221] >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: === returned 1 row. - took 16 ms. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: --- will run 1 query. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: SELECT t0.parent_id, t0.level, t0.id, t0.description, t0.name, >>>>>>>> t0.version FROM domain t0 WHERE (t0.name = ?) AND (t0.level = ?) >>>> [bind: >>>>>>>> 1->name:'CayenneModeler', 2->level:0] >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: === returned 1 row. - took 0 ms. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: --- will run 1 query. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: SELECT t0.key_value_pairs, t0.domain_id, t0.key, t0.id FROM >>>>>>>> domain_preference t0 JOIN domain t1 ON (t0.domain_id = t1.id) WHERE >>>>>>> (t0.key >>>>>>>> = ?) AND (t0.domain_id = ?) ORDER BY t1.level DESC LIMIT 1 OFFSET 0 >>>>>>> [bind: >>>>>>>> 1->key:'HMSysLocalSQLServer', 2->domain_id:200] >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: === returned 1 row. - took 0 ms. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: --- will run 1 query. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: SELECT t0.parent_id, t0.level, t0.id, t0.description, t0.name, >>>>>>>> t0.version FROM domain t0 WHERE (t0.name = ?) AND (t0.level = ?) >>>> [bind: >>>>>>>> 1->name:'CayenneModeler', 2->level:0] >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: === returned 1 row. - took 15 ms. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: --- will run 1 query. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: SELECT t0.domain_preference_id, t0.db_adapter, t0.user_name, >>>>>>>> t0.password, t0.url, t0.jdbc_driver, t0.id FROM db_connection_info > t0 >>>>>>> WHERE >>>>>>>> t0.id IN (?, ?, ?, ?, ?) [bind: 1->id:203, 2->id:210, 3->id:211, >>>>>>> 4->id:220, >>>>>>>> 5->id:246] >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: === returned 3 rows. - took 0 ms. >>>>>>>> Nov 2, 2010 5:14:50 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: --- will run 1 query. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: SELECT t0.parent_id, t0.level, t0.id, t0.description, t0.name, >>>>>>>> t0.version FROM domain t0 WHERE (t0.name = ?) AND (t0.level = ?) >>>> [bind: >>>>>>>> 1->name:'CayenneModeler', 2->level:0] >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: === returned 1 row. - took 0 ms. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: --- will run 1 query. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: SELECT t0.parent_id, t0.level, t0.id, t0.description, t0.name, >>>>>>>> t0.version FROM domain t0 WHERE t0.parent_id = ? [bind: >>>>>>> 1->parent_id:200] >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: === returned 15 rows. - took 0 ms. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: --- will run 1 query. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: SELECT t0.key_value_pairs, t0.domain_id, t0.key, t0.id FROM >>>>>>>> domain_preference t0 JOIN domain t1 ON (t0.domain_id = t1.id) WHERE >>>>>>> (t0.key >>>>>>>> = ?) AND (t0.domain_id = ?) ORDER BY t1.level DESC LIMIT 1 OFFSET 0 >>>>>>> [bind: >>>>>>>> 1->key:'recent.strategies', 2->domain_id:217] >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: === returned 1 row. - took 0 ms. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:53 PM >>>>>>>> DEBUG: will show progress... >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: --- will run 1 query. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: SELECT t0.parent_id, t0.level, t0.id, t0.description, t0.name, >>>>>>>> t0.version FROM domain t0 WHERE (t0.name = ?) AND (t0.level = ?) >>>> [bind: >>>>>>>> 1->name:'CayenneModeler', 2->level:0] >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: === returned 1 row. - took 0 ms. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: --- will run 1 query. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: SELECT t0.key_value_pairs, t0.domain_id, t0.key, t0.id FROM >>>>>>>> domain_preference t0 JOIN domain t1 ON (t0.domain_id = t1.id) WHERE >>>>>>> (t0.key >>>>>>>> = ?) AND (t0.domain_id = ?) ORDER BY t1.level DESC LIMIT 1 OFFSET 0 >>>>>>> [bind: >>>>>>>> 1->key:'recent.strategies', 2->domain_id:217] >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: === returned 1 row. - took 0 ms. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: --- will run 1 query. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: SELECT t0.parent_id, t0.level, t0.id, t0.description, t0.name, >>>>>>>> t0.version FROM domain t0 WHERE (t0.name = ?) AND (t0.level = ?) >>>> [bind: >>>>>>>> 1->name:'CayenneModeler', 2->level:0] >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: === returned 1 row. - took 0 ms. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: --- will run 1 query. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: --- transaction started. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: SELECT t0.key_value_pairs, t0.domain_id, t0.key, t0.id FROM >>>>>>>> domain_preference t0 JOIN domain t1 ON (t0.domain_id = t1.id) WHERE >>>>>>> (t0.key >>>>>>>> = ?) AND (t0.domain_id = ?) ORDER BY t1.level DESC LIMIT 1 OFFSET 0 >>>>>>> [bind: >>>>>>>> 1->key:'recent.strategies', 2->domain_id:217] - prepared in 15 ms. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: === returned 1 row. - took 15 ms. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: +++ transaction committed. >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> DEBUG: searching for resource under: >>>>>>>> org/apache/cayenne/dba/sqlserver/types.xml >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> INFO: Detected and installed adapter: >>>>>>>> org.apache.cayenne.dba.sqlserver.SQLServerAdapter >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> DEBUG: will show progress... >>>>>>>> Nov 2, 2010 5:14:55 PM >>>>>>>> DEBUG: task still in progress, will show progress dialog... >>>>>>> >>>>>>> >>>>>> >>>> >>>> >> >
