I need a very large field on a database:
It may look like:

DWORD ID
DWORD USER
CHAR NAME[40]
CHAR HEADING[80]
CHAR *DATA (very large field)

The DATA is a file I need to insert into the database.
It may be from 0 to 100MB in size.

I have to use a variable length database. But I can't find any samples
how to create this at Btrieve level.

Also How do I browse the database (Step first/next) . I don't want to
receive all the records length only the first part. If I need the DATA I
simple
get it.
Another whey is to create another database with only:
DWORD ID
CHAR *DATA (very large field)
and change the other database to:
DWORD ID
DWORD USER
CHAR NAME[40]
CHAR HEADING[80]
DWORD DATABASEID (pointer to ID on the other database)

But I really need some samples how I can create, store and receive
database with variable length.


Anyone?

Well, let's start with the language you're using.


"GTi" <nospam (AT) online (DOT) com> wrote

Sorry (living only in one world):
c/c++


"David Reed" <d (AT) nospam (DOT) com> wrote

The following code uses the Windows SDK GlobalAlloc. Feel free to
substitute the malloc of your choice.

All my files were created via Novell's XQL, so I don't have code to tell you
how to create it. The XQL statement was:

create table TABLE using "TABLE .DAT" DCOMPRESS pagesize 4096
(recordID INT(4),
code CHAR(8),
reserved CHAR(16),
flags INT(4),
commentText LVAR(4000))
with index (recordID UNIQUE,
code UNIQUE MOD)

Here's how I access it in the code:

Ok, start with a structure for the fixed portion of the record:

//
// Table Structure
// Record length: Variable: Fixed portion = 32
// Defined flags: None
//
// Record Layout: Bytes Description
// ~~~~~ ~~~~~~~~~~~
// 1-4 Record ID
// 5-12 Code
// 13-28 Reserved
// 29-32 Record Flags
// 33-4032 Text (variable)
//
#define CODE_LENGTH 8
#define RESERVED_LENGTH 16
#define FIXED_LENGTH 32
#define VARIABLE_LENGTH 4000
#define TOTAL_LENGTH FIXED_LENGTH + VARIABLE_LENGTH

typedef struct TABLEStruct
{
long recordID;
char code[CODE_LENGTH];
char reserved[RESERVED_LENGTH];
long flags;
} TABLEDef;

typedef struct TABLEKey0Struct
{
long recordID;
} TABLEKey0Def;

typedef struct TABLEKey1Struct
{
char code[COMMENTS_CODE_LENGTH];
} TABLEKey1Def;

EXTERN TABLEDef TABLE;
EXTERN TABLEKey1Def TABLEKey1;
EXTERN HANDLE hMemTableText;
EXTERN char *pTableText;


* * * *

You can do the following in the main routine:

//
// Allocate space for the text portion of the table
//
hMemTableText = GlobalAlloc(GPTR, TOTAL_LENGTH);
pTableText = (char *)hMemTableText;
if(hMemCommentText == NULL || pCommentText == NULL)
{
/* Error */
}

* * * * *

In the routine you use to access all of the variable length file:

char code[] = "SomeText";

strcpy(TABLE.code, code);

dataBufferLength = TOTAL_LENGTH;
rcode = BTRVID((BTI_WORD)GETEQUAL, positionBlock, pTableText ,
&dataBufferLength, TABLE.code, (BTI_SINT)1, (BTI_BUFFER_PTR)&clientID);

if(rcode == 0)
{
memcpy(&TABLE, pTableText, FIXED_LENGTH);
/* Variable portion starts at pTableText[FIXED_LENGTH] */
}

* * * * *
If you're just cycling through, set the data buffer length to FIXED_LENGTH
and pass the data structure instead of pTable Text. You'll get rcode == 22,
but you won't have the overhead of getting (potentially) a bunch of
additional data for records you may end up skipping.

*******

Bill Bach, if you're reading, is there much overhead savings in this?


Anyway, hope this helps.

David Reed
Schedule Masters, Inc.

Thanks David,

In your case you have a maximum length of 4000 bytes in the comment field -
right?
And what if the comment fields is over 4000 bytes. What happens then?

10000
or 400000 it MUST be a value. Is that correct?

Today I store the files on the disk, and the filename is the key value of my
record.
That gives me no limits on the file size. But it should be in the database.
Say - For some years ago a digital camera was producing pictures of 1-2 MB.
Today it have 5-6 MB.
If I limited this to 3 MB for some years ago, I have been lost today.
So if I go for this I must set a limit for the records to 100 MB - say 200
MB to be
sure if I can still use it in the future (not limited to pictures).
Or ?



"David Reed" <d (AT) nospam (DOT) com> wrote

You're correct about the maximum.

Funny you mention pictures.

I was faced with a similar situation earlier this year. I ended up just
storing a path to an external file containing the picture. Once I hit the
record I wanted, I'd just open the file at that location, which was a file
in the folder I was in.

....dave


"GTi" <nospam (AT) online (DOT) com> wrote

Dave,
Is this "maximum" limit restricted to Btrieve,
or is this a common to all Databases ?

Using files on disk is handy, but when using IDS it is not handy anymore...

GTi


"David Reed" <d (AT) nospam (DOT) com> wrote

The maximum I set was just arbitrary. The field is supposed to contain 3-4
lines of information, not a novel, so I set the limit. According to the
now-ancient 6.15 manual, the limit is 55,296 bytes in the server-based
environment, and 64,000 bytes in the client-based.

I'm not sure about higher verseions of Btrieve, nor other databases.

....dave



"GTi" <nospam (AT) online (DOT) com> wrote

Dave,
So storing large files on database not a option at all.
Thanks for mention the limits, that saved me loots of work

GTi

"David Reed" <d (AT) nospam (DOT) com> wrote

There are a few possible limits involved.
1) In a normal file the fixed length portion of the record must fit on
a single data file page. Indexes must reside on the fixed length
portion of the record. Page sizes currently go up to 4K so indexes
must be in the first 4K of the record. The index location is relaxed
somewhat with compressed files but I believe the compressed file total
record length is limitted to a 64K internal buffer.
2) The operation must fit into the 64K data buffer or 53K normal worst
case client / server limitted by network stack which I have seen
software chop to 32K and routers start dropping data at 16K. You can
use Get_Chunk* and Update_Chunk* operations to work on a data buffer
sized portion of the record at a time.
3) The total length of a record (fixed + variable) is limitted to 4GB
(32 bit pointer). The practical limit is smaller.
4) Total single file size is limitted to 64GB with 4K pages, smaller
with smaller pages. You can have as many files as you want.
*) IDS does not support chunk operations. IDS is pretty much a
legacy product and is in transition support.
It has been superceeded by the Pervasive.SQL V8 Security release.

Hmmm.... very interesting.
He gave a mixed mode example creating the file through one API (SQL
Create Table statement) and accessing it through the transactional
API. And in fact the way LVAR() fields are stored in the current
product has changed to allow more than one LVAR() per record which
requires some pointers to be stored in the record. So with the
current product running that create table statement would produce a
file that did not quite match the example struct given. If the file
is created through the Btrieve API it can still be created to match
the example or if accessed through the SQL API it would be
transparent. Check out the current documentation at:
http://www.pervasive.com/developerZone/

It would also be possible to break the one arbitrarily large "file"
that needs to be stored into any number of arbitrary sized chunks, and
add a segment / sequence to the record. Although in all honesty it is
probably better to just store the file as a file and eliminate the
complexity and overhead of accessing a file through a database engine.

Leonard

On Fri, 7 Nov 2003 23:42:47 +0100, "GTi" <nospam (AT) online (DOT) com> wrote: