Announcement Announcement Module
Collapse
No announcement yet.
Deletion of Oracle Table during JUnit tests Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Deletion of Oracle Table during JUnit tests

    i successfully went thru the wedding tutorial and was able to run the app. now i'm on to my own stuff. i have a table that is defined as...

    create table david.setting
    ( id number not null
    ,setting_name varchar2(100) not null
    ,setting_value varchar2(10)
    ,date_added date default sysdate
    ,primary key (id)
    );

    as you will see in my log.roo i entered this table (entity) and fields...

    // Spring Roo 1.0.1.RELEASE [rev 601] log opened at 2010-01-30 17:44:56
    hint
    project --topLevelPackage com.mobley.taglib
    hint
    persistence setup --provider HIBERNATE --database ORACLE
    hint
    entity --class Setting
    hint
    database properties list
    database properties set --key database.username --value david
    database properties set --key database.password --value xxxxxx
    field number id --notNull --type java.math.BigDecimal
    field string setting_name --notNull --sizeMin 1 --sizeMax 100
    field string setting_value --sizeMin 0 --sizeMax 10
    field date date_added --type java.util.Date
    test integration
    controller scaffold ~.web.TaglibController
    exit
    // Spring Roo 1.0.1.RELEASE [rev 601] log closed at 2010-01-30 17:49:23

    i ran "mvn eclipse:eclipse" (as i did w/ the wedding tutorial)..added ojdbc:ojdbc:14 (oracle's ojdbc14.jar). each time i run "clean install" i fail on the tests...

    Tests run: 9, Failures: 0, Errors: 8, Skipped: 0, Time elapsed: 1.799 sec <<< FAILURE!
    testCountSettings(com.mobley.taglib.SettingIntegra tionTest) Time elapsed: 0.202 sec <<< ERROR!
    org.springframework.orm.jpa.JpaSystemException: org.hibernate.exception.SQLGrammarException: could not execute query; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
    ...

    i would love to know what query...not sure where to look, or what to enable to see the log of the sql..at any rate it (or some ghost) then deletes my table!!! luckily this is a local "play" table that i can (and do!) recreate.

    help!!! who and why is this table being deleted?? how do i locate the offending query???

    btw: great tool...just what i've been needing (minus the "table delete on test failure" feature!).

    David

  • #2
    more....

    i found (in Setting_Roo_Entity.aj) the following sql....

    public static long Setting.countSettings() {
    return (Long) entityManager().createQuery(
    "select count(o) from Setting o").getSingleResult();
    }

    public static List<Setting> Setting.findAllSettings() {
    return entityManager().createQuery("select o from Setting o").getResultList();
    }

    pls note that that is a lower case "o" (oh!) in "count(o)" and "select o from"...which is not valid!

    where did this come from???

    Comment


    • #3
      Hi David,

      pls note that that is a lower case "o" (oh!) in "count(o)" and "select o from"...which is not valid!
      Can you elaborate why this is not valid for Oracle so we can look into making it valid?

      Cheers,
      Stefan

      Comment


      • #4
        well, the short answer is "select count(*)..." or even "select count(0)..." (that's a zero!) are valid sql stmts to get counts of #rows of a table. i know this is valid for SqlServer, postgreSql, DB2, and Oracle. i've never seen the syntax "select count(o)..." (lower or even upper case letter "o"). perhaps there is an RMDBS where that would work.

        i did, however, notice in my "wedding" eclipse project that i have the same syntax in similar .java (or .aj) files...and wedding worked for me! so perhaps this is not the problem. dunno.

        Comment


        • #5
          well, the short answer is "select count(*)..." or even "select count(0)..." (that's a zero!) are valid sql stmts to get counts of #rows of a table. i know this is valid for SqlServer, postgreSql, DB2, and Oracle. i've never seen the syntax "select count(o)..." (lower or even upper case letter "o"). perhaps there is an RMDBS where that would work.
          Actually, I think you are misunderstanding something here. The queries that Roo generates are not SQL queries but JPA queries (called JQL). So the syntax needs to be compliant with JQL and not SQL. The count(*) is actually invalid as per JQL specification. See the following EBNF expression taken directly from the JSR 220 specification:

          aggregate_expression ::=
          { AVG | MAX | MIN | SUM } ([DISTINCT] state_field_path_expression) | COUNT ([DISTINCT] identification_variable | state_field_path_expression |
          single_valued_association_path_expression)
          So, the query "select count(myVariableName) from Setting myVariableName"
          is absolutely correct as per JPA spec.

          HTH,
          Stefan

          Comment


          • #6
            Ok...at least i know now why the Wedding example worked w/ similar syntax. i still need to resolve the "invalid char" error that prevents the JUnit tests from connecting...and why, when that happens, Roo decides to drop my table. :-(

            any ideas on where to look for the query that has the "invalid char"

            thanks $1M

            Comment


            • #7
              deletion of table not related to JUnit running

              i'm back! i have regenerated my simple web-app following the wedding example (almost verbatum). since i was having trouble ("invalid char in query") running the JUnit testing i decided to skip testing ("mvn clean install -Dmaven.test.skip=true") and continue on.

              the website actually comes up with my entity! yet it fails when i try to add/delete/update...because my table has been dropped! i found a "remove()" function and commented out the body, but that made no difference.

              i have uploaded my code: http://www.mobley.com/roo/Taglib.tar.gz
              and my ddl: http://www.mobley.com/roo/setting.sql

              i see no indication as to why this is happening. must be something i'm doing, as i said, i followed the wedding example, yet no one else seems to have this problem...

              help, all knowing Roo Gods.

              Comment


              • #8
                aha!

                after reading several posts i realize that there is no reverse engineering functionality in Roo (Yet!). What i had done was to run DDL creating the table...created my project in Roo w/ the entity matching the table i had manually created.

                what i should have done (and now do): create the project specifying the entity i need and, voila, it's there.

                it's odd that Roo seems to ignore most of the "Field xxx" specifications (notNull, sizeMax) but otherwise it's a good tool.

                Comment


                • #9
                  i realize that there is no reverse engineering functionality in Roo (Yet!)
                  Please go ahead and vote on the Jira ticket .

                  Roo seems to ignore most of the "Field xxx" specifications (notNull, sizeMax)
                  Can you elaborate on that? What exactly is not working as expected?

                  -Stefan

                  Comment


                  • #10
                    from roo.log....

                    entity --class ~.domain.Setting
                    field string setting_name --notNull --sizeMin 1 --sizeMax 100
                    field string setting_value --notNull --sizeMin 1 --sizeMax 10

                    from sql (after table creation)...

                    create table "david"."setting" (
                    "ID" number(19,0) not null enable
                    ,"VERSION number(10,0)
                    ,"SETTING_NAME" varchar2(255 byte)
                    ,"SETTING_VALUE" varchar2(255 byte)
                    ,primary key ("ID")
                    ...

                    you'll notice the "--notNull" and "--sizeMax()" were ignored.

                    Comment

                    Working...
                    X