[sqlite] How do I attach a database from C++?

[Greg Stark]

I'm having difficulty attaching a database from C++.

Within my code, I'm using an sqlite3_prepare call:

prepareResult = pSqlBundle->sqlite3_prepare (db_, "ATTACH  
DATABASE 'window_5.sdb' AS W5;", -1, &attachSqlStmt, &tailOfSqlStmt);


but it returns an error (i.e., prepareResult comes back as  
SQLITE_ERROR).


Just to make sure that there were no problems with the databases or  
my syntax, I tried the attach command (i.e., ATTACH DATABASE  
'window_5.sdb' AS W5;) from the command line, and everything worked  
fine.  I tried using the full path name for the database-filename,  
but that didn't work either.


I'm sure it's something simple, but what I'm I doing wrong?

Thanks,
Greg

Re: [sqlite] Possible bug regarding endiannes and realstorageclass (sqlite3)

[D. Richard Hipp]

On Thu, 2005-08-18 at 23:08 +0200, Frank van Vugt wrote:
> > Right. So for your ARM FP library, the code goes from 
> 
> Yep, will try that patch tomorrow morning, the soft-float cross-compiler 
> should be ready then as well, so we'll see if that makes any difference as 
> well.

The code shown was for reading the database.  You'll also need
to find and fix the spot where the database is written, of course.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] modifying insert/updat data in triggers (was: manipulating new.? in triggers)

[Kurt Welgehausen]

> create trigger r1 after insert on foo begin
>   update foo set date_create=current_timestamp where rowid=new.rowid;
> end;
> create trigger r2 after update on foo begin
>   update foo set date_lch=current_timestamp where rowid=new.rowid;
> end;
>
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>
>

create trigger r2 after update on foo begin
  update foo set date_lch=current_timestamp where rowid=new.rowid;
  update foo set date_create=old.date_create where rowid=new.rowid;
 -- so the user can't change it
end;

I believe that's what he's after.

Regards

Re: [sqlite] Possible bug regarding endiannes and realstorageclass (sqlite3)

[Frank van Vugt]

> Right. So for your ARM FP library, the code goes from 

Yep, will try that patch tomorrow morning, the soft-float cross-compiler 
should be ready then as well, so we'll see if that makes any difference as 
well.





-- 
Best,




Frank.

Re: [sqlite] modifying insert/updat data in triggers

[Derrell . Lipman]

Mark de Vries <[EMAIL PROTECTED]> writes:

> But I still don't know how to do what I want to do. Perhaps I need to
> explain with a litle more detail what I mean. Consider the following
> table.
>
> create table foo (
>   value TEXT,
>   date_create TEXT,
>   date_lch TEXT
> );
>
> Now, whenever I insert into this table I want to set date_create to
> CURRENT_TIMESTAMP.
>
> Whenever I update a row I want date_lch (lch=last change) to be set to
> CURRENT_TIMESTAMP. (Changes to the date_create col should be (silently)
> 'ignored'.

I think this is what you're looking for.

CREATE TRIGGER foo_insert_tr
  AFTER INSERT
  ON foo
  BEGIN
UPDATE foo
  SET date_create = CURRENT_TIMESTAMP
  WHERE ROWID = new.rowid;
  END;

CREATE TRIGGER foo_update_tr
  AFTER UPDATE
  ON foo
  BEGIN
UPDATE foo
  SET date_lch = CURRENT_TIMESTAMP
  WHERE ROWID = new.rowid;
  END;

Derrell

Re: [sqlite] modifying insert/updat data in triggers (was: manipulating new.? in triggers)

[D. Richard Hipp]

On Thu, 2005-08-18 at 22:26 +0200, Mark de Vries wrote:
> create table foo (
>   value TEXT,
>   date_create TEXT,
>   date_lch TEXT
> );
> 
> Now, whenever I insert into this table I want to set date_create to
> CURRENT_TIMESTAMP.
> 
> Whenever I update a row I want date_lch (lch=last change) to be set to
> CURRENT_TIMESTAMP. (Changes to the date_create col should be (silently)
> 'ignored'.
> 

create trigger r1 after insert on foo begin
  update foo set date_create=current_timestamp where rowid=new.rowid;
end;
create trigger r2 after update on foo begin
  update foo set date_lch=current_timestamp where rowid=new.rowid;
end;

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] modifying insert/updat data in triggers

[Puneet Kishor]

Puneet Kishor wrote:

I am jumping in the middle here, but...

Mark de Vries wrote:


On Thu, 18 Aug 2005, Kurt Welgehausen wrote:



Is it possible to change the values of certain rows that
are inserted into the database? ...



I think everything you need is explained at

   .




Yeah, I've read it.



If you don't understand how to get the current date in
SQLite, look at the wiki page.




This too.

But I still don't know how to do what I want to do. Perhaps I need to
explain with a litle more detail what I mean. Consider the following
table.

create table foo (
  value TEXT,
  date_create TEXT,
  date_lch TEXT
);

Now, whenever I insert into this table I want to set date_create to
CURRENT_TIMESTAMP.




Set DEFAULT CURRENT_TIMESTAMP above for date_lch

  

/date_lch/date_create/

You know what I mean ;-)





