[sqlite] Trying to free not allocated memory while Insert

[Mahalakshmi.m]

Hi ,

I am working in Sqlite 3.3.6 source files.
I tried to insert one record and I found that sqlite3GenericFree (p) is
called. But that address is not allocated using sqlite3GenericMalloc (int
n).
Is this Correct.Kindly clarify.
Will all memory gets allocated and freed inside os_common.h or at some other
place.

But I can insert that record without any problem .Then I tried to insert
some 1 records but at that time it hangs inside sqlite3Parser ()
--- > yy_reduce. To fix this what changes I have to do in my code.


In Parse.c I found some line of codes like:

#ifndef NDEBUG
  /* Silence complaints from purify about yygotominor being uninitialized
   In some cases when it is copied into the stack after the following
   Switch.  yygotominor is uninitialized when a rule reduces that does
   Not set the value of its left-hand side nonterminal.  Leaving the
   Value of the nonterminal uninitialized is utterly harmless as long
   As the value is never used.  So really the only thing this code
   accomplishes is to quieten purify.  
  */
  memset (, 0, sizeof (yygotominor));
#endif

But in (SQLite ticket #2172) I read that:
The wireshark project (www.wireshark.org) reports that
without this code, their parser segfaults.  I'm not sure what there
parser is doing to make this happen.  This is the second bug report
from wireshark this week.  Clearly they are stressing Lemon in ways
that it has not been previously stressed...  
So they removed that #ifndef NDEBUG.

Please help me solve this issue.

Thanks & Regards,
Mahalakshmi


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

[sqlite] Is this Sorting order right?

[Mahalakshmi.m]

typedef struct 
{
  unsigned short s8_ArtistName[256];  

}STRUCT_SQLITE_MY_RECORD;

 UINT32   u32_Return;
 const UINT16 *u16_SearchResult;
 STRUCT_SQLITE_MY_RECORD  st_SQLITE_DB_Record; 
 sqlite3*   db;
 sqlite3_stmt*  insert; 

 
  u32_Return = sqlite3_open("mysqlite.db ", );

  u32_Return = sqlite3_exec(db, "pragma encoding= UTF16", 0, 0, 0);
  u32_Return = sqlite3_exec(db, "create table ARTIST(id  integer primary key
not null , ArtistName  test not null collate nocase )", 0, 0, 0);

  u32_Return = sqlite3_prepare(db,"INSERT INTO ARTIST (ArtistName)
VALUES(?);",-1,,0);
{
I will call this loop 2 times for inserting 2 records at run time.
memset(_SQLITE_DB_Record,0,sizeof(STRUCT_SQLITE_MY_RECORD));
memcpy(st_SQLITE_DB_Record.s8_ArtistName,tmp_str,strlen(tmp_str)*sizeof(
short));
u32_Return = sqlite3_bind_text16(insert,1,(char *)
st_SQLITE_DB_Record.s8_ArtistName,-1,SQLITE_STATIC); -->input will be in
UTF16 format.If it is utf8 I will convert it to utf16 and then I will bind.
u32_Return = sqlite3_step(insert);

u32_Return = sqlite3_reset(insert);
}
  u32_Return = sqlite3_finalize(insert);

  u32_Return = sqlite3_prepare(db,"SELECT ArtistName from ARTIST order by
ArtistName;",-1,,0);
  u32_Return = sqlite3_step(insert);
 
  while( u32_Return == SQLITE_ROW )
   {
u16_SearchResult=sqlite3_column_text16(insert,0);
printf(u16_SearchResult);
printf("\n");
u32_Return = sqlite3_step(insert);
   }
  u32_Return = sqlite3_finalize(insert);
  u32_Return = sqlite3_close(db);

I tried like this and I didn’t got .
   



 
 


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

[sqlite] Is this Sorting order right?

[Mahalakshmi.m]

Igor Tandetnik wrote:

>I'm not sure I understand. What exactly are you doing differently in these
>two cases? Can you quote the exact code that fails, in full?

#include 
#include 

int main() 
{
  sqlite3* db;
  sqlite3_stmt* insert; 
 unsigned short ArtistName;
  sqlite3_open(" mysqlite.db ", );

  sqlite3_exec(db, "pragma encoding = UTF16", 0, 0, 0);
  sqlite3_exec(db, "create table ARTIST(id  integer primary key not null ,
ArtistName  test not null collate nocase )", 0, 0, 0);
 
 sqlite3_prepare(db,"INSERT INTO ARTIST (ArtistName)
VALUES(?);",-1,,0);
 sqlite3_bind_text16(insert,1,ArtistName,-1,SQLITE_STATIC);
 sqlite3_step(insert);
 sqlite3_finalize(insert);

  sqlite3_stmt* select;
  sqlite3_prepare(db, " select  ArtistName  from ARTIST  order by
ArtistName  ", -1, , 0);
  while (sqlite3_step(select) == SQLITE_ROW) 
 {
    unsigned char *  Name = sqlite3_column_text16(select,  0 );
    printf("text= %s  \n", Name );
  }
  sqlite3_finalize(select);
  sqlite3_close(db);
  return 0;
}

For this I got wrong output..

Thanks & Regards,
Mahalakshmi


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

[sqlite] Is this Sorting order right?

[Mahalakshmi.m]

Igor Tandetnik wrote
>> I can't reproduce this particular problem. Here's the test I wrote:
>> Figure out what you are doing differently.

I too did the same using both UTF8 anf UTF16 .Its coming right for UTF8 but
its wrong for UTF16.

For both encoding I used the below procedure,
sqlite3_prepare(db, "insert into t(id, text) values (?, ?)", -1, 
, 0);

 unsigned short Name[64];
 sqlite3_bind_int(insert, 1, 1);
 sqlite3_bind_text16(insert, 2, Name, -1, SQLITE_STATIC);
 sqlite3_step(insert);

For UTF8 output is
id=2 text=30A2
id=1 text=6B4C
This sorting is as expected.

For UTF16 output is
id=1 text=6B4C
id=2 text=30A2
This sorting is not expected.

Where I was wrong in UTF16 encoding.
Is there any reson for coming in UTF8 and not in UTF16?

Thanks & Regards,
Mahalakshmi



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

[sqlite] Is this Sorting order right?

[Mahalakshmi.m]

Mahalakshmi wrote 
> What about Japanese Kanji?
> Will sqlite sort all the Hiragana,Katakana,Kanji.

Igor Tandetnik wrote

>> Not out of the box. You will have to implement a custom collation. Or, 
>> you can build SQLite with ICU support, then it >> will use ICU collation
>> functions.

Thanks a lot.
But I didn't get the point.
Do I want to write my own string comparison code and use that as Custom
Collation .If so where can I find the examples.(or) 
I can use just sqlite3_create_function( *gpst_SqliteInstance, "shellstatic",
0, SQLITE_UTF16, 0, shellstaticFunc, 0, 0); My database has UTF16 encoding.

Even if the collation is UTF8, Sqlite will do memcmp() say 1st record has
the Unicode 6B4C and 2nd records has 30A2.If sqlite is comparing byte by
byte means then 30A2 only has to come as first record.Is it right? But I am
not getting.

Please help to clarify this.

Thanks & Regards,
Mahalakshmi






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

[sqlite] Is this Sorting order right?

[Mahalakshmi.m]

What about Japanese Kanji? 

Will sqlite sort all the Hiragana,Katakana,Kanji.

I am having some records with starting Unicode as follows. 

 

6B4C  Kanji 歌手生活25周却記念 北島三石1 

30A2  Katakana アンテナ  

 

I am storing it using sqlite3_bind_text16().

But if I sort the above two the Unicode with 6B4C comes first but my desired
output is

30A2  Katakanaアンテナ 

6B4C  Kanji   歌手生活25周却記念 北島三石1

 

Where I was wrong.Kindly advice.

 

Thanks & Regards,

Mahalakshmi

 

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

[sqlite] Select by order Speed

[Mahalakshmi.m]

My table has records as follows:

Id  Track
1   zz
2   aaa
3   cc
4   aaa
5   aaa
6   aaa

1st 2 records -> SELECT Id, Track FROM MUSIC ORDER BY Track LIMIT 2;

Output: 2   aaa
4   aaa

Next 2->SELECT Id, Track FROM MUSIC WHERE Track > aaa ORDER BY Track LIMIT 2

Output: 3   cc
1   zz

But I want  5   aaa
6   aaa

Is there any other way for doing this or I have to use Limit, Offset only to
solve this.


For Previous 2:
SELECT Id, Track FROM MUSIC WHERE Track < aaa ORDER BY Track DESC LIMIT 2;

Thanks & Regards,
Mahalakshmi



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

[sqlite] speed for select statements

[Mahalakshmi.m]

MY Table is:

"CREATE TABLE ARTIST(ArtistId INTEGER PRIMARY KEY,ArtistName TEXT NOT NULL
COLLATE NOCASE, ArtistTrackCount INTEGER, UNIQUE(ArtistName));"

"CREATE TABLE ALBUM(AlbumId INTEGER PRIMARY KEY,AlbumName TEXT NOT NULL
COLLATE NOCASE,AlbumTrackCount INTEGER,UNIQUE(AlbumName));"

"CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY,Track TEXT NOT NULL,URL TEXT NOT
NULL,Album_Id INTEGER,Artist_Id INTEGER);"

"CREATE UNIQUE INDEX ARTIST_idx ON ARTIST (ArtistName);"
"CREATE UNIQUE INDEX ALBUM_idx ON ALBUM (AlbumName);"
"CREATE INDEX MUSIC_idx ON MUSIC (Track);"
"CREATE INDEX MUSIC_ARTIST_idx ON MUSIC (Artist_Id);"
"CREATE INDEX MUSIC_ALBUM_idx ON MUSIC (Album_Id);"
"CREATE INDEX MUSIC_ARTIST_ALBUM_idx ON MUSIC (Artist_Id, Album_Id);"

I am using
First 10 --> "SELECT * FROM ARTIST ORDER BY ArtistName LIMIT 10 ;" 
Next 10 ie., 11 to 20 --> "SELECT * FROM ARTIST WHERE ArtistName > ? ORDER
BY ArtistName LIMIT 10 ;"
Previous 10 -->"SELECT * FROM ARTIST WHERE ArtistName < ? ORDER BY
ArtistName DESC LIMIT ? ;"

The above statements provides best performance .i have used < ,> since I
want the results to be in ORDER BY.

If I don't want to use ORDER BY then how can I optimize without using
OFFSET.
If I am using OFFSET then it more time.i am having 6 records.
Is there any other way to optimize.

Another doubt is : 
For this querry "SELECT * FROM ARTIST ORDER BY ArtistName LIMIT 10 ;" will
it use ARTIST_idx or not.

Thanks & Regards,
Mahalakshmi




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

[sqlite] Update

[Mahalakshmi.m]

Hi,

"CREATE TABLE ALBUM(AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT
NULL COLLATE NOCASE ,AlbumTrackCount INTEGER,UNIQUE(AlbumName));"

AlbumId AlbumName   AlbumTrackCount
1   aaa 3
2   ddd 2
3   ccc 1   

Here I am maintaining the Number of track for that particular Album in
AlbumTrackCount. Bcoz I need to find the total number of track so instead of
using "select Count(*)  from MUSIC where Album_Id = 1 ;" I will just read
the AlbumTrackCount from ALBUM table.This speed up my performance.

"CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
NULL,Album_Id INTEGER);"

Id  Track   Album_Id
1   t1  1
2   t2  1
3   t3  1
4   t4  2
5   t5  2
6   t6  3

I want to update all the Album to some new name say 'xxx' then i have to
delete all the records in ALBUM table and to insert one new Album  with name
as 'xxx' and the AlbumTrackCount should now become 7.After that I have to
change the Album_Id in MUSIC also.

So after updating 
AlbumId AlbumName   AlbumTrackCount
1   xxx 7
And all the Album_Id value should be 1.

Can any one help to solve this.


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

[sqlite] Update Trigger

[Mahalakshmi.m]

Dennis Wrote:

>If you want to update the AlbumName field, you must do that with an 
>update statement running on the Album table, not the Music table, since 
>that is where the AlbumName field is stored. You haven't said what you 
>want to update the AlbumName or ArtistName to. You probably have a 
>condition, that you also haven't described, that selects which records 
>in the table to update. Generally it will look something like this.

k.say there are 4 records in the MUSIC table This table has only the
Album_id and not the Albumname .But If I want  to update all the AlbumName
for all the records in MUSIC table to only one AlbumName say 'Album1' then
the rest of the AlbmName has to be deleted in the ALBUM Table and Album1 Id
should be provided as Album_Id for all the records in the MUSIC table.

I think then I have to delete all the records in the ALBUM Table and insert
one new record with the new AlbumName.Then I have to update Albim_Id for all
the records in the MUSIC table.Am I right or is there any other way.


>If this isn't what you are looking for, you will have to describe your 
>problem in more detail (i.e. what you are trying to do, an example of 
>before and after data, etc.) before anyone can provide more assistance.
>Original Table Before Update:

 "CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT
 NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));"
 
 ArtistId   ArtistName  YomiArtistName  
 10 bbb BBB
 11 xxx XXX
 12 aaa AAA

 "CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
 NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id
 INTEGER);"
 
 Id Track   YomiTrack  URLAlbum_Id  Artist_Id
 1  trak1   TRAK1 c:/trak1  22   10   
 2  songSONG  c:/song   21   11   
 3  abc ABC   c:/abc23   12   

Delete * from ARTIST.
Insert into ARTIST (ArtistName,YomiArtistName) values ('Album1');
Update MUSIC SET Album_Id = ( select Albumid from ALBUM where ArtistName
='Album1');

Modified Table After Update:

"CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT
 NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));"
 
 ArtistId   ArtistName  YomiArtistName  
 1  Album1  ALBUM1

 "CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
 NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id
 INTEGER);"
 
 Id Track   YomiTrack  URLAlbum_Id  Artist_Id
 1  trak1   TRAK1 c:/trak1  110   
 2  songSONG  c:/song   111   
 3  abc ABC   c:/abc112   


Similarly I will change the artist name of all the records also.
 

Thanks & Regards,
Mahalakshmi




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

[sqlite] Update Trigger

[Mahalakshmi.m]

Hi,

I am having 4 records in my database.
I am using Joins method.

My Table Looks like:

"CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT
NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));"

ArtistIdArtistName  YomiArtistName  
10  bbb BBB
11  xxx XXX
12  aaa AAA

"CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT
NULL COLLATE NOCASE ,YomiAlbumName TEXT NOT NULL,UNIQUE(AlbumName));"

AlbumId AlbumName   YomiAlbumName
20  zzz ZZZ
21  ccc CCC
22  bbb BBB

"CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id
INTEGER,AlbumArtist_Id INTEGER);"

Id TrackYomiTrack   URL   Album_Id  Artist_Id
AlbumArtist_Id
1  trak1TRAK1 c:/trak1  22   10   1
2  song SONG  c:/song   21   11   2
3  abc  ABC   c:/abc23   12   3

Now I want to Update the AlbumName or ArtistName for all the records in
MUSIC table . How can I do.If I update All the Records to one New AlbumName
Then the rest of the AlbumName should be deleted.

Can I use Update Trigger I tried but not working.

Please help to solve this.

Thanks & Regards,
Mahalakshmi



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

[sqlite] Count(1)

[Mahalakshmi.m]

Hi,

I am having 4 records in my Harddisk.
My Processor speed is 400 Mhz.

For "SELECT COUNT(1) FROM MUSIC ;" its getting more time to display the
count.
I have also tried with "SELECT COUNT(*) FROM MUSIC ;This also take more
time.
Is there any other way we can get the Total number of records.
Please help to solve this.

Thanks & Regards,
Mahalakshmi


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

[sqlite] Indexing and Search speed

[Mahalakshmi.m]

Hi,

I am having 4 records in my database.
I am using Joins method.

My Table Looks like:

"PRAGMA encoding = UTF16;" 

"CREATE TABLE ALBUMARTIST (AlbumArtistId INTEGER PRIMARY KEY NOT NULL,
AlbumArtistName TEXT NOT NULL COLLATE NOCASE, YomiAlbumArtistName TEXT NOT
NULL, UNIQUE (AlbumArtistName));"

CREATE UNIQUE INDEX ALBUMARTIST_idx ON ALBUMARTIST (YomiAlbumArtistName);

AlbumArtistId   AlbumArtistName YomiAlbumArtistName
1   zzz/bbb ZZZ/BBB
2   ccc/xxx CCC/XXX
3   bbb/aaa BBB/AAA

"CREATE TABLE ARTIST (ArtistId INTEGER PRIMARY KEY NOT NULL,ArtistName TEXT
NOT NULL COLLATE NOCASE ,YomiArtistName TEXT NOT NULL,UNIQUE(ArtistName));"

CREATE UNIQUE INDEX ARTIST_idx ON ARTIST (YomiArtistName);

ArtistIdArtistName  YomiArtistName  
10  bbb BBB
11  xxx XXX
12  aaa AAA

"CREATE TABLE ALBUM (AlbumId INTEGER PRIMARY KEY NOT NULL,AlbumName TEXT NOT
NULL COLLATE NOCASE ,YomiAlbumName TEXT NOT NULL,UNIQUE(AlbumName));"

CREATE UNIQUE INDEX ALBUM_idx ON ALBUM (YomiAlbumName);

AlbumId AlbumName   YomiAlbumName
20  zzz ZZZ
21  ccc CCC
22  bbb BBB

"CREATE TABLE MUSIC(Id INTEGER PRIMARY KEY NOT NULL,Track TEXT NOT
NULL,YomiTrack TEXT NOT NULL,URL TEXT NOT NULL,Album_Id INTEGER,Artist_Id
INTEGER,AlbumArtist_Id INTEGER);"

CREATE UNIQUE INDEX MUSIC_idx ON MUSIC (YomiTrack);

Id TrackYomiTrack   URL   Album_Id  Artist_Id
AlbumArtist_Id
1  trak1TRAK1 c:/trak1  22   10   1
2  song SONG  c:/song   21   11   2
3  abc  ABC   c:/abc23   12   3

I am using the following Searching Statements.

"SELECT * FROM ARTIST ORDER BY YomiArtistName;"  
Idx -> YomiArtistName

"SELECT * FROM ALBUMARTIST ORDER BY YomiAlbumArtistName;"
Idx -> YomiAlbumArtistName

SELECT Track,YomiTrack,URL FROM MUSIC ORDER BY YomiTrack;
Idx -> YomiTrack

SELECT AlbumId,AlbumName,YomiAlbumName FROM ALBUM ORDER BY YomiAlbumName;
Idx -> YomiAlbumName

Am I rightly using Indexing or not needed.
To speed up the Search Statements Below Do I want to any Indexing .If so for
which Fields I have to Add.

select DISTINCT ALBUM.AlbumId,ALBUM.AlbumName,ALBUM.YomiAlbumName from ALBUM
inner join (select * from MUSIC where Artist_Id =?) types on
ALBUM.AlbumId=types.Album_Id order by ALBUM.YomiAlbumName;

select types.Track,types.URL from ARTIST inner join (select * from MUSIC
where Album_Id =?) types on ARTIST.ArtistId=types.Artist_Id order by
ARTIST.YomiArtistName;

select MUSIC.Track,MUSIC.URL from MUSIC inner join ARTIST on
ARTIST.ArtistId=MUSIC.Artist_Id order by ARTIST.YomiArtistName ;

select types.Track,types.URL from ALBUM inner join (select * from MUSIC
where Artist_Id =?) types on ALBUM.AlbumId=types.Album_Id order by
ALBUM.YomiAlbumName ;

SELECT Track,URL FROM MUSIC WHERE Artist_Id = ? and Album_Id = ?;

select MUSIC.Track,MUSIC.URL from MUSIC inner join ALBUM on
MUSIC.Album_Id=ALBUM.AlbumId order by ALBUM.YomiAlbumName;

SELECT Track,URL FROM MUSIC WHERE AlbumArtist_Id = ?;

I am Using this for real time Application and my processor speed is 400MHz.
Foe Each Search I will get 100 results and Using the "Scrolling Cursor
Method" I will retrive the next for Sorted Fields.

Please help to increase my performance speed.

Thanks & Regards,
Mahalakshmi



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

[sqlite] sqlite3_mprintf and Prepare

[Mahalakshmi.m]

Igor Tandetnik Wrote:
>The first case is slower, since it has to make an extra sqlite3_mprintf 
>call (that achieves precisely nothing).

Thanks a lot Igor.Its am clear now.



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

[sqlite] sqlite3_mprintf and Prepare

[Mahalakshmi.m]

Hi,

I want to know why we have to use sqlite3_mprintf before sqlite3_prepare()

Case 1:
Query = sqlite3_mprintf ("DELETE FROM MUSIC WHERE URL = ?;");
sqlite3_prepare(db, Query ,-1,,0);
sqlite3_free(Query);

case 2:
We can also use directly - sqlite3_prepare(db, "DELETE FROM MUSIC WHERE URL
= ?;",-1,,0);

Will there be any performance difference between case 1 and case2.Kindly
help me to solve.Right now I am directly passing the Sqlite staments inside
sqlite3_prepare().Do I need to use sqlite3_mprintf or not needed.

Sometimes I am using sprintf instead of sqlite3_mprintf.will both have the
same functionality.After calling sqlite3_mprintf()we are freeing using
sqlite3_free but not sprintf().

sprintf(buff,"DELETE FROM MUSIC WHERE URL = ?;");
sqlite3_prepare(db, buff,-1,,0);


Thanks & Regards,
Mahalakshmi


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

[sqlite] Rowid After Sorting

[Mahalakshmi.m]

Dennis Cote wrote:
>Then you should add an index on the Name column and use that to process 
>your queries in Name order more quickly.
>create index on MyTable(Name);

Thanks a lot Dennis. My process is more fast by means indexing.

Dennis Cote wrote:
>If you really insist on reordering your table, you must copy the data 
>somewhere else, empty the table, and reinsert the data in the order you 
>want the rowid to present. Note, this will not work if you plan on 
>inserting or deleting data after this initial insert. 

Ya. Right now each time when I insert or delete I will drop the tbl and
insert once again like: create temp table t as select Id, Name from t order
by Name; I think no other way than this will work out.

Mahalakshmi.m wrote:
> So, to find the index of a name, which is in sorted order, I need the
>Rowid to be changed as shown in case 2 rather than in case 1.

Dennis Cote wrote:
>Why do you want to find the index of a Name?
>Tables in SQL databases are not arrays. You don't use an index to 
>retrieve the data. Tables are more like unordered sets of data.

Bcoz in my Application as input - I will give the starting letter say 'c'
Then as output - I need the rowid of the name that is starting with 'c' if
no name starts with that character then the rowid of the name which is next
should be provided along with the name.

For eg, I will create one temp tblb where I will store the name in sorted
order itsef s follows:
Rowid   id  name
1   4   aaa
2   2   bb
3   1   eee
4   3   zzz

Input for my application is - c
Required output is  - rowid -3 and name - eee

I will use the following querry:
Select rowid,name from tbl where name >= 'c'; 
Think this gives a clear idea abt my requirement.


Thanks & Regards,
Mahalakshmi.M








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

Re: [sqlite] Rowid After Sorting

[Mahalakshmi.m]

Dennis Cote wrote:
>Then you should add an index on the Name column and use that to process 
>your queries in Name order more quickly.
>create index on MyTable(Name);

Thanks a lot Dennis. My process is more fast by means indexing.

Dennis Cote wrote:
>If you really insist on reordering your table, you must copy the data 
>somewhere else, empty the table, and reinsert the data in the order you 
>want the rowid to present. Note, this will not work if you plan on 
>inserting or deleting data after this initial insert. 

Ya. Right now each time when I insert or delete I will drop the tbl and
insert once again like: create temp table t as select Id, Name from t order
by Name; I think no other way than this will work out.

Mahalakshmi.m wrote:
> So, to find the index of a name, which is in sorted order, I need the
>Rowid to be changed as shown in case 2 rather than in case 1.

Dennis Cote wrote:
>Why do you want to find the index of a Name?
>Tables in SQL databases are not arrays. You don't use an index to 
>retrieve the data. Tables are more like unordered sets of data.

Bcoz in my Application as input - I will give the starting letter say 'c'
Then as output - I need the rowid of the name that is starting with 'c' if
no name starts with that character then the rowid of the name which is next
should be provided along with the name.

For eg, I will create one temp tblb where I will store the name in sorted
order itsef s follows:
Rowid   id  name
1   4   aaa
2   2   bb
3   1   eee
4   3   zzz

Input for my application is - c
Required output is  - rowid -3 and name - eee

I will use the following querry:
Select rowid,name from tbl where name >= 'c'; 
Think this gives a clear idea abt my requirement.


Thanks & Regards,
Mahalakshmi.M








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

[sqlite] Rowid After Sorting

[Mahalakshmi.m]

1)
SELECT rowid,Id,Name FROM MyTable ORDER BY Name;

Rowid   Id  Name

 

4 4  aaa

3 3  bbb

2 2  xxx

1 1  zzz


2)
"create table Temp as select Name from Mytable order by Name;" 
RowidId  Name

1 4  aaa

2 3  bbb

3 2  xxx

4 1  zzz


I wish to perform all operations in my code with sorted order of the Name
field.

So, to find the index of a name, which is in sorted order, I need the Rowid
to be changed as shown in case 2 rather than in case 1.








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

[sqlite] Performance of bulk insertion

[Mahalakshmi.m]

Hi,

I am working in 3.3.6

The table schema of my code is as follows.



"CREATE TABLE ALBUMARTIST(

AlbumArtistId INTEGER PRIMARY KEY NOT NULL,

AlbumArtistName TEXT NOT NULL COLLATE
NOCASE,UNIQUE(AlbumArtistName));"

 

"CREATE TABLE ARTIST(

ArtistId INTEGER PRIMARY KEY NOT NULL,

ArtistName TEXT NOT NULL COLLATE NOCASE ,

   UNIQUE(ArtistName));"


"CREATE TABLE ALBUM(

AlbumId INTEGER PRIMARY KEY NOT NULL,

AlbumName TEXT NOT NULL COLLATE NOCASE ,

   UNIQUE(AlbumName));"




 "CREATE TABLE MUSIC(

Id INTEGER PRIMARY KEY NOT NULL,

Track TEXT NOT NULL,

URL TEXT NOT NULL,

Album_Id INTEGER,

Artist_Id INTEGER,

AlbumArtist_Id INTEGER);"


 "CREATE TABLE PLAYLIST (

PlayListId INTEGER PRIMARY KEY NOT NULL,

PlayListTrack TEXT,

PlayListUrl TEXT);"

 

"CREATE TRIGGER fkdc_MUSIC AFTER DELETE ON MUSIC FOR EACH ROW  

BEGIN

DELETE FROM ALBUM WHERE AlbumId = old.Album_Id and not exists
(SELECT Id FROM MUSIC WHERE Album_Id = old.Album_Id); 

DELETE FROM ARTIST WHERE ArtistId = old.Artist_Id and not exists
(SELECT Id FROM MUSIC WHERE Artist_Id = old.Artist_Id);

 DELETE FROM ALBUMARTIST WHERE AlbumArtistId = old.AlbumArtist_Id
and not exists (SELECT Id FROM MUSIC WHERE AlbumArtist_Id =
old.AlbumArtist_Id);

 DELETE FROM TRACKLIST WHERE PlayListUrl = old.URL and not exists
(SELECT Id FROM MUSIC WHERE URL = old.URL);

END;"

 

 My database has 4 records.

My need is to add all the Track  in the Table MUSIC to the Table
TRACKLIST.For that i have used the following code.

 

 while(u32_PlayListRecordCount < TotalRecordCount)
 {

   if ( sqlite3_prepare(pst_SqliteCallback->db," SELECT Track, URL FROM
MUSIC LIMIT 100 OFFSET ? ; ",-1,_PlayListPrepareStmt,0)!= SQLITE_OK) 
   {
return SQLITE_DB_ERROR;
   }
   else
   {
sqlite3_bind_int(pst_PlayListPrepareStmt,1, u32_OffsetValue);

 

Now I will step and store all the 100 results in one Buffer.

   }
  
  if(e_ReturnStatus == SQLITE_DB_SUCCESS)
  {
   if ( sqlite3_prepare(pst_SqliteCallback->db,"INSERT INTO TRACKLIST
(PlayListTypeId,PlayListTrack,PlayListUrl) VALUES
(?,?,?);",-1,_PlayListPrepareStmt,0)!= SQLITE_OK) 
   {
return SQLITE_DB_ERROR;
   }

 

   else
   {
for(u32_Offset = 0;u32_Offset < gu32_PlayListindex;u32_Offset++)

Here gu32_PlayListindex  is the count of  "SELECT count(*) FROM MUSIC LIMIT
100 OFFSET ? ;"


{
 sqlite3_bind_int(pst_PlayListPrepareStmt,1,PlayListIndex);
 
sqlite3_bind_text(pst_PlayListPrepareStmt,2,st_PlayList[u32_Offset].PlayList
TrackBuffer,-1,SQLITE_STATIC);
 
sqlite3_bind_text(pst_PlayListPrepareStmt,3,st_PlayList[u32_Offset].PlayList
UrlBuffer,-1,SQLITE_STATIC);
u32_Return = sqlite3_step(pst_PlayListPrepareStmt);
sqlite3_reset(pst_PlayListPrepareStmt);
 }
u32_Return = sqlite3_finalize(pst_PlayListPrepareStmt);
u32_ PlayListRecordCount = u32_ PlayListRecordCount +
gu32_PlayListindex;
   }
  }
  else
  {
   return SQLITE_DB_ERROR;
  }
 }

Am I doing right or it's a lengthy process.If so suggest some other way for
inserting the records.
 

But  this takes very long time to insert all the Tracks into
playlist.Totally I need to insert 4 records.

 

Is there anyother way to insert huge number of data in to another table..

I want to insert  each record after getting the Track from MUSIC select
statement.Can we use Triggers for solving this.

Can anyone help to solve this .

 

Thanks & Regards,

Mahalakshmi.M


 

 

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

[sqlite] Rowid After Sorting

[Mahalakshmi.m]

Hi,

I am working in 3.3.6 and my table looks like.

 

Id  -  Integer Primary Key

Name-   Text

 

Id Name

1 zzz

2 xxx

3 bbb

4 aaa

 

SELECT rowid,Id,Name FROM MyTable ORDER BY Name;

 

Rowid   Id  Name

 

4 4  aaa

3 3  bbb

2 2  xxx

1   1  zzz

 

But I need my rowid to be chaged as follows.

 

Rowid   Id  Name

 

1 4  aaa

2 3  bbb

3 2  xxx

4   1  zzz

 

I tried with Views but its rowid is not changed.

 

But by creating one new table like

 "create table Temp as select Name from Mytable order by Name;" 

gives the desired result as above.

 

Its taking more time for this.

So I there any other way I can do the same without creating table because in
My table I am having many

 fields and each time I will create and drop the table for each fields.

 

Can anyone please help to solve this.

 

Thanks & Regards,

Mahalakshmi.M

 

 

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

[sqlite] Prepare Statement

[Mahalakshmi.m]

If my Table is as follows:
create table Music ( id integer not null primary key,
classificationCode integer, input text) <<

Table:
id  classificationCode  input
--  --  -
1   1   aaa
2   0   1345
3   1   asdf

At this point, 

sqlite3_prepare(gpst_SqliteInstance, "SELECT id,
classificationCode, input FROM MUSIC WHERE input
>= ? LIMIT 1;", -1,_SearchPrepareStmt, 0);

Can I bind the unsigned short value [ie., like 0x0065 for English and 0x3045
for Japanese] to its corresponding string value.is it possible.

Unsigned short temp;
For eg,
If temp = 0x0065 then its corresponding english string 'a' should come while
binding.It works out by using sprintf();But If temp = 0x30E4 then its
corresponding Japanese string should come.For this sprintf() is not working.

Can anyone please help to solve this.

Regards,
Mahalakshmi





-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Stephen Oberholtzer
Sent: Friday, February 29, 2008 2:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Prepare Statement

On Thu, Feb 28, 2008 at 9:22 AM, Mahalakshmi.m
<[EMAIL PROTECTED]> wrote:
>
>
>  Hi,
>  My table looks like:
>  IdName
>  1 1aaa
>  2 01345
>  3 1asdf
>
>  I want to bind unsigned short as text. i.e, If the Unsighed short is 
> 0x0061  I want to bind it as 'a'.
>
>  My Prepare statement is as follows:
>
>  Unsigned char u8_ClassificationCode=1;
>
>  Unsigned short u16_Input=0x0061;
>
>  if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC 
> WHERE  Name >= '%d%c'  LIMIT 1;",-1,_SearchPrepareStmt,0)!= 
> SQLITE_OK)
>
>  {
>
> return SQLITE_DB_ERROR;
>
>  }
> 
> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
>
> sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
>  *)u16_Input,-1,SQLITE_STATIC);
>
>  }
>

Since nobody else mentioned it: there's something seriously wrong with your
database design.

But first: Your usage of sqlite3_bind_text16 is incorrect.   The
fourth argument, -1, means "My string is NUL-terminated. Use strlen() to
figure out how long my string is and use that.".

However, for that to always work correctly, u16_input needs to be an array
with a NUL terminator:

>> unsigned short u16_input[] = { 'a', '\0' }; <<

Anyway, back to what I was saying: your database design needs rethinking.
1NF (http://en.wikipedia.org/wiki/First_normal_form)
states that a column should only have one value.   However, you seem
to be combining *two* values (Classification Code and Input) into one column
(Name).  Therefore, you should be doing this:

>> create table Music ( id integer not null primary key,
classificationCode integer, input text) <<

Table:
id  classificationCode  input
--  --  -
1   1   aaa
2   0   1345
3   1   asdf

At this point, you would do this:

>> sqlite3_prepare(gpst_SqliteInstance, "SELECT id,
classificationCode, input FROM MUSIC WHERE classificationCode = ? AND input
>= ? LIMIT 1;", -1,_SearchPrepareStmt, 0); << Note that, if you you want
the original form, you can do
>> sqlite3_prepare(gpst_SqliteInstance, "SELECT id, classificationCode
|| input as Name FROM MUSIC WHERE classificationCode = ? AND input >=
? LIMIT 1;", -1,_SearchPrepareStmt, 0); << This will convert
classificationCode to a string and join it against the 'input' column to
return your original Name.

>> sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);
sqlite3_bind_text(pst_SearchPrepareStmt, 2, "a", -1, SQLITE_STATIC); <<

This also means you can index the string portion of your Name column
separately, and quickly search for something with a specific name without
knowing its classification.

--
-- Stevie-O
Real programmers use COPY CON PROGRAM.EXE
___
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] Prepare Statement

[Mahalakshmi.m]

 

Hi,

 

My table looks like:

 

IdName

1 1aaa

2 01345

3 1asdf

 

 

I want the statement to be like:

"SELECT id, Name FROM MUSIC WHERE Name >= '1a'  LIMIT 1;"

But using prepare I could not able to get the desired statements.

I want to bind unsigned short as text. i.e, If the Unsighed short is 0x0061
I want to bind it as 'a'.

 

My Prepare statement is as follows:

 

Unsigned char u8_ClassificationCode=1;

Unsigned short u16_Input=0x0061;

if ( sqlite3_prepare(gpst_SqliteInstance,"SELECT id, Name FROM MUSIC WHERE
Name >= '%d%c'  LIMIT 1;",-1,_SearchPrepareStmt,0)!= SQLITE_OK) 

{

return SQLITE_DB_ERROR;

}

else

{

sqlite3_bind_int(pst_SearchPrepareStmt,1,u8_ClassificationCode);

sqlite3_bind_text16(pst_SearchPrepareStmt,2,(char
*)u16_Input,-1,SQLITE_STATIC);

}

 

For the above the return status of sqlite3_prepare is success  but not
properly binded.

 

Please help me to solve this.

 

Thanks & Regards,

Mahalakshmi.M

 

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

[sqlite] Like operator

[Mahalakshmi.m]

Hi,
I am working in 3.3.6
My table is as follows:

"create table MUSIC(id integer primary key,Album text,Artist text);"
IdAlbum Artist
1   z
2   w
3   s
4   t

I want to sort the Album First and then I have to tell the rowid of the
particular string pattern.If that pattern is not present it has to provide
the next string match.

I tried as follows:
"CREATE TABLE IF NOT EXISTS Temp as SELECT Album from MUSIC ORDER BY Album;

Rowid   Id  Album   
1   4   
2   3   
3   2   
4   1   

SELECT rowid,Album FROM Temp WHERE Album like 'c%'; 
Output: rowed -> 2 and Album -> 

My doubt is for this statement
"SELECT rowid , Album FROM Temp WHERE Album like 'b%'; " I am not having any
Album with match pattern starting with b so I want the rowed and Album for
the string next to the provided ie, rowed ->3  and Album -> .

Can any one please help to solve my problem.

Thanks & Regards,
Mahalakshmi.M




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

[sqlite] Sorting Japanese records

[Mahalakshmi.m]

Hi,

 

I am working in 3.3.6

 

My database looks as follows:

 

"Create table MUSIC (id integer primary key,Track text);"

 

I have inserted some Japanese records inside and I tried to list all the
tracks sorted by Tracks.

 

"select Tracks from MUSIC order by Track;" [ All records are Japanese Full
Size Katakana ]

 

But I am not getting the results in sorting order.

I have created the table in UTF-16 encoding format.this I did by just
enabling the "PRAGMA encoding = UTF16;" before creating the table.

Then while inserting I will use as follows.

 

if (
sqlite3_prepare(pst_CallbackInstance->db,"INSERT OR IGNORE INTO MUSIC (
Track ) VALUES ( ? );",-1,_ PrepareStmt,0)!= SQLITE_OK) 

{

return SQLITE_DB_ERROR;

}

else

{

sqlite3_bind_text16 (pst_PrepareStmt,1,
TrackName,-1,SQLITE_STATIC);



sqlite3_step (pst_ PrepareStmt);

 

i_Return = sqlite3_finalize (pst_
PrepareStmt);

 

if( i_Return || ps8_SqliteErrMsg )

{

if( ps8_SqliteErrMsg!=0 )

{

#ifdef
PRINTF_ENABLED

 
printf("SQL error: %s\n", ps8_SqliteErrMsg);

#endif

 
sqlite3_free(ps8_SqliteErrMsg);

ps8_SqliteErrMsg
= 0;

}

}

else

{

e_ReturnStatus =
SQLITE_DB_SUCCESS;  

}

}

 

Will the above function  insert the track name in UTF16 format inside
Database  or I am wrong some where ?

 

I want the Tracks to be in Sorted order [ All track are inserted as UTF16 -
Japanese letters ]

Can I use UTF8 itself for doing this.

 

Can Anyone help to solve this.

 

Thanks & Regards,

Mahalakshmi.M

 

 

 

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

Re: [sqlite] sqlite-users Digest, Vol 2, Issue 24

[Mahalakshmi.m]

I am interested to join in this [EMAIL PROTECTED]

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Sunday, February 10, 2008 10:30 PM
To: sqlite-users@sqlite.org
Subject: sqlite-users Digest, Vol 2, Issue 24

Send sqlite-users mailing list submissions to
sqlite-users@sqlite.org

To subscribe or unsubscribe via the World Wide Web, visit
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
[EMAIL PROTECTED]

You can reach the person managing the list at
[EMAIL PROTECTED]

When replying, please edit your Subject line so it is more specific
than "Re: Contents of sqlite-users digest..."


Today's Topics:

   1. Re: looping over a result set in a query (Alexander Batyrshin)
   2. Re: Quoting identifier vs literal (was: Version 3.2.2) (Kees Nuyt)
   3. Re: May one software write to the SQLite database while a
  other read the same SQLite database ? (Dusan Gibarac)


--

Message: 1
Date: Sat, 9 Feb 2008 18:12:35 +0100
From: "Alexander Batyrshin" <[EMAIL PROTECTED]>
Subject: Re: [sqlite] looping over a result set in a query
To: [EMAIL PROTECTED],  "General Discussion of SQLite Database"

Message-ID:
<[EMAIL PROTECTED]>
Content-Type: text/plain; charset=ISO-8859-1

> Is it possible to refine/combine the above two sets of queries into one?

Yes. It's possible:

A)
SELECT e.to_node_id AS node_id FROM edge e WHERE
e.from_node_id = $node_id
  UNION
SELECT e.from_node_id AS node_id FROM edge e WHERE
e.to_node_id = $node_id


B)
SELECT count(edge_id)
FROM edge
WHERE
  edge.to_node_id IN (Query_A)
   OR
  edge.from_node_id IN (Query_A)


--

Message: 2
Date: Sat, 09 Feb 2008 19:03:36 +0100
From: Kees Nuyt <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Quoting identifier vs literal (was: Version
3.2.2)
To: General Discussion of SQLite Database 
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain; charset=us-ascii

On Sat, 09 Feb 2008 17:16:57 +0100, you wrote:

>On Sat, 9 Feb 2008 23:51:03 +1100, you wrote:

[..]

>>create table MyTable( MyField );
>>alter table MyTable rename to MyNewTable;
>>select SQL from SQLite_Master;
>>
>>which gives:
>>
>>CREATE TABLE 'MyNewTable'( MyField )
>>
>>SQLite should instead use the quotes (if any) used in the alter table  
>>command.
>>
>>Tom
>>BareFeet
>
>Reproduced with v3.5.4. I would say that's a bug. You
>could open a bug ticket for it (after checking the
>most recent version still behaves like that).

No need to file a ticket, it has been repaired today:
http://www.sqlite.org/cvstrac/chngview?cn=4781 
-- 
  (  Kees Nuyt
  )
c[_]


--

Message: 3
Date: Sun, 10 Feb 2008 07:58:48 -0700
From: "Dusan Gibarac" <[EMAIL PROTECTED]>
Subject: Re: [sqlite] May one software write to the SQLite database
while a other read the same SQLite database ?
To: 
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain;   charset="us-ascii"

We are usually in situation that two processes requiring database access
work at the same time. Any of them can impose implicit lock updating
database. Does it mean that in such scenario we must use
sqlite3_busy_timeout() in front of each database access  call to manage a
better way concurrent work? 

 

Dusan Gibarac

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 23, 2008 9:45 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] May one software write to the SQLite database while a
other read the same SQLite database ?

 

Pierre8r <[EMAIL PROTECTED]> wrote:

> Hello,

> 

> One SQLite database on my PC.

> Two softwares.

> May one software write to the SQLite database while a other read the 

> same SQLite database  ?

> 

 

Your programs cannot be reading and writing at exactly the

same instant in time.  But both programs can have the database

open for reading and writing.  While one program is writing,

the other is blocked from reading.  But the write normally 

only takes a few dozen milliseconds.  Surely your reader can

wait that long.

 

The waiting is handled for you automatically if you set

 

sqlite3_busy_timeout()

 

--

D. Richard Hipp <[EMAIL PROTECTED]>

 

 


-

To unsubscribe, send email to [EMAIL PROTECTED]


-



--

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


End of sqlite-users Digest, Vol 2, Issue 24

[sqlite] FW: sqlite-users Digest, Vol 2, Issue 24

[Mahalakshmi.m]

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Sunday, February 10, 2008 10:30 PM
To: sqlite-users@sqlite.org
Subject: sqlite-users Digest, Vol 2, Issue 24

Send sqlite-users mailing list submissions to
sqlite-users@sqlite.org

To subscribe or unsubscribe via the World Wide Web, visit
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
[EMAIL PROTECTED]

You can reach the person managing the list at
[EMAIL PROTECTED]

When replying, please edit your Subject line so it is more specific
than "Re: Contents of sqlite-users digest..."


Today's Topics:

   1. Re: looping over a result set in a query (Alexander Batyrshin)
   2. Re: Quoting identifier vs literal (was: Version 3.2.2) (Kees Nuyt)
   3. Re: May one software write to the SQLite database while a
  other read the same SQLite database ? (Dusan Gibarac)


--

Message: 1
Date: Sat, 9 Feb 2008 18:12:35 +0100
From: "Alexander Batyrshin" <[EMAIL PROTECTED]>
Subject: Re: [sqlite] looping over a result set in a query
To: [EMAIL PROTECTED],  "General Discussion of SQLite Database"

Message-ID:
<[EMAIL PROTECTED]>
Content-Type: text/plain; charset=ISO-8859-1

> Is it possible to refine/combine the above two sets of queries into one?

Yes. It's possible:

A)
SELECT e.to_node_id AS node_id FROM edge e WHERE
e.from_node_id = $node_id
  UNION
SELECT e.from_node_id AS node_id FROM edge e WHERE
e.to_node_id = $node_id


B)
SELECT count(edge_id)
FROM edge
WHERE
  edge.to_node_id IN (Query_A)
   OR
  edge.from_node_id IN (Query_A)


