Type "e" does not exist.

[huminclouds]

Hi there

In delphi I have the following code:

Code: Select all

PgQuery1.SQL.Text := 'SELECT * '
+ 'FROM table1 '
+ 'WHERE field1 = :param1';
PgQuery1.ParamByName('param1').AsString := Edit1.Text;
PgQuery1.Open;

When I run this code I get the following error:

Type "e" does not exist.

I don't understand what am I doing wrong?

I use Dalphi XE 2 and PgDac 3.1.4

Thanks.

[AlexP]

Hello,

We could not reproduce the problem.
Please send a sample demonstrating the problem and a script to create the table to alexp*devart*com.

[huminclouds]

I'm afraid that doesn't help, because when I expand the dump to another server parametric queries work correctly.
This is quite strange, because the servers are configured identically.
And with version 2.6 this problem doesn't happen.

[AlexP]

Hello,

We cannot fix the problem until we reproduce it. That's why we need an example that demonstrates the problem.

[huminclouds]

I understand. But I don't know what to do. Well I will patch the queries without params.

[ipai]

We are also having this issue with pgDAC version 3.1.5 in Delphi 7. It appears to be related to parameters, as a non-parameterised version of the query works without any problems. Note that the problem occurs with PostgreSQL version 7.4.19 but does not happen with PostgreSQL version 9.0.5. Both databases are LATIN9 encoding.

This is the error message I get when the query is opened:

Code: Select all

---------------------------
Debugger Exception Notification
---------------------------
Project MyQ.exe raised exception class EOleException with message 'type "e" does not exist'. Process stopped. Use Step or Run to continue.
---------------------------
OK   Help   
---------------------------

The problem does not occur with all parameterised queries, but it occurs consistently with the following TpgQuery object:

Code: Select all

  object AuthenticationABRIpgQuery1: TPgQuery
    Connection = AdminPgConnection1
    SQL.Strings = (
      'select u.*, ua.soc_code, ua.access_option '
      'from users u '
      'left join user_auths ua on u.user_id = ua.user_id '
      'where u.user_status = ''A'' '
      'and u.user_id = :UserID '
      'and passwd = :Password;')
    ReadOnly = True
    Options.ExtendedFieldsInfo = False
    Options.UnknownAsString = True
    Options.UnpreparedExecute = True
    Left = 184
    Top = 304
    ParamData = 
  end

This is the relevant section from a log obtained via a TPgSQLMonitor component:

Code: Select all

11:20:38.191	Start: 
11:20:38.237	select u.*, ua.soc_code, ua.access_option 
from users u 
left join user_auths ua on u.user_id = ua.user_id 
where u.user_status = 'A' 
and u.user_id = :UserID 
and passwd = :Password;
:UserID(String[2],IN)='aj' 
:Password(String[16],IN)='vG85YqZ9zSziXw=='
11:20:38.316	Error: type "e" does not exist
11:20:41.784	Commit: 

This is a serious problem for us as we attempt to migrate from dbExpress to pgDAC, so I hope the above information will help you isolate and fix the problem quickly.

Regards,

Alex.

[ipai]

For completeness, this is the definition of the tables referred to in the parameterised query which fails under Postgres 7.4.19:

Code: Select all

CREATE TABLE users
(
  user_id character varying(8) NOT NULL,
  user_name character varying(40),
  user_manager_id character varying(8),
  passwd character varying(50),
  passwd_expiry_date date,
  db_passwd character varying(50),
  user_status character(1),
  email character varying(64),
  user_location character varying(40),
  user_phone character varying(32),
  user_contact_info character varying(100),
  default_printer_key smallint,
  hint_level smallint DEFAULT 1,
  signon_cnt smallint DEFAULT 0,
  failed_signon_cnt smallint DEFAULT 0,
  last_signon_date timestamp(0) with time zone,
  signon_tag integer DEFAULT 0,
  shell_opts character varying(32),
  create_date timestamp with time zone,
  create_user_id character varying(8),
  last_upd_date timestamp with time zone,
  last_upd_user_id character varying(8),
  location_list character varying(64),
  location_key integer,
  CONSTRAINT users_pkey PRIMARY KEY (user_id),
  CONSTRAINT "$1" CHECK (user_status = 'I'::bpchar OR user_status = 'A'::bpchar OR user_status = 'T'::bpchar OR user_status = 'L'::bpchar)
)
WITHOUT OIDS;

CREATE TABLE user_auths
(
  user_id character varying(8) NOT NULL,
  soc_code character varying(8) NOT NULL,
  access_option character varying(20),
  user_status character(1),
  db_name character varying(32),
  email character varying(64),
  default_printer_key smallint,
  create_date timestamp with time zone,
  create_user_id character varying(8),
  last_upd_date timestamp with time zone,
  last_upd_user_id character varying(8),
  location_key integer,
  CONSTRAINT user_auths_pkey PRIMARY KEY (user_id, soc_code),
  CONSTRAINT ua_db_name_fk FOREIGN KEY (soc_code, db_name)
      REFERENCES dbs (soc_code, db_name) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "$1" CHECK (user_status = 'I'::bpchar OR user_status = 'A'::bpchar OR user_status = 'T'::bpchar)
)
WITHOUT OIDS;

Because the query fails before it's even executed, I have confirmed that the actual data is irrelevant - the problem definitely occurs with the parameters given in my last post with empty tables as defined above.

Also, the connection object is defined as follows:

Code: Select all

  object AdminPgConnection1: TPgConnection
    Username = 'user'
    Password = 'password'
    Server = '192.168.0.100'
    LoginPrompt = False
    AfterConnect = PgConnection1AfterConnect
    BeforeConnect = PgConnection1BeforeConnect
    AfterDisconnect = PgConnection1AfterDisconnect
    Database = 'admin_db'
    Options.ApplicationName = 'pgDACTest'
    ConnectionTimeout = 1
    Left = 584
    Top = 56
  end

Could someone form Devart please reply to confirm that this problem is being investigated?

Thanks,

Alex.

[AlexP]

Hello,

This 7.4 PostgreSQL version doesn't support the 3rd version of the protocol, where the "real" support of parameters is implemented. To resolve the problem, you should set the 2nd version of the protocol in the TPgConnection.ProtocolVersion property, in this case, the parameter values will be explicitly substituted in SQL expressions, and your application will work on PostgreSQL 7.4

[ipai]

Thanks, Alex!

I'll make the necessary changes and give it a go. If you don't hear back from me, then everything is OK.

Regards,

Alex.

[ipai]

Hello Alex...

I'm afraid it still doesn't work. I have set the pgConnection ProtocolVersion to pv20 both at design time and also in the BeforeConnect event handler and I get exactly the same result with the Postgres 7 database.

Please advise when you have a fix for this problem.

Thanks,

Alex.

[AlexP]

Hello,

We have reproduced and fixed the problem, this fix will be included into the next build.
Currently, to solve the problem you can either set the UnpreparedExecute property to False, or if your has the source code version, we can send you the necessary fix.