CREATE TABLE foo (
  value TEXT,
  date_create DATETIME DEFAULT CURRENT_TIMESTAMP,
  date_lch DATETIME
);



Whenever I update a row I want date_lch (lch=last change) to be set to
CURRENT_TIMESTAMP. (Changes to the date_create col should be (silently)
'ignored'.



Don't include the date_create column in the UPDATE statement.

UPDATE foo
SET value = 'whatever', date_lch = 'current datetime value'
WHERE your constraint

What's the problem?

Re[2]: [sqlite] Possible bug regarding endiannes and realstorageclass (sqlite3)

[Doug Currie]

Thursday, August 18, 2005, 3:18:56 PM, Frank wrote:

> I repeated the test using the value 1.2345678 in order to be
> able to identify the position of each byte:

> linux i386:

> 1bde8342cac0f33f
> 0100

> linux arm:

> cac0f33f1bde8342
> 0100

> So, it indeed looks like 32bits based middle-endian or something

Right. So for your ARM FP library, the code goes from

case 6:   /* 8-byte signed integer */
case 7: { /* IEEE floating point */
  u64 x = (buf[0]<<24) | (buf[1]<<16) | (buf[2]<<8) | buf[3];
  u32 y = (buf[4]<<24) | (buf[5]<<16) | (buf[6]<<8) | buf[7];
  x = (x<<32) | y;
  if( serial_type==6 ){
pMem->i = *(i64*)&x;
pMem->flags = MEM_Int;
  }else{
pMem->r = *(double*)&x;
pMem->flags = MEM_Real;
  }

to

case 6:   /* 8-byte signed integer */
  u64 x = (buf[0]<<24) | (buf[1]<<16) | (buf[2]<<8) | buf[3];
  u32 y = (buf[4]<<24) | (buf[5]<<16) | (buf[6]<<8) | buf[7];
  x = (x<<32) | y;
  pMem->i = *(i64*)&x;
  pMem->flags = MEM_Int;
  break;
case 7: { /* IEEE floating point */
  u64 x = (buf[0]<<24) | (buf[1]<<16) | (buf[2]<<8) | buf[3];
  u32 y = (buf[4]<<24) | (buf[5]<<16) | (buf[6]<<8) | buf[7];
  x = (y<<32) | x;
  pMem->r = *(double*)&x;
  pMem->flags = MEM_Real;

e

Re: [sqlite] modifying insert/updat data in triggers

[Puneet Kishor]

I am jumping in the middle here, but...

Mark de Vries wrote:

On Thu, 18 Aug 2005, Kurt Welgehausen wrote:



Is it possible to change the values of certain rows that
are inserted into the database? ...


I think everything you need is explained at

   .



Yeah, I've read it.



If you don't understand how to get the current date in
SQLite, look at the wiki page.




This too.

But I still don't know how to do what I want to do. Perhaps I need to
explain with a litle more detail what I mean. Consider the following
table.

create table foo (
  value TEXT,
  date_create TEXT,
  date_lch TEXT
);

Now, whenever I insert into this table I want to set date_create to
CURRENT_TIMESTAMP.



Set DEFAULT CURRENT_TIMESTAMP above for date_lch

CREATE TABLE foo (
  value TEXT,
  date_create DATETIME DEFAULT CURRENT_TIMESTAMP,
  date_lch DATETIME
);



Whenever I update a row I want date_lch (lch=last change) to be set to
CURRENT_TIMESTAMP. (Changes to the date_create col should be (silently)
'ignored'.


Don't include the date_create column in the UPDATE statement.

UPDATE foo
SET value = 'whatever', date_lch = 'current datetime value'
WHERE your constraint

What's the problem?

Re: [sqlite] modifying insert/updat data in triggers (was: manipulating new.? in triggers)

[Mark de Vries]

On Thu, 18 Aug 2005, Kurt Welgehausen wrote:

> > Is it possible to change the values of certain rows that
> > are inserted into the database? ...
>
> I think everything you need is explained at
>
> .

Yeah, I've read it.

> If you don't understand how to get the current date in
> SQLite, look at the wiki page.
>

This too.

But I still don't know how to do what I want to do. Perhaps I need to
explain with a litle more detail what I mean. Consider the following
table.

create table foo (
  value TEXT,
  date_create TEXT,
  date_lch TEXT
);

Now, whenever I insert into this table I want to set date_create to
CURRENT_TIMESTAMP.

Whenever I update a row I want date_lch (lch=last change) to be set to
CURRENT_TIMESTAMP. (Changes to the date_create col should be (silently)
'ignored'.

This is exactly what the oracle trigger from my first mail does. Basically
any user specified value for these columns is always ignored as the values
to be inserted are modified by the trigger before the insert happens.

Can someone tell me if someting simmilar is or isn't possible with
triggers sqlite? And if it is possible an example of how to do it?

If it isn't possible I'll just push this logic to the application level.
No real biggy. It's just that (for larger databases) I always try to push
as many 'business rules' & logic into the database. This makes the data
better protected from application level errors/bug and saves (duplicate)
code in the apps accessing the database.

Regards,
Mark

[sqlite] NULL in compound keys - Optimization

[Christian Smith]

Hi,

I have the following schema:

create table fields (
  type text,
  instance text,
  name text,
  value text,
  primary key (type,instance,name) on conflict replace
);


What I'm trying to do is read all rows of some type, that are not
associated with an instance, to form the basis of default value processing
for new instances of that type:

SELECT name, value FROM fields WHERE type = '' AND instance IS NULL;

I'd expect this to use the compound index to check for NULL instances, but
it does not. It instead opens the index, and iterates through all rows
with the given type. Thus, I'm searching all rows associated with any
instance just to find rows not associated with any instance. This is with
2.8.x, but I've just checked and 3.2.1 also shows this behaviour.

Any ideas on how I could optimize this query? For the time being, I'll
just use an empty string instead to indicate no instance, but I'd prefer a
NULL as it's more intuitive.

Christian

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

Re: [sqlite] Possible bug regarding endiannes and realstorageclass (sqlite3)

[Frank van Vugt]

I repeated the test using the value 1.2345678 in order to be able to identify 
the position of each byte:

linux i386:

1bde8342cac0f33f
0100



linux arm:

cac0f33f1bde8342
0100



So, it indeed looks like 32bits based middle-endian or something




-- 
Best,




Frank.

RE: [sqlite] Possible bug regarding endiannes and realstorageclass (sqlite3)

[Thomas Briggs]

   I can also confirm that the original test case posted works correctly
when moving the file from Linux to Sparc (Solaris) and PA-RISC (HP-UX). 

   -Tom

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, August 18, 2005 2:21 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Possible bug regarding endiannes and 
> realstorageclass (sqlite3)
> 
> On Thu, 2005-08-18 at 14:10 -0400, D. Richard Hipp wrote:
> > On Thu, 2005-08-18 at 09:40 -0700, Robert Simpson wrote:
> > > http://www.psc.edu/general/software/packages/ieee/ieee.html
> > > 
> > > The way I interpreted this site, is that the IEEE 
> standard for floating 
> > > point numbers was processor agnostic and the storage of 
> the bits went from 
> > > left to right.
> > > 
> > 
> > I wrote a test program to print out the byte values for 1.0 and 1
> > on both ix86 (Intel, Linux) and powerpc (G5, OS-X).  The results:
> > 
> > ix86:   f03f  0100
> >  powerpc:   3ff0  0001
> > 
> > This seems to validate the approach taken by SQLite, which is to
> > byteswap floating point values on ix86 machines.
> > 
> 
> As a double-check, I have confirmed that floating-point values
> written into an SQLite3 database file written on intel/linux
> are readable on max/os-x and vice versa.
> -- 
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 

Re: [sqlite] Possible bug regarding endiannes and real storageclass (sqlite3)

[Frank van Vugt]

> ARM has at least two FL formats.

Yes, I'm currently rebuilding my crosscompiler with specific soft-float 
options, but it'll take a while.

Also, it seems that apart from the endiannes of the processor, there's also 
'endiannes of peripheral wiring', i.e. the way the memory is connected to the 
processor. At the moment I have no tech data on that regarding this 
particular machine.




-- 
Best,




Frank.

Re[2]: [sqlite] Possible bug regarding endiannes and real storageclass (sqlite3)

[Doug Currie]

See http://lists.debian.org/debian-arm/2003/10/msg00030.html

ARM has at least two FL formats.

> from the ARM Architecture Reference Manual, Page C2-4:
>
> "The word order [for DP format] defined here for the VFP architecture
> differs from that of the earlier FPA floating-point architecture.  In
> the FPA architecture, the most significant word always appeared at the
> lower memory address, with the least significant word at the higher,
> regardless of the memory system endianess".

e

RE: [sqlite] Possible bug regarding endiannes and realstorageclas s (sqlite3)

[Brandon, Nicholas]

Richard,

As of interest, I've modified your code below and ran on two systems:
a) Sun Ultra-Enterprise SPARC (gcc)
b) Windows XP AMD (VS .NET 2003)

Code snippet:

union utest {
  double r;
  long long i;
  unsigned char z[8];
};

float  test_2;

int main(int argc, char **argv){
  union utest x;
  x.r = 1.0;
  printf("%02x%02x%02x%02x%02x%02x%02x%02x\n",
x.z[0], x.z[1], x.z[2], x.z[3], x.z[4], x.z[5], x.z[6], x.z[7]);
  x.i = 1;
  printf("%02x%02x%02x%02x%02x%02x%02x%02x\n",
x.z[0], x.z[1], x.z[2], x.z[3], x.z[4], x.z[5], x.z[6], x.z[7]);

  test_2 = 1.0;

  printf("%08x\n", *((unsigned int *) &test_2) );

  return 0;
}

Results:

a)

3ff0
0001
3f80
wsi012 (16) file test2
test2:  ELF 32-bit MSB executable SPARC Version 1, dynamically
linked, not stripped

b)

f03f
0100
3f80

Regards
Nick


This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.

Re: [sqlite] Possible bug regarding endiannes and real storageclass (sqlite3)

[Frank van Vugt]

Hi,

> My test code is below.  Please run this on your ARM and let
> me know what you get.

# ./test
f03f
0100


It's getting smelly.. 32 bits only?




-- 
Best,




Frank.

Re: [sqlite] Possible bug regarding endiannes and real storageclass (sqlite3)

[D. Richard Hipp]

On Thu, 2005-08-18 at 14:10 -0400, D. Richard Hipp wrote:
> On Thu, 2005-08-18 at 09:40 -0700, Robert Simpson wrote:
> > http://www.psc.edu/general/software/packages/ieee/ieee.html
> > 
> > The way I interpreted this site, is that the IEEE standard for floating 
> > point numbers was processor agnostic and the storage of the bits went from 
> > left to right.
> > 
> 
> I wrote a test program to print out the byte values for 1.0 and 1
> on both ix86 (Intel, Linux) and powerpc (G5, OS-X).  The results:
> 
> ix86:   f03f  0100
>  powerpc:   3ff0  0001
> 
> This seems to validate the approach taken by SQLite, which is to
> byteswap floating point values on ix86 machines.
> 

As a double-check, I have confirmed that floating-point values
written into an SQLite3 database file written on intel/linux
are readable on max/os-x and vice versa.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Possible bug regarding endiannes and real storageclass (sqlite3)

[D. Richard Hipp]

On Thu, 2005-08-18 at 09:40 -0700, Robert Simpson wrote:
> http://www.psc.edu/general/software/packages/ieee/ieee.html
> 
> The way I interpreted this site, is that the IEEE standard for floating 
> point numbers was processor agnostic and the storage of the bits went from 
> left to right.
> 

I wrote a test program to print out the byte values for 1.0 and 1
on both ix86 (Intel, Linux) and powerpc (G5, OS-X).  The results:

ix86:   f03f  0100
 powerpc:   3ff0  0001

This seems to validate the approach taken by SQLite, which is to
byteswap floating point values on ix86 machines.

My test code is below.  Please run this on your ARM and let me
know what you get.

union utest {
  double r;
  long long i;
  unsigned char z[8];
};

int main(int argc, char **argv){
  union utest x;
  x.r = 1.0;
  printf("%02x%02x%02x%02x%02x%02x%02x%02x\n",
x.z[0], x.z[1], x.z[2], x.z[3], x.z[4], x.z[5], x.z[6], x.z[7]);
  x.i = 1;
  printf("%02x%02x%02x%02x%02x%02x%02x%02x\n",
x.z[0], x.z[1], x.z[2], x.z[3], x.z[4], x.z[5], x.z[6], x.z[7]);
  return 0;
}

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] manipulating new.? in triggers

[Kurt Welgehausen]

> Is it possible to change the values of certain rows that
> are inserted into the database? ...

I think everything you need is explained at

.

If you don't understand how to get the current date in
SQLite, look at the wiki page.


Regards

RE: [sqlite] Possible bug regarding endiannes and real storagecla ss (sqlite3)

[Brandon, Nicholas]

>SQLite tries to store everything on disk as big-endian.  That
>means it always byte swaps on little-endian machines (basically,
>ix86) and omits byte swapping for big-endian machines (which is
>to say, everything other than ix86.)  The byte swapping happens
>for integers *and* floating-point numbers.

When I was doing research [1] a while ago into how little endian
architectures store IEEE-754 floating point numbers, I understood that they
store them exactly like big endian platforms unlike integer formats. I never
found information to suggest they were different though I've never done any
experimentation in this area.

[1] Both AMD & Intel websites publish (with a little delving) technical
documentation on their respective processor architecture.

This may give more information on the matter:
http://babbage.cs.qc.edu/courses/cs341/IEEE-754.html

Regards
Nick







This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender.
You should not copy it or use it for any purpose nor disclose or
distribute its contents to any other person.

Re: [sqlite] Possible bug regarding endiannes and real storage class (sqlite3)

[D. Richard Hipp]

On Thu, 2005-08-18 at 12:24 -0400, D. Richard Hipp wrote:
> On Thu, 2005-08-18 at 18:04 +0200, Frank van Vugt wrote:
> > L.S.
> > 
> > It looks like there's something wrong with the endiannes when using sqlite3 
> > (v3.2.2) on an ARM architecture (SA1100 nanoboard) while storing floating 
> > point data.
> > 
> 
> SQLite assumes float point values are stored in the IEEE 64-bit
> format with the same byte order as the machine integer.

