[patch_done] Postgre rev 2415 Query params bahaviour changed

[markus]

Hi,

In my apps i use queries with lots of params, that are provided for ZQuery component in runtime
for example:
ZQuery1->SQL->Text = tSQL;
My SQL could look like this:
select case when :p1 is not null then now() - :p1 else now() end as p_date,
case when :p2 is not null then 10 * :p2 else 1 end as p_num

User may provide data for only few params. The ones that user left empty i am setting empty in code:
ZQuery1->Params->Items->Value = Variant(NULL).AsType(varNull);

it worked until rev2415 good, as i found in ZDBCPostgresqlStatement
in TZPostgreSQLEmulatedPreparedStatement.PrepareAnsiSQLParam empty params were replaced by 'NULL'.

In CAPI prepared statements it bahaves differently and now i get error message "Could not determine data type of parameter $2".

Is there any way i can get this old behaviour with CAPI statements?

Best regards,
Marek

[EgonHugeist]

markus,

hmpf we did start discussing about...

The problem is the lazy query you want to send. We can send null-values. But we crash on prepareing the Statement. The same happens to Firebird, Oracle, ADO where we have no emulations for the statements..

current adivice until we find a better solution:

Add ZEOS_TEST_ONLY as define to you project. Than after open the TZConnection add the line

TZConnection.DbcConnection.SetSetTestMode(2);

and you'll have your old behavior back.

[EgonHugeist]

markus,

don't understand me as rude accordingly the "lazy" words, Marek.

The background discussion didn't end yet. Would it be sufficient if you use a Cast(:p1 as varchar(10)) f.e.? Than postgre is able to determine the result or in parameter.

Well we did support that previously and now we've a behavior change. The CAPI stamtent is twice faster against the emulated once.

[markus]

Hi Michael,

don't worry i didn't took Your words as rude:)

I used TestMode with Your advice and for now i'm back wit old behaviour.

I figured out from svn comments that CAPI is way faster, that's why i asked about way to handle empty parameters "old" way in CAPI.

for old porjects i'll stick with emulated statement - too many sqls to correct - i'm too lazy;)
But soon i'm starting new one and here i'll keep casting of params in mind.

Thanks a lot for Your advice.

Best regards,
Marek

[EgonHugeist]

markus,

hope you understand what exactly is going wrong:

select :p1 as param1; will fail allways. No "good" server will be able to prepare such a statement. That's the issue you're running in after the behavior change.

select Cast(:p1 as varchar(2)) as param1 will be executed successfully.

Well we're thinking about different possible ways to handle this issue. Something like a emergency proc would be nice. I'll give you feedback...

[EgonHugeist]

markus,

i made a simple improvement:

Patch done R2527 \testing 7.1 (SVN)
See http://sourceforge.net/p/zeoslib/code-0/2527/

Add 'handle_indeterminate_datatype=true' to the TZConnection/TZDataSet.Properties.

It would be nice if you can test it, Marek.

[markus]

Michael,

Thank You for this patch.
I've tired it on my app:
Adding 'handle_indeterminate_datatype=true' to TZConnection doesn't seem to do anything - i still got error about unknown param types.
But adding it to TZQuery does the trick - i've used ZEOS with Postgresql CAPI and no errors about unknown params.


I've run along another problem with my "lazy queries".
Sometimes i use such query for inserting data in simple tables:
"insert into (...) values (...);
select currval('table_id_seq') as id;"

In emulated statements it worked ok, in CAPI i got error "cannot insert multiple commands into prepared statement".
But this i can live with - i'll simply use functions for all insert statements:)

Best regards,
Marek

[EgonHugeist]

markus,

TZConnection doesn't seem to do anything

Well that's true and can be fixed. (;

I've run along another problem with my "lazy queries".

((:

"insert into (...) values (...);
select currval('table_id_seq') as id;"

uff. Grumble. We've no test for this situation! Some more improvements? Nope..

Seems to be the best option to keep the old behavior instead of using some more workarounds... ): OR trying to fix that too ); So i think best practice should be to keep the CAPI and RealPrepared Statements optional like we're doing it for MySQL. On the other hand we'll get some more bugreports later on. You might (in the forum) be the first who was running into this issue.

Going to restart the background discussion (;

[EgonHugeist]

Marek,

discussion results: We'll keep the CAPI stmts by default. I've omited the switch. Which means in full effect:

Each prepareable statment will be prepared. Each non prepareble stmt switches back to the old behavior. We log failing prepare (with the TZSQLMonitor you can see that).

Your multiple stmts we won't handle. We've a ExecuteDirect possibility for this case. As you wrote you can live with that. We too. A stmt is one stmt not two or more of them.

So omit (i'm sorry) the handle_indeterminate_datatype=true option, it's out of use inbetween.

[markus]

Michael,

Thanks for the information - this means my old apps will work without much work:)

For new ones i'll make sure to create queries that will fit for CAPI statement convention.

I expected that handle_indeterminate_datatype option is quick patch for testing purposes, and sooner or later it would be changed somehow, so i used it in only one of my apps:)

Best regards,
Marek

[fhaut]

Here another sample where Postgresql+CAPI are not working well.

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
var QAux : TZQuery;
    ZConnection1: TZConnection;
begin

  ZConnection1 := TZConnection.Create(Self);

  with ZConnection1 do
  begin
    Name := 'ZConnection1';
    HostName := 'change_to_postgresql_ip';
    Port := 5432;
    Database := 'some_posgresql_database';
    User := 'postgres';
    Password := '123456';
    Protocol := 'postgresql-9';
    connect;
  end;

   QAux := TZQuery.Create(Self);
   try
      QAux.Connection := ZConnection1;

      QAux.SQL.Text := 'drop table if exists test;';
      QAux.ExecSQL;

      // Create table
      QAux.SQL.Text := 'create table test ( id int4, name varchar(50) );';
      QAux.ExecSQL;

      // Populate data
      QAux.SQL.Text := 'select * from test';
      QAux.Open;
      QAux.Append;
      QAux.FieldByName('id').asInteger := 1;
      QAux.FieldByName('name').asString := 'nam1';
      QAux.Post;
      QAux.Append;
      QAux.FieldByName('id').asInteger := 2;
      QAux.FieldByName('name').asString := 'nam2';
      QAux.Post;
      QAux.Append;
      QAux.FieldByName('id').asInteger := 3;
      QAux.FieldByName('name').asString := 'nam3';
      QAux.Post;
      QAux.Append;
      QAux.FieldByName('id').Clear;
      QAux.FieldByName('name').asString := 'namnul';
      QAux.Post;

      // Produce problem
      QAux.SQL.Text := 'select * from test where ( id = :P0 or :P0 is null) ';
      QAux.ParamByName('P0').asInteger := 2;
      QAux.Open;
      ShowMessage( '2=' + IntToStr(QAux.RecordCount) );
      QAux.Close;

   finally
     QAux.Free;
   end;


end;