All right, here is the code:
unit Unit1;
{$mode objfpc}{$H+}
interface
uses
Classes, SysUtils, LResources, Forms, Controls, Graphics, Dialogs,
pqconnection, sqldb, DBGrids, DB;
type
{ TForm1 }
TForm1 = class(TForm)
Datasource1: TDatasource;
dbGrid1: TdbGrid;
PQConnection1: TPQConnection;
SQLQuery1: TSQLQuery;
SQLTransaction1: TSQLTransaction;
private
{ private declarations }
public
{ public declarations }
end;
var
Form1: TForm1;
implementation
initialization
{$I unit1.lrs}
end.
object Form1: TForm1
Left = 14
Height = 555
Top = 225
Width = 983
HorzScrollBar.Page = 982
VertScrollBar.Page = 554
ActiveControl = dbGrid1
Caption = 'Form1'
object dbGrid1: TdbGrid
Left = 9
Height = 460
Top = 66
Width = 967
AutoFillColumns = True
DataSource = Datasource1
FixedColor = clBtnFace
Options = [dgEditing, dgTitles, dgIndicator, dgColumnResize,
dgColumnMove, dgColLines, dgRowLines, dgTabs, dgAlwaysShowSelection,
dgConfirmDelete, dgCancelOnExit]
OptionsExtra = [dgeAutoColumns, dgeCheckboxColumn]
ParentColor = False
TabOrder = 0
TabStop = True
end
object PQConnection1: TPQConnection
Connected = True
DatabaseName = 'MONITORINGZ_WIN1250'
Password = 'nadaneumire'
Transaction = SQLTransaction1
UserName = 'postgres'
HostName = 'localhost'
left = 8
top = 8
end
object SQLTransaction1: TSQLTransaction
Active = True
Database = PQConnection1
left = 8
top = 48
end
object SQLQuery1: TSQLQuery
Active = True
AutoCalcFields = True
Database = PQConnection1
Transaction = SQLTransaction1
ReadOnly = True
SQL.Strings = (
'select departments.department,'
' plants.plant,'
' batches_microbs.sampling_type,'
' batches_microbs.batch,'
' batches_microbs.sampling_datetime, batches_microbs.status,'
' results_microbs.sample,'
' results_microbs.sample_description,'
' results_microbs.area,'
' results_microbs.area_description, results_microbs.class,'
' results_microbs.alert_limit,'
' results_microbs.action_limit,'
' results_microbs.frequency,'
' results_microbs.frequency_unit,'
' results_microbs.result,'
' results_microbs.measuring_unit,'
' results_microbs.sample_commentary,'
' alert_limit_exceeds(results_microbs.result,'
' results_microbs.alert_limit,'
' results_microbs.action_limit) as alert_limit_exceeds,'
' alert_limit_passes(results_microbs.result,'
' results_microbs.alert_limit) as alert_limit_passes,'
' action_limit_exceeds(results_microbs.result,'
' results_microbs.action_limit) as action_limit_exceeds,'
' action_limit_passes(results_microbs.result,'
' results_microbs.action_limit) as action_limit_passes,'
' exceeds_sum(results_microbs.result,'
' results_microbs.alert_limit,'
' results_microbs.action_limit) as exceeds_sum,'
' exceeds_total(exceeds_sum(results_microbs.result,'
' results_microbs.alert_limit,'
' results_microbs.action_limit)) as exceeds_total,'
' batches_microbs.batch_commentary'
'from ((results_microbs join batches_microbs on'
' (((results_microbs.batch) ='
' (batches_microbs.batch)))) join'
' (departments join plants on'
' ((departments.department ='
' plants.department))) on'
' (((batches_microbs.plant) ='
' (plants.plant))))'
'order by departments.department,'
' plants.plant,'
' results_microbs.sampling_type,'
' batches_microbs.sampling_datetime desc,'
' results_microbs.sample, batches_microbs.area;'
)
IndexDefs = <
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end
item
end>
Params = <>
UpdateMode = upWhereKeyOnly
UsePrimaryKeyAsKey = True
StatementType = stSelect
ParseSQL = True
left = 8
top = 88
end
object Datasource1: TDatasource
DataSet = SQLQuery1
left = 8
top = 136
end
end
-- Table: batches_microbs
-- DROP TABLE batches_microbs;
CREATE TABLE batches_microbs
(
plant varchar NOT NULL,
sampling_type varchar,
area varchar,
area_description text,
"class" varchar,
batch varchar NOT NULL,
sampling_datetime timestamp,
status varchar,
batch_commentary text,
"user" varchar DEFAULT "current_user"(),
"timestamp" timestamp DEFAULT now(),
batch_microbs_id int8 NOT NULL DEFAULT
nextval('batches_microbs_batch_microbs_id_seq'::regclass),
CONSTRAINT batches_microbs_pkey PRIMARY KEY (batch_microbs_id),
CONSTRAINT batches_microbs_fk FOREIGN KEY (plant)
REFERENCES plants (plant) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT batches_microbs_status_fkey FOREIGN KEY (status)
REFERENCES batch_status_localization (status_local) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION
)
WITH OIDS;
ALTER TABLE batches_microbs OWNER TO matalab;
GRANT ALL ON TABLE batches_microbs TO matalab;
GRANT INSERT ON TABLE batches_microbs TO "GROUP2";
GRANT INSERT ON TABLE batches_microbs TO "GROUP3";
GRANT UPDATE, INSERT, DELETE ON TABLE batches_microbs TO "GROUP5";
GRANT UPDATE, INSERT, DELETE ON TABLE batches_microbs TO "GROUP6";
GRANT UPDATE, INSERT, DELETE ON TABLE batches_microbs TO "ADMINS";
GRANT SELECT ON TABLE batches_microbs TO public;
GRANT UPDATE, INSERT, DELETE ON TABLE batches_microbs TO "GROUP7";
GRANT SELECT ON TABLE batches_microbs TO backup_operators;
-- Index: batches_microbs_batch_key
-- DROP INDEX batches_microbs_batch_key;
CREATE UNIQUE INDEX batches_microbs_batch_key
ON batches_microbs
USING btree
(batch);
-- Index: batches_microbs_idx1
-- DROP INDEX batches_microbs_idx1;
CREATE INDEX batches_microbs_idx1
ON batches_microbs
USING btree
(sampling_type);
-- Index: batches_microbs_idx2
-- DROP INDEX batches_microbs_idx2;
CREATE INDEX batches_microbs_idx2
ON batches_microbs
USING btree
(area);
-- Index: batches_microbs_idx3
-- DROP INDEX batches_microbs_idx3;
CREATE INDEX batches_microbs_idx3
ON batches_microbs
USING btree
("class");
-- Index: batches_microbs_idx4
-- DROP INDEX batches_microbs_idx4;
CREATE INDEX batches_microbs_idx4
ON batches_microbs
USING btree
(sampling_datetime);
-- Index: batches_microbs_idx5
-- DROP INDEX batches_microbs_idx5;
CREATE INDEX batches_microbs_idx5
ON batches_microbs
USING btree
(status);
-- Trigger: audit_public_batches_microbs_delete_trigger on batches_microbs
-- DROP TRIGGER audit_public_batches_microbs_delete_trigger ON
batches_microbs;
CREATE TRIGGER audit_public_batches_microbs_delete_trigger
AFTER DELETE
ON batches_microbs
FOR EACH ROW
EXECUTE PROCEDURE auditor.audit_public_batches_microbs_func_delete();
-- Trigger: audit_public_batches_microbs_insert_trigger on batches_microbs
-- DROP TRIGGER audit_public_batches_microbs_insert_trigger ON
batches_microbs;
CREATE TRIGGER audit_public_batches_microbs_insert_trigger
AFTER INSERT
ON batches_microbs
FOR EACH ROW
EXECUTE PROCEDURE auditor.audit_public_batches_microbs_func_insert();
-- Trigger: audit_public_batches_microbs_update_trigger on batches_microbs
-- DROP TRIGGER audit_public_batches_microbs_update_trigger ON
batches_microbs;
CREATE TRIGGER audit_public_batches_microbs_update_trigger
AFTER UPDATE
ON batches_microbs
FOR EACH ROW
EXECUTE PROCEDURE auditor.audit_public_batches_microbs_func_update();
-- Table: results_microbs
-- DROP TABLE results_microbs;
CREATE TABLE results_microbs
(
sample varchar NOT NULL,
sample_description text,
sampling_type varchar,
area varchar,
area_description text,
"class" varchar,
alert_limit numeric,
action_limit numeric,
frequency int8,
frequency_unit varchar,
batch varchar NOT NULL,
result numeric,
measuring_unit varchar,
sample_commentary text,
"user" varchar DEFAULT "current_user"(),
"timestamp" timestamp DEFAULT now(),
result_microbs_id int8 NOT NULL DEFAULT
nextval('results_microbs_result_microbs_id_seq'::regclass),
CONSTRAINT results_microbs_pkey PRIMARY KEY (result_microbs_id),
CONSTRAINT results_microbs_fk FOREIGN KEY (batch)
REFERENCES batches_microbs (batch) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH OIDS;
ALTER TABLE results_microbs OWNER TO matalab;
GRANT ALL ON TABLE results_microbs TO matalab;
GRANT INSERT ON TABLE results_microbs TO "GROUP2";
GRANT INSERT ON TABLE results_microbs TO "GROUP3";
GRANT UPDATE, INSERT, DELETE ON TABLE results_microbs TO "GROUP5";
GRANT UPDATE, INSERT, DELETE ON TABLE results_microbs TO "GROUP6";
GRANT UPDATE, INSERT, DELETE ON TABLE results_microbs TO "ADMINS";
GRANT SELECT ON TABLE results_microbs TO public;
GRANT UPDATE, INSERT, DELETE ON TABLE results_microbs TO "GROUP7";
GRANT SELECT ON TABLE results_microbs TO backup_operators;
-- Index: results_microbs_idx1
-- DROP INDEX results_microbs_idx1;
CREATE INDEX results_microbs_idx1
ON results_microbs
USING btree
(sampling_type);
-- Index: results_microbs_idx2
-- DROP INDEX results_microbs_idx2;
CREATE INDEX results_microbs_idx2
ON results_microbs
USING btree
(area);
-- Index: results_microbs_idx3
-- DROP INDEX results_microbs_idx3;
CREATE INDEX results_microbs_idx3
ON results_microbs
USING btree
("class");
-- Trigger: audit_public_results_microbs_delete_trigger on results_microbs
-- DROP TRIGGER audit_public_results_microbs_delete_trigger ON
results_microbs;
CREATE TRIGGER audit_public_results_microbs_delete_trigger
AFTER DELETE
ON results_microbs
FOR EACH ROW
EXECUTE PROCEDURE auditor.audit_public_results_microbs_func_delete();
-- Trigger: audit_public_results_microbs_insert_trigger on results_microbs
-- DROP TRIGGER audit_public_results_microbs_insert_trigger ON
results_microbs;
CREATE TRIGGER audit_public_results_microbs_insert_trigger
AFTER INSERT
ON results_microbs
FOR EACH ROW
EXECUTE PROCEDURE auditor.audit_public_results_microbs_func_insert();
-- Trigger: audit_public_results_microbs_update_trigger on results_microbs
-- DROP TRIGGER audit_public_results_microbs_update_trigger ON
results_microbs;
CREATE TRIGGER audit_public_results_microbs_update_trigger
AFTER UPDATE
ON results_microbs
FOR EACH ROW
EXECUTE PROCEDURE auditor.audit_public_results_microbs_func_update();
-- Table: departments
-- DROP TABLE departments;
CREATE TABLE departments
(
department varchar NOT NULL,
department_report bool NOT NULL DEFAULT false,
department_input bool NOT NULL DEFAULT true,
department_active bool NOT NULL DEFAULT true,
"timestamp" timestamp DEFAULT now(),
"user" varchar DEFAULT "current_user"(),
department_id int8 NOT NULL DEFAULT
nextval('departments_department_id_seq'::regclass),
CONSTRAINT departments_pkey PRIMARY KEY (department_id)
)
WITH OIDS;
ALTER TABLE departments OWNER TO matalab;
GRANT ALL ON TABLE departments TO matalab;
GRANT UPDATE, INSERT, DELETE ON TABLE departments TO "GROUP6";
GRANT UPDATE, INSERT, DELETE ON TABLE departments TO "ADMINS";
GRANT SELECT ON TABLE departments TO public;
GRANT UPDATE, INSERT, DELETE ON TABLE departments TO "GROUP7";
GRANT SELECT ON TABLE departments TO backup_operators;
-- Index: departments_department_key
-- DROP INDEX departments_department_key;
CREATE UNIQUE INDEX departments_department_key
ON departments
USING btree
(department);
-- Trigger: audit_public_departments_delete_trigger on departments
-- DROP TRIGGER audit_public_departments_delete_trigger ON departments;
CREATE TRIGGER audit_public_departments_delete_trigger
AFTER DELETE
ON departments
FOR EACH ROW
EXECUTE PROCEDURE auditor.audit_public_departments_func_delete();
-- Trigger: audit_public_departments_insert_trigger on departments
-- DROP TRIGGER audit_public_departments_insert_trigger ON departments;
CREATE TRIGGER audit_public_departments_insert_trigger
AFTER INSERT
ON departments
FOR EACH ROW
EXECUTE PROCEDURE auditor.audit_public_departments_func_insert();
-- Trigger: audit_public_departments_update_trigger on departments
-- DROP TRIGGER audit_public_departments_update_trigger ON departments;
CREATE TRIGGER audit_public_departments_update_trigger
AFTER UPDATE
ON departments
FOR EACH ROW
EXECUTE PROCEDURE auditor.audit_public_departments_func_update();
-- Table: plants
-- DROP TABLE plants;
CREATE TABLE plants
(
department varchar NOT NULL,
plant varchar NOT NULL,
plant_report bool NOT NULL DEFAULT false,
plant_input bool NOT NULL DEFAULT true,
plant_active bool NOT NULL DEFAULT true,
"timestamp" timestamp DEFAULT now(),
"user" varchar DEFAULT "current_user"(),
plant_id int8 NOT NULL DEFAULT nextval('plants_plant_id_seq'::regclass),
CONSTRAINT plants_pkey PRIMARY KEY (plant_id),
CONSTRAINT plants_fk FOREIGN KEY (department)
REFERENCES departments (department) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE CASCADE
)
WITH OIDS;
ALTER TABLE plants OWNER TO matalab;
GRANT ALL ON TABLE plants TO matalab;
GRANT UPDATE, INSERT, DELETE ON TABLE plants TO "GROUP6";
GRANT UPDATE, INSERT, DELETE ON TABLE plants TO "ADMINS";
GRANT SELECT ON TABLE plants TO public;
GRANT UPDATE, INSERT, DELETE ON TABLE plants TO "GROUP7";
GRANT SELECT ON TABLE plants TO backup_operators;
-- Index: vrste_proizvodnje_vrsta_proizvodnje_key
-- DROP INDEX vrste_proizvodnje_vrsta_proizvodnje_key;
CREATE UNIQUE INDEX vrste_proizvodnje_vrsta_proizvodnje_key
ON plants
USING btree
(plant);
-- Trigger: audit_public_plants_delete_trigger on plants
-- DROP TRIGGER audit_public_plants_delete_trigger ON plants;
CREATE TRIGGER audit_public_plants_delete_trigger
AFTER DELETE
ON plants
FOR EACH ROW
EXECUTE PROCEDURE auditor.audit_public_plants_func_delete();
-- Trigger: audit_public_plants_insert_trigger on plants
-- DROP TRIGGER audit_public_plants_insert_trigger ON plants;
CREATE TRIGGER audit_public_plants_insert_trigger
AFTER INSERT
ON plants
FOR EACH ROW
EXECUTE PROCEDURE auditor.audit_public_plants_func_insert();
-- Trigger: audit_public_plants_update_trigger on plants
-- DROP TRIGGER audit_public_plants_update_trigger ON plants;
CREATE TRIGGER audit_public_plants_update_trigger
AFTER UPDATE
ON plants
FOR EACH ROW
EXECUTE PROCEDURE auditor.audit_public_plants_func_update();
----- Original Message -----
From: "Joost van der Sluis" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Saturday, July 01, 2006 12:14 PM
Subject: Re: [lazarus] problems with SQLdb components and Postgres
On Sat, 2006-07-01 at 08:41 +0200, Zlatko Matić wrote:
I downloaded binaries Lazarus-0.9.16-20060528-win32.exe,
Lazarus-0.9.17-20060628-win32.exe, Lazarus-0.9.17-20060630-win32.exe. I
don't know which version of the compiler is included. How can I check it?
All these versions (the snapshots) have fpc 2.1.1. You can chech it to
tun 'ppc386.exe'. That will tell you the version.
And there are very much changes in sqldb between 2.0.2 and the latest
versions.
It could be that it's something windows-specific. Can you provide an
example? A table-create script, some test-data and code to show the
problem?
Joost
----- Original Message -----
From: "Joost van der Sluis" <[EMAIL PROTECTED]>
To: <[email protected]>
Sent: Friday, June 30, 2006 11:20 PM
Subject: Re: [lazarus] problems with SQLdb components and Postgres
On Fri, 2006-06-30 at 18:25 +0200, Zlatko Matić wrote:
> I have big problem with displaying results from a query, by using
> Postgres 8.1.X, TPQConnection, TSQLQuery and TSQLTransaction. I think
> that something is very wrong with SqlDB components...Values in some
> columns are not displayed at all, or are displayed incorrectly (zero
> insted of actual numerical value, the same datetime in all timestamp
> fields etc., empty cells in some varchar columns etc.).
> I tried with different queries and it is always the same: values in
> some columns are displayed correctly, while other columns are empty or
> with wrong values. It is not related to field's type, because some
> fields are correctly displayed while others are not, although having
> the same type.
> I tested also on the other computer and there was the same result.
> Win XP SP2, PostgreSQL 8.1.2 and PostgreSQL 8.1.4; encoding: WIN1250,
> recent Lazarus snapshots, as well as with current official Windows
> binary.
> Just for record, all the queries are previously tested on pgAdmin
> and MS Access front-end and are working without any problems on those
> platforms...
>
> Do you have any idea?
Which version of fpc are you using? If you want to use postgres for
real, you should use 2.0.3 or 2.1.1 from a recent date.
Joost.
_________________________________________________________________
To unsubscribe: mail [EMAIL PROTECTED] with
"unsubscribe" as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
_________________________________________________________________
To unsubscribe: mail [EMAIL PROTECTED] with
"unsubscribe" as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
--
Met vriendelijke groeten,
Joost van der Sluis
CNOC Informatiesystemen en Netwerken
http://www.cnoc.nl
_________________________________________________________________
To unsubscribe: mail [EMAIL PROTECTED] with
"unsubscribe" as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
_________________________________________________________________
To unsubscribe: mail [EMAIL PROTECTED] with
"unsubscribe" as the Subject
archives at http://www.lazarus.freepascal.org/mailarchives
