Skip site navigation (1) Skip section navigation (2)

Re: Character invalid saving memo with UpdateSQL

From: Richard Combs <rncombs(at)covad(dot)net>
To: Cristian Custodio <cristian_custodio(at)terra(dot)com(dot)br>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Character invalid saving memo with UpdateSQL
Date: 2003-11-29 07:52:13
Message-ID: 3FC8502D.7060805@covad.net (view raw or flat)
Thread:
Lists: pgsql-odbc
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.
>

In response to

Responses

pgsql-odbc by date

Next:From: Jeff EckermannDate: 2003-11-29 17:35:34
Subject: Re: State S0002 (Base table not found)
Previous:From: Dave PageDate: 2003-11-28 19:42:06
Subject: Re: ODBC 7.3.0.200: MergeModule problems.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group