Full Unicode/Ansi-Support in /testing branch

[olehs]

EgonHugeist,

all right, the main difference is that I'm using PG 8.4 )))

and differences in config (that might cause troubles), mine
"client_encoding";"UTF8"
"standard_conforming_strings";"off"

yours
"bytea_output";"escape"
"client_encoding";"UNICODE"
"standard_conforming_strings";"on"

The most interesting here is bytea_output. It was introduced in 9.0 and defaults to 'hex', but yours doesn't (did you change it manually?).

But what actually changes behavior is standard_conforming_strings. When I change my to 'on' - I get better result, but not perfect (1029 bytes instead of 1024). difference is in ' chars. In second stream they are doubled.

Michael, can you please run tests with SCS=off ?

[EgonHugeist]

olehs,

The most interesting here is bytea_output. It was introduced in 9.0 and defaults to 'hex', but yours doesn't (did you change it manually?).

Nope i didn't change anything there. My result of EncodeBytea looks like:

'\\xffd8ffe000104a46494600010100000100010000ffdb0043000a07070807060a0808080b0a0a0b0e18100e0d0d0e1d15161118231f2524221f2221262....'

Hmm can't be a

'x'ffd8ffe000104a46494600010100000100010000ffdb0043000a07070807060a0808080b0a0a0b0e18100e0d0d0e1d15161118231f2524221f2221262...''

equal valid? This is the SQL standart syntax.. I allready wrote a function in ZDbcUtils i think.

1 Fehlschlag aufgetreten:
1) TestRealPreparedStatement: ETestFailure
bei
"postgresql-9_Unicode_Preprepare/postgresql-9: Different stream size. Expe
cted: 1024. Actual: 2050.Binary Stream"

run the tests with standart_confirming_strings.. So the issue is confirmed, Oleg.

Hmpf isn't that annoying? Must i add again a test runner with such options.

What do you think? Can you make a patch?

[olehs]

EgonHugeist,

that's really annoying.
I actually don't know what to patch. The problem is not in tests. Even with SCS=on my tests fail

[EgonHugeist]

olehs,

i was thinking on adding the formats, lengths, types parameters?. Or find out what postgre does expect here. Writing to Postgre directly?

For the first i propose we'll change the default PreparedStatment to my last TZPostgreSQLPreparedStatement. Or we check again for PG9+ and standart_confirming_strings=ON. Again a bug isn't that i want to have here. And i'll add again a Postgre protocoll with that option to see such issues..

But Oleg, i'm sure this Statement is turbo fast. No string escaping, no string parsing, tiny, and fast. What could be better in this case?

[olehs]

EgonHugeist,

I think the problem is in PQescapeByteaConn. It's used for escaping strings that are going to be inserted DIRECTLY into SQL query.
When we are using it for PQexecPrepared - we get side-effects of PQescapeByteaConn depending on server settings. Here are some examples.

For data like #0#39#0 the results of PQescapeByteaConn will be:
SCS='on';bytea_output='hex' -> \x002700
SCS='off';bytea_output='hex' -> \\x002700
SCS='on';bytea_output='escape' -> \000''\000
SCS='off';bytea_output='escape' -> \\000\'\\000

but PQexecPrepared expects
bytea_output='hex' -> \x002700 (this is what you get by default)
bytea_output='escape' -> \000'\000

Michael, do you have any idea how we can pass PQexecPrepared what it expects in text format, or you can pass these values as binaries?

[EgonHugeist]

olehs,

I think the problem is in PQescapeByteaConn. It's used for escaping strings that are going to be inserted DIRECTLY into SQL query.
When we are using it for PQexecPrepared - we get side-effects of PQescapeByteaConn depending on server settings. Here are some examples.

I think this is right oleg. For me personally it is only the question what does postgre expect exactly. If we know this and we are sure it works with PQexecPrepared for all servers then we can easiely add a function to ZDbcPostgreUtils.

I'm not sure if postgre allways want to have a '\\x...Lowercase(BinToHex)' but this can be testet...
Actually i testet my GetSQLHexAnsiString function and failed again.

So what exactly does Postgre expect for an bytea field?

[EgonHugeist]

olehs,

got it running now with scs=off/on for PG9. Can you check your PG8.4?

i made my own function.. R1710

[olehs]

