Can't seem to force DD to use SQL UPDATE

OD 6.2 on Windows 2003 Server w/ Oracle 10g

I can't seem to get real-update to work nor does it seem to do anything with the PK. I've dumbed down my ddconfig as much as possible and tried various tweaks on the following, but I can't seem to get it to fly.

The reason we need real updates is that the initial tables are being populated by an integration with some backend systems outside of the web initiative. TeamSite is used to add additional web-targetted content to existing rows. Since there's much much more data than we care to pull into TS, we only want to update those columns that are relevant. The default delete+insert won't work because of constraints on data that isn't maintained in TS and we'd be trying to insert NULL into those (not to mention erasing all that data). Before you ask why we can't just split the affected tables in two to separate backend content from TS content, let's just say that's not an option.

When I had a few columns, the log's DoesRowExist would try to match all specified columns and, if the content had changed, would naturally determine that the row didn't exist thereby ignoring the PK. (Seems to me that DoesRowExist should just be checking SELECT count(*) FROM my_table WHERE my_pk=* but it doesn't seem to do that). When I dumb down my config to the following snippet (PK only), it does match a row and then tries to delete it (but can't, because of the afore-mentioned constraints). The log does say "Performing Real Update" but the SQL it executes doesn't match.

Any help will be greatly appreciated.

Dumbed down DDConfig:
[HTML]

db = "blah:1521 Smiley Frustrated

TG"
user = "blah"
password = "blah"
vendor = "oracle"
max-id-length = "128"
real-update = "yes"
delete-tracker = "yes"

>

name="FLOORPLAN_ID"
data-type="NUMBER(15,0)"
value-from-element="floorplan/0/details/0/floorplanID/0"
allows-null="no"
is-url="no" />

[/HTML]

Log file snippet based on above dumbed down config:
[HTML]
ENG: 2008-05-02 16:39:33 EDT GMT-04:00 Begin logfile [E:\Interwoven\OpenDeployNG\log\eCustomer\src.floorplan.floorplanDeploy.grvs81.to.database.log] ---------
ENG: 2008-05-02 16:39:33 EDT GMT-04:00 Job ID=m32 Deployment=eCustomer\floorplan
ENG: 2008-05-02 16:39:33 EDT GMT-04:00 DataDeploy Version is 6.2.0.0.0
ENG: 2008-05-02 16:39:33 EDT GMT-04:00 Owner started the deployment is SYSTEM
ENG: 2008-05-02 16:39:33 EDT GMT-04:00 DD args iwdd="floorplanDeploy" cfg=E:\Interwoven\OpenDeployNG\conf\eCustomer\floorplan.xml need-result=yes depuuid=df0dad50-1887-11dd-8ad0-84a6a5ce5ddc leglabel=database

ENG: 2008-05-02 16:39:33 EDT GMT-04:00 Proceed to execute the database deployment

DBD: Deployment : floorplanDeploy
DBD: Database Vendor set to oracle
DBD: Max Identifier length set to 128 characters.
DBD: SQL datatype for column FLOORPLAN_ID is NUMBER(15,0)
DBD: # of columns: 1
DBD: # of primary key columns specified: 1
DBD: # of groups defined in element: 1
DBD: [Fri May 02 16:39:33 EDT 2008] Begin executing floorplanDeploy
DBD: Begin Set up the publisher thread
DBD: End Set up the publisher thread
DBD: Area VPath:Y:\default\main\eCustomer\STAGING\templatedata\Community\Floor Plan\data\transformed
DBD: Setting up TDbSchemaAgent.
DBD: Validating cached connection...
DBD: Cached connection OK
DBD: Connected to [grvs6100:1521:ECDEV]
DBD: SELECT * FROM USER_TABLES WHERE TABLE_NAME='IWDELTRACKER'
DBD: SELECT * FROM USER_TABLES WHERE TABLE_NAME='FLOORPLAN'
DBD: Table [FLOORPLAN] exists.
DBD: Running deployment [floorplanDeploy]
DBD: extraction type=default
DBD: tuple mode=wide
DBD: area=Y:\default\main\eCustomer\STAGING\templatedata\Community\Floor Plan\data\transformed
DBD: Building replicant order column information.
DBD: Building replicant order column information... Done.
DBD: TUPLE={floorplan/0/details/0/display_cc/0=N, floorplan/0/details/0/display_web/0=N, state=Original, floorplan/0/details/0/propertyID/0=1006, floorplan/0/details/0/updated_by/0=TEAMSITE, floorplan/0=

, floorplan/0/rps/0/rp/0=

, floorplan/0/rps/0/rp/0/updated_by/0=TEAMSITE, floorplan/0/rps/0/rp/0/paragraph_text/0=This is a roaring great pad for all your whips, cages and acoutrements for bearded female companions. It goes well with the disco soundtrack, path=./floorplan.xml, floorplan/0/details/0/created_by/0=TEAMSITE, floorplan/0/rps/0/rp/0/floor_plan_name/0=The Lion Tamer's Lair, floorplan/0/details/0/display_ext/0=N, floorplan/0/rps/0/rp/0/locale_id/0=en_US, floorplan/0/details/0=

, floorplan/0/rps/0/rp/0/paragraph_num/0=0, floorplan/0/rps/0/rp/0/created_by/0=TEAMSITE, floorplan/0/rps/0=

, floorplan/0/details/0/floorplanID/0=93, IWOV_PROC_FLAG=true}
DBD: Performing Real Update
DBD: TTableSchemaHelper object for [FLOORPLAN] found in cache.
DBD: SELECT KEYCOLNAME , KEYCOLVALUE FROM IWDELTRACKER WHERE PATH = ? AND AREA = ? AND KEYCOLNAME IN ( 'FLOORPLAN_ID')
DBD: 2 PK values retrieved from iwdeltracker for ./floorplan.xml, Y:\default\main\eCustomer\STAGING\templatedata\Community\Floor Plan\data\transformed
DBD: TTableSchemaHelper object for [FLOORPLAN] found in cache.
DBD: TTableSchemaHelper object for [FLOORPLAN] found in cache.
DBD: TTableSchemaHelper object for [FLOORPLAN] found in cache.
DBD: SelectCurrentRows Smiley Frustrated

ELECT FLOORPLAN_ID FROM FLOORPLAN WHERE FLOORPLAN_ID IN (?,? ) ORDER BY FLOORPLAN_ID
DBD: Index: 1, value: 213
DBD: Index: 2, value: 213
DBD: SelectCurrentRows: 1 rows selected.
DBD: TTableSchemaHelper object for [FLOORPLAN] found in cache.
DBD: GetCurrentRowsFromGroupInfo: 1 rows selected from FLOORPLAN
DBD: DoesRowExist Smiley Frustrated

ELECT COUNT(*) FROM FLOORPLAN WHERE FLOORPLAN_ID = ?
DBD: Column: FLOORPLAN_ID, value-from-element: floorplan/0/details/0/floorplanID/0, Index: 1,Converting '93' to DECIMAL
DBD: DELETE FROM FLOORPLAN WHERE FLOORPLAN_ID = ?
DBD: Column: FLOORPLAN_ID, value-from-element: null, Index: 1,Converting '213' to DECIMAL
DBD:
DBD: *******************************************************
DBD: SQLException occured in TDbSchemaGroupCfg
DBD: Exception Message: ORA-02292: integrity constraint (WLPRE.UNIT_FLOORPLAN_FK1) violated - child record found

DBD: Vendor Error Code: 2292
DBD: SQL state: 23000
DBD: *******************************************************
DBD:
DBD: *******STACK TRACE*************
DBD: ERROR:
java.sql.SQLException: ORA-02292: integrity constraint (WLPRE.UNIT_FLOORPLAN_FK1) violated - child record found

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1093)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2047)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1940)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2709)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
at com.interwoven.dd100.dd.TDbSchemaUpdateHelper.DeleteOneRow(TDbSchemaUpdateHelper.java:1286)
at com.interwoven.dd100.dd.TDbSchemaUpdateHelper.DoUpdate(TDbSchemaUpdateHelper.java:803)
at com.interwoven.dd100.dd.TDbSchemaGroupCfg.DoRealUpdate(TDbSchemaGroupCfg.java:835)
at com.interwoven.dd100.dd.TDbSchemaCfg.RealUpdateWithGroupTree(TDbSchemaCfg.java:955)
at com.interwoven.dd100.dd.TDbSchemaCfg.RealUpdate(TDbSchemaCfg.java:935)
at com.interwoven.dd100.dd.TDbSchemaAgent.BasicWriteTuple(TDbSchemaAgent.java:441)
at com.interwoven.dd100.dd.TDbSchemaAgent.WriteTuple(TDbSchemaAgent.java:336)
at com.interwoven.dd100.dd.TConsumerManager.WriteConsumerInternal(TConsumerManager.java:399)
at com.interwoven.dd100.dd.TConsumerManager.WriteConsumers(TConsumerManager.java:388)
at com.interwoven.dd100.dd.TAgentClient.DoOneTeamSiteSource(TAgentClient.java:1018)
at com.interwoven.dd100.dd.TAgentClient.DoTeamSiteSources(TAgentClient.java:502)
at com.interwoven.dd100.dd.TAgentClient.DoOneDeployment(TAgentClient.java:289)
at com.interwoven.dd100.dd.TAgentClient.Go(TAgentClient.java:178)
at com.interwoven.dd100.dd.IWDataDeploy.Go(IWDataDeploy.java:582)
at com.interwoven.dd100.dd.TDASThread.ProcessItem(TDASThread.java:203)
at com.interwoven.dd100.dd.TDASThread.run(TDASThread.java:128)

