Re: Advice on using DISqlite3
- From: Falanga Alain <afalanga~r~emove-this@xxxxxxxxxxx>
- Date: Wed, 25 Oct 2006 08:52:09 +0200
marek jedlinski a présenté l'énoncé suivant :
I hope it's OK to post here; there is no support forum at the Delphi
Inspiration site. I'm trying the Personal edition of DISqlite3 and there
are a few things I cannot figure out.
1. What is the simplest way to store a stream in a blob field? There is a
demo that deals with blobs, but the procedure seems quite convoluted. I'm
creating a table and trying to store files in a blob field. The table is:
CREATE TABLE IF NOT EXISTS BlobTable (rowid INTEGER PRIMARY KEY name
TEXT, data BLOB);
Here is what I'm curently doing:
- load file contents into TMemoryStream
- prepare an UPDATE statement: 'UPDATE "BlobTable" SET "data"=? WHERE "rowid"=?;'
- add a row to the table:
'INSERT INTO "BlobTable" ("name") VALUES ("testfile");'
- get the last inserted rowid (sqlite3_last_insert_rowid)
- bind the rowid to the prepared UPDATE statement
- bind the stream contents to the prepared UPDATE statement
- execute the UPDATE statement.
There are two problems here: instead of moving file contents directly from
TFileStream, I have to use an intermediary TMemoryStream, because
sqlite3_bind_blob requires a pointer to a memory block. It would be more
efficient to move data without having to make an in-memory copy first.
Secondly, it takes two separate operations to create the blob: first create
a new row, then update it using its rowid. I suppose there should be a
single-step procedure, but there is nothing in the help or the demos.
2. What is the simplest way to extract blob contents, and can I use any
TStream descendant for that?
3. What happens when a blob-type field is returned by a SELECT statement
(via sqlite3_exec) and passed to the row callback? (The callback procedure
provides all values as strings)
4. The feature matrix mentions "BLOB Literal SQL Syntax X'ABCD'", which is
only supported in the commercial edition. What exactly does it do? I
couldn't find anything in various SQL tutorials, perhaps I don't know what
I'm looking for ;)
5. By trial and error I've found that I need to double every single and
double quote before storing a string in a TEXT field. Are there any other
characters that must be escaped? What about control characters, like CR,
LF? (And couldn't the DB engine take care of it? It breaks encapsulation,
since it means every class that stores text in the DB has to be aware of
this internal implementation detail in sqlite.)
Thanks a lot,
marek
marek
Do you have trying to contact Ralph, it answer very fine and with details.
Cheers,
A.
--
Ceci est une signature automatique de MesNews.
Site : http://www.mesnews.net
.
- References:
- Advice on using DISqlite3
- From: marek jedlinski
- Advice on using DISqlite3
- Prev by Date: getting Delphi to accept a passed-in VBScript 'ByRef Variant' as a file handle or similar
- Next by Date: Re: getting Delphi to accept a passed-in VBScript 'ByRef Variant' as a file handle or similar
- Previous by thread: Advice on using DISqlite3
- Next by thread: Re: Advice on using DISqlite3
- Index(es):
Relevant Pages
|
