Re: [sqlite] undefined reference to sqlite3_mutex_held

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Daniel Mierswa wrote:
> Couldn't it be done with something like a sqlite3.h.in which gets
> preprocessed by autotools (relevant switches would be for example
> --enable-debug or something) and set constants which can then be used to
> opt in/out certain prototypes?

sqlite3.h is not generated from a .in template.  Are you volunteering to do
that and become the maintainer of it?

Secondly your solution would only work for autotools which not everyone uses.

Thirdly it wouldn't support the case of people supplying flags at the
command line to the compiler which is the usual case.

SQLite 3 is shipped with a pkg-config data file so you could try to get the
flags into that.

In any event futzing with the header file doesn't actually solve anything.
If you call a function that has no header then the compiler will whine a
little and then you'll still get a link time error.

The bottom line is that the presence of declarations in headers only alters
compiler warnings for C code and that at link time you are told about there
being an issue.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksgmOoACgkQmOOfHg372QQ6aQCglydXigRUE+6LEQyIgxzb3JWB
bTAAn1OksONBetlQZXY7wbPZ+hCAsWe6
=cYIW
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] undefined reference to sqlite3_mutex_held

[Daniel Mierswa]

On 10.12.2009 06:32, Roger Binns wrote:
> The header file has no idea what options you used when compiling the
> library.  This applies to much other functionality you can include/omit.
> 
> Roger
Couldn't it be done with something like a sqlite3.h.in which gets
preprocessed by autotools (relevant switches would be for example
--enable-debug or something) and set constants which can then be used to
opt in/out certain prototypes?

-- 
Mierswa, Daniel

If you still don't like it, that's ok: that's why I'm boss. I simply
know better than you do.
   --- Linus Torvalds, comp.os.linux.advocacy, 1996/07/22
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] BUG: The FTS3 is broken in reliase 3.6.21

[Alexey Pechnikov]

Hello!

In new relise doesn't some queries. The simplest non-working query is like to:

sqlite> select 1 from file_text where rowid=1 and file_text match 'document';
Error: SQL logic error or missing database

sqlite> select 1 from file_text where docid=1 and file_text match 'document';
Error: SQL logic error or missing database

In previous versions these work fine.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] undefined reference to sqlite3_mutex_held

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Daniel Mierswa wrote:
> Though I wonder why the header does not hide the prototype when it gets
> installed into the system.

The header file has no idea what options you used when compiling the
library.  This applies to much other functionality you can include/omit.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksgh98ACgkQmOOfHg372QSoHwCgvO6apUZsWW0WwM+X840LfKrj
+qcAnjiz18sa53okZKZStAcEHXtI9Ct8
=Id0Q
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The trace command show system queriel like analyze, vacuum...

[Alexey Pechnikov]

Hello!

On Thursday 10 December 2009 04:04:34 Roger Binns wrote:
>

Thanks! The great idea.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] undefined reference to sqlite3_mutex_held

[Daniel Mierswa]

On 10.12.2009 05:42, Dan Kennedy wrote:
> sqlite3_mutex_held() is only defined if SQLite is built with
> SQLITE_DEBUG defined. Looks like this was not the case when
> the library linked to by -lsqlite3 above was compiled.
I wrote exactly that in my initial mail to the list.
Though I wonder why the header does not hide the prototype when it gets
installed into the system.

-- 
Mierswa, Daniel

If you still don't like it, that's ok: that's why I'm boss. I simply
know better than you do.
   --- Linus Torvalds, comp.os.linux.advocacy, 1996/07/22
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] undefined reference to sqlite3_mutex_held

[Dan Kennedy]

On Dec 10, 2009, at 11:19 AM, Daniel Mierswa wrote:

> On 10.12.2009 01:22, Shawn Wilsher wrote:
>> See https://bugzilla.mozilla.org/show_bug.cgi?id=533171 (this isn't  
>> really a
>> SQLite issue).
> I'm not sure what you mean. I can reproduce it without any Thunderbird
> code involved at all.
>
> impu...@istari ~ $ echo -e "#include  \n int main() {
> sqlite3_mutex_held(0); return 0; }" | gcc -xc - -lsqlite3
> /tmp/ccRHfvsh.o: In function `main':
> :(.text+0xa): undefined reference to `sqlite3_mutex_held'
> collect2: ld returned 1 exit status

