Page 1 of 1

Query Error

Posted: 01.08.2017, 07:32
by konsul41
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.

Re: Query Error

Posted: 03.08.2017, 10:36
by marsupilami
Hello,

could you provide a sample database and a sample project?

With best regards,

Jan

Re: Query Error

Posted: 03.08.2017, 11:58
by konsul41
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

Re: Query Error

Posted: 03.08.2017, 15:04
by marsupilami
Usually you should be able to attach a zip file to your post? I will check into this...

Re: Query Error

Posted: 04.08.2017, 10:08
by konsul41
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;

Re: Query Error

Posted: 10.08.2017, 19:18
by marsupilami
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

Re: Query Error

Posted: 12.08.2017, 18:02
by markus
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