Обсуждение: Character invalid saving memo with UpdateSQL

I haven't used the BDE in a while, however it looks like a problem with character sets.  That is BDE is using one character set and PG is using another, hence the "invalid" characters.  Check the postgreSQL documentation on localization to see how to make your server recognize other language (character) sets.

One other question, does this happen only with varchar and only with that one column, or can you duplicate the problem with character type in another column?

Cristian Custodio wrote:

 

I'm using delphi 5 and PG: 7.3.4 and BDE

 

I have 1 varchar(4000) column. When I try post any value in this column

using UpdateSQL it save very invalids caracters. Like this: ííííííííí.

 

Any body could help me?

 

Thanks,

 

Cristian Luciano Custodio

----- Original Message -----

From: Richard Combs

To: Cristian Custodio

Cc: pgsql-odbc@postgresql.org

Sent: Wednesday, November 26, 2003 2:50 PM

Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

I haven't used the BDE in a while, however it looks like a problem with character sets.  That is BDE is using one character set and PG is using another, hence the "invalid" characters.  Check the postgreSQL documentation on localization to see how to make your server recognize other language (character) sets.

One other question, does this happen only with varchar and only with that one column, or can you duplicate the problem with character type in another column?

Cristian Custodio wrote:

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Cristian Custodio
Sent: donderdag 27 november 2003 12:59
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

I'm using LATIN1 but the problem happened with any other database.

 

It Occur with any varchar column greatest 255 characters.

 

Cristian

 

 

----- Original Message -----

From: Richard Combs

To: Cristian Custodio

Cc: pgsql-odbc@postgresql.org

Sent: Wednesday, November 26, 2003 2:50 PM

Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

I haven't used the BDE in a while, however it looks like a problem with character sets.  That is BDE is using one character set and PG is using another, hence the "invalid" characters.  Check the postgreSQL documentation on localization to see how to make your server recognize other language (character) sets.

One other question, does this happen only with varchar and only with that one column, or can you duplicate the problem with character type in another column?

Cristian Custodio wrote:

From: Leeuw van der, Tim

To: pgsql-odbc@postgresql.org

Sent: Thursday, November 27, 2003 8:41 AM

Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

In quite a lot of databases, VARCHAR has an upper limit of 255 characters. I would say that most likely some of the libraries don't count of ever having a VARCHAR of greater length than that. It sounds like a buffer-overrun problem, but I couldn't say where - in the postgresql ODBC drivers, or in other parts of the odbc layers / libraries. Most like not in the postgresql drivers though.

 

I don't know what BDE is - some sort of library on top of ODBC? If so, what happens if you try to insert/update such long strings using direct ODBC? Or from another platform than Delphi, from MS-Access for instance?

 

regards,

 

--Tim

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Cristian Custodio
Sent: donderdag 27 november 2003 12:59
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

I'm using LATIN1 but the problem happened with any other database.

 

It Occur with any varchar column greatest 255 characters.

 

Cristian

 

 

----- Original Message -----

From: Richard Combs

To: Cristian Custodio

Cc: pgsql-odbc@postgresql.org

Sent: Wednesday, November 26, 2003 2:50 PM

Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

I haven't used the BDE in a while, however it looks like a problem with character sets.  That is BDE is using one character set and PG is using another, hence the "invalid" characters.  Check the postgreSQL documentation on localization to see how to make your server recognize other language (character) sets.

One other question, does this happen only with varchar and only with that one column, or can you duplicate the problem with character type in another column?

Cristian Custodio wrote:

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Cristian Custodio
Sent: donderdag 27 november 2003 15:06
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

Its imposible. We are converting ours application in Oracle to PG.

 

I will try create a trigger or a function to resolve this problem.

 

Thanks,

 

Cristian

 

 

 

 

----- Original Message -----

From: Leeuw van der, Tim

To: pgsql-odbc@postgresql.org

Sent: Thursday, November 27, 2003 8:41 AM

Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

In quite a lot of databases, VARCHAR has an upper limit of 255 characters. I would say that most likely some of the libraries don't count of ever having a VARCHAR of greater length than that. It sounds like a buffer-overrun problem, but I couldn't say where - in the postgresql ODBC drivers, or in other parts of the odbc layers / libraries. Most like not in the postgresql drivers though.

 

I don't know what BDE is - some sort of library on top of ODBC? If so, what happens if you try to insert/update such long strings using direct ODBC? Or from another platform than Delphi, from MS-Access for instance?

 

regards,

 

--Tim

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Cristian Custodio
Sent: donderdag 27 november 2003 12:59
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

I'm using LATIN1 but the problem happened with any other database.

 