I misspoke.

SQLite tries to store everything on disk as big-endian.  That
means it always byte swaps on little-endian machines (basically,
ix86) and omits byte swapping for big-endian machines (which is
to say, everything other than ix86.)  The byte swapping happens
for integers *and* floating-point numbers.

Here is the code that reads from the disk, for example.
buf[0]..buf[7] has been loaded with 8 bytes of floating point
or integer data taken from the disk.

case 6:   /* 8-byte signed integer */
case 7: { /* IEEE floating point */
  u64 x = (buf[0]<<24) | (buf[1]<<16) | (buf[2]<<8) | buf[3];
  u32 y = (buf[4]<<24) | (buf[5]<<16) | (buf[6]<<8) | buf[7];
  x = (x<<32) | y;
  if( serial_type==6 ){
pMem->i = *(i64*)&x;
pMem->flags = MEM_Int;
  }else{
pMem->r = *(double*)&x;
pMem->flags = MEM_Real;
  }

If this is incorrect, then I have a very serious problem...

-- 
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] C/C++ interpreter Ch bindings to sqlite

[Kiel W.]

> I wonder if it is possible
> that sqlite will bundle the ch bindings source code together
> for distribution. 

Peter,

I don't speak for Dr. H or any of the developers/commiters, but asking
Sqlite to package your software with your's seems a little backward. 
Their focus is on Sqlite, not your product.  There are _a lot_ of
third party extensions, add-ins, etc for Sqlite. In my mind, it
wouldn't be practical for the Sqlite devs to support all the wrappers,
etc out there.

