Recent

Author Topic: Select Error: Raspberry Pi 3 Jessie, Lazarus 1.8RC1, PostgreSQL 9.4.10  (Read 3464 times)

ertank

  • Sr. Member
  • ****
  • Posts: 274
Hello,

My detailed versions are:
Lazarus 1.8RC1 build from sources on Raspberry Pi 3. SVN Revision: 55121, Date: 27-05-2017, FPC version: 3.0.2 (also build from sources)
PostgreSQL 9.4.10 on armv7l-unknown-linux-gnueabihf, compiled by gcc (Raspbian 4.9.2-10) 4.9.2, 32-bit

Both are on the same device.

I have below table in my database:
Code: [Select]
CREATE TABLE personel
(
  adisoyadi character varying(40) NOT NULL,
  grup character varying(10),
  kayitzamani timestamp without time zone,
  kaydeden character varying(40),
  degisiklikzamani timestamp without time zone,
  degistiren character varying(40),
  CONSTRAINT personel_pkey PRIMARY KEY (adisoyadi)
)

When I do "select * from personel" PgAdmin III displaying that I have one record in it and all fields have value except degisiklikzamani and degistiren. These two fields both are null.

Problem 1: I start a fresh new Application, put PQConnection, SQLTransaction, SQLQuery on the form. Activate database connection at design time.

Write below in SQL property of SQLQuery to
Code: [Select]
select * from personel

When I try to activate SQLQuery (design time or run-time) I get error message saying:
Code: [Select]
SQLQuery1 : Field not found : "degisiklikzamani"

Problem 2: I create a test table:
Code: [Select]
CREATE TABLE test
(
  atext text,
  anumber integer
);
insert into test (anumber) values(1);
Do a select in PgAdmin III SQL Editor and I get single row with anumber having value of 1, atext is null (as expected)

If I try to use SQLQuery to "select * from test" this time I receive:
Code: [Select]
Access violation error.
It doesn't help to have single row with having both fields with some value. I still continue to get Access violation error. Error message is displayed both at  Activating and dis-activating SQLQuery.

Even I have Access violation error, a DataSource and DBGrid connected to SQLQuery display me records selected. This does not work with my above Problem 1. I do not get any record in this case.

I do not understand if I am doing wrong here. I have another table

Appreciate any help.

Thanks & regards,
-Ertan

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Some questions before we can help you:
1. Does your same program work on other OSes (maybe Windows or Linux)?
2. Have you read http://wiki.freepascal.org/postgres ?
3. Are you sure that client and server of PostgreSQL are matching each ohter?
4. Can you create a very small example where those erros happen and attach it here?
5. Are you sure that permissions on PostgreSQL are correctly set for your program's user?

ertank

  • Sr. Member
  • ****
  • Posts: 274
Below my answers to your questions.
1. No problem on Lazarus on Windows 64bit (target also 64bit executable) Date: 2017-05-14, FPC Version: 3.0.2, SVN Revision: 54919. Database server used is same one on my original post (PostgreSQL on Rasbperry Pi) accessed over network.
2. I've read it a long time ago. I re-read after you mention about it in your questions. Not sure what I am looking for in there though.
3. I am sure that client and server matches. I tried to emphasize that Lazarus using PostgreSQL both are on the same OS. PostgreSQL is installed using apt. There is no other version of it installed.
4. Attached is the small sample project from my Raspberry Pi device. However, one should create necessary tables manually.
5. No permission problem for sure. I am using postgres user to connect to database server.

Edit: I have another table which is fine to "select * from"
Code: [Select]
CREATE TABLE "public"."data" (
"serial" text,
"surgeryroomcode" text,
"surgeryroomname" text
)
All fields have some value in this table and there is just a single row in it. I am not sure why this works and others don't.
« Last Edit: June 01, 2017, 01:50:04 am by ertank »

ertank

  • Sr. Member
  • ****
  • Posts: 274
Problem is because of the default system locale settings. My Raspberry Pi was set to tr_TR.UTF-8 locale by default. PostgreSQL database initialization was made with this locale as well.

It seems somewhere in TSQLQuery code it is using system default locale to make CAPS or smallcaps the SQL string. Unfortunately, Turkish language has somewhat different capitalization for small letter "i" which becomes "İ" (I with dot at top) and another small letter "ı" (small i without dot at top) becomes "I" when capitalized.

If my SQL text or any of the field names in select list contains any "i" character it becomes a problem.

My solution I set system default language to en_US.UTF-8 and also add tr_TR.UTF-8 in generated locales list so that Lazarus correctly handles capitalization and PostgreSQL is happy because initdb is started with tr_TR.UTF-8 which is necessary for handling database operations right.

I believe it may be beneficial to modify source of TSQLQuery SQL parsing. It should still allow strictly provided names as it is. Something like select test as "Test" from some_table should not change "Test" and use as it is. However, select a.*, b.* from test a left join b on b.id = a.id should handle word "join" and "a.id" and "b.id" as to US locale. Being said that, this will be possible if using US locale is possible even it is not installed in the system locales (as it was in my  first case when I got error messages).

Thanks for your time.

Regards,
Ertan

sky_khan

  • Guest
Yeminle benim aklıma gelmişti :D Daha önce benim de başıma gelmişti ama windows dosya adlarında oluyodu böyle şeyler , postgre'de de baş ağrıtacağını düşünmemiştim. :)

 

TinyPortal © 2005-2018