--

Message: 2
Date: Sat, 09 Feb 2008 19:03:36 +0100
From: Kees Nuyt <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Quoting identifier vs literal (was: Version
3.2.2)
To: General Discussion of SQLite Database 
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain; charset=us-ascii

On Sat, 09 Feb 2008 17:16:57 +0100, you wrote:

>On Sat, 9 Feb 2008 23:51:03 +1100, you wrote:

[..]

>>create table MyTable( MyField );
>>alter table MyTable rename to MyNewTable;
>>select SQL from SQLite_Master;
>>
>>which gives:
>>
>>CREATE TABLE 'MyNewTable'( MyField )
>>
>>SQLite should instead use the quotes (if any) used in the alter table  
>>command.
>>
>>Tom
>>BareFeet
>
>Reproduced with v3.5.4. I would say that's a bug. You
>could open a bug ticket for it (after checking the
>most recent version still behaves like that).

No need to file a ticket, it has been repaired today:
http://www.sqlite.org/cvstrac/chngview?cn=4781 
-- 
  (  Kees Nuyt
  )
c[_]


--

Message: 3
Date: Sun, 10 Feb 2008 07:58:48 -0700
From: "Dusan Gibarac" <[EMAIL PROTECTED]>
Subject: Re: [sqlite] May one software write to the SQLite database
while a other read the same SQLite database ?
To: 
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain;   charset="us-ascii"