-- 
Kiel W.
[EMAIL PROTECTED]
--
>> time is swift <<

Re: [sqlite] sqlite3_step() question

[Kiel W.]

> There's the possibility of make a function called
> sqlite3_step_at_position()?!


Is it not feasable to do this in your wrapper function?  For instance,
in the wrappers I've written an use, I return a set of "rows" and the
user can manipulate them however they wish.  In your application, this
seems to me it would be a solution..

Make the general selection (or a set of 1000 queries) and "step"
through those in memory.  When you need more, get the next set..

Will that work for you?


-- 
Kiel W.
[EMAIL PROTECTED]
--
>> time is swift <<

Re: [sqlite] Possible bug regarding endiannes and real storageclass (sqlite3)

[Robert Simpson]

 Original Message - 
From: "D. Richard Hipp" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, August 18, 2005 9:24 AM
Subject: Re: [sqlite] Possible bug regarding endiannes and real storageclass 
(sqlite3)




On Thu, 2005-08-18 at 18:04 +0200, Frank van Vugt wrote:

L.S.

It looks like there's something wrong with the endiannes when using 
sqlite3

(v3.2.2) on an ARM architecture (SA1100 nanoboard) while storing floating
point data.



SQLite assumes float point values are stored in the IEEE 64-bit
format with the same byte order as the machine integer.