It Occur with any varchar column greatest 255 characters.

 

Cristian

 

 

----- Original Message -----

From: Richard Combs

To: Cristian Custodio

Cc: pgsql-odbc@postgresql.org

Sent: Wednesday, November 26, 2003 2:50 PM

Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

I haven't used the BDE in a while, however it looks like a problem with character sets.  That is BDE is using one character set and PG is using another, hence the "invalid" characters.  Check the postgreSQL documentation on localization to see how to make your server recognize other language (character) sets.

One other question, does this happen only with varchar and only with that one column, or can you duplicate the problem with character type in another column?

Cristian Custodio wrote:

----- Original Message -----

From: Leeuw van der, Tim

To: 'Cristian Custodio' ; pgsql-odbc@postgresql.org

Sent: Thursday, November 27, 2003 11:17 AM

Subject: RE: [ODBC] Character invalid saving memo with UpdateSQL

Hi,

 

If you're converting an application from Oracle to PG, that implies that in the Oracle DB you also had such large VARCHAR fields. And since that didn't give trouble, it furthermore implies that the problem is in the PG ODBC drivers.

 

Perhaps you should file a bug against them?

 

I have a varchar(1000) field in my db but since it's not actually used I haven't ran into any trouble with my application (using C#) (it's just a comment-field and I haven't filled the comment with sensible and large things in my tests, and I'm the only one testing with PG from time to time - regular testing is with MySQL and Oracle, production on Oracle only).

 

About testing with MS-Access, I thought you could perhaps try to open the db tables as linked tables in Access, and execute an update-query from there just to see whether or not any garbage is entered into the database instead of the proper info.

 

 

regards,

 

--Tim

 

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Cristian Custodio
Sent: donderdag 27 november 2003 15:06
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

Its imposible. We are converting ours application in Oracle to PG.

 

I will try create a trigger or a function to resolve this problem.

 

Thanks,

 

Cristian

 

 

 

 

----- Original Message -----

From: Leeuw van der, Tim

To: pgsql-odbc@postgresql.org

Sent: Thursday, November 27, 2003 8:41 AM

Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

In quite a lot of databases, VARCHAR has an upper limit of 255 characters. I would say that most likely some of the libraries don't count of ever having a VARCHAR of greater length than that. It sounds like a buffer-overrun problem, but I couldn't say where - in the postgresql ODBC drivers, or in other parts of the odbc layers / libraries. Most like not in the postgresql drivers though.

 

I don't know what BDE is - some sort of library on top of ODBC? If so, what happens if you try to insert/update such long strings using direct ODBC? Or from another platform than Delphi, from MS-Access for instance?

 

regards,

 

--Tim

-----Original Message-----
From: pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org]On Behalf Of Cristian Custodio
Sent: donderdag 27 november 2003 12:59
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

I'm using LATIN1 but the problem happened with any other database.

 

It Occur with any varchar column greatest 255 characters.

 

Cristian

 

 

----- Original Message -----

From: Richard Combs

To: Cristian Custodio

Cc: pgsql-odbc@postgresql.org

Sent: Wednesday, November 26, 2003 2:50 PM

Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

I haven't used the BDE in a while, however it looks like a problem with character sets.  That is BDE is using one character set and PG is using another, hence the "invalid" characters.  Check the postgreSQL documentation on localization to see how to make your server recognize other language (character) sets.

One other question, does this happen only with varchar and only with that one column, or can you duplicate the problem with character type in another column?

Cristian Custodio wrote:

I just did a quick test using the following setup

postgreSQL 7.4 running on Linux (SuSE 8.2)
ODBC client 7.03.02.00 on W2K - SP4
MDAC 2.8
Borland D7
BDE 5.01
    LANGDRIVER set to (blank - default setting none chosen)
    SQLQRYMODE set to SERVER
   

This is the test database structure

bigtest=# \encoding
SQL_ASCII

bigtest=# \d table1
            Table "public.table1"
 Column |          Type           | Modifiers
--------+-------------------------+-----------
 c1     | character(40)           |
 v2     | character varying(4000) |


Sample Delphi project

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

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
//    query1.ExecSQL;
{ the query is
    insert into table1 (c1,v2)
    values('characters',varchars)
}
    updatesql1.ExecSQL(ukInsert);
{ updatesql1 is linked to Table1
  the insert query is
    insert into table1 (c1,v2)
    values('characters-n','varchars-n')
}
end;

end.

All the above is linked through BDE to a user DSN
Advanced options set to
    Use Declare/Fetch
    Parse Statements
    Unknown Sizes as Longest
    Updateable Cursors
    Server side prepare
All else is default

