Re: [sqlite] Column names in a UNION

[Rob Lohman]

Microsoft SQL seems to use the column names from the
first select. So that would be "a, b" in your example.

Rob

- Original Message - 
From: <[EMAIL PROTECTED]>

To: "Sqlite-users" 
Sent: Saturday, March 25, 2006 3:45 PM
Subject: [sqlite] Column names in a UNION



Who can tell me what the "correct" column names should be
for a UNION.  For example:

 SELECT a, b FROM t1 UNION SELECT x, y FROM t2;

The result set of the query above has two columns.  Should
those columns be named a and b or x and y?

Does anybody know what the SQL standard says?  Do all the
other SQL database engines get it right or is there some
disagreement?

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

Re: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB in different threads

[Rob Lohman]

Hi Ralf,

If I remember correctly you cannot use a SQLite database handle
across threads. Each thread will need to open (and close) the
database itself.

Cheers,

Rob

- Original Message - 
From: "Ralf Junker" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, March 21, 2006 12:02 PM
Subject: [sqlite] SQLITE_ENABLE_MEMORY_MANAGEMENT: AV when Open / Close DB 
in different threads




Hello,

I am using SQLite compiled with SQLITE_ENABLE_MEMORY_MANAGEMENT on Win32.

I execute the following psydocode, all with same DB handle:

* In application's main thread: sqlite3_open
* Create a new thread
* In new thread: sqlite3_close

This creates an access violation in pager.c, lines 2065 to 2076:

#ifdef SQLITE_ENABLE_MEMORY_MANAGEMENT
 /* Remove the pager from the linked list of pagers starting at
 ** ThreadData.pPager if memory-management is enabled.
 */
 if( pPager==pTsd->pPager ){
   pTsd->pPager = pPager->pNext;
 }else{
   Pager *pTmp;
   for(pTmp = pTsd->pPager; pTmp->pNext!=pPager; pTmp=pTmp->pNext);
   pTmp->pNext = pPager->pNext;
 }
#endif

While I understand from the FAQ that it might be problematic to use more 
than one thread with SQLITE_ENABLE_MEMORY_MANAGEMENT, I wonder if SQLite 
should cause an AV in this case?


I even found that other SQL instructions, like INSERT, work fine when 
called from the 2nd thread.


Could anybody help, please?

Thanks & regards,

Ralf

Re: [sqlite] SQLITE3.DLL fails to load if address 0x60900000 occupied and DLL recompilation

[Rob Lohman]

Hi,

According to that ticket it is fixed in the latest
download on the website. So there should be
no need to recompile yourself unless it is an
older version?

Cheers,

Rob

- Original Message - 
From: "Clinco, Michele" <[EMAIL PROTECTED]>

To: 
Sent: Tuesday, March 21, 2006 9:37 AM
Subject: [sqlite] SQLITE3.DLL fails to load if address 0x6090 occupied 
and DLL recompilation



Hallo.



After I changed my development machine, I was not able any more to load
the SQLITE3.DLL in my .Net application.



Looking around, I found this article that describes the problem.



http://www.sqlite.org/cvstrac/tktview?tn=1474



The solution is to recompile the DLL with visual studio:



I created a new project with Visual Studio 2005 and I recompiled the
DLL, everything works.

There are two things I'm a bit worried about:

The first is the huge number of parameters of the compiler, maybe they
are right, maybe they are wrong, but how can we know it in a
deterministic way?

The second is the number of warnings I received in the compilation: 144.
The warnings are of two categories, deprecated functions (sprintf )
and unsafe typecast (signed/unsigned, int/double)



Is there anybody who already did this compilation?



Bye, Michele

Re: [sqlite] sqlite_get_table performance problems

[Rob Lohman]

Hi,

I suspect "your network" (solution) is to blame. The client
probably needs to (re)connect to your server or (re-)
authenticate etc.

This can easily be tested by running the application and
database on the same system. Is the first query does not
take 2 seconds you have your answer.

