Get_Next_Extended and LESS_THAN_OR_EQUAL and Dates

Cardinal24 · #1 (**permalink**) 08-04-06, 22:17

Basically I have a problem with get-next-extended.

If I say "Give me all the records in the db that have a date after mm/dd/yyyy", it works fine. If I say, "show me all the records that have a date BEFORE mm/dd/yyyy", it never shows me any records.

Now, admittedly, your ability to help may be frustrated by the fact that I am using a layer on top of raw Btrieve but perhaps you can think up a question or a test to make the solution appear. Right now it LOOKS LIKE Btrieve has a problem doing a less-than compare on dates although it does greater-than compares fine. I DO NOT BELIEVE that. However it behaves AS IF that were the cause.

What you should notice is there are 2 nearly identical “frames” (FIRST_FRAME and SECOND_FRAME).
FIRST_FRAME always works. SECOND_FRAME always fails.

At A, I am grabbing the date I'm comparing to from a control.
At B, I change it to the format shown at X below:
At C, I make it another filter clause. (DSQL_DATE = 3, DSQL_GORE = 5, DSQL_LORE = 6)

What is strange (and consistent) is that frame FIRST_FRAME always works perfectly.and SECOND_FRAME never works.

So, for example,
if I set the FIRST_FRAME date to January 1, 2005, and leave SECOND_FRAME empty, I correctly get everything at and after January 1, 2005. (> 1-1-2005)
If I put January 1, 2005 in FIRST_FRAME and February 2, 2005 in SECOND_FRAME, I get absolutely nothing despite the fact that there are MANY matching records throughout January 2005. (> 1-1-2005 AND < 2-1-2005)
If I put March 30th, 2005 in SECOND_FRAME and leave FIRST_FRAME empty, I still get zero hits. (<3-30-2005)
---------------------
So, one way of saying it, is: No matter what I put in SECOND_FRAME, none of my records are less than that value – as if second frame is always 00-00-0000 no matter what I put in it.

FIRST_FRAME:
if (IsDlgButtonChecked(hLWnd, DISPLAY_TD_FILTERING))
{
A SendMessage(GetDlgItem(hLWnd,DISPLAY_TD_START), HEM_GETDATA, 0, (LPARAM)(LPVOID) &DosDate);
if (DosDate.year != 0)
{
B DosDateToBnDate(&DosDate, &tempdate);
C MakeTerm(&ext.lpSQLFilter->Term[ext.lpSQLFilter->NumTerms++], DSQL_DATE,
sizeof(Bn_Date), offsetof(struct TRANS_REC, tdate),
DSQL_GORE, DSQL_ANDTERM, (LPVOID)&tempdate) ;
}

SECOND_FRAME:
SendMessage(GetDlgItem(hLWnd,DISPLAY_TD_END), HEM_GETDATA, 0, (LPARAM)(LPVOID) &DosDate);
if (DosDate.year != 0)
{
DosDateToBnDate(&DosDate, &tempdate);
MakeTerm(&ext.lpSQLFilter->Term[ext.lpSQLFilter->NumTerms++], DSQL_DATE,
sizeof(Bn_Date), offsetof(struct TRANS_REC, tdate),
DSQL_LORE, DSQL_ANDTERM, (LPVOID)&tempdate) ;
}
}

X typedef struct tagBn_Date
{
unsigned char Day;
unsigned char Month;
unsigned int Year;
} Bn_Date,
far *LPBNDATE;

void MakeTerm(LPSQLTERM lpTerm, char Type, UINT Length, UINT uOffset,
char Relate, char AndOr, LPVOID Value)
{
lpTerm->Type = Type ;
lpTerm->Length = Length ;
lpTerm->Offset = uOffset ;
lpTerm->Relate = Relate ;
lpTerm->AndOr = AndOr ;
lpTerm->Value = Value ;
return ;
}

mirtheil · #2 (**permalink**) 08-04-06, 23:05

As posted elsewhere:
A few questions to start:
1. What version of Btrieve/PSQL are you using?
2. What's showing in the MKDE trace for the opcode 36?
3. Do you have an index on this date field?
4. What key are you using for the GNE?

