SmartQuery and fieldtype MDSYS.SDO_GEOMETRY

[Aufhauser]

Hello,

I have problems updating a field of type MDSYS.SDO_GEOMETRY using TSmartQuery. If SDOGEOMETRY is the only field, which value has been changed and oldValue and newValue are not null, SmartQuery generates no update statement (checked via DBMonitor). I have do use the following workarround to force SmartQuery to update the value:

Query.Edit;
SDOGEOMETRY.Value:= NULL;
Query.Post;
Query.Edit;
SDOGEOMETRY.Value:= newValue;
Query.Post;

Is there a way to mark a field as changed to force SmartQuery to generate an update statement?

Thanks,
Stefan

[bork]

Hello

I don't have any troubles with changing fields with the SDO_GEOMETRY data type. I executed the following code successfully:

Code: Select all

var
  MySession: TOraSession;
  MyQuery: TOraQuery;
begin
  MySession := TOraSession.Create(self);
  MySession.Username := 'scott';
  MySession.Password := 'tiger';
  MySession.Server := 'ORA1110';
  MySession.Open;

  try
    OraSession1.ExecSQL('drop table test_table', []);
  except
  end;

  OraSession1.ExecSQL('CREATE TABLE test_table ( ' + #13 +
                      'ID NUMBER, ' + #13 +
                      'NAME VARCHAR2(50), ' + #13 +
                      'geometry MDSYS.SDO_GEOMETRY, ' + #13 +
                      'CONSTRAINT pk_test_table PRIMARY KEY (ID) ' + #13 +
                      ')', []);

  MyQuery := TOraQuery.Create(self);
  MyQuery.Session := MySession;

  MyQuery.SQL.Text := 'select * from test_table';

  MyQuery.Open;
  MyQuery.Append;
  MyQuery.FieldByName('ID').AsInteger := 1;
  MyQuery.FieldByName('Name').AsString := 'test';
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.X').AsInteger := 1;
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.Y').AsInteger := 2;
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.Z').AsInteger := 3;
  MyQuery.Post;
  MyQuery.Close;

  MyQuery.Open;
  ShowMessage('POINT.X = ' + MyQuery.FieldByName('GEOMETRY.SDO_POINT.X').AsString + #13 +
              'POINT.Y = ' + MyQuery.FieldByName('GEOMETRY.SDO_POINT.Y').AsString + #13 +
              'POINT.Z = ' + MyQuery.FieldByName('GEOMETRY.SDO_POINT.Z').AsString);
end;

Please create a new empty application and execute the same code. And please provide me the result of this code execution. If this is working correctly then please modify this code to reproduce your issue.

[Aufhauser]

Hello,
Query.Append works fine, my problem is to edit an existing value with a complex geometry (e.g. polygon geometry). I do not update SDO_POINT but the ordinate-array.
I am not shure if this is an ODAC problem or an oracle bug, so my question: is there a way to manuelly mark a field as changed?

[bork]

Hello

No you cannot mark fields as changed.
I modified the end of my previous sample:

Code: Select all

  MyQuery.Open;
  MyQuery.Append;
  MyQuery.FieldByName('ID').AsInteger := 1;
  MyQuery.FieldByName('Name').AsString := 'test';
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.X').AsInteger := 1;
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.Y').AsInteger := 2;
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.Z').AsInteger := 3;
  MyQuery.FieldByNAme('GEOMETRY.SDO_ORDINATES[0]').AsVariant := 10;
  MyQuery.Post;
  MyQuery.Close;

  MyQuery.Open;
  MyQuery.Edit;
  MyQuery.FieldByName('ID').AsInteger := 1;
  MyQuery.FieldByName('Name').AsString := 'test';
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.X').AsInteger := 6;
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.Y').AsInteger := 7;
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.Z').AsInteger := 8;
  MyQuery.FieldByNAme('GEOMETRY.SDO_ORDINATES[0]').AsVariant := 20;
  MyQuery.Post;
  MyQuery.Close;

  MyQuery.Open;
  ShowMessage('POINT.X = ' + MyQuery.FieldByName('GEOMETRY.SDO_POINT.X').AsString + #13 +
              'POINT.Y = ' + MyQuery.FieldByName('GEOMETRY.SDO_POINT.Y').AsString + #13 +
              'POINT.Z = ' + MyQuery.FieldByName('GEOMETRY.SDO_POINT.Z').AsString + #13 +
              'ORDINATES[0] = ' + MyQuery.FieldByNAme('GEOMETRY.SDO_ORDINATES[0]').AsString);

