Exception - Can't convert AsMemo

Discussion of open issues, suggestions and bugs regarding PgDAC (PostgreSQL Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
coderdan
Posts: 8
Joined: Mon 09 Mar 2009 16:21
Location: Gig Harbor, WA USA

Exception - Can't convert AsMemo

Post by coderdan » Sun 15 Mar 2009 07:55

Hi,

I am calling a PG function:
------------------------------------
CREATE OR REPLACE FUNCTION spLog(log_type integer, log_msg text, log_user integer)
RETURNS INTEGER AS $$
..........

However, when using either TpgSQL or TpgStoredProc the following happens

pgspLog.Params.Items[1].AsInteger := 1;
pgspLog.Params.Items[2].AsMemo := ''; //NO PROBLEM
pgspLog.Params.Items[3].AsInteger := 1;
pgspLog.Execute;

retval := pgspLog.Params[0].AsInteger;
showmessage(inttostr(retval));

-------------------------------------------------------
pgspLog.Params.Items[1].AsInteger := 1;
pgspLog.Params.Items[2].AsMemo := 'test'; //NOW WE HAVE PROBLEM AsString makes no difference
pgspLog.Params.Items[3].AsInteger := 1;
pgspLog.Execute;

retval := pgspLog.Params[0].AsInteger;
showmessage(inttostr(retval));

IF I attempt to modify the parameter 'log_msg' an exception is thrown:

Project pgTester.exe raised class Exception with message 'Cannot convert type'. Process Stopped. Use Step or Run to Continue.

Application Error: Exception Exception in module pgdac70.bpl at 00016E41


Any thoughts here?

Thanks,

Dan Wade

coderdan
Posts: 8
Joined: Mon 09 Mar 2009 16:21
Location: Gig Harbor, WA USA

Re: Exception - Can't convert AsMemo

Post by coderdan » Sun 15 Mar 2009 08:08

I have come up with a workaround:

Explanation:

I have kept my field in the table as TEXT. However, for the function/procedure I made a small change. PostgreSQL has a way of passing a varchar(character varying) without a size restriction. When you do that then pgDAC components see this as a STRING and not a MEMO.

Problem solved - or at least worked around for now.

Usage:

PostgreSQL function

CREATE OR REPLACE FUNCTION spLog(log_type integer, log_msg character varying, log_user integer)


Delphi

pgspLog.Params.Items[1].AsInteger := 1;
pgspLog.Params.Items[2].AsString := 'Test 123'; //No longer being identified as a memo field
pgspLog.Params.Items[3].AsInteger := 1;
pgspLog.Execute;

------------------------------------
CREATE OR REPLACE FUNCTION spLog(log_type integer, log_msg text, log_user integer)
RETURNS INTEGER AS $$
..........

-------------------------------------------------------
pgspLog.Params.Items[1].AsInteger := 1;
pgspLog.Params.Items[2].AsMemo := 'test'; //NOW WE HAVE PROBLEM AsString makes no difference
pgspLog.Params.Items[3].AsInteger := 1;
pgspLog.Execute;

retval := pgspLog.Params[0].AsInteger;
showmessage(inttostr(retval));

IF I attempt to modify the parameter 'log_msg' an exception is thrown:

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Tue 17 Mar 2009 13:23

We could not reproduce the problem. Please supply us the following information:
- exact version of PgDAC including build number (see PostgreSQL | About PgDAC in Delphi menu);
- exact version of Delphi.

coderdan
Posts: 8
Joined: Mon 09 Mar 2009 16:21
Location: Gig Harbor, WA USA

Post by coderdan » Tue 17 Mar 2009 15:25

pgDAC: 1.00.0.3
Delphi: Professional Version 7.0 Build 4.453
Windows: XP Professional Build 2600 SP2
PostgreSQL Version: PostgreSQL Database Server 8.3 (pgsql-8.3)

Again, the issue is setting the Parameter.AsMemo := 'xyz'; When the input parameter of the stored function is of type 'TEXT'. Change the input parameter of the stored function to 'CHARACTER VARYING'. This provides an appropriate workaround.

If you would like I can attach a project and include a stored function to replicate.
Plash wrote:We could not reproduce the problem. Please supply us the following information:
- exact version of PgDAC including build number (see PostgreSQL | About PgDAC in Delphi menu);
- exact version of Delphi.

Plash
Devart Team
Posts: 2844
Joined: Wed 10 May 2006 07:09

Post by Plash » Thu 19 Mar 2009 08:47

Please check this problem with the latest PgDAC version (1.00.0.4).

Post Reply