Re: [sqlite] Temp table in which dir?

[Martin Engelschalk]

Hi,

there is a pragma temp_store_directory which defines this. See 
http://www.sqlite.org/pragma.html#pragma_temp_store_directory


Martin

R S schrieb:


Hi,
I programatically create a Temp table for my DB. Does the Temp table reside
in the same directory as my Database or in another directory (/var/tmp I
suppose).
Thanks?

 

[sqlite] Re: iso8859-9 encoding problem

[Igor Tandetnik]

Veysel Harun Sahin <[EMAIL PROTECTED]>
wrote:

I have already started using sqlite. The version which i use is
3.2.7. I have no problems except sorting. When i query a table using
the "order by" clause i can not get true sorting according to
iso8859-9 charset. The special characters of my encoding are seen at
the end of the list. What can i do to overcome this problem?


You define a custom collation function with sqlite3_create_collation[16] 
or sqlite3_collation_needed[16], then specify it with a COLLATE clause 
in your query.


Igor Tandetnik 

Re: [sqlite] Concat?

[Alfredo Cole]

El Martes, 25 de Octubre de 2005 17:08, Puneet Kishor escribió:
 > On Oct 25, 2005, at 6:08 PM, Alfredo Cole wrote:
 > > What is the equivalent of:
 > >
 > > select concat(col1,col2,col3) from table
 > >
 > > in SQlite?
 >
 > SELECT col1 || col2 || col3 FROM table
 >
 > should do, no?
 >
 > --
 > Puneet Kishor

Perfect!! Thank you.

-- 
Alfredo J. Cole
Grupo ACyC

Re: [sqlite] .import null values

[Kurt Welgehausen]

> Affinity Modes ... how do you activate these?

I don't believe strict affinity has been implemented;
someone correct me if that's not right.

Regards

Re: [sqlite] Is this query optimized?

[Kurt Welgehausen]

count() has always done a full table scan. As far as I know,
nothing has been done to optimize it, as your observations
seem to confirm.

Regards

Re: [sqlite] Concat?

[Puneet Kishor]

On Oct 25, 2005, at 6:08 PM, Alfredo Cole wrote:


What is the equivalent of:

select concat(col1,col2,col3) from table

in SQlite?


SELECT col1 || col2 || col3 FROM table

should do, no?

--
Puneet Kishor

[sqlite] Concat?

[Alfredo Cole]

Hi:

What is the equivalent of:

select concat(col1,col2,col3) from table

in SQlite? How can I achieve the same results?

Thank you.

-- 
Alfredo J. Cole
Grupo ACyC

Re: [sqlite] Optimal page size

[Chris Schirlinger]

> For what it is worth I did some trials in matching page size to the 
> underlying virtual memory page size and was surprised to find that I did 
> not measure any significant performance change.  My guess is that it is 
> the structure of the data in your application which would be sensitive 
> to page size.
> JS

I will concur with this :)

Our tests were done by three different people (Who all wrote their 
own test programs and had their own ideas about what would be the 
best way to test this) 

We discovered it made no difference at all in one case, and a small, 
yet noticeable difference in the best case.

Someone already replied here saying a 15% speed increase is probably 
the best case. I agree that's probably the best result you'll get, 
but add a rider that you are unlikely to get that much. 

You will probably get better savings fine tuning your code and 
algorithms 

Re: [sqlite] .import null values

[David Finlayson]

It sounds like you loose some of the economy of sqlite by needing a
more sophisticated front-end coded up to drive it (for example, to do
type checking)...

At any rate, my needs aren't too complicated. I was looking for a
portable replacement for MS Access to hold my research results (long
time series of measurements) that was easier to script. Sqlite will
do, I just need to be careful about gotchas like this.

By the way, the affinity modes I was referring to in Section 6 appear
to be different than the "normal" mode discussed in Section 2. They
are either more restrictive (Strict affinity mode), or less
restrictive (No affinity mode). Maybe these have not been implemented
yet?

Thanks for your help.

David



On 10/25/05, Dennis Cote <[EMAIL PROTECTED]> wrote:
> David Finlayson wrote:
>
> >Thanks for the link . In section
> >6 of this page there is mention of Affinity Modes, how do you activate
> >these?
> >
> >
> See section 2.1 of that page.
>
> >As it is now you can get completely irrational behavior with
> >mathematical operators:
> >
> >sqlite> create table temp (a INTEGER);
> >sqlite> insert into temp values("1");
> >sqlite> insert into temp values("1.9");
> >sqlite> insert into temp values("2hello");
> >sqlite> insert into temp values("hello2");
> >sqlite> select * from temp;
> >a
> >--
> >1
> >1.9
> >2hello
> >hello2
> >
> >sqlite> select sum(a) from temp;
> >sum(a)
> >--
> >4.9
> >
> >sqlite> select sum(a) from temp where a = "2hello";
> >sum(a)
> >--
> >2
> >
> >sqlite> select sum(a) from temp where a = "hello2";
> >sum(a)
> >--
> >0
> >
> >That is just irrational. str("2hello") should always return an error
> >or null or maybe even  0, but surely not 2. Is this some kind of
> >casting behavior inherited from C?
> >
> >
> Sort of. The text values are converted from strings to integers using
> atoi() which stops on the first illegal character.
>
> This usually isn't a problem in practice. Its is also kind of
> "irrational" to have both string and numeric data in the same column and
> expect to get a valid sum.
>
> If all the values are actually string representations of actual numeric
> data, the conversions will succeed and the sum will be correct. This
> behavior is a holdover from earlier versions of sqlite which were
> completely typeless (all data was stored as text strings).
>
> >Also, significant white space between delimiters? ",0.9," is numeric
> >", 0.9," is text. That isn't very friendly.
> >
> >
> I agree, but this is only a problem with the .import meta command and
> not something inherent in sqlite. You can always write your own program
> to read the data the way you want (ignoring whitespace) and call the
> correct sqlite API functions to bind that data with the correct type
> when it is inserted into sqlite.
>
>
>


--
David Finlayson
Marine Geology & Geophysics
School of Oceanography
Box 357940
University of Washington
Seattle, WA  98195-7940
USA

Office: Marine Sciences Building, Room 112
Phone: (206) 616-9407
Web: http://students.washington.edu/dfinlays

Re: [sqlite] Convert SQLite to Java

[Randy Ray]

>>Java libraries linked with C (via JNI) generally have to run with the exact
>>same version of Java they were built with.
> 
> Uh-oh, Sun advertises something else.

Hmm... I may have been confused in the matter by having had (unrelated)
problems. I would go with what Sun advertises.

