Lazarus

Programming => Databases => Topic started by: Ali KOCA on July 24, 2019, 10:48:34 am

Title: Lazarus, PostgreSQL, "Field not found" error
Post by: Ali KOCA on July 24, 2019, 10:48:34 am
Hello;
I created the database on PostgreSQL with the following script:
Code: MySQL  [Select][+][-]
  1. CREATE DATABASE db_test WITH OWNER u_test
  2.         TEMPLATE template0
  3.         ENCODING 'UTF-8'
  4.         LC_COLLATE 'tr_TR.UTF-8'
  5.         LC_CTYPE = 'tr_TR.UTF-8';

The table is as follows:
Code: MySQL  [Select][+][-]
  1. create table kullanicilar(
  2.    okytno                               serial                  NOT NULL,
  3.                 constraint pk__kullanicilar__okytno     primary key(okytno),  
  4.    kullanici_ismi               varchar(100)    NOT NULL,
  5.                 constraint uk__kullanicilar__kullanici_ismi     unique (kullanici_ismi),
  6.    faal                                 boolean                 NOT NULL        DEFAULT FALSE,
  7.    olusturulma_tarihi   timestamp               NOT NULL        DEFAULT Now()
  8. );

When I type the SELECT clause into the query object, it throws error like this:
'Field not found: "kullanici_ismi"'

What is the reason for this?

Is it possible to convert "i" in the column name to "İ"?
If so, how do I prevent it?
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: tr_escape on July 24, 2019, 10:55:19 am
Dear Ali,

Could you please create a example project about your configuration and you can upload as attachment.

Also please tell us what is your OS, lazarus / fpc versions.
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: Ali KOCA on July 24, 2019, 01:04:10 pm
Hello.
First of all, thank you for your interest.

Data unit.
Code: Pascal  [Select][+][-]
  1. unit dm_yp_01;
  2.  
  3. {$mode objfpc}{$H+}
  4.  
  5. interface
  6.  
  7. uses
  8.   Classes, SysUtils, db, pqconnection, sqldb, ZConnection, ZDataset;
  9.  
  10. type
  11.  
  12.   { TDataModule1 }
  13.  
  14.   TDataModule1 = class(TDataModule)
  15.     DataSource9: TDataSource;
  16.     dsKullanicilar: TDataSource;
  17.     DataSource2: TDataSource;
  18.     DataSource3: TDataSource;
  19.     DataSource4: TDataSource;
  20.     DataSource5: TDataSource;
  21.     DataSource6: TDataSource;
  22.     DataSource7: TDataSource;
  23.     DataSource8: TDataSource;
  24.     PQConnection1: TPQConnection;
  25.     qKullanicilarokytno: TLongintField;
  26.     SQLQuery1: TSQLQuery;
  27.     SQLTransaction1: TSQLTransaction;
  28.     ZConnection1: TZConnection;
  29.     qKullanicilar: TZQuery;
  30.     ZQuery2: TZQuery;
  31.     ZQuery3: TZQuery;
  32.     ZQuery4: TZQuery;
  33.     ZQuery5: TZQuery;
  34.     ZQuery6: TZQuery;
  35.     ZQuery7: TZQuery;
  36.     ZQuery8: TZQuery;
  37.     procedure ZConnection1AfterConnect(Sender: TObject);
  38.   private
  39.  
  40.   public
  41.  
  42.   end;
  43.  
  44. var
  45.   DataModule1: TDataModule1;
  46.  
  47. implementation
  48.  
  49. {$R *.lfm}
  50.  
  51. { TDataModule1 }
  52.  
  53. procedure TDataModule1.ZConnection1AfterConnect(Sender: TObject);
  54. begin
  55.  
  56. end;
  57.  
  58. end.