We are usually in situation that two processes requiring database access
work at the same time. Any of them can impose implicit lock updating
database. Does it mean that in such scenario we must use
sqlite3_busy_timeout() in front of each database access  call to manage a
better way concurrent work? 

 

Dusan Gibarac

 

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, January 23, 2008 9:45 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] May one software write to the SQLite database while a
other read the same SQLite database ?

 

Pierre8r <[EMAIL PROTECTED]> wrote:

> Hello,

> 

> One SQLite database on my PC.

> Two softwares.

> May one software write to the SQLite database while a other read the 

> same SQLite database  ?

> 

 

Your programs cannot be reading and writing at exactly the

same instant in time.  But both programs can have the database

open for reading and writing.  While one program is writing,

the other is blocked from reading.  But the write normally 

only takes a few dozen milliseconds.  Surely your reader can

wait that long.

 

The waiting is handled for you automatically if you set

 

sqlite3_busy_timeout()

 

--

D. Richard Hipp <[EMAIL PROTECTED]>

 

 


-

To unsubscribe, send email to [EMAIL PROTECTED]


-



--

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


End of sqlite-users Digest, Vol 2, Issue 24
***

[sqlite] Trace

[Mahalakshmi.m]

Hi ,

 

I am working in 3.3.6.

If I want to use the  "TRACE"  what steps I have to follow.

 

Thanks & Regards,

Mahalakshmi

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

Re: [sqlite] sqlite-users Digest, Vol 2, Issue 16

[Mahalakshmi.m]

Hi,
I am interested in joining the sqlite-users mailing list.
Kindly add my name.
Thanks & Regards,
Mahalakshmi

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: Friday, February 08, 2008 8:22 AM
To: sqlite-users@sqlite.org
Subject: sqlite-users Digest, Vol 2, Issue 16

Send sqlite-users mailing list submissions to
sqlite-users@sqlite.org

To subscribe or unsubscribe via the World Wide Web, visit
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
or, via email, send a message with subject or body 'help' to
[EMAIL PROTECTED]

You can reach the person managing the list at
[EMAIL PROTECTED]

When replying, please edit your Subject line so it is more specific
than "Re: Contents of sqlite-users digest..."


Today's Topics:

   1. Why attach databases? (Jason Tudor)
   2. Re: Why attach databases? (David Baird)
   3. Re: Why attach databases? (Samuel R. Neff)
   4. Re: Why attach databases? (Nicolas Williams)
   5. Re: Why attach databases? (Jason Tudor)
   6. Re: Why attach databases? (Samuel R. Neff)
   7. Re: Why attach databases? (Nicolas Williams)
   8. Re: Why attach databases? (Martin Pelletier)
   9. Re: How to select Strict Affinity or No Affinity modes?
  Attention: DRH (Lee Crain)
  10. Re: Version 3.2.2 (Mike McGonagle)


--

Message: 1
Date: Thu, 7 Feb 2008 14:21:06 -0800
From: "Jason Tudor" <[EMAIL PROTECTED]>
Subject: [sqlite] Why attach databases?
To: sqlite-users@sqlite.org
Message-ID:
<[EMAIL PROTECTED]>
Content-Type: text/plain; charset=ISO-8859-1

I have been playing around with attaching databases.  I'm not sure what the
point is.
Assume that I have two databases with the same schema, say db1 and db2, and
I attach them to one connection.

In order to get the count from a particular table, I could do the following:

SELECT COUNT id FROM main.table UNION
SELECT COUNT id FROM db2.table

I would then step through the result and add the two values to get a total
count.


If I have two separate connections, I could run two queries and add the
results.
Is there a speed difference?

In general, what is the benefit of attaching databases verses maintaining
multiple connections?

TIA
TUD


--

Message: 2
Date: Thu, 7 Feb 2008 15:33:36 -0700
From: "David Baird" <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Why attach databases?
To: "General Discussion of SQLite Database" 
Message-ID:
<[EMAIL PROTECTED]>
Content-Type: text/plain; charset=ISO-8859-1

On Feb 7, 2008 3:21 PM, Jason Tudor <[EMAIL PROTECTED]> wrote:
> In general, what is the benefit of attaching databases verses maintaining
> multiple connections?

Well, let's say that you don't have the same schema in both databases.

Attaching two databases allows you to do queries across multiple
tables and would require much more effort if you maintained separate
connections.

e.g.

SELECT People.name, Pets.name
   FROM
People AS People
other_database.Pets AS Pets
WHERE
People.id = Pets.owner_id;

It would be less pleasant to do that query via multiple connections.

-David


--

Message: 3
Date: Thu, 7 Feb 2008 17:34:54 -0500
From: "Samuel R. Neff" <[EMAIL PROTECTED]>
Subject: Re: [sqlite] Why attach databases?
To: "'General Discussion of SQLite Database'"

Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain;   charset="us-ascii"


It's particularly valuable when you want to run queries across databases.

INSERT INTO main.table
SELECT *
FROM newdata.table;

Also if it's possible for you to segment out your data to multiple databases
but normally only work with one of them, then you can increase performance
and concurrency for those times when you only need to work with one
database, but then have the ability to attach databases when you need all
the data together.

Most if not all database engines have similar functionality, but syntax and
functionality differs in each.  SQLite syntax is easiest to use of the ones
I've used (MSSQL, Access, Sybase ASE, Oracle).

HTH,

Sam 


---
We're Hiring! Seeking a passionate developer to join our team building Flex
based products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jason Tudor
Sent: Thursday, February 07, 2008 5:21 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Why attach databases?

I have been playing around with attaching databases.  I'm not sure what the
point is.
Assume that I have two databases with the same schema, say db1 and db2, and
I attach them to one connection.

In order to get the count from a particular table, I could do the following:

SELECT COUNT id FROM main.table UNION
SELECT