Re: Character invalid saving memo with UpdateSQL

I will create a package and will send my TUpdateSQL component with this change to the groups list.

Thanks Richard,

Cristian

----- Original Message -----
From: Richard Combs
To: Cristian Custodio
Cc: pgsql-odbc(at)postgresql(dot)org
Sent: Saturday, November 29, 2003 4:52 AM
Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

Since you mentioned update as the root of the problem, I retried my
experiment. Same setup as before, however table1 was changed to the
following

bigtest=# \d table1
idnumber | integer | not null default nextval('public.table1_id
number_seq'::text)
c1 | character(40) |
v2 | character varying(4000) |

Sample Delphi code

type
TForm1 = class(TForm)
Table1: TTable;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
Query1: TQuery;
procedure DBGrid1DblClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.DBGrid1DblClick(Sender: TObject);
begin
{ query is
update table1
set v2 = :v2
where idnumber = :idnumber
}
// query1.Params[0].AsMemo := 'mod '+table1.Fields[2].AsString; // this gives invalid characters
query1.Params[0].AsString := 'mod '+table1.Fields[2].AsString; // this gives only valid characters
query1.Params[1].AsInteger := table1.Fields[0].AsInteger;
query1.ExecSQL;
table1.Refresh;
end;

end.

Table before any updates

bigtest=# select * from table1;
1 | one | var one
2 | two | var two
3 | three | var three

Table after updates using asMemo

bigtest=# select * from table1;
idnumber | c1 | v2

----------+------------------------------------------+--------------------------
-
1 | one | var one
2 | two | mod var twoER
3 | three | mod mod mod var three[i?

idnumber 2 errored after 1 modify
idnumber 3 errored after 3 modifies

Table after updates using asString

bigtest=# select * from table1;
1 | one | var one
3 | three | mod mod mod var three
2 | two | mod mod mod mod mod mod mod var two

As you said, the problem seems to be in using asMemo.
Borland docs say
Note: Some servers support string field types
that are too long to fit in the record buffer.
These fields are represented as memo fields,
although they are not actually Blob fields.
When working with such "pseudo-Blob" fields,
use the AsString property instead.

Also

Memo fields are a form of binary large object (BLOB) field where the data consists of simple text.
Use AsString to assign the BLOB field value to a string or to assign a string value to the BLOB field.
Typically, using AsString makes sense only if the BLOB contains text, such as that in a memo field. The string data (Delphi) or AnsiString (C++) type can, however, store binary data as well. Thus, even BLOB fields for nontextual BLOB types such as ftGraphic or ftTypedBinary can use the AsString property.

This would seem to be the case with varchar in PostgreSQL. Perhaps
the best thing to do is use asString for all char and varchar columns
unless you find an indication to the contrary.

There are some mentions in the Borland newsgroups to having to use
asMemo with Oracle. Check those out by searching the old archives,
you may still have to use asMemo to get your Oracle data.

HTH

Feel free to send me your code, and I will take a look at it.

Cristian Custodio wrote:

Sorry, I forget to say that the problem occur just in Upate, not in Insert.
And not in first time. You sometimes need update two or tree times
until see the error.