1

I am working on Typhon IDE (v.5.1 and FPC v.2.7.1)and am creating user login function, utilizing PostgreSQL 9.3 as database and Zeos 7.2 as data connector.

I have this following code:

   // ZQ: TZQuery;    
   zq := ExecSQL('SELECT '+
          'role_name, '+
          'role_enabled, '+
          'uid, '+
          'role_uid, '+
          'user_name, '+
          'enabled, '+
          'full_name, '+
          'user_email '+
          'FROM vw_usr_users '+
          'where user_name = '+QuotedStr(AUserName)+' and enabled = ''Y'' and user_password = md5('+QuotedStr(APassword)+');');
      ShowDebugSQL(zq.SQL);
      ShowMessage(IntToStr(zq.FieldCount));
      ShowMessage(zq.FieldByName('role_uid').AsString);
      if not zq.IsEmpty then
      begin
        if zq.FieldByName('role_uid').AsString = '' then
        begin
          MessageDlg('Error','User have no role...',mtError, [mbOK], 0);
        end
        else
        begin
            Self.FId:=zq.FieldByName('uid').AsString;
            Self.FUserName:=AUserName;
            Self.FPassword:=APassword;
            Self.fEnabled:= zq.FieldByName('enabled').AsString = 'Y';
            Self.fRoleID:=zq.FieldByName('role_uid').AsString;
            Self.FRoleName:=zq.FieldByName('role_name').AsString;
            Self.FFullName:= zq.FieldByName('full_name').AsString;
            Self.FEmail:= zq.FieldByName('user_email').AsString;
            Self.Log(LOG_ACTIVITY_LOGIN);
            Result := True;
        end;
     end
  //......
  • ExecSQL is a helper function, create TZQuery instance, assign connection and SQL, then open it.

When I run above code, I get error message stating that field 'role_uid' not found.

field 'role_uid' not found

and the

ShowMessage(IntToStr(zq.FieldCount));

displayed only 6 fields, but helper ShowDebugSQL I made to show up SQL within a memo 

ShowDebugSQL(zq.SQL);

gave me expected SQL

full SQL

and correct result as I tested on pgAdmin: pgAdmin shows 8 fields

I have tested the resulted field list using loop and got only 6 fields. Please help me point out what was wrong with my code. Many thanks.

Buyut Joko Rivai
  • 188
  • 2
  • 6
  • Try to connect a tzsqlmonitor to your form, and set it to log to file, check the logfile contents if the query really is put on the wire, or that zeos mangles it. Are both the GUID fields missing? Maybe it is GUID support, 2.7.1 as FPC version is a bit vague (it is a rolling development version spanning 3 years), could be it is not in there yet if it is an older one – Marco van de Voort May 16 '15 at 10:51
  • Thanks Marco, I'll give it a try and report back here. – Buyut Joko Rivai May 16 '15 at 10:58
  • Tried using TZSQLMonitor as @MarcovandeVoort suggested, logged SQL was correct: `2015-05-16 22:27:59 cat: Prepare, proto: postgresql-9, msg: Statement 1 : SELECT role_name, role_enabled, uid, role_uid, user_name, enabled, full_name, user_email FROM vw_usr_users where user_name = 'jokorivai' and enabled = 'Y' and user_password = md5('jokorivai');`. So I assume that the problem would happen a the time TZQuery dataset columns being built up. – Buyut Joko Rivai May 16 '15 at 14:40
  • After some checks, a thought came up: TZquery - and apparently also other Zeos datasets - of Zeos 7.2 doesn't support PostgreSQL fields of UUID type. – Buyut Joko Rivai May 16 '15 at 15:21
  • It might also be the base FPC database classes that zeos inherits from. Which is why I dissed 2.7.1 :-) – Marco van de Voort May 16 '15 at 18:41

1 Answers1

0

Zeos does not recognize PostgreSQL fields of UUID type. The UUID fields have to be type-casted to character varying(36) to make them available on Zeos datasets.

zq := Query('SELECT '+
      'role_name, '+
      'role_enabled, '+
      //---------------------------
      'uid::varchar(36) as uid, '+
      'role_uid::varchar(36) as role_uid, '+
      //---------------------------
      'user_name, '+
      'enabled, '+
      'full_name, '+
      'user_email '+
      'FROM vw_usr_users '+
      'where user_name = '+QuotedStr(AUserName)+' and enabled = ''Y'' and user_password = md5('+QuotedStr(APassword)+');', False);
Buyut Joko Rivai
  • 188
  • 2
  • 6