Updating a database column of type xml via ClientDataset

Updating a database column of type xml via ClientDataset

Postby timpara » Thu 23 Jan 2014 12:59

I'm using a Delphi TClientDataSet to read a PostgreSQL table which contains an xml column.
The READ works ok - the xml value comes back as a string, but the ApplyUpdates call fails
with the error EpgError Column "contents" is of type xml but expression is of type text.

I can manually create an SQL command to update the xml column, but I have to wrap the new value in the PostgreSQL xml function i.e.
update document set contents=xml('<xml/>') where document_id=1
This works OK using the Devart drivers, it's just that the ClientDataSet method fails.
Is this a bug in the devart drivers? Or Delphi libraries?
As a workround, can the SQL be intercepted and modified to wrap the new value in the xml function?
Any suggestions would be appreciated.


Code below:

begin
{
tblDocument is a TClientDataset instance linked to the Postgres table:

CREATE TABLE document
(
document_id integer NOT NULL DEFAULT nextval('document_document_idnew_seq'::regclass),
contents xml,
CONSTRAINT document_pkey PRIMARY KEY (document_id)
)
}
with tbldocument do
begin
Open;
if(not locate('document_id',1,[])) then ShowMessage('Rec 1 not found'); // locate rec
contentsField := FieldByName('contents');
Edit;
ShowMessage('XML Before: ' + contentsField.AsString); // OK - field reads OK
contentsField.AsString := '<newContent/>';
Post;
ApplyUpdates(0); // ERROR: EpgError Column "contents" is of type xml but expression is of type text
Close;
end;
timpara
 
Posts: 2
Joined: Thu 23 Jan 2014 12:32

Re: Updating a database column of type xml via ClientDataset

Postby AlexP » Thu 23 Jan 2014 14:54

Hello,

To solve the problem, you should set the DetectParamTypes option to True:

Code: Select all
  SQLConnection1.Params.Values['DetectParamTypes'] := 'True';
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35

Re: Updating a database column of type xml via ClientDataset

Postby timpara » Thu 23 Jan 2014 18:05

AlexP wrote:Hello,
To solve the problem, you should set the DetectParamTypes option to True:
Code: Select all
  SQLConnection1.Params.Values['DetectParamTypes'] := 'True';


Thanks Alex - that's solved it. (But searching for info about that parameter, I'm not sure why!)
timpara
 
Posts: 2
Joined: Thu 23 Jan 2014 12:32

Re: Updating a database column of type xml via ClientDataset

Postby AlexP » Fri 24 Jan 2014 08:34

Hello,

Description of this property is in the ...\Devart\Dbx\PostgreSql\Readme.html file. If this option is set to False (the default value), then types from the DataSet are used on parameter binding, and since dbExpress has no XML type, then the string type is used, and PostgreSQL server returns a type mismatch error when executing the query. If the option is set to True, local types are ignored and type definition occurs on the server side
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35


Return to dbExpress driver for PostgreSQL