[yunqa.de] Re: Disqlite 3 Pro : Temporary table question

  • From: "Jon Burnham" <jba@xxxxxxxxxxxxxx>
  • To: <yunqa@xxxxxxxxxxxxx>
  • Date: Sun, 1 Apr 2012 20:26:38 +0100

Hi Ralf

Thanks for sticking by me on this. Under a bit of pressure here (huge 
actually). Should have stuck to repainting the Sistine Chapel.

This is what I did. I just put a DB control on the GUI, gave the database a 
name  - switched to connected. It gave me the warning obviously. The other way 
round, no name and switched to connected warns me that it will be a temporary 
db, but it does not allow me to give it a name. It was this second action that 
I was referring to. It is arguable that if the temp db is created here, that it 
ought to be able to be named, although its detail is not defined. So that when 
you go down to code against it, at least you know what you have called it. This 
becomes even more important when there are several dbs being used in the same 
project as it allows you to keep track of them visually. Also, perhaps there 
should be visual feedback on the type they are: disktemp, mem and file.

I am using three MySQL dbs - local, remote and backup, each using the same DB 
structure of over 50 tables. Some of these I want to be loaded on run into 
Disqlite, as they are mainly read-only, but need updating via the MySQL tables. 
This app is a client and needs to work in a high performance pooled basis from 
various different sites. I cannot resort to creating static databases on each 
installation, because something in my head tells me this might increase the 
points of failure, as error checking the MySQL to local updates have been 
successful is more complex.. So either a temporary disk or memory based 
on-the-fly creation seems less complicated. I *think* you may have said in the 
past that disk is preferable to ram for speed vs. Memory. Especially if there 
are going to be quite a few bigger databases (100K rows x 300 bytes plus) in 
memory terms.

So getting back to my first point. If there was a way of reflecting the 
allocation of the database by name and type on the control, this would be very 
helpful to see at a glance (rather than ploughing through code), what Disqlite 
DBs there are and what they are called. Especially if there are more than a 
few. But then I am also think of one table per DB in some cases. Thinking about 
this, perhaps I should be working with only one DB and many tables instead.

Another question that comes up, is this SQL statement:

=======
SELECT
iwin.geo.CountryCode,
iwin.geo.CountryName
FROM
iwin.geo
WHERE
404232216 >= iwin.geo.StartIPNum AND
404232216 <= iwin.geo.EndIPNum LIMIT 1
=========

How do I use parameters for the numbers, quick pointer please.

Again, thank you.

Kind regards

Jon




-----Original Message-----
From: yunqa-bounce@xxxxxxxxxxxxx [mailto:yunqa-bounce@xxxxxxxxxxxxx] On Behalf 
Of Delphi Inspiration
Sent: 01 April 2012 19:53
To: yunqa@xxxxxxxxxxxxx
Subject: [yunqa.de] Re: Disklite 3 Pro : Temporary table question

On 01.04.2012 17:19, Jon Burnham wrote:

> I was looking at the control, not in code, it warns me that I cannot 
> name a temp file when enabled.

I do not know what you did to "create a temp file at the control" so it is hard 
for me to help avoiding the warning.

Please know that in DISQLite3, a memory database and a temporary table are two 
different things. Memory databases are stored entirely in memory, including all 
tables and indexes. A memory database is temporary in that they are never 
written to disk, it memory is automatically freed when the database is closed. 
No need to create temporary tables for memory databases.

  DISQLite3.chm -> SQLite3 Documentation -> In-Memory Database

Temp tables are not kept in memory but are written to a separate disk file, 
very much like regular tables. The only difference is that this disk file (with 
all its tables) is automatically deleted when the database is closed:

  DISQLite3.chm -> SQL Syntax -> CREATE TABLE
  DISQLite3.chm -> SQLite3 Documentation -> Temporary Dis Files used

My impression is that you are in fact looking for memory databases rather than 
temporary tables.

> It would be good if you could do it from there. I just want to borrow 
> a structure/data from an existing dataset and use it as a local SQL 
> cache.

Did you try the TDISQLite3DataSetImporter class? It does not import at 
design-time, but at run-time does exactly what you are looking for:
Create structure and copy data with just a single command.

Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa



_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa



Other related posts: