Hi folks!

I have some betrieve files created by Microfocus Cobol (trough
FILETYPE"6" directive), and accessed trough DDF files created with
"CREATE TABLE" statements. My (BIG) problem concern to the INDEXES.
With BUTIL -STAT, I can see that every index on the data file is
created eith type=String.

So, the DDF index definition will never match. For example, for the
table:

FD DC0030.
01 DC0030-REG.
2 DC0030-CODE PIC 9(3).
2 DC0030-NAME PIC X(50).

SELECT DC0030 ASSIGN TO WS0030-CAM-GERAL
ORGANIZATION IS INDEXED
ACCESS MODE IS DYNAMIC
LOCK MODE IS AUTOMATIC
FILE STATUS IS WS-8005-STATUS
RECORD KEY IS DC0030-CODE

If your Btrieve indices are strings, yet the data is Numeric, then the
Btrieve file structure is incorrect. You may need to contact
Microfocus to determine how to get the file definition to be created
properly at the Btrieve layer.

You *MAY* be able to make the repair yourself, but I do not know if
this will impact the MF operating environment or not. Luckily, a
simple PIC 9(3) field has a range of "000" through "999", so treating
it as a string works. However, if SQL looks at it as a signed value,
everything gets messed up. So, you MUST fix the problem to get good
queries from SQL.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 07/2006 ***


alexandretalbert (AT) gmail (DOT) com wrote:

Hi Bill,

One detail: I only have problems when I try to query a table with a
SEGMENTED KEY. For example:

FD DC0099.
01 DC0030-REG.
2 DC0099-ORDER.
3 DC0099-DOC-CODE PIC 9(1).
3 DC0099-ORDER-CODE PIC 9(6).
2 DC0099-ORDER-DESCR PIC X(30).

SELECT DC0099 ASSIGN TO "DC0099"
ORGANIZATION IS INDEXED
ACCESS MODE IS DYNAMIC
LOCK MODE IS AUTOMATIC
FILE STATUS IS WS-8005-STATUS
RECORD KEY IS DC0099-ORDER


The FD / SELECT above, cause Microfocus to create a btrieve data file
with one KEY (1) which contains only ONE STRING SEGMENT ( 7 caracters
length (1+6) ).

But when I run the CREATE STATEMENT for the table, the INDEX will be
formed by 2 SEGMENTS (NUMERIC LENGTH 1 + NUMERIC LENGTH 6). Then my
queries don´t work properly.

select * from dc0099 where doc_code = 1 (doesn´t work)

select * from dc0099 where convert(doc_code,sql_numeric) = 1 (works,
but using no index)


Thanks again!


Bill Bach wrote:

Exactly - this is the case where it will NOT work! Let's see why:

In SQL, you can specify just about anything, but at the lower MKDE
level (the engine behind the scenes), you have a limited number of
commands. In this case, the engine has to pick the right Btrieve
operation to make the query work. Since you specified only ONE segment
(and not both segments) in the query, the database cannot use the
GetEqual command to find your data. (GetEqual would provide the entire
7-byte key value.)

What's the engine to do, then? It decides to use the GetGreaterOrEqual
Btrieve operation (op=9). This is valid if you provide the first part
of the key value, and then the SMALLEST POSSIBLE VALUES for the
remainder of the key segments. IN this case, the first field gets a
value of "1" (ASCII or NUMERIC is the same, of course), and the second
field must be the SMALLEST possible value.

When COBOL needs to find this value, it knows that the key is a string.
What is the smallest value for a string key , as defined by Btrieve?
This one is easy -- the smallest value is al NULL (0x00) bytes. Using
this combination will allow the GetGreaterOrEqual to correctly find the
records where the first byte of the key is "1" and the remainder is
ANYTHING greater than 0x00 values.

However, life in SQL is a little more complicated -- you have defined
the field as a NUMERIC field. According to the manual:

NUMERIC values are stored as ASCII strings, right justified with
leading zeros. Each digit occupies one byte internally. The rightmost
byte of the number includes an embedded sign with an EBCDIC value.
Table A-7 <sqlref-6-5.html> indicates how the rightmost digit is
represented when it contains an embedded sign for positive and negative
numbers.
Table A-7 Rightmost Digit with Embedded Sign
Digit Positive Negative
1 A J
2 B K
3 C L
4 D M
5 E N
6 F O
7 G P
8 H Q
9 I R
0 { }
For positive numbers, the rightmost digit can be represented by 1
through 0 instead of A through {. The MicroKernel processes positive
numbers represented either way. The NUMERIC type is commonly used in
COBOL applications.

So, what does this mean? The SMALLEST POSSIBLE VALUE for a NUMERIC(6)
value is actually -999999. When this value is represented as a NUMERIC
field, it is PHYSICALLY stored as "99999R". As you can see, when the
SQL engine passes a Btrieve GetGreaterOrEqual to the database, it
passes the fierst field as "1" the way you want, but then it must match
the SMALLEST POSSIBLE VALUE for the second field. In the end, the key
it passes is "199999R".

When you interpret the data as a string field (as Btrieve is doing),
the value "199999R" is already greater than all possible values, and
the query returns NO results.

There are two ways to fix this problem, as stated previously:
1) The best solution is to change the Btrieve data type from STRING to
NUMERIC. This fixes the problem and allows Btrieve to look at the
199999R value as a very low value, instead of a high one. This is easy
to do by running the SQL CREATE TABLE statement *AFTER* running the
Btrieve Create from the COBOL side, and THEN loading your data.
However, this may have an adverse impact on the COBOL side.
2) You can also change your SQL to treating these values as strings
only. This will still make Btrieve==SQL and allow the query to work.
However, you will need to convert your numeric data from strings back
to numeric values (which may not handle the sign flag correctly) every
time you want to use it. A very ugly solution, but it doesn't require
that you mess with the COBOL/Btrieve side.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 07/2006 ***


alexandretalbert (AT) gmail (DOT) com wrote:

BIll, THANK YOU very mach for your answer / time!

Things are clear to me know. I´ll try OPTION 1....


Thnks again!
Alexandre

Bill Bach wrote: