Peter,
Here's a guess. <s>
When dealing with Oracle and VFP character fields, sometimes the data is submitted with the trailing spaces intact. In Oracle, your laborcode field would look something like "12345 ". When the remote view builds the update statement internally, it trims trailing spaces. The update statement would look something like this:
update labor
set craft = <new value>
where laborcode = '12345'
"12345 " <> "12345" so no updates are done.
One way to get around this is to add insert triggers to all your tables where character data is used as search criteria or key fields. Ex:
create or replace trigger owner.labor_trim
before insert or update
on owner.labor
referencing NEW as NEW OLD as OLD
for each row
begin
:new:laborcode := rtrim(:new.laborcode);
:new:craft := rtrim(:new.craft);
end;
When rows are modified or inserted, the key value is trimmed so subsequent queries work as expected. Once the trigger is in place simply execute the following to update the table:
update labor
set laborcode = laborcode;
commit;
All the values will be trimmed at this point.
Credit Mark McCasland for showing me the trigger technique awhile back.
HTH.
>Hi Larry,
>Here is the dump.....
>
>* *
>* * 04/08/02 MAX_ORA.DBC 14:52:39
>* *
>* *********************************************************
>* *
>* * Description:
>* * This program was automatically generated by GENDBC
>* * Version 2.26.67
>* *
>* *********************************************************
>
>CLOSE DATA ALL
>CREATE DATABASE 'MAX_ORA.DBC'
>
>***************** Connection Definitions MAX_ORA ***************
>
>CREATE CONNECTION MAX_ORA ;
> DATASOURCE "Maximo" ;
> USERID "sysadm" ;
> PASSWORD "xxxxxxxxx"
>
>****
>=DBSetProp('MAX_ORA', 'Connection', 'Asynchronous', .F.)
>=DBSetProp('MAX_ORA', 'Connection', 'BatchMode', .T.)
>=DBSetProp('MAX_ORA', 'Connection', 'Comment', '')
>=DBSetProp('MAX_ORA', 'Connection', 'DispLogin', 1)
>=DBSetProp('MAX_ORA', 'Connection', 'ConnectTimeOut', 15)
>=DBSetProp('MAX_ORA', 'Connection', 'DispWarnings', .F.)
>=DBSetProp('MAX_ORA', 'Connection', 'IdleTimeOut', 0)
>=DBSetProp('MAX_ORA', 'Connection', 'QueryTimeOut', 0)
>=DBSetProp('MAX_ORA', 'Connection', 'Transactions', 1)
>=DBSetProp('MAX_ORA', 'Connection', 'Database', 'Maximo')
>
>***************** View setup for LABOR ***************
>
>CREATE SQL VIEW "LABOR" ;
> REMOTE CONNECT "MAX_ORA" ;
> AS SELECT * FROM LABOR Labor
>
>DBSetProp('LABOR', 'View', 'UpdateType', 1)
>DBSetProp('LABOR', 'View', 'WhereType', 3)
>DBSetProp('LABOR', 'View', 'FetchMemo', .T.)
>DBSetProp('LABOR', 'View', 'SendUpdates', .T.)
>DBSetProp('LABOR', 'View', 'UseMemoSize', 255)
>DBSetProp('LABOR', 'View', 'FetchSize', 100)
>DBSetProp('LABOR', 'View', 'MaxRecords', -1)
>DBSetProp('LABOR', 'View', 'Tables', 'LABOR')
>DBSetProp('LABOR', 'View', 'Prepared', .F.)
>DBSetProp('LABOR', 'View', 'CompareMemo', .T.)
>DBSetProp('LABOR', 'View', 'FetchAsNeeded', .F.)
>DBSetProp('LABOR', 'View', 'FetchSize', 100)
>DBSetProp('LABOR', 'View', 'Comment', "")
>DBSetProp('LABOR', 'View', 'BatchUpdateCount', 1)
>DBSetProp('LABOR', 'View', 'ShareConnection', .F.)
>
>*!* Field Level Properties for LABOR
>* Props for the LABOR.laborcode field.
>DBSetProp('LABOR.laborcode', 'Field', 'KeyField', .T.)
>DBSetProp('LABOR.laborcode', 'Field', 'Updatable', .T.)
>DBSetProp('LABOR.laborcode', 'Field', 'UpdateName', 'LABOR.LABORCODE')
>DBSetProp('LABOR.laborcode', 'Field', 'DataType', "C(18)")
>* Props for the LABOR.craft field.
>DBSetProp('LABOR.craft', 'Field', 'KeyField', .F.)
>DBSetProp('LABOR.craft', 'Field', 'Updatable', .T.)
>DBSetProp('LABOR.craft', 'Field', 'UpdateName', 'LABOR.CRAFT')
>DBSetProp('LABOR.craft', 'Field', 'DataType', "C(18)")
>
>*--- Other fields but all are non-key fields and updatable.
>
>Any guesses !
>
>pete,
Larry Miller
MCSD
LWMiller3@verizon.netAccumulate learning by study, understand what you learn by questioning. -- Mingjiao