http://www.psc.edu/general/software/packages/ieee/ieee.html

The way I interpreted this site, is that the IEEE standard for floating 
point numbers was processor agnostic and the storage of the bits went from 
left to right.


Robert

Re: [sqlite] blocking - busy_timeout vs database is locked(5)

[Robert Simpson]

- Original Message - 
From: "Jonathan H N Chin" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, August 18, 2005 8:00 AM
Subject: [sqlite] blocking - busy_timeout vs database is locked(5)


[snip]


but I still occasionally get failures:

   DBD::SQLite::db do failed: database is locked(5) at dbdimp.c line 
403


Am I doing something wrong?

What is the correct way to make accesses block/retry when the
database is busy?


I'm afraid you'll have to write your own internal retry mechanism.  The 
busy_timeout only works in certain areas when the database is busy.  When an 
update is in progress however, all attempts to read will return 
*immediately* with a failure message.  You'll then have to call 
sqlite3_reset() to find out what that error message is.  If its a 
SQLITE_SCHEMA you need to call sqlite3_prepare() again (don't forget to 
rebind your parameters if any), and if its a SQLITE_LOCKED then you need to 
sleep for some random amount of time and retry -- with hopefully an eventual 
timeout mechanism in place.


Robert

Re: [sqlite] Possible bug regarding endiannes and real storage class (sqlite3)

[D. Richard Hipp]

On Thu, 2005-08-18 at 18:04 +0200, Frank van Vugt wrote:
> L.S.
> 
> It looks like there's something wrong with the endiannes when using sqlite3 
> (v3.2.2) on an ARM architecture (SA1100 nanoboard) while storing floating 
> point data.
> 

SQLite assumes float point values are stored in the IEEE 64-bit
format with the same byte order as the machine integer.
-- 
D. Richard Hipp <[EMAIL PROTECTED]>

[sqlite] manipulating new.? in triggers

[Mark de Vries]

Hi,