Code: Pascal  [Select][+][-]
  1. object DataModule1: TDataModule1
  2.   OldCreateOrder = False
  3.   Height = 580
  4.   HorizontalOffset = 488
  5.   VerticalOffset = 149
  6.   Width = 896
  7.   PPI = 96
  8.   object ZConnection1: TZConnection
  9.     ControlsCodePage = cCP_UTF8
  10.     AutoEncodeStrings = False
  11.     Properties.Strings = (
  12.       'AutoEncodeStrings='
  13.     )
  14.     Connected = True
  15.     AfterConnect = ZConnection1AfterConnect
  16.     HostName = 'localhost'
  17.     Port = 0
  18.     Database = 'db_test'
  19.     User = 'u_test'
  20.     Password = 'şifrem'
  21.     Protocol = 'postgresql'
  22.     left = 56
  23.     top = 40
  24.   end
  25.   object qKullanicilar: TZQuery
  26.     Connection = ZConnection1
  27.     SQL.Strings = (
  28.       'SELECT *'
  29.       'FROM kullanicilar'
  30.     )
  31.     Params = <>
  32.     left = 56
  33.     top = 424
  34.     object qKullanicilarokytno: TLongintField
  35.       FieldKind = fkData
  36.       FieldName = 'okytno'
  37.       Index = 0
  38.       LookupCache = False
  39.       ProviderFlags = [pfInUpdate, pfInWhere]
  40.       ReadOnly = False
  41.       Required = True
  42.     end
  43.   end
  44.   object dsKullanicilar: TDataSource
  45.     DataSet = qKullanicilar
  46.     left = 56
  47.     top = 496
  48.   end
  49.   object ZQuery2: TZQuery
  50.     Connection = ZConnection1
  51.     Params = <>
  52.     left = 160
  53.     top = 424
  54.   end
  55.   object DataSource2: TDataSource
  56.     DataSet = ZQuery2
  57.     left = 160
  58.     top = 496
  59.   end
  60.   object ZQuery3: TZQuery
  61.     Connection = ZConnection1
  62.     Params = <>
  63.     left = 248
  64.     top = 424
  65.   end
  66.   object DataSource3: TDataSource
  67.     DataSet = ZQuery3
  68.     left = 248
  69.     top = 496
  70.   end
  71.   object ZQuery4: TZQuery
  72.     Connection = ZConnection1
  73.     Params = <>
  74.     left = 352
  75.     top = 424
  76.   end
  77.   object DataSource4: TDataSource
  78.     DataSet = ZQuery4
  79.     left = 352
  80.     top = 496
  81.   end
  82.   object ZQuery5: TZQuery
  83.     Connection = ZConnection1
  84.     Params = <>
  85.     left = 456
  86.     top = 424
  87.   end
  88.   object DataSource5: TDataSource
  89.     DataSet = ZQuery5
  90.     left = 456
  91.     top = 496
  92.   end
  93.   object ZQuery6: TZQuery
  94.     Connection = ZConnection1
  95.     Params = <>
  96.     left = 560
  97.     top = 424
  98.   end
  99.   object DataSource6: TDataSource
  100.     DataSet = ZQuery6
  101.     left = 560
  102.     top = 496
  103.   end
  104.   object ZQuery7: TZQuery
  105.     Connection = ZConnection1
  106.     Params = <>
  107.     left = 648
  108.     top = 424
  109.   end
  110.   object DataSource7: TDataSource
  111.     DataSet = ZQuery7
  112.     left = 648
  113.     top = 496
  114.   end
  115.   object ZQuery8: TZQuery
  116.     Connection = ZConnection1
  117.     Params = <>
  118.     left = 752
  119.     top = 424
  120.   end
  121.   object DataSource8: TDataSource
  122.     DataSet = ZQuery8
  123.     left = 752
  124.     top = 496
  125.   end
  126.   object PQConnection1: TPQConnection
  127.     Connected = True
  128.     LoginPrompt = False
  129.     DatabaseName = 'db_test'
  130.     KeepConnection = False
  131.     Password = 'şifrem'
  132.     Transaction = SQLTransaction1
  133.     UserName = 'u_test'
  134.     CharSet = 'tr_TR.UTF-8'
  135.     HostName = 'localhost'
  136.     left = 248
  137.     top = 40
  138.   end
  139.   object SQLQuery1: TSQLQuery
  140.     IndexName = 'DEFAULT_ORDER'
  141.     FieldDefs = <    
  142.       item
  143.         Name = 'okytno'
  144.         DataType = ftInteger
  145.         Precision = -1
  146.       end    
  147.       item
  148.         Name = 'kullanici_ismi'
  149.         DataType = ftString
  150.         Precision = -1
  151.         Size = 100
  152.       end    
  153.       item
  154.         Name = 'faal'
  155.         DataType = ftBoolean
  156.         Precision = -1
  157.       end    
  158.       item
  159.         Name = 'olusturulma_tarihi'
  160.         DataType = ftDateTime
  161.         Precision = -1
  162.       end>
  163.     Database = PQConnection1
  164.     Transaction = SQLTransaction1
  165.     SQL.Strings = (
  166.       'SELECT okytno, kullanici_ismi, faal, olusturulma_tarihi'
  167.       'FROM sch_test.kullanicilar'
  168.     )
  169.     Params = <>
  170.     left = 368
  171.     top = 37
  172.   end
  173.   object DataSource9: TDataSource
  174.     DataSet = SQLQuery1
  175.     left = 472
  176.     top = 37
  177.   end
  178.   object SQLTransaction1: TSQLTransaction
  179.     Active = True
  180.     Database = PQConnection1
  181.     left = 248
  182.     top = 112
  183.   end
  184. end
  185.  



Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: tr_escape on July 24, 2019, 01:08:45 pm
What version do you using of postgresql?
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: Ali KOCA on July 24, 2019, 01:41:53 pm
PostgreSQL verison is 11.
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: Ali KOCA on July 24, 2019, 01:44:52 pm
In the meantime, I changed the sql table creation script.
And I tried again. He keeps making the same mistake.

