Full Unicode/Ansi-Support in /testing branch
Moderators: gto, EgonHugeist, 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 ?
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 ?
Oleg
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
olehs,
Hmpf isn't that annoying? Must i add again a test runner with such options.
What do you think? Can you make a patch?
Nope i didn't change anything there. My result of EncodeBytea looks like: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?).
Hmm can't be a'\\xffd8ffe000104a46494600010100000100010000ffdb0043000a07070807060a0808080b0a0a0b0e18100e0d0d0e1d15161118231f2524221f2221262....'
equal valid? This is the SQL standart syntax.. I allready wrote a function in ZDbcUtils i think.'x'ffd8ffe000104a46494600010100000100010000ffdb0043000a07070807060a0808080b0a0a0b0e18100e0d0d0e1d15161118231f2524221f2221262...''
run the tests with standart_confirming_strings.. So the issue is confirmed, Oleg.1 Fehlschlag aufgetreten:
1) TestRealPreparedStatement: ETestFailure
bei
"postgresql-9_Unicode_Preprepare/postgresql-9: Different stream size. Expe
cted: 1024. Actual: 2050.Binary Stream"
Hmpf isn't that annoying? Must i add again a test runner with such options.
What do you think? Can you make a patch?
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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?
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?
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
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?
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?
Oleg
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
olehs,
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?
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 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'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?
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
olehs,
got it running now with scs=off/on for PG9. Can you check your PG8.4?
i made my own function.. R1710
got it running now with scs=off/on for PG9. Can you check your PG8.4?
i made my own function.. R1710
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
EgonHugeist,
\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))
as I wrote, it expectsSo what exactly does Postgre expect for an bytea field?
\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))
No, it doesn't work, because 8.4 supports only escaped syntax, not hexCan you check your PG8.4?
Oleg
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.
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.
Oleg
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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.
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.
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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?
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?
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
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]
In this case I agree, we have to cast.
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]
Oh, I didn't know it fails already on Prepare...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?
In this case I agree, we have to cast.
Oleg
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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?
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?
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
- EgonHugeist
- Zeos Project Manager
- Posts: 1936
- Joined: 31.03.2011, 22:38
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...
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...
Best regards, Michael
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/
You want to help? http://zeoslib.sourceforge.net/viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/