Page 1 of 1
SmartQuery and fieldtype MDSYS.SDO_GEOMETRY
Posted: Thu 29 Jul 2010 15:34
by 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
Posted: Tue 03 Aug 2010 14:06
by 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.
Posted: Tue 03 Aug 2010 15:42
by 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?
Posted: Thu 05 Aug 2010 14:53
by 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.
Posted: Sun 08 Aug 2010 09:35
by 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
Posted: Tue 10 Aug 2010 10:26
by 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.
Posted: Wed 11 Aug 2010 07:53
by 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
Posted: Wed 11 Aug 2010 13:57
by 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.