This code modifies the ordinate-array correctly. Please modify this code or provide your own sample to reproduce your issue. We want to help you to resolve this issue but we cannot do it while we cannot reproduce this issue.

[Aufhauser]

Hello,

thank you for answer, but my problems are not solved. Your test-code does not work because you have to set
Query.SparseArray:= True;
Query. ObjectView:= True;[/i]

to deal with MDSYS.SDOGEOMETRY.
SDOGEOMETRY.SDO_ORDINATES can hold many points (> 1000) so you have do use following code to handle the points:

FGeomField:= Query.GetObject(FGeomFieldName);
FElemInfo:= FGeomField.AttrAsArray['SDO_ELEM_INFO'];
FOrdinates:= FGeomField.AttrAsArray['SDO_ORDINATES'];

Stefan

[bork]

I modified my sample and it is working with SDOGEOMETRY by TOraObject and TOraArray:

Code: Select all

var
  MySession: TOraSession;
  MyQuery: TSmartQuery;
  GeomField: TOraObject;
  OrdArray: TOraArray;
begin
  MySession := TOraSession.Create(self);
  MySession.Username := 'scott';
  MySession.Password := 'tiger';
  MySession.Server := 'ORA1110';
  MySession.Open;


  try
    OraSession1.ExecSQL('drop table test_table', []);
  except
  end;

  OraSession1.ExecSQL('CREATE TABLE test_table ( ' + #13 +
                      'ID NUMBER, ' + #13 +
                      'NAME VARCHAR2(50), ' + #13 +
                      'geometry MDSYS.SDO_GEOMETRY, ' + #13 +
                      'CONSTRAINT pk_test_table PRIMARY KEY (ID) ' + #13 +
                      ')', []);

  MyQuery := TSmartQuery.Create(self);
  MyQuery.SparseArrays := True;
  MyQuery.ObjectView := True;
  MyQuery.Session := MySession;

  MyQuery.SQL.Text := 'select * from test_table';

  MyQuery.Open;
  MyQuery.Append;
  MyQuery.FieldByName('ID').AsInteger := 1;
  MyQuery.FieldByName('Name').AsString := 'test';
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.X').AsInteger := 1;
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.Y').AsInteger := 2;
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.Z').AsInteger := 3;
  MyQuery.Post;
  MyQuery.Close;

  MyQuery.Open;
  MyQuery.Edit;
  MyQuery.FieldByName('ID').AsInteger := 1;
  MyQuery.FieldByName('Name').AsString := 'test';
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.X').AsInteger := 6;
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.Y').AsInteger := 7;
  MyQuery.FieldByName('GEOMETRY.SDO_POINT.Z').AsInteger := 8;

  GeomField:= MyQuery.GetObject('GEOMETRY');
  OrdArray := GeomField.AttrAsArray['SDO_ORDINATES'];
  OrdArray.ItemAsInteger[0] := 99;
  OrdArray.ItemAsInteger[1] := 199;

  MyQuery.Post;

  MyQuery.Close;

  MyQuery.Open;

  GeomField:= MyQuery.GetObject('GEOMETRY');
  OrdArray := GeomField.AttrAsArray['SDO_ORDINATES'];
  ShowMessage('POINT.X = ' + MyQuery.FieldByName('GEOMETRY.SDO_POINT.X').AsString + #13 +
              'POINT.Y = ' + MyQuery.FieldByName('GEOMETRY.SDO_POINT.Y').AsString + #13 +
              'POINT.Z = ' + MyQuery.FieldByName('GEOMETRY.SDO_POINT.Z').AsString + #13 +
              'ORDINATES[0] = ' + IntToStr(OrdArray.ItemAsInteger[0]) + #13 +
              'ORDINATES[1] = ' + IntToStr(OrdArray.ItemAsInteger[1]));
end;

This code shows the following message:

POINT.X = 6
POINT.Y = 7
POINT.Z = 8
ORDINATES[0] = 99
ORDINATES[1] = 199

If you get different message, please specify the exact ODAC version you are using (for example: 6.90.0.59). If you are using old ODAC version it can contain some errors that have influence on the result.

[Aufhauser]

Hallo Bork,

thank you for testing code, it works on my system too.
But I wonder, why your use of SDO_ORDINATES works. You never initialize the array and can set new values whithout appending them.
In my (5 year old - ODAC 4.50.xx) code I use the methodes AllocObject and AppendItem etc. so, did you change your code to do this work in background?
I'll change my code to use the objects in your manner.

Stefan

[bork]

Hello

If you are trying to set a new value to the array element that wasn't appended, then ODAC appends this element in background. You can do this manually also. It is good to see that the last sample works on your system too. If any other questions come up, please contact me.