sqlite3_mutex_held() is only defined if SQLite is built with
SQLITE_DEBUG defined. Looks like this was not the case when
the library linked to by -lsqlite3 above was compiled.

Dan.


>
> -- 
> Mierswa, Daniel
>
> If you still don't like it, that's ok: that's why I'm boss. I simply
> know better than you do.
>   --- Linus Torvalds, comp.os.linux.advocacy, 1996/07/22
> ___
> 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

Re: [sqlite] undefined reference to sqlite3_mutex_held

[Daniel Mierswa]

On 10.12.2009 01:22, Shawn Wilsher wrote:
> See https://bugzilla.mozilla.org/show_bug.cgi?id=533171 (this isn't really a
> SQLite issue).
I'm not sure what you mean. I can reproduce it without any Thunderbird
code involved at all.

impu...@istari ~ $ echo -e "#include  \n int main() {
sqlite3_mutex_held(0); return 0; }" | gcc -xc - -lsqlite3
/tmp/ccRHfvsh.o: In function `main':
:(.text+0xa): undefined reference to `sqlite3_mutex_held'
collect2: ld returned 1 exit status

-- 
Mierswa, Daniel

If you still don't like it, that's ok: that's why I'm boss. I simply
know better than you do.
   --- Linus Torvalds, comp.os.linux.advocacy, 1996/07/22
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] .importing file into a BLOB

[Jean-Christophe Deschamps]

Ted,

>Is it the command-line program misinterpreting the CR/LFs?

Unfortunately, any command-line tool will interpret control characters 
at their face value when they are fed directly.

With no ad hoc program doing it for you, the solution, as I've 
discussed at length before and even if it is far from optimal, is to 
use a command-line tool or combination thereof that will open your 
binary file, read each byte in turn and output its hex value in plain 
ASCII characters, until end of file.  That (possibly long) chain of 
text can be stored as a single BLOB without any problem using for 
instance an SQL file fed to sqlite3.exe.

Another much simpler possibility is to use one of the good SQLite 
managers available and use it to load the data/file in the cell where 
it belongs.  It all depends if you need to do that in a very small 
number of instances or if you have to perform such loading routinely.


>I'm reluctantly on a Windows system.

Don't feel like a rebel, despite its problems you can have some work done.

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

Re: [sqlite] Using pivot table

[Igor Tandetnik]

Walter  wrote:
> I have the following tables
> 
> CREATE TABLE data(
> id   INTEGER PRIMARY KEY NOT NULL,
> name TEXT DEFAULT '',
> titleTEXT DEFAULT '',
> 
> )
> 
> 
> CREATE TABLE pivot(
> id   INTEGER PRIMARY KEY  NOT NULL,
> link1INTEGER DEFAULT  0,
> link2INTEGER DEFAULT  0,
> rank INTEGER DEFAULT  0,
> mdateMYDATE  DEFAULT  0,
> status   TEXTDEFAULT  '',
> )
> 
> link1 and link2 are id's from tbl1
> 
> With this sql I get half way to what I want
> 
> SELECT name,title,pivot.id AS id,mdate,status
> FROM data,pivot
> WHERE data.id=pivot.link1
> ORDER BY name,pivot.rank
> 
> How do I get the name,title from data onto the same row
> 
> WHERE data.id=pivot.link1  and pivot.link2=a different data.id
> this obviously does not work

select d1.name, d1.title, d2.name. d2.title, pivot.id, mdate, status
from pivot join data d1 on (pivot.link1 = d1.id) join data d2 on (pivot.link2 = 
d2.id)
order by d1.name, pivot.rank;

Igor Tandetnik

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

[sqlite] Using pivot table

[Walter]

I have the following tables

CREATE TABLE data(
id   INTEGER PRIMARY KEY NOT NULL,
name TEXT DEFAULT '',
titleTEXT DEFAULT '',

)


CREATE TABLE pivot(
id   INTEGER PRIMARY KEY  NOT NULL,
link1INTEGER DEFAULT  0,
link2INTEGER DEFAULT  0,
rank INTEGER DEFAULT  0,
mdateMYDATE  DEFAULT  0,
status   TEXTDEFAULT  '',
)

