Return-Path: Delivered-To: apmail-cayenne-user-archive@www.apache.org Received: (qmail 58561 invoked from network); 8 Nov 2010 00:03:40 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 8 Nov 2010 00:03:40 -0000 Received: (qmail 35522 invoked by uid 500); 8 Nov 2010 00:04:11 -0000 Delivered-To: apmail-cayenne-user-archive@cayenne.apache.org Received: (qmail 35453 invoked by uid 500); 8 Nov 2010 00:04:10 -0000 Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@cayenne.apache.org Delivered-To: mailing list user@cayenne.apache.org Received: (qmail 35445 invoked by uid 99); 8 Nov 2010 00:04:10 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 08 Nov 2010 00:04:10 +0000 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: local policy) Received: from [208.78.103.231] (HELO vorsha.objectstyle.org) (208.78.103.231) by apache.org (qpsmtpd/0.29) with SMTP; Mon, 08 Nov 2010 00:04:06 +0000 Received: (qmail 6863 invoked from network); 8 Nov 2010 00:03:44 -0000 Received: from unknown (HELO ?IPv6:::1?) (127.0.0.1) by localhost with SMTP; 8 Nov 2010 00:03:44 -0000 Content-Type: text/plain; charset=us-ascii Mime-Version: 1.0 (Apple Message framework v1081) Subject: Re: Reengineer Database Schema not generating anything From: Andrus Adamchik In-Reply-To: Date: Sun, 7 Nov 2010 19:03:44 -0500 Content-Transfer-Encoding: quoted-printable Message-Id: <33691622-B452-44C4-BB51-C292BFC106B2@objectstyle.org> References: <4337D7C9-3234-4B28-8B17-116EE802F7E9@objectstyle.org> <9A7E8491-D727-4FED-9B30-23CD5097DA6F@objectstyle.org> To: user@cayenne.apache.org X-Mailer: Apple Mail (2.1081) 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=3Dy,transport=3Ddt_socket,address=3D4142,suspend=3Dn = -jar CayenneModeler.jar=20 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: >=20 > * >=20 > public* *void* displayDbProperties(){ >=20 > java.sql.DatabaseMetaData dm =3D *null*; >=20 > java.sql.ResultSet rs =3D *null*; >=20 > *try*{ >=20 > con=3D *this*.getConnection(); >=20 > *if*(con!=3D*null*){ >=20 > dm =3D con.getMetaData(); >=20 > System.*out*.println("Driver Information"); >=20 > System.*out*.println("\tDriver Name: "+ dm.getDriverName()); >=20 > System.*out*.println("\tDriver Version: "+ dm.getDriverVersion ()); >=20 > System.*out*.println("\nDatabase Information "); >=20 > System.*out*.println("\tDatabase Name: "+ = dm.getDatabaseProductName()); >=20 > System.*out*.println("\tDatabase Version: "+ > dm.getDatabaseProductVersion()); >=20 > System.*out*.println("Avalilable Catalogs "); >=20 > rs =3D dm.getCatalogs(); >=20 > *while*(rs.next()){ >=20 > System.*out*.println("\tcatalog: "+ rs.getString(1)); > } >=20 >=20 > String[] types =3D {"TABLE", "VIEW"}; >=20 > rstables =3D dm.getTables(*null*, "%", "%", types); >=20 > *while*(rstables.next()){ >=20 > System.*out*.println("\ttables: " + rstables.getString(3) + "\t" + > rstables.getString(2) + "\t" + rstables.getString(1) + "\t" + > rstables.getString(4) ); >=20 > } >=20 > rs.close(); >=20 > rs =3D *null*; >=20 > closeConnection(); >=20 > }*else* System.*out*.println("Error: No active Connection"); >=20 > }*catch*(Exception e){ >=20 > e.printStackTrace(); >=20 > } >=20 > dm=3D*null*; >=20 > } > 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: >=20 >=20 > Connection Successful! >=20 > Driver Information >=20 > Driver Name: Microsoft SQL Server JDBC Driver 3.0 >=20 > Driver Version: 3.0.1301.101 >=20 > Database Information >=20 > Database Name: Microsoft SQL Server >=20 > Database Version: 10.00.1600 >=20 > Avalilable Catalogs >=20 > ***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?? >=20 >=20 > On Wed, Nov 3, 2010 at 5:36 PM, Andrus Adamchik = wrote: >=20 >> At the end reverse engineering in Cayenne is based on JDBC API. More >> specifically it invokes DatabaseMetaData.getTables(..) method: >>=20 >>=20 >> = http://download.oracle.com/javase/6/docs/api/java/sql/DatabaseMetaData.htm= l#getTables%28java.lang.String,%20java.lang.String,%20java.lang.String,%20= java.lang.String[]%29 >>=20 >> 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. >>=20 >> I guess one way to debug it is to write a simple JDBC test to see if = the >> above returns any data. >>=20 >> Andrus >>=20 >>=20 >>=20 >> On Nov 3, 2010, at 6:19 PM, Mark Fischer wrote: >>=20 >>> 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 >> wrote: >>>=20 >>>> 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. >>>>=20 >>>> Thanks, >>>> Mark >>>>=20 >>>> On Tue, Nov 2, 2010 at 9:17 PM, Andrus Adamchik < >> andrus@objectstyle.org>wrote: >>>>=20 >>>>> Hi Mark, >>>>>=20 >>>>> 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... >>>>>=20 >>>>> Andrus >>>>>=20 >>>>>=20 >>>>> On Nov 2, 2010, at 6:16 PM, Mark Fischer wrote: >>>>>=20 >>>>>> 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. >>>>>>=20 >>>>>> I create a node and have the DataSource >>>>>> Factory org.apache.cayenne.conf.DriverDataSourceFactory selected. >>>>>>=20 >>>>>> With the node selected I go to Tools -> Reengineer Database = Schema. >>>>>>=20 >>>>>> 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. >>>>>>=20 >>>>>> 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. >>>>>>=20 >>>>>> 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. >>>>>>=20 >>>>>> Here is the text in the console while I use the Reeingineer = menuitem: >>>>>>=20 >>>>>> 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 =3D ?, x =3D ? WHERE id =3D = ? >>>>>> 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: =3D=3D=3D updated 1 row. >>>>>> Nov 2, 2010 5:14:47 PM >>>>>> INFO: UPDATE domain_preference SET key_value_pairs =3D ? WHERE id = =3D ? >>>>>> 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: =3D=3D=3D 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 =3D t1.id) = WHERE >>>>> (t0.key >>>>>> =3D ?) AND (t0.domain_id =3D ?) 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: =3D=3D=3D 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 =3D ?) AND (t0.level =3D = ?) >> [bind: >>>>>> 1->name:'CayenneModeler', 2->level:0] >>>>>> Nov 2, 2010 5:14:50 PM >>>>>> INFO: =3D=3D=3D 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 =3D t1.id) = WHERE >>>>> (t0.key >>>>>> =3D ?) AND (t0.domain_id =3D ?) 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: =3D=3D=3D 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 =3D ?) AND (t0.level =3D = ?) >> [bind: >>>>>> 1->name:'CayenneModeler', 2->level:0] >>>>>> Nov 2, 2010 5:14:50 PM >>>>>> INFO: =3D=3D=3D 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: =3D=3D=3D 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 =3D ?) AND (t0.level =3D = ?) >> [bind: >>>>>> 1->name:'CayenneModeler', 2->level:0] >>>>>> Nov 2, 2010 5:14:53 PM >>>>>> INFO: =3D=3D=3D 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 =3D ? [bind: >>>>> 1->parent_id:200] >>>>>> Nov 2, 2010 5:14:53 PM >>>>>> INFO: =3D=3D=3D 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 =3D t1.id) = WHERE >>>>> (t0.key >>>>>> =3D ?) AND (t0.domain_id =3D ?) 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: =3D=3D=3D 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 =3D ?) AND (t0.level =3D = ?) >> [bind: >>>>>> 1->name:'CayenneModeler', 2->level:0] >>>>>> Nov 2, 2010 5:14:55 PM >>>>>> INFO: =3D=3D=3D 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 =3D t1.id) = WHERE >>>>> (t0.key >>>>>> =3D ?) AND (t0.domain_id =3D ?) 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: =3D=3D=3D 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 =3D ?) AND (t0.level =3D = ?) >> [bind: >>>>>> 1->name:'CayenneModeler', 2->level:0] >>>>>> Nov 2, 2010 5:14:55 PM >>>>>> INFO: =3D=3D=3D 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 =3D t1.id) = WHERE >>>>> (t0.key >>>>>> =3D ?) AND (t0.domain_id =3D ?) 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: =3D=3D=3D 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... >>>>>=20 >>>>>=20 >>>>=20 >>=20 >>=20