S
Siegfried Heintze
I inherited a perl CGI application that uses the integer primary key
in the
Microsoft Access database fabricate as the file name of flat text
file to hold arbitrary amounts of text. It just dawned on me that this
is a bad idea! If we compress the database, all those unique integer
keys will change! Correct?
What is a way to fix this?
Should we have actually store the text in the Microsoft database
instead of
storing the file name of the text file in the Access database? Many
database
vendors, including Microsoft Access have a feature like memo or blob
for hold large amounts of data. Can Microsoft Access store memos and blobs
as efficiently as the file system can store them in external flat files?
But what about jpg or png or gif files? Let us suppose we have a web
application that stores data on persons. One data enters then, their
name, address, email address, phone etc. and then uploads a gif or jpg
photograph.
If we use the blob feature in the database, how do we use the img tag
to point to a database record? If you are going to store gifs, it
seems you would have to store them in a flat file and store the file
name in the database. OK, how do you generate unique file names if you
don't use the integer primary key?
Assuming that you agree that my text and pictures need to be stored in flat
files external to the Microsoft Access database and primary keys will change
when I compress the database: how do I dream up unique integers that are not
based on the primary key? I think I need to store a large array of bits and
look for the first bit clear to find a unique integer, and set it when
creating a new record. When deleting a record, I need to clear its
corresponding bit. Where might I store a single large array of bits and
synchronize access to it? I suppose I could create a table with a single row
and that row has a single field of type blob. Ughhh... Is there any example
code to do this?
Thanks,
Siegfried
in the
Microsoft Access database fabricate as the file name of flat text
file to hold arbitrary amounts of text. It just dawned on me that this
is a bad idea! If we compress the database, all those unique integer
keys will change! Correct?
What is a way to fix this?
Should we have actually store the text in the Microsoft database
instead of
storing the file name of the text file in the Access database? Many
database
vendors, including Microsoft Access have a feature like memo or blob
for hold large amounts of data. Can Microsoft Access store memos and blobs
as efficiently as the file system can store them in external flat files?
But what about jpg or png or gif files? Let us suppose we have a web
application that stores data on persons. One data enters then, their
name, address, email address, phone etc. and then uploads a gif or jpg
photograph.
If we use the blob feature in the database, how do we use the img tag
to point to a database record? If you are going to store gifs, it
seems you would have to store them in a flat file and store the file
name in the database. OK, how do you generate unique file names if you
don't use the integer primary key?
Assuming that you agree that my text and pictures need to be stored in flat
files external to the Microsoft Access database and primary keys will change
when I compress the database: how do I dream up unique integers that are not
based on the primary key? I think I need to store a large array of bits and
look for the first bit clear to find a unique integer, and set it when
creating a new record. When deleting a record, I need to clear its
corresponding bit. Where might I store a single large array of bits and
synchronize access to it? I suppose I could create a table with a single row
and that row has a single field of type blob. Ughhh... Is there any example
code to do this?
Thanks,
Siegfried