link1 and link2 are id's from tbl1

With this sql I get half way to what I want

SELECT name,title,pivot.id AS id,mdate,status
FROM data,pivot
WHERE data.id=pivot.link1
ORDER BY name,pivot.rank

How do I get the name,title from data onto the same row

WHERE data.id=pivot.link1  and pivot.link2=a different data.id
this obviously does not work

My knowledge of SQL is limited

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

Re: [sqlite] .importing file into a BLOB

[Simon Slavin]

.import imports data from a file in CSV format.  In a CSV file a newline 
indicates a new record.  If you want all your data to be in one record have all 
your data on one line.

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

Re: [sqlite] .importing file into a BLOB

[Ted Rolle, Jr.]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Wed, 09 Dec 2009 11:48:31 -0800
Roger Binns  wrote:

> http://catb.org/~esr/faqs/smart-questions.html

What wasn't clear?  I apologize for any murkiness.
My understanding is that a BLOB takes any piece of data and stores it
away intact.
Is it the command-line program misinterpreting the CR/LFs?
I'm reluctantly on a Windows system.  I have no C compiler.  PHP might
be an option, but I can't get PDO to work.

Any direction is appreciated.

Ted
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.12 (MingW32)

iQGcBAEBAgAGBQJLIGBFAAoJEAWk0Z459zwzd0gL/2vAgDbGJ0H2NBnv+ajEsRhj
PaaevYyTRQOg+8ndZhtzQ1Fw6X579e3gi1k+zrvfDxmPIVpXk22y1Vf2olNAAmpR
EuhJI7oxghMuKYGCj1nX8MnjdyDGn3az/7kYBkhFVJhEya7rLKzc+7BHkr+zt3x9
H3JoMMxoWEhPtwu75nbjAtUbOLHsKZWv3UYFUrqr5WoqBXbUNwl9Ld7zprOob2cD
H71EyiR7ulItrdSF8kdob1OTptNxSiek1uFgaidimNYhTsh+vEBCoH5hFzVc8tSQ
uoHZ21c+4tNgRVgRfMcfsBU4a/Ryzrl8V3+qibMuna+UAPoEN5Xcztb/mHWXsyj6
P96JpV86I786rZOsx4etGYlCGmFnpWDzbo563nP9gxs8WF8dwXnk8udpHua+2u9E
CUXyODmMhGQVY7p+b7ApwcCu4qon43kLG63XHpajnaHaO97Y8z35iFu96zFLXMbD
fU3LyVPCw8Go2l/p4s75vhV8smkuIOhmDt1mjv9LGg==
=W7xS
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The trace command show system queriel like analyze, vacuum...

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Alexey Pechnikov wrote:
> I want to build a universal extension for logging all user queries. I can 
> write
> this with trace function but it's difficult or impossible to split system and
> user queries.

There are two ways that can be done.  The first is to require your extension
and its API be used at which you can then filter and log as needed.

The way I would go about it is not using a SQLite extension, but instead
provide a shared library with logging functions.  You can ensure the shared
library is loaded before SQLite (use LD_PRELOAD on Linux/Unix and something
similar on Windows) and hook the SQLite APIs.  This requires no change to
the application.

For apswtrace I can output all SQL and bindings, rows returned, how long
each query took, most popular queries, queries that took the longest
(individually and ones that have the same text) etc.  This doesn't require
any code changes in the Python application.

This unobtrusive mechanism also allows you to get more detailed.  For
example you can track how often SQLite is busy (install a busy handler,
transparently call the user one if needed).  You can even use it to fake
things.  For example historically this mechanism has been used to fake the
current time, but you could fake busy results to help with testing.  If you
also intercept read and write system calls you can even correspond I/O
activity to the queries that cause it.  You can even capture stack
backtraces when tying them to SQL calls.  There are so many possibilities.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksgSSIACgkQmOOfHg372QRXzgCfUfQ/9uG0KBMIC5876ATFkgDC
y08An0GalZmCSVg23pNBTSSrGKSilPm3
=zPiv
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The trace command show system queriel like analyze, vacuum...

[Alexey Pechnikov]

Hello!

On Thursday 10 December 2009 02:36:25 Simon Slavin wrote:
> Do not rely on the operation of 'trace' in production code.  It's a debugging 
> feature only.