It's not the same but I modified the BTRSAMP to do a Less than or Equal and it worked for me. It was a string field and not a date but it should function the same. It might be worth while to modify the BTRSAMP to use a Date field and try directly (without your layer).
x

mirtheil · #3 (**permalink**) 08-04-06, 23:10

Here's the code I used:

Code:

/*  
 Copyright 1982-2003 Pervasive Software Inc. All Rights Reserved
modified by Mirtheil
 BTRSAMP.C
*/
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <btrapi.h>
#include <btrconst.h>

/*
  Constants
*/
#ifdef BTI_LINUX
#define FILE1_NAME "/usr/local/psql/data/samples/sample.btr"
#else
#ifdef BTI_NLM
#define FILE1_NAME "sys:\\pvsw\\samples\\sample.btr"
#else
#define FILE1_NAME "c:\\pvsw\\samples\\sample.btr"
#endif
#endif

#ifdef BTI_LINUX
#define FILE2_NAME "/usr/local/psql/data/samples/sample2.btr"
#else
#ifdef BTI_NLM
#define FILE2_NAME "sys:\\pvsw\\samples\\sample2.btr"
#else
#define FILE2_NAME "c:\\pvsw\\samples\\sample2.btr"
#endif
#endif

#define EXIT_WITH_ERROR     1
#define TRUE                1
#define FALSE               0
#define VERSION_OFFSET      0
#define REVISION_OFFSET     2
#define PLATFORM_ID_OFFSET  4
#define MY_THREAD_ID        50

/* Don't pad our structures */
#if defined(__BORLANDC__)
  #pragma option -a-
#else
  #if defined(_MSC_VER) || defined(__WATCOMC__) || defined(BTI_LINUX)
    #pragma pack(1)
  #endif
#endif

/***************************************************************************
  Type definitions for Client ID and version Structures
****************************************************************************/
typedef struct
{
  BTI_CHAR networkAndNode[12];
  BTI_CHAR applicationID[2];
  BTI_WORD threadID;
} CLIENT_ID;

typedef struct
{
  BTI_SINT  Version;
  BTI_SINT  Revision;
  BTI_CHAR  MKDEId;
} VERSION_STRUCT;

/***************************************************************************
  Definition of record from 'sample.btr'
****************************************************************************/
typedef struct
{
  BTI_LONG  ID;
  BTI_CHAR  FirstName[16];
  BTI_CHAR  LastName[26];
  BTI_CHAR  Street[31];
  BTI_CHAR  City[31];
  BTI_CHAR  State[3];
  BTI_CHAR  Zip[11];
  BTI_CHAR  Country[21];
  BTI_CHAR  Phone[14];
} PERSON_STRUCT;

/***************************************************************************
  Type definitions for Stat/Create structure
****************************************************************************/
typedef struct
{
  BTI_SINT recLength;
  BTI_SINT pageSize;
  BTI_SINT indexCount;
  BTI_CHAR reserved[4];
  BTI_SINT flags;
  BTI_BYTE dupPointers;
  BTI_BYTE notUsed;
  BTI_SINT allocations;
} FILE_SPECS;

typedef struct
{
  BTI_SINT position;
  BTI_SINT length;
  BTI_SINT flags;
  BTI_CHAR reserved[4];
  BTI_CHAR type;
  BTI_CHAR null;
  BTI_CHAR notUsed[2];
  BTI_BYTE manualKeyNumber;
  BTI_BYTE acsNumber;
} KEY_SPECS;

typedef struct
{
  FILE_SPECS fileSpecs;
  KEY_SPECS  keySpecs[5];
} FILE_CREATE_BUF;

/***************************************************************************
  Structure type definitions for Get Next Extended operation
****************************************************************************/
typedef struct
{
  BTI_SINT    descriptionLen;
  BTI_CHAR    currencyConst[2];
  BTI_SINT    rejectCount;
  BTI_SINT    numberTerms;
} GNE_HEADER;

typedef struct
{
  BTI_CHAR    fieldType;
  BTI_SINT    fieldLen;
  BTI_SINT    fieldOffset;
  BTI_CHAR    comparisonCode;
  BTI_CHAR    connector;
  BTI_CHAR value[3];
} TERM_HEADER;

typedef struct
{
  BTI_SINT    maxRecsToRetrieve;
  BTI_SINT    noFieldsToRetrieve;
} RETRIEVAL_HEADER;

typedef struct
{
  BTI_SINT    fieldLen;
  BTI_SINT    fieldOffset;
} FIELD_RETRIEVAL_HEADER;

typedef struct
{
  GNE_HEADER              gneHeader;
  TERM_HEADER             term1;
  TERM_HEADER             term2;
  RETRIEVAL_HEADER        retrieval;
  FIELD_RETRIEVAL_HEADER  recordRet;
} PRE_GNE_BUFFER;

typedef struct
{
  BTI_SINT      recLen;
  BTI_LONG      recPos;
  PERSON_STRUCT personRecord;
} RETURNED_REC;

typedef struct
{
  BTI_SINT      numReturned;
  RETURNED_REC  recs[20];
} POST_GNE_BUFFER;

typedef union
{
  PRE_GNE_BUFFER  preBuf;
  POST_GNE_BUFFER postBuf;
} GNE_BUFFER, BTI_FAR* GNE_BUFFER_PTR;

/* restore structure packing */
#if defined(__BORLANDC__)
  #pragma option -a.
#else
  #if defined(_MSC_VER) || defined(__WATCOMC__) || defined(BTI_LINUX)
    #pragma pack()
  #endif
#endif

/***************************************************************************
  Main
****************************************************************************/
int main(void)
{
  /* Btrieve function parameters */
  BTI_BYTE posBlock1[128];
  BTI_BYTE posBlock2[128];
  BTI_BYTE dataBuf[255];
  BTI_WORD dataLen;
  BTI_BYTE keyBuf1[255];
  BTI_BYTE keyBuf2[255];
  BTI_WORD keyNum = 0;

  BTI_BYTE btrieveLoaded = FALSE;
  BTI_LONG personID;
  BTI_BYTE file1Open = FALSE;
  BTI_BYTE file2Open = FALSE;
  BTI_SINT status;
  BTI_SINT getStatus = -1;
  BTI_SINT i;
  BTI_SINT posCtr;

  CLIENT_ID       clientID;
  VERSION_STRUCT  versionBuffer[3];
  FILE_CREATE_BUF fileCreateBuf;
  GNE_BUFFER_PTR  gneBuffer;
  PERSON_STRUCT   personRecord;

  printf("**************** Btrieve C/C++ Interface Demo ****************\n\n");

  /* set up the Client ID */
  memset(clientID.networkAndNode, 0, sizeof(clientID.networkAndNode));
  memcpy(clientID.applicationID, "MT", 2);  /* must be greater than "AA" */
  clientID.threadID = MY_THREAD_ID;

  memset(versionBuffer, 0, sizeof(versionBuffer));
  dataLen = sizeof(versionBuffer);

  status = BTRVID(
              B_VERSION,
              posBlock1,
              &versionBuffer,
              &dataLen,
              keyBuf1,
              keyNum,
              (BTI_BUFFER_PTR)&clientID);

  if (status == B_NO_ERROR)
  {
    printf("Btrieve Versions returned are: \n");
    for (i = 0; i < 3; i++) {
      if (versionBuffer[i].Version != 0)
      {
        printf("   %d.%d %c\n", versionBuffer[i].Version,
                versionBuffer[i].Revision,
                versionBuffer[i].MKDEId);
      }
    }
    printf("\n");
    btrieveLoaded = TRUE;
  }
  else
  {
    printf("Btrieve B_VERSION status = %d\n", status);
    if (status != B_RECORD_MANAGER_INACTIVE)
    {
      btrieveLoaded = TRUE;
    }
  }

  /* clear buffers */
  if (status == B_NO_ERROR)
  {
    memset(dataBuf, 0, sizeof(dataBuf));
    memset(keyBuf1, 0, sizeof(keyBuf1));
    memset(keyBuf2, 0, sizeof(keyBuf2));
  }

  /* open sample.btr */
  if (status == B_NO_ERROR)
  {

    strcpy((BTI_CHAR *)keyBuf1, FILE1_NAME);
    strcpy((BTI_CHAR *)keyBuf2, FILE2_NAME);

    keyNum  = 0;
    dataLen = 0;

    status = BTRVID(
                B_OPEN,
                posBlock1,
                dataBuf,
                &dataLen,
                keyBuf1,
                keyNum,
                (BTI_BUFFER_PTR)&clientID);

    printf("Btrieve B_OPEN status (sample.btr) = %d\n", status);
    if (status == B_NO_ERROR)
    {
      file1Open = TRUE;
    }
  }

  /* get the record with key 0 = 263512477 using B_GET_EQUAL */
  if (status == B_NO_ERROR)
  {
    memset(&personRecord, 0, sizeof(personRecord));
    dataLen = sizeof(personRecord);
    personID = 263512477;    /* this is really a social security number */
    *(BTI_LONG BTI_FAR *)&keyBuf1[0] = personID;
    keyNum = 0;

    status = BTRVID(
                B_GET_EQUAL,
                posBlock1,
                &personRecord,
                &dataLen,
                keyBuf1,
                keyNum,
                (BTI_BUFFER_PTR)&clientID);

    printf("Btrieve B_GET_EQUAL status = %d\n", status);
    if (status == B_NO_ERROR)
    {
      printf("\n");
      printf("The retrieved record is:\n");
      printf("ID:      %ld\n", personRecord.ID);
      printf("Name:    %s %s\n", personRecord.FirstName,
                                 personRecord.LastName);
      printf("Street:  %s\n", personRecord.Street);
      printf("City:    %s\n", personRecord.City);
      printf("State:   %s\n", personRecord.State);
      printf("Zip:     %s\n", personRecord.Zip);
      printf("Country: %s\n", personRecord.Country);
      printf("Phone:   %s\n", personRecord.Phone);
      printf("\n");
    }
  }

  /* perform a stat operation to populate the create buffer */
  memset(&fileCreateBuf, 0, sizeof(fileCreateBuf));
  dataLen = sizeof(fileCreateBuf);
  keyNum = (BTI_WORD)-1;

  status = BTRVID(B_STAT,
                posBlock1,
                &fileCreateBuf,
                &dataLen,
                keyBuf1,
                keyNum,
                (BTI_BUFFER_PTR)&clientID);

  if (status == B_NO_ERROR)
  {
    /* create and open sample2.btr */
    keyNum = 0;
    dataLen = sizeof(fileCreateBuf);
    status = BTRVID(B_CREATE,
                  posBlock2,
                  &fileCreateBuf,
                  &dataLen,
                  keyBuf2,
                  keyNum,
                  (BTI_BUFFER_PTR)&clientID);

    printf("Btrieve B_CREATE status = %d\n", status);
  }

  if (status == B_NO_ERROR)
  {
    keyNum = 0;
    dataLen = 0;

    status = BTRVID(
                B_OPEN,
                posBlock2,
                dataBuf,
                &dataLen,
                keyBuf2,
                keyNum,
                (BTI_BUFFER_PTR)&clientID);

    printf("Btrieve B_OPEN status (sample2.btr) = %d\n", status);
    if (status == B_NO_ERROR)
    {
      file2Open = TRUE;
    }
  }

  /* now extract data from the original file, insert into new one */
  if (status == B_NO_ERROR)
  {
    /* getFirst to establish currency */
    keyNum = 2; /* STATE-CITY index */
    memset(&personRecord, 0, sizeof(personRecord));
    memset(&keyBuf2[0], 0, sizeof(keyBuf2));
    dataLen = sizeof(personRecord);

mirtheil · #4 (**permalink**) 08-04-06, 23:10

continued

Code:

    getStatus = BTRVID(
                   B_GET_FIRST,
                   posBlock1,
                   &personRecord,
                   &dataLen,
                   keyBuf1,
                   keyNum,
                   (BTI_BUFFER_PTR)&clientID);

    printf("Btrieve B_GET_FIRST status (sample.btr) = %d\n\n", getStatus);
  }

  gneBuffer = malloc(sizeof(GNE_BUFFER));
  if (gneBuffer == NULL)
  {
     printf("Insufficient memory to allocate buffer");
    return(EXIT_WITH_ERROR);
  }
  memset(gneBuffer, 0, sizeof(GNE_BUFFER));
  memcpy(&gneBuffer->preBuf.gneHeader.currencyConst[0], "UC", 2);
  while (getStatus == B_NO_ERROR)
  {
    gneBuffer->preBuf.gneHeader.rejectCount = 0;
    gneBuffer->preBuf.gneHeader.numberTerms = 2;
    posCtr = sizeof(GNE_HEADER);

    /* fill in the first condition */
    gneBuffer->preBuf.term1.fieldType = 11;
    gneBuffer->preBuf.term1.fieldLen = 3;
    gneBuffer->preBuf.term1.fieldOffset = 108;
    gneBuffer->preBuf.term1.comparisonCode = 5;
    gneBuffer->preBuf.term1.connector = 1;
    memcpy(&gneBuffer->preBuf.term1.value[0], "CA", 2);
    posCtr += sizeof(TERM_HEADER);

    /* fill in the second condition */
    gneBuffer->preBuf.term2.fieldType = 11;
    gneBuffer->preBuf.term2.fieldLen = 3;
    gneBuffer->preBuf.term2.fieldOffset = 108;
    gneBuffer->preBuf.term2.comparisonCode = 6;
    gneBuffer->preBuf.term2.connector = 0;
    memcpy(&gneBuffer->preBuf.term2.value[0], "TX", 2);
    posCtr += sizeof(TERM_HEADER);

    /* fill in the projection header to retrieve whole record */
    gneBuffer->preBuf.retrieval.maxRecsToRetrieve = 20;
    gneBuffer->preBuf.retrieval.noFieldsToRetrieve = 1;
    posCtr += sizeof(RETRIEVAL_HEADER);
    gneBuffer->preBuf.recordRet.fieldLen = sizeof(PERSON_STRUCT);
    gneBuffer->preBuf.recordRet.fieldOffset = 0;
    posCtr += sizeof(FIELD_RETRIEVAL_HEADER);
    gneBuffer->preBuf.gneHeader.descriptionLen = posCtr;

    dataLen = sizeof(GNE_BUFFER);
    getStatus = BTRVID(
                   B_GET_NEXT_EXTENDED,
                   posBlock1,
                   gneBuffer,
                   &dataLen,
                   keyBuf1,
                   keyNum,
                   (BTI_BUFFER_PTR)&clientID);

     printf("Btrieve B_GET_NEXT_EXTENDED status = %d\n", getStatus);

    /* Get Next Extended can reach end of file and still return some records */
    if ((getStatus == B_NO_ERROR) || (getStatus == B_END_OF_FILE))
    {
      printf("GetNextExtended returned %d records.\n", gneBuffer->postBuf.numReturned);
      for (i = 0; i < gneBuffer->postBuf.numReturned; i++)
      {
        dataLen = sizeof(PERSON_STRUCT);
        memcpy(dataBuf, &gneBuffer->postBuf.recs[i].personRecord, dataLen);
        status = BTRVID(
                    B_INSERT,
                    posBlock2,
                    dataBuf,
                    &dataLen,
                    keyBuf2,
                    -1,   /* no currency change */
                    (BTI_BUFFER_PTR)&clientID);
      }
      printf("Inserted %d records in new file, status = %d\n\n", gneBuffer->postBuf.numReturned, status);
    }
    memset(gneBuffer, 0, sizeof(GNE_BUFFER));
    memcpy(&gneBuffer->preBuf.gneHeader.currencyConst[0], "EG", 2);
  }

  free(gneBuffer);
  gneBuffer = NULL;

  /* close open files */
  if (file1Open)
  {
    dataLen = 0;

    status = BTRVID(
                B_CLOSE,
                posBlock1,
                dataBuf,
                &dataLen,
                keyBuf1,
                keyNum,
                (BTI_BUFFER_PTR)&clientID);

    printf("Btrieve B_CLOSE status (sample.btr) = %d\n", status);
  }

  if (file2Open)
  {
    dataLen = 0;

    status = BTRVID(
                B_CLOSE,
                posBlock2,
                dataBuf,
                &dataLen,
                keyBuf2,
                keyNum,
                (BTI_BUFFER_PTR)&clientID);

    printf("Btrieve B_CLOSE status (sample2.btr) = %d\n", status);
  }

    #if !defined(BTI_DOS) && !defined(BTI_DOS_32R) && \
  !defined(BTI_DOS_32B) && !defined(BTI_DOS_32P)
  if (btrieveLoaded)
  {
    dataLen = 0;
    status = BTRVID(B_STOP, posBlock1, dataBuf, &dataLen, keyBuf1, keyNum,
             (BTI_BUFFER_PTR)&clientID);
    printf("Btrieve STOP status = %d\n", status);
    if (status != B_NO_ERROR)
    {
       status = EXIT_WITH_ERROR;
    }
  }
  #endif

  return(status);
}

Cardinal24 · #5 (**permalink**) 08-04-06, 23:30

Quote:

Originally Posted by mirtheil

As posted elsewhere:
A few questions to start:
1. What version of Btrieve/PSQL are you using?
2. What's showing in the MKDE trace for the opcode 36?
3. Do you have an index on this date field?
4. What key are you using for the GNE?

It's not the same but I modified the BTRSAMP to do a Less than or Equal and it worked for me. It was a string field and not a date but it should function the same. It might be worth while to modify the BTRSAMP to use a Date field and try directly (without your layer).
x

Before proceeding, I have an "update". I have discovered that the 2 controls ARE working (I swear they weren't before) but independently! In other words, I can say "Greater than or equal" and it works. I can say "Less than or equat" and it works. But I can't say both!

1. An old version. Like 6x, or even earlier.
2. I haven't look at the trace.
3. Yes. This file has 4 (segmented) indices, all of which use this field.
4. Key 1. THe date field is the second segment in this key.

Cardinal24 · #6 (**permalink**) 08-04-06, 23:49

I've discovered that I am failing with a Status 64.

64: The filter limit has been reached
The MicroKernel returns this status code for the following reasons:

During a Get Next Extended, Get Previous Extended, Step Next Extended, or Step Previous Extended operation, a rejected record was reached; no other record can satisfy the given filtering condition, going in the direction that the operation specified. This is applicable only if the first segment of the key that the key number specified is also used as the first term of the filtering field.
The number of records to be retrieved is greater than the number of records present in the file that satisfy the filter condition. This option is specified in the data buffer of the extended operation.

Cardinal24 · #7 (**permalink**) 08-05-06, 00:31

I made an error. Us programmers do that, y'know.
If you look at my code, I am putting the date that GNE is comparing each record to into tempdate.

FIRST_FRAME:
MakeTerm(&ext.lpSQLFilter->Term[ext.lpSQLFilter->NumTerms++], DSQL_DATE, sizeof(Bn_Date), offsetof(struct TRANS_REC, tdate),
DSQL_GORE, DSQL_ANDTERM, (LPVOID)&tempdate) ;
}

