[sqlite] Help Please Sqlite3Explorer

[Rajesh Nair]

Hi all

I have asked about Sqlite3 reporting tool before. I got some reply and I
downloaded the Sqlite3Explorer and the dll mkfrXengine.dll for the reporting
service. But it can be used with only SqLite3Explorer and is working
perfectly along with it. Can any one help me to call the function "*
LoadEngine*" inside the dll. I don't know the params that the function
requires. I am looking for a free VC++ 6 compatible report engine that
supports SqLite3 with out ODBC connection. If I start using ODBC then the
most positive point on which I started using Sqlite will be supressed ( NO
CONFIGURATION HEADACHE ) . So please help me to do the explained problem or
PLEASE DIRECT ME TO SUCH A FREE REPORTING TOOL ( Designer / Viewer ) that
can be used with Sqlite without ODBC connection.

-- 
Regards
Rajesh Nair
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] offical full list of valid data types?

[灵感之源]

Hi,

 I found an offical mention here: http://www.sqlite.org/datatype3.html

 but seems not a "full list", because it only mention some "Affinity". I
want a full list of ALL VALID  data types that sqlite really support, here
is my result, does anyone could make it really full list?

REAL
FLOA  (FLOAT will do??)
DOUB  (DOUBLE will do??)
INT(duplicate with INTEGER??)
INTEGER
NUMERIC
DATETIME
CHAR
CLOB
VARCHAR   (NVARCHAR will do??)
TEXT  (NTEXTwill do??)
BLOB

and, does CHAR/CLOB/VARCHAR/TEXT/BLOB all support size definition? let's say
CHAR(50), VARCHAR(100), TEXT(200), BLOB(500) etc?


-- 
Regards
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create table default expr

[John]

Igor Tandetnik wrote:
> "John" <[EMAIL PROTECTED]> wrote
> in message news:[EMAIL PROTECTED]
>> I want to (if possible) create a table with a default timestamp in a
>> format other than "-MM-DD ..." per example below. I have tried a
>> few variants but always get same error.
>>
>> Can this be done and if so, how?
>>
>> create table (
>> custnum integer primary key not null,
>> note text,
>> stamp default (strftime('%s', current_timestamp))
>> );
>> SQL error: near "(": syntax error
> 
> SQLite complains about the very first opening paren, the one right after 
> "create table". You are missing the table name, as in "create table 
> tableName ...". Once this is fixed, your statement works as written.
> 
> Igor Tandetnik
Thanks Igor, I did eventually notice the missing tablename when I was 
reworking that example last night with Simons trigger suggestion. I have 
retested that example with a tablename and confirm that it does indeed work.

John
-- 
Regards
John McMahon
   [EMAIL PROTECTED]


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite concurrency problem

[D. Richard Hipp]

On Nov 3, 2008, at 8:04 AM, Paul Clarke wrote:

> Is it really correct that in order to perform an INSERT, Sqlite  
> demands
> that no other connections be active?
>
> It uses the same database instance but (obviously) separate statements
> for each operation
>

Please distinguish between a "connection" and a "statement".  A  
"connection" is what you get back from sqlite3_open().  A "statement"  
is what you get back from sqlite3_prepare_v2().

You can INSERT while a query is pending on the same connection.  You  
cannot INSERT while a query is pending on the same database file but  
using a different connection.  Within a single program there is rarely  
a need to have multiple connections so this is seldom an issue.


>
>
> When the insert is attempted, SQLITE_BUSY is  returned
>
>
>
> Waiting is obviously no use because the conflicting operation is in  
> the
> outer loop
>
>
>
> Is this expected or am I doing something silly?
>
>
>
> Thanks
>
>
>
> Paul Clarke
>
>
> *
>
> Notice:  This email is confidential and may contain copyright  
> material of Ocado Limited (the "Company"). Opinions and views  
> expressed in this message may not necessarily reflect the opinions  
> and views of the Company.
> If you are not the intended recipient, please notify us immediately  
> and delete all copies of this message. Please note that it is your  
> responsibility to scan this message for viruses.
> Please do not print this email unless necessary.
>
> *
>
>
> Ocado Limited
>
> Titan Court
> 3 Bishops Square
> Hatfield Business Park
> Hatfield
> Herts
> AL10 9NE
> Tel: +44 (0) 1707 228000
> Fax: +44 (0) 1707 227999
> www.ocado.com
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] simple? query problem

[BareFeet]

Hi Ralf,

> I've got two tables A, B
>
> Now I want the entries from B with no corresponding entry in A
>
> I did the following:
>
> Select id.field from B
>
> Where (select count(id.field) from A) = 0
>
> Unfortunately it didn't work, the query should have returned 1 entry.
>
> Where is my mistake?

Firstly, it's inefficient to use count() here since you only care  
whether it exists or doesn't, so counting through the whole table is  
unnecessary. Using "in" would be equally inefficient.

Try this:

select ID from B
where not exists (select 1 from A where A.ID = B.ID)
;

Or you could use this:

select ID from B
except
select ID from A
;

Tom
BareFeet

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subselect question

[Ingo Koch]

Igor Tandetnik wrote:

> How about this:
> 
> select A.*, B.*
> from TBOOKING A, TBOOKING B
> where A.EVENTTYPE = 3 and B.ID = (
> select min(C.ID) from TBOOKING C
> where C.EVENTTYPE = 4 and C.ID > A.ID
> );
> 
> Igor Tandetnik 

Igor, you are my hero ;-)
I've tried a subselect in the join but I've missed  the (somehow obvious)
min(ID) part.


Ingo
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] simple? query problem

[Griggs, Donald]

Hi, Ralf,

Regarding:
   "I want the entries from B with no corresponding entry in A "


I believe one way would be:

SELECT * FROM B
  WHERE id NOT IN (SELECT id FROM A);  

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite concurrency problem

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Paul Clarke wrote:
> Is it really correct that in order to perform an INSERT, Sqlite demands
> that no other connections be active?

http://www.sqlite.org/lockingv3.html
http://www.sqlite.org/sharedcache.html
http://www.sqlite.org/atomiccommit.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkkPfZgACgkQmOOfHg372QT0UwCfeukTaWoY6+Mv5TfYtG+/0fIr
ifEAoNffZzvvjbjIvbji5wQ5DrgBQXI5
=Ycbl
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] simple? query problem

[Ralf Jantschek]

Hello,

 

I've got two tables A, B

 

Now I want the entries from B with no corresponding entry in A 

 

I did the following:

 

Select id.field from B

Where (select count(id.field) from A) = 0

 

Unfortunately it didn't work, the query should have returned 1 entry.

Where is my mistake?

 

Thx

Ralf




Virus checked by G DATA AntiVirusKit
Version: AVK 19.1338 from 03.11.2008

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite concurrency problem

[Paul Clarke]

Is it really correct that in order to perform an INSERT, Sqlite demands
that no other connections be active?

 

I have an application that loops through a results set and performs
INSERTS.

 

It uses the same database instance but (obviously) separate statements
for each operation

 

When the insert is attempted, SQLITE_BUSY is  returned

 