But why? I think with additional query type argument the trace function will be 
useful in 
production for database logging and real-time audit tasks. I don't see any 
reasons to create
the equal functional in applications.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] undefined reference to sqlite3_mutex_held

[Shawn Wilsher]

See https://bugzilla.mozilla.org/show_bug.cgi?id=533171 (this isn't really a
SQLite issue).

Cheers,

Shawn

On Wed, Dec 9, 2009 at 3:32 PM, Daniel Mierswa  wrote:

> Hi list,
> when I try to build the current thunderbird 3.0 release against
> sqlite-3.6.21 i get an undefined reference to sqlite3_mutex_held, with
> 3.6.19 that didn't occur. When i compile sqlite3 with -DSQLITE_DEBUG the
> symbol is exported, now I wonder if this symbol is not supposed to be
> exported in a release build, in whch case the header should reflect
> that, or if it's a bug in the current source? Or am I totally off?
> Thanks for reading.
>
> --
> Mierswa, Daniel
>
> If you still don't like it, that's ok: that's why I'm boss. I simply
> know better than you do.
>   --- Linus Torvalds, comp.os.linux.advocacy, 1996/07/22
> ___
> 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

Re: [sqlite] The trace command show system queriel like analyze, vacuum...

[Simon Slavin]

On 9 Dec 2009, at 11:16pm, Alexey Pechnikov wrote:

> I want to build a universal extension for logging all user queries. I can 
> write
> this with trace function but it's difficult or impossible to split system and
> user queries.

Do not rely on the operation of 'trace' in production code.  It's a debugging 
feature only.

If you want to log all queries, supply your own routine for executing SELECT, 
and make it log the SELECT before it applies it.

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

Re: [sqlite] iPhone App close database

[Simon Slavin]

On 9 Dec 2009, at 11:13pm, Greg Walters wrote:

> I am looking for some general information regarding closing the sqlite 
> database when used from an iPhone app. Since you never know when you app will 
> go away, when do you close the database. I have found one example application 
> that closes the database after getting data and doing updates. Most never 
> close the database. What is the best way to handle this so in an iPhone app. 

Your application does get warned about important like that, and is expected to 
provide a routine to respond to each warning.  Look for

applicationWillTerminate

for quitting, which is the question you asked.  But also look for

applicationWillResignActive
applicationDidBecomeActive

since, if possible without too much extra work, I'd recommend that you also 
close files while the iPhone is handling external interruptions like phone 
calls, SMS notifications, and 'push' notifications.  You might find this useful:

http://developer.apple.com/iPhone/library/documentation/iPhone/Conceptual/iPhoneOSProgrammingGuide/ApplicationEnvironment/ApplicationEnvironment.html#//apple_ref/doc/uid/TP40007072-CH7-SW6

(that's all one long URL).

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

[sqlite] undefined reference to sqlite3_mutex_held

[Daniel Mierswa]

Hi list,
when I try to build the current thunderbird 3.0 release against
sqlite-3.6.21 i get an undefined reference to sqlite3_mutex_held, with
3.6.19 that didn't occur. When i compile sqlite3 with -DSQLITE_DEBUG the
symbol is exported, now I wonder if this symbol is not supposed to be
exported in a release build, in whch case the header should reflect
that, or if it's a bug in the current source? Or am I totally off?
Thanks for reading.

-- 
Mierswa, Daniel

If you still don't like it, that's ok: that's why I'm boss. I simply
know better than you do.
   --- Linus Torvalds, comp.os.linux.advocacy, 1996/07/22
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] pthreadMutex

[Luiz Gustavo P Tonello]

Thanks Thomas,

My problem was when I compile the code with gcc

I used like this:
# gcc sqlite3.c code.c -o test -lpthread -ldl

Work fine!

Thanks again.

Gustavo Tonello

On Dec 9, 2009, at 8:52 PM, Thomas Mittelstaedt  wrote:

