[patch_done] Strings like E'\\' are incorrectly tokenized

[EgonHugeist]

olehs,

Oh... now I understood the whole idea you put into PreprepareSQL processing.
I don't really know what to say... This seems like a big challenge. There can be so many issues like the one I patched at rev.1651. There was a query like
Code: [Erweitern] [mehr anzeigen] [Verkleinern] [Alles auswählen]
select id as Код from orders
and TWordState couldn't detect non-latin alias. And now according to what you said, ttWord has to be checked for Encoding. And there may be surprises also in DDL statements and so on.

I think as a developer that uses Zeos, for me is better to manually control that encoding of SQL and in-parameters were the same.

Yes i know this is an nightmare. But i was forced to introduce this option because of to many bugreports...

I absolutely don't mind Smile. Couldn't do it myself because I'm not familiar with other databases' specific syntax.

I also think there is no need for extra token type in PostgreSQL. ttQuote (and maybe ttEscapedQuoted) is quite enough.

sounds ok for me. I'll add this type if i've the time. Thank you for your constribution, Oleg.

If you need any help I will be glad to help you.

That would be great, Oleg. I did complete this Statement now and commited the Patch. Rev. 1657.

I was forced to keep the emulated statement as default. I did run all test we have with that statement now.
Steps to reproduce my deep test:
comment "and ( Self.FExecCount > 2 )" line 838 in procedure TZPostgreSQLPreparedStatement.Prepare; ZDbcPostgreSqlStatment.pas out. This activates that all statements with parameters where handled with 'PREPARE','EXECUTE','DEALLOCATE'. I did introduce an execution counter because the JDBC API works like this too and if this statment will be called only once then there are 2 steps to much here.

Also you need to open ZDbcPostgreSql.pas and go to:
function TZPostgreSQLConnection.CreatePreparedStatement(
const SQL: string; Info: TStrings): IZPreparedStatement;

Here comment everything out after lin 575 except Result := TZPostgreSQLPreparedStatement.Create(GetPlainDriver, Self, SQL, Info)

Then this statment is 100% the default one. All tests did run successfully except the tests: TestStandartConformingStrings because PostgreSQL is not able to determine the type of your introduced parameter. I wonder about again because all other Drivers do return here a string but PostgreSQL raises an exception.

To set this statement as default without modification use TZQuery.Options := [doPreferPrepared, doPreferPreparedResolver].

Then you can now execute all Parameter queries in Batch-mode which, i hope, should speed up the whole handling.

Also was i afraid about one thing: i did read in several JDBC examples that we do NOT need to escape the Strings. But i didn't get them running successfully without escaping.

This is also now the firs 100% AnsiString statement with String-handling which means for the FullUnicodeIDE's a lot of speed increase because of minior UTF8Encode/Decode the Strings. And it is 100% unicode save. (I already had all of them ready but the team decided to keep the old behavior)

I would be happy if you run some tests.. Or fix issues you can see. I don't feel tarnished here (:

Michael

[olehs]

EgonHugeist,

I fixed StandardConformingStrings test. I also perfomed tests on some IDE's.
Delphi7, DelphiXE2/Win32 - seems to be OK, Lazarus 1.1/Win32 - have some errors.

Also was i afraid about one thing: i did read in several JDBC examples that we do NOT need to escape the Strings. But i didn't get them running successfully without escaping.

I guess you read about libpq prepared statements (PQprepare/PQexecPrepared). The indeed need no quoting/escaping.

[EgonHugeist]

olehs,

The Lazarus issues are known: http://zeos.firmos.at/viewtopic.php?t=3534 ludob did submit a lot of hotfixes for FPC2.7 but i don't know if they are accepted. The failing tests are issues on the FPC DataSets...

But i like that you test with other IDE's too!!

I fixed StandardConformingStrings test

There was no need for me, Oleg. I didn't wanted to say you made a mistake, no! That test did show me only that using this Statement by default will cause trouble for others too.

(PQprepare/PQexecPrepared)

Now i'm affraid! That kind of C++API call i wanted to have! I did google the prepared statments for postgre sevaral times but did not found anything about direct API calls. We have such an API for all PlainDrivers except for PostgreSQL and SQLite(i don't think this will happen soon:) ). Hmpf now i made it ready with a deprecated design ))):

Do you know about version dependencies? I can only find a comment that these calls are only supported since protocol version 3 and not in 2. What is protocol 3? IF wa support the API direct call then i think it will speed up the Prepared Statments again.

Michael

[olehs]

EgonHugeist,

That test did show me only that using this Statement by default will cause trouble for others too.

Yes, that's true, but this problem will dissapear if we use API calls (PQprepare/PQexecPrepared).

About protocols. Protocol 3 was introduced in Postgre 7.4, but support for prepared statemets was introduced in libpq that came with PG 8.0
So libpq from 8.0+ should work fine with servers 7.4+ (I guess so...)

Here are PG docs
http://www.postgresql.org/docs/7.3/static/protocol.html
http://www.postgresql.org/docs/7.4/static/protocol.html
http://www.postgresql.org/docs/8.0/stat ... l#AEN23224

[EgonHugeist]

olehs,

Damn Oleg this sounds goooood! Point 1 my latest work can be used for Libpg <8.0. The Next point by reading the docs i like is: we really do not need such casts! And i'm hopefully i can add this statment too.

Is there somewhere a better documentation or some running examples available?

Michael

[olehs]

EgonHugeist,

well typecasts are not that bad thing. According to PG forums it's better to NOT specify parameter types in PQexecPrepared. Explicit parameter type can lead to bad plan.

The weak spot of
the index system is that *cross type* comparisons were unindexable until
recently. So if you wrote something like
WHERE id = 42
this would be indexable if id was int4 (like the constant) but not if it
was int8 or numeric. That problem actually goes away if you use quoted
literals or parameters:
WHERE id = '42'
WHERE id = $1
In either case, the literal or parameter will be resolved as being the
same type as "id", and so you don't risk creating an unindexable
comparison. So at least for examples like this, it's *better* not to
specify a type for the parameter.

As of 8.0, btree indexes can handle cross-type comparisons, at least for
most of the common cases, so this isn't as big a deal as it once was.
But it's still true that you should usually avoid specifying parameter
types unless you have to. "Have to" means you get an error like this:

regression=# select - '42';
ERROR: operator is not unique: - "unknown"
HINT: Could not choose a best candidate operator. You may need to add explicit type casts.
regression=#

Here the parser is punting because it hasn't any good way to guess which
numeric type to impute to the literal.

(http://grokbase.com/t/postgresql/pgsql- ... -pqprepare)

And I also can't find any examples of using these functions. Found something in Freepascal sources, but it's commeted and not used in there.

[EgonHugeist]

olehs,

well typecasts are not that bad thing. According to PG forums it's better to NOT specify parameter types in PQexecPrepared. Explicit parameter type can lead to bad plan

A good start to know about..

And I also can't find any examples of using these functions. Found something in Freepascal sources, but it's commeted and not used in there.

Really annoying. I didn't found anything too. But i've an idea which i didn't checked yet. I know the JDBC do support this code sequence. Now i don't know if code is available...