Devart Forums

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

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:

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.

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.

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