Randy
-- 
[EMAIL PROTECTED]  http://www.rjray.org http://www.svsm.org

We will never have true civilization until we have learned to recognize the
rights of others.-- Will Rogers

[sqlite] iso8859-9 encoding problem

[Veysel Harun Sahin]

Hi,

I have already started using sqlite. The version which i use is 3.2.7. I
have no problems except sorting. When i query a table using the "order by"
clause i can not get true sorting according to iso8859-9 charset. The
special characters of my encoding are seen at the end of the list. What can
i do to overcome this problem?

Thanks in advance.

--
Veysel Harun Sahin

[sqlite] iso8859-9 encoding problem

[Veysel Harun Sahin]

Hi,

I have already started using sqlite. The version which i use is 3.2.7. I
have no problems except sorting. When i query a table using the "order by

--
Veysel Harun Sahin

Re: [sqlite] .import null values

[Dennis Cote]

David Finlayson wrote:


Thanks for the link . In section
6 of this page there is mention of Affinity Modes, how do you activate
these?
 


See section 2.1 of that page.


As it is now you can get completely irrational behavior with
mathematical operators:

sqlite> create table temp (a INTEGER);
sqlite> insert into temp values("1");
sqlite> insert into temp values("1.9");
sqlite> insert into temp values("2hello");
sqlite> insert into temp values("hello2");
sqlite> select * from temp;
a
--
1
1.9
2hello
hello2

sqlite> select sum(a) from temp;
sum(a)
--
4.9

sqlite> select sum(a) from temp where a = "2hello";
sum(a)
--
2

sqlite> select sum(a) from temp where a = "hello2";
sum(a)
--
0

That is just irrational. str("2hello") should always return an error
or null or maybe even  0, but surely not 2. Is this some kind of
casting behavior inherited from C?
 

Sort of. The text values are converted from strings to integers using 
atoi() which stops on the first illegal character.


This usually isn't a problem in practice. Its is also kind of 
"irrational" to have both string and numeric data in the same column and 
expect to get a valid sum.


If all the values are actually string representations of actual numeric 
data, the conversions will succeed and the sum will be correct. This 
behavior is a holdover from earlier versions of sqlite which were 
completely typeless (all data was stored as text strings).



Also, significant white space between delimiters? ",0.9," is numeric
", 0.9," is text. That isn't very friendly.
 

I agree, but this is only a problem with the .import meta command and 
not something inherent in sqlite. You can always write your own program 
to read the data the way you want (ignoring whitespace) and call the 
correct sqlite API functions to bind that data with the correct type 
when it is inserted into sqlite.

[sqlite] Temp table in which dir?

[R S]

Hi,
I programatically create a Temp table for my DB. Does the Temp table reside
in the same directory as my Database or in another directory (/var/tmp I
suppose).
Thanks?

Re: [sqlite] built-in functrion suggestion: size of blob

[Joe Wilson]

The built-in Sqlite length() function works just fine on blobs:

  sqlite> select length(X'00');
  5

Why do you think it doesn't?

--- Lloyd Dupont <[EMAIL PROTECTED]> wrote:

> I look into the build in function of SQLite and saw there is a function to 
> know the length of a
> string (in a record).
> Great!
> 
> But to my disbelief there is (apparently) no way to get the size of a blob 
> (other than loading
> it :-()
> And no, length() doesn't work on Blob.
> 
> I think it would be a worthy addition!
> 
> Or is there already an (undocumented) such addition?





__ 
Yahoo! Mail - PC Magazine Editors' Choice 2005 
http://mail.yahoo.com

Re: [sqlite] Is this query optimized?

[R S]

Actually I have an index on both columnVal (a varchar) and insertTime
(bigint).


On 10/25/05, Martin Engelschalk <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> if the where clause in your subquery is not supported by an index (and i
> suspect this is the case), sqlite has to do a full table scan.
> Therefore, the execution time is you observe is to be expected.
> I think thar you can change your query to something like (i didn't test
> it,)
>
> select count(*) as totalCount
> from myTable
> where (insertionTime BETWEEN  and )
> and columnVal > '0'
> group by columnVal
> having totalCount > 10
>
> If you also create an index on insertionTime, your query should be fast.
>
> Martin
>
>
> R S schrieb:
>
> >Hi,
> >I am trying to use this query and notice that the execution time
> increasing
> >linearly as the Table size increases.
> >
> >select totalCount from (select count(*) as totalCount from myTable where
> >(insertionTime BETWEEN  and ) and columnVal > '0'
> group
> >by columnVal) where totalCount > 10;
> >
> >Diff between beginTime and endTime is always constant.
> >columnVal is a varchar.
> >aColumn is an integer.
> >
> >Thanks!
> >
> >
> >
>

Re: [sqlite] Is this query optimized?

[Martin Engelschalk]

Hi,

if the where clause in your subquery is not supported by an index (and i 
suspect this is the case), sqlite has to do a full table scan. 
Therefore, the execution time is you observe is to be expected.

I think thar you can change your query to something like (i didn't test it,)

select count(*) as totalCount 
 from myTable 
where (insertionTime BETWEEN  and ) 
  and columnVal > '0'

group by columnVal
having totalCount > 10

If you also create an index on insertionTime, your query should be fast.

Martin


R S schrieb:


Hi,
I am trying to use this query and notice that the execution time increasing
linearly as the Table size increases.

select totalCount from (select count(*) as totalCount from myTable where
(insertionTime BETWEEN  and ) and columnVal > '0' group
by columnVal) where totalCount > 10;

Diff between beginTime and endTime is always constant.
columnVal is a varchar.
aColumn is an integer.

Thanks!

 

Re: [sqlite] .import null values

[David Finlayson]

Thanks for the link . In section
6 of this page there is mention of Affinity Modes, how do you activate
these?

As it is now you can get completely irrational behavior with
mathematical operators:

sqlite> create table temp (a INTEGER);
sqlite> insert into temp values("1");
sqlite> insert into temp values("1.9");
sqlite> insert into temp values("2hello");
sqlite> insert into temp values("hello2");
sqlite> select * from temp;
a
--
1
1.9
2hello
hello2

sqlite> select sum(a) from temp;
sum(a)
--
4.9

sqlite> select sum(a) from temp where a = "2hello";
sum(a)
--
2

sqlite> select sum(a) from temp where a = "hello2";
sum(a)
--
0

That is just irrational. str("2hello") should always return an error
or null or maybe even  0, but surely not 2. Is this some kind of
casting behavior inherited from C?

Also, significant white space between delimiters? ",0.9," is numeric
", 0.9," is text. That isn't very friendly.

David


On 10/25/05, Brass Tilde <[EMAIL PROTECTED]> wrote:
> > >sqlite> create table t (a, b integer, c real);
>
> > this is a great explanation, but, why does 1,2,3 return as
> > text,integer,integer and not integer,integer,integer?
>
> I suspect that it's because no type was specified for field 'a', so
> everything is interpreted as text.
>
> Brad
>
>


--
David Finlayson
Marine Geology & Geophysics
School of Oceanography
Box 357940
University of Washington
Seattle, WA  98195-7940
USA

Office: Marine Sciences Building, Room 112
Phone: (206) 616-9407
Web: http://students.washington.edu/dfinlays

[sqlite] Is this query optimized?

[R S]

Hi,
I am trying to use this query and notice that the execution time increasing
linearly as the Table size increases.

select totalCount from (select count(*) as totalCount from myTable where
(insertionTime BETWEEN  and ) and columnVal > '0' group
by columnVal) where totalCount > 10;

Diff between beginTime and endTime is always constant.
columnVal is a varchar.
aColumn is an integer.

Thanks!

Re: [sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

[Kurt Welgehausen]

> Can anyone guide me to where "fdatasync" should be?



(maybe)


Regards

[sqlite] begin transaction and commit

[Wilfried Mestdagh]

Hello,

Do I need the 'begin transaction' and 'commit' ? It seems it works
without them also. I'm not sure I fully understeand wy I need them. If
someone can explain in simple English ?

I understeand that if I use them, and some serious error occures then
there is a rollback so that my application can know the place where is
not updated , but do I need it for a simple insert or update of 1 record
?

And yes I have read all documentation and FAQ but still have the
question...

---
Mvg, Wilfried
http://www.mestdagh.biz

Re[2]: [sqlite] getting only count of records

[Wilfried Mestdagh]

Hi Guillaume,

> if restricting in the where clause on VehicleID, then you should create
> an index on VehicleID.

I' not sure I understeand correct. Does this mean that if I uses 'where'
clauses it is not needed to make indexes and it make index itself when
needed ?

By now I have made an index on every 'search thing' I use in where
clause. Since I dont find instructions to say to the database which
index to use, I assume it uses the right ones itself. But when it
autocreate indexes... hmm should be nice thing :)

Unless of course I misinterprete :(

---
Rgds, Wilfried
http://www.mestdagh.biz

Re: [sqlite] SQLite 3.2.5 and Mac OSX network folders

[Steve Palmer]

What are those reasons and is there any expectation that they can be
made available as patches for folks who build SQLite privately? I
cannot use the libsqlite3.dylib that comes with Mac OSX 10.4 since my
application needs to run on 10.3.9 too and there is no equivalent
static version that I can find.

- Steve


On Oct 25, 2005, at 4:57am, [EMAIL PROTECTED] wrote:


Steve Palmer <[EMAIL PROTECTED]> wrote:


I'm currently investigating a problem with my application, SQLite
3.2.5 and a database located on a Mac OSX Server network share that
does not seem to repro with the SQLite 3.1.3 that ships with Mac OSX
4.1. Specifically if I place a SQLite database file on a folder on a
remote network share and attempt to access it using the sqlite3
utility, I get different results.




Apple added special hacks to their release of SQLite 3.1.3 that
allow it to work on remote filesystems with broken file locking.
For various reasons, those hacks have not been incorporated into
the SQLite core, yet.

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

Re: [sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

[rpyne]

Thanks, changing my path order and replacing awk with gawk solved the 
(first) problem. Now I am getting:

gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -
DTHREADSAFE=0 -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=1 -
I/usr/local/include/readline -o .libs/sqlite3 ./src/shell.c  
./.libs/libsqlite3.so -lreadline -lcurses -Wl,--rpath -
Wl,/usr/local/lib
./.libs/libsqlite3.so: undefined reference to `fdatasync'
collect2: ld returned 1 exit status
make: *** [sqlite3] Error 1


Can anyone guide me to where "fdatasync" should be?

Thanks.

--Richard



On 25 Oct 2005 at 7:06, Kurt Welgehausen wrote:

> This looks like a problem that has come up several
> times before on the list. If I remember correctly,
> it's usually that the awk shipped by Sun doesn't
> support the extended syntax that Sqlite expects.
> 
> Try installing gawk if it's not there already, and
> do whatever is required to use it in place of awk.
> 
> Regards
> 

Re: [sqlite] FYI: Fortran interface to SQLite

[Hamid Benhocine]

Hello,
Very interesting, I planned to do some work (not done yet) to
provide my division with some examples using sqlite with fortran 90/95
(to replace a home pivot format in  numerical computing : we use f90/95).
So, if you have a release, you want to share, I will be happy to test it
on our Unix platforms(IBM , SGI, Linux) with the Fortran 90/95 compilers
- IBM XL Fortan Compiler ( 64/32 bits Mode),
- SGI Mips Pro F90/95 Compiler (64/32 bits).,
- PGF f90/95 (Portland Group)

Regards.

Hamid

Arjen Markus wrote:


Hello,

my (almost) first experience with SQLite was the sample
code that Al Danial published to show how to use SQLite
in a FORTRAN program.

I was inspired by his work to develop a more comprehensive
interface, this time in Fortran 90/95. I am pleased to 
say that this was fairly easy to do and that it is 
nearing a stage where I dare release it in the wild.


The idea:
- Provide a library that hides as much as possible 
 the detail of Fortran-C interfacing
- Provide low-level routines for flexibility and 
 some high-level routines for common tasks and

 ease of use.

It has been tested on Windows with Compaq Visual Fortran
and MS VC/C++ only so far, but it should not be
a problem to get to work with other common 
compiler combinations.


Anyone who is interested, just drop a note.

Regards,

Arjen


 

Re: [sqlite] .import null values

[Brass Tilde]

> >sqlite> create table t (a, b integer, c real);

> this is a great explanation, but, why does 1,2,3 return as
> text,integer,integer and not integer,integer,integer?

I suspect that it's because no type was specified for field 'a', so
everything is interpreted as text.

Brad

Re: [sqlite] .import null values

[Puneet Kishor]

On Oct 25, 2005, at 10:02 AM, Dennis Cote wrote:


..

The following shows what happens when some sample data is imported 
into tables with different column types (note the spaces after the 
commas in the last three rows).


1,2,3
3.14,1.414,2.718
5+6,7-8,9*9
1, 2, 3
3.14, 1.414, 2.718
5+6, 7-8, 9*9

   SQLite version 3.2.7
   Enter ".help" for instructions
   sqlite> create table t (a, b integer, c real);
   sqlite> .separator ,
   sqlite> .import test.csv t
   sqlite> select * from t;
   1,2,3
   3.14,1.414,2.718
   5+6,7-8,9*9
   1, 2, 3
   3.14, 1.414, 2.718
   5+6, 7-8, 9*9
   sqlite> select typeof(a), typeof(b), typeof(c) from t;
   text,integer,integer
   text,real,real
   text,text,text
   text,text,text
   text,text,text
   text,text,text
   sqlite> select sum(a), sum(b), sum(c) from t;
   18,10.414,14.718



this is a great explanation, but, why does 1,2,3 return as 
text,integer,integer and not integer,integer,integer?



--
Puneet Kishor

Re: [sqlite] Optimal page size

[John Stanton]

For what it is worth I did some trials in matching page size to the 
underlying virtual memory page size and was surprised to find that I did 
not measure any significant performance change.  My guess is that it is 
the structure of the data in your application which would be sensitive 
to page size.

JS

Zibetti Paolo wrote:

I could not find a document explaining how to find the optimal value for the
"page size" parameter.
Should I set the page size to match the allocation size (cluster size) of
the file system ?
Should I set it so that each page contains exactly a given number of records
(i.e. no record is split between two pages) ?

Thank you, bye

Re: [sqlite] Convert SQLite to Java

[Tom Poindexter]

On Tue, Oct 25, 2005 at 10:05:31AM -0500, John Stanton wrote:
> I've been worn down by my quest for a comparable product to SQLite
> written in pure Java and have come to the conclusion that it doesn't
> exit.  I've looked at Derby, QED, Axion, blah blah and I'm not too
> impressed by any of them.
> None of them are as good as SQLite and they all use multiple files for
> their storage.
> 
> Sqlite is portable to pretty much anything more advanced than an abacus, 
> so I don't see your problem.  If there were gcc for abacus it would work 
> on that too :-).


This company has a C to Java Bytecode compiler.  E.g., compile C 
programs to run on a Java JVM.   

http://www.axiomsol.com/

I have no idea how this actually works in practice, but any 
SQLite fan with $49 to burn and some time might want to give it 
a try.

-- 
Tom Poindexter
[EMAIL PROTECTED]
http://www.nyx.net/~tpoindex/

Re: [sqlite] Convert SQLite to Java

[John Stanton]

Fred injects a big dose of common sense, and a principle which goes back 
almost a thousand years - Occam's Razor also known as K.I.S.S.


How often do we see people laboring over a high level "simple" solution 
to a low level problem and in exasperation jump in and solve it in a 
very short time with a very few lines of C or Assembler?  It is our 
experience that computer languages are just a tool, not a culture or 
religion.  Use the lightest one which cuts deepest and fastest for the 
particular application.  An adept programmer can handle any language and 
learn a new one in no time flat.


I find Sqlite a particularly well thought through product.  It is simple 
in important ways, but not so simple that it cannot handle serious 
applications.  Like other successful systems it is "a coherent system 
that pushes a few good ideas to the limit".  A lesson in simplicity well 
worth pondering.

JS

Fred Williams wrote:

Well I kind'a hope you are not a screaming Java bigot, but there are
serious reasons particular programming languages are chosen for
particular projects.  And if the language choice is done properly,
logically, and realistically it will definitely not be the only language
the programmer happens to know.  Harkens back to the very old joke, "He
can write Fortran in any language!"

If my world was perfect we would all still be writing ASSEMBLER...  But,
I use the poor substitute "C" when performance, module size, and
portability are important.  When I feel real lazy or the client starts
screaming about the overtime, I use a "third generation" language
(Object Oriented.)  If I particularly dislike the client and want to get
rid of him as fast as I can, I use an interpreted language (no compile
time).  And as I slip away he is on the phone ordering bigger and faster
hardware.

Fred

Re: [sqlite] Convert SQLite to Java

[Jay Sprenkle]

On 10/25/05, Clay Dowling <[EMAIL PROTECTED]> wrote:
> No matter if JVM versions do or don't have to be matched, using a native
> DLL is clearly not a good solution for the original poster.  It does
> completely defeat the compile once run anywhere goal of Java.  Presumably
> if he was willing to accept platform lock-in he would just write the app
> in C++ and be done with it.

If he knew C++, and I've found C++ to be as portable as anything else
I've tried.
It's not the language that makes things unportable, it's the environment.

Re: [sqlite] C++ wrapper for SQLite C API

[Alfredo Cole]

El Martes, 25 de Octubre de 2005 08:55, Martin Engelschalk escribió:
 > Hello Alfredo,
 >
 > i used a Wrapper from CodeProject by Rob Groves:
 >  http://www.codeproject.com/database/CppSQLite.asp
 > I changed the code a bit (mostly deleted stuff i do not need), and i am
 > not sure if all features of SQLite are wrapped, but the code is easy to
 > understand and can easily be expanded.
 >
 > Martin

Thank you Martin. I will take a look at it.

Regards.

-- 
Alfredo J. Cole
Grupo ACyC

Re: [sqlite] Convert SQLite to Java

[John Stanton]

I've been worn down by my quest for a comparable product to SQLite
written in pure Java and have come to the conclusion that it doesn't
exit.  I've looked at Derby, QED, Axion, blah blah and I'm not too
impressed by any of them.
None of them are as good as SQLite and they all use multiple files for
their storage.

Sqlite is portable to pretty much anything more advanced than an abacus, 
so I don't see your problem.  If there were gcc for abacus it would work 
on that too :-).

Re: [sqlite] .import null values

[Dennis Cote]

David Finlayson wrote:


OK, I can deal with the Nulls that in SQL...now, what do you mean that
"even numeric data" is imported as text?

I loaded about 85k records into a database, all numeric, and didn't
notice any problems using max() or stdev(). Should I be concerned
about something?

 


David,

Sqlite is a lot looser with data types than other databases. It actually 
assigns a type to each field value, rather than each column. Internally 
the .import command binds the data it reads from your file to a prepared 
insert statement. It always binds this data as text. Then it relies on 
the type conversion rules based on the column's type affinity to 
actually store the data.


The following shows what happens when some sample data is imported into 
tables with different column types (note the spaces after the commas in 
the last three rows).


1,2,3
3.14,1.414,2.718
5+6,7-8,9*9
1, 2, 3
3.14, 1.414, 2.718
5+6, 7-8, 9*9

   SQLite version 3.2.7
   Enter ".help" for instructions
   sqlite> create table t (a, b integer, c real);
   sqlite> .separator ,
   sqlite> .import test.csv t
   sqlite> select * from t;
   1,2,3
   3.14,1.414,2.718
   5+6,7-8,9*9
   1, 2, 3
   3.14, 1.414, 2.718
   5+6, 7-8, 9*9
   sqlite> select typeof(a), typeof(b), typeof(c) from t;
   text,integer,integer
   text,real,real
   text,text,text
   text,text,text
   text,text,text
   text,text,text
   sqlite> select sum(a), sum(b), sum(c) from t;
   18,10.414,14.718

As you can see, only a few cases actually store the data as numeric 
values, and even then it may store an integer in a real typed column or 
a real value in an integer typed column. Also many functions, like sum() 
above, can produce unexpected results.


The mismatches between the declared and actual types of values in a 
column can also cause problems with math operation such as integer 
results from integer division between columns that are declared as real 
(but actually hold integer data), or a real (non-integer) valued sum 
from a column that is declared integer.


Another problem you may encounter occurs when comparing text and numeric 
values. A value stored as text '1.50' in an untyped column will not 
compare equal to a real value 1.5 as shown in the example below 
(continued with the same table t as above).


   sqlite> delete from t;
   sqlite> insert into t values ('1.50', '2.50', '3.50');
   sqlite> select * from t;
   1.50,2.5,3.5
   sqlite> select typeof(a), typeof(b), typeof(c) from t;
   text,real,real
   sqlite> select a=1.5, b=2.5, c=3.5 from t;
   0,1,1

These issues aren't usually a big problem, but you need to keep them in 
the back of your mind. However, if you want numeric values in your 
database you must import into columns declared with some numeric type.


HTH
Dennis Cote

Re: [sqlite] C++ wrapper for SQLite C API

[Martin Engelschalk]

Hello Alfredo,

i used a Wrapper from CodeProject by Rob Groves:
http://www.codeproject.com/database/CppSQLite.asp
I changed the code a bit (mostly deleted stuff i do not need), and i am 
not sure if all features of SQLite are wrapped, but the code is easy to 
understand and can easily be expanded.


Martin

Alfredo Cole schrieb:


Hi:

I am looking for a C++ wrapper that will support all features of SQLite. I am 
using sqlitewrapped-1.0 but does not work correctly when trying to read from 
a table and insert to another one within the same method. Also, it seems the 
author is not working on it any longer.


I would appreciate your suggestions. Thank you.

 

Re: [sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

[Christian Smith]

Inline.

On Tue, 25 Oct 2005 [EMAIL PROTECTED] wrote:

>Has anyone sucessfully built sqlite 3.2.7 on a SunOS 5.6 (Solaris
>sparc 6) box?
>
>I had no problems with sqlite 2, but can't seem to get sqlite3 to
>build.
>
>Using the ./configure ; make route, it dies with:
>
>./lemon -DSQLITE_OMIT_CURSOR   parse.y
>cat parse.h ./src/vdbe.c | awk -f ./mkopcodeh.awk >opcodes.h
> ...
>make: *** [opcodes.h] Error 2
>


Put /usr/xpg4/bin in your PATH before /usr/bin. On Solaris, the tools in
/usr/bin are from the ark, and those in /usr/xpg4/bin are the more recent
standards compliant tools. In this case, you need a nawk compatible awk.

Christian


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

Re: [sqlite] How to determine if a column is autoincremented?

[Gerry Snyder]

Thomas Briggs wrote:

   See http://www.sqlite.org/autoinc.html - INTEGER PRIMARY KEY will
autoincrement only until you delete a row from the table.



The above seems overstated.

INTEGER PRIMARY KEYs with or without the AUTOINCREMENT keyword behave 
identically unless 1) the last row is deleted or 2) a row with the 
highest possible ROWID exists or has existed in the table, and an 
insertion is made.


In case 1) the ROWID used for the new row will be one more than the 
current largest ROWID if the AUTOINCREMENT keyword is not present, and 
one more than the largest value ever used in the table if the keyword is 
present. In my mind both qualify as autoincrementing, with either being 
fine for most purposes, but the latter behavior can certainly be needed 
in some circumstances.


In case 2) an error is thrown if AUTOINCREMENT is present; if not, a 
random ROWID is used if the row with the largest possible value is still 
in the table, otherwise one more than the largest current value.


AUTOINCREMENT guarantees unique ROWID values for the life of the table; 
without it values can be reused, but will always be unique at any given 
time, and will autoincrement under normal circumstances.


Gerry
--
--
Gerry Snyder
American Iris Society Director, Symposium Chair
in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19

[sqlite] C++ wrapper for SQLite C API

[Alfredo Cole]

Hi:

I am looking for a C++ wrapper that will support all features of SQLite. I am 
using sqlitewrapped-1.0 but does not work correctly when trying to read from 
a table and insert to another one within the same method. Also, it seems the 
author is not working on it any longer.

I would appreciate your suggestions. Thank you.

-- 
Alfredo J. Cole
Grupo ACyC

RE: [sqlite] getting only count of records

[Brandon, Nicholas]

>Is this the right way to do or is there a better way ?

In addition to the comments others have made, I would suggest you use:

'select count(*) from tx where VehicleID = ' + QuotedStr(VehicleID)

'count(*)' is a special case for the count function that returns "count of
records".

In terms of performance, this will have no measurable benefit with SQLite.
However other database's can often return results substantially quicker
(particularly when there is no 'where' clause) than using 'count()'.

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] Receive error: database is full

[drh]

R S <[EMAIL PROTECTED]> wrote:
> with Return Value 13.
> I checked my partition space and its usage is just 2% (Platform is Linux
> using SQLite 3.2.2).
> Also I am using Temp tables and periodically move data into my Main Table. I
> wondered if my Temp Table is full because strace on my process gave me
> messages like:
> access("/var/tmp/sqlite_MyjXYCDJGFYkfnc-journal", F_OK) = -1 ENOENT (No such
> file or directory)
> 
> However, I have some debug statements in my code which indicate that the
> failure is during insertion directly in the Main Table (Commit phase of that
> transaction).
> Any ideas?
> Thanks!

Do you have write permission on the directory containing the
database file?  That is necessary in order to write to the
database.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Are DELETE TRIGGERS recursive or not?

[drh]

Ralf Junker <[EMAIL PROTECTED]> wrote:
> I wonder if a DELETE TRIGGER should trigger itself recursively 

Not at this time.  Though work is underway to change this.
We need recusive delete triggers in order to implement
cascadinig deletes for referential integrity.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] sqlite tuning for db writes?

[Alex Chudnovsky]

Guy Hindell wrote:


The majority of SQL commands issued are INSERTs and DELETEs, and so it
is these that I would like to optimise. Performance at the moment is
respectable, although I would prefer to see DELETEs go a bit faster. 
 

In my application I use transaction for multiple inserts and they seem 
to run faster than MySQL, I have not noticed that deletes were 
particularly slow but if they are then the best way to optimise some job 
is not to do it -- since inserts are very fast you can change logic to 
insert new data into growing table and update some rows pointing to 
current set of inserted rows, so instead of: DELETE + INSERTS, you will 
have INSERTS + UPDATE. You will need to truncate table once in a while 
or even drop/create it.


--
regards,

Alex Chudnovsky
Majestic-12: Distributed Search Engine
http://www.majestic12.co.uk

[sqlite] sqlite tuning for db writes?

[Guy Hindell]

Hi
 
I'm relatively new to sqlite and this list but I have recently used it
(v3.2.7) as an alternative to the db backend of an application which
previously used SQLServer or Access as part of a proposed port to
Unix/Linux. So far so good - the app works just as it did before with
SQLServer. However, I'm interested to know how I can tune sqlite to best
advantage for the app.
 
The database is only accessed by one thread and only to store the
current state of the application, so the database is read once at start
up and then does nothing but writes (inserts, updates and deletes) after
that. The schema is simple, essentially consisting of a table where each
row represents object instances, another table which stores the values
for each object instance, and a third which stores relationships between
objects. All tables have simply a primary key which covers between 2 and
4 columns depending on the table. There are no complex queries performed
on the database (no multi table selects, joins etc.).
 
The majority of SQL commands issued are INSERTs and DELETEs, and so it
is these that I would like to optimise. Performance at the moment is
respectable, although I would prefer to see DELETEs go a bit faster. To
minimise the number of SQL commands issued I have implemented some
batching of row DELETEs (e.g. DELETE FROM Instances WHERE Id in
(1,2,3,4,5.)). All commands are issued within transactions.
 
Any suggestions? I have read the tuning hints at www.sqlite.org
  but can't tell which of these favour reading
or writing.
 
Many thanks
guy

Re: [sqlite] Are DELETE TRIGGERS recursive or not?

[Kurt Welgehausen]

If the trigger were recursive, it would invoke
itself forever, even if it's not doing anything
because the where clause fails.

Regards

RE: [sqlite] How to determine if a column is autoincremented?

[Thomas Briggs]

   See http://www.sqlite.org/autoinc.html - INTEGER PRIMARY KEY will
autoincrement only until you delete a row from the table.

   -Tom 

> -Original Message-
> From: Mario Gutierrez [mailto:[EMAIL PROTECTED] 
> Sent: Monday, October 24, 2005 11:05 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How to determine if a column is autoincremented?
> 
> You learn something new everyday. I didn't know that was the 
> behaviour of 
> INTEGER PRIMARY KEY.   All other databases I've worked with 
> require you to 
> explicitly declare an identiy/autoincrement column. I was 
> strictly looking 
> at the metadata returned by PRAGMA. SQLite is certainly a 
> different breed of 
> cat.
> 
> Thanks for the tip. Anyway, if anyone out there uses 
> CodeSmith, I uploaded 
> the SQLite schema provider.
> 
> --
> Mario Gutierrez
> mario.l.gutierrez @ hotmail.com
> 
> 
> 
> 
> >From: Peter Bierman <[EMAIL PROTECTED]>
> >Reply-To: sqlite-users@sqlite.org
> >To: sqlite-users@sqlite.org
> >Subject: Re: [sqlite] How to determine if a column is 
> autoincremented?
> >Date: Sun, 23 Oct 2005 23:20:19 -0700
> >
> >Actually, on SQLite, 'INTEGER PRIMARY KEY' does designate a 
> special type of 
> >autoincremented column.
> >
> >The internal 64 bit rowid is used directly in that case, which is 
> >essentially 'free' storage.
> >
> >http://www.sqlite.org/faq.html#q1
> >http://www.sqlite.org/lang_createtable.html
> >http://www.sqlite.org/autoinc.html
> >
> >-pmb
> >
> >
> >At 12:19 PM -0700 10/22/05, Mario Gutierrez wrote:
> >>Thanks for the reply.
> >>
> >>This would not work as I could define a table like this
> >>
> >>CRETE TABLE my_table (
> >>  id INTEGER PRIMARY KEY,
> >>  ...
> >>)
> >>
> >>This would meet your criteria, but 'id' is not an 
> autoincremented column.
> >>
> >>--
> >>Mario Gutierrez
> >>mario.l.gutierrez @ hotmail.com
> >>
> >>
> >>
> >>>From: "David M. Cook" <[EMAIL PROTECTED]>
> >>>Reply-To: sqlite-users@sqlite.org
> >>>To: sqlite-users@sqlite.org
> >>>Subject: Re: [sqlite] How to determine if a column is 
> autoincremented?
> >>>Date: Sat, 22 Oct 2005 10:52:20 -0700
> >>>
> >>>On Fri, Oct 21, 2005 at 05:38:28PM -0700, Mario Gutierrez wrote:
> >>>
>   I'm writing a SQLite adapter for a code generation 
> tool. I'm a little
>   stumped on how you query SQLite to determine if a column is
>   autoincremented. I've tried
> >>>
> >>>An auto-increment column is defined as INTEGER PRIMARY 
> KEY, so look for
> >>>primary key columns of type 'INTEGER'.
> >>>
> >>>Dave Cook
> >>
> >>_
> >>Express yourself instantly with MSN Messenger! Download 
> today - it's FREE! 
> >>http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
> >
> 
> _
> Don't just search. Find. Check out the new MSN Search! 
> http://search.msn.click-url.com/go/onm00200636ave/direct/01/
> 
> 

Re: [sqlite] getting only count of records

[Arjen Markus]

Wilfried Mestdagh wrote:
> 
> Hi Arjen,
> 
> AM> No, they will probably not all do the same internally: the result
> AM> may be the same, but the "virtual machine" that runs the SQL statements
> AM> will very probably do different things - this depends on optimisation
> AM> and so on. But for most of us it is mainly the result that counts
> AM> (and the time it takes for that result)
> 
> But what should then be the best one in speed ?
> 

As others have replied, the version(s) where you use count() return(s)
only
one record. And that can be seen from the sQL statement all by itself.
I do not think things can be faster than that: only one record
that will actually be returned to the calling program.

Regards,

Arjen

Re: [sqlite] getting only count of records

[Guillaume MAISON]

Wilfried Mestdagh a écrit :

AM> No, they will probably not all do the same internally: the result
AM> may be the same, but the "virtual machine" that runs the SQL statements
AM> will very probably do different things - this depends on optimisation
AM> and so on. But for most of us it is mainly the result that counts 
AM> (and the time it takes for that result)


But what should then be the best one in speed ?


well, in your SQL query, if you don't mind about VehicleID, there's 
nothing to care about speed as SQLite will do a full table scan (and 
will find its way to get the fastest full table scan :) )


if restricting in the where clause on VehicleID, then you should create 
an index on VehicleID.


HTH,

--

Guillaume MAISON - [EMAIL PROTECTED]
83, Cours Victor Hugo
47000 AGEN
Tél : 05 53 87 91 48 - Fax : 05 53 68 73 50
e-mail : [EMAIL PROTECTED] - Web : http://nauteus.com

Re: [sqlite] .import null values

[Kurt Welgehausen]

> "even numeric data" is imported as text

Column types in Sqlite are not strictly enforced.
If you import data that 'look' numeric from your
text file into a column with a numeric type, the
data will be stored in a numeric format; if a
particular value cannot be converted to a numeric
format, it will be stored as text -- so you may
have '' in a numeric column where you want a null.
See  for a more
rigorous explanation.

BTW, you shouldn't have to type individual update
statements for each column. Sqlite is usable from
almost all scripting languages. For example, in
tcl if you get the column names into a variable
called columns, you can do something like

  load tclsqlite3.so
  sqlite dbcmd filename.db
  dbcmd eval {begin exclusive}
  foreach c $columns {
dbcmd eval {upate table set $c=null where $c=''}
  }
  dbcmd eval commit
  dbcmd close


Regards

Re: [sqlite] Optimal page size

[Florian Weimer]

* Chris Schirlinger:

> 4096 in Win32 machines and 1024 on nix ones (I think, I am no expert 
> on Unix style OS's)

This depends on the file system.  On Linux on x86, it's typically 4096
bytes.

According to my performance measurements, switching from page size of
1024 bytes to 4096 bytes gives a measurable performance improvement
(but nothing dramatic, for my workload).

Re: [sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

[Kurt Welgehausen]

This looks like a problem that has come up several
times before on the list. If I remember correctly,
it's usually that the awk shipped by Sun doesn't
support the extended syntax that Sqlite expects.

Try installing gawk if it's not there already, and
do whatever is required to use it in place of awk.

Regards

Re: [sqlite] SQLite 3.2.5 and Mac OSX network folders

[drh]

Steve Palmer <[EMAIL PROTECTED]> wrote:
> I'm currently investigating a problem with my application, SQLite  
> 3.2.5 and a database located on a Mac OSX Server network share that  
> does not seem to repro with the SQLite 3.1.3 that ships with Mac OSX  
> 4.1. Specifically if I place a SQLite database file on a folder on a  
> remote network share and attempt to access it using the sqlite3  
> utility, I get different results.
> 

Apple added special hacks to their release of SQLite 3.1.3 that
allow it to work on remote filesystems with broken file locking.
For various reasons, those hacks have not been incorporated into
the SQLite core, yet.

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

Re: [sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

[Arjen Markus]

[EMAIL PROTECTED] wrote:
> 
> Has anyone sucessfully built sqlite 3.2.7 on a SunOS 5.6 (Solaris
> sparc 6) box?
> 
> I had no problems with sqlite 2, but can't seem to get sqlite3 to
> build.
> 
> Using the ./configure ; make route, it dies with:
> 
> ./lemon -DSQLITE_OMIT_CURSOR   parse.y
> cat parse.h ./src/vdbe.c | awk -f ./mkopcodeh.awk >opcodes.h
> awk: syntax error near line 36
> awk: illegal statement near line 36
> awk: syntax error near line 37
> awk: illegal statement near line 37
> awk: syntax error near line 42
> awk: illegal statement near line 42
> awk: syntax error near line 103
> awk: illegal statement near line 103
> make: *** [opcodes.h] Error 2
> 
> Trying to use the generic Makefile, I get the same results.
> 

I get the very same result, apparently awk on Sun does not
like the mkopcodeh.awk script.

It does not like the sub command and it does not like:

  nopush[i] = nopush[i] + (2^j)


I tried with "nawk" instead: this works without any 
problem! 

Regards,

Arjen

Re: [sqlite] getting only count of records

[Wilfried Mestdagh]

Hi Guillaume and Martin,

Thanks for quick responce. Studying the count() function if I use the
method of Guillaume I dont need the 'where' clause:

  'select count(VehicleID) from tx'

But I assume all 3 methods will do internal exact the same. Result is
also 1 field with the count in it.

---
Mvg, Wilfried
http://www.mestdagh.biz

RE: [sqlite] Optimal page size

[Allan, Mark]

There doesn't appear to be any real documentation over what page size to use. I 
think it is more of a case of experimenting and determining which is best for 
your system/application.

In the archive I found an article stating that for optimum performance on Win32 
to match the page size with that of the disk cluster size.

We are using SQLite on an embedded system with a Flash filesystem and found 
that SQLite performed best with 8K pages and we matched the sector size in 
Flash to 8K. (Where a sector size on our flash filesystem is equivalent to the 
cluster size on a FAT disk). We were able to effectivaley select the cluster 
size for our filesystem and found SQlite worked best with the larger page size 
although it does need to match the cluster size for optimum performance.

Hope this helps.

> -Original Message-
> From: Zibetti Paolo [mailto:[EMAIL PROTECTED]
> Sent: 25 October 2005 11:40
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Optimal page size
> 
> 
> I could not find a document explaining how to find the 
> optimal value for the
> "page size" parameter.
> Should I set the page size to match the allocation size 
> (cluster size) of
> the file system ?
> Should I set it so that each page contains exactly a given 
> number of records
> (i.e. no record is split between two pages) ?
> 
> Thank you, bye
> 
> 
> 


DISCLAIMER:
This information and any attachments contained in this email message is 
intended only for the use of the individual or entity to which it is addressed 
and may contain information that is privileged, confidential, and exempt from 
disclosure under applicable law.  If the reader of this message is not the 
intended recipient, or the employee or agent responsible for delivering the 
message to the intended recipient, you are hereby notified that any 
dissemination, distribution, forwarding, or copying of this communication is 
strictly prohibited.  If you have received this communication in error, please 
notify the sender immediately by return email, and delete the original message 
immediately.

Re: [sqlite] Convert SQLite to Java

[Florian Weimer]

* Randy Ray:

> Java libraries linked with C (via JNI) generally have to run with the exact
> same version of Java they were built with.

Uh-oh, Sun advertises something else.

I can understand that you must exactly match JVM versions for
certified configurations, but this isn't true even if you don't use
native code.

Re: [sqlite] Optimal page size

[Martin Engelschalk]

Hi Paolo,

i met the same problem.
I have experimented with the page size and found that the cluster size 
of the file system brought the best results. However, the gain in speed 
was not very big (5%)


Martin

Zibetti Paolo schrieb:


I could not find a document explaining how to find the optimal value for the
"page size" parameter.
Should I set the page size to match the allocation size (cluster size) of
the file system ?
Should I set it so that each page contains exactly a given number of records
(i.e. no record is split between two pages) ?

Thank you, bye

 

[sqlite] Optimal page size

[Zibetti Paolo]

I could not find a document explaining how to find the optimal value for the
"page size" parameter.
Should I set the page size to match the allocation size (cluster size) of
the file system ?
Should I set it so that each page contains exactly a given number of records
(i.e. no record is split between two pages) ?

Thank you, bye

Re: [sqlite] getting only count of records

[Guillaume MAISON]

Wilfried Mestdagh a écrit :

Hello,

I want to have only the count of records for a particular 'where
clause', need no fields. It works using null as argument:

'select null from tx where VehicleID = ' + QuotedStr(VehicleID)

Is this the right way to do or is there a better way ?



Hi Wilfried,

a better request would be :

select count(VehicleID) as CountVehicle from tx where VehicleID = ' + 
QuotedStr(VehicleID)


then you get the field "CountVehicle" value.

HTH,

--

Guillaume MAISON - [EMAIL PROTECTED]
83, Cours Victor Hugo
47000 AGEN
Tél : 05 53 87 91 48 - Fax : 05 53 68 73 50
e-mail : [EMAIL PROTECTED] - Web : http://nauteus.com

Re: [sqlite] getting only count of records

[Martin Engelschalk]

Hello Wilfried,

your query will have as many result - Rows as meet your where-clause.
A Better way will be

select count(*) from tx where VehicleID = ' + QuotedStr(VehicleID)

This will have one result row with the number of records in the first 
(and only) column.


Martin

Wilfried Mestdagh schrieb:


Hello,

I want to have only the count of records for a particular 'where
clause', need no fields. It works using null as argument:

'select null from tx where VehicleID = ' + QuotedStr(VehicleID)

Is this the right way to do or is there a better way ?

---
Mvg, Wilfried
http://www.mestdagh.biz

 

[sqlite] getting only count of records

[Wilfried Mestdagh]

Hello,

I want to have only the count of records for a particular 'where
clause', need no fields. It works using null as argument:

'select null from tx where VehicleID = ' + QuotedStr(VehicleID)

Is this the right way to do or is there a better way ?

---
Mvg, Wilfried
http://www.mestdagh.biz

[sqlite] fragmented tables

[Wilfried Mestdagh]

Hello,

I read in FAQ that SQLite will re-use deleted record space. But what
about fragmenting? I mean suppose a record of 1 kb is deleted and a
while later a record of 2 kb is add, whill this record then append to
the file, or will it fragment partly into the free space ?

A question related: To gain the most speed I assume it is best to have
most tables continues. Can I reserve space for a table? If no then
solution is maybe to create data after creating database and delete all
records? Or does this not gain mutch for speed ?

3th and last (for the moment :) I have set page_size to 4 KB as I have
read in a wiki article. Does this gain a lot ?

---
Mvg, Wilfried
http://www.mestdagh.biz

[sqlite] FYI: Fortran interface to SQLite

[Arjen Markus]

Hello,

my (almost) first experience with SQLite was the sample
code that Al Danial published to show how to use SQLite
in a FORTRAN program.

I was inspired by his work to develop a more comprehensive
interface, this time in Fortran 90/95. I am pleased to 
say that this was fairly easy to do and that it is 
nearing a stage where I dare release it in the wild.

The idea:
- Provide a library that hides as much as possible 
  the detail of Fortran-C interfacing
- Provide low-level routines for flexibility and 
  some high-level routines for common tasks and
  ease of use.

It has been tested on Windows with Compaq Visual Fortran
and MS VC/C++ only so far, but it should not be
a problem to get to work with other common 
compiler combinations.

Anyone who is interested, just drop a note.

Regards,

Arjen

Re: [sqlite] .import null values

[David Finlayson]

On 10/24/05, Dennis Cote <[EMAIL PROTECTED]> wrote:
> The .import command in sqlite always imports data as text  (even numeric
> data into numeric columns). There is no way to import a NULL value. The
> closest you can get is an empty string or some other sentinel value,
> like the string NULL (but that would require a couple of search and
> replace operations in your source data file). Note, this will give you a
> string containing the word NULL in these columns rather than an SQL NULL
> value.

OK, I can deal with the Nulls that in SQL...now, what do you mean that
"even numeric data" is imported as text?

I loaded about 85k records into a database, all numeric, and didn't
notice any problems using max() or stdev(). Should I be concerned
about something?

It seems like the dot command extensions really ought to have a help
file somewhere!

David



>
> This can of course also be done is SQL as Kurt suggested.
>
> Or you could write your own import program that reads in the file and
> does the inserts with the correct data bound to the columns as the
> correct type (see sqlite3_bind_double() and sqlite3_bind_null()).
>
> HTH
> Dennis Cote
>


--
David Finlayson
Marine Geology & Geophysics
School of Oceanography
Box 357940
University of Washington
Seattle, WA  98195-7940
USA

Office: Marine Sciences Building, Room 112
Phone: (206) 616-9407
Web: http://students.washington.edu/dfinlays

[sqlite] sqlite on SunOS 5.6 (Solaris Sparc 6)

[rpyne]

Has anyone sucessfully built sqlite 3.2.7 on a SunOS 5.6 (Solaris 
sparc 6) box?

I had no problems with sqlite 2, but can't seem to get sqlite3 to 
build.

Using the ./configure ; make route, it dies with:

./lemon -DSQLITE_OMIT_CURSOR   parse.y
cat parse.h ./src/vdbe.c | awk -f ./mkopcodeh.awk >opcodes.h
awk: syntax error near line 36
awk: illegal statement near line 36
awk: syntax error near line 37
awk: illegal statement near line 37
awk: syntax error near line 42
awk: illegal statement near line 42
awk: syntax error near line 103
awk: illegal statement near line 103
make: *** [opcodes.h] Error 2


Trying to use the generic Makefile, I get the same results.

Thanks,

--Richard