Re[2]: [sqlite] Inserting image files into a database table

[Teg]

Hello Srikanth,

If you use his method, bind_blob, no conversion is necessary. You
insert a JPG and you retrieve the JPG. I'd probably include the
filename to you can actually name the file if you want to later. I do
this all the time.

C



Monday, March 27, 2006, 5:59:24 PM, you wrote:

S> Boris,
S> Thanks. I have few more questions, though. How do I convert an image into
S> its hex equivalent? This is my situation: I would like to a) insert pictures
S> and their captions into a database table and, when needed fetch them and
S> display them in a webpage. I am going to use Python for inserting/fetching
S> the images and their captions. I am not sure, though, how an image can be
S> converted into its hex form, inserted, fetched and then reconverted into an
S> image. I am new to this process, so am not sure how this is done.
S> Thanks
S> Srikanth.

S> On 3/27/06, Boris Popov <[EMAIL PROTECTED]> wrote:
>>
>> You should bind the blob to the prepared insert statement,
>>
>> insert into first_table (picture_name, picture) values (?,?)
>>
>> Then sqlite3_bind_text() your name and sqlite3_bind_blob() your image
>> bytes
>> to the sqlite3_stmt that's the result of sqlite3_prepare().
>>
>> http://www.sqlite.org/capi3ref.html#sqlite3_prepare
>>
>> http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob
>>
>> Or you could use X'53514697465' notation, where any blob can be expressed
>> as
>> a string in hex preceded by x or X.
>>
>> insert into first_table (picture_name, picture) values
>> ('dog',X'53514697465')
>>
>> Hope this helps,
>>
>> -Boris
>>
>> --
>> +1.604.689.0322
>> DeepCove Labs Ltd.
>> 4th floor 595 Howe Street
>> Vancouver, Canada V6C 2T5
>>
>> [EMAIL PROTECTED]
>>
>> CONFIDENTIALITY NOTICE
>>
>> This email is intended only for the persons named in the message
>> header. Unless otherwise indicated, it contains information that is
>> private and confidential. If you have received it in error, please
>> notify the sender and delete the entire message including any
>> attachments.
>>
>> Thank you.
>>
>> -Original Message-
>> From: Srikanth [mailto:[EMAIL PROTECTED]
>> Sent: Monday, March 27, 2006 2:15 PM
>> To: sqlite-users@sqlite.org
>> Subject: [sqlite] Inserting image files into a database table
>>
>> Hi,
>> Could someone give me the procedure for inserting images into a database
>> table?
>> E.g., say I created a table thus:
>> create table first_table( picture_name string, picture BLOB);
>>
>> How do I insert an image  into the picture field?
>>
>> Thanks.
>> Srikanth.
>>
>>
>>



-- 
Best regards,
 Tegmailto:[EMAIL PROTECTED]

RE: [sqlite] Inserting image files into a database table

[Boris Popov]

You should bind the blob to the prepared insert statement,

insert into first_table (picture_name, picture) values (?,?)

Then sqlite3_bind_text() your name and sqlite3_bind_blob() your image bytes
to the sqlite3_stmt that's the result of sqlite3_prepare().

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

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

Or you could use X'53514697465' notation, where any blob can be expressed as
a string in hex preceded by x or X.

insert into first_table (picture_name, picture) values
('dog',X'53514697465')

Hope this helps,

-Boris

-- 
+1.604.689.0322
DeepCove Labs Ltd.
4th floor 595 Howe Street
Vancouver, Canada V6C 2T5

[EMAIL PROTECTED]

CONFIDENTIALITY NOTICE

This email is intended only for the persons named in the message
header. Unless otherwise indicated, it contains information that is
private and confidential. If you have received it in error, please
notify the sender and delete the entire message including any
attachments.

Thank you.

-Original Message-
From: Srikanth [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 27, 2006 2:15 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Inserting image files into a database table

Hi,
Could someone give me the procedure for inserting images into a database
table?
E.g., say I created a table thus:
create table first_table( picture_name string, picture BLOB);

How do I insert an image  into the picture field?

Thanks.
Srikanth.


smime.p7s
Description: S/MIME cryptographic signature

[sqlite] Inserting image files into a database table

[Srikanth]

Hi,
Could someone give me the procedure for inserting images into a database
table?
E.g., say I created a table thus:
create table first_table( picture_name string, picture BLOB);

How do I insert an image  into the picture field?

Thanks.
Srikanth.

Re: [sqlite] help with sqlite command

[Jay Sprenkle]

On 3/27/06, Uma Venkataraman <[EMAIL PROTECTED]> wrote:
> I would like to delete n records from a table, based on some condition. Can
> some one please let me know how to do this with sqlite?

http://sqlite.org/lang_delete.html

Re: [sqlite] help with sqlite command

[Uma Venkataraman]

I would like to delete n records from a table, based on some condition. Can 
some one please let me know how to do this with sqlite?


Thanks 

[sqlite] Distribution Removal

[James Mayhew]

Please remove me from your distribution list.

[EMAIL PROTECTED]

John Stanton wrote:


Tito Ciuro wrote:


On 26/03/2006, at 10:51, MGC wrote:


Your design is fundamentaly wrong.
I don't know what your intended use
is for this data, but I am logging identical fstat file info along  
with an

MD5 sums.




Well... if you don't know what is the intended use for the data, how  
can you say that my design is fundamentally wrong? :-)


It's not wrong. That's the way it has to be. Now, if I could match  
the data properly with LIKE and GLOB, that would be great.


Thanks for your response though.

Regards,

-- Tito


LIKE and GLOB do a row scan, and give you none of the advantages of an 
RDBMS.  Why not use a flat file and grep and get simplicity and 
greater speed?

Re: [sqlite] help with sqlite command

[Uma Venkataraman]

Thanks Dennis..that seems to do the trick...

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

To: 
Sent: Monday, March 27, 2006 2:46 PM
Subject: Re: [sqlite] help with sqlite command



Jay Sprenkle wrote:



I believe rowid is assigned dynamically to the result set so it would
give a different
set of results for a different query.


 


Jay,

The rowid is the key from the btree used to store the table rows. It is 
not generated dynamically.


To get every N'th row after deletions you need some way to assign a 
series of integers to the result rows. The easiest way I can think of is 
to create a temporary table from your initial query. Then you can use 
the modulus operator to select every N'th record from that table as you 
have suggested since the rowids will all be freshly assigned. You will 
also need to drop the temp table when you are done with it.


 create temp table temp_table as select * from my_table where ;
 select * from temp_table where rowid % N = 0;
 drop table temp_table;

HTH
Dennis Cote

Re: [sqlite] help with sqlite command

[Jay Sprenkle]

Too bad sqlite doesn't have Oracle's ROWNUM:

"Pseudo-Columns

While not actual datatypes, Oracle supports several special-purpose
data elements. These elements are not actually contained in a table,
but are available for use in SQL statements as though they were part
of the table.
ROWNUM

For each row of data returned by a SQL query, ROWNUM will contain a
number indicating the order in which the row was retrieved. For
example, the first row retrieved will have a ROWNUM of 1, the second
row will have a ROWNUM of 2, and so on. This approach can be useful
for limiting the number of rows returned by a query. To display only
ten rows of the emp table, the following SQL statement makes use of
the ROWNUM pseudo-column:

SELECT *
FROM emp
WHERE ROWNUM < 11;

WARNING:

ROWNUM returns a number indicating the order in which the row was
retrieved from the table, but this is not always the order in which a
row is displayed. For example, if a SQL statement includes an ORDER BY
clause, rows will not be displayed in ROWNUM sequence, since ROWNUM is
assigned before the sort operation. "

Re: [sqlite] help with sqlite command

[Jay Sprenkle]

On 3/27/06, Dennis Cote <[EMAIL PROTECTED]> wrote:
> Jay,
>
> The rowid is the key from the btree used to store the table rows. It is
> not generated dynamically.

Ah. Thanks! Learn something new every day.

Re: [sqlite] help with sqlite command

[Dennis Cote]

Jay Sprenkle wrote:



I believe rowid is assigned dynamically to the result set so it would
give a different
set of results for a different query.


 


Jay,

The rowid is the key from the btree used to store the table rows. It is 
not generated dynamically.


To get every N'th row after deletions you need some way to assign a 
series of integers to the result rows. The easiest way I can think of is 
to create a temporary table from your initial query. Then you can use 
the modulus operator to select every N'th record from that table as you 
have suggested since the rowids will all be freshly assigned. You will 
also need to drop the temp table when you are done with it.


 create temp table temp_table as select * from my_table where ;
 select * from temp_table where rowid % N = 0;
 drop table temp_table;

HTH
Dennis Cote

Re: [sqlite] help with sqlite command

[Derrell . Lipman]

"Uma Venkataraman" <[EMAIL PROTECTED]> writes:

> Hi Jay,
>
> Thanks for your reply. I am trying the command
>
>select * from mytable where row_id = row_id % 5

Try this instead:

  SELECT * FROM mytable WHERE ROWID % 5 = 0;

Note that if you have an integer primary key in mytable, then ROWID and your
primary key are the same thing.  If those ROWID values are not incrementing
numbers (e.g. you inserted values into your primary key which were out of
sequence or if you have deleted any rows) then this method won't work.

Here's an example of one way to do it:

SQLite version 3.2.1
Enter ".help" for instructions
sqlite> .read /tmp/x.sql
CREATE TABLE x (i INTEGER PRIMARY KEY, t TEXT);
INSERT INTO x VALUES (1, 'one');
INSERT INTO x VALUES (2, 'two');
INSERT INTO x VALUES (3, 'three');
INSERT INTO x VALUES (4, 'four');
INSERT INTO x VALUES (5, 'five');
INSERT INTO x VALUES (6, 'six');
INSERT INTO x VALUES (7, 'seven');
INSERT INTO x VALUES (8, 'eight');
-- Retrieve every other row (we hope)
SELECT * FROM x WHERE ROWID % 2 = 0;
2|two
4|four
6|six
8|eight
-- Delete a row
DELETE FROM x WHERE i = 4;
-- The table now looks like this:
SELECT * FROM x;
1|one
2|two
3|three
5|five
6|six
7|seven
8|eight
-- Retrieve what should be every other row, but isn't
SELECT * FROM x WHERE ROWID % 2 = 0;
2|two
6|six
8|eight
-- Insert the values into a new table so pk is properly incrementing
CREATE TEMPORARY TABLE y
(pk INTEGER PRIMARY KEY,
 i INTEGER,
 t TEXT);
INSERT INTO y (i, t) SELECT i, t FROM x;
-- Now we can get every other row
SELECT * FROM y WHERE pk % 2 = 0;
2|2|two
4|5|five
6|7|seven
DROP TABLE y;
sqlite>

Cheers,

Derrell

Re: [sqlite] help with sqlite command

[Clark Christensen]

I think

select * from mytable where rowid %5 = 0;

will get you something like every fifth row in the table.  But that assumes 
your rowids are 1-nnn with no gaps.  If your rowids happen to skip a value 
evenly divisible by 5, you won't get another row until the next one divisible 
by 5.

 -Clark


- Original Message 
From: Uma Venkataraman <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, March 27, 2006 11:07:18 AM
Subject: Re: [sqlite] help with sqlite command

Hi Jay,

Thanks for your reply. I am trying the command

select * from mytable where row_id = row_id % 5

from sqlite browser and it says, no such column row_id.. Also I replaced 
row_id with rowid and it gave only the first 4 records from my table. My 
other concern is I will be deleting and adding records to the table. If I 
want to select every nth record after such deletions and additions will the 
row id not get affected?

Thanks
Uma



- Original Message - 
From: "Jay Sprenkle" <[EMAIL PROTECTED]>
To: 
Sent: Monday, March 27, 2006 1:56 PM
Subject: Re: [sqlite] help with sqlite command


On 3/27/06, Uma Venkataraman <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I need to be able to select  the TOP N rows from a table. How do i do it =

select * from mytable limit 5


> with sqlite? Also  how does one select EVERY Nth row from a table?

use modulus operator for that:
select * from mytable where row_id = row_id % 5



---
On Wednesday, March 1, 2006, at a hearing on the proposed
Constitutional Amendment to prohibit gay marriage, Jamie Raskin,
professor of law at AU, was requested to testify.

At the end of his testimony, Republican Senator Nancy Jacobs said:
"Mr. Raskin, my Bible says marriage is only between a man and a woman.
What do you have to say about that?"

Raskin replied: "Senator, when you took your oath of office, you
placed your hand on the Bible and swore to uphold the Constitution.
You did not place your hand on the Constitution and swear to uphold
the Bible."

The room erupted into applause. 

Re: [sqlite] help with sqlite command

[Jay Sprenkle]

> Thanks for your reply. I am trying the command
>
> select * from mytable where row_id = row_id % 5
>
> from sqlite browser and it says, no such column row_id.. Also I replaced
> row_id with rowid and it gave only the first 4 records from my table. My
> other concern is I will be deleting and adding records to the table. If I
> want to select every nth record after such deletions and additions will the
> row id not get affected?

Sorry, I'm a little off today and wrote the wrong formula!

To get the even numbered records use:
where rowid % 2 = 0

To get the odd numbered records use:
where rowid % 2 = 1

To get every 5th record
where rowid % 5 = 0 ( this will return record 5, 10, 15, etc).

look up the 'modulus' or 'modulo' operator to see what this does.

I believe rowid is assigned dynamically to the result set so it would
give a different
set of results for a different query.

If you want the same records from different select statements
you could create an integer column with a primary key and use that
instead of rowid.

Re: [sqlite] NHibernate

[Bert Verhees]

Robert Simpson wrote:


- Original Message - From: "Bert Verhees" <[EMAIL PROTECTED]>
To: 
Sent: Monday, March 27, 2006 9:27 AM
Subject: [sqlite] NHibernate


Hi, I am trying about whole day to connect a Delphi.NET app over 
NHibernate to SQLite.


I cannot get it done.

I have following App.config (in the exe-directory)



I'm afraid you'll have to ask about this on the nHibernate forums.  
Most of the people on this list are concerned with the core sqlite 
engine itself and not the wrappers and O/R mappers built on top of it.


Robert


It is free to ask, my mother always told me, and in a sqlite user list 
there is a possibility that people had to solve the same problem.
But since I got no reaction, I am afraid yoy could be right, and I will 
also try elsewhere


regards
Bert Verhees

Re: Re[2]: [sqlite] Unaligned access in SQLite on Itanium

[Alexei Alexandrov]

>
> Unless the on disk format is carried over into memory, there's no
> reason not to use a structure alignment which prevents unaligned
> access.
>
> I'm a little surprised your compiler hadn't already padded the struct
> out to proper alignment when you built SQLite.
>

Sorry, I'm a little bit lost here. Maybe I mis-explained something -
maybe due to my non-native English. Sorry for that. Let's re-iterate
through my problem once again.

My compiler does have right padding. By default, almost all compilers
will align structure members on natural alignment boundary, which
means align a member on the offset which is a multiple of the member
data size.

In my case it is array of chars that has got mis-aligned. To be
precise, it is aligned just right to store 1, 2, 4, 8 size types
there, but it is not aligned properly for 16-byte long double. For
this case, #pragma pack, which is more or less portable (I really like
this 'more or less portable' - crazy world, you've gone too far...),
won't work because it allows only to pack - that is, to lower
alignment boundaries - it is used when you need to pack structures for
network transfer, for example. And in fact, #pragma pack can only make
hardware alignment issues only worse.

So the only (known so far) portable way to fix the problem is to union
the problematic array with long double variable to get it aligned
properly - on 16-bytes boundary. On GCC we could also use

__attribute__ ((aligned (16)))

but it is not portable.

Please don't hesitate to ask for clarifications if something is not
clear here - I really want to follow it up ASAP.

--
Alexei Alexandrov

Re: [sqlite] help with sqlite command

[Uma Venkataraman]

Hi Jay,

Thanks for your reply. I am trying the command

   select * from mytable where row_id = row_id % 5

from sqlite browser and it says, no such column row_id.. Also I replaced 
row_id with rowid and it gave only the first 4 records from my table. My 
other concern is I will be deleting and adding records to the table. If I 
want to select every nth record after such deletions and additions will the 
row id not get affected?


Thanks
Uma



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

To: 
Sent: Monday, March 27, 2006 1:56 PM
Subject: Re: [sqlite] help with sqlite command


On 3/27/06, Uma Venkataraman <[EMAIL PROTECTED]> wrote:

Hi All,

I need to be able to select  the TOP N rows from a table. How do i do it =


select * from mytable limit 5



with sqlite? Also  how does one select EVERY Nth row from a table?


use modulus operator for that:
select * from mytable where row_id = row_id % 5



---
On Wednesday, March 1, 2006, at a hearing on the proposed
Constitutional Amendment to prohibit gay marriage, Jamie Raskin,
professor of law at AU, was requested to testify.

At the end of his testimony, Republican Senator Nancy Jacobs said:
"Mr. Raskin, my Bible says marriage is only between a man and a woman.
What do you have to say about that?"

Raskin replied: "Senator, when you took your oath of office, you
placed your hand on the Bible and swore to uphold the Constitution.
You did not place your hand on the Constitution and swear to uphold
the Bible."

The room erupted into applause. 

Re: [sqlite] help with sqlite command

[Jay Sprenkle]

On 3/27/06, Uma Venkataraman <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I need to be able to select  the TOP N rows from a table. How do i do it =

select * from mytable limit 5


> with sqlite? Also  how does one select EVERY Nth row from a table?

use modulus operator for that:
select * from mytable where row_id = row_id % 5



---
On Wednesday, March 1, 2006, at a hearing on the proposed
Constitutional Amendment to prohibit gay marriage, Jamie Raskin,
professor of law at AU, was requested to testify.

At the end of his testimony, Republican Senator Nancy Jacobs said:
"Mr. Raskin, my Bible says marriage is only between a man and a woman.
What do you have to say about that?"

Raskin replied: "Senator, when you took your oath of office, you
placed your hand on the Bible and swore to uphold the Constitution.
You did not place your hand on the Constitution and swear to uphold
the Bible."

The room erupted into applause.

[sqlite] help with sqlite command

[Uma Venkataraman]

Hi All,

I need to be able to select  the TOP N rows from a table. How do i do it =
with sqlite? Also  how does one select EVERY Nth row from a table?

Thanks

Re: [sqlite] NHibernate

[Robert Simpson]

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

To: 
Sent: Monday, March 27, 2006 9:27 AM
Subject: [sqlite] NHibernate


Hi, I am trying about whole day to connect a Delphi.NET app over 
NHibernate to SQLite.


I cannot get it done.

I have following App.config (in the exe-directory)



I'm afraid you'll have to ask about this on the nHibernate forums.  Most of 
the people on this list are concerned with the core sqlite engine itself and 
not the wrappers and O/R mappers built on top of it.


Robert

Re: [sqlite] Unaligned access in SQLite on Itanium

[Jay Sprenkle]

On 3/27/06, Alexei Alexandrov <[EMAIL PROTECTED]> wrote:
> >
> > Isn't this eliminated with the proper compile settings for data packing?
> > There should be a compile option for sometihng like 'align data on N
> > byte boundaries'
> >
>
> Even if that is possible, I don't want to say to compiler "Please
> align everything on 16 bytes" because this will lead to huge memory
> footprint overhead - every element - even 1 byte-long will take 16
> bytes in memory. This is not an option.

The compiler I used was smarter than that in the 1980's...

Re: [sqlite] Unaligned access in SQLite on Itanium

[Alexei Alexandrov]

>
> Isn't this eliminated with the proper compile settings for data packing?
> There should be a compile option for sometihng like 'align data on N
> byte boundaries'
>

Even if that is possible, I don't want to say to compiler "Please
align everything on 16 bytes" because this will lead to huge memory
footprint overhead - every element - even 1 byte-long will take 16
bytes in memory. This is not an option.

--
Alexei Alexandrov

[sqlite] NHibernate

[Bert Verhees]

Hi, I am trying about whole day to connect a Delphi.NET app over 
NHibernate to SQLite.


I cannot get it done.

I have following App.config (in the exe-directory)



 
type="System.Configuration.NameValueSectionHandler, System, 
Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"

   />
 
 
   value="NHibernate.Driver.SQLiteDriver" />
   value="NHibernate.Connection.DriverConnectionProvider" />
   value="NHibernate.Dialect.SQLiteDialect" />
   

   
  


and this code
 cfg := Configuration.Create;
 cfg.AddAssembly('nhibernate');
--->  factory := cfg.BuildSessionFactory; <--
 session := factory.OpenSession;
session.BeginTransaction;

The error message appears on the errors

---
Project4
---
The hibernate.connection.driver_class must be specified in the 
NHibernate configuration section.

---
OK  
---


I am using the Finisar SQLite ADO-driver, which is used to build 
NHibernate (one can see in the code)


Someone have an idea?

Thanks in advance.

Re: [sqlite] How to invert positional argument in ORDER BY

[Jay Sprenkle]

> > order by N is SQL for "order by the column number N in the result set".
> > So -1 is meaningless, there isn't a -1th column.
> Yes, you're right. But that also means there couldn't ever be -1st
> column in the result set so we could safely assume -1 to be 1
> inverted.
> I wanted to submit a ticket for this but I checked Postgres, Firebird
> and MySQL and none of them support it so I gave up on that. Actually
> Firebird and MySQL parse it, but ignore it when time comes to do the
> ordering and still sort in non-inverted order. Postgres throws an
> error, just like SQLite.

Ah, I understand. I would have done that as "order by 1 desc"
Never thought of it that way.

Re: [sqlite] How to invert positional argument in ORDER BY

[Nemanja Corlija]

On 3/27/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
> > Is there a way to use positional argument (hopefully that's the right
> > term) instead of column name here?
> > Something like this:
> > SELECT date FROM chng ORDER BY -1;
>
> FYI:
>
> order by N is SQL for "order by the column number N in the result set".
> So -1 is meaningless, there isn't a -1th column.
Yes, you're right. But that also means there couldn't ever be -1st
column in the result set so we could safely assume -1 to be 1
inverted.
I wanted to submit a ticket for this but I checked Postgres, Firebird
and MySQL and none of them support it so I gave up on that. Actually
Firebird and MySQL parse it, but ignore it when time comes to do the
ordering and still sort in non-inverted order. Postgres throws an
error, just like SQLite.

Not a big deal anyway, there are other ways to get same results. This
would come in handy just as a shortcut in _some_ cases.

--
Nemanja Corlija <[EMAIL PROTECTED]>

Re: [sqlite] How to invert positional argument in ORDER BY

[Jay Sprenkle]

> Is there a way to use positional argument (hopefully that's the right
> term) instead of column name here?
> Something like this:
> SELECT date FROM chng ORDER BY -1;

FYI:

order by N is SQL for "order by the column number N in the result set".
So -1 is meaningless, there isn't a -1th column.

Re: [sqlite] Unaligned access in SQLite on Itanium

[Jay Sprenkle]

On 3/25/06, Alexei Alexandrov <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I don't know whether it's been already reported or not, so anyway.
> There are places in SQLite where unaligned access exception is
> generated on Itanium. The unaligned access means that someone tries to
> read or write memory crossing 8-bytes boundary. Usually this occur as
> a result of pointers casting.

Isn't this eliminated with the proper compile settings for data packing?
There should be a compile option for sometihng like 'align data on N
byte boundaries'

Re: [sqlite] LIKE and GLOB bug with numbers?

[Martin Jenkins]

Tito,

I knocked up a quick test with python and apsw and it worked as intended. My 
data isn't exactly the same as yours in that I don't have the variety in the 
keys, but you're not having problems with those. My test database contains 
your data with/without embedded carriage returns - as expected, it makes no 
difference.


In the following, zip(..) is a quick hack to get all the results from the 
query. The spurious '[', ']' and other brackets surrounding the results are 
a result of the way that apsw returns data (as lists of python tuples).


Apologies for the extreme width of the following lines. :(

zip(csr.execute("select * from t"))

[
(('file5809', 
'(0,NSFileTypeRegular,0,22537,0,staff,234881026,294022,2004-12-16 
10:11:00 -0800,tciuro,384,2006-03-26 08:01:55 -0800,502,20)'),),
(('file0101581a', 
'(1,NSFileTypeRegular,1,22554,0,staff,234881026,294022,2004-12-16 
10:11:03 -0800,tciuro,384,2006-03-26 08:04:55 -0800,502,20)'),),
(('file0202582b', 
'(2,NSFileTypeRegular,2,22571,0,staff,234881026,294022,2004-12-16 
10:11:06 -0800,tciuro,384,2006-03-26 08:07:55 -0800,502,20)'),),

...
(('file595d', '(\n   0,\nNSFileTypeRegular,\n0,\n 
22877,\n0,\nstaff,\n234881026,\n294022,\n2004-12-16 
10:11:00 -0800,\ntciuro,\n384,\n2006-03-26 08:01:55 -0800,\n 
502,\n20\n)'),),
(('file0101596e', '(\n   1,\nNSFileTypeRegular,\n1,\n 
22894,\n0,\nstaff,\n234881026,\n294022,\n2004-12-16 
10:11:03 -0800,\ntciuro,\n384,\n2006-03-26 08:04:55 -0800,\n 
502,\n20\n)'),), (('file0202597f',
'(\n   2,\nNSFileTypeRegular,\n2,\n22911,\n0,\nstaff,\n 
234881026,\n294022,\n2004-12-16 10:11:06 -0800,\ntciuro,\n 
384,\n2006-03-26 08:07:55 -0800,\n502,\n20\n)'),),

...
]

zip(csr.execute("SELECT * FROM t WHERE CMValues GLOB '*2004-12-16 
10:11:45 -0800*'"))


[
(('file15155908', 
'(15,NSFileTypeRegular,15,22792,0,staff,234881026,294022,2004-12-16 
10:11:45 -0800,tciuro,384,2006-03-26 08:46:55 -0800,502,20)'),),
(('file15155a5c', '(\n   15,\nNSFileTypeRegular,\n15,\n 
23132,\n0,\nstaff,\n234881026,\n294022,\n2004-12-16 
10:11:45 -0800,\ntciuro,\n384,\n2006-03-26 08:46:55 -0800,\n 
502,\n20\n)'),)

]

zip(csr.execute("SELECT * FROM t WHERE CMValues LIKE '%2004-12-16 
10:11:45 -0800%'"))


[
(('file15155908', 
'(15,NSFileTypeRegular,15,22792,0,staff,234881026,294022,2004-12-16 
10:11:45 -0800,tciuro,384,2006-03-26 08:46:55 -0800,502,20)'),),
(('file15155a5c', '(\n   15,\nNSFileTypeRegular,\n15,\n 
23132,\n0,\nstaff,\n234881026,\n294022,\n2004-12-16 
10:11:45 -0800,\ntciuro,\n384,\n2006-03-26 08:46:55 -0800,\n 
502,\n20\n)'),)

]

Could you try reducing your search strings and see if there's a point at 
which they start working?


HTH,

Martin Jenkins
XQP Ltd
Ascot, UK

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

To: "Forum SQLite" 
Sent: Sunday, March 26, 2006 6:50 PM
Subject: [sqlite] LIKE and GLOB bug with numbers?



Hello,

I've populated a datafile with 40.176 records which contain file 
attributes and file paths. I have two columns, CMKey and CMValues.  The 
column CMKey contains the path to the file and the column  CMValues 
contains the attribute values. For example:


CMKey: Application Support/AbiSuite/AbiWord.Profile

CMValues:
(
0,
NSFileTypeRegular,
1,
21508,
0,
staff,
234881026,
294022,
2004-12-16 10:11:35 -0800,
tciuro,
384,
2006-03-26 08:35:55 -0800,
502,
20
)

Both columns are of type TEXT.

This is what I've found:

1) SELECT * FROM FinderFiles WHERE CMKey GLOB '*AbiWord.Profile*'  returns 
1 match. This is correct.


2) SELECT * FROM FinderFiles WHERE CMKey LIKE '%ABIWORD.Profile%'  returns 
1 match. This is correct.


3) SELECT * FROM FinderFiles WHERE CMValues GLOB '*2004-12-16 
 10:11:35 -0800*' returns 40.176 matches. This is not correct. There  is 
no way I created these 40.176 file at the *very same* time. Just  to be 
sure, I looked at one random file (of the 40.176) and I've  obtained the 
following creation date attribute:


NSFileCreationDate = 2004-02-21 06:12:43 -0800;

The same problem occurs if I perform the query:

SELECT * FROM FinderFiles WHERE CMValues LIKE '%2004-12-16 
0:11:35  -0800%'


This problem seems to occur when trying to match something with numbers:

- If I look for NSFilePosixPermissions 448 (which I know exists) I  get 
zero matches

- If I look for strings, such as in step #1 or #2, it works fine.

Something is wrong, I just can't figure out why...

Any ideas? Is this a bug?

Thanks,

-- Tito

Re: [sqlite] Unaligned access in SQLite on Itanium

[drh]

"Alexei Alexandrov" <[EMAIL PROTECTED]> wrote:
> >
> > Well, it's supported by most compilers today, but I try to avoid
> > anonymous unions in C code as well. They are fairly standard for C++,
> > but not for C. But the approach seems fine to me - the alignment will
> > be forced in this case.
> >
> 
> Just to make it clear: can I expect this fix in the next SQLite
> release? Should I submit a ticket for this? I will provide Linux
> ia64/x86_64 testing from my side.
> 

Submit a ticket or it will likely fall through the cracks.
--
D. Richard Hipp   <[EMAIL PROTECTED]>