Keep in mind that SQLite is not a client-server system,
it is more like an embedded database system.

To me it feels like you are trying to use a product in a
way it was not designed and I would expect problems
like these from that.

Good luck,

Rob

- Original Message - 
From: "Patrik Svensson" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, March 08, 2006 2:40 PM
Subject: [sqlite] sqlite_get_table performance problems



Hi,

I have BIG BIG problems with my database (sqlite2).

I have a 50Mb database located on a server.
From my client I run a quite large select statement using the
sqlite_get_table function.

The first time this select is issued it takes about 2 minutes to get the
result.
If I run the same select again it will take approx 1 second.
Then after a while it suddenly can take 2 minutes again.

I guess this must be some caching problem but I'm stuck and 2 minutes is 
NOT

acceptable even for the first select.

Can someone PLEASE PLEASE PLEASE help me?

Cheers,
Patrik

Re: [sqlite] Failing Transaction Help.

[Rob Lohman]

If I'm not mistaken you still need to close the transaction.
Are you doing an "end transaction" even if a statement
fails (ie, a rollback is done)?

- Original Message - 
From: "nbiggs" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, March 01, 2006 10:24 PM
Subject: [sqlite] Failing Transaction Help.



In my application, I am using a transaction to insert about 10 records
at a time.  The problem is that if one of the statements in the
transaction fail, commit is not being executed.  When I try creating
another transaction, I get a constant error message "can not create a
transaction within a transaction".  How do I get around this issue?

Nathan Biggs
Computerway Food Systems
(336) 841-7289

Re: [sqlite] How to unsubscribe?

[Rob Lohman]

That information was given (in an email) when you subscribed:

To remove your address from the list, just send a message to
the address in the ``List-Unsubscribe'' header of any list
message. If you haven't changed addresses since subscribing,
you can also send a message to:
  <[EMAIL PROTECTED]>

- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Friday, January 13, 2006 5:12 PM
Subject: [sqlite] How to unsubscribe?




Hello,

When I visit http://www.sqlite.org/support.html there is plenty of 
information about joining this list but nothing about how to unsubscribe.


Anyone know how to do that?

Thanks,

Tom

Re: [sqlite] Regarding String Comparision

[Rob Lohman]

It seems we are both right :)

sqlite> create table test (filename varchar(1000) primary key);
sqlite> insert into test (filename) values ('test');
sqlite> select * from test where filename='test';
test
sqlite> select * from test where filename='tesT';
sqlite> select * from test where filename like 'tesT';
test
sqlite> insert into test (filename) values ('testing');
sqlite> select * from test where filename like 'tesT';
test

In other words, when doing field = 'value' it is case sensitive,
with a field like 'value' it isn't. If you don't use '%value%' it
will do an exact match (it seems).

This was tested on 3.2.1

- Original Message - 
From: "Brandon, Nicholas" <[EMAIL PROTECTED]>

To: 
Sent: Monday, December 05, 2005 12:15 PM
Subject: RE: [sqlite] Regarding String Comparision




Rob/Ritesh


Also keep in mind that such a search is CASE SENSITIVE.
There are two solutions to that, either makes the collation
case insensitive or do a:


I don't have access to SQLite immediately but I seem to remember in one of
my applications that the use of

select * from test where filename like '%file%';

would return string that are case INsensitive. (ie 'file', 'FILE', 'File'
...)

Ritesh I would suggest that you confirm this before relying on it.

Regards
Nick


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

Re: [sqlite] Regarding String Comparision

[Rob Lohman]

Keep in mind that string should be surrounded by
single quotes instead of double quotes:

exact match:

select * from test where filename = 'file';

partial match:

select * from test where filename like '%file%';

Also keep in mind that such a search is CASE SENSITIVE.
There are two solutions to that, either makes the collation
case insensitive or do a:

select * from test where lower(filename) = lower('TESTFILE.txt');

Hower it would be better to do the first lowercase in your
program code when inserting and the second when doing
the select so you don't have to do both in the select.

Rob

p.s. keep in mind that filenames can have single quotes in
them (at least Windows filenames can)! So you should
either call the sqlite interface with parameters (preferred)
or do a search and replace ' with '' in your filenames
before executing either the insert or the select

- Original Message - 
From: "Martin Engelschalk" <[EMAIL PROTECTED]>

To: 
Sent: Monday, December 05, 2005 10:06 AM
Subject: Re: [sqlite] Regarding String Comparision



Hi,

I am not sure if i understand your question right. Perhaps the following 
will help:


The behaviour depends on the comparison operator.

If you do

select ...
from FileTable
where FileName = "testfile.txt"

then the whole string will be matched

If you do

select ...
from FileTable
where FileName like "%/testfile.txt"

then you would get all files named "testfile.txt" regardless of their 
path. sqlite will search for strings ending in "/testfile.txt".


Martin

Ritesh Kapoor schrieb:


Hi,

I am debugging a program which has a FileTable field called FileName
declared as-

"FileName  varchar(1024) primary key"

It stores filenames including their paths e.g. - "myDir1/testfile.txt"

If I were to search in this FileTable in the column FileName for a
string "testfile.txt" would it return me the row "myDir1/testfile.txt"?

I'm not sure about this but currently I think this is what is
happening.  Is this the default behavior of SQLite?  


Are there any setting which would make it match the whole string?  So
that I don't have ambigious situations where two rows might match to the
same search string.

Thanks & Regards,
ritesh

 

Re: [sqlite] uSQLiteServer Source code available

[Rob Lohman]

I like your concept and agree with your design choices, so much in fact 
that I independently implemented a very similar system.  In my case I 
packaged the responses in XML, 


Difficult isn't it, XML or no XML :-) I had also considered XML, if
nothing else it is the 'in thing'. But the other side of the coin is
that if your XML replies do not comply to a recognised dictionary, the
only advantage is that ready made parsers are available. 


Apart from the horrendous overhead of XML, it is not necessarily easier


I just wanted to add that I've seen some shifting where XML sometimes
is compressed (as in (g/b)zip for example) before it is saved or send over
a wire to make up for that overhead while still having a flexible format.

Of course this would definitely be overkill (and a performance cost) for
this particular application, I just wanted to mention it for completeness
sake.

Thanks :)

Rob

Re: [sqlite] sqlite 2.0 database

[Rob Lohman]

Isn't it so that sqlite3 can't open certain
old sqlite2 database because of a format
change? At least I seem to remember
something like that. I would try opening
it with version 2.

Rob

- Original Message - 
From: "Manuel Enache" <[EMAIL PROTECTED]>

To: 
Sent: Monday, November 07, 2005 3:48 PM
Subject: RE: [sqlite] sqlite 2.0 database


I've tried with sqlite3 and nothing:



C:\sqlite>sqlite3.exe database
SQLite version 3.2.7
Enter ".help" for instructions
sqlite> .databases
Error: file is encrypted or is not a database
sqlite>



--- Clay Dowling <[EMAIL PROTECTED]> wrote:



Try opening it with an sqlite3 client as well.  If
neither will open the
file, it's either not an SQLite database, or it's
hopelessly corrupted and
you aren't getting into it.

Clay Dowling