Code: MySQL  [Select][+][-]
  1. CREATE TABLE SCH_ACSHB.KULLANICILAR3(
  2.    OKYTNO               SERIAL          NOT NULL,
  3.         CONSTRAINT PK__KULLANICILAR3__OKYTNO PRIMARY KEY(OKYTNO),  
  4.    KULLANICI_ISMI       VARCHAR(100)    NOT NULL,
  5.         CONSTRAINT UK__KULLANICILAR3__KULLANICI_ISMI UNIQUE (KULLANICI_ISMI),
  6.    FAAL                 BOOLEAN         NOT NULL    DEFAULT FALSE,
  7.    OLUSTURULMA_TARIHI   TIMESTAMP       NOT NULL    DEFAULT NOW()
  8. );
Select sql script:

Code: MySQL  [Select][+][-]
  1. SELECT OKYTNO, "KULLANICI_ISMI", FAAL, "OLUSTURULMA_TARIHI"
  2. FROM SCH_ACSHB.KULLANICILAR3
  3. ORDER BY KULLANICI_ISMI

I know that there is no upper-case/lower-case separation. But why is he doing this? Could it be about the language of the database?

Selamlar.


Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: Ali KOCA on July 24, 2019, 01:54:09 pm
I am using DBEAVER ce. Both select statements below give no errors. It works and the data comes in.

Code: MySQL  [Select][+][-]
  1. SELECT OKYTNO, KULLANICI_ISMI, FAAL, OLUSTURULMA_TARIHI
  2. FROM SCH_ACSHB.KULLANICILAR3
  3. ORDER BY KULLANICI_ISMI;

Code: MySQL  [Select][+][-]
  1. SELECT okytno, kullanici_ismi, faal, olusturulma_tarihi
  2. FROM sch_acshb.kullanicilar3;
I think DBEAVER is made in java and uses jdbc. Why doesn't he make that mistake, but there's an error with these free pascal components.
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: Ali KOCA on July 24, 2019, 02:05:14 pm
Hello;
I'm connecting to the same database as PHP. And I can pull the data. This error only occurs when working with lazarus.

Code: PHP  [Select][+][-]
  1. <!DOCTYPE html>
  2. <html lang="tr">
  3.  
  4. <head>
  5.     <meta charset="utf-8">
  6.     <title>PostgreSQL Veritabanına PHP den bağlanabiliyorum.</title>
  7. </head>
  8.  
  9. <body>
  10.     <h1>Kullanıcılar Listesi</h1>
  11.     <?php
  12.     $db = pg_connect("host=localhost port=5432 dbname=db_tecrube user=u_tecrube password=şifrem");
  13.     $result = pg_query($db, "SELECT okytno, kullanici_ismi, faal, olusturulma_tarihi
  14.                             FROM kullanicilar3");
  15.     echo "<table>";
  16.     while ($row = pg_fetch_assoc($result)) {
  17.         echo "<tr>";
  18.         echo "<td align='center' width='200'>" . $row['okytno'] . "</td>";
  19.         echo "<td align='center' width='200'>" . $row['kullanici_ismi'] . "</td>";
  20.         echo "<td align='center' width='200'>" . $row['faal'] . "</td>";
  21.         echo "<td align='center' width='200'>" . $row['olusturulma_tarihi'] . "</td>";
  22.         echo "</tr>";
  23.     }
  24.     echo "</table>"; ?>
  25.     </div>
  26. </body>
  27.  
  28. </html>
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: Ali KOCA on July 24, 2019, 02:16:09 pm

OS: Linux MX Last Version (MX-18.3_x64, with a 64 bit kernel)
Lazarus: 2.0.2
FPC: 3.0.4
PostgreS: 11.4 (Debian 11.4-1.pgdg90+1)

Error: qKullanicilar : Field not found : "kullanici_ismi"
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: tr_escape on July 25, 2019, 09:36:55 am

OS: Linux MX Last Version (MX-18.3_x64, with a 64 bit kernel)
Lazarus: 2.0.2
FPC: 3.0.4
PostgreS: 11.4 (Debian 11.4-1.pgdg90+1)

Error: qKullanicilar : Field not found : "kullanici_ismi"

Actually I am using win64 but I will try on linux too.

So I tried by psqlODBC dlls and zeos dbo I got only catalog names in windows.

I couldn't get table names too maybe the zeos team should work on it.

Note: My postgresql version is 11.4 64 bits but my client dll is 32 bits.

Code: Pascal  [Select][+][-]
  1. object Form1: TForm1
  2.   Left = 377
  3.   Height = 360
  4.   Top = 176
  5.   Width = 845
  6.   Caption = 'Form1'
  7.   DesignTimePPI = 144
  8.   LCLVersion = '2.1.0.0'
  9.   object DataSource1: TDataSource
  10.     Left = 100
  11.     Top = 106
  12.   end
  13.   object ZConnection1: TZConnection
  14.     ControlsCodePage = cCP_UTF8
  15.     AutoEncodeStrings = False
  16.     ClientCodepage = 'UTF8'
  17.     Catalog = 'db_test'
  18.     Properties.Strings = (
  19.       'AutoEncodeStrings='
  20.       'codepage=UTF8'
  21.     )
  22.     Connected = True
  23.     DesignConnection = True
  24.     HostName = '127.0.0.1'
  25.     Port = 5432
  26.     User = 'postgres'
  27.     Password = 'post1234'
  28.     Protocol = 'postgresql'
  29.     LibraryLocation = 'C:\Program Files (x86)\PostgreSQL\psqlODBC\bin\libpq.dll'
  30.     Left = 272
  31.     Top = 24
  32.   end
  33.   object ZTable1: TZTable
  34.     Connection = ZConnection1
  35.     Left = 272
  36.     Top = 106
  37.   end
  38. end
  39.  
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: Ali KOCA on July 26, 2019, 10:42:30 am
I've tried the other components. The result has not changed. It's like a problem with lazarus.
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: tr_escape on July 26, 2019, 11:20:39 am
I've tried the other components. The result has not changed. It's like a problem with lazarus.

Maybe, but to be sure you have try by last know stable version of the PostgreSQL (9.6 I think) and Lazarus's DB components (also ZeosDBO).

If you wish you can create a bug report about your work-around.
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: Ali KOCA on July 26, 2019, 02:37:20 pm
Hello;

Code: MySQL  [Select][+][-]
  1. SELECT okytno, kullanici_ismi as kullanici, faal, olusturulma_tarihi as tarih
  2. FROM kullanicilar;


If I write it this way, it doesn't give an error.

Do you think of anything?
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: Ali KOCA on August 01, 2019, 11:00:44 am
Hi.

SELECT OKYTNO, "kullanici_ismi"
FROM kullanicilar;
This is the way it works via dbeaver. However, Lazarus says he could not find the column again.

SELECT OKYTNO, "KULLANICI_ISMI"
FROM kullanicilar;
In this way, neither dbeaver nor over lazarus does not work. The double quotes character (") provides upper case separation.


select  kullanici_ismi as isimisimisimis, kullanici_ismi as is_im
FROM kullanicilar;
That's how it not works.


select  kullanici_ismi as is_im
FROM kullanicilar;

That's how it works.
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: Ali KOCA on August 01, 2019, 11:01:46 am
Hi.
Code: MySQL  [Select][+][-]
  1. SELECT OKYTNO, "kullanici_ismi"
  2. FROM kullanicilar;
This is the way it works via dbeaver. However, Lazarus says he could not find the column again.

Code: MySQL  [Select][+][-]
  1. SELECT OKYTNO, "KULLANICI_ISMI"
  2. FROM kullanicilar;
In this way, neither dbeaver nor over lazarus does not work. The double quotes character (") provides upper case separation.

Code: MySQL  [Select][+][-]
  1. select  kullanici_ismi as isimisimisimis, kullanici_ismi as is_im
  2. FROM kullanicilar;
That's how it not works.


Code: MySQL  [Select][+][-]
  1. select  kullanici_ismi as is_im
  2. FROM kullanicilar;

That's how it works.
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: Ali KOCA on August 01, 2019, 11:05:17 am
Code: MySQL  [Select][+][-]
  1. select  kullanici_ismi as is_im
  2. FROM kullanicilar;
  3.  

And there is both "i" and "_".

I don't think it's about the Turkish locale. And I've never had such a problem except postgres.
Title: Re: Lazarus, PostgreSQL, "Field not found" error
Post by: altanbozoglu on December 01, 2019, 06:33:31 pm
Hi;
It's been a long time ago, but maybe this solution might work.
The problem is that lazarus and fpc treat unicode strings differently.
If you test with a simple console application, you will see that the queries are running. The problem is somewhat related to the cwstring unit.
In my opinion, the best thing to do is to run the database part as a dynamic link library. I made a very simple example below.

Library
Code: Pascal  [Select][+][-]
  1.  
  2. library mydatalink;
  3.  
  4. {$mode objfpc}{$H+}
  5.  
  6. uses
  7.    Classes, SysUtils, parentunit, ZDataset, ZConnection, db;
  8.  
  9. function ServerCnn():Boolean cdecl;
  10. Begin
  11.   lasterror := False;
  12.  try
  13.   SQLServerCnn := TZConnection.Create(nil);
  14.   SQLServerCnn.Protocol  := 'postgresql';
  15.   SQLServerCnn.Database  := 'yourdatabasename';
  16.   SQLServerCnn.User      := 'usernamame';
  17.   SQLServerCnn.Password  := 'password';
  18.   SQLServerCnn.HostName  := 'serverip';
  19.   SQLServerCnn.Connected := True;
  20.   Result := SQLServerCnn.Connected;
  21.   Except on E : Exception do
  22.   Begin
  23.     lasterror    := True;
  24.     ErrorClass   := E.ClassName;
  25.     ErrorMessage := E.Message;
  26.   end;
  27.  end;
  28. end;
  29.  
  30. function SQLrun( SQLText: String):TDataSet; cdecl;
  31. var
  32.  sql:TZQuery;
  33. Begin
  34.   lasterror := False;
  35.   try
  36.     sql := TZQuery.Create(SQLServerCnn);
  37.     sql.Connection := SQLServerCnn;
  38.     sql.SQL.Text := SQLText;
  39.     sql.Open;
  40.     Result := sql;
  41.     Except on E : Exception do
  42.     Begin
  43.       lasterror    := True;
  44.       ErrorClass   := E.ClassName;
  45.       ErrorMessage := E.Message;
  46.       Result := nil;
  47.     end;
  48.   end;
  49. end;
  50.  
  51. function ErrorExsist():Boolean; cdecl;
  52. Begin
  53.   Result := lasterror;
  54. end;
  55.  
  56. function GetErrorClass():String; cdecl;
  57. Begin
  58.   Result := ErrorClass;
  59. end;
  60.  
  61. function GetErrorMessage():String; cdecl;
  62. Begin
  63.   Result := ErrorMessage;
  64. end;
  65.  
  66.  
  67. exports
  68. ServerCnn, SQLrun, ErrorExsist, GetErrorClass, GetErrorMessage;
  69. end.
  70.  
  71. //--------------------------------------------------------------
  72.  
  73. unit parentunit;
  74.  
  75. {$mode objfpc}{$H+}
  76.  
  77. interface
  78.  
  79. uses
  80.   Classes, ZConnection;
  81.  
  82.  type
  83.  
  84.    { TParentObject }
  85.  
  86.    TParentObject = class(TComponent)
  87.    private
  88.    public
  89.      connected:Boolean;
  90.      constructor Create(AOwner: TComponent); override;
  91.    end;
  92.  
  93.  var
  94.    ParentObj:TParentObject;
  95.    lasterror:Boolean;
  96.    ErrorClass:String;
  97.    ErrorMessage:String;
  98.    SQLServerCnn:TZConnection;
  99.  
  100. implementation
  101.  
  102.  
  103.  
  104. { TParentObject }
  105.  
  106. constructor TParentObject.Create(AOwner: TComponent);
  107. begin
  108.    inherited;
  109. end;
  110.  
  111.  
  112. end.
  113.  


Note: After compiling the dynamic link library, remember to copy it to /usr/lib

Sample Application
Code: Pascal  [Select][+][-]
  1.  
  2.   //...
  3.   function ServerCnn():Boolean cdecl; external 'libmydatalink.so';
  4.   function SQLrun( SQLText: String):TDataSet; cdecl; external 'libmydatalink.so';
  5.   function ErrorExsist():Boolean; cdecl; external 'libmydatalink.so';
  6.   function GetErrorClass():String; cdecl; external 'libmydatalink.so';
  7.   function GetErrorMessage():String; cdecl; external 'libmydatalink.so';
  8.  
  9. var
  10.   Form1: TForm1;
  11.  
  12. implementation
  13.  
  14. {$R *.lfm}
  15.  
  16. { TForm1 }  
  17.  
  18. procedure TForm1.Button1Click(Sender: TObject);
  19. begin
  20.  if ServerCnn() then
  21.    ShowMessage('Connected');
  22. end;
  23.  
  24. procedure TForm1.Button2Click(Sender: TObject);
  25. begin
  26.   DataSource1.DataSet := SQLrun('select * from testtable');
  27.   if ErrorExsist() then
  28.   Memo1.Lines.Text := GetErrorMessage();
  29. end;
  30.        
  31.  

Cheers..
TinyPortal © 2005-2018