DBD: ERROR Smiley Very Happy

oRealUpdate() failed for group [FLOORPLAN].
DBD: -- Failed
DBD: ERROR:WriteTuple failure to consumer[0]
DBD: ERROR:Unable to process TeamSite source .
DBD: ERROR:deployment [floorplanDeploy] FAILED
DBD: Error occured in area outside TDbAgent consumer (explicit rollback initiated)
DBD: Stat for group [FLOORPLAN]:
DBD: -----------------------------------
DBD: Rows inserted : 0
DBD: Rows updated : 0
DBD: Rows deleted : 0
DBD: Rows failed : 0
DBD: Rows rolled back : 0
DBD: Rows skipped : 0
DBD: -----------------------------------
DBD: Rollback database [grvs6100:1521:ECDEV]...
DBD: Deployment[floorplanDeploy]
DBD: -----------------------------------
DBD: DCRs inserted : 0
DBD: DCRs updated : 0
DBD: DCRs deleted : 0
DBD: DCRs failed : 0
DBD: DCRs rolled back : 0
DBD: DCRs skipped : 0
DBD: -----------------------------------
DBD: Started : Fri May 02 16:39:33 EDT 2008
DBD: Shutting down DDD PublisherThread
DBD: Finished: Fri May 02 16:39:33 EDT 2008
DBD:
### IWDataDeploy FAILED ###

DBD:
### Sending result ###

ENG: 2008-05-02 16:39:33 EDT GMT-04:00 Database deployment execution returned 1

ENG: 2008-05-02 16:39:33 EDT GMT-04:00 <<< Completed database deployment Invocation>>>

ENG: 2008-05-02 16:39:33 EDT GMT-04:00 Manifest path is E:/Interwoven/OpenDeployNG/log/eCustomer/src.floorplan.floorplanDeploy.grvs81.to.database.log.mf

ENG: 2008-05-02 16:39:33 EDT GMT-04:00 Got Manifest object com.interwoven.od.adapter.delivery.base.IWODManifest@d6d09a

ENG: 2008-05-02 16:39:33 EDT GMT-04:00 fHasFailedItems is set to true

ENG: 2008-05-02 16:39:33 EDT GMT-04:00 Published leg event depUuid=[df0dad50-1887-11dd-8ad0-84a6a5ce5ddc] legLabel=[database] nextUuid=[null] nextDep=[null]
ENG: 2008-05-02 16:39:33 EDT GMT-04:00 Job ID=m32 Deployment leg=database Elapsed Time=94 ms <<<
[/HTML]

Find more posts tagged with

Comments

Adam Stoller

From what you posted - it doesn't look like FLOORPLAN_ID is a unique value in the table - and thus, by itself, cannot be used as a primary key.

If there's some other column in the table that actually has a unique value - or which when combined with FLOORPLAN_ID creates a unique value - then you might be able to use a tuple preprocessor to retrieve the value and utilize it in constructing the complete tuple for DD to process ... however, I've never dealt with the development of tuple preprocessors and thus am not sure of the effort involved nor whether it would actually solve your problem.

Another possibility is to scrap using DD and write your own DB interaction code with either Perl DBI or the Java equivalent - it's more work (in some cases) but it gives you full control over the query and update statements.

Rick Poulin

Hey, thanks for the input fish.

Floorplan_id is unique (it's actually the pk in the db) but your comment did help to point out that it was finding two rows somewhere. Somehow, we ended up with two identical rows for that floor plan in IWDELTRACKER. I just deleted those rows and now everything works just fine. Talk about helpful error messages...

Thanks!