Re: INDEXES: BTRIEVE vs EXTFH (cobol)

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@xxxxxxxxxxxxxxxxxxxx
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 07/2006 ***


alexandretalbert@xxxxxxxxx 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:
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@xxxxxxxxxxxxxxxxxxxx
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 07/2006 ***


alexandretalbert@xxxxxxxxx wrote:

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
.

I have the following CREATE TABLE:

CREATE TABLE DC0030 USING 'DC0030.MKD' (CODE NUMERIC(3,0), NAME
CHAR(50),PRIMARY KEY(CODE))

Then the PRIMARY INDEX CODE is NUMERIC on DDF but STRING on the
data file....For this reason, some queries doesn´t work.....

Any suggestion??

Thanks!!!!

.

Relevant Pages

  • Re: INDEXES: BTRIEVE vs EXTFH (cobol)
    ... Bill Bach wrote: ... In SQL, you can specify just about anything, but at the lower MKDE ... the engine has to pick the right Btrieve ... When COBOL needs to find this value, it knows that the key is a string. ...
    (comp.databases.btrieve)
  • Re: a cheep interface to database
    ... I just don't see SQL as ... >> SQL COBOL ... These indicator variables have a couple of uses, ... you have to "cast" the string into the appropriate data type. ...
    (comp.lang.cobol)
  • Re: COBOL KEY SEGMENTS
    ... Can anyone tell me why COBOL creates every key segment (for indexed ... When the programa creates the file (btrieve), ... utility) that its index has ONE STRING SEGMENT instead NUMERIC ...
    (comp.lang.cobol)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... posted in response to my last post was some SQL, but this is not the SQL that ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)