> Luiz,
>
> You may try to compile with -pthread in the compile flags.
>
> Am Mittwoch, den 09.12.2009, 00:12 -0200 schrieb Luiz Gustavo P  
> Tonello:
>> undefined reference to `pthread_mutexattr_init'
>
> -- 
> thomas
>
>
>
> ___
> 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

Re: [sqlite] pthreadMutex

[Luiz Gustavo P Tonello]

Gustavo Tonello

On Dec 9, 2009, at 8:52 PM, Thomas Mittelstaedt  wrote:

> Luiz,
>
> You may try to compile with -pthread in the compile flags.
>
> Am Mittwoch, den 09.12.2009, 00:12 -0200 schrieb Luiz Gustavo P  
> Tonello:
>> undefined reference to `pthread_mutexattr_init'
>
> -- 
> thomas
>
>
>
> ___
> 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

Re: [sqlite] The trace command show system queriel like analyze, vacuum...

[Alexey Pechnikov]

Hello!

On Thursday 10 December 2009 01:36:39 Roger Binns wrote:
> My point was that if you only want to log queries your code makes then alter
> your code.  For example instead of calling sqlite3_prepare/bind directly you
> can make wrapper functions that log the query and bindings.  And because you
> are doing it, you can log them in any way that makes sense to your code.
> 
> It should be clear that SQLite is a library.  And just like any library, how
> you use it is entirely under your control.

I want to build a universal extension for logging all user queries. I can write
this with trace function but it's difficult or impossible to split system and
user queries.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] iPhone App close database

[Greg Walters]

Hi,
I am looking for some general information regarding closing the sqlite database 
when used from an iPhone app. Since you never know when you app will go away, 
when do you close the database. I have found one example application that 
closes the database after getting data and doing updates. Most never close the 
database. What is the best way to handle this so in an iPhone app. 
Thanks,
Greg



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

Re: [sqlite] pthreadMutex

[Thomas Mittelstaedt]

Luiz,

You may try to compile with -pthread in the compile flags.

