Store an Object in a SQLite Database

What would be the advantages and disadvantages of storing information as an object in a SQLite Blob field.

Say you create a class for employee that has all the appropriate information related to employees in it then instead of having fields for each piece of information about the employee in the database table, you just store the object instead.

Am very interested in your opinions.

using sqlite I would not put it all in a blob

you basically negate the point of having a sql database but doing so

try & find all the employees who work in a certain department - you have to search EVERY blob
try & find all employees who work in a certain dept & make more than some amount - now YOU have to write EVERY query in code where this is a pretty simple sql query

basically I’d say this is a bad idea

Agree with Norman. Add a field for all information because you just know that some day, someone will want to search on the information in the blob.

I see nothing good coming out of storing in in the blob. Let the database do the job it’s good at: storing data, reading, querying data in fields. It’s not good at looking through blob data.

Yes, it’s more work but only marginally. I would actually argue putting it into a blob is MORE work but your results may vary depending on how comfortable you are with database coding.

Using a blob for that data is a big mistake that won’t be obvious until you’re required to query that data. Follow Norman’s and Bob’s advice. Create the appropriate tables and fields for the data so that it is well organized, easily searched and easily retrieved.

i create a query with a field that join all the other field for my keyword search.

CREATE VIEW "qryfrmClientSearch" AS 
SELECT ClientID AS ClientID, ClientID AS ClientNo,
LastName, FirstName, CompanyName, Country, HomeEmail, WorkEmail, ClientStatus, Category, 
CASE WHEN Address Is Null Then '' Else Address End
|| '!' || CASE WHEN County Is Null Then '' Else County End
|| '!' || CASE WHEN Country Is Null Then '' Else Country  End
|| '!' || CASE WHEN PostCode Is Null Then '' Else PostCode End
|| '!' || CASE WHEN Town Is Null Then '' Else Town End
|| '!' || CASE WHEN ClientTitle Is Null Then '' Else ClientTitle End
|| '!' || CASE WHEN FirstName Is Null Then '' Else FirstName End
|| '!' || CASE WHEN LastName Is Null Then '' Else LastName End
|| '!' || CASE WHEN CompanyName Is Null Then '' Else CompanyName End
|| '!' || CASE WHEN HomeEmail Is Null Then '' Else HomeEmail End
|| '!' || CASE WHEN WorkEmail Is Null Then '' Else WorkEmail End
|| '!' || CASE WHEN Category Is Null Then '' Else Category End
|| '!' || CASE WHEN JobTitle Is Null Then '' Else JobTitle End
|| '!' || CASE WHEN ClientNotes Is Null Then '' Else ClientNotes End
|| '!' || CASE WHEN Comments Is Null Then '' Else Comments End
|| '!' || CASE WHEN InterestedIn Is Null Then '' Else InterestedIn End
|| '!' || CASE WHEN SourceContact Is Null Then '' Else SourceContact End
|| '!' || CASE WHEN ClientStatus Is Null Then '' Else ClientStatus  End
|| '!' || CASE WHEN ClientID Is Null Then '' Else ClientID  End
AS shwKeyword, 
InterestedIn , Address, Town ,  PostCode , 
ClientNotes , County, SourceContact, 
SelectionName
FROM tblClient
ORDER BY UPPER(LastName), UPPER(FirstName), UPPER(CompanyName)

William

depends on intent and plans for how data is used.

for example, its not uncommon to store XML in a database (MS SQL has an XML column type for example) . Items I would store in XML would be more of a application configuration settings than data that is intended to have relationships with other data sets.

Richard,

instead of case statement:

CASE WHEN ClientTitle Is Null Then '' Else ClientTitle End

try short hand :slight_smile:

ifnull(ClientTitle, '') as ClientTitle

[quote=147321:@Rich Hatfield]William

depends on intent and plans for how data is used.

for example, its not uncommon to store XML in a database (MS SQL has an XML column type for example) . Items I would store in XML would be more of a application configuration settings than data that is intended to have relationships with other data sets.

Richard,

instead of case statement:

CASE WHEN ClientTitle Is Null Then '' Else ClientTitle End

try short hand :slight_smile:

ifnull(ClientTitle, '') as ClientTitle

Fancy Schmancy Rich :wink: :wink:

Seriously nice tip though as I put that into my devXcentric notes.

change the query to and making it so much shorter. No idea i can do that… what a great tip.

ifnull(Address, '') || '!' || ifnull(County, '') || '!' || ifnull(Country, '')
as xxKeyword,

Thanks everyone for useful comments.