Manuel Enache said:
> I'done like Shawn said and here is the result:
> 
> C:\sqlite>sqlite.exe database
> Unable to open database "database": file is
encrypted
> or is not a database
> 
>
> The file is not corupted because is used by a
> "program" and that program is working fine.
>
> That program is using tcl to access the data base:
>
> 
> sqlite pcdb pcdatabase
> # Construct SQL string for requested search
> ...
> pcdb close
> 
>
> From this I drawn the conclusion that the DB is
not
> encrypted. Is this a correct conclusion?
>
> --- "Downey, Shawn" <[EMAIL PROTECTED]> wrote:
>
>> Download sqlite.exe (version 2.8.16 which has no
>> numeric extension) from
>> http://www.sqlite.org/download.html.
>>
>> From the command line attempt to open the
database:
>>
>> sqlite 
>>
>> where > database file.  If the
>> file does not open, it is probably a damaged
file.
>>
>> Shawn M. Downey
>> MPR Associates
>> 10 Maxwell Drive, Suite 204
>> Clifton Park, NY 12065
>> 518-371-3983 x113 (work)
>> 860-508-5015 (cell)
>>
>>
>> -Original Message-
>> From: Manuel Enache
[mailto:[EMAIL PROTECTED]
>> Sent: Monday, November 07, 2005 9:06 AM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] sqlite 2.0 database
>>
>> I have a sqlite 2.0 DB and I need the data
within.
>>
>> I tried open it with almost all the tools I
finded.
>>
>> All say that: "file is encrypted or is not a
>> database"
>>
>> I'm 90% sure that the DB is not encrypted.
>>
>> It is possible that the may be to old for these
>> tools?
>> What tool should I use to open that DB and export
>> the
>> data?
>>
>> Thanks in advance!
>>
>>
>>
>> __
>> Yahoo! FareChase: Search multiple travel sites in
>> one click.
>> http://farechase.yahoo.com
>>
>
>
>
>
> __
> Yahoo! FareChase: Search multiple travel sites in
one click.
> http://farechase.yahoo.com
>


--
Simple Content Management
http://www.ceamus.com








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

Re: [sqlite] Request for comment: Proposed SQLite API changes

[Rob Lohman]

Another proposal:  Suppose that when creating an
sqlite3_stmt using sqlite3_prepare, the original SQL
text was stored in the sqlite3_stmt.  Then when a
schema change occurred, the statement was automatically
recompiled and rebound.  There would no more SQLITE_SCHEMA
errors.  But sqlite3_stmts would use a little more
memory.  And sqlite3_step might take a little longer
to initialize sometimes if it found it needed to rerun
the parser.  


What about this change?  Is it a worth-while tradeoff?


I'm a big fan of this change. We do quite a lot of bookkeeping in our 
own code to do exactly the same thing at the moment. It would make me 
very happy to delete that code.


This is not directly a question for you, Eric, but does anyone
actually have schema changes on a working database? I've
never seen any schema changes on my databases except for
a new version, bugfixes etc.

Does anyone runs code that actually changes the schema of
your database as the normal process in an application? If so,
for what reason?

Rob

Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2

[Rob Lohman]

A quick test here on MSSQL & Oracle:

Microsoft SQL 2000 & SQL 2005 (beta):

create table MATHTEST (
  CINT int  null,
  CDEC decimal  null,
  CDPREC   double precision null,
  CFLOAT   floatnull,
  CNUM numeric  null,
  CREALreal null
)
go

insert into MATHTEST (CINT, CDEC, CDPREC, CFLOAT, CNUM, CREAL)
values (5, 5, 5, 5, 5, 5);
go

select CINT/2, CDEC/2, CDPREC/2, CFLOAT/2, CNUM/2, CREAL/2
from MATHTEST
go

2
2.50
2.5
2.5
2.50
2.5

-

Oracle 8i2:

create table MATHTEST  (
  CDEC DEC,
  CDECIMAL DECIMAL,
  CDPREC   DOUBLE PRECISION,
  CFLOAT   FLOAT,
  CINT INT,
  CNUM NUMBER,
  CREALREAL
);

insert into MATHTEST (CDEC, CDECIMAL, CDPREC, CFLOAT, CINT, CNUM, CREAL)
values (5, 5, 5, 5, 5, 5, 5);

select CDEC/2, CDECIMAL/2, CDPREC/2, CFLOAT/2, CINT/2, CNUM/2, CREAL/2
from MATHTEST;

   CDEC/2 CDECIMAL/2   CDPREC/2   CFLOAT/2 CINT/2 CNUM/2CREAL/2
-- -- -- -- -- -- --
  2,52,52,52,52,52,52,5

So it seems these two have different opinions on this as well.

Rob

- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, November 02, 2005 4:36 PM
Subject: Re: [sqlite] Proposed 3.3.0 changes. Was: 5/2==2


Consider the following SQL:

  CREATE TABLE t1(a INTEGER, b REAL);
  INSERT INTO t1 VALUES(5,5);
  SELECT a/2, b/2 FROM t1;


From the above SQL, SQLite version 3.2.7 and earlier will return


  2|2

If my proposed changes for 3.3.0 go in, then the result will be:

  2.5|2.5

If I understand what most people are saying, the SQL standard
says that the result should be:

  2|2.5

Does this correctly summarize the situation?

Do other SQL database engines consistently return the 3rd case?

Am I alone in thinking that a division operator that does
different things depending on the declared datatype of a
column is an abomination?

Does anybody have a real-world example where any of this
will actually make a difference, or is this really just an
academic argument?

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

Re: [sqlite] SQLite as a Windows kernel tool

[Rob Lohman]

I have been silently reading the conversation, but I have
to reply on this latest message.

- Original Message - 
From: "Fred Williams" <[EMAIL PROTECTED]>

Look I'm certain you mean well, but the rest of us are pretty busy using
one of the best small footprint databases on the planet.  That means we
are way too busy to nit-pic a good product to pieces, just because it
won't compile clean using Mickeysoft's latest and greatest.


This is unfair. As stated it will actually compile. There are
just warnings to indicate data loss might occur. Which we
all know happens if you convert int64 to a char for example
(Which has nothing todo with the fact that it is a Microsoft
compiler, or Mickeysoft as you call it).

The original poster is (imho) just looking for clarification
if these possible problem spots are working as they are
supposed to, on all platforms. Isn't this a good discussion
to have? To make sure the codebase is at the best quality
it can be? I thought we would all benefit from such a thing,
guess you don't agree.


How' bout you go through this buggy code and fix all your concerns then
upload it to CVS.  That way you would be making a huge contribution to


This would mean the original poster has to know every
in and out of the SQLite code. Fixing something that seems
obvious might break something else. Since the original
question was about whether the mentioned warnings are
real I doubt the poster has the knowledge to fix it himself
and no if there are any consequences or not.


us all, and won't come off as such an irritating whiner.


This is just completely rude and uncalled for. If you don't
want to "waste your time" discussing a genuine concern,
then why bother replying at all?

Regards,

Rob Lohman

p.s. funny how you are using "Mickeysoft" Outlook

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

[Rob Lohman]

I assume the wrapper has wrapped this particular function. I'm
currently writing my own wrapper and it has wrapped it as well.

