Hsqldb 2.2.8 View dont display updated data

Help
2012-10-22
2014-01-19
  • Lucille Wilson

    Lucille Wilson - 2012-10-22

    I created a view:
    CREATE VIEW rr_cache_pagelist_no_standby AS select page.* from rr_cache_pagelist page join rr_cache_jobs jobs on (page.job_id = jobs.job_id) where jobs.standby = false"

    At this point the table had x records.

    I inserted some records into the rr_cache_pagelist table and I updated some records in the table.
    At this point the underlying table had x+2 records.

    I selected on rr_cache_pagelist_no_standby (the view) and I received x records.  After updating the underlying table, and then selecting on the view, the view does not show the updated table.

     
  • Fred Toussi

    Fred Toussi - 2012-10-22

    Have you tried executing the SELECT statement in the view directly?

    A view is processed each time you execute it. Therefore it sees the current state of the records. Do the new records match the WHERE clause?

     
  • Lucille Wilson

    Lucille Wilson - 2012-10-29

    Please close this.  I made a mistake and when I inserted the records to conform to the join properly, the records were displayed in the view.  My apologies.

     
  • Lucille Wilson

    Lucille Wilson - 2012-11-26

    Upon further testing it seems I am correct. 
    The sequence is this:
    1. The underlying table: rr_cache_pagelist is empty.  The rr_cache_jobs table has one record (and it's standby field is false).
    2.
    CREATE VIEW rr_cache_pagelist_no_standby AS select page.* from rr_cache_pagelist page join rr_cache_jobs jobs on (page.job_id = jobs.job_id) where jobs.standby = false"

    3. Insert records into rr_cache_pagelist. 
    4. Select * from rr_cache_pagelist_no_standby.  The view is empty.  This is wreaking havoc in my code.  I would like to rely on this view.

     
  • Fred Toussi

    Fred Toussi - 2012-11-26

    Please report a list of statements, including CREATE TABLE and INSERT statements, which can be used to test what you are saying.

     
  • Lucille Wilson

    Lucille Wilson - 2012-11-26

    I create the in-memory database through hibernate. 
    1. Create for rr_cache_jobs:

    <hibernate-mapping>
        <class name=\"com.rampageinc.rrserverdatabase.hiber.cache.RrCacheJobs\" table=\"rr_cache_jobs\">
           <id name=\"jobId\" column=\"job_id\" type=\"java.lang.Integer\">
       <generator class=\"assigned\">
           </generator>
        </id>
               
            <property name=\"customerId\" type=\"java.lang.Integer\" index = \"jobs_customerid_idx\">
                <column name=\"customer_id\" />
            </property>
            <property name=\"name\" type=\"java.lang.String\">
                <column name=\"name\" />
            </property>
            <property name=\"security\" type=\"java.lang.String\">
                <column name=\"security\" />
            </property>
            <property name=\"pageApproval\" type=\"java.lang.String\">
                <column name=\"page_approval\" />
            </property>
            <property name=\"bookApproval\" type=\"java.lang.String\">
                <column name=\"book_approval\" />
            </property>
            <property name=\"type\" type=\"java.lang.String\">
                <column name=\"type\" />
            </property>
            <property name=\"active\" type=\"java.lang.String\">
                <column name=\"active\" />
            </property>
            <property name=\"customerReference\" type=\"java.lang.String\">
                <column name=\"customer_reference\" />
            </property>
            <property name=\"internalId\" type=\"java.lang.Integer\" index=\"jobs_internalid_idx\">
                <column name=\"internal_id\" />
            </property>
            <property name=\"publish\" type=\"java.lang.String\">
                <column name=\"publish\" />
            </property>
            <property name=\"hasJdf\" type=\"java.lang.String\">
                <column name=\"has_jdf\" />
            </property>
            <property name=\"done\" type=\"java.lang.String\">
                <column name=\"done\" />
            </property>
           
           
            <property name=\"createTimestamp\" type=\"timestamp\">
                <column name=\"create_timestamp\" />
            </property>
            <property name=\"modifiedTimestamp\" type=\"timestamp\">
                <column name=\"modified_timestamp\" />
            </property>
            <property name=\"locked\" type=\"java.lang.Boolean\">
                <column name=\"locked\" />
            </property>
            <property name=\"lockedBy\" type=\"java.lang.String\">
                <column name=\"locked_by\" />
            </property>
            <property name=\"lockedReason\" type=\"java.lang.String\">
                <column name=\"locked_reason\" />
            </property>
            <property name=\"lockedTimestamp\" type=\"timestamp\">
                <column name=\"locked_timestamp\" />
            </property>
             <property name=\"deleteTimestamp\" type=\"timestamp\">
                <column name=\"delete_timestamp\" />
            </property>
            <property name=\"dueDateTimestamp\" type=\"timestamp\">
                <column name=\"due_date_timestamp\" />
            </property>
            <property name=\"dueDateType\" type=\"java.lang.Integer\">
                <column name=\"due_date_type\" />
            </property>
            <property name=\"priority\" type=\"java.lang.Integer\">
                <column name=\"priority\" />
            </property>
            <property name=\"jobMode\" type=\"java.lang.Integer\">
                <column name=\"job_mode\" />
            </property>
            <property name=\"jdfJob\" type=\"java.lang.Boolean\">
                <column name=\"jdf_job\" />
            </property>
            <property name=\"standby\" type=\"java.lang.Boolean\" index = \"job_standby_idx\">
                <column name=\"standby\" />
            </property>
            <property name=\"remoteJobSubmit\" type=\"java.lang.Boolean\">
                <column name=\"remote_job_submit\" />
            </property>
            <property name=\"workflowJob\" type=\"java.lang.Boolean\">
                <column name=\"workflow_job\" />
            </property>
            <property name=\"autoPageMapped\" type=\"java.lang.Boolean\">
                <column name=\"auto_page_mapped\" />
            </property>
            <property name=\"printableTemplate\" type=\"java.lang.Boolean\">
                <column name=\"printable_template\" />
            </property>
            <property name=\"printableJob\" type=\"java.lang.Boolean\">
                <column name=\"printable_job\" />
            </property>
            <property name=\"varData\" type=\"java.lang.Boolean\">
                <column name=\"var_data\" />
            </property>
            <property name=\"autoOutput\" type=\"java.lang.Boolean\">
                <column name=\"auto_output\" />
            </property>
            <property name=\"autoOutputAutoApprove\" type=\"java.lang.Boolean\">
                <column name=\"auto_output_auto_approve\" />
            </property>
            <property name=\"autoOutputAutoReleaseFlats\" type=\"java.lang.Boolean\">
                <column name=\"auto_output_auto_release_flats\" />
            </property>
            <property name=\"smartJdf\" type=\"java.lang.Boolean\">
                <column name=\"smart_jdf\" />
            </property>
            <property name=\"smartTask\" type=\"java.lang.Boolean\">
                <column name=\"smart_task\" />
            </property>
            <property name=\"status\" type=\"java.lang.Character\">
                <column name=\"status\" />
            </property>
            <property name=\"registrarUserId\" type=\"java.lang.Integer\">
                <column name=\"registrar_user_id\" />
            </property>
            <property name=\"autoApprove\" type=\"java.lang.Boolean\">
                <column name=\"auto_approve\" />
            </property>
            <property name=\"autoPublish\" type=\"java.lang.Boolean\">
                <column name=\"auto_publish\" />
            </property>
            <property name=\"autoReleaseFlats\" type=\"java.lang.Boolean\">
                <column name=\"auto_release_flats\" />
            </property>
            <property name=\"autoWebEnable\" type=\"java.lang.Boolean\">
                <column name=\"auto_web_enable\" />
            </property>
        </class>
    </hibernate-mapping>

    2. Create for rr_cache_pagelist:

    <hibernate-mapping>
    <class name=\"com.rampageinc.rrserverdatabase.hiber.cache.RrCachePagelist\" table=\"rr_cache_pagelist\">
           
            <id name=\"pageId\" column=\"page_id\" type=\"java.lang.Integer\">
       <generator class=\"assigned\">
           </generator>
        </id>
         

            <property name=\"name\" type=\"java.lang.String\">
                <column name=\"name\" />
            </property>
            <property name=\"job\" type=\"java.lang.String\">
                <column name=\"job\" />
            </property>
            <property name=\"userId\" type=\"java.lang.String\">
                <column name=\"user_id\"  />
            </property>
            <property name=\"customerId\" type=\"java.lang.Integer\" index=\"page_customerid_idx\">
                <column name=\"customer_id\" />
            </property>
            <property name=\"fileState\" type=\"java.lang.Character\" index=\"page_filestate_idx\">
                <column name=\"file_state\"   />
            </property>
            <property name=\"prescanQueue\" type=\"java.lang.Character\" index=\"page_prescanqueue_idx\">
                <column name=\"prescan_queue\"  />
            </property>
            <property name=\"fontQueue\" type=\"java.lang.Character\" index=\"page_font_queue_idx\">
                <column name=\"font_queue\"   />
            </property>
            <property name=\"spotQueue\" type=\"java.lang.Character\">
                <column name=\"spot_queue\"   />
            </property>
            <property name=\"opiQueue\" type=\"java.lang.Character\">
                <column name=\"opi_queue\"  />
            </property>
            <property name=\"trapQueue\" type=\"java.lang.Character\">
                <column name=\"trap_queue\"   />
            </property>
            <property name=\"ripQueue\" type=\"java.lang.Character\" index=\"page_rip_queue_idx\">
                <column name=\"rip_queue\"  />
            </property>
            <property name=\"fpoQueue\" type=\"java.lang.Character\" index=\"page_fpo_queue_idx\">
                <column name=\"fpo_queue\"  />
            </property>
            <property name=\"deviceProofQueue\" type=\"java.lang.Character\" index = \"page_device_proof_queue_idx\">
                <column name=\"device_proof_queue\"   />
            </property>
            <property name=\"plotQueue\" type=\"java.lang.Character\" index=\"page_plotqueue_idx\">
                <column name=\"plot_queue\"  />
            </property>
            <property name=\"workflow\" type=\"java.lang.String\">
                <column name=\"workflow\" />
            </property>
            <property name=\"revision\" type=\"java.lang.Integer\">
                <column name=\"revision\"  />
            </property>
            <property name=\"type\" type=\"java.lang.String\">
                <column name=\"type\" />
            </property>
            <property name=\"state\" type=\"java.lang.String\">
                <column name=\"state\" />
            </property>
            <property name=\"annotated\" type=\"java.lang.String\">
                <column name=\"annotated\"  />
            </property>
            <property name=\"published\" type=\"java.lang.String\">
                <column name=\"published\" />
            </property>
          
            <property name=\"jobId\" type=\"java.lang.Integer\" index=\"page_jobid_idx\">
                <column name=\"job_id\" />
            </property>

            <property name=\"boxId\" type=\"java.lang.String\">
                <column name=\"box_id\" />
            </property>
            <property name=\"pdfPrescanQueue\" type=\"java.lang.Character\" index=\"page_pdfprescanqueue_idx\">
                <column name=\"pdf_prescan_queue\" />
            </property>
            <property name=\"monitorProofQueue\" type=\"java.lang.Character\" index=\"page_monitorproofqueue_idx\">
                <column name=\"monitor_proof_queue\" />
            </property>
            <property name=\"version\" type=\"java.lang.Integer\">
                <column name=\"version\" />
            </property>
            <property name=\"inkzoneQueue\" type=\"java.lang.Character\" index=\"page_inkzonequeue_idx\">
                <column name=\"inkzone_queue\" />
            </property>
            <property name=\"proof1DeviceId\" type=\"java.lang.Integer\">
                <column name=\"proof1_device_id\" />
            </property>
            <property name=\"proof2Queue\" type=\"java.lang.Character\" index=\"page_proof2queue_idx\">
                <column name=\"proof2_queue\" />
            </property>
            <property name=\"proof2DeviceId\" type=\"java.lang.Integer\">
                <column name=\"proof2_device_id\" />
            </property>
            <property name=\"proof3Queue\" type=\"java.lang.Character\" index=\"page_proof3queue_idx\">
                <column name=\"proof3_queue\" />
            </property>
            <property name=\"proof3DeviceId\" type=\"java.lang.Integer\">
                <column name=\"proof3_device_id\" />
            </property>
            <property name=\"screenQueue\" type=\"java.lang.Character\" index=\"page_screenqueue_idx\">
                <column name=\"screenqueue\" />
            </property>
            <property name=\"plotDeviceId\" type=\"java.lang.Integer\">
                <column name=\"plot_device_id\" />
            </property>
            <property name=\"workflowProcess\" type=\"java.lang.Integer\">
                <column name=\"workflow_process\" />
            </property>
            <property name=\"createTimestamp\" type=\"timestamp\">
                <column name=\"create_timestamp\" />
            </property>
            <property name=\"modifiedTimestamp\" type=\"timestamp\">
                <column name=\"modified_timestamp\" />
            </property>
            <property name=\"locked\" type=\"java.lang.Boolean\">
                <column name=\"locked\" />
            </property>
            <property name=\"lockedBy\" type=\"java.lang.String\">
                <column name=\"locked_by\" />
            </property>
            <property name=\"lockedReason\" type=\"java.lang.String\">
                <column name=\"locked_reason\" />
            </property>
            <property name=\"priority\" type=\"java.lang.Integer\">
                <column name=\"priority\" />
            </property>
            <property name=\"webAccess\" type=\"java.lang.Boolean\">
                <column name=\"web_access\" />
            </property>
            <property name=\"rdm\" type=\"java.lang.Boolean\">
                <column name=\"rdm\" />
            </property>
            <property name=\"approved\" type=\"java.lang.Boolean\">
                <column name=\"approved\" />
            </property>
            <property name=\"fpoFlag\" type=\"java.lang.Boolean\">
                <column name=\"fpo_flag\" />
            </property>
            <property name=\"jdf\" type=\"java.lang.Boolean\">
                <column name=\"jdf\" />
            </property>
            <property name=\"contentsApproved\" type=\"java.lang.Boolean\">
                <column name=\"contents_approved\" />
            </property>
            <property name=\"largeFormat\" type=\"java.lang.Boolean\">
                <column name=\"large_format\" />
            </property>
            <property name=\"rjs\" type=\"java.lang.Boolean\">
                <column name=\"rjs\" />
            </property>
            <property name=\"rework\" type=\"java.lang.Boolean\">
                <column name=\"rework\" />
            </property>
            <property name=\"asr\" type=\"java.lang.Boolean\">
                <column name=\"asr\" />
            </property>
            <property name=\"publishedProofDirty\" type=\"java.lang.Boolean\">
                <column name=\"published_proof_dirty\" />
            </property>
            <property name=\"mixedScreens\" type=\"java.lang.Boolean\">
                <column name=\"mixed_screens\" />
            </property>
            <property name=\"deimpose\" type=\"java.lang.Boolean\">
                <column name=\"deimpose\" />
            </property>
            <property name=\"reimpose\" type=\"java.lang.Boolean\">
                <column name=\"reimpose\" />
            </property>
            <property name=\"directRip\" type=\"java.lang.Boolean\">
                <column name=\"direct_rip\" />
            </property>
            <property name=\"ripIcc\" type=\"java.lang.Boolean\">
                <column name=\"rip_icc\" />
            </property>
            <property name=\"purgeable\" type=\"java.lang.Boolean\">
                <column name=\"purgeable\" />
            </property>
            <property name=\"prescanWarn\" type=\"java.lang.Boolean\">
                <column name=\"prescan_warn\" />
            </property>
            <property name=\"inkdrop\" type=\"java.lang.Boolean\">
                <column name=\"inkdrop\" />
            </property>
            <property name=\"xtask\" type=\"java.lang.Boolean\">
                <column name=\"xtask\" />
            </property>
            <property name=\"pdfFixedUp\" type=\"java.lang.Boolean\">
                <column name=\"pdf_fixedup\" />
            </property>
            <property name=\"pitStopped\" type=\"java.lang.Boolean\">
                <column name=\"pit_stopped\" />
            </property>
            <property name=\"norm\" type=\"java.lang.Boolean\">
                <column name=\"norm\" />
            </property>
            <property name=\"workflowIndex\" type=\"java.lang.Integer\">
                <column name=\"workflow_index\" />
            </property>
            <property name=\"unpublishedProof\" type=\"java.lang.Boolean\">
                <column name=\"unpublished_proof\" />
            </property>
            <property name=\"flat\" type=\"java.lang.Boolean\">
                <column name=\"flat\" />
            </property>
            <property name=\"lockedTimestamp\" type=\"timestamp\">
                <column name=\"locked_timestamp\" />
            </property>
            <property name=\"registrarUserId\" type=\"java.lang.Integer\" index=\"page_registraruserid_idx\">
                <column name=\"registrar_user_id\" />
            </property>
            <property name=\"pdfFixupOnly\" type=\"java.lang.Boolean\">
                <column name=\"pdffixuponly\" />
            </property>
            <property name=\"filenameExtension\" type=\"java.lang.String\">
                <column name=\"filename_extension\" />
            </property>
            <property name=\"pageRank\" type=\"java.lang.Integer\" index=\"page_pagerank_idx\">
                <column name=\"rr_page_rank\" />
            </property>
            <property name=\"pageFilter\" type=\"java.lang.Boolean\">
                <column name=\"rr_page_filter\" />
            </property>
            <property name=\"originalPageName\" type=\"java.lang.String\">
                <column name=\"original_page_name\" />
            </property>
          
        </class>
    </hibernate-mapping>

    3. hbm file for the view:

    <hibernate-mapping>
      <class name=\"com.rampageinc.rrserverdatabase.hiber.cache.RrCachePagelistNoStandby\" table=\"rr_cache_pagelist_no_standby\">
            <id name=\"pageId\" column=\"page_id\" type=\"java.lang.Integer\">
       <generator class=\"assigned\">
           </generator>
        </id>

            <property name=\"name\" type=\"java.lang.String\">
                <column name=\"name\" />
            </property>
            <property name=\"job\" type=\"java.lang.String\">
                <column name=\"job\" />
            </property>
            <property name=\"userId\" type=\"java.lang.String\">
                <column name=\"user_id\"  />
            </property>
            <property name=\"customerId\" type=\"java.lang.Integer\">
                <column name=\"customer_id\" />
            </property>
            <property name=\"fileState\" type=\"java.lang.Character\" >
                <column name=\"file_state\"   />
            </property>
            <property name=\"prescanQueue\" type=\"java.lang.Character\" >
                <column name=\"prescan_queue\"  />
            </property>
            <property name=\"fontQueue\" type=\"java.lang.Character\">
                <column name=\"font_queue\"   />
            </property>
            <property name=\"spotQueue\" type=\"java.lang.Character\">
                <column name=\"spot_queue\"   />
            </property>
            <property name=\"opiQueue\" type=\"java.lang.Character\">
                <column name=\"opi_queue\"  />
            </property>
            <property name=\"trapQueue\" type=\"java.lang.Character\">
                <column name=\"trap_queue\"   />
            </property>
            <property name=\"ripQueue\" type=\"java.lang.Character\">
                <column name=\"rip_queue\"  />
            </property>
            <property name=\"fpoQueue\" type=\"java.lang.Character\">
                <column name=\"fpo_queue\"  />
            </property>
            <property name=\"deviceProofQueue\" type=\"java.lang.Character\">
                <column name=\"device_proof_queue\"   />
            </property>
            <property name=\"plotQueue\" type=\"java.lang.Character\">
                <column name=\"plot_queue\"  />
            </property>
            <property name=\"workflow\" type=\"java.lang.String\">
                <column name=\"workflow\" />
            </property>
            <property name=\"revision\" type=\"java.lang.Integer\">
                <column name=\"revision\"  />
            </property>
            <property name=\"type\" type=\"java.lang.String\">
                <column name=\"type\" />
            </property>
            <property name=\"state\" type=\"java.lang.String\">
                <column name=\"state\" />
            </property>
            <property name=\"annotated\" type=\"java.lang.String\">
                <column name=\"annotated\"  />
            </property>
            <property name=\"published\" type=\"java.lang.String\">
                <column name=\"published\" />
            </property>
          
            <property name=\"jobId\" type=\"java.lang.Integer\">
                <column name=\"job_id\" />
            </property>

            <property name=\"boxId\" type=\"java.lang.String\">
                <column name=\"box_id\" />
            </property>
            <property name=\"pdfPrescanQueue\" type=\"java.lang.Character\">
                <column name=\"pdf_prescan_queue\" />
            </property>
            <property name=\"monitorProofQueue\" type=\"java.lang.Character\">
                <column name=\"monitor_proof_queue\" />
            </property>
            <property name=\"version\" type=\"java.lang.Integer\">
                <column name=\"version\" />
            </property>
            <property name=\"inkzoneQueue\" type=\"java.lang.Character\">
                <column name=\"inkzone_queue\" />
            </property>
            <property name=\"proof1DeviceId\" type=\"java.lang.Integer\">
                <column name=\"proof1_device_id\" />
            </property>
            <property name=\"proof2Queue\" type=\"java.lang.Character\">
                <column name=\"proof2_queue\" />
            </property>
            <property name=\"proof2DeviceId\" type=\"java.lang.Integer\">
                <column name=\"proof2_device_id\" />
            </property>
            <property name=\"proof3Queue\" type=\"java.lang.Character\">
                <column name=\"proof3_queue\" />
            </property>
            <property name=\"proof3DeviceId\" type=\"java.lang.Integer\">
                <column name=\"proof3_device_id\" />
            </property>
            <property name=\"screenQueue\" type=\"java.lang.Character\">
                <column name=\"screenqueue\" />
            </property>
            <property name=\"plotDeviceId\" type=\"java.lang.Integer\">
                <column name=\"plot_device_id\" />
            </property>
            <property name=\"workflowProcess\" type=\"java.lang.Integer\">
                <column name=\"workflow_process\" />
            </property>
            <property name=\"createTimestamp\" type=\"timestamp\">
                <column name=\"create_timestamp\" />
            </property>
            <property name=\"modifiedTimestamp\" type=\"timestamp\">
                <column name=\"modified_timestamp\" />
            </property>
            <property name=\"locked\" type=\"java.lang.Boolean\">
                <column name=\"locked\" />
            </property>
            <property name=\"lockedBy\" type=\"java.lang.String\">
                <column name=\"locked_by\" />
            </property>
            <property name=\"lockedReason\" type=\"java.lang.String\">
                <column name=\"locked_reason\" />
            </property>
            <property name=\"priority\" type=\"java.lang.Integer\">
                <column name=\"priority\" />
            </property>
            <property name=\"webAccess\" type=\"java.lang.Boolean\">
                <column name=\"web_access\" />
            </property>
            <property name=\"rdm\" type=\"java.lang.Boolean\">
                <column name=\"rdm\" />
            </property>
            <property name=\"approved\" type=\"java.lang.Boolean\">
                <column name=\"approved\" />
            </property>
            <property name=\"fpoFlag\" type=\"java.lang.Boolean\">
                <column name=\"fpo_flag\" />
            </property>
            <property name=\"jdf\" type=\"java.lang.Boolean\">
                <column name=\"jdf\" />
            </property>
            <property name=\"contentsApproved\" type=\"java.lang.Boolean\">
                <column name=\"contents_approved\" />
            </property>
            <property name=\"largeFormat\" type=\"java.lang.Boolean\">
                <column name=\"large_format\" />
            </property>
            <property name=\"rjs\" type=\"java.lang.Boolean\">
                <column name=\"rjs\" />
            </property>
            <property name=\"rework\" type=\"java.lang.Boolean\">
                <column name=\"rework\" />
            </property>
            <property name=\"asr\" type=\"java.lang.Boolean\">
                <column name=\"asr\" />
            </property>
            <property name=\"publishedProofDirty\" type=\"java.lang.Boolean\">
                <column name=\"published_proof_dirty\" />
            </property>
            <property name=\"mixedScreens\" type=\"java.lang.Boolean\">
                <column name=\"mixed_screens\" />
            </property>
            <property name=\"deimpose\" type=\"java.lang.Boolean\">
                <column name=\"deimpose\" />
            </property>
            <property name=\"reimpose\" type=\"java.lang.Boolean\">
                <column name=\"reimpose\" />
            </property>
            <property name=\"directRip\" type=\"java.lang.Boolean\">
                <column name=\"direct_rip\" />
            </property>
            <property name=\"ripIcc\" type=\"java.lang.Boolean\">
                <column name=\"rip_icc\" />
            </property>
            <property name=\"purgeable\" type=\"java.lang.Boolean\">
                <column name=\"purgeable\" />
            </property>
            <property name=\"prescanWarn\" type=\"java.lang.Boolean\">
                <column name=\"prescan_warn\" />
            </property>
            <property name=\"inkdrop\" type=\"java.lang.Boolean\">
                <column name=\"inkdrop\" />
            </property>
            <property name=\"xtask\" type=\"java.lang.Boolean\">
                <column name=\"xtask\" />
            </property>
            <property name=\"pdfFixedUp\" type=\"java.lang.Boolean\">
                <column name=\"pdf_fixedup\" />
            </property>
            <property name=\"pitStopped\" type=\"java.lang.Boolean\">
                <column name=\"pit_stopped\" />
            </property>
            <property name=\"norm\" type=\"java.lang.Boolean\">
                <column name=\"norm\" />
            </property>
            <property name=\"workflowIndex\" type=\"java.lang.Integer\">
                <column name=\"workflow_index\" />
            </property>
            <property name=\"unpublishedProof\" type=\"java.lang.Boolean\">
                <column name=\"unpublished_proof\" />
            </property>
            <property name=\"flat\" type=\"java.lang.Boolean\">
                <column name=\"flat\" />
            </property>
            <property name=\"lockedTimestamp\" type=\"timestamp\">
                <column name=\"locked_timestamp\" />
            </property>
            <property name=\"registrarUserId\" type=\"java.lang.Integer\">
                <column name=\"registrar_user_id\" />
            </property>
            <property name=\"pdfFixupOnly\" type=\"java.lang.Boolean\">
                <column name=\"pdffixuponly\" />
            </property>
            <property name=\"filenameExtension\" type=\"java.lang.String\">
                <column name=\"filename_extension\" />
            </property>
            <property name=\"pageRank\" type=\"java.lang.Integer\" >
                <column name=\"rr_page_rank\" />
            </property>
            <property name=\"pageFilter\" type=\"java.lang.Boolean\">
                <column name=\"rr_page_filter\" />
            </property>
    <property name=\"originalPageName\" type=\"java.lang.String\">
                <column name=\"original_page_name\" />
            </property>
          
        <!-  comment only: <query name=\"rr_cache_pagelist_no_standby_query\"><![CDATA]></query> ->
        </class>
    </hibernate-mapping>

    4. hibernate file to point to the hbm.xml files. Note that the schema is created by hibernate:

    <hibernate-configuration>

    <session-factory name=\"session\">
    <!- Database connection settings ->
    <property name=\"connection.driver_class\">org.hsqldb.jdbcDriver</property>
    <property name=\"connection.url\">jdbc:hsqldb:mem:rrcacheserverdb</property>
    <property name=\"connection.username\">sa</property>
    <property name=\"connection.password\"></property>

    <!- c3p0 connection pool parameters ->
    <property name=\"hibernate.c3p0.min_size\">5</property>
    <property name=\"hibernate.c3p0.max_size\">20</property>
    <property name=\"hibernate.c3p0.timeout\">300</property>
    <property name=\"hibernate.c3p0.max_statements\">50</property>
    <property name=\"hibernate.c3p0.idle_test_period\">3000</property>

    <!- SQL dialect ->
    <property name=\"dialect\">org.hibernate.dialect.HSQLDialect</property>

    <!- Enable Hibernate\'s automatic session context management ->
    <property name=\"current_session_context_class\">thread</property>
    <!- have hibernate create the in-memory database schema ->
    <property name=\"hbm2ddl.auto\">create</property>

    <!- hibernate batching parameters ->
    <property name=\"hibernate.jdbc.batch_size\">30</property>
    <property name=\"hibernate.order_inserts\">true</property>
    <property name=\"hibernate.order_updates\">true</property>

    <!- this will show us all sql statements ->
    <property name=\"hibernate.show_sql\">false</property>
    <property name=\"use_sql_comments\">false</property>
    <property name=\"format_sql\">false</property>

    <!- these mappings will allow hibernate session factory to access the database tables for us ->

    <mapping
    resource=\"com/rampageinc/rrserverdatabase/hiber/cache/RrCacheJobs.hbm.xml\" />
    <mapping
    resource=\"com/rampageinc/rrserverdatabase/hiber/cache/RrCachePagelist.hbm.xml\" />
    <mapping
    resource=\"com/rampageinc/rrserverdatabase/hiber/cache/RrCachePagelistNoStandby.hbm.xml\" />

    </session-factory>

    </hibernate-configuration>

    5. Insert a job record into rr_cache_jobs:
    INSERT INTO rr_cache_jobs(
    job_id,
                customer_id,
        name, security, page_approval, book_approval,
                type, active, customer_reference, internal_id, publish, has_jdf, done,
        create_timestamp, modified_timestamp, locked, locked_by,locked_reason,   
                due_date_type, priority, job_mode, jdf_job, standby, remote_job_submit,
                workflow_job, auto_page_mapped, printable_template, printable_job,
                var_data, auto_output, auto_output_auto_approve, auto_output_auto_release_flats,
                smart_jdf, smart_task, status,
        registrar_user_id,
        auto_approve, auto_publish, auto_release_flats, auto_web_enable)
        VALUES ( 500, 1,
                'Default Job','NORMAL','OFF','OFF',
        'JOB','YES','',2000,'NO','NO','NO',
      '2012-04-24 00:00:00','2012-04-24 00:00:00',FALSE,'','',
    0,1,1,FALSE,FALSE,FALSE,
             FALSE,FALSE,FALSE,FALSE,
             FALSE,FALSE,FALSE,FALSE,
             FALSE,FALSE,'0',
              1,
             FALSE,FALSE,FALSE,FALSE);

    6. the rr_cache_pagelist table is empty. 

    7. When you start hibernate, hibernate will create the schema of the in-memory hsql database and create the view before the data is populated in the table. 
    Later when the application runs, a record is inserting to the rr_cache_pagelist table.  The record does have the fk to the job_id, and the job record does have standby set to false. 
    But when you select * from rr_cache_pagelist_no_standby, the result is no records found.

     
  • Fred Toussi

    Fred Toussi - 2012-11-26

    Sorry, we cannot setup a Hibernate project to test your data.

    In order to test easily, set up an HSQLDB Server instead of the in-process database. Then execute the queries that you need on the Server after Hibernate has inserted the rows. You can then run some simple SELECT statements to verify.

     
  • Lucille Wilson

    Lucille Wilson - 2012-11-26

    The problem I am asking you to solve is one of timing. 

    The issue is that 1. I have to use a in-memory database.  2. I am using hibernate for a very good set of reasons, and hibernate does create the database schema when it is reading the hbm.xml files.  Because the view is defined in an hbm.xml file, just the same as any other table is defined in an hbm.xml file and because the hbm2ddl.auto = create, the view is created just as the hbm.xml files are read (which is when tomcat is starting up the war).

    The timing issue is that the view is created before the data exists in the table because the view is created when the schema for the in-memory database is created.

    After hibernate creates the schema, I insert data into the rr_cache_jobs table, but no data is inserted into rr_cache_pagelist table.

    Even later, while the application is running, I insert a record into rr_cache_pagelist.  At this point, I'd reasonably expect the view to pick up the pagelist record.  But it does not.

     
  • Fred Toussi

    Fred Toussi - 2012-11-26

    It's fine to use Hibernate.

    If you run the memory database as a Server, and specify the server URL instead of in-process, Hibernate will perform its operations exactly in the same order. You can then connect to the Server with a database manager to examine the CREATE TABLE statements and the data that has been inserted.

    This allows you to save a database script to file and report it if there is an obvious problem.

    The fact that your view is generated before the data is inserted shouldn't make any difference.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:

JavaScript is required for this form.





No, thanks