[firebird-support] Problem with CONTAINING and COLLATE
Hi All, I have a database with DEFAULT CHARACTER SET WIN1250 COLLATION PXW_HUN and a table in it: CREATE TABLE TABLE_1 (NEV VARCHAR(60) CHARACTER SET WIN1250 COLLATE PXW_HUN); Connect with ISQL: isql 1.2.3.4:MYDB -charset UTF8 -user myadmin -password mypasswd Execute this: SELECT * FROM TABLE_1 WHERE NAME CONTAINING 'x' COLLATE PXW_HUNDC; Got this error: Statement failed, SQLSTATE = 22021 Dynamic SQL Error -SQL error code = -204 -COLLATION PXW_HUNDC for CHARACTER SET UTF8 is not defined If connect with isql 1.2.3.4:MYDB -user myadmin -password mypasswd got this: Statement failed, SQLSTATE = 22021 Dynamic SQL Error -SQL error code = -204 -COLLATION PXW_HUNDC for CHARACTER SET NONE is not defined Why does the connection charset count? I tried with 3.0(.3.32850 snapshot). Gabor ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Problem with CONTAINING and COLLATE
07.12.2017 18:46, Gabor Boros mlngl...@bgss.hu [firebird-support] wrote: > Why does the connection charset count? Because you are sending queries in connection charset. And string literals in them are in connection charset. Because of that your 'x' cannot have COLLATE PXW_HUNDC. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Problem with CONTAINING and COLLATE
On 2017-12-07 19:06, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 07.12.2017 18:46, Gabor Boros mlngl...@bgss.hu [firebird-support] > wrote: >> Why does the connection charset count? > >Because you are sending queries in connection charset. And string > literals in them are > in connection charset. Because of that your 'x' cannot have COLLATE > PXW_HUNDC. I don't agree with this. The string literal is in the connection character set, but the value should be converted to the target character set (of the column) for the comparison, and that target character set does know PXW_HUNDC. Right now, it looks like instead Firebird only tries to apply the target collation, and not the target character set. Mark
Re: [firebird-support] Problem with CONTAINING and COLLATE
08.12.2017 11:56, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > I don't agree with this. The string literal is in the connection > character set, but the value should be converted to the target character > set (of the column) for the comparison No. Imagine connection charset to be UTF-8 and some characters in query that not exist in column charset. Should this situation throw "Cannot transliterate" error or return no results? -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Problem with CONTAINING and COLLATE
On 2017-12-08 12:01, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 08.12.2017 11:56, Mark Rotteveel m...@lawinegevaar.nl > [firebird-support] wrote: >> I don't agree with this. The string literal is in the connection >> character set, but the value should be converted to the target >> character >> set (of the column) for the comparison > >No. Imagine connection charset to be UTF-8 and some characters in > query that not exist > in column charset. Should this situation throw "Cannot transliterate" > error or return no > results? It should return no results. If it can't transliterate, it means that the character definitely does not exist in the target column, so the comparison is false. It should issue a warning in my opinion (and I believe the SQL standard agrees on that, but I don't have time to back that up ;) Mark
Re: [firebird-support] Problem with CONTAINING and COLLATE
08.12.2017 12:07, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > It should return no results. If it can't transliterate, it means that > the character definitely does not exist in the target column, so the > comparison is false. When character is transliterated into charset where it doesn't exist, it becomes '?'. I'm not sure that it should match '?' stored in the column. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Problem with CONTAINING and COLLATE - UTF8
Hi All,
After the original "Problem with CONTAINING and COLLATE" thread I
switched to UTF8 but not happy with it. The optional COLLATE after
CONTAINING have no effect, the database default COLLATION used. See the
below examples. Tried with 3.0.3.32854 snapshot and Linux 64bit (Ubuntu
14.04.1).
1.Database defaults: UTF8+UNICODE_CI
CREATE DATABASE 'CHARSET_TEST' PAGE_SIZE 4096 DEFAULT CHARACTER SET UTF8
COLLATION UNICODE_CI;
isql CHARSET_TEST -ch UTF8 -u SYSDBA
CREATE TABLE TABLE1 (NAME VARCHAR(60));
INSERT INTO TABLE1 (NAME) VALUES ('od');
INSERT INTO TABLE1 (NAME) VALUES ('őb');
INSERT INTO TABLE1 (NAME) VALUES ('Őc');
INSERT INTO TABLE1 (NAME) VALUES ('Oa');
SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'o';
NAME
od
Oa
SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'ő';
NAME
őb
Őc
SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'o' COLLATE UNICODE_CI_AI;
NAME
od
Oa
SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'ő' COLLATE
UNICODE_CI_AI;
NAME
őb
Őc
2.Database defaults: UTF8+UNICODE_CI_AI
CREATE DATABASE 'CHARSET_TEST' PAGE_SIZE 4096 DEFAULT CHARACTER SET UTF8
COLLATION UNICODE_CI_AI;
isql CHARSET_TEST -ch UTF8 -u SYSDBA
CREATE TABLE TABLE1 (NAME VARCHAR(60));
INSERT INTO TABLE1 (NAME) VALUES ('od');
INSERT INTO TABLE1 (NAME) VALUES ('őb');
INSERT INTO TABLE1 (NAME) VALUES ('Őc');
INSERT INTO TABLE1 (NAME) VALUES ('Oa');
SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'o';
NAME
od
őb
Őc
Oa
SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'ő';
NAME
od
őb
Őc
Oa
SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'o' COLLATE UNICODE_CI;
NAME
od
őb
Őc
Oa
SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'ő' COLLATE UNICODE_CI;
NAME
od
őb
Őc
Oa
Gabor
++
Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++
Yahoo Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-dig...@yahoogroups.com
firebird-support-fullfeatu...@yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Problem with CONTAINING and COLLATE - UTF8
>After the original "Problem with CONTAINING and COLLATE" thread I switched to UTF8 but not happy with it. >The optional COLLATE after CONTAINING have no effect, the database default COLLATION used. See the below >examples. Tried with 3.0.3.32854 snapshot and Linux 64bit (Ubuntu 14.04.1). Why would you want collate AFTER containing? I must admit I haven't tried your queries in Fb 3.0 (I'm still on 2.5), but I'd say the sensible way to specify what you want, would be SELECT * FROM TABLE1 WHERE NAME COLLATE UNICODE_CI_AI CONTAINING 'o'; //returns four rows on an old 2.5 installation and not SELECT * FROM TABLE1 WHERE NAME CONTAINING 'o' COLLATE UNICODE_CI_AI; //gives a 'data type unknown' error message I read your statement as "'o' COLLATE UNICODE_CI_AI", not the entire where clause being told to use that collation. Though, as I said, I don't have Fb 3.0 on this computer and don't know whether or not Fb agrees with me. HTH, Set ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Problem with CONTAINING and COLLATE - UTF8
2017. 12. 19. 21:13 keltezéssel, setysvar setys...@gmail.com [firebird-support] írta: > Why would you want collate AFTER containing? I must admit I haven't > tried your queries in Fb 3.0 (I'm still on 2.5), but I'd say the > sensible way to specify what you want, would be > > SELECT * FROM TABLE1 WHERE NAME COLLATE UNICODE_CI_AI CONTAINING 'o'; Thank you very much Set! With the correct syntax works as expected. Gabor ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
