Query Error

The BETA tester's forum for ZeosLib 7.2 Report problems. Ask for help, post proposals for the new version and Zeoslib 7.2 features here. This is a forum that will be edited once the 7.2.x version goes into RC/stable!!

My personal intention for 7.2 is to speed up the internals as optimal a possible for all IDE's. Hope you can help?! Have fun with testing 7.2
konsul41
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 11.12.2013, 09:09
Location: Poland

Query Error

Postby konsul41 » 01.08.2017, 07:32

I have sql

Code: Select all

CREATE OR REPLACE FUNCTION dajmiesieczneodpisyzarok(
    IN rok character varying,
    IN id_srodektrwaly integer)
  RETURNS TABLE(id_srodektrwaly numeric, styczen numeric, luty numeric, marzec numeric, kwiecien numeric, maj numeric, czerwiec numeric, lipiec numeric, sierpien numeric, wrzesien numeric, pazdziernik numeric, listopad numeric, grudzien numeric) AS
$BODY$ SELECT * FROM crosstab( 'select id_srodektrwaly::numeric,miesiac::numeric,miesodpis::numeric from planamortyzacji where rok='''||$1||''' and id_srodektrwaly='''||$2||''' order by id_srodektrwaly') AS final_result(id_srodektrwaly numeric,m01 numeric,m02 numeric,m03 numeric,m04 numeric,m05 numeric,m06 numeric,m07 numeric,m08 numeric,m09 numeric,m10 numeric,m11 numeric,m12 numeric); $BODY$
  LANGUAGE sql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION dajmiesieczneodpisyzarok(character varying, integer)
  OWNER TO wij;



Delphi code

Code: Select all

var
  Query: TZQuery;
begin
  Query := TZQuery.Create(nil);
  try
    Query.Connection := ZQuery1.Connection;
    Query.SQL.Text := 'select exists(select * from pg_proc where proname = ''dajmiesieczneodpisyzarok'');';
    Query.Open;
    if not Query.FieldByName('exists').AsBoolean then
    begin
      Query.Close;
      Query.SQL.Clear;
      Query.SQL.add('CREATE OR REPLACE FUNCTION dajmiesieczneodpisyzarok( ');
      Query.SQL.add(' IN rok character varying, ');
      Query.SQL.add(' IN id_srodektrwaly integer) ');
      Query.SQL.add(' RETURNS TABLE(id_srodektrwaly numeric,styczeń numeric,luty numeric,marzec numeric,');
      Query.SQL.add('kwiecień numeric,maj numeric,czerwiec numeric,lipiec numeric,sierpień numeric,wrzesień numeric,październik numeric,listopad numeric,grudzień numeric) AS ');
      Query.SQL.add(' $BODY$ ');
      Query.SQL.add('       SELECT * ');
      Query.SQL.add('         FROM crosstab( ''select id_srodektrwaly::numeric,miesiac::numeric,miesodpis::numeric from planamortyzacji where rok=''''2017'''' and id_srodektrwaly=16 order by id_srodektrwaly');
      Query.SQL.add('            AS final_result(id_srodektrwaly numeric,m1 numeric,m2 numeric,m3 numeric,m4 numeric,m5 numeric,m6 numeric,m7 numeric,m8 numeric,m9 numeric,10 numeric,11 numeric,12 numeric); ');
      Query.SQL.add(' $BODY$ ');
      Query.SQL.add(' LANGUAGE sql VOLATILE ');
      Query.SQL.add(' COST 100 ');
      Query.SQL.add(' ROWS 1000; ');
      Query.SQL.add(' ALTER FUNCTION dajmiesieczneodpisyzarok(character varying,  integer) ');
      Query.SQL.add(' OWNER TO wij; ');
      Query.ExecSQL;
    end;
  finally
    FreeAndNil(Query);
  end;
end;


In PGadmin the function writes correctly and works as expected.

Unfortunately, I have a problem to type it into the database using Zeos
No matter if I type it directly
Query.SQl.text: = '...';

Or using the resource string
resourcestring
sql = '...';
Query.SQL.Text: = sql;

Or using
Query.Close;
Query.SQL.Clear;
Query.SQL.add ('...');

I still get Access Violation
this
Query.SQL.add ('SELECT * FROM scheduling;');
Of course, it does not make mistakes.

I buried a little I followed
And crashes into a function
Function TZTokenizer.TokenizeStreamToList (Stream: TStream;
Options: TZTokenOptions): TStrings;

Has a problem with the sign ::

this
FROM crosstab ('select id_srodektrwaly :: numeric ...
I changed
on

Query.SQL.add ('FROM crosstab (' 'select');
Query.SQL.add ('CAST (id_srodektrwaly AS numeric)');
...

I'm not lecturing here but still can not handle SQL in ExecSQL
The problem is that around the Unicode and the string break on the Token may not cope with the "nested" SQL.

How to deal with this?

The best thing is that if I put Query on the form and execute SQL it is all right.

marsupilami
Gold Boarder
Gold Boarder
Posts: 590
Joined: 17.01.2011, 14:17

Re: Query Error

Postby marsupilami » 03.08.2017, 10:36

Hello,

could you provide a sample database and a sample project?

With best regards,

Jan

konsul41
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 11.12.2013, 09:09
Location: Poland

Re: Query Error

Postby konsul41 » 03.08.2017, 11:58

The whole base does not matter
Underneath sql

Code: Select all


CREATE TABLE planamortyzacji (
    id integer NOT NULL,
    id_srodektrwaly integer,
    lp integer,
    rok character varying(4),
    miesiac character varying(2),
    wartoscpoczatkowa numeric(11,2),
    wartoscumozenia numeric(11,2),
    podliczamor numeric(11,2),
    rocznyodpis numeric(11,2),
    miesodpis numeric(11,2),
    amornarastajaco numeric(11,2),
    amortyzacjapocz numeric(11,2),
    metoda integer DEFAULT 0
);


INSERT INTO planamortyzacji VALUES (12419, 40, 14, '1996', '7', 162430.40, 8527.59, 162430.40, 7309.37, 609.11, 153902.81, 153902.81, 1);
INSERT INTO planamortyzacji VALUES (12420, 40, 15, '1996', '8', 162430.40, 9136.70, 162430.40, 7309.37, 609.11, 153293.70, 153293.70, 1);
INSERT INTO planamortyzacji VALUES (12421, 40, 16, '1996', '9', 162430.40, 9745.81, 162430.40, 7309.37, 609.11, 152684.59, 152684.59, 1);
INSERT INTO planamortyzacji VALUES (12422, 40, 17, '1996', '10', 162430.40, 10354.92, 162430.40, 7309.37, 609.11, 152075.48, 152075.48, 1);
INSERT INTO planamortyzacji VALUES (12423, 40, 18, '1996', '11', 162430.40, 10964.03, 162430.40, 7309.37, 609.11, 151466.37, 151466.37, 1);
INSERT INTO planamortyzacji VALUES (12424, 40, 19, '1996', '12', 162430.40, 11573.19, 162430.40, 7309.37, 609.16, 150857.21, 150857.21, 1);
INSERT INTO planamortyzacji VALUES (13056, 41, 264, '2017', '5', 47971.10, 47491.40, 47971.10, 2158.70, 179.89, 479.70, 479.70, 1);
INSERT INTO planamortyzacji VALUES (13057, 41, 265, '2017', '6', 47971.10, 47671.29, 47971.10, 2158.70, 179.89, 299.81, 299.81, 1);
INSERT INTO planamortyzacji VALUES (13058, 41, 266, '2017', '7', 47971.10, 47851.18, 47971.10, 2158.70, 179.89, 119.92, 119.92, 1);
INSERT INTO planamortyzacji VALUES (13059, 41, 267, '2017', '8', 47971.10, 47971.10, 47971.10, 2158.70, 119.92, 0.00, 0.00, 1);


I have no way to upload the project additionally I will have other data to connect to DataBase

I've already amputated the function

marsupilami
Gold Boarder
Gold Boarder
Posts: 590
Joined: 17.01.2011, 14:17

Re: Query Error

Postby marsupilami » 03.08.2017, 15:04

Usually you should be able to attach a zip file to your post? I will check into this...

konsul41
Fresh Boarder
Fresh Boarder
Posts: 10
Joined: 11.12.2013, 09:09
Location: Poland

Re: Query Error

Postby konsul41 » 04.08.2017, 10:08

The attachment is a project
First, create a database from the specified SQL code.
The CONTRIB (linux) plugin must be installed.
and

Code: Select all

CREATE EXTENSION IF NOT EXISTS tablefunc

Must be running from superuser level
to.rar


The problem lies probably in my SQL because this code already works

Code: Select all

Query.SQL.Clear;
      Query.SQL.add('SELECT * FROM planamortyzacji;');
      Query.SQL.add('CREATE OR REPLACE FUNCTION dajmiesieczneodpisyzarok(');
      Query.SQL.add('    IN rok character varying,');
      Query.SQL.add('    IN id_srodektrwaly integer)');
      Query.SQL.add('  RETURNS TABLE(id_srodektrwaly numeric, m01 numeric, m02 numeric, m03 numeric, m04 numeric, m05 numeric, m06 numeric, m07 numeric, m08 numeric, m09 numeric, m10 numeric, m11 numeric, m12 numeric) AS');
      Query.SQL.add('$BODY$');
      Query.SQL.add('   SELECT *');
      Query.SQL.add('  FROM');
      Query.SQL.add('    crosstab( ''SELECT ''||$2||''::numeric AS id_srodektrwaly, generate_series::numeric,miesodpis::numeric');
      Query.SQL.add('  FROM generate_series(1,12)');
      Query.SQL.add('  LEFT JOIN (select miesiac::numeric,id_srodektrwaly::numeric, miesodpis::numeric from planamortyzacji where rok=''''''||$1||'''''' and id_srodektrwaly=''||$2||'')AS plan ON plan.miesiac =generate_series'')');
      Query.SQL.add('    AS final_result(id_srodektrwaly numeric,m01 numeric,m02 numeric,m03 numeric,m04 numeric,m05 numeric,m06 numeric,m07 numeric,m08 numeric,m09 numeric,m10 numeric,m11 numeric,m12 numeric)');
      Query.SQL.add('   ;');
      Query.SQL.add('$BODY$');
      Query.SQL.add('  LANGUAGE sql VOLATILE');
      Query.SQL.add('  COST 100');
      Query.SQL.add('  ROWS 1000;');
      Query.SQL.add('ALTER FUNCTION dajmiesieczneodpisyzarok(character varying, integer)');
      Query.SQL.add('  OWNER TO wij;');
      Query.ExecSQL;
You do not have the required permissions to view the files attached to this post.

marsupilami
Gold Boarder
Gold Boarder
Posts: 590
Joined: 17.01.2011, 14:17

Re: Query Error

Postby marsupilami » 10.08.2017, 19:18

Hello,

sooo - I checked your test project and it works for me. Which Version of Zeos did you use?

My configuration:
  • Zeos 7.2 from SVN
  • PostgreSQL Server 9.6
  • PostgreSQL Client 9.3
  • Delphi XE7
  • Windows 10

With best regards,

Jan

markus
Senior Boarder
Senior Boarder
Posts: 55
Joined: 17.10.2011, 12:43
Location: Piotrków Trybunalski, Poland

Re: Query Error

Postby markus » 12.08.2017, 18:02

konsul,

You use :: as cast operator - that works fine in psql or pgadmin, but
zeos uses : as param character by default - so after parsing Your query there are weird results.
You should either change param char in ZQuery object you use, or use cast() function from postgres.

Best regards,
Marek


Return to “ZeosLib 7.2 Betatest Forum”

Who is online

Users browsing this forum: No registered users and 1 guest