Waiting is obviously no use because the conflicting operation is in the
outer loop

 

Is this expected or am I doing something silly?

 

Thanks

 

Paul Clarke


*

Notice:  This email is confidential and may contain copyright material of Ocado 
Limited (the "Company"). Opinions and views expressed in this message may not 
necessarily reflect the opinions and views of the Company. 
If you are not the intended recipient, please notify us immediately and delete 
all copies of this message. Please note that it is your responsibility to scan 
this message for viruses.
Please do not print this email unless necessary.

*


Ocado Limited

Titan Court
3 Bishops Square
Hatfield Business Park
Hatfield
Herts
AL10 9NE
Tel: +44 (0) 1707 228000
Fax: +44 (0) 1707 227999
www.ocado.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subselect question

[Igor Tandetnik]

Griggs, Donald <[EMAIL PROTECTED]>
wrote:
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]
> On Behalf Of Ingo Koch Sent: Monday, November 03, 2008 2:15 PM
>> SELECT t.*, u.*
>> FROM TBOOKING AS t LEFT JOIN TBOOKING as u ON t.ID+1=u.ID WHERE
>> t.EVENTTYPE+1=u.EVENTTYPE
>
> The problem with the join is, that although t.ID+1=u.ID is most often
> the case, it's not guaranteed that t.ID+1=u.ID. Users may undo the
> last
> booking (by deleting it from the database) which is why the subselects
> have u.ID>t.ID as part of the where clause. So the join, no matter how
> simple and elegant it would be, isn't a choice.

How about this:

select A.*, B.*
from TBOOKING A, TBOOKING B
where A.EVENTTYPE = 3 and B.ID = (
select min(C.ID) from TBOOKING C
where C.EVENTTYPE = 4 and C.ID > A.ID
);

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subselect question

[Ingo Koch]

Griggs, Donald wrote:

> When you wrote:   "... but I didn't want to keep track of an additional
> ID in the application but instead let the database do the work."
> 
> I don't think I understand what logic the database is supposed to use to
> determine this.  If you were talking to a database that was as smart as
> a human, how would you instruct it to choose the proper record without
> an eventId?

Well, I don't need an eventId in the application. All I need is the information
about the startevent and the corresponding stopevent. SQLite *can* collect the
necessary information (see the select in my initial posting) for *my usecase*.
The misbehaviour that I reported is confirmed as a bug and corrected by Dan. So
the database hasn't to be as smart as a human, it only has to be as smart as
SQLite, and I try to instruct it with my limited SQL knowledge. ;-)

I, personally, try to reduce application logic to simple insert, select and
delete statements. Anything else related to the data stored in the database and
the relations between the tables should be handled by the database itself (with
the help of the application programmer of course by means of triggers, database
procedures and functions).
Normally, if handled internally, the database is much faster to do the adequate
things than an application through an interface can do.
And as a result of that this is the single point on my wish list for SQLite:
Stored procedures and functions with support for variables.

Ingo






___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subselect question

[Griggs, Donald]

 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ingo Koch
Sent: Monday, November 03, 2008 2:15 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Subselect question

Csaba wrote:
> Firstly, perhaps you should be linking the start and stop event across

> a common id rather than relying on a start and stop appearing as 
> consecutive entries.  Without knowing more about where your database 
> comes from it's hard to say.
> 
> If you insist on keeping the current structure, here's a way to get 
> what you want (you'll have to set the columns you want to keep as
> appropriate):
> 
> SELECT t.*, u.*
> FROM TBOOKING AS t LEFT JOIN TBOOKING as u ON t.ID+1=u.ID WHERE 
> t.EVENTTYPE+1=u.EVENTTYPE

The problem with the join is, that although t.ID+1=u.ID is most often
the case, it's not guaranteed that t.ID+1=u.ID. Users may undo the last
booking (by deleting it from the database) which is why the subselects
have u.ID>t.ID as part of the where clause. So the join, no matter how
simple and elegant it would be, isn't a choice.

> If, however, you to have a common Id, as mentioned above, for paired 
> event start and stop rows, call it EventId, then you could do:
> SELECT t.*, u.*
> FROM TBOOKING AS t LEFT JOIN TBOOKING as u ON t.EventId=u.EventId 
> WHERE t.EVENTTYPE+1=u.EVENTTYPE

I thought about that too, but I didn't want to keep track of an
additional ID in the application but instead let the database do the
work.
Maybe an additional table for the current eventid and a on insert
trigger could do the trick without changing the application logic. I'll
think about it.

Thanks for your answer.

Ingo


Hello, Ingo,

When you wrote:   "... but I didn't want to keep track of an additional
ID in the application but instead let the database do the work."

I don't think I understand what logic the database is supposed to use to
determine this.  If you were talking to a database that was as smart as
a human, how would you instruct it to choose the proper record without
an eventId?


Donald

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subselect question

[Ingo Koch]

Csaba wrote:
> Firstly, perhaps you should be linking the start and stop event across
> a common id rather than relying on a start and stop appearing as
> consecutive entries.  Without knowing more about where your database
> comes from it's hard to say.
> 
> If you insist on keeping the current structure, here's a way to get
> what you want (you'll have to set the columns you want to keep as
> appropriate):
> 
> SELECT t.*, u.*
> FROM TBOOKING AS t LEFT JOIN TBOOKING as u
> ON t.ID+1=u.ID
> WHERE t.EVENTTYPE+1=u.EVENTTYPE

The problem with the join is, that although t.ID+1=u.ID is most often the case,
it's not guaranteed that t.ID+1=u.ID. Users may undo the last booking (by
deleting it from the database) which is why the subselects have u.ID>t.ID as
part of the where clause. So the join, no matter how simple and elegant it would
be, isn't a choice.

> If, however, you to have a common Id, as mentioned above, for paired
> event start and stop rows, call it EventId, then you could do:
> SELECT t.*, u.*
> FROM TBOOKING AS t LEFT JOIN TBOOKING as u
> ON t.EventId=u.EventId
> WHERE t.EVENTTYPE+1=u.EVENTTYPE

I thought about that too, but I didn't want to keep track of an additional ID in
the application but instead let the database do the work.
Maybe an additional table for the current eventid and a on insert trigger could
do the trick without changing the application logic. I'll think about it.

Thanks for your answer.

Ingo

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Issues: Duplicates In UNION Query

[TW]

> Frankly, I don't understand why you would ever need such a beast, and I 
> probably don't want to know (I suspect it will give me nightmares). In 
> any case, assuming you really have a reason for this monster, try 
> something like this:

Yeah, I need it, and the query that I auto-generate from C does this 
for 80 records.

Thanks for the help Igor.  I wish it were simpler.

-- 
VR~
TW
Email: [EMAIL PROTECTED]
"Knowledge Is Power"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Issues: Duplicates In UNION Query

[Igor Tandetnik]