Results after several inserts

bigtest=# select * from table1;
                    c1                    |     v2
------------------------------------------+------------
 characters                               | varchars
 characters                               | varchars
 characters                               | varchars
 characters-n                             | varchars-n
(4 rows)

These results are seen in both psql on the server and in the
DbGrid that is linked to the table.

No "invalid" characters were found.

I also tried changing the LANGDRIVER in the BDE to other encodings,
while leaving postgreSQL at the above encoding, and got the
same results.

I can't replicate your problem.  I upgraded to postgreSQL 7.4, and
no longer have 7.3.4 running, so I don't know if that is the problem,
or if it is a problem with BDE and D5.  Also note, I'm using
D7 Pro not D5, but I don't think the BDE components have
changed all that much over the last few versions.

One other thing check Borland's usage notes on the updatesql
component, they indicate it should only be used for readonly
datasets.  You might also try Borland's newsgroups see if
there is anything about the "invalid" characters in them.

HTH

Richard


Cristian Custodio wrote:

If you're converting an application from Oracle to PG, that implies that in the Oracle DB you also had such large VARCHAR fields. And since that didn't give trouble, it furthermore implies that the problem is in the PG ODBC drivers.

 

Perhaps you should file a bug against them?

I'm using delphi 5 and PG: 7.3.4 and BDE

 

I have 1 varchar(4000) column. When I try post any value in this column

using UpdateSQL it save very invalids caracters. Like this: ííííííííí.

 

Any body could help me?

 

Thanks,

 

Cristian Luciano Custodio

----- Original Message -----

From: Richard Combs

To: Cristian Custodio

Cc: pgsql-odbc@postgresql.org

Sent: Friday, November 28, 2003 4:45 AM

Subject: Re: [ODBC] Character invalid saving memo with UpdateSQL

I just did a quick test using the following setup

postgreSQL 7.4 running on Linux (SuSE 8.2)
ODBC client 7.03.02.00 on W2K - SP4
MDAC 2.8
Borland D7
BDE 5.01
    LANGDRIVER set to (blank - default setting none chosen)
    SQLQRYMODE set to SERVER
   

This is the test database structure

bigtest=# \encoding
SQL_ASCII

bigtest=# \d table1
            Table "public.table1"
 Column |          Type           | Modifiers
--------+-------------------------+-----------
 c1     | character(40)           |
 v2     | character varying(4000) |


Sample Delphi project

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

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
begin
//    query1.ExecSQL;
{ the query is
    insert into table1 (c1,v2)
    values('characters',varchars)
}
    updatesql1.ExecSQL(ukInsert);
{ updatesql1 is linked to Table1
  the insert query is
    insert into table1 (c1,v2)
    values('characters-n','varchars-n')
}
end;

end.

All the above is linked through BDE to a user DSN
Advanced options set to
    Use Declare/Fetch
    Parse Statements
    Unknown Sizes as Longest
    Updateable Cursors
    Server side prepare
All else is default

Results after several inserts

bigtest=# select * from table1;
                    c1                    |     v2
------------------------------------------+------------
 characters                               | varchars
 characters                               | varchars
 characters                               | varchars
 characters-n                             | varchars-n
(4 rows)

These results are seen in both psql on the server and in the
DbGrid that is linked to the table.

No "invalid" characters were found.

I also tried changing the LANGDRIVER in the BDE to other encodings,
while leaving postgreSQL at the above encoding, and got the
same results.

I can't replicate your problem.  I upgraded to postgreSQL 7.4, and
no longer have 7.3.4 running, so I don't know if that is the problem,
or if it is a problem with BDE and D5.  Also note, I'm using
D7 Pro not D5, but I don't think the BDE components have
changed all that much over the last few versions.

One other thing check Borland's usage notes on the updatesql
component, they indicate it should only be used for readonly
datasets.  You might also try Borland's newsgroups see if
there is anything about the "invalid" characters in them.

HTH

Richard


Cristian Custodio wrote:

If you're converting an application from Oracle to PG, that implies that in the Oracle DB you also had such large VARCHAR fields. And since that didn't give trouble, it furthermore implies that the problem is in the PG ODBC drivers.

 

Perhaps you should file a bug against them?

I'm using delphi 5 and PG: 7.3.4 and BDE

 

I have 1 varchar(4000) column. When I try post any value in this column

using UpdateSQL it save very invalids caracters. Like this: ííííííííí.

 

Any body could help me?

 

Thanks,

 

Cristian Luciano Custodio

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:

----- Original Message -----

From: Richard Combs

To: Cristian Custodio

Cc: pgsql-odbc@postgresql.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: