|
Home > Archive > Programming with dBASE > March 2006 > Strange order !!
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Angelo Bocchini 2006-03-21, 3:25 am |
| Do someone know why if I create with query designer a query,
--Database:ANALOCALE
SELECT OGGETTO
FROM SPEDI Spedi
ORDER BY UPPER(OGGETTO)
and I start from the control center , the resullt is :
PR
pr
QU
se
te
( for me this is quite ok )
If I write on the command line
(after open the database from the control center)
SELECT OGGETTO FROM SPEDI Spedi ORDER BY UPPER(OGGETTO)
I have the error : Engine error : not valid use of UPPER(OGGETTO)
if I write:
SELECT OGGETTO FROM SPEDI ORDER BY OGGETTO
I have
PR
QU
pr
se
te
(for me this is idiot )
The database is Firebird, the charset win1252 collate win1252
the ldriver DBWINWEO
--
Thank in advance,
ciao
Angelo Bocchini
GENESI
angelo@genesigroup.it
| |
| Bruce Beacham 2006-03-21, 3:25 am |
| Angelo Bocchini wrote:
> Do someone know why if I create with query designer a query,
> If I write on the command line
> (after open the database from the control center)
>
> SELECT OGGETTO FROM SPEDI Spedi ORDER BY UPPER(OGGETTO)
> I have the error : Engine error : not valid use of UPPER(OGGETTO)
Perhaps try:
SELECT OGGETTO, upper(OGETTO) as uOGETTO FROM SPEDI ORDER BY uOGGETTO
> if I write:
> SELECT OGGETTO FROM SPEDI ORDER BY OGGETTO
>
> I have
>
> PR
> QU
> pr
> se
> te
>
> (for me this is idiot )
All upper case characters collate before any lower case characters.
Bruce Beacham
| |
| *Lysander* 2006-03-21, 3:25 am |
| Angelo Bocchini schrieb:
> SELECT OGGETTO FROM SPEDI Spedi ORDER BY UPPER(OGGETTO)
> I have the error : Engine error : not valid use of UPPER(OGGETTO)
The command line (dbase command-window) is using another
database-object than the query designer.
if in command-window you do the following:
d = new database()
d.databasename = < bde_alias_for_FB_dat
abase>
d.active = .T.
q = new query()
q.database = d
q.sql = "select OGGETTO from SPEDI spedi ORDER BY upper(OGGETTO)"
q.active = .T.
then it _should_ work!
ciao,
André
| |
| Angelo Bocchini 2006-03-21, 3:25 am |
| No, Bruce, It does not work.
There is the error:
Database engine error: The table is read only
ciao
--
Angelo Bocchini
GENESI
angelo@genesigroup.it
"Bruce Beacham" <bbeacham@beacham.no-spam.co.uk> ha scritto nel messaggio
news:8wRbWx$SGHA.2016@news-server...
> Angelo Bocchini wrote:
>
>
> Perhaps try:
> SELECT OGGETTO, upper(OGETTO) as uOGETTO FROM SPEDI ORDER BY uOGGETTO
>
>
>
> All upper case characters collate before any lower case characters.
>
>
> Bruce Beacham
| |
| Angelo Bocchini 2006-03-21, 3:25 am |
| No, André, it does not work.
there is the error message:
Database engine error: not valid use of the key word: upper(OGGETTO)
ciao
--
Angelo Bocchini
GENESI
angelo@genesigroup.it
"*Lysander*" <nobody@nowhere.com> ha scritto nel messaggio
news:NDQkex$SGHA.1740@news-server...
> Angelo Bocchini schrieb:
>
>
> The command line (dbase command-window) is using another database-object
> than the query designer.
>
> if in command-window you do the following:
>
> d = new database()
> d.databasename = < bde_alias_for_FB_dat
abase>
> d.active = .T.
>
> q = new query()
> q.database = d
> q.sql = "select OGGETTO from SPEDI spedi ORDER BY upper(OGGETTO)"
> q.active = .T.
>
> then it _should_ work!
>
> ciao,
> André
>
>
| |
| *Lysander* 2006-03-21, 3:25 am |
| Angelo Bocchini schrieb:
> No, André, it does not work.
>
> there is the error message:
>
> Database engine error: not valid use of the key word: upper(OGGETTO)
sorry, forgot to mention that when using the command-window, you
MUST set the property requestlive to .F. manually when you are using
an "order by".
Here is an example that just now worked for me; please replace the
field- and table-name with your own identifiers.
release all
d = new database()
d.databasename = "Fire_bIZ/Current"
d.active = .T.
q = new query()
q.database = d
q.sql = "select P3010_014Z from D3010001 order by upper(P3010_014Z)"
q.requestlive = .F.
q.active = .T.
ciao,
André
| |
| Bruce Beacham 2006-03-21, 3:25 am |
| Angelo Bocchini wrote:
> No, Bruce, It does not work.
>
> There is the error:
>
> Database engine error: The table is read only
Well, it _does_ work but then you come across another problem further
along: you want to edit the rowset, but it is read-only. <g>
What editing process are you trying to implement?
Bruce
| |
| Angelo Bocchini 2006-03-21, 3:25 am |
| Bruce,
It's only a grid in a mask datalinked to a table ordered on a field.
The table is opened with a simple:
sql = 'SELECT * FROM "SPEDI" ORDER BY OGGETTO '
and the order of the field oggetto, for me, is simply idiot.
I don't need to have the grid editable, but also if I change the
requestlive as André suggest, and I add upper, nothing change.
ciao
Angelo Bocchini
GENESI
angelo@genesigroup.it
"Bruce Beacham" <bbeacham@beacham.no-spam.co.uk> ha scritto nel messaggio
news:F7w3dRCTGHA.2320@news-server...
> Angelo Bocchini wrote:
>
> Well, it _does_ work but then you come across another problem further
> along: you want to edit the rowset, but it is read-only. <g>
>
> What editing process are you trying to implement?
>
>
> Bruce
| |
| Roland Wingerter 2006-03-21, 3:25 am |
| Hi André,
*Lysander* wrote
> release all
> d = new database()
> d.databasename = "Fire_bIZ/Current"
> d.active = .T.
> q = new query()
> q.database = d
> q.sql = "select P3010_014Z from D3010001 order by upper(P3010_014Z)"
> q.requestlive = .F.
> q.active = .T.
------
I have the same settings as Angelo (Firebird charset win1252, collate
win1252, dbase ldriver DBWINWE0).
I tried your query with a dictionary table (ISpell US-English dictionary).
The result looks quite good, but words starting with accented vowels (like
é) appear at the end of the table. Any ideas how this can be solved?
Roland
| |
| Angelo Bocchini 2006-03-21, 3:25 am |
| Andre',
with requestlive false it work.
Thank you very much, also if I don't agree the
way it order .
I would agree:
a
A
b
B
c
C
and so on.
Thank you very much
ciao
--
Angelo Bocchini
GENESI
angelo@genesigroup.it
"*Lysander*" <nobody@nowhere.com> ha scritto nel messaggio
news:Ckuw2iBTGHA.1740@news-server...
> Angelo Bocchini schrieb:
>
> sorry, forgot to mention that when using the command-window, you MUST set
> the property requestlive to .F. manually when you are using an "order by".
>
> Here is an example that just now worked for me; please replace the field-
> and table-name with your own identifiers.
>
>
> release all
> d = new database()
> d.databasename = "Fire_bIZ/Current"
> d.active = .T.
> q = new query()
> q.database = d
> q.sql = "select P3010_014Z from D3010001 order by upper(P3010_014Z)"
> q.requestlive = .F.
> q.active = .T.
>
> ciao,
> André
>
| |
| *Lysander* 2006-03-21, 3:25 am |
| Angelo Bocchini schrieb:
> I would agree:
> a
> A
> b
> B
> c
> C
> and so on.
But which order _do_ you get??
I am sure such problems can be solved. Somehow.
ciao,
André
| |
| *Lysander* 2006-03-21, 3:25 am |
| Roland Wingerter schrieb:
> I have the same settings as Angelo (Firebird charset win1252, collate
> win1252, dbase ldriver DBWINWE0).
>
> I tried your query with a dictionary table (ISpell US-English dictionary).
> The result looks quite good, but words starting with accented vowels (like
> é) appear at the end of the table. Any ideas how this can be solved?
language drivers is my weak side.
I am using the ISO8859_1 because:
a.) ISO-drivers are the smallest (1Byte per Character) and thus they
are the fastest in all regards.
b.) it works perfectly for me in all situations.
If correct sorting according to any norm is vital for you, you
should be playing a bit with different collations on the column.
Every column could be kept in the record 2 or 3 times with different
collations. You can default their values to be loaded from the one
"origin" column.
That's fast, but will cost some disk-space, of course.
If disk-space is vital, but you have a fast server and/or only
rarely need a special collation, you can build an output query
(read-only!) using a selectable stored procedure.
This will generate any given collation order at runtime; extra
disk-space only is needed for the source-code of the stored procedure.
ciao,
André
| |
| Simone Bartoccioni 2006-03-21, 3:25 am |
| It's not a strange order....
FireBird is case sensitive
Probably when you run the query from an sql file, this one is processed by
dBase and not by the RDBMS; upper function is not sql standard.
Ciao
Simone
"Angelo Bocchini" <genesi@genesigroup.it> ha scritto nel messaggio
news:L02OZj$SGHA.560@news-server...
> Do someone know why if I create with query designer a query,
>
> --Database:ANALOCALE
> SELECT OGGETTO
> FROM SPEDI Spedi
> ORDER BY UPPER(OGGETTO)
>
> and I start from the control center , the resullt is :
>
> PR
> pr
> QU
> se
> te
>
> ( for me this is quite ok )
>
> If I write on the command line
> (after open the database from the control center)
>
> SELECT OGGETTO FROM SPEDI Spedi ORDER BY UPPER(OGGETTO)
> I have the error : Engine error : not valid use of UPPER(OGGETTO)
>
> if I write:
> SELECT OGGETTO FROM SPEDI ORDER BY OGGETTO
>
> I have
>
> PR
> QU
> pr
> se
> te
>
> (for me this is idiot )
>
> The database is Firebird, the charset win1252 collate win1252
>
> the ldriver DBWINWEO
> --
> Thank in advance,
> ciao
> Angelo Bocchini
> GENESI
> angelo@genesigroup.it
>
| |
| Roland Wingerter 2006-03-21, 3:25 am |
| Angelo Bocchini wrote
> Andre',
> with requestlive false it work.
> Thank you very much, also if I don't agree the
> way it order .
>
> I would agree:
> a
> A b
> B
> c
> C
> and so on.
-----
I reread the thread you started in dbase.sql-servers, July 7, 2005, subject
"Order & ldriver & charset".
Ivar's advice was to try charset ISO8859_1, collation IT_IT. Works as
expected for simple characters but not for special characters like é, É etc.
Roland
| |
| Roland Wingerter 2006-03-21, 3:25 am |
| *Lysander* wrote
>
> If correct sorting according to any norm is vital for you, you should be
> playing a bit with different collations on the column.
>
> Every column could be kept in the record 2 or 3 times with different
> collations. You can default their values to be loaded from the one
> "origin" column.
-----
I went back and reread a thread started on July 7, 2005 in
dbase.sql-servers.
Case insensitive orders can be achieved using charset ISO8859_1 and, for
example, collation ISO8859_1. However it looks like accent insensitive
sorting is not possible.
The tools offered at www.ibcollate.com does not support firebird 1.5, the
last update was in 2003.
Looks like collation order is a weak point in Firebird. :-(
Roland
| |
| *Lysander* 2006-03-21, 3:25 am |
| Roland Wingerter schrieb:
> *Lysander* wrote
> -----
> I went back and reread a thread started on July 7, 2005 in
> dbase.sql-servers.
>
> Case insensitive orders can be achieved using charset ISO8859_1 and, for
> example, collation ISO8859_1. However it looks like accent insensitive
> sorting is not possible.
>
> The tools offered at www.ibcollate.com does not support firebird 1.5, the
> last update was in 2003.
>
> Looks like collation order is a weak point in Firebird. :-(
possibly. Lucky are those that can come by without too strict
collation-rules, meaning, for whom ISO-collation is enough.
But I wouldn't give up too early, though.
Firebird still carries all the old Borland stuff for sorting and collating.
So, with which driver in dBase is the collation as you would wish?
It should be possible also to achieve this in Firebird.
Remember that you _have_ do do a Phoenix (cycle of backup/restore)
before changes in the collation of columns and tables will become
effective on _already saved_ data.
ciao,
André
| |
| Ivar B. Jessen 2006-03-21, 3:25 am |
| On Mon, 20 Mar 2006 21:13:02 +0100, in dbase.programming,
Subject: Re: Strange order !!,
Message-ID: <TWJIBvFTGHA.1488@news-server>,
"Roland Wingerter" <ich@hier.de> wrote:
>I reread the thread you started in dbase.sql-servers, July 7, 2005, subject
>"Order & ldriver & charset".
>
>Ivar's advice was to try charset ISO8859_1, collation IT_IT. Works as
>expected for simple characters but not for special characters like é, É etc.
Roland,
In the Firebird table employee the charset is WIN1252. I have defined a domain
in the employee table named firstname for the field FIRST_NAME where Charset =
ISO8859_1 and Collate = DA_DA. I have also changed some of the names in the
FIRST_NAME column to égal/ègal/egal/aalborg/Aalborg.
With this sql,
q.sql = 'select first_name from employee order by first_name'
the first part of the FIRST_NAME column is:
Ashok
Bill
Carol
Chris
Claudia
Dana
Egal
egal
Égal
égal
ègal
Jacques
and the two last records are,
Aalborg
aalborg
In other words upper case letter sort before lower case letter, accented letters
end up in the correct place and special Danish sort order works :-)
Ivar B. Jessen
| |
| Roland Wingerter 2006-03-21, 7:24 am |
| Roland W. wrote
>
> Case insensitive orders can be achieved using charset ISO8859_1 and, for
> example, collation ISO8859_1. However it looks like accent insensitive
> sorting is not possible.
------
Went back and tried again with a new table. Now the result looks good. The
database uses charset ISO8859_1. In the column, I defined charset ISO8859_1
and collation DE_DE.
For this test, I used a table filled with 225 different printable
characters. The sort order looks like this:
a
A
ª
á
Á
à
À
â
Â
å
Å
ä
Ä
ã
Ã
æ
Æ
b
B
c
C
ç
Ç
etc.
Thanks, André and Ivar for your support.
Roland
| |
| Roland Wingerter 2006-03-21, 7:24 am |
| Ivar B. Jessen wrote
>
> In other words upper case letter sort before lower case letter, accented
> letters
> end up in the correct place and special Danish sort order works :-)
------
Thanks Ivar,
now it works for me, too. See my post a few minutes ago, where I replied to
myself. ;-)
Thanks again
Roland
| |
| *Lysander* 2006-03-21, 7:24 am |
| Roland Wingerter schrieb:
> Went back and tried again with a new table. Now the result looks good.
It would most likely also work with the old table, after doing a
backup/restore. Such a pity, that Firebird itself is not warning the
administrator, that such changes will only be effective on new
objects OR after a backup/restore.
We did a one-hour unit in the latest workshop in Mannheim about
exactly that topic.
The lesson was to convert dBase-III (!) tables (LD DOS 437) with
correct special characters into a Firebird database.
| |
| Roland Wingerter 2006-03-21, 7:24 am |
| *Lysander* wrote
>
> It would most likely also work with the old table, after doing a
> backup/restore.
-----
Tried that, but got an error message saying I was not allowed to do that...
OTOH I had a backup of that database, and I was allowed to restore it. Oh
well, never mind.
Thanks
Roland
| |
| Ivar B. Jessen 2006-03-21, 11:24 am |
| On Tue, 21 Mar 2006 11:04:43 +0100, in dbase.programming,
Subject: Re: Strange order !!,
Message-ID: <zjorz$MTGHA.1740@news-server>,
"Roland Wingerter" <ich@hier.de> wrote:
>------
>Thanks Ivar,
>
>now it works for me, too. See my post a few minutes ago, where I replied to
>myself. ;-)
>
>Thanks again
You are welcome :-)
Please see my question in the thread on the behaviour of the character Ó.
Ivar B. Jessen
| |
| Ivar B. Jessen 2006-03-21, 11:24 am |
| On Tue, 21 Mar 2006 11:00:35 +0100, in dbase.programming,
Subject: Re: Strange order !!,
Message-ID: <ycpJg9MTGHA.1740@news-server>,
"Roland Wingerter" <ich@hier.de> wrote:
>------
>Went back and tried again with a new table. Now the result looks good. The
>database uses charset ISO8859_1. In the column, I defined charset ISO8859_1
>and collation DE_DE.
>
>For this test, I used a table filled with 225 different printable
>characters. The sort order looks like this:
>
>a
>A
>ª
& #91;snip]
>ç
>Ç
>etc.
>
>Thanks, André and Ivar for your support.
>
Roland,
Did you try it with the character Ó corresponding to chr(211) in 1252 Windows
Latin 1, or in words, Latin Capital Letter O with Acute, and did you notice
anything unusual?
The reason why I ask is that this character plays havoc with a Firebird table.
To see it open the employee example table from the navigator by double clicking
on the name. In the browse change the name in column FIRST_NAME, record one from
"Robert" to "ÓRobert" and close the browse. Then from the command pane type
this:
use :FDBemployee:employe
e
browse
and you will see that the data in the first 22 records have been changed to
blank or 0.00 or to a BC date :-(
Can you confirm this observation ( make a backup of the database before you try
it out ). I get the same result when changing the table using updateset and
reading the result using sql.select.
Ivar B. Jessen
| |
| *Lysander* 2006-03-21, 11:24 am |
| Ivar B. Jessen schrieb:
> and you will see that the data in the first 22 records have been changed to
> blank or 0.00 or to a BC date :-(
well, this sounds like the real horror.
I would also like to test it as soon as I have an opportunity.
ciao,
André
| |
| Roland Wingerter 2006-03-22, 3:23 am |
| Ivar B. Jessen wrote
> Roland,
>
> Did you try it with the character Ó corresponding to chr(211) in 1252
> Windows
> Latin 1, or in words, Latin Capital Letter O with Acute, and did you
> notice
> anything unusual?
-------
I programmatically filled the Firebird table with characters from chr(32) to
chr(256). No visible problems with the resulting table. I can post the code
if you like.
> The reason why I ask is that this character plays havoc with a Firebird
> table.
>
> To see it open the employee example table from the navigator by double
> clicking
> on the name. In the browse change the name in column FIRST_NAME, record
> one from
> "Robert" to "ÓRobert" and close the browse. Then from the command pane
> type
> this:
>
> use :FDBemployee:employe
e
> browse
>
> and you will see that the data in the first 22 records have been changed
> to
> blank or 0.00 or to a BC date :-(
-------
I cannot confirm this observation. Nothing unusual here, all records are
displayed correctly.
Roland
| |
| Ivar B. Jessen 2006-03-22, 7:25 am |
| On Wed, 22 Mar 2006 09:11:30 +0100, in dbase.programming,
Subject: Re: Strange order !!,
Message-ID: <wnt$OlYTGHA.1152@news-server>,
"Roland Wingerter" <ich@hier.de> wrote:
>I programmatically filled the Firebird table with characters from chr(32) to
>chr(256). No visible problems with the resulting table. I can post the code
>if you like.
>
>-------
>I cannot confirm this observation. Nothing unusual here, all records are
>displayed correctly.
Thanks for testing this. I tried it on the employee table where I had a domain
on FIRST_NAME setting charset to ISO8859_1 and collate to DA_DA. With that
domain I am able to use the following sql from dbase,
q.sql = 'select first_name from employee order by first_name collate DA_DA'
It gives the correct Danish sorting and puts upper case letter before lower case
letter. Changing the collate to DE_DE it sorts lower case letter before upper
case letter and puts aa up between the a's which I suppose is the correct German
sorting order.
To confim this I reinstated several times the original employee table and it
must have been during one of those occasions I succeded in damaging the table.
The problem is the one that Lysander has pointed out earlier. The employee
database is born without having a characterset defined and the characterset for
each table column is therefore NONE. Any attempts to insert characters ( outside
the range chr(32) to chr(127) ?? ) results in errors.
Opening the original employee table in IBExpert I attempted to insert the
character Ó and got an error about arithmetic overflow ... cannot transliterate
between different charactersets. Nevertheless the new character was accepted and
all records disappeared ;-)
The confusing thing is that it is possible in dbase to open the employee table
and insert an unacceptable charater without getting any transliteration error
message from Firebird, but you still find that records are missing from the
table.
The conclusion of all this is that I forgot to keep track of the settings for
the table and the error I reported could be expected :-(
The lesson learned from this is: Always set the characterset when create a
Firebird database. Never use a Firebird database in which a characterset is not
defined.
I found the quotes below from the IBExpert help file very helpful.
Ivar B. Jessen
***********
IBExpert Help file:
The default character set is the character set defined when creating the
database, and applicable for all areas of the database unless overridden by the
domain or field definition. It controls not only the available characters that
can be stored and displayed, but also the collation order. If not specified, the
parameter defaults to NONE, i.e. values are stored exactly as typed.
[snip]
If a character set is defined as the default character set when creating the
database, it is not necessary to define this again for individual columns.
[snip ]
The ASCII character set is not synonymous with a non-defined character set. If
no character set is defined, Firebird/InterBase chooses the character set NONE.
The character set NONE does not translate characters. Umlauts and accents are
not sorted correctly. When the ASCII character set is specified, all characters
are translated into the ASCII equivalents from the character set under which
they were input.
The character set WIN 1252 is recommended for European countries, as it includes
all characters and collation orders of the most important European languages.
( Hmm, ISO8859_1 is probably better )
Generally this default character set cannot be altered at a later date (only
using the command line tools IBExtract and IBExpert Script). Alternate character
sets can however be defined for individual domains and tables, which override
the default character set.
********************
**
| |
| Roland Wingerter 2006-03-22, 9:23 am |
| Ivar B. Jessen wrote
>
> Thanks for testing this. I tried it on the employee table where I had a
> domain
> on FIRST_NAME setting charset to ISO8859_1 and collate to DA_DA. With that
> domain I am able to use the following sql from dbase,
>
> q.sql = 'select first_name from employee order by first_name collate
> DA_DA'
>
> It gives the correct Danish sorting and puts upper case letter before
> lower case
> letter. Changing the collate to DE_DE it sorts lower case letter before
> upper
> case letter and puts aa up between the a's which I suppose is the correct
> German
> sorting order.
-------
Yep.
> To confim this I reinstated several times the original employee table and
> it
> must have been during one of those occasions I succeded in damaging the
> table.
>
> The problem is the one that Lysander has pointed out earlier. The employee
> database is born without having a characterset defined and the
> characterset for
> each table column is therefore NONE. Any attempts to insert characters (
> outside
> the range chr(32) to chr(127) ?? ) results in errors.
-------
FWIW, in the Database Registration Info of my EMPLOYEE.FDB the field CHARSET
is empty (I don't know if that's different from "NONE" ;-)). Font Characters
Set is set to "ANSI_CHARSET". I don't remember if I changed any settings.
> The lesson learned from this is: Always set the characterset when create a
> Firebird database. Never use a Firebird database in which a characterset
> is not
> defined.
>
> I found the quotes below from the IBExpert help file very helpful.
------
I agree, thank you for the reminder.
Roland
| |
| Ivar B. Jessen 2006-03-22, 9:23 am |
| On Wed, 22 Mar 2006 14:47:06 +0100, in dbase.programming,
Subject: Re: Strange order !!,
Message-ID: <Wjc2vgbTGHA.592@news-server>,
"Roland Wingerter" <ich@hier.de> wrote:
>FWIW, in the Database Registration Info of my EMPLOYEE.FDB the field CHARSET
>is empty (I don't know if that's different from "NONE" ;-)). Font Characters
>Set is set to "ANSI_CHARSET". I don't remember if I changed any settings.
When I opened the employee database in the personal version of IBExpert the
property CHARSET was displayed as Win1252, in the C't version of IBExpert the
setting was ISO8859_1. What you see is what you set when registering the
database. I do not know what the effect of this setting is.
However if you set for example the charset to be ISO8859_1 when a database is
created and then in IBExpert open any table created in said database you will
see that fields of type char or varchar have the properties CHARSET and COLLATE
pre-set to ISO8859_1.
Ivar B. Jessen
|
|
|
|
|