Recent

Author Topic: Lazarus, PostgreSQL, "Field not found" error  (Read 630 times)

Ali KOCA

  • New Member
  • *
  • Posts: 12
Lazarus, PostgreSQL, "Field not found" error
« 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?
« Last Edit: July 24, 2019, 10:57:58 am by Ali KOCA »

tr_escape

  • Sr. Member
  • ****
  • Posts: 386
  • sector name toys | respect to spectre
    • Github:
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #1 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.
« Last Edit: July 24, 2019, 10:57:34 am by tr_escape »

Ali KOCA

  • New Member
  • *
  • Posts: 12
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #2 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.  




tr_escape

  • Sr. Member
  • ****
  • Posts: 386
  • sector name toys | respect to spectre
    • Github:
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #3 on: July 24, 2019, 01:08:45 pm »
What version do you using of postgresql?

Ali KOCA

  • New Member
  • *
  • Posts: 12
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #4 on: July 24, 2019, 01:41:53 pm »
PostgreSQL verison is 11.

Ali KOCA

  • New Member
  • *
  • Posts: 12
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #5 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.



Ali KOCA

  • New Member
  • *
  • Posts: 12
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #6 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.

Ali KOCA

  • New Member
  • *
  • Posts: 12
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #7 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>

Ali KOCA

  • New Member
  • *
  • Posts: 12
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #8 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"

tr_escape

  • Sr. Member
  • ****
  • Posts: 386
  • sector name toys | respect to spectre
    • Github:
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #9 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.  

Ali KOCA

  • New Member
  • *
  • Posts: 12
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #10 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.

tr_escape

  • Sr. Member
  • ****
  • Posts: 386
  • sector name toys | respect to spectre
    • Github:
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #11 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.

Ali KOCA

  • New Member
  • *
  • Posts: 12
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #12 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?

Ali KOCA

  • New Member
  • *
  • Posts: 12
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #13 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.

Ali KOCA

  • New Member
  • *
  • Posts: 12
Re: Lazarus, PostgreSQL, "Field not found" error
« Reply #14 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.