Re: [sqlite] Memory DB to disk DB

[brettg]

Very good Keith.  I think this will work fine for me.

thanks

Quoting Keith Herold <[EMAIL PROTECTED]>:

> I am/was doing this in application, with 2.8.15 .  I simply attached
> the on-disk database to the memory, and then wrote a bunch of dump
> queries to drop the memory data to disk (from the memory db
> connection):
>
> ATTACH 'C:\my_database_on_disk.sqlitedb' AS diskdb ;
>
> Unfortunately, I don't think you can execute transactions on the
> attached database in 2.8.15, and I couldn't think of a way to attach
> to the memory database from the disk database.  I think you can
> execute transactions on the attached db in 3.x, though.
>
> It works ok, although since I was using the in-memory database as a
> preliminary db (multithreaded app), my dump queries got ugly, and
> quite slow on large sets, because I had to do the checks to ensure
> that the in-memory db wasn't dumping data that already existed in the
> database (I know, triggers, etc., but I hadn't tried those yet, and
> INSERT OR IGNORE scares me).  I eventually substituted writing text
> sql scripts to disk, and then executing those directly into the disk
> database, which saved me 25-30% of the insert time.
>
> --Keith
>
> On Fri, 18 Feb 2005 04:01:32 +0100, chorlya <[EMAIL PROTECTED]> wrote:
> > I gues you could attach in-memory db to a newly created disk db and
> > then do something like
> >
> > CREATE TABLE newDiskTbl AS SELECT * FROM memoryTbl
> >
> > Take a look at http://www.sqlite.org/lang_createtable.html for more details
> >
> > Regards,
> > chorlya
> >
> > On Fri, 18 Feb 2005 11:13:40 +1100, [EMAIL PROTECTED]
> > <[EMAIL PROTECTED]> wrote:
> > >
> > > I have a situation where I start with an in-memory DB, then need to save
> the
> > > entire thing to a new disk DB.  Does anyone know the best way to do this?
> > > Would I attach the memory DB to a newly created disk DB?  Is this even
> > > possible?  I notice the COPY command is not supported in 3.x according to
> the
> > > documentation on the website, so even if I could attach it I would still
> need a
> > > way to copy the tables.  Any suggestions would be greatly appreciated.
> > >
> > > TIA
> > > -brett
> > >
> > > 
> > > This message was sent using IMP, the Internet Messaging Program.
> > >
> > >
> >
>
>
> --
> **
> - Ever notice how 'big' isn't, compared to 'small'?
>
> - Sounds like a Wookie; acts like mad cow.
>
> - I'm not a professional; I just get paid to do this.
>
> - Rules for programming:
>1.  Get it working, right?
>2.  Get it working right.
>
> - Things I've learned about multithreaded programming:
>
> 123...   PPArrvooottieedcc ttm  ueelvvteeirrtyyhtt
> rhheiianndgge  dwi hnpi rctohhg eri aslm omscitanalgt
>  iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb
> .ee^Nr waicscee snsoetd  'aotb jtehcet -slaomcea lt'il
> m^Ne from two or more threads
> **
>





This message was sent using IMP, the Internet Messaging Program.

Re: [sqlite] Memory DB to disk DB

[Keith Herold]

I am/was doing this in application, with 2.8.15 .  I simply attached
the on-disk database to the memory, and then wrote a bunch of dump
queries to drop the memory data to disk (from the memory db
connection):

ATTACH 'C:\my_database_on_disk.sqlitedb' AS diskdb ;

Unfortunately, I don't think you can execute transactions on the
attached database in 2.8.15, and I couldn't think of a way to attach
to the memory database from the disk database.  I think you can
execute transactions on the attached db in 3.x, though.

It works ok, although since I was using the in-memory database as a
preliminary db (multithreaded app), my dump queries got ugly, and
quite slow on large sets, because I had to do the checks to ensure
that the in-memory db wasn't dumping data that already existed in the
database (I know, triggers, etc., but I hadn't tried those yet, and
INSERT OR IGNORE scares me).  I eventually substituted writing text
sql scripts to disk, and then executing those directly into the disk
database, which saved me 25-30% of the insert time.

--Keith

On Fri, 18 Feb 2005 04:01:32 +0100, chorlya <[EMAIL PROTECTED]> wrote:
> I gues you could attach in-memory db to a newly created disk db and
> then do something like
> 
> CREATE TABLE newDiskTbl AS SELECT * FROM memoryTbl
> 
> Take a look at http://www.sqlite.org/lang_createtable.html for more details
> 
> Regards,
> chorlya
> 
> On Fri, 18 Feb 2005 11:13:40 +1100, [EMAIL PROTECTED]
> <[EMAIL PROTECTED]> wrote:
> >
> > I have a situation where I start with an in-memory DB, then need to save the
> > entire thing to a new disk DB.  Does anyone know the best way to do this?
> > Would I attach the memory DB to a newly created disk DB?  Is this even
> > possible?  I notice the COPY command is not supported in 3.x according to 
> > the
> > documentation on the website, so even if I could attach it I would still 
> > need a
> > way to copy the tables.  Any suggestions would be greatly appreciated.
> >
> > TIA
> > -brett
> >
> > 
> > This message was sent using IMP, the Internet Messaging Program.
> >
> >
> 


-- 
**
- Ever notice how 'big' isn't, compared to 'small'?

- Sounds like a Wookie; acts like mad cow.

- I'm not a professional; I just get paid to do this.

- Rules for programming:
   1.  Get it working, right?
   2.  Get it working right.

- Things I've learned about multithreaded programming:

123...   PPArrvooottieedcc ttm  ueelvvteeirrtyyhtt
rhheiianndgge  dwi hnpi rctohhg eri aslm omscitanalgt 
 iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb
.ee^Nr waicscee snsoetd  'aotb jtehcet -slaomcea lt'il
m^Ne from two or more threads
**

Re: [sqlite] Memory DB to disk DB

[chorlya]

I gues you could attach in-memory db to a newly created disk db and
then do something like

CREATE TABLE newDiskTbl AS SELECT * FROM memoryTbl

Take a look at http://www.sqlite.org/lang_createtable.html for more details

Regards,
chorlya


On Fri, 18 Feb 2005 11:13:40 +1100, [EMAIL PROTECTED]
<[EMAIL PROTECTED]> wrote:
> 
> I have a situation where I start with an in-memory DB, then need to save the
> entire thing to a new disk DB.  Does anyone know the best way to do this?
> Would I attach the memory DB to a newly created disk DB?  Is this even
> possible?  I notice the COPY command is not supported in 3.x according to the
> documentation on the website, so even if I could attach it I would still need 
> a
> way to copy the tables.  Any suggestions would be greatly appreciated.
> 
> TIA
> -brett
> 
> 
> This message was sent using IMP, the Internet Messaging Program.
> 
>

RE: [sqlite] BLOB versus table storage

[Ned Batchelder]

That's not "good database design", it's relational dogma.  Good database
design involves understand what data needs to be stored and *how it's going
to be accessed*, which John hasn't told us in detail.  If you don't need to
access individual point relationally, and only are going to process entire
polygons, storing them in a blob will be a better solution.

--Ned.
http://nedbatchelder.com

-Original Message-
From: Clay Dowling [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 17, 2005 2:28 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] BLOB versus table storage


[EMAIL PROTECTED] said:
> CREATE TABLE polygons (id INTEGER PRIMARY KEY, externalref INTEGER,
> pointcount INTEGER, pointlist BLOB)
>
> When I insert data to this table, I have to write a binary list of x,y
> coordinates in a sequential memory region before storing in the database.
> Getting information back from the database requires a conversion in the
> opposite direction.  Typically these lists contain 10-20 points, so they
> are not very large.
>
> This currently works, but I'm wondering if it would be better to create a
> new table for the points and reference the polygon primary key:

Create the second table.  There shouldn't be any question about this. 
That's just good database design.

Clay Dowling
Opinionated Programmer

-- 
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/
Articles, Reviews and Commentary on web development

[sqlite] Memory DB to disk DB

[brettg]

I have a situation where I start with an in-memory DB, then need to save the
entire thing to a new disk DB.  Does anyone know the best way to do this? 
Would I attach the memory DB to a newly created disk DB?  Is this even
possible?  I notice the COPY command is not supported in 3.x according to the
documentation on the website, so even if I could attach it I would still need a
way to copy the tables.  Any suggestions would be greatly appreciated.

TIA
-brett




This message was sent using IMP, the Internet Messaging Program.

Re: [sqlite] BLOB versus table storage

[Noel Frankinet]

Clay Dowling wrote:
[EMAIL PROTECTED] said:
 

CREATE TABLE polygons (id INTEGER PRIMARY KEY, externalref INTEGER,
pointcount INTEGER, pointlist BLOB)
When I insert data to this table, I have to write a binary list of x,y
coordinates in a sequential memory region before storing in the database.
Getting information back from the database requires a conversion in the
opposite direction.  Typically these lists contain 10-20 points, so they
are not very large.
This currently works, but I'm wondering if it would be better to create a
new table for the points and reference the polygon primary key:
   

Create the second table.  There shouldn't be any question about this. 
That's just good database design.

Clay Dowling
Opinionated Programmer
 

maybe but none of the GIS database use that option, store your point in 
a blob and create function to manipulate it, look the OpenGis simple 
feature spec for inspiration.

--
Noël Frankinet
Gistek Software SA
http://www.gistek.net

[sqlite] Journalling Settings

[Ehren Katzur]

Hello everyone.

I can see that the journal settings for the SQLite DB are buried deep within the
code, so I figured I would ask before I go meddling.  

Is there a simple / easy / proper  way to disable journalling for a given
database?  Can this be done on the fly ( on / off ), or is it possible at all?

I would like to run journalling most of the time, but for some very intensive
operations, I would like to disable it.   

Any information would be appreciated.

Ehren K

[sqlite] Using sqlite3_get_table get a row, modifing the row, and then updating the row in the sqlite datatabase obtain from sqlite3_get_table

[Steve Frierdich]

I retrieved some rows from a sqlite database using the sqlite3_get_table 
function. Some of these rows are these are modified. How do I have these 
rows update the same sqlite database? Is there another sqlite function I 
can call passing the table that was retrieved from the sqlite3_get_table 
function that will update the database?

Like in with ADO I can retrieve some rows in a table, then make a the 
table a disconnected recordset, next modify the rows in the disconnected 
record set, and then just reapply a connection the disconnected 
recordset and the database is updated.
Thanks
Steve

Re: [sqlite] BLOB versus table storage

[Clay Dowling]

[EMAIL PROTECTED] said:
> CREATE TABLE polygons (id INTEGER PRIMARY KEY, externalref INTEGER,
> pointcount INTEGER, pointlist BLOB)
>
> When I insert data to this table, I have to write a binary list of x,y
> coordinates in a sequential memory region before storing in the database.
> Getting information back from the database requires a conversion in the
> opposite direction.  Typically these lists contain 10-20 points, so they
> are not very large.
>
> This currently works, but I'm wondering if it would be better to create a
> new table for the points and reference the polygon primary key:

Create the second table.  There shouldn't be any question about this. 
That's just good database design.

Clay Dowling
Opinionated Programmer

-- 
Lazarus Notes from Lazarus Internet Development
http://www.lazarusid.com/notes/
Articles, Reviews and Commentary on web development

Re: [sqlite] Re: AUTOINCREMENT Functionality by default required in 3.0.8 (Is it possible?)

[John LeSueur]

[EMAIL PROTECTED] wrote:
Hi Morten,
Thanks for the help. I got the idea for doing this based on the trigger. I 
shall implement the same. Thanks once again for the help. 

Regards,
Sankara Narayanan B

morten bjoernsvik <[EMAIL PROTECTED]> 
02/17/2005 03:06 PM
Please respond to
sqlite-users@sqlite.org

To
sqlite-users@sqlite.org
cc
Subject
Re: [sqlite] Re: AUTOINCREMENT Functionality by default required in 3.0.8 
(Is it possible?)



Hi
You can easily fix this trick with the "insert null to
and integer primary key" trick or by using a trigger.
Use a reference value you either increase or decrease,
or count to set for new values.
% cat /data/temp/test.sql
.header on
create table test (
   num integer,
   num2integer,
   infotext,
   primary key (num)
);
create trigger total_num_for_test after insert on test
begin
   update test set num2 = (
   select count(*) from test
   ) where num=new.num;
end;
insert into test (num,info) values (null,'number
one');
insert into test (num,info) values (null,'number
two');
insert into test (num,info) values (null,'number
three');
select num,info,num2 from test;
% sqlite3 test.db < /data/temp/test.sql
num|info|num2
1|number one|1
2|number two|2
3|number three|3
%
--
MortenB
--- [EMAIL PROTECTED] skrev: 
 

Hi,
A soft reminder. Please inform if the below
mentioned request is a 
possibility?

Regards,
Sankara Narayanan.
   


 

what happens If you do:
delete from test where num = 2;
insert into test (num, info) values(null, 'number four');
Wouldn't you have:
num|info|num2
1|number one|1
3|number three|3
3|number four|3
John LeSueur

Re: [sqlite] Basic insert statement - syntax problem

[John LeSueur]

Asko Kauppi wrote:
Still, shouldn't the 'mri_output' (table name) be quoted?
17.2.2005 kello 11:45, Anirban Sarkar kirjoitti:
 I am really very sorry.
I mistyped the entire sql statement.
This is what my actual code looks like:
sqlite db1 "cesc_simputer.db"
#Inserting data into mri_output table
  set connection_no { }
  set insert_normal "insert into mri_output (pwd,ac_my,con_no) values 
('$pass','$ac_mth$ac_yr','$sel_con')"
  db1 eval insert_normal {} {

  }

Table names (and column names) only need to be quoted when they may 
conflict with keywords in the sql language.

Googling, I found:
Before the SQL-92 standard, it was not legal to have object names 
(identifiers) in a database that duplicated keywords in the language, 
were case-sensitive or contained spaces. SQL-92 introduced a single new 
standard to make any of them legal, provided that the identifiers were 
defined within pairs of double-quote symbols and were always referred to 
using double-quote delimiters.

So while sqlite would understand 'mri_output', it is not necessary, and 
it would be preferred to use double quotes:"mri_output"

John LeSueur

Re: [sqlite] Python bindings for SQLite 3?

[Christopher Petrilli]

On Thu, 17 Feb 2005 10:00:08 -0800, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> On Feb 17, 2005, at 9:53 AM, H. Wade Minter wrote:
> > I'm playing around with some Python stuff, and was wondering if there
> > were any reasonably stable bindings for SQLite 3?  I've got an
> > existing SQLite 3 database that I want to work against, so I'd rather
> > not drop back to SQLite 2?
> 
> I have been using Roger Binn's Another Python SQLite Wrapper.  It is a
> direct wrapper of the SQLite APIs while still "bringing up" the API to
> Python's level of abstraction.
> 
> It has worked flawlessly for me.
> 
> http://www.rogerbinns.com/apsw.html
> 

I'd second this, and have found it more reliable than the other Python
wrapper (PySQLite)...

Chris
-- 
| Christopher Petrilli
| [EMAIL PROTECTED]

Re: [sqlite] Python bindings for SQLite 3?

[bbum]

On Feb 17, 2005, at 9:53 AM, H. Wade Minter wrote:
I'm playing around with some Python stuff, and was wondering if there 
were any reasonably stable bindings for SQLite 3?  I've got an 
existing SQLite 3 database that I want to work against, so I'd rather 
not drop back to SQLite 2?
I have been using Roger Binn's Another Python SQLite Wrapper.  It is a 
direct wrapper of the SQLite APIs while still "bringing up" the API to 
Python's level of abstraction.

It has worked flawlessly for me.
http://www.rogerbinns.com/apsw.html

[sqlite] Python bindings for SQLite 3?

[H. Wade Minter]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I'm playing around with some Python stuff, and was wondering if there were 
any reasonably stable bindings for SQLite 3?  I've got an existing SQLite 
3 database that I want to work against, so I'd rather not drop back to 
SQLite 2?

I've found http://www.pysqlite.org/ , but it doesn't appear to be ready 
for prime time.

Suggestions?
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (FreeBSD)
iD8DBQFCFNoeo4DwsyRGDscRAr3UAKCvyuBRCSmovgtjXMCpA5GxsV4OggCfdU7Y
fHp27AfJqhmvm2NozW3EaiU=
=mMX8
-END PGP SIGNATURE-

Re: [sqlite] BLOB versus table storage

[Bob Gilson]

[EMAIL PROTECTED] wrote:
Does anyone have a recommendation as to which solution would be more optimal, both for space and processing time?  

Perhaps you should consider using Postgres. It has builtin support for 
polygons. It can index polygon columns. And it includes some builtin 
functions to work with them.
   
http://www.postgresql.org/docs/7.4/static/datatype-geometric.html#AEN4852
   http://www.postgresql.org/docs/7.4/static/functions-geometry.html

Good luck,
Bob

Re: [sqlite] BLOB versus table storage

[Noel Frankinet]

[EMAIL PROTECTED] wrote:
I have a fairly simple database design with a series of tables that reference 
graphical data.  Currently, there is one table that contains the following:
CREATE TABLE polygons (id INTEGER PRIMARY KEY, externalref INTEGER, pointcount 
INTEGER, pointlist BLOB)
When I insert data to this table, I have to write a binary list of x,y 
coordinates in a sequential memory region before storing in the database.  
Getting information back from the database requires a conversion in the 
opposite direction.  Typically these lists contain 10-20 points, so they are 
not very large.
This currently works, but I'm wondering if it would be better to create a new 
table for the points and reference the polygon primary key:
CREATE TABLE pointlist (polyid INTEGER, x INTEGER, y INTEGER)
On average, I'm managing 5+ polygons, so the pointlist could exceed 1 
million entries very easily.  Points are not shared between polygons.
Does anyone have a recommendation as to which solution would be more optimal, 
both for space and processing time?  In a worst case scenario, I could insert 
over 1 million polygons, resulting in a max 20 million point list table.
Thanks,
John

 

you will need some form of spatial indexing if you want to retrieve your 
data faster. I use a simpe quad-tree but I'm sure that better design exist.


--
Noël Frankinet
Gistek Software SA
http://www.gistek.net

RE: [sqlite] BLOB versus table storage

[Cariotoglou Mike]

I agree. I have done similar work for GIS data. Unless you absolutely
need access to inidvidual x,y data at the *sql* level, it is much better
to use memory storage for these. And, if you plan to do operations like
point-in-polygon, which I am sure you will, you need access to all the
points at the same time, which means that streaming them into a BLOB
will always be much better. One tip: if you can, use a binary storing
technqiue instead of a text//based one. Converting to/from floating
point numbers is a rather expensive operation. So, serialize the points
to a data structure, and write it with one operation.

It helps if you violate some design rules, when you do this. Consider
this:

In Delphi, which is my native language, the *good* design (theoretically
speaking) would be this :

Type

Tpoint=class
 x,
 y:double
End;

Tpolygon=class
 points:tlist;
... Other properties and methods
End;

Or even :

Tpolygon=class
 points:array of tpoint;
End;

However, this design does not serialize wellm as each TPOINT element is
not just data, but has "hidden" pointer that cannot be serialized

Now, consider this:

Tpoint=RECORD
 x,y:double
End;

Tpolygon=class
 points:array of tpoint;
End;

Now, "points" is a sequencial array of records in memory, so you can
stream to/from a blob with a single operation... 

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, February 17, 2005 5:30 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] BLOB versus table storage
> 
> On Thu, 2005-02-17 at 10:12 -0500, [EMAIL PROTECTED] wrote:
> > Does anyone have a recommendation as to which solution 
> would be more 
> > optimal, both for space and processing time?  In a worst case 
> > scenario, I could insert over 1 million polygons, resulting 
> in a max 
> > 20 million point list table.
> > 
> 
> I think storing the points in a BLOB will likely be much 
> faster and more compact too.  The downside is that you cannot 
> query for polygons by point values (unless, perhaps, you 
> create some custom SQL functions to scan the
> BLOBs.)
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 

Re: [sqlite] Beginner Problem...

[Keith Herold]

On Thu, 17 Feb 2005 16:34:07 +0100, Peter Berkenbosch <[EMAIL PROTECTED]> wrote:
> Yes :)
> 
> -Oorspronkelijk bericht-
> Van: Claudio Bezerra Leopoldino [mailto:[EMAIL PROTECTED]
> Verzonden: donderdag 17 februari 2005 16:25
> Aan: SQLiter Mailing List
> Onderwerp: [sqlite] Beginner Problem...
> 
> 
> I've used SQLite utility to learn about the library,
> but have a doubt...
> I need to submit sql text scripts to the utility
> instead of command line sql code. It´s possible?
> 
> Cláudio Leopoldino
> 
> ___
> Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora.
> http://br.acesso.yahoo.com/ - Internet rápida e grátis
> 
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 14-2-2005
> 
> --
> No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 14-2-2005
> 
> 

somewhat more helpfully :), 

.read 

replace  with the name of your script.  I have had some
trouble with this, when reading in files which have binary-encoded
data (using sqlite_binary_encode), but for everything else, it works
fine.


I would also use .output when reading scripts; if there are any
errors, the information scrolls by a little two quickly, and if the
script is long, the scroll buffer will lose the info:

.output myoutputfile.txt

To redirect to stdout, use:

.output stdout

For help:

.help

--Keith




-- 
**
- Ever notice how 'big' isn't, compared to 'small'?

- Sounds like a Wookie; acts like mad cow.

- I'm not a professional; I just get paid to do this.

- Rules for programming:
   1.  Get it working, right?
   2.  Get it working right.

- Things I've learned about multithreaded programming:

123...   PPArrvooottieedcc ttm  ueelvvteeirrtyyhtt
rhheiianndgge  dwi hnpi rctohhg eri aslm omscitanalgt 
 iowcbh,je engceltvo ebwrah lip,co hso srci abonlt ehb
.ee^Nr waicscee snsoetd  'aotb jtehcet -slaomcea lt'il
m^Ne from two or more threads
**

RE: [sqlite] Beginner Problem...

[Peter Berkenbosch]

EG:
% sqlite3 test.db < /data/temp/test.sql

-Oorspronkelijk bericht-
Van: Claudio Bezerra Leopoldino [mailto:[EMAIL PROTECTED] 
Verzonden: donderdag 17 februari 2005 16:25
Aan: SQLiter Mailing List
Onderwerp: [sqlite] Beginner Problem...


I've used SQLite utility to learn about the library,
but have a doubt... 
I need to submit sql text scripts to the utility
instead of command line sql code. It´s possible?

Cláudio Leopoldino







___ 
Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora.
http://br.acesso.yahoo.com/ - Internet rápida e grátis

-- 
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 14-2-2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 14-2-2005
 

Re: [sqlite] INSERT, UPDATE or..?

[Dennis Cote]

On Thu, 17 Feb 2005 01:22:38 +0200, Asko Kauppi <[EMAIL PROTECTED]> wrote:
> 
> Please help me refine the following SQL.  I have pretty much all the C
> code in place, table creation & detection works, then.. brick wall.
> 
> I cannot add a single small text string into the table. What am I doing
> wrong?  Trying with the
> "UPDATE '%q' SET val=?2 WHERE key=?1;" string, where ?1 and ?2 and
> bound to text strings.
> 
> SQLite says SQLITE_DONE, but so does reading the table (= no data).
> Also sqlite3 cmdline tool confirms the emptiness.
> 

Asko,

I think your problem is that you are expecting UPDATE to insert a new record 
into the table. It won't do that. UPDATE is used to modify columns of 
records that already exist in the table. To insert new data into a table you 
need to use the INSERT command.

CREATE TABLE ini (key, value)

INSERT INTO ini VALUES('my key', 'some value')

UPDATE ini SET value = 'another value' WHERE key = 'my key'

SELECT * FROM ini 

HTH
Dennis Cote

RE: [sqlite] Beginner Problem...

[Peter Berkenbosch]

Yes :)

-Oorspronkelijk bericht-
Van: Claudio Bezerra Leopoldino [mailto:[EMAIL PROTECTED] 
Verzonden: donderdag 17 februari 2005 16:25
Aan: SQLiter Mailing List
Onderwerp: [sqlite] Beginner Problem...


I've used SQLite utility to learn about the library,
but have a doubt... 
I need to submit sql text scripts to the utility
instead of command line sql code. It´s possible?

Cláudio Leopoldino







___ 
Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora.
http://br.acesso.yahoo.com/ - Internet rápida e grátis

-- 
No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 14-2-2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 14-2-2005
 

Re: [sqlite] BLOB versus table storage

[D. Richard Hipp]

On Thu, 2005-02-17 at 10:12 -0500, [EMAIL PROTECTED] wrote:
> Does anyone have a recommendation as to which solution would be more
> optimal, both for space and processing time?  In a worst case
> scenario, I could insert over 1 million polygons, resulting in a max
> 20 million point list table.
> 

I think storing the points in a BLOB will likely be much faster and more
compact too.  The downside is that you cannot query for polygons by
point
values (unless, perhaps, you create some custom SQL functions to scan
the
BLOBs.)
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] Beginner Problem...

[Claudio Bezerra Leopoldino]

I've used SQLite utility to learn about the library,
but have a doubt... 
I need to submit sql text scripts to the utility
instead of command line sql code. It´s possible?

Cláudio Leopoldino







___ 
Yahoo! Acesso Grátis - Instale o discador do Yahoo! agora. 
http://br.acesso.yahoo.com/ - Internet rápida e grátis

[sqlite] SQLite for didatic purposes...

[Claudio Bezerra Leopoldino]

Hi,

I think to use SQLite for didactical purposes but i
want to start with some aditional information about
the results, activities performed the possible didatic
uses of sqlite. 
Do you have some experience using SQLite to teach
about DBMS? There is some known article or book about
this specific issue?

Cláudio Leopoldino, Msc




__
Converse com seus amigos em tempo real com o Yahoo! Messenger 
http://br.download.yahoo.com/messenger/ 

[sqlite] BLOB versus table storage

[john_oneill]

I have a fairly simple database design with a series of tables that reference 
graphical data.  Currently, there is one table that contains the following:

CREATE TABLE polygons (id INTEGER PRIMARY KEY, externalref INTEGER, pointcount 
INTEGER, pointlist BLOB)

When I insert data to this table, I have to write a binary list of x,y 
coordinates in a sequential memory region before storing in the database.  
Getting information back from the database requires a conversion in the 
opposite direction.  Typically these lists contain 10-20 points, so they are 
not very large.

This currently works, but I'm wondering if it would be better to create a new 
table for the points and reference the polygon primary key:

CREATE TABLE pointlist (polyid INTEGER, x INTEGER, y INTEGER)

On average, I'm managing 5+ polygons, so the pointlist could exceed 1 
million entries very easily.  Points are not shared between polygons.

Does anyone have a recommendation as to which solution would be more optimal, 
both for space and processing time?  In a worst case scenario, I could insert 
over 1 million polygons, resulting in a max 20 million point list table.

Thanks,
John

Re: [sqlite] Basic insert statement - syntax problem

[Christian Smith]

On Thu, 17 Feb 2005, Anirban Sarkar wrote:

>I am really very sorry.
>I mistyped the entire sql statement.
>This is what my actual code looks like:
>
>sqlite db1 "cesc_simputer.db"
>
>#Inserting data into mri_output table
>  set connection_no { }
>  set insert_normal "insert into mri_output (pwd,ac_my,con_no) values 
> ('$pass','$ac_mth$ac_yr','$sel_con')"
>  db1 eval insert_normal {} {
>
>  }
>
>Please note that the problem is with the syntax of the sql statement.


What is the syntax error message? Do any of the substituted values contain
apostrophe "'" characters?


>
>Regards,
>Anirban Sarkar
>
>- Original Message -
>From: "Asko Kauppi" <[EMAIL PROTECTED]>
>To: 
>Sent: Thursday, February 17, 2005 2:46 PM
>Subject: Re: [sqlite] Basic insert statement - syntax problem
>
>
>>
>> The error messages provided by SQLite seem to be very helpful, can you
>> use them?
>>
>> like: if (rc) glua_errorN( "sqlite3 error %d: %s", rc,
>> sqlite3_errmsg(db) );
>>
>> Aside that, shouldn't the table name (mri) have quotes ('mri')?
>> What's the $con_no doing without quotes as well.  Use ?1 for binding
>> stuff if that's what you need.
>>
>> -ak
>>
>>
>> 17.2.2005 kello 11:04, Anirban Sarkar kirjoitti:
>>
>>   Hi all,
>> >
>> > I am having syntax problem with a basic sqlite insert statement. I
>> > know it's very simple but just could'nt make out where I am going
>> > wrong.
>> >
>> > set insert_details "insert into mri (pwd,con_no) values
>> > ('$pwd',$con_no)"
>> > Where am I going wrong?
>> >
>> > Thanks and regards,
>> > Anirban Sarkar
>>

-- 
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
 X   - AGAINST MS ATTACHMENTS
/ \

RE: [sqlite] Getting metadata

[Dinsmore, Jeff]

SELECT * FROM SQLITE_MASTER WHERE type = 'table'

There's an explanation of the SQLITE_MASTER table at
http://sqlite.org/faq.html

Jeff Dinsmore
MIS - Interfaces
Ridgeview Medical Center
[EMAIL PROTECTED]
952.442.2191 x6592


-Original Message-
From: Sijmen Mulder [mailto:[EMAIL PROTECTED] 
Sent: Thursday, February 17, 2005 8:23 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Getting metadata


>> PRAGMA table_info(table-name);
>>
>> http://sqlite.org/pragma.html
>
> That's just what I was looking for. Thank you very much!

It looks very good, but is there also a way to query the currently
opened 
database to get a list of tables? 

Re: [sqlite] Getting metadata

[Witold Czarnecki]

SELECT name FROM sqlite_master WHERE type = 'table'
Best regards,
Witold Czarnecki
- Original Message - 
From: "Sijmen Mulder" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, February 17, 2005 3:22 PM
Subject: Re: [sqlite] Getting metadata


PRAGMA table_info(table-name);
http://sqlite.org/pragma.html
That's just what I was looking for. Thank you very much!
It looks very good, but is there also a way to query the currently opened 
database to get a list of tables?

Re: [sqlite] Getting metadata

[Sijmen Mulder]

PRAGMA table_info(table-name);
http://sqlite.org/pragma.html
That's just what I was looking for. Thank you very much!
It looks very good, but is there also a way to query the currently opened 
database to get a list of tables? 

Re: [sqlite] Getting metadata

[Sijmen Mulder]

PRAGMA table_info(table-name);
http://sqlite.org/pragma.html
That's just what I was looking for. Thank you very much!
Sijmen Mulder

Re: [sqlite] Getting metadata

[Witold Czarnecki]

PRAGMA table_info(table-name);
http://sqlite.org/pragma.html
- Original Message - 
From: "Sijmen Mulder" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, February 17, 2005 3:03 PM
Subject: [sqlite] Getting metadata


Hi there,
Is there a way to get information about the format of the database from an 
appication? IE, I would like to know whether collumn x or table y does 
exist, and what type they are. So, is it possible to get this information 
with SQLite?

Thanks in advance,
Sijmen Mulder 

[sqlite] Getting metadata

[Sijmen Mulder]

Hi there,
Is there a way to get information about the format of the database from an 
appication? IE, I would like to know whether collumn x or table y does 
exist, and what type they are. So, is it possible to get this information 
with SQLite?

Thanks in advance,
Sijmen Mulder 

[sqlite] Re: SQlite 3.1.2 autoconf problem

[Andreas Rottmann]

"D. Richard Hipp" <[EMAIL PROTECTED]> writes:

> On Wed, 2005-02-16 at 21:31 +0100, Andreas Rottmann wrote:
>> >
>> > Does check-in [2346] help?
>> > http://www.sqlite.org/cvstrac/chngview?cn=2346
>> >
>> >From a quick glance, this should fix it. I'll add this patch to
>> Debian's 3.1.2 packaging. 
>> 
>
> Version 3.1.3 is probably not more than a few days away.  I
> don't know if you want to wait or not
>
I'll wait, then.

Rotty
-- 
Andreas Rottmann | [EMAIL PROTECTED]  | [EMAIL PROTECTED] | [EMAIL 
PROTECTED]
http://yi.org/rotty  | GnuPG Key: http://yi.org/rotty/gpg.asc
Fingerprint  | DFB4 4EB4 78A4 5EEE 6219  F228 F92F CFC5 01FD 5B62

A. Because it breaks the logical sequence of discussion
Q. Why is top posting bad?

Re: [sqlite] Unrecognised token error in insert statement

[lawrence.chitty]

> 
> From: "Anirban Sarkar" <[EMAIL PROTECTED]>
> Date: 2005/02/17 Thu AM 10:51:08 GMT
> To: 
> Subject: [sqlite] Unrecognised token error in insert statement

> The variable $system_tm contains the value 16:40
> When I try to insert this value in a varchar field in a sqlite table it gives 
> me ' unrecognised token ":" ' due to which the insertion process fails.
> 
> How do I get rid of this?

ensure that $system_tm is surrounded with ' ' in the sql. If that isn't the 
problem, use 'puts' to output the actual sql statement you have built and are 
passing to 'db eval', and post here.

> Probably should be some addslashes or some other stuff, but I am not quite 
> sure.

The only thing that I think needs escaping are , (commas), which need escaping 
with a second comma.
You can deal with these with a string map, e.g.

set my_var [string map {, ,,} $my_var]

Lawrence

> Kindly help.
> 
> Regards,
> Anirban Sarkar
> 
> 

-
Email sent from www.ntlworld.com
virus-checked by McAfee
visit www.ntlworld.com/security for more information
 

[sqlite] Unrecognised token error in insert statement

[Anirban Sarkar]

The variable $system_tm contains the value 16:40
When I try to insert this value in a varchar field in a sqlite table it gives 
me ' unrecognised token ":" ' due to which the insertion process fails.

How do I get rid of this?
Probably should be some addslashes or some other stuff, but I am not quite sure.
Kindly help.

Regards,
Anirban Sarkar

Re: Re: [sqlite] Basic insert statement - syntax problem

[Anirban Sarkar]

Thanks Lawrence, once again.

Regards,
Anirban

- Original Message -
From: <[EMAIL PROTECTED]>
To: 
Sent: Thursday, February 17, 2005 3:46 PM
Subject: Re: Re: [sqlite] Basic insert statement - syntax problem


>
> >
> > From: "Anirban Sarkar" <[EMAIL PROTECTED]>
> > Date: 2005/02/17 Thu AM 09:45:03 GMT
> > To: 
> > Subject: Re: [sqlite] Basic insert statement - syntax problem
> >
> > #Inserting data into mri_output table
> >   set connection_no { }
> >   set insert_normal "insert into mri_output (pwd,ac_my,con_no) values
('$pass','$ac_mth$ac_yr','$sel_con')"
> >   db1 eval insert_normal {} {
> >
> >   }
>
> Unless you mistyped agains, then the las line above should be
>
> db1 eval $insert_normal {} {
> .^ missing in original
>
> Lawrence
>
>
> -
> Email sent from www.ntlworld.com
> virus-checked by McAfee
> visit www.ntlworld.com/security for more information
>
>

Re: [sqlite] Basic insert statement - syntax problem

[Asko Kauppi]

Still, shouldn't the 'mri_output' (table name) be quoted?
17.2.2005 kello 11:45, Anirban Sarkar kirjoitti:
 I am really very sorry.
I mistyped the entire sql statement.
This is what my actual code looks like:
sqlite db1 "cesc_simputer.db"
#Inserting data into mri_output table
  set connection_no { }
  set insert_normal "insert into mri_output (pwd,ac_my,con_no) values 
('$pass','$ac_mth$ac_yr','$sel_con')"
  db1 eval insert_normal {} {

  }
Please note that the problem is with the syntax of the sql statement.
Regards,
Anirban Sarkar
- Original Message -
From: "Asko Kauppi" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, February 17, 2005 2:46 PM
Subject: Re: [sqlite] Basic insert statement - syntax problem

The error messages provided by SQLite seem to be very helpful, can you
use them?
like: if (rc) glua_errorN( "sqlite3 error %d: %s", rc,
sqlite3_errmsg(db) );
Aside that, shouldn't the table name (mri) have quotes ('mri')?
What's the $con_no doing without quotes as well.  Use ?1 for binding
stuff if that's what you need.
-ak
17.2.2005 kello 11:04, Anirban Sarkar kirjoitti:
  Hi all,
I am having syntax problem with a basic sqlite insert statement. I
know it's very simple but just could'nt make out where I am going
wrong.
set insert_details "insert into mri (pwd,con_no) values
('$pwd',$con_no)"
Where am I going wrong?
Thanks and regards,
Anirban Sarkar

Re: Re: [sqlite] Basic insert statement - syntax problem

[lawrence.chitty]

> 
> From: "Anirban Sarkar" <[EMAIL PROTECTED]>
> Date: 2005/02/17 Thu AM 09:45:03 GMT
> To: 
> Subject: Re: [sqlite] Basic insert statement - syntax problem
> 
> #Inserting data into mri_output table
>   set connection_no { }
>   set insert_normal "insert into mri_output (pwd,ac_my,con_no) values 
> ('$pass','$ac_mth$ac_yr','$sel_con')"
>   db1 eval insert_normal {} {
>   
>   }

Unless you mistyped agains, then the las line above should be

db1 eval $insert_normal {} {
.^ missing in original

Lawrence


-
Email sent from www.ntlworld.com
virus-checked by McAfee
visit www.ntlworld.com/security for more information
 

Re: [sqlite] Basic insert statement - syntax problem

[Anirban Sarkar]

I am really very sorry.
I mistyped the entire sql statement.
This is what my actual code looks like:

sqlite db1 "cesc_simputer.db"

#Inserting data into mri_output table
  set connection_no { }
  set insert_normal "insert into mri_output (pwd,ac_my,con_no) values 
('$pass','$ac_mth$ac_yr','$sel_con')"
  db1 eval insert_normal {} {
  
  }

Please note that the problem is with the syntax of the sql statement.

Regards,
Anirban Sarkar

- Original Message - 
From: "Asko Kauppi" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, February 17, 2005 2:46 PM
Subject: Re: [sqlite] Basic insert statement - syntax problem


> 
> The error messages provided by SQLite seem to be very helpful, can you 
> use them?
> 
> like: if (rc) glua_errorN( "sqlite3 error %d: %s", rc, 
> sqlite3_errmsg(db) );
> 
> Aside that, shouldn't the table name (mri) have quotes ('mri')?
> What's the $con_no doing without quotes as well.  Use ?1 for binding 
> stuff if that's what you need.
> 
> -ak
> 
> 
> 17.2.2005 kello 11:04, Anirban Sarkar kirjoitti:
> 
>   Hi all,
> >
> > I am having syntax problem with a basic sqlite insert statement. I 
> > know it's very simple but just could'nt make out where I am going 
> > wrong.
> >
> > set insert_details "insert into mri (pwd,con_no) values 
> > ('$pwd',$con_no)"
> > Where am I going wrong?
> >
> > Thanks and regards,
> > Anirban Sarkar
> 

Re: [sqlite] Re: AUTOINCREMENT Functionality by default required in 3.0.8 (Is it possible?)

[morten bjoernsvik]

Hi

You can easily fix this trick with the "insert null to
and integer primary key" trick or by using a trigger.

Use a reference value you either increase or decrease,
or count to set for new values.


% cat /data/temp/test.sql
.header on

create table test (
num integer,
num2integer,
infotext,
primary key (num)
);

create trigger total_num_for_test after insert on test
begin
update test set num2 = (
select count(*) from test
) where num=new.num;
end;

insert into test (num,info) values (null,'number
one');
insert into test (num,info) values (null,'number
two');
insert into test (num,info) values (null,'number
three');

select num,info,num2 from test;

% sqlite3 test.db < /data/temp/test.sql
num|info|num2
1|number one|1
2|number two|2
3|number three|3

%

--
MortenB


 --- [EMAIL PROTECTED] skrev: 
> Hi,
> 
> A soft reminder. Please inform if the below
> mentioned request is a 
> possibility?
> 
> Regards,
> Sankara Narayanan.
> 

[sqlite] Cannot compile using CygWin

[Dennis Volodomanov]

Hello all,
 
I cannot compile SQLite 3 anymore (I used to before) using CygWin. I update 
files daily from CVS, but when I try to run autoconf I get the following:
 
configure.ac:357: warning: AC_CANONICAL_HOST invoked multiple 
timesautoconf/specific.m4:363: AC_CYGWIN is expanded 
from...configure.ac:357: the top level
 
Any ideas what could be wrong?
 
Thank you in advance,
   Dennis
 
// MCP, MCSD// ASP Developer Member// Software for animal 
shelters!// www.smartpethealth.com// www.amazingfiles.com
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 14-Feb-05

Re: [sqlite] Basic insert statement - syntax problem

[Asko Kauppi]

The error messages provided by SQLite seem to be very helpful, can you 
use them?

like: if (rc) glua_errorN( "sqlite3 error %d: %s", rc, 
sqlite3_errmsg(db) );

Aside that, shouldn't the table name (mri) have quotes ('mri')?
What's the $con_no doing without quotes as well.  Use ?1 for binding 
stuff if that's what you need.

-ak
17.2.2005 kello 11:04, Anirban Sarkar kirjoitti:
 Hi all,
I am having syntax problem with a basic sqlite insert statement. I 
know it's very simple but just could'nt make out where I am going 
wrong.

set insert_details "insert into mri (pwd,con_no) values 
('$pwd',$con_no)"
Where am I going wrong?

Thanks and regards,
Anirban Sarkar

Re: [sqlite] Versions 2.8.16 and 3.1.2

[Jolan Luff]

On Tue, Feb 15, 2005 at 08:23:59AM -0500, D. Richard Hipp wrote:
> Version 3.1.2 is also (by coincidence) the first stable release
> of the 3.1 line.  SQLite 3.1 adds support for correlated subqueries,
> autovacuum, autoincrement, ALTER TABLE, and other features.
> 
> Please let me know if you find any problems with either release.

with 2.8.16 and 3.0.8, i had to set the following in the environment in
order for the tcl headers and library to be properly found:

config_TARGET_TCL_LIBS="-L${LOCALBASE}/lib -ltcl84 -lm"
config_TARGET_TCL_INC="-I${LOCALBASE}/include/tcl8.4"

(where ${LOCALBASE} is set to /usr/local)

now with 3.1.2 i use --with-tcl=${LOCALBASE}/lib/tcl8.4 as an
argument to configure and sqlite links to tcl properly, but it cant find
the headers.  somehow this gets set:

TCL_INCLUDE_SPEC='-I/usr/local/include'

but i couldn't figure out where or how to override it.  since configure
doesn't respect CPPFLAGS i've had to resort to appending
-I${LOCALBASE}/include/tcl8.4 to CFLAGS.  the above no longer works and
appending this to CFLAGS is rather hacky.  i'd be more than happy to
provide you with any information you need in order to get this working
in a sane way.

- jolan

Re: [sqlite] Re: AUTOINCREMENT Functionality by default required in 3.0.8 (Is it possible?)

[Asko Kauppi]

Didn't SQLite provide commercial support somewhere..?
I'm sure this is not an open source issue, since no-one dealing there 
would "not have time to upgrade".  Funny, I'm sad for your timetables.

-ak
17.2.2005 kello 10:02, [EMAIL PROTECTED] kirjoitti:
 Hi,
A soft reminder. Please inform if the below mentioned request is a
possibility?
Regards,
Sankara Narayanan.

Sankara Narayanan/BTC/PIN/PHILIPS
02/14/2005 05:40 PM
To
SQLITE
cc
Subject
AUTOINCREMENT Functionality by default required in 3.0.8


Hi,
I am currently using Sqlite3.0.8 in our application. We have a 
functional
requirement wherein we need for our tables to have unique Primary key
value irrespective of deletions/additions.

To be in detail, even if i delete the last row in a table, the 
subsequent
addition should not reuse the integer primary key of the last deleted 
row.
I find that this functionality is available in Sqlite 3.1 by defining
AUTOINCREMENT for the primary key column. But porting to 3.1.1 is a
tedious task the sqlite database engine is ported onto an ARM 7 
platform
and I dont have time to perform port to 3.1.

I request details of what code needs to be modified so that I get this
AUTOINCREMENT functionality implementation as a default functionality 
in
3.0.8. i.e. for all tables in my database running on the modified 
Sqlite
engine, I require that the integer primary key is never reused.

Please help in this regard.
Thanks in advance,
With regards,
Sankara Narayanan Balasubramanian.

[sqlite] Basic insert statement - syntax problem

[Anirban Sarkar]

Hi all,

I am having syntax problem with a basic sqlite insert statement. I know it's 
very simple but just could'nt make out where I am going wrong.

set insert_details "insert into mri (pwd,con_no) values ('$pwd',$con_no)"
Where am I going wrong?

Thanks and regards,
Anirban Sarkar