This is my definition (in case your wrapper doesn't have it):
/// 
/// Returns the lengh of data in a single column of the current result row 
of a query

/// 
/// Statement handle
/// Zero based column index. The left-most column has 
an index of 0
/// Column data length. If the SQL statement is not currently 
pointing to a valid row, or if the the column index is out of range, the 
result is undefined

[DllImport("sqlite3.dll", CallingConvention=CallingConvention.Cdecl)]
internal static extern Int32 sqlite3_column_bytes(IntPtr statementhandle, 
Int32 column);


Rob

- Original Message - 
From: "Lloyd Dupont" <[EMAIL PROTECTED]>

To: 
Sent: Sunday, October 23, 2005 3:46 PM
Subject: Re: [sqlite] built-in functrion suggestion: size of blob



Isn't this what you are looking for?

http://www.sqlite.org/capi3ref.html#sqlite3_column_bytes

" If the result is a BLOB then the sqlite3_column_bytes() routine returns 
the number of bytes in that BLOB. "


Or do you really need it inside an SQL statement?

that's right!
I'm not using SQLite C API.
I'm using a .NET wrapper.
I don't see how I could could call this function in a pratical way from 
the wrapper...

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

[Rob Lohman]

Isn't this what you are looking for?

http://www.sqlite.org/capi3ref.html#sqlite3_column_bytes

" If the result is a BLOB then the sqlite3_column_bytes() routine returns 
the number of bytes in that BLOB. "


Or do you really need it inside an SQL statement?

Rob

- Original Message - 
From: "Lloyd Dupont" <[EMAIL PROTECTED]>

To: 
Sent: Sunday, October 23, 2005 3:15 PM
Subject: [sqlite] built-in functrion suggestion: size of blob


I look into the build in function of SQLite and saw there is a function to 
know the length of a string (in a record).

Great!

But to my disbelief there is (apparently) no way to get the size of a blob 
(other than loading it :-()

And no, length() doesn't work on Blob.

I think it would be a worthy addition!

Or is there already an (undocumented) such addition? 

Re: [sqlite] who is responsible for memory

[Rob Lohman]

Sorry, I forgot to add: sqlite3_column_name(16). Currently I
let MS .NET free memory from that as well.

Thanks,

Rob

- Original Message - 
From: "Rob Lohman" <[EMAIL PROTECTED]>

To: <sqlite-users@sqlite.org>
Sent: Friday, October 21, 2005 7:05 PM
Subject: [sqlite] who is responsible for memory



Hi everyone,

A couple of months ago I started writing a Microsoft .NET
(1.1 at the moment, but it will be 2.0) wrapper around SQLite.
I know a couple of those already exist, but for various
reasons these do not meet my requirements.

Everything is looking fine except for managing memory. I've
had some memory problems according to some profilers
which I've seem to have fixed but I want to make sure. I
have looked at the everything in the site but with certain
(SQLite) functions it is unclear to me who is responsible
to clean up any allocated memory.

Now I seem to remember from long long ago that there
where some "rules" in C(++) who is responsible in which case
with a library or DLL, but I have no idea if that is true or what
those rules would be.

The first sign of trouble began with the sqlite3_errmsg(16)
function. I had MS .NET release that memory which seemed
a bad thing to do. After I stopped doing that, and I assume
SQLite frees it, everything appears fine.

That let me to wonder about the following functions:

- sqlite3_column_blob/text(16)

With these functions I copy out the data and (hopefully) let
SQLite free the memory.

- sqlite3_libversion

I let the MS .NET framework free this memory.

At the moment this is inconsistent as you can see. Is any of
this correct or?

Since sqlite3_bind_blob/text(16) let me select either transient
or static I'm always responsible for that memory, right?

Any clarification on this matter will be greatly appreciated.

Thanks!

Best,

Rob Lohman

[sqlite] who is responsible for memory

[Rob Lohman]

Hi everyone,

A couple of months ago I started writing a Microsoft .NET
(1.1 at the moment, but it will be 2.0) wrapper around SQLite.
I know a couple of those already exist, but for various
reasons these do not meet my requirements.

Everything is looking fine except for managing memory. I've
had some memory problems according to some profilers
which I've seem to have fixed but I want to make sure. I
have looked at the everything in the site but with certain
(SQLite) functions it is unclear to me who is responsible
to clean up any allocated memory.

Now I seem to remember from long long ago that there
where some "rules" in C(++) who is responsible in which case
with a library or DLL, but I have no idea if that is true or what
those rules would be.

The first sign of trouble began with the sqlite3_errmsg(16)
function. I had MS .NET release that memory which seemed
a bad thing to do. After I stopped doing that, and I assume
SQLite frees it, everything appears fine.

That let me to wonder about the following functions:

- sqlite3_column_blob/text(16)

With these functions I copy out the data and (hopefully) let
SQLite free the memory.

- sqlite3_libversion

I let the MS .NET framework free this memory.

At the moment this is inconsistent as you can see. Is any of
this correct or?

Since sqlite3_bind_blob/text(16) let me select either transient
or static I'm always responsible for that memory, right?

Any clarification on this matter will be greatly appreciated.

Thanks!

Best,

Rob Lohman