SECOND_FRAME:
MakeTerm(&ext.lpSQLFilter->Term[ext.lpSQLFilter->NumTerms++], DSQL_DATE, sizeof(Bn_Date), offsetof(struct TRANS_REC, tdate),
DSQL_LORE, DSQL_ANDTERM, (LPVOID)&tempdate) ;

That is wrong.

I need to put the date that the GTE is comparing each record to into tempdate1 and the date that the LTE is comparing each record to into tempdate2. I was somehow unconsciously led to that by running the following tests:

I carelessly let myself believe that my code somehow transferred the date to the call and then made tempdate available for reuse. Nope. tempdate is a pointer. Oh well. I only spent 2 hours on this one. I have a lot more at other times in my career.

For example:
GTE 04-04-2006 (LTE empty) 3525 hits
GTE 04-05-2006 (LTE empty) 3511 hits
GTE 04-06-2006 (LTE empty) 3492 hits
The above is completely normal and expected.

(Next set)
GTE 04-04-2006 (LTE 04-04-2006) 14 hits
GTE 04-04-2006 (LTE 04-05-2006) 14 hits (again)
GTE 04-04-2006 (LTE 04-06-2006) 14 hits (still)
GTE 04-04-2006 (LTE 04-06-2099) 14 hits (93 years)
GTE 04-04-2006 (LTE 04-06-1950) 14 hits (-56 years)

Now that I know what I was doing wrong, I can clearly see why I got those results. However, we work forward, not backwards.

I remember that little bit of doubt when I typed in that code earlier tonight. Some call it a brain fart. That sounds about right to me.

Thank you,
David

Internet Services

Web Development

Digital Marketing

Consumer Tech