Am Mittwoch, den 09.12.2009, 00:12 -0200 schrieb Luiz Gustavo P Tonello:
> undefined reference to `pthread_mutexattr_init'

-- 
thomas



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

Re: [sqlite] The trace command show system queriel like analyze, vacuum...

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

> The second argument with query type [user|system] for trace function
> will be very helpful. And third argument for profile function. But may be
> exists another way for logging only user queries?..

I'm sorry but I can not understand what you are saying.

My point was that if you only want to log queries your code makes then alter
your code.  For example instead of calling sqlite3_prepare/bind directly you
can make wrapper functions that log the query and bindings.  And because you
are doing it, you can log them in any way that makes sense to your code.

It should be clear that SQLite is a library.  And just like any library, how
you use it is entirely under your control.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksgJncACgkQmOOfHg372QS/0gCfV9uyg6xWlaQbCLH3z6fIgLvQ
bgoAn3SUciKIrz97+iLq26Yluc9mxHbY
=O0N1
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The trace command show system queriel like analyze, vacuum...

[Alexey Pechnikov]

Hello!

On Wednesday 09 December 2009 22:49:53 Roger Binns wrote:
> Since you are the "user" you can do your own logging to the SQLite API
> recording whatever information you want.

The second argument with query type [user|system] for trace function
will be very helpful. And third argument for profile function. But may be
exists another way for logging only user queries?..

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The trace command show system queriel like analyze, vacuum...

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Alexey Pechnikov wrote:
> As you can see internal system queries are visualized too. It's may be useful 
> for developers but very bad for users becouse it's impossible to log only 
> user-applied queries.

Since you are the "user" you can do your own logging to the SQLite API
recording whatever information you want.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAksf/2EACgkQmOOfHg372QQVbQCaAgGTw25EuavI5pwO8qZWPztJ
BSMAoMq0TQfCqLN5xy2Jwt0l9J+4/PNO
=RF+O
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] USING bug

[Wilson, Ronald]

> > On Dec 9, 2009, at 3:42 AM, Wiktor Adamski wrote:
> > 
> > SQLite version 3.6.21
> > Enter ".help" for instructions
> > Enter SQL statements terminated with a ";"
> > sqlite> create table t1(a int);
> > sqlite> create table t2(a int);
> > sqlite> create table t3(a int, b int);
> > sqlite> insert into t1 values(1);
> > sqlite> insert into t3 values(1, 1);
> > sqlite> select * from t1 left join t2 using(a) left join t3
using(a);
> > 1|
> > sqlite> select * from (t1 left join t2 using(a)) left join t3
> > using(a);
> > 1|
> >
> > I think that correct result should be
> > 1|1
> > MySQL and PostgreSQL agree with me.
> 
> Cannot reproduce this problem here:
> 
>sqlite> d...@computer2:~/work/sqliteG/bld$ ./sqlite3
>SQLite version 3.6.21
>Enter ".help" for instructions
>Enter SQL statements terminated with a ";"
>sqlite> CREATE TABLE t1(a int);
>sqlite> CREATE TABLE t2(a int);
>sqlite> CREATE TABLE t3(a int, b int);
>sqlite> INSERT INTO t1 VALUES(1);
>sqlite> INSERT INTO t2 VALUES(1);
>sqlite> INSERT INTO t3 VALUES(1, 1);
>sqlite> SELECT * FROM t1 LEFT JOIN t2 USING(a) LEFT JOIN t3
USING(a);
>1|1
>sqlite> SELECT * FROM (t1 LEFT JOIN t2 USING(a)) LEFT JOIN t3
> USING(a);
>1|1
> 
> 
> Dan.
> 

Dan's SQL has an extra insert.

SQLite version 3.6.18
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table t1(a int);
sqlite> create table t2(a int);
sqlite> create table t3(a int, b int);
sqlite> insert into t1 values(1);
sqlite> insert into t3 values(1,1);
sqlite> select * from t1 left join t2 using(a) left join t3 using(a);
1|
sqlite> select * from (t1 left join t2 using(a)) left join t3 using(a);
1|
sqlite> insert into t2 values(1);
sqlite> select * from t1 left join t2 using(a) left join t3 using(a);
1|1
sqlite> select * from (t1 left join t2 using(a)) left join t3 using(a);
1|1
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-dev] query on sqlite3_open16

[William Bardwell]

> According to documentation on CreateFile() function: "In the ANSI
> version of this function, the name is limited to MAX_PATH characters.
> To extend this limit to 32,767 wide characters, call the Unicode
> version of the function and prepend "\\?\" to the path."
> 
> SQLite is already calling CreateFileW() function so all you need is to
> prepend your file name with "\\?\".

Last time I tried this (1 year or so) that was not enough, you also had to
compile with an increased max path define because the IO system.  I
reported a bug on it at the time.

William Bardwell
wbard...@curl.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to show blob data while select in sqlite3 shell?

[liubin liu]

Thanks!!!


SimonDavies wrote:
> 
> 2009/12/9 liubin liu <7101...@sina.com>:
>>
>> sqlite> INSERT OR REPLACE INTO periods_value VALUES (0, 1,
>> x'000102030400a0afaabbaa');
>> sqlite>
>> sqlite> SELECT * FROM periods_value;
>> 0|1|
>> sqlite>
>>
>> how to show the blob data?
> 
> Select id, valid, hex( value ) from periods_value;
> 
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://old.nabble.com/how-to-show-blob-data-while-select-in-sqlite3-shell--tp26709509p26709778.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] Commit failing due to DB locked.

[John Clayton]

Hi
Thanks for responding, I've added my comments in below.
On Dec 9, 2009, at 11:30 AM, Kees Nuyt wrote:

> On Tue, 08 Dec 2009 22:07:34 +0100, John Clayton
>  wrote:
>
>> Hi
>>
>> I've got two processes opening up two sqlite databases.  Assuming the
>> databases are called A and B respectively, then both processes do  
>> this:
>>  using A, BEGIN IMMEDIATE
>>  using B, BEGIN IMMEDIATE
>>  ... do some stuff possibly involving A ...
>>  using B, COMMIT
>>  using A, COMMIT
>>
>> In a couple of cases, I've seen one of the processes get an exception
>> on COMMIT, the error is 'DB is locked'.  I had thought that BEGIN
>> IMMEDIATE resulted in a write lock being taken out on the DB, and
>> therefore it should be possible to get a locking error on commit.
>>
>> Has anyone seen this kind of locking error or have
>> suggestions as to how I could track it down?
>
> First a few questions:
>
> - Which version of SQLite?

3.6.11

> - On what platform?

Mac OS X

> - On what filesystem type is the database located?

A local disk

> - Are any PRAGMA's used?

Nope

> - Do you use the sqlite3 C API, the sqlite3

C & C++, the sqlite3xx API is what we mostly use

>  command line tool, or some wrapper?
> - Did you access any other databases than A or B?
>

Yes.  I should have mentioned that.  One of these two processes locks  
the DB slightly differently.  It does this:
create a NEW DB, lets call it C
attach to A
attach to B
BEGIN IMMEDIATE
copy all table content from A into C
COMMIT
detach B
detach A

> You could try PRAGMA locking_mode=EXCLUSIVE or start your
> transaction with BEGIN EXCLUSIVE.

How is that different from BEGIN IMMEDIATE, I understood that to have  
the same affect.

>
>> Thanks
> -- 
>  (  Kees Nuyt
>  )
> c[_]
> ___
> 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

Re: [sqlite] how to show blob data while select in sqlite3 shell?

[Simon Davies]

2009/12/9 liubin liu <7101...@sina.com>:
>
> sqlite> INSERT OR REPLACE INTO periods_value VALUES (0, 1,
> x'000102030400a0afaabbaa');
> sqlite>
> sqlite> SELECT * FROM periods_value;
> 0|1|
> sqlite>
>
> how to show the blob data?

Select id, valid, hex( value ) from periods_value;

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

Re: [sqlite] BUG: the rowid column in view is automatically named as id

[Alexey Pechnikov]

Hello!

On Wednesday 09 December 2009 15:07:16 Pavel Ivanov wrote:
> It's been said in this list not once already: unless you're using "as
> ..." to name the column it's not guaranteed to have any particular
> name you expect it to. So it's not a bug.

This is bug becouse the column with alias will be renamed. As example:

sqlite> create table test (id INTEGER PRIMARY KEY, a text);
sqlite> insert into test (a) values ('test');
sqlite> create view view_test as select rowid,a as id from test;
sqlite> .header on
sqlite> select * from view_test;
id|id:1
1|test

The result is wrong becouse fileld "id"  must be equal to 'test'.

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] how to show blob data while select in sqlite3 shell?

[liubin liu]

[...@** createdb]$ 
[...@** createdb]$ sqlite3 ./db_ctrl_0.1.db
SQLite version 3.6.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE periods_value (id INTEGER PRIMARY KEY, valid CHAR(1),
value BLOB );
sqlite> 
sqlite> INSERT OR REPLACE INTO periods_value VALUES (0, 1,
x'000102030400a0afaabbaa');
sqlite> 
sqlite> SELECT * FROM periods_value;
0|1|
sqlite> 



how to show the blow data?
-- 
View this message in context: 
http://old.nabble.com/how-to-show-blob-data-while-select-in-sqlite3-shell--tp26709509p26709509.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] [sqlite-dev] query on sqlite3_open16

[Pavel Ivanov]

According to documentation on CreateFile() function: "In the ANSI
version of this function, the name is limited to MAX_PATH characters.
To extend this limit to 32,767 wide characters, call the Unicode
version of the function and prepend "\\?\" to the path."

SQLite is already calling CreateFileW() function so all you need is to
prepend your file name with "\\?\".


Pavel

On Wed, Dec 9, 2009 at 12:52 AM, Kishore Ulligadda
 wrote:
> Dear sqlite,
>
>
>
> I am  faching problem with below the API,
>
>
>
> sqlite3dll_open16(path, )
>
>
>
> Problem Statement:
>
> When I try to open the .dat file where the length of the Path is 250
> (including filename- length  croses 256 ), the API returns the error value =
> 14 i.e. Unable to open the database
>
>
>
> Kindly provide any alternative solution (or) api’s available for accessing
> the “dat” file where the limitation of the length is around 1024.
>
>
>
> Thanks and Regards,
>
> Kishore.U
>
> 
> http://www.mindtree.com/email/disclaimer.html
>
> ___
> sqlite-dev mailing list
> sqlite-...@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-dev
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] BUG: the rowid column in view is automatically named as id

[Pavel Ivanov]

It's been said in this list not once already: unless you're using "as
..." to name the column it's not guaranteed to have any particular
name you expect it to. So it's not a bug.

Also:

sqlite> create table test (id INTEGER PRIMARY KEY, a text);
sqlite> insert into test (a) values (1);
sqlite> .h on
sqlite> select rowid, a from test;
id|a
1|1
sqlite> select rowid as rowid, a from test;
rowid|a
1|1


Pavel

On Wed, Dec 9, 2009 at 6:47 AM, Alexey Pechnikov  wrote:
> Hello!
>
> $ sqlite3
> SQLite version 3.6.20
>
> sqlite> create table test (id INTEGER PRIMARY KEY, a text);
> sqlite> insert into test (a) values (1);
> sqlite> create view view_test as select rowid,* from test;
> sqlite> .header on
> sqlite> select * from view_test;
> id|id:1|a
> 1|1|1
>
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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

[sqlite] BUG: the rowid column in view is automatically named as id

[Alexey Pechnikov]

Hello!

$ sqlite3
SQLite version 3.6.20

sqlite> create table test (id INTEGER PRIMARY KEY, a text);
sqlite> insert into test (a) values (1);
sqlite> create view view_test as select rowid,* from test;
sqlite> .header on
sqlite> select * from view_test;
id|id:1|a
1|1|1


Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] USING bug

[Dan Kennedy]

On Dec 9, 2009, at 3:42 AM, Wiktor Adamski wrote:

> SQLite version 3.6.21
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> create table t1(a int);
> sqlite> create table t2(a int);
> sqlite> create table t3(a int, b int);
> sqlite> insert into t1 values(1);
> sqlite> insert into t3 values(1, 1);
> sqlite> select * from t1 left join t2 using(a) left join t3 using(a);
> 1|
> sqlite> select * from (t1 left join t2 using(a)) left join t3  
> using(a);
> 1|
>
> I think that correct result should be
> 1|1
> MySQL and PostgreSQL agree with me.

Cannot reproduce this problem here:

   sqlite> d...@computer2:~/work/sqliteG/bld$ ./sqlite3
   SQLite version 3.6.21
   Enter ".help" for instructions
   Enter SQL statements terminated with a ";"
   sqlite> CREATE TABLE t1(a int);
   sqlite> CREATE TABLE t2(a int);
   sqlite> CREATE TABLE t3(a int, b int);
   sqlite> INSERT INTO t1 VALUES(1);
   sqlite> INSERT INTO t2 VALUES(1);
   sqlite> INSERT INTO t3 VALUES(1, 1);
   sqlite> SELECT * FROM t1 LEFT JOIN t2 USING(a) LEFT JOIN t3 USING(a);
   1|1
   sqlite> SELECT * FROM (t1 LEFT JOIN t2 USING(a)) LEFT JOIN t3  
USING(a);
   1|1


Dan.

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

Re: [sqlite] Commit failing due to DB locked.

[Kees Nuyt]

On Tue, 08 Dec 2009 22:07:34 +0100, John Clayton
 wrote:

>Hi
>
> I've got two processes opening up two sqlite databases.  Assuming the  
> databases are called A and B respectively, then both processes do this:
>   using A, BEGIN IMMEDIATE
>   using B, BEGIN IMMEDIATE
>   ... do some stuff possibly involving A ...
>   using B, COMMIT
>   using A, COMMIT
>
> In a couple of cases, I've seen one of the processes get an exception  
> on COMMIT, the error is 'DB is locked'.  I had thought that BEGIN  
> IMMEDIATE resulted in a write lock being taken out on the DB, and  
> therefore it should be possible to get a locking error on commit.
>
> Has anyone seen this kind of locking error or have 
> suggestions as to how I could track it down?

First a few questions:

- Which version of SQLite?
- On what platform?
- On what filesystem type is the database located?
- Are any PRAGMA's used?
- Do you use the sqlite3 C API, the sqlite3 
  command line tool, or some wrapper?
- Did you access any other databases than A or B?

You could try PRAGMA locking_mode=EXCLUSIVE or start your
transaction with BEGIN EXCLUSIVE.

>Thanks
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.21

[Michael Knigge]

>> And what compiler are you using that is sensitive to these violations
>> yet generates no warnings?
> 
> It's the very nature of undefined behaviour that it does not require a
> warning.  Welcome to the world of C.

Is the compiler you use a secret? Do you have to kill us if you tell the 
name/version of the compiler to us? Or why don't you give this (maybe) 
important information?


Bye,
Michael

P.S.: Have you changed the compiler Options? Maybe you've played around 
with the "-O" option?

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