Re: Character invalid saving memo with UpdateSQL

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.

I have more detail about it.

The problem occur because the componet UpdateSQL
apply the update using params. Param[x].AsMemo (That is the problem)

I change the componente Update SQL to use AsString and it is Worked now.

I did the following modification in Unit: DBTables.pas, and Method: SetParams(UpdateKind: TUpdateKind);

if Old then Param.AssignFieldValue(Field, Field.OldValue) else
begin
Value := Field.NewValue;
if VarIsEmpty(Value) then Value := Field.OldValue;
if Field.DataType = ftMemo then begin
Param.AsString := Value;
end else begin
Param.AssignFieldValue(Field, Value);
end;
end;

I can send you my project in file attachment?

Cristian

----- Original Message -----
From: Richard Combs
To: Cristian Custodio
Cc: pgsql-odbc(at)postgresql(dot)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:

I'll do this, thanks.

Cristian

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