Is it possible to change the values of certain rows that are inserted into
the database? Let say I have a col that records the date the row it was
entered. On insert I want to set it, and on update I want to make sure
it's never altered. Also I have a col that records the date it was last
changed.

I have some experiance with postresql and oracle though and in Oracle I
would do:

CREATE OR REPLACE TRIGGER foo BEFORE INSERT OR UPDATE ON bla
yada yada yada FOR EACH ROW
BEGIN
  IF INSERTING THEN :NEW.date_create:=SYSDATE();
  ELSE :NEW.date_create:=:OLD.date_create;
  END IF;
  :NEW.date_lch:=SYSDATE();
END;

Now I'm 'stuck' in sqlite without the powerfull features of full-blown
procedural languages... And I don't know what to do?!? :)

Don't get me wrong; I'm faling in love with sqlite for what it is, and not
trying to compare it to (e.g.) Oracle. But something simmilar to the above
must be possible in plain SQL also?

Regards,
Mark

[sqlite] Possible bug regarding endiannes and real storage class (sqlite3)

[Frank van Vugt]

L.S.

It looks like there's something wrong with the endiannes when using sqlite3 
(v3.2.2) on an ARM architecture (SA1100 nanoboard) while storing floating 
point data.

Databases created on i386 can basically be read and used on the ARM device and 
viceversa. However, data that is stored using the REAL storage class seems to 
be misinterpreted when it is read on the 'other' architecture.

This seems to be independent of which architecture created the database and/or 
the table. So, doing this on one architecture:

sqlite3 test.db

create table f1 (value numeric);

insert into f1 values (12.345);

and then (after transferring the database file) doing a select on f1 using the 
other architecture will yield a result like:

-1.20245408977952e+111

updating this table/row/column with some value and re-reading it will give the 
correct result, but then after switching sides the other architecture will 
return the wrong number.


Any ideas what might be causing this as far as sqlite is concerned?



-- 
Best,




Frank.

Re: [sqlite] blocking - busy_timeout vs database is locked(5)

[Jonathan H N Chin]

I should perhaps note that there are only around fifty accesses in
any given five minute interval, so it is not as if anything is being
overloaded.


-jonathan

-- 
Jonathan H N Chin, 2 dan | deputy computer | Newton Institute, Cambridge, UK
<[EMAIL PROTECTED]> | systems mangler | tel/fax: +44 1223 767091/330508

"respondeo etsi mutabor" --Rosenstock-Huessy

[sqlite] blocking - busy_timeout vs database is locked(5)

[Jonathan H N Chin]

With debian packages:
sqlite3 3.2.1-1  
libsqlite3-03.2.1-1
libdbd-sqlite3-perl 1.08-1 (with looks_like_number test elided)

I have an sqlite3 database that is accessed by a perl cgi script.
I want accesses to block if the database is in use by another process,
and not to fail with an error.
The cgi code looks like:

my $attr = { RaiseError => 1, AutoCommit => 1 };
my $cdbh = DBI->connect( "dbi:SQLite:dbname=$CREDB", "", "", $attr);
$cdbh->func( 360, 'busy_timeout' );
$cdbh->do( q[DELETE FROM m2u WHERE user = ?;], undef, $user);

but I still occasionally get failures:

DBD::SQLite::db do failed: database is locked(5) at dbdimp.c line 403

Am I doing something wrong?

What is the correct way to make accesses block/retry when the
database is busy?


-jonathan

-- 
Jonathan H N Chin, 2 dan | deputy computer | Newton Institute, Cambridge, UK
<[EMAIL PROTECTED]> | systems mangler | tel/fax: +44 1223 767091/330508

"respondeo etsi mutabor" --Rosenstock-Huessy

[sqlite] PBSDBMS, sqlite database tool

[Edwin Knoppert]

On my site http://www.hellobasic.com/ (free downloads) you can find PBSDBMS,
a DBMS for sqlite v3.07 and up.

Does handle the main aspects for sqlite like tables, columns, views and a
few others..
Also does import and export.
Blob data having nul chars is supported.
An extensive querybuilder.
Zero installation, just unzip and run.

Yes, intention is to ever smoothen the gui more, i know..

Hope you like it..

Thanks!