EgonHugeist,

So what exactly does Postgre expect for an bytea field?

as I wrote, it expects
\000'\000 if (PG < 9.0) or (PG >=9.0 and bytea_output='escape')
\x002700 if (PG >=9.0 and bytea_output='hex'(it's default))

Can you check your PG8.4?

No, it doesn't work, because 8.4 supports only escaped syntax, not hex

[olehs]

EgonHugeist,

I read some PG forums and now understand what is going on.
PQescapeByteaConn not only encodes bytes using current bytea_output setting, but it also escapes(quotes) the string using current standard_conforming_strings setting.
But we just need the first part (encoding), not escaping, because we are not including it directly in a query.

As soon as PG doesn't have PQescapeByteaConn without escaping, or there is no PQunascapeString - we can't safely pass bytea as text.

So I think best solution would be passing it as binary.

[EgonHugeist]

olehs,

agree. I must admit i thought we can do it with strings. Anyway BinToHex needs time too. But now i see no posibility do complete it without PQdescribePrepared, or am i wrong?

We need the right parameters types/formats/lengths. Then we've got an API like firebird. But this has also his advantages, Oleg. transforming floating point values means allways loosing some precision. Which we can avoid in this kind eventually.

For the first i'll deactivate the statement until we have the time to complete it. I'll concentrate me back to my own project and the MySQL callable statment for the next days..

If you want then fix these issues.

[olehs]

EgonHugeist,
we don't have to use binary format for all parameters.
Here is the plan.
1. Examine PQdescribePrepared.
2. For parameters of unknown type set apropriate type in paramTypes, 0 - for the rest
3. For Bytea fields set paramFormat to '1' and rest to '0'
4. Pass Bytea 'as is'

[EgonHugeist]

olehs,

i think testing it would be better. Actually i've disabled this Statement to avoid reports on the forum/bugtracker. So all tests should behave like before.

Also did i thought a while about PQdescribePrepared. To solve the binary issue it seems to be the solution. But then we need also the paramLengths parameter. On the other hand Postgre reads only #0 terminated ansistrings which are trunked on binary data. I think you mean the original byte-sequence with 'as is'. Do i understand it right? If paramFormat[x] = 0 then postgre does not check the type in the oid-array?

The way you want go seems for me to work and fix this issue.

But i don't think we can solve the 'select $1' issue. The reason is PQdescribePrepared can descibe only prepared statments, right? But exactly the preparation of 'select $1' fails so PQdescribePrepared helps neither. The only way around is a cast of that value.
Do you agree?

[olehs]

EgonHugeist,

Here is a sample statement for bytea, but I can be wrong.
PQprepare
command='INSERT INTO people (p_name, p_picture) VALUES($1, $2)'
nParams=2
paramTypes=0

PQexecPrepared
nParams=2
paramValues='text string\0picturedatawith\0inside'
paramLength=[0,22]
paramFormats=[0,1]

But i don't think we can solve the 'select $1' issue. The reason is PQdescribePrepared can descibe only prepared statments, right? But exactly the preparation of 'select $1' fails so PQdescribePrepared helps neither. The only way around is a cast of that value.

Do you agree?

Oh, I didn't know it fails already on Prepare...
In this case I agree, we have to cast.

[EgonHugeist]

olehs,

i'm not sure about paramTypes vs. paramFormats but i've testet the prepare issue and my impression was right. We got an exception on PQprepare. So nothing is prepared and all other improvements we talked about will fail too.

So let's say activating the Real-prepared statments isn't mad for such statements/dummies... Or do you see a generic way to know if such a statment is preparable or not? Can tell us postgres something about previously? If the statment is not able to prepare then we avoid the prepare stuff and send the statement with PQexecParams (Btw why was it not done before?)

Can you patch this remaining issue, Oleg?

[EgonHugeist]

miab3,

i added now the callable statement for MySQL. Can you start playing with the TZStoredProcedure component?

I know some issues. Actually i have no clue how to get INOUT params running. MySQL has a terrible syntax here. I can't Execute the statment and fetch the result with one call, or do i something wrong? I execute a 'CALL' and then i do 'SELECT' the Params afterwards. Is there a better way to do this?

Also are some more tricky tests interesting to find out if everything is ok. You 'ABTEST' does work well...