I tested 3.7.9 on both Windows and Linux.
Both gave just "31|" as the output instead of "31|0" as you show.
And changing the select 9 made no difference...still got the same "31|"
answer for both.
I'm compiling with default options.
Michael D. Black
Senior Scientist
Advanced Anal
Natural joins are generally considered to be evil. Too many columns in common
can be bad.
If you just spell it out it works as expected
sqlite> explain query plan select * from a left join b where a.id=1 and
b.id=a.id;
0|0|0|SEARCH TABLE a USING COVERING INDEX sqlite_autoindex_a_1 (id=?) (~1
ested query in outer join
"Black, Michael (IS)"
writes:
> Natural joins are generally considered to be evil. Too many columns
> in common can be bad.
>
> If you just spell it out it works as expected
>
> sqlite> explain query plan select * from a left join b wh
You need to surround your entire sqlite3 command with back tics.
TomaCampo=`sqlite3 /Users/.and RecordTy8pe='R';"`
That's how you execute a command in bourne shell.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop
Works for me
CREATE TABLE employee(name text,editby text);
CREATE TRIGGER update_editby AFTER INSERT ON employee
BEGIN
UPDATE employee SET editby = 'unknown' WHERE editby = '' AND rowid =
new.rowid;
END;
INSERT INTO employee VALUES('Ralph','');
SELECT * FROM employee:
Ralph|u
Possible causes.
#1 You need indexes (quite possible) -- though it appears to me that all the
casting is going to kill using indexes.
#2 You need to increase cache (likely) -- try "pragam cache_size=200"
#3 You're hitting swap space (doubtful)
#4 You need to do this in your own code inst
What does strlen() tell you on the XML before you put it in the table?
Can you boil all this down to one record in a table, and some code so we can
all see what's going on?
Do a .dump of the table and post the code.
See if you can reproduce it in a simple example.
Michael D. Black
Senio
You're probably seeing disk thrashing.
Try increasing your database cache size:
.pragma cache_size XX
It's in kilobytes. Try and make it as big as your database if you can. In
other words, cache the whole thing.
Secondly, you may want to try using the FTS3/FTS4 search capability if you
I"m not a UTF expert but codepage 437 seems to work fine for your example.
codepage 65001 is not "real" UTF-8 according to several google sources.
You do have to use Lucida font.
C:\chcp 437
Active code page: 437
C:\sqlite test.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" fo
I don't know if FTS or a normal table will matter here but just normalize the
whole thing.
CREATE VIRTUAL TABLE virfts4 using fts4(id,level,value);
Your level can be CO, ST, CI, VI. Or 1,2,3,4. 1,2,3,4 would be a touch faster.
INSERT INTO virfts4 VALUES(1,'CO','country1');
INSERT INTO vi
Does this type of insert need a begin/commit around it? And would WAL mode
make any difference?
And try increasing your cache_size to at least the size of your database file.
cache_size is in pages so the default of 2000 is 2MB.
#pragma cache_size=20
That'll make it 200MB which is pro
I duplicated your problem on C++ 2005 Express and C++ 2008 Express.
C++ 2010 Express does the syntax highlighting correctly (or at least a lot
better).
Can you upgrade?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop G
l. So maybe there might be some syntax
> AN> elements in 3.7.9 (large comments or whatever) causing this
> AN> behaviour or there is really a maximum source file size that
> AN> VS2005 and 2008 syntax highlighter can support, which was reached
> between 3.7.7.1 and 3.7.9.
> AN&g
This was just answered in another chain. Visual Studio has a 64k limit on
source code files for the debugger (thanks Microsoft...a 16-bit
limit?...really!).
If you need to step into it use WinDbg
http://msdn.microsoft.com/en-us/windows/hardware/gg463009
Nobody has done a split on the sour
December 30, 2011 7:41 AM
To: General Discussion of SQLite Database
Cc: u okafor
Subject: EXT :Re: [sqlite] Fw: A question on sqlite processing
On Fri, Dec 30, 2011 at 8:17 AM, Black, Michael (IS) wrote:
>
> Nobody has done a split on the source file yet to make it < 64k.
>
S
, 2011 at 5:50 PM, Black, Michael (IS) wrote:
> So can we get a link on the download page for this?
>
There is a "Download" link on the lower menu bar. (ref:
http://www.sqlite.org/src/artifact/d9be87f1c340285a3?ln)
--
D. Richard Hipp
ation Systems
From: u okafor [uo07...@yahoo.com]
Sent: Monday, January 02, 2012 4:41 PM
To: sqlite-users@sqlite.org
Cc: Black, Michael (IS); d...@sqlite.org
Subject: EXT :Fw: [sqlite] Fw: A question on sqlite processing
Is that to say that the 'split on the source
Your question made me want to do something I've been meaning to for a while...a
custom function.
So...the following words as loadable extension (at least for the shell)
gcc -g -shared -fPIC -o sortprds.sqlext sortprds.c
sqlite3 test.db
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help
Yup...putting the main in a separate file fixed it.
Thanks.
Or is there some other init function that can be called to maintain it in one
file?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
Along with the sortprds code and database I sent out previously this now works.
gcc -g -o main main.c -DSQLITE_ENABLE_LOAD_EXTENSION sqlite3.c -lpthread -ldl
./main test.db "select id,sortprds(prdtype) from a order by sortprds(prdtype)"
ID = 2
sortprds(prdtype) = 3|10|15|27
ID =
Sure looks like cache to me. In your slow version you're walking thriugh the
entire table twice as opposed to interleaving.
You don't say how big your database is.
Increasing cache size may solve the problem.
pragma cache_size=2000 (default cache size in pages...1024 size pages means 2MB
Can't you just copy the DLL into the application directory?
That just does what the app ought to do (if they don't already).
Then you might have to turn off safe DLL mode to find the correct DLL unless
you remove the system one.
http://msdn.microsoft.com/en-us/library/windows/desktop/ms682586
Does REPLACE do what you want?
http://www.sqlite.org/lang_replace.html
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqli
What if you do a BEFORE INSERT and stick the values in a temporary table (use a
matching rowid).
Then you can just retrieve in in your AFTER trigger.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Syste
Come to thihk of it just add another field which is set to the "old" value
during the update.
Then both fields are available AFTER INSERT.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
___
I ran your test on my SSD system with 1M inserts. I used WAL mode.
pragma journal_mode=WAL;
CREATE TABLE [TestTable] (
[Id] INTEGER PRIMARY KEY AUTOINCREMENT,
[Text] TEXT
);
begin;
insert.
end;
On SSD:
time sqlite3 gen.db wrote:
>
> Fast. Fasty fast. Speed is high. INSERT, UPDATE,
My point still standsyou can test the application compatibility by copying
the DLL into the app directory and changing the search order as I recommended.
Did you try that?
The applications really need to compile sqlite in their app. That's the good
fix here as has been pointed out (and
A simple one -- and please compile sqlite3.c into your program and make
everybody happy.
Forget the shared library stuff as we have just been talking about.
CFLAGS=-O
OBJECTS=myapp.o sqlite3.o
LIBS=-lpthread -ldl
myapp: $(OBJECTS)
$(CC) -o $@ $(OBJECTS) $(LIBS)
Michael D. Black
Se
I also ran without WAL mode (about 3X slower than WAL mode)
On SSD: (system spent a LOT of time in disk wait states)
time sqlite3 gen.db wrote:
>
> Fast. Fasty fast. Speed is high. INSERT, UPDATE, and DELETE all
> significantly faster. SELECT is a bit faster, but there's less difference.
>
. Are you sure
your /tmp is actually using disk? It's the default in a lot of setups.
Best
Peter
> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Black, Michael (IS)
> Sent: Sunday, January 15, 2012 1
Mon, Jan 16, 2012 at 12:29 AM, Black, Michael (IS) <
michael.bla...@ngc.com> wrote:
>
> On SSD with 16384 page size and no WAL mode:
>
> time sqlite3 gen.db
> real4m4.816s ...
> Note: Database is only 595M with this page size. Much more efficient
> storage m
For this particular test cache_size didn't matter much...actually ran a touch
faster with smaller cache (although may be within the margin of error)
I dropped the cache_size to 125 pages (which matches the 16X increase in page
size that I did)
On SSD:
time sqlite3 gen.db
int
main ()
{
i
You did 5 inserts and got 5 records. You need to do either just ONE insert or
ONE insert and FOUR updates.
I'd got for the one insert.
query = "insert into ONTTable (slotId,ponChannelId,onuType,onuId,adminStatus)
values (?1,?2,?3,?4,?5);";
then bind each parameter.
Michael D. Black
S
You have to do all the binds together, THEN step. This works for me.
#include
#include
#include
#include "sqlite3.h"
int main()
{
sqlite3 *db;
sqlite3_stmt *stmt;
int status;
char *create = "create table
ONTTable(slotId,ponChannelId,onuType,onuId,adminStatus);";
char *query
the problem with the shared lib stuff?
>>
>> Thanks!!
>> Black, Michael (IS) wrote, On 1/15/2012 2:27 PM:
>>> A simple one -- and please compile sqlite3.c into your program and make
>>> everybody happy.
>>>
>>> Forget the shared library stuff
Read here on Mac OS X
http://sqlite.org/cvstrac/wiki?p=ReadLine
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-us
You don't need to "register" the SQLite DLL. That's for ActiveX and COM DLLs.
Not ones that just export functions.
The search path stuff is a mess under Microsoft.
http://msdn.microsoft.com/en-us/library/windows/desktop/ms682586(v=vs.85).aspx#standard_search_order_for_desktop_applications
Y
Just add sqlite3.c to your compilation. You likely have no reason to use the
DLL.
This should work:
gcc -O -o test test.c sqlite3.c -ldl -lpthread
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Syst
I'm confused as to why you would expect any match at all. And indeed, when I
run your queries against a test set I get nothing back at all for both queries.
Your datetime formats don't match at all. And "ORDER BY" for that field is not
date/time order since you have DD.MM. which won't re
Yes -- you do need to worry.
Plusyou apparently should rename the DLL so it doesn't collide with any
others from what I can discern from this:
http://msdn.microsoft.com/en-us/library/windows/desktop/ms682586(v=vs.85).aspx
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
version of
System.Data.SQLite.dll that is in the same directory as the executing
application. The only time it will not use this version is if you load
System.Data.SQLite.dll into the Global Assembly Cache which I do not
recommend.
On Tue, Jan 24, 2012 at 10:49 AM, Black, Michael (IS)
wrote:
> Y
Try this utility on both programs and find out what DLL they are actually going
for. And remember that if the DLL is already loaded it will use that.
http://www.dependencywalker.com/
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
If you do a .dump of your table you will see that your 'true' and 'false' are
exactly that -- the strings (and not the boolean values). So yes, there's only
one row that has a value 0. The other rows have 1, 'true', and 'false'.
As the web page says, use 1 or 0 for true/false. It won't conv
Without knowing what you're doing with the data or what your asbtract structure
is many will asssume that too many columns means not enough "normalization" of
your database.
It's not SQLite -- it's standard database design.
http://en.wikipedia.org/wiki/Database_normalization
You may be p
I don't think you want shell.c in your library. That has a "main" which will
conflict.
You don't need it anways.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
Do you have shell access I hope?
Check out "fuser" and see if you can find a process attached to it.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlit
o, no shell no fuser.
Shahar.
2012/1/29 Black, Michael (IS)
> Do you have shell access I hope?
>
>
>
> Check out "fuser" and see if you can find a process attached to it.
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> Advanced Analytics Di
CREATE TABLE tblMainInventory(id,q);
INSERT INTO "tblMainInventory" VALUES(1,40);
INSERT INTO "tblMainInventory" VALUES(2,50);
CREATE TABLE tblSales(id,q);
INSERT INTO "tblSales" VALUES(1,30);
INSERT INTO "tblSales" VALUES(2,20);
CREATE TABLE tblReceives(id,q);
INSERT INTO "tblReceives" VALUES(1,20
Non-standard ISO formatalready doable like this:
sqlite> select substr(strftime("%Y",'now'),3,2);
12
This will work until the year 10,000 :-)
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Sys
When you say "flash" and "JFFS" and "years" I think "life cycle". You may be
hitting the wall.
http://www.chuckstr89134.com/newsltrs/015.htm
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
I don't know python for squatso this is what I get with your codewhat
do you expect?
./py
abc800_111__FIRST ## tablename sql_test 24
## locate sql_test 34
[] ## Rcount sql_test 69
['abc', '800 111 ', 'FIRST'] ## vRecordKey sql_test 97
[(u'1)No Row Name',)] ## divcolinfo
I think I found your problem.
Change
self.con.commit
To
self.con.commit()
In your Fupdate function
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE abc800_111__FIRST
(pink varchar,
Hmmmcould .dump also have the ability to put out the column names for the
inserts?
That would solve this problem without having to write a special program to do
it yourself.
I suppose somebody might already have made a utility to do this?
Michael D. Black
Senior Scientist
Advanced
Any reason you can't just .dump your SQLite database and import into db2?
Or do you need an ongoing connection?
There's a guy in this thread that connected his Linux system to AS400 using PHP
but the .dump/import would be a lot easier.
http://php.net/manual/en/function.odbc-connect.php
M
e3 to
run on an as400?
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 10:46 AM
To: General Discussion of SQLite Database
Subject: [sqlite] RE Connect As400/Iseries to SQLite
d
is the file format.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 11:09 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] RE Connect As400/Iseries to SQLite .db
qlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Wednesday, February 08, 2012 11:47 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] RE Connect As400/Iseries to SQLite .db
Do you have a C compiler on the AS400 you can use?
I co
echo .separator "," >grocery.sql
echo .output %filename% >>grocery.sql
echo select * from grocery; >>grocery.sql
echo .quit >>grocery.sql
sqlite3 grocery.db http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
s
I think you may find you're running into buffer cache limits (not sqlite but OS
limits).
So the 1st third all fits into buffer cache. Once it starts committing to disk
things slow down a LOT.
Since you're not showing an real times it's hard to say you are any slower than
anybody else.
>> test2.csv
Test3.db >> test3.csv
.separator ","
.output "orginal DB file name" + .csv
select * from grocery;
.quit
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Black, Michael (IS)
Sent: Th
Is this from inside your application?
Works fine from the shell which makes me think you're truncating your value in
your program.
SQLite version 3.7.9 2011-11-01 00:52:41
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE IF NOT EXISTS Exports (id
Let's change them a touch...one too many @'s in loop.bat
loop.bat
@for %%i in (%1) do @call dump %%i
dump.bat
@echo .separator "," >grocery.sql
@echo .output %~n1.csv >>grocery.sql
@echo select * from grocery; >>grocery.sql
@echo .quit >>grocery.sql
@sqlite3 %1 grocery.sql
echo .output %~n1.csv
dump.bat
@del /q %~n1.csv
@if not exist %1 echo No such file: %1
@echo .separator "," >grocery.sql
@echo .output %~n1.csv >>grocery.sql
@echo select * from grocery; >>grocery.sql
@echo .quit >>grocery.sql
@sqlite3 %1 mailto:sqlite-users-boun...@sqlite.org] On Behal
801 - 863 of 863 matches
Mail list logo