TW <[EMAIL PROTECTED]> wrote:
> It would look like this:
>
> FName LName Side Height
> --
> Igor Tande A 1
> Telly Will B 1
> John Smith A 1
> Bob Hope B 1
> Sandy Rivera A 2
> Bobby Sangria B 2
> Jane Tane A 2
> Tom Jones B 2
>
> SELECT Fname, Lname, Side, Height FROM (SELECT * FROM table WHERE
> Side='A' AND ((Height=1) OR (Height=2)) ORDER BY random() LIMIT 1)
> UNION
> SELECT Fname, Lname, Side, Height FROM (SELECT * FROM table WHERE
> Side='B' AND ((Height=1) OR (Height=2)) ORDER BY random() LIMIT 1)
> UNION
> SELECT Fname, Lname, Side, Height FROM (SELECT * FROM table WHERE
> Side='A' AND ((Height=1) OR (Height=2)) ORDER BY random() LIMIT 1);

Frankly, I don't understand why you would ever need such a beast, and I 
probably don't want to know (I suspect it will give me nightmares). In 
any case, assuming you really have a reason for this monster, try 
something like this:

SELECT Fname, Lname, Side, Height, 1 sequence FROM (SELECT * FROM table 
WHERE
Side='A' AND ((Height=1) OR (Height=2)) ORDER BY random() LIMIT 1) 
UNION
SELECT Fname, Lname, Side, Height, 2 sequence FROM (SELECT * FROM table 
WHERE
Side='B' AND ((Height=1) OR (Height=2)) ORDER BY random() LIMIT 1) 
UNION
SELECT Fname, Lname, Side, Height, 3 sequence FROM (SELECT * FROM table 
WHERE
Side='A' AND ((Height=1) OR (Height=2)) ORDER BY random() LIMIT 1)
ORDER BY sequence;

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Issues: Duplicates In UNION Query

[TW]

> Order is never guaranteed in a SQL statement's resultset, unless this 
> statement specifies an ORDER BY clause. If some statement without such a 
> clause happens to give you an order you want, it does so by accident. If 
> you want a particular order, say so in the statement.

Yeah, which would work for the height example, but then would still 
mess up the alternation of the side column.

> It would help if you showed the actual statement you have now, and the 
> results you expect.

Apologies.

It would look like this:

FName   LName   SideHeight
--
IgorTande   A   1
Telly   WillB   1
JohnSmith   A   1
Bob HopeB   1
Sandy   Rivera  A   2
Bobby   Sangria B   2
JaneTaneA   2
Tom Jones   B   2

SELECT Fname, Lname, Side, Height FROM (SELECT * FROM table WHERE 
Side='A' AND ((Height=1) OR (Height=2)) ORDER BY random() LIMIT 1) UNION
SELECT Fname, Lname, Side, Height FROM (SELECT * FROM table WHERE 
Side='B' AND ((Height=1) OR (Height=2)) ORDER BY random() LIMIT 1) UNION
SELECT Fname, Lname, Side, Height FROM (SELECT * FROM table WHERE 
Side='A' AND ((Height=1) OR (Height=2)) ORDER BY random() LIMIT 1);

With the UNION statement above I might get:

FName   LName   SideHeight
--
JaneTaneA   2
IgorTande   A   1
Tom Jones   B   2

The Height is out of order (not 1, 2, 2).  If I use a UNION ALL 
then I might get:

FName   LName   SideHeight
--
JohnSmith   A   1
Telly   WillB   1
JohnSmith   A   1

See the duplicate?  I'm doing this in a C++ program, so I'm thinking 
that 
I might have to run individual SELECT statements for each one in the order that 
I
need. 

-- 
VR~
TW
Email: [EMAIL PROTECTED]
"Knowledge Is Power"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Valgrind complains about sqlite

[Martin Engelschalk]

Hello Daniel,

i know this specific output "Syscall param write(buf) points to 
uninitialised byte(s)" from my programs. It appears outside sqlite, too. 
It is not a problem.

Martin

Daniel Hellsson wrote:
> I have sqlite3 3.6.4 and the program I've written gets in trouble with
> valgrind.
>
>  
>
> Valgrind reports:
>
>  
>
> ==29506== 128 errors in context 7 of 7:
>
> ==29506== Syscall param write(buf) points to uninitialised byte(s)
>
> ==29506==at 0x41354FB: (within /targets/DIABLO_X86/lib/libc-2.5.so)
>
> ==29506==by 0x402A562: sqlite3OsWrite (sqlite3.c:12200)
>
> ==29506==by 0x4044F5E: writeJournalHdr (sqlite3.c:28956)
>
> ==29506==by 0x40450C2: pager_open_journal (sqlite3.c:31263)
>
> ==29506==by 0x4045281: sqlite3PagerBegin (sqlite3.c:31335)
>
> ==29506==by 0x404B4FB: sqlite3BtreeBeginTrans (sqlite3.c:35486)
>
> ==29506==by 0x4073931: sqlite3Step (sqlite3.c:48410)
>
> ==29506==by 0x406558B: sqlite3_step (sqlite3.c:45084)
>
> ==29506==by 0x80492C4: sql_helper_execute (sql_helper.c:39)
>
> ==29506==by 0x8048DD8: create_meal (main.c:242)
>
> ==29506==by 0x80490AB: create_random_meals_for_year (main.c:338)
>
> ==29506==by 0x80491DB: main (main.c:383)
>
> ==29506==  Address 0x41ce07c is 36 bytes inside a block of size 1,032
> alloc'd
>
> ==29506==at 0x40207C4: malloc (vg_replace_malloc.c:207)
>
> ==29506==by 0x402A92A: sqlite3MemMalloc (sqlite3.c:12547)
>
> ==29506==by 0x402B1A8: mallocWithAlarm (sqlite3.c:16221)
>
> ==29506==by 0x402B288: sqlite3Malloc (sqlite3.c:16244)
>
> ==29506==by 0x402F3A3: pcacheMalloc (sqlite3.c:27408)
>
> ==29506==by 0x402F435: sqlite3PageMalloc (sqlite3.c:27421)
>
> ==29506==by 0x4037FBC: sqlite3BtreeFactory (sqlite3.c:30125)
>
> ==29506==by 0x407968B: openDatabase (sqlite3.c:84530)
>
> ==29506==by 0x80488E6: database_open_event_database (main.c:50)
>
> ==29506==by 0x8049139: main (main.c:367)
>
>  
>
> Is this a known problem or not a problem at all? It's worrysome because
> I seem to have wierd stack-problems in my code, which make themselves
> known by outputting random values in printf and sprintf. These are the
> only problems valgrind report that have any source in my own code so it
> seems reasonable to assume that there is some kind of problem here. 
>
>  
>
> In the code I'm writing the SQL statement with vsprintf from varargs and
> then running that through sqlite3_exec or sqlite3_prepare_v2. Is there
> some incompatibility with stdargs and sqlite3?
>
>  
>
> Any help is greatly appreciated.
>
> Kind regards,
>
> Daniel
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   

-- 

* Codeswift GmbH *
Traunstr. 30
A-5026 Salzburg-Aigen
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 12120 / 204645
[EMAIL PROTECTED]
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Valgrind complains about sqlite

[Dan]

On Nov 3, 2008, at 11:01 PM, Daniel Hellsson wrote:

> I have sqlite3 3.6.4 and the program I've written gets in trouble with
> valgrind.
>
>
>
> Valgrind reports:
>
>
>
> ==29506== 128 errors in context 7 of 7:
>
> ==29506== Syscall param write(buf) points to uninitialised byte(s)
>
> ==29506==at 0x41354FB: (within /targets/DIABLO_X86/lib/ 
> libc-2.5.so)
>
> ==29506==by 0x402A562: sqlite3OsWrite (sqlite3.c:12200)
>
> ==29506==by 0x4044F5E: writeJournalHdr (sqlite3.c:28956)
>
> ==29506==by 0x40450C2: pager_open_journal (sqlite3.c:31263)
>
> ==29506==by 0x4045281: sqlite3PagerBegin (sqlite3.c:31335)
>
> ==29506==by 0x404B4FB: sqlite3BtreeBeginTrans (sqlite3.c:35486)
>
> ==29506==by 0x4073931: sqlite3Step (sqlite3.c:48410)
>
> ==29506==by 0x406558B: sqlite3_step (sqlite3.c:45084)
>
> ==29506==by 0x80492C4: sql_helper_execute (sql_helper.c:39)
>
> ==29506==by 0x8048DD8: create_meal (main.c:242)
>
> ==29506==by 0x80490AB: create_random_meals_for_year (main.c:338)
>
> ==29506==by 0x80491DB: main (main.c:383)
>
> ==29506==  Address 0x41ce07c is 36 bytes inside a block of size 1,032
> alloc'd
>
> ==29506==at 0x40207C4: malloc (vg_replace_malloc.c:207)
>
> ==29506==by 0x402A92A: sqlite3MemMalloc (sqlite3.c:12547)
>
> ==29506==by 0x402B1A8: mallocWithAlarm (sqlite3.c:16221)
>
> ==29506==by 0x402B288: sqlite3Malloc (sqlite3.c:16244)
>
> ==29506==by 0x402F3A3: pcacheMalloc (sqlite3.c:27408)
>
> ==29506==by 0x402F435: sqlite3PageMalloc (sqlite3.c:27421)
>
> ==29506==by 0x4037FBC: sqlite3BtreeFactory (sqlite3.c:30125)
>
> ==29506==by 0x407968B: openDatabase (sqlite3.c:84530)
>
> ==29506==by 0x80488E6: database_open_event_database (main.c:50)
>
> ==29506==by 0x8049139: main (main.c:367)
>
>
>
> Is this a known problem or not a problem at all? It's worrysome  
> because
> I seem to have wierd stack-problems in my code, which make themselves
> known by outputting random values in printf and sprintf. These are the
> only problems valgrind report that have any source in my own code so  
> it
> seems reasonable to assume that there is some kind of problem here.

It's not a problem.

Dan.






___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Valgrind complains about sqlite

[Daniel Hellsson]

I have sqlite3 3.6.4 and the program I've written gets in trouble with
valgrind.

 

Valgrind reports:

 

==29506== 128 errors in context 7 of 7:

==29506== Syscall param write(buf) points to uninitialised byte(s)

==29506==at 0x41354FB: (within /targets/DIABLO_X86/lib/libc-2.5.so)

==29506==by 0x402A562: sqlite3OsWrite (sqlite3.c:12200)

==29506==by 0x4044F5E: writeJournalHdr (sqlite3.c:28956)

==29506==by 0x40450C2: pager_open_journal (sqlite3.c:31263)

==29506==by 0x4045281: sqlite3PagerBegin (sqlite3.c:31335)

==29506==by 0x404B4FB: sqlite3BtreeBeginTrans (sqlite3.c:35486)

==29506==by 0x4073931: sqlite3Step (sqlite3.c:48410)

==29506==by 0x406558B: sqlite3_step (sqlite3.c:45084)

==29506==by 0x80492C4: sql_helper_execute (sql_helper.c:39)

==29506==by 0x8048DD8: create_meal (main.c:242)

==29506==by 0x80490AB: create_random_meals_for_year (main.c:338)

==29506==by 0x80491DB: main (main.c:383)

==29506==  Address 0x41ce07c is 36 bytes inside a block of size 1,032
alloc'd

==29506==at 0x40207C4: malloc (vg_replace_malloc.c:207)

==29506==by 0x402A92A: sqlite3MemMalloc (sqlite3.c:12547)

==29506==by 0x402B1A8: mallocWithAlarm (sqlite3.c:16221)

==29506==by 0x402B288: sqlite3Malloc (sqlite3.c:16244)

==29506==by 0x402F3A3: pcacheMalloc (sqlite3.c:27408)

==29506==by 0x402F435: sqlite3PageMalloc (sqlite3.c:27421)

==29506==by 0x4037FBC: sqlite3BtreeFactory (sqlite3.c:30125)

==29506==by 0x407968B: openDatabase (sqlite3.c:84530)

==29506==by 0x80488E6: database_open_event_database (main.c:50)

==29506==by 0x8049139: main (main.c:367)

 

Is this a known problem or not a problem at all? It's worrysome because
I seem to have wierd stack-problems in my code, which make themselves
known by outputting random values in printf and sprintf. These are the
only problems valgrind report that have any source in my own code so it
seems reasonable to assume that there is some kind of problem here. 

 

In the code I'm writing the SQL statement with vsprintf from varargs and
then running that through sqlite3_exec or sqlite3_prepare_v2. Is there
some incompatibility with stdargs and sqlite3?

 

Any help is greatly appreciated.

Kind regards,

Daniel

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "SQL logic error or missing database" with multithreaded program

[D. Richard Hipp]

On Nov 3, 2008, at 9:54 AM, Ken wrote:

> No I would not wrap the querries in a transaction.
>
> I think the problem you are encountering is due to thread  
> interaction upon the sqlite structures. Since it was compiled with  
> THREADsafety disabled.
>
> If you have two threads that share the same connection. You need to  
> compile with THREADSAFE turned on. Or provide your own mutexing such  
> that neither thread interacts with the sqlite connection concurrently.

Note:  when SQLITE_THREADSAFE=0 then it is not safe for two threads to  
call SQLite under any circumstances, even if they are using completely  
separate database connections.

My advice is that you not use threads. Threads are evil.  But,  
recognizing that you are unlikely to heed this warning, at the very  
least compile with SQLITE_THREADSAFE=1 if you really think you must  
use threads.

D. Richard Hipp
[EMAIL PROTECTED]



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_prepare memory leak

[Sherief N. Farouk]

> As you can see I use sqlite3_prepare/sqlite3_finalize pairs. My memory
> leak checker ( Deleaker : www.deleaker.com ) reports memory leak at
> sqlite3_prepare :
> 
> msvcrt.dll!malloc
> sqlite3.dll!sqlite3_malloc + 120 bytes
> sqlite3.dll!sqlite3_realloc + 27 bytes
> sqlite3.dll!sqlite3_release_memory + 200 bytes
> sqlite3.dll!sqlite3_release_memory + 242 bytes
> sqlite3.dll!sqlite3_set_authorizer + 1072 bytes
> sqlite3.dll!sqlite3_set_authorizer + 22149 bytes
> sqlite3.dll!sqlite3_declare_vtab + 11632 bytes
> sqlite3.dll!sqlite3_prepare16_v2 + 18070 bytes
> sqlite3.dll!sqlite3_declare_vtab + 27000 bytes
> sqlite3.dll!sqlite3_declare_vtab + 31896 bytes
> sqlite3.dll!sqlite3_declare_vtab + 36104 bytes
> sqlite3.dll!sqlite3_reset_auto_extension + 12492 bytes
> sqlite3.dll!sqlite3_reset_auto_extension + 13270 bytes
> sqlite3.dll!sqlite3_prepare + 31 bytes
> RSR2.exe!SQLite3pp::readBlob Line 164
> (c:\furtado\furtado\sqlite\sqlite3pp.cpp)
> 
> I am closing database properly.
> Am I doing something wrong or is it some kind of bug?

99% of the time the leaks are due to API abuse. Post a small sample app
(that build and runs) exhibiting the behavior, if possible. What does
_CrtDumpMemoryLeaks() say?

- Sherief

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "SQL logic error or missing database" with multithreaded program

[Ken]

No I would not wrap the querries in a transaction.

I think the problem you are encountering is due to thread interaction upon the 
sqlite structures. Since it was compiled with THREADsafety disabled.

If you have two threads that share the same connection. You need to compile 
with THREADSAFE turned on. Or provide your own mutexing such that neither 
thread interacts with the sqlite connection concurrently.

Steps to take from here:
  1. re compile with thread saftey turned on.
  2. Each thread should create its own connection independently of the other.
  3. Perform test for sqlite busy when starting transactions and the prepare 
and first calls to step (ie for selects).

 

Hope that helps


--- On Mon, 11/3/08, dbikash <[EMAIL PROTECTED]> wrote:

> From: dbikash <[EMAIL PROTECTED]>
> Subject: Re: [sqlite] "SQL logic error or missing database" with 
> multithreaded program
> To: sqlite-users@sqlite.org
> Date: Monday, November 3, 2008, 7:39 AM
> Many Thanks Ken for your guidelines.
> 
> Following your suggestions I found that indeed there was a
> silly mistake in
> the querying thread (I was using a 2-D array of queries,
> and I missed a
> comma :-( ). 
> 
> After correcting this mistake, the two threads ran fine
> when run one at a
> time. When run together, I did not get any error message in
> the query
> thread. However, I did receive the "SQL logic error or
> missing database"
> message (error code 1) during an insertion after a random
> number of
> successful insertions and queries. This was accompanied by
> a seg fault. 
> 
> After many futile tries, I replaced the call to
> sqlite_prepare by
> sqlite_prepare_v2. And it seems to work! The program is
> running for 10
> minutes now.
> 
> What could have gone wrong with prepare?
> 
> Another question, should I wrap each individual query (not
> inserts) within a
> transaction too?
> 
> Thanks again for all the help.
> dbikash
> 
> 
> ken-33 wrote:
> > 
> > I think you should consider running the program with
> one thread at a time.
> > 
> > This will help you isolate if this is a problem within
> a thread or is an
> > interaction among the threads.
> > 
> > I'll assume its both. After you fix all of the
> localized issues.
> > 
> > I'd suggest recompiling sqlite with thread safe
> turned on. 
> > 
> > Allow each thread to create its own connection. 
> > 
> > Then Wrap each transaction with a begin immediate.
> That way you can
> > acquire a lock when needed. Use a loop around the
> begin immediate testing
> > for success. If you fail with sqlite busy. Simply
> sleep a short while
> > (hint usleep) loop and try again.
> > 
> > HTH
> > Ken
> > 
> > 
> > 
> > --- On Fri, 10/31/08, dbikash
> <[EMAIL PROTECTED]> wrote:
> > 
> >> From: dbikash <[EMAIL PROTECTED]>
> >> Subject: Re: [sqlite] "SQL logic error or
> missing database" with
> >> multithreaded program
> >> To: sqlite-users@sqlite.org
> >> Date: Friday, October 31, 2008, 10:24 AM
> >> I use both int sqlite3_errcode() and
> sqlite3_errmsg(). Here
> >> is what they
> >> return (I get 3 different types of errors in
> different
> >> runs):
> >> 1, SQL logic error or missing database.
> >> 1, unrecognized token "" (there's a
> non
> >> printable character like a rectangle
> >> within the quotes).
> >> 1, SQL logic error or missing database,
> sqlite3_get_table()
> >> called with two
> >> or more incompatible queries (this last print is
> obtained
> >> from zErrMsg
> >> returned when the return value from
> sqlite3_get_table() is
> >> not SQLITE_OK)
> >> 
> >> Also, the program is written to print an error and
> then
> >> continue in the
> >> infinite loop. I can find a few errors in query
> execution,
> >> but it is after
> >> an error in insertion (sqlite3_step() !=
> SQLITE_DONE) that
> >> the program quits
> >> by itself. 
> >> 
> >> 
> >> D. Richard Hipp wrote:
> >> > 
> >> > 
> >> > On Oct 31, 2008, at 9:46 AM, dbikash wrote:
> >> >>
> >> >> I get a SQLite error 1 (SQL logic error
> >> >> or missing database). What could be the
> problem?
> >> > 
> >> > Is that the error message text that SQLite
> returns: 
> >> "SQL logic error  
> >> > or missing database"?  Or is that just
> the
> >> meaning of SQLITE_ERROR  
> >> > that you looked up?  What error message does
> >> sqlite3_errmsg(db) return?
> >> > 
> >> > D. Richard Hipp
> >> > [EMAIL PROTECTED]
> >> > 
> >> > 
> >> > 
> >> >
> ___
> >> > sqlite-users mailing list
> >> > sqlite-users@sqlite.org
> >> >
> >>
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >> > 
> >> > 
> >> 
> >> -- 
> >> View this message in context:
> >>
> http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20268213.html
> >> Sent from the SQLite mailing list archive at
> Nabble.com.
> >> 
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users@sqlite.org
> >>
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 

Re: [sqlite] Convert integer IP address to string IP address in Sqlite

[dbikash]

Thanks Igor. It is working now.

I seem to have made a mistake earlier with the brackets and pipes and quotes
and ampersands ... Sorry the trouble.

dbikash.


Igor Tandetnik wrote:
> 
> "dbikash" <[EMAIL PROTECTED]> wrote in
> message news:[EMAIL PROTECTED]
>> This is not working for me. I just get the first octet.
>>
>> (The concat function is also not supported?)
> 
> || (two pipe characters) is a concatenation operator in SQL. That is, 
> 'a' || 'b' = 'ab'. concat() function is not supported because it's not 
> needed.
> 
> How do you run the statement? Do you, by any chance, pass it unquoted to 
> some program on a command line, where a pipe character might be 
> interpreted specially? Or something along these lines?
> 
> What does this statement produce:
> 
> select 'a' || 'b', 'a' || 'b' = 'ab';
> 
> Igor Tandetnik
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Convert-integer-IP-address-to-string-IP-address-in-Sqlite-tp20267362p20303143.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "SQL logic error or missing database" with multithreaded program

[dbikash]

Many Thanks Ken for your guidelines.

Following your suggestions I found that indeed there was a silly mistake in
the querying thread (I was using a 2-D array of queries, and I missed a
comma :-( ). 

After correcting this mistake, the two threads ran fine when run one at a
time. When run together, I did not get any error message in the query
thread. However, I did receive the "SQL logic error or missing database"
message (error code 1) during an insertion after a random number of
successful insertions and queries. This was accompanied by a seg fault. 

After many futile tries, I replaced the call to sqlite_prepare by
sqlite_prepare_v2. And it seems to work! The program is running for 10
minutes now.

What could have gone wrong with prepare?

Another question, should I wrap each individual query (not inserts) within a
transaction too?

Thanks again for all the help.
dbikash


ken-33 wrote:
> 
> I think you should consider running the program with one thread at a time.
> 
> This will help you isolate if this is a problem within a thread or is an
> interaction among the threads.
> 
> I'll assume its both. After you fix all of the localized issues.
> 
> I'd suggest recompiling sqlite with thread safe turned on. 
> 
> Allow each thread to create its own connection. 
> 
> Then Wrap each transaction with a begin immediate. That way you can
> acquire a lock when needed. Use a loop around the begin immediate testing
> for success. If you fail with sqlite busy. Simply sleep a short while
> (hint usleep) loop and try again.
> 
> HTH
> Ken
> 
> 
> 
> --- On Fri, 10/31/08, dbikash <[EMAIL PROTECTED]> wrote:
> 
>> From: dbikash <[EMAIL PROTECTED]>
>> Subject: Re: [sqlite] "SQL logic error or missing database" with
>> multithreaded program
>> To: sqlite-users@sqlite.org
>> Date: Friday, October 31, 2008, 10:24 AM
>> I use both int sqlite3_errcode() and sqlite3_errmsg(). Here
>> is what they
>> return (I get 3 different types of errors in different
>> runs):
>> 1, SQL logic error or missing database.
>> 1, unrecognized token "" (there's a non
>> printable character like a rectangle
>> within the quotes).
>> 1, SQL logic error or missing database, sqlite3_get_table()
>> called with two
>> or more incompatible queries (this last print is obtained
>> from zErrMsg
>> returned when the return value from sqlite3_get_table() is
>> not SQLITE_OK)
>> 
>> Also, the program is written to print an error and then
>> continue in the
>> infinite loop. I can find a few errors in query execution,
>> but it is after
>> an error in insertion (sqlite3_step() != SQLITE_DONE) that
>> the program quits
>> by itself. 
>> 
>> 
>> D. Richard Hipp wrote:
>> > 
>> > 
>> > On Oct 31, 2008, at 9:46 AM, dbikash wrote:
>> >>
>> >> I get a SQLite error 1 (SQL logic error
>> >> or missing database). What could be the problem?
>> > 
>> > Is that the error message text that SQLite returns: 
>> "SQL logic error  
>> > or missing database"?  Or is that just the
>> meaning of SQLITE_ERROR  
>> > that you looked up?  What error message does
>> sqlite3_errmsg(db) return?
>> > 
>> > D. Richard Hipp
>> > [EMAIL PROTECTED]
>> > 
>> > 
>> > 
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> >
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> > 
>> > 
>> 
>> -- 
>> View this message in context:
>> http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20268213.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/%22SQL-logic-error-or-missing-database%22-with-multithreaded-program-tp20266281p20302902.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert integer IP address to string IP address in Sqlite

[Igor Tandetnik]

"dbikash" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> This is not working for me. I just get the first octet.
>
> (The concat function is also not supported?)

|| (two pipe characters) is a concatenation operator in SQL. That is, 
'a' || 'b' = 'ab'. concat() function is not supported because it's not 
needed.

How do you run the statement? Do you, by any chance, pass it unquoted to 
some program on a command line, where a pipe character might be 
interpreted specially? Or something along these lines?

What does this statement produce:

select 'a' || 'b', 'a' || 'b' = 'ab';

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Issues: Duplicates In UNION Query

[Igor Tandetnik]

"TW" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> I have been doing a Union query and would like to obtain distinct
> results.
> Via the manual, the correct way to accomplish this is to do a UNION
> statement (versus
> doing a UNION ALL).
>
> Problem: I want the order of the select statements left intact after
> the UNION
> select statements.

Order is never guaranteed in a SQL statement's resultset, unless this 
statement specifies an ORDER BY clause. If some statement without such a 
clause happens to give you an order you want, it does so by accident. If 
you want a particular order, say so in the statement.

> How should I write this thing so that I get the ascending
> height ALONG WITH the alternating sides according to the order of the
> select statements that I put in the query?

It would help if you showed the actual statement you have now, and the 
results you expect.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create table default expr

[Igor Tandetnik]

"John" <[EMAIL PROTECTED]> wrote
in message news:[EMAIL PROTECTED]
> I want to (if possible) create a table with a default timestamp in a
> format other than "-MM-DD ..." per example below. I have tried a
> few variants but always get same error.
>
> Can this be done and if so, how?
>
> create table (
> custnum integer primary key not null,
> note text,
> stamp default (strftime('%s', current_timestamp))
> );
> SQL error: near "(": syntax error

SQLite complains about the very first opening paren, the one right after 
"create table". You are missing the table name, as in "create table 
tableName ...". Once this is fixed, your statement works as written.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Pragma integrity_check and attached ddatabases

[BR-Software]

Hello,

Will "pragma integrity_check" also check attached databases?

Best regards

Baard Riiber

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with dates

[Igor Tandetnik]

"Timothy A. Sawyer"
<[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
> My SQLite database was imported from an Access database where the
> dates (in
> Access) were stored as mm/dd/. My application is written in Java
> where I
> am using the DateFormat method to convert the date to mm/dd/yy.
> However when
> I do a select based on that date, for example "SELECT * FROM
> tblEventInfo
> WHERE fldDateBowled >= '01/01/08' it returns all the rows in the
> database.

'01/01/08' is just a string literal. You are doing string comparisons, 
in alphabetical order.

> Reading the documentation, I found that dates in SQLite are in
> '/mm/dd/'
> format.

SQLite doesn't have a dedicated date type at all. Instead, it has a set 
of functions for manipulating dates, and a number of formats these 
functions recognize:

http://sqlite.org/lang_datefunc.html

You may choose any of these formats to represent your dates internally - 
but you have to be consistent about it.

> Still, doing "SELECT * FROM tblEventInfo WHERE fldDateBowled
> >= '2008-01-01'" does not yield the results I expect.

'2008-01-01' is still just a string literal, and you are still doing 
simple string comparisons. Now, for two dates in -MM-DD format, an 
alphabetical comparison happens to order them correctly as dates, too. 
So, if you represent all your dates in this format - actual dates stored 
in your tables as well as literals used in the query - then the 
statement above would do the right thing.

> What do I need to do with the dates when I import the data from
> Access? Do I
> need to convert them to -MM-DD?

It would definitely help. That's what I'd recommend, unless you have a 
good reason not to.

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with dates

[Timothy A. Sawyer]

Good morning,

 

I am having a problem extracting correct rows from the database based on
date. Perhaps someone can help.

 

My SQLite database was imported from an Access database where the dates (in
Access) were stored as mm/dd/. My application is written in Java where I
am using the DateFormat method to convert the date to mm/dd/yy. However when
I do a select based on that date, for example "SELECT * FROM tblEventInfo
WHERE fldDateBowled >= '01/01/08' it returns all the rows in the database.
My database does not use the timestamp, so the 00:00:00 is inconsequential.

 

Reading the documentation, I found that dates in SQLite are in '/mm/dd/'
format. Still, doing "SELECT * FROM tblEventInfo WHERE fldDateBowled >=
'2008-01-01'" does not yield the results I expect.

 

What do I need to do with the dates when I import the data from Access? Do I
need to convert them to -MM-DD?

 

Any assistance is appreciated.

 

Timothy A. Sawyer, CISSP
Managing Director
MBD Solutions

Phone: (603) 546-7132
Web:   http://www.mybowlingdiary.com
Email:   [EMAIL PROTECTED]

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_prepare memory leak

[Bartosz Wiklak]

Hi,

In my C++ application I'm writing/reading some blob data to the
database. This is sample of my code (in 99% taken from example i found
in the internet ):

/**
 * This function reads one blob value asqued in zSql query into memory
pointed by pzBlob
 * and returns size of allocated memory in pnBlob integer. One MUST
remember to deallocate
 * memory provided by pzBlob pointer.
 * Example: readBlob("SELECT value FROM table WHERE id=5", , );
 * \param *zSql Query string f.e. "SELECT value FROM table WHERE id=5"
 * \param **pzBlob Pointer to memory where data is retuned. This
memory is allocated automatically. One MUST remember to deallocate it.
 * \param *pnBlob Size of data returned in pzBlob
 * \return False if anything went wrong, else True;
 */
bool SQLite3pp::readBlob(const char *zSql, unsigned char **pzBlob, int *pnBlob){
  if(zErrMsg) sqlite3_free(zErrMsg);

  sqlite3_stmt *pStmt;
  int rc;

  /* In case there is no table entry for key zKey or an error occurs,
  ** set *pzBlob and *pnBlob to 0 now.
  */
  *pzBlob = 0;
  *pnBlob = 0;

  bool retValue = false;

  do {
/* Compile the SELECT statement into a virtual machine. */
rc = sqlite3_prepare(db, zSql, -1, , 0);
 // Line 164
if( rc!=SQLITE_OK ){
  return false;
}



/* Run the virtual machine. We can tell by the SQL statement that
** at most 1 row will be returned. So call sqlite3_step() once
** only. Normally, we would keep calling sqlite3_step until it
** returned something other than SQLITE_ROW.
*/
rc = sqlite3_step(pStmt);
if( rc==SQLITE_Row ){
  /* The pointer returned by sqlite3_column_blob() points to memory
  ** that is owned by the statement handle (pStmt). It is only good
  ** until the next call to an sqlite3_XXX() function (e.g. the
  ** sqlite3_finalize() below) that involves the statement handle.
  ** So we need to make a copy of the blob into memory obtained from
  ** malloc() to return to the caller.
  */
  *pnBlob = sqlite3_column_bytes(pStmt, 0);
  *pzBlob = (unsigned char *)malloc(*pnBlob);
  memcpy(*pzBlob, sqlite3_column_blob(pStmt, 0), *pnBlob);

  retValue=true;
}

/* Finalize the statement (this releases resources allocated by
** sqlite3_prepare() ).
*/
rc = sqlite3_finalize(pStmt);

/* If sqlite3_finalize() returned SQLITE_SCHEMA, then try to execute
** the statement all over again.
*/
  } while( rc==SQLITE_SCHEMA );

  return retValue;
}


As you can see I use sqlite3_prepare/sqlite3_finalize pairs. My memory
leak checker ( Deleaker : www.deleaker.com ) reports memory leak at
sqlite3_prepare :

msvcrt.dll!malloc
sqlite3.dll!sqlite3_malloc + 120 bytes
sqlite3.dll!sqlite3_realloc + 27 bytes
sqlite3.dll!sqlite3_release_memory + 200 bytes
sqlite3.dll!sqlite3_release_memory + 242 bytes
sqlite3.dll!sqlite3_set_authorizer + 1072 bytes
sqlite3.dll!sqlite3_set_authorizer + 22149 bytes
sqlite3.dll!sqlite3_declare_vtab + 11632 bytes
sqlite3.dll!sqlite3_prepare16_v2 + 18070 bytes
sqlite3.dll!sqlite3_declare_vtab + 27000 bytes
sqlite3.dll!sqlite3_declare_vtab + 31896 bytes
sqlite3.dll!sqlite3_declare_vtab + 36104 bytes
sqlite3.dll!sqlite3_reset_auto_extension + 12492 bytes
sqlite3.dll!sqlite3_reset_auto_extension + 13270 bytes
sqlite3.dll!sqlite3_prepare + 31 bytes
RSR2.exe!SQLite3pp::readBlob Line 164
(c:\furtado\furtado\sqlite\sqlite3pp.cpp)

I am closing database properly.
Am I doing something wrong or is it some kind of bug?


Bartosz Wiklak
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create table default expr

[John]

Simon Davies wrote:
> 2008/11/3 John <[EMAIL PROTECTED]>:
>> Hi
>>
>> I want to (if possible) create a table with a default timestamp in a
>> format other than "-MM-DD ..." per example below. I have tried a few
>> variants but always get same error.
>>
>> Can this be done and if so, how?
>>
>> create table (
>>custnum integer primary key not null,
>>note text,
>>stamp default (strftime('%s', current_timestamp))
>> );
>> SQL error: near "(": syntax error
>>
>> --
>> Regards
>>John McMahon
>>   [EMAIL PROTECTED]
>>
>>
> 
> From http://www.sqlite.org/lang_createtable.html,
> "The DEFAULT constraint specifies a default value to use when doing an
> INSERT. The value may be NULL, a string constant or a number. "; using
> a function as a default is not covered.

Thanks Simon, I had been looking at the "column-constraint" diagram and 
misinterpreted the "expr" part of "default ( expr )" as a generic 
expression that would include a function.

> 
> A trigger can probably achieve what you want :

Good idea thank you.
> 
>   create table tst( id integer primary key, ts integer default
> current_timestamp );
>   create trigger tst_update_ts after insert on tst begin
>   update tst set ts = case strftime( '%s', ts ) not null when 1
> then strftime( '%s', ts ) else ts end where id=new.id;
>   end;
> 
>   insert into tst( id ) values( null );
>   insert into tst( id ) values( null );
>   select * from tst;
> 1|1225703251
> 2|1225703259
> 
>   insert into tst values( null, 'my birthday' );
>   select * from tst;
> 1|1225703251
> 2|1225703259
> 3|my birthday
> 
> Rgds,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 
Regards
John McMahon
   [EMAIL PROTECTED]


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Making the binary small

[Pados Károly]

Using strip solved the problem, object file size is down to 114KB. Thank you 
very much.

Károly

> The sizes that I mentioned (315KB vs 205KB) are for the final .dll and
> .so size.  You might try linking your object files into a lib to see
> how that affects size.   You could also try running the "strip"
> command on the object files to ensure all the debugging symbols are
> removed.
> 
> Any OMIT options you compile your application with must also be used
> to compile the mkkeywordhash tool (used to generate keywordhash.h) and
> passed to the lemon parser (used to generate parse.c).   They must
> also be passed to gcc to compile sqlite3.c.
> 
> Note how the define for SQLITE_OMIT_LOAD_EXTENSION is used below:
> 
> gcc -o mkkeywordhash.exe -DSQLITE_OMIT_LOAD_EXTENSION=1  mkkeywordhash.c
> 
> ./lemon.exe -DSQLITE_OMIT_LOAD_EXTENSION=1  parse.y
> 
> gcc -Os -DSQLITE_OS_WIN=1 -DNDEBUG -DSQLITE_OMIT_LOAD_EXTENSION=1  -c 
> sqlite3.c
> 
> 
> Another thing to check is that NDEBUG is defined.   SQLite makes
> significant use of asserts for debugging and testing, and defining
> NDEBUG will leave this code out.
> 
> HTH.
> -Shane


_
Invite your mail contacts to join your friends list with Windows Live Spaces. 
It's easy!
http://spaces.live.com/spacesapi.aspx?wx_action=create_url=/friends.aspx=en-us
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Subselect question

[Dan]

>> Here is some test data:
>> -
>> CREATE TABLE "TBOOKING" (
>> "ID" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
>> "EVENTTIMESTAMP" TIMESTAMP NOT NULL,
>> "EVENTTYPE" INTEGER NOT NULL,
>> "EMPLOYEE" INTEGER);
>>
>> INSERT INTO "TBOOKING" VALUES(42,'2008-09-22 09:19:35.000',3,NULL);
>> INSERT INTO "TBOOKING" VALUES(43,'2008-09-22 09:24:50.000',4,NULL);
>> INSERT INTO "TBOOKING" VALUES(44,'2008-09-22 10:43:03.000',3,NULL);
>> INSERT INTO "TBOOKING" VALUES(45,'2008-09-22 10:48:46.000',4,NULL);
>> INSERT INTO "TBOOKING" VALUES(46,'2008-09-22 11:56:56.000',3,NULL);
>> INSERT INTO "TBOOKING" VALUES(47,'2008-09-22 12:01:13.000',4,NULL);
>> INSERT INTO "TBOOKING" VALUES(48,'2008-09-22 14:23:05.000',3,NULL);
>> INSERT INTO "TBOOKING" VALUES(49,'2008-09-22 14:27:11.000',4,NULL);
>>
>> -
>> Here is the select for the view:
>> -
>>
>> SELECT
>>  A.ID AS ID1,
>>  A.EVENTTIMESTAMP AS TS1,
>>  A.EVENTTYPE AS ET1,
>> (SELECT B.ID FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND B.ID>A.ID
>> LIMIT 1) AS ID2,
>> (SELECT B.EVENTTIMESTAMP FROM TBOOKING AS B WHERE B.EVENTTYPE=4
>> AND B.ID>A.ID LIMIT 1) AS TS2,
>> (SELECT B.EVENTTYPE  FROM TBOOKING AS B WHERE B.EVENTTYPE=4 AND
>> B.ID>A.ID LIMIT 1) AS ET2
>> FROM TBOOKING AS A
>> WHERE A.EVENTTYPE=3;
>>
>> -
>> and here is the result:
>> -
>>
>> RecNo ID1 TS1 ET1 ID2 TS2 ET2
>> - --- --- --- --- --- ---
>>   1  43 22.09.2008 09:19:35   3  43 22.09.2008 09:24:50   4
>>   2  45 22.09.2008 10:43:03   3  45 22.09.2008 10:48:46   4
>>   3  47 22.09.2008 11:56:56   3  47 22.09.2008 12:01:13   4
>>   4  49 22.09.2008 14:23:05   3  49 22.09.2008 14:27:11   4
>>
>> -
>>
>> Have a look at the column ID1. It should contain the values
>> 42,44,46, and 48.
>>
>> Is this a bug, or am I doing something wrong?
>
> I think it's a bug.

   http://www.sqlite.org/cvstrac/chngview?cn=5855


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Convert integer IP address to string IP address in Sqlite

[dbikash]

This is not working for me. I just get the first octet.

(The concat function is also not supported?)

Thanks
dbikash


Igor Tandetnik wrote:
> 
> Jonathon <[EMAIL PROTECTED]> wrote:
>> I was just curious if there is a way to convert an integer
>> representation of an IP address that I store in my DB, to it's string
>> equivalent (xxx.xxx.xxx.xxx)?  I would also need to convert it to
>> host-byte order as well.
> 
> select (ip >> 24) || '.' || ((ip >> 16) & 255) || '.' || ((ip >> 8) & 
> 255) || '.' || (ip & 255)
> from mytable;
> 
> Igor Tandetnik 
> 
> 
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Convert-integer-IP-address-to-string-IP-address-in-Sqlite-tp20267362p20299412.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] create table default expr

[Simon Davies]

2008/11/3 John <[EMAIL PROTECTED]>:
> Hi
>
> I want to (if possible) create a table with a default timestamp in a
> format other than "-MM-DD ..." per example below. I have tried a few
> variants but always get same error.
>
> Can this be done and if so, how?
>
> create table (
>custnum integer primary key not null,
>note text,
>stamp default (strftime('%s', current_timestamp))
> );
> SQL error: near "(": syntax error
>
> --
> Regards
>John McMahon
>   [EMAIL PROTECTED]
>
>

>From http://www.sqlite.org/lang_createtable.html,
"The DEFAULT constraint specifies a default value to use when doing an
INSERT. The value may be NULL, a string constant or a number. "; using
a function as a default is not covered.

A trigger can probably achieve what you want :

  create table tst( id integer primary key, ts integer default
current_timestamp );
  create trigger tst_update_ts after insert on tst begin
  update tst set ts = case strftime( '%s', ts ) not null when 1
then strftime( '%s', ts ) else ts end where id=new.id;
  end;

  insert into tst( id ) values( null );
  insert into tst( id ) values( null );
  select * from tst;
1|1225703251
2|1225703259

  insert into tst values( null, 'my birthday' );
  select * from tst;
1|1225703251
2|1225703259
3|my birthday

Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] create table default expr

[John]

Hi

I want to (if possible) create a table with a default timestamp in a 
format other than "-MM-DD ..." per example below. I have tried a few 
variants but always get same error.

Can this be done and if so, how?

create table (
custnum integer primary key not null,
note text,
stamp default (strftime('%s', current_timestamp))
);
SQL error: near "(": syntax error

-- 
Regards
John McMahon
   [EMAIL PROTECTED]


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users