Hi Tim,

I have the following query:

SELECT LoanNo FROM Loans ORDER BY LoanNo

The query returns the following result:

LoanNo
-----------
BOV -13
BOV -17
BOV 103

The problem is that the result is not in the proper order based on ANSI collation.  If you do a AnsiCompareStr or WideCompareStr on these value, you will find that BOV 103 is considered less than both BOV -17 and BOV -13.  Because the query is not properly ordered, it cause the report builder that we use to not work properly.

I assume that the sort order is (or should be) based on ANSI collation and not ASCII collation.  Please correct me if I'm wrong.

Is this an issue or am I doing something wrong or made a bad assumption on the sort order collation.

I am using DBISAM 4.28 Build 6.

Any assistance you can provide is greatly appreciated.

Thanks,
Scott

Scott,

<< The problem is that the result is not in the proper order based on ANSI
collation.  If you do a AnsiCompareStr or WideCompareStr on these value, you
will find that BOV 103 is considered less than both BOV -17 and BOV -13.
Because the query is not properly ordered, it cause the report builder that
we use to not work properly. >>

The ANSI Standard locale in DBISAM is not the same thing as the English (US)
locale.  The ANSI Standard locale is a binary comparison based upon the
Windows ANSI character set (Windows 1252).

If you want the sorting to be done the same as in Windows, then you need to
use the English (US) locale with the LOCALE or LOCALE CODE clauses:

SELECT LoanNo FROM Loans ORDER BY LoanNo
LOCALE CODE 1033

http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=d&version=7&topic=SELECT_Statement

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

I ran the following two queries against the Loans table and though a few Loans in the result set where ordered different both queries returned the same ordering for:

BOV -13
BOV -17
BOV 102
BOV 103

SELECT LoanNo FROM Loans ORDER BY LoanNo

SELECT LoanNo FROM Loans ORDER BY LoanNo
LOCALE CODE 1033

Setting the Locale Code to 1033 did not seem to change the ordering of those Loans but WideCompareStr and AnsiCompareStr say that BOV 103 and BOV 102 are less than BOV -13 and BOV -17.

Is LOCALE CODE 1033 the same as ANSI in Windows?

Thanks,
Scott

"Tim Young [Elevate Software]" wrote:

Scott,

<< The problem is that the result is not in the proper order based on ANSI
collation.  If you do a AnsiCompareStr or WideCompareStr on these value, you
will find that BOV 103 is considered less than both BOV -17 and BOV -13.
Because the query is not properly ordered, it cause the report builder that
we use to not work properly. >>

The ANSI Standard locale in DBISAM is not the same thing as the English (US)
locale.  The ANSI Standard locale is a binary comparison based upon the
Windows ANSI character set (Windows 1252).

If you want the sorting to be done the same as in Windows, then you need to
use the English (US) locale with the LOCALE or LOCALE CODE clauses:

SELECT LoanNo FROM Loans ORDER BY LoanNo
LOCALE CODE 1033

http://www.elevatesoft.com/manual?action=viewtopic&id=dbisam4&product=d&version=7&topic=SELECT_Statement

--
Tim Young
Elevate Software
www.elevatesoft.com

Scott,

I think its the minus symbol. It comes right before the numeric symbols.
so  -17 should come before 103.

BOV -13
BOV -17
BOV 103

Scott,

<< Setting the Locale Code to 1033 did not seem to change the ordering of
those Loans but WideCompareStr and AnsiCompareStr say that BOV 103 and BOV
102 are less than BOV -13 and BOV -17. >>

We use the SORT_STRINGSORT attribute for the CompareStringA call that we use
when you use a non-ANSI Standard locale like English (US).  That may cause
differing results from the AnsiCompareStr call.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Just to be clear.  DBISAM doesn't have a true ANSI locale that would base the collation on ANSI using an ANSI comparision but rather the ANSI Standard locale uses a binary comparision.  And the English (US) locale is a non-ANSI locale.  Based on this there is no way for me to use ANSI collation in the report builder but rather will need to switch to a Binary collation in the report builder and then it should work properly since the ANSI Standard locale uses a binary comparison for sorting.

Does ElevateDB have a true ANSI collation or is it similiar to DBISAM?

Thanks,
Scott

Hi Abdon,

When comparing strings using ANSI comparision methods (WideCompareStr and AnsiCompareStr), BOV 103 is less than BOV -13 and BOV -17 and therefore is displayed first in the list.  Depending on the comparison method used will depend on where BOV 103 is located.  Using CompareStr, which is a non-ANSI compare, BOV 103 is greater than BOV -13 and BOV -17.  We've been setting the collation in our reporting engine to ANSI assuming that ANSI Standard in DBISAM was the same thing, but have been running into a few wierd situations in some reports.  As Tim has pointed out the ANSI Standard locale in DBISAM does not use an ANSI comparison but rather a Binary comparison method.  So, hopefully switching the reporting engine to use a Binary collation will resolve our issue.

Thanks,
Scott

Abdon Ibarra wrote:

Scott,

I think its the minus symbol. It comes right before the numeric symbols.
so  -17 should come before 103.

BOV -13
BOV -17
BOV 103

Scott,

<< Just to be clear.  DBISAM doesn't have a true ANSI locale that would base
the collation on ANSI using an ANSI comparision but rather the ANSI Standard
locale uses a binary comparision.  And the English (US) locale is a non-ANSI
locale. >>

No, DBISAM uses the *Windows API* CompareStringA call on any non-ANSI
Standard locales, so you are gettting an ANSI comparison based upon the
Windows 1252 (ANSI) character set.   The difference is due to the
SORT_STRINGSORT flag being used, which affects how punctuation characters
such as '-' are sorted.

http://msdn.microsoft.com/en-us/library/ms647476(VS.85).aspx

--
Tim Young
Elevate Software
www.elevatesoft.com

Scott,

<< When comparing strings using ANSI comparision methods (WideCompareStr and
AnsiCompareStr), BOV 103 is less than BOV -13 and BOV -17 and therefore is
displayed first in the list.  Depending on the comparison method used will
depend on where BOV 103 is located.  Using CompareStr, which is a non-ANSI
compare, >>

You're confusing CompareStr, a native Delphi string comparison routine, with
CompareStringA, which is a Windows API call and is what DBISAM uses.  See my
other reply for more information.

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks for all the good information (and yes, I'm probably a bit confused... and confusing).  Just to (re) confirm, the ANSI Standard locale in DBISAM uses a binary comparison, correct?  I'm just trying to get ReportBuilder to work properly with DBISAM.  In ReportBuilder when ANSI collation is selected it uses WideCompareStr or AnsiCompareStr for comparison and setting DBISAM to ANSI Standard and ReportBuilder to ANSI collation does not seem to work properly in a few instances.  So, if the ANSI Standard locale in DBISAM uses a binary comparison, then I'm thinking that selecting Binary collation versus ANSI collation in ReportBuilder will fix the problem if I leave the locale of the DBISAM tables at ANSI Standard.

Scott

"Tim Young [Elevate Software]" wrote:

Scott,

<< When comparing strings using ANSI comparision methods (WideCompareStr and
AnsiCompareStr), BOV 103 is less than BOV -13 and BOV -17 and therefore is
displayed first in the list.  Depending on the comparison method used will
depend on where BOV 103 is located.  Using CompareStr, which is a non-ANSI
compare, >>

You're confusing CompareStr, a native Delphi string comparison routine, with
CompareStringA, which is a Windows API call and is what DBISAM uses.  See my
other reply for more information.

--
Tim Young
Elevate Software
www.elevatesoft.com