[sqlite] PENDING Lock / Sqlite .NET
Hi I have a question on how to enable PENDING LOCK before issuing an INSERT statement from the C# code. Do let me know if you can point me to some resources where I can find the answer or provide me the response. Regards Ajay Solanki +91 98192 54465 tel:%2B91%2098192%2054465 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Maintaining Master-Child relationships
Hi, I've two tables in my database, a master and a child with ID as a common key. I've created a table with one column to generate the sequence number.How can I insert related records into both the tables using same ID? Thanks Ajay THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, copying or use of this message and any attachment is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and permanently delete it from your computer and destroy any printout thereof. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] RE: Maintaining Master-Child relationships
Can anyone please look into my query, I've tables Master ( id integer primary key, field1 text) And Child (id integer, name text) My application receive values for field1 and name. I need to populate master and child with incoming values using the same id. A quick reply will be highly appreciated. Regards Ajay -Original Message- From: Arora, Ajay Sent: 28 March 2007 15:04 To: 'sqlite-users@sqlite.org' Subject: Maintaining Master-Child relationships Hi, I've two tables in my database, a master and a child with ID as a common key. I've created a table with one column to generate the sequence number.How can I insert related records into both the tables using same ID? Thanks Ajay THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, copying or use of this message and any attachment is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and permanently delete it from your computer and destroy any printout thereof. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Any way to do this faster?
I'm not sure if SQLite support this syntax, but try following statement, Delete from tableB b Where not exist ( select 'x' from tableA a where a.id = b.id ) -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: 26 March 2007 16:12 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Any way to do this faster? RB Smissaert wrote: Simplified I have the following situation: 2 tables, tableA and tableB both with an integer field, called ID, holding unique integer numbers in tableA and non-unique integer numbers in tableB. Both tables have an index on this field and for tableA this is an INTEGER PRIMARY KEY. Now I need to delete the rows in tableB where this number doesn't appear in the corresponding field in tableA. Currently I do this with this SQL: Delete from tableB where ID not in (select tableA.ID from tableA) When table tableB gets big (say some 10 rows) this will get a bit slow and I wonder if there is a better way to do this. RBS - To unsubscribe, send email to [EMAIL PROTECTED] - Your query is doing a complete table scan of tableA for each record in a table scan of tableB. SQLite version 3.3.13 Enter .help for instructions sqlite create table tableA(id integer primary key, b); sqlite create table tableB(id, c); sqlite create index b_id on tableB(id); sqlite explain query plan delete from tableB where id not in (select tableA.id from tableA); 0|0|TABLE tableB 0|0|TABLE tableA You can improve this greatly using correlated subquery that will use the primary key index on tableA to find any matching records. sqlite explain query plan delete from tableB where not exists (select id from t ableA where tableA.id = tableB.id); 0|0|TABLE tableB 0|0|TABLE tableA USING PRIMARY KEY Note that your index on tableB.id is not used and could be eliminated unless it serves another purpose. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, copying or use of this message and any attachment is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and permanently delete it from your computer and destroy any printout thereof. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Query Issue
Hi, I'm trying to run following query in sqllite, select a.extract_sequence,a.file_location,a.active,b.start_date,b.end_date,b.po rtfolio_code,c.status from extract_master a, extract_parameter b, ( select extract_sequence,status,user_id from extract_status e where datetime = (select max(datetime) from extract_status s where e.extract_sequence = s.extract_sequence) ) c where a.extract_sequence = b.extract_sequence and b.extract_sequence = c.extract_sequence and c.extract_sequence = a.extract_sequence And it gives me an error saying e.extract_sequence does not exist. Then I tried writing the same query using a different syntax, select a.extract_sequence,a.file_location,a.active,b.start_date,b.end_date,b.po rtfolio_code,c.status from extract_master a, extract_parameter b, ( select extract_sequence,status,user from extract_status e where (extract_sequence,datetime) in (select extract_sequence,max(datetime) from extract_status group by extract_sequence ) ) c where a.extract_sequence = b.extract_sequence and b.extract_sequence = c.extract_sequence and c.extract_sequence = a.extract_sequence But, unfortunately this does not work either and gives an error [syntax error near , ] in the above line. Please let me know if there is anyother way to achieve this in SQLite. Regards Ajay THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM DISCLOSURE. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, copying or use of this message and any attachment is strictly prohibited. If you have received this message in error, please notify us immediately by replying to the message and permanently delete it from your computer and destroy any printout thereof. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite]cross compiling with powerpc
configure: error: unable to find a compiler for building build tools Have you tried setting the path where the powerpc compiler in installed? Regards, Ajay. On 12/2/05, Julien LEFORT [EMAIL PROTECTED] wrote: Hi, I've been struggling for few hours trying to run the configure script the right way so I can cross compile sqlite3 for a powerpc target. I guess it is only a problem of options I pass into the configure script. here is the command I pass: CC=powerpc-linux-gcc CXX=powerpc-linux-g++ BUILD_CC=gcc ../configure --host=i686-linux --target=powerpc-linux --disable-shared --disable-tcl and the error I get: Version number set to 3002007 checking for i686-linux-gcc... (cached) powerpc-linux-gcc checking whether we are using the GNU C compiler... (cached) yes checking whether powerpc-linux-gcc accepts -g... (cached) yes checking for powerpc-linux-gcc option to accept ANSI C... (cached) none needed configure: error: unable to find a compiler for building build tools Anyone has any idea? Thanks Julien -- Prosperity is only an instrument to be used, not a deity to be worshipped.
[sqlite] sqlite3-3.2.1 execution error!
Hello, I tried compiling the source sqlite3-3.2.1 with the following commands on a i686 machine distro rehat linux 9 with the following commands, ./configure make make install And the above process returns successful message and all the lib* libraries are present in /usr/local/lib, i can also invoke sqlite3 by typing in the same at the prompt I have created an application which uses GTK+-1.2 and sqlite3 , the application compiles fine without any error but while trying to execute the binary the following error surface,, ./main(This is the binary) error while loading shared libraries: libsqlite3.so.0: cannot open shared object file: No such file or directory as mentioned above, i have cross-checked and these libraries are present in /usr/local/lib and all have executable permissions, The above library, libsqlite3.so.0 is linked with another library libsqlite3.so.0.8.6 But the above process works fine if i replicate them on a i386 machine with the same distro installed..
[sqlite] Problem Installing sqlite3 in redhat linux 9 i686 machine
Hello, I tried compiling the source sqlite3-3.2.1 with the following commands on a i686 machine distro rehat linux 9 with the following commands, ./configure make make install And the above process returns successful message and all the lib* libraries are present in /usr/local/lib, i can also invoke sqlite3 by typing in the same at the prompt I have created an application which uses GTK+-1.2 and sqlite3 , the application compiles fine without any error but while trying to execute the binary the following error surface,, ./main(This is the binary) error while loading shared libraries: libsqlite3.so.0: cannot open shared object file: No such file or directory as mentioned above, i have cross-checked and these libraries are present in /usr/local/lib and all have executable permissions, The above library, libsqlite3.so.0 is linked with another library libsqlite3.so.0.8.6 But the above process works fine if i replicate them on a i386 machine with the same distro installed..
[sqlite] INTEGER data type
Hello All, What is the maximum number that can be stored using INTEGER data type? My table uses INTEGER to store primary key, I store lot of records in table. I suspect that primary key will run out of limit. How can I use long instead of INTEGER? Regards, Ajay Sonawane
RE: [sqlite] Insert all rows from old table into new table but in sorted order
you misinterpreted my problem, I want to add all rows of old table into new table but with sorted order I don't want to fire another query (select * from newtable order by desc no ) to give sorted rows, I want to insert all rows in sorted order into new table. -Original Message- From: Paul Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 4:53 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Insert all rows from old table into new table but in sorted order I can insert all rows of existing table into new table having same columns using query : Insert into NEWTABLE select * from OLDTABLE But I want all rows of NEWTABLE sorted by field No, So I used query Insert into NEWTABLE select * from OLDTABLE order by no desc But it is not giving me sorted output as new table? Can you tell me where I am wrong ??? You can't do that. The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the right order , but then, when you do an unordered query on 'NEWTABLE', the results are returned in an undefined order - not necessarily in the order they were inserted into the table You should do the sorting when you read 'NEWTABLE' So, instead of Insert into NEWTABLE select * from OLDTABLE order by no desc select * from NEWTABLE do Insert into NEWTABLE select * from OLDTABLE select * from NEWTABLE order by no desc PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
RE: [sqlite] Insert all rows from old table into new table but in sorted order
Seems to be top of my head, Is there any simple and sweet solution ? -Original Message- From: Brad DerManouelian [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 6:36 PM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order The solution is to always specify an order when you want to return data in a particular order. SQL standard does not specify that rows come back in a particular order unless you specify. Order is never assumed as to enhance speed - especially for functions where order is irrelevant like totaling columns. If you're familiar with how hashes are stored in C++ or Perl (and others I'm not familiar with), it's the same situation. If you don't pass it through a sort function, the interpreter decides how to return the values in the most efficient way which may not be the way they were originally inserted. _brad -Original Message- From: Ajay [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 8:21 AM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order Yaa that's what I wanted to do , So what do you think what could be the solution for this ? -Original Message- From: Steve O'Hara [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 5:28 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order I might be wrong, but if you don't specify a sort column, you will get the rows out in PRIMARY KEY order, irrespective of how you loaded the data. Therefore, you will need to do something a little more interesting with your loading statement to perhaps exclude the primary key and let the insert re-generate them. Just a thought. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of Paul Smith Sent: 30 June 2005 12:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Insert all rows from old table into new table but in sorted order I can insert all rows of existing table into new table having same columns using query : Insert into NEWTABLE select * from OLDTABLE But I want all rows of NEWTABLE sorted by field No, So I used query Insert into NEWTABLE select * from OLDTABLE order by no desc But it is not giving me sorted output as new table? Can you tell me where I am wrong ??? You can't do that. The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the right order , but then, when you do an unordered query on 'NEWTABLE', the results are returned in an undefined order - not necessarily in the order they were inserted into the table You should do the sorting when you read 'NEWTABLE' So, instead of Insert into NEWTABLE select * from OLDTABLE order by no desc select * from NEWTABLE do Insert into NEWTABLE select * from OLDTABLE select * from NEWTABLE order by no desc PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
RE: [sqlite] Insert all rows from old table into new table but in sorted order
Ok, it means that I can't do so ! -Original Message- From: Brad DerManouelian [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 7:33 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order My apologies for being long-winded. Basically the answer is to not insert in a particular order and do your order by when you recall the data from NEWTABLE. Insert the data: Insert into NEWTABLE select * from OLDTABLE Then to get the data back in the order you want: select * from NEWTABLE order by no desc _brad -Original Message- From: Ajay [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 9:38 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order Seems to be top of my head, Is there any simple and sweet solution ? -Original Message- From: Brad DerManouelian [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 6:36 PM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order The solution is to always specify an order when you want to return data in a particular order. SQL standard does not specify that rows come back in a particular order unless you specify. Order is never assumed as to enhance speed - especially for functions where order is irrelevant like totaling columns. If you're familiar with how hashes are stored in C++ or Perl (and others I'm not familiar with), it's the same situation. If you don't pass it through a sort function, the interpreter decides how to return the values in the most efficient way which may not be the way they were originally inserted. _brad -Original Message- From: Ajay [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 8:21 AM To: sqlite-users@sqlite.org; [EMAIL PROTECTED] Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order Yaa that's what I wanted to do , So what do you think what could be the solution for this ? -Original Message- From: Steve O'Hara [mailto:[EMAIL PROTECTED] Sent: Thursday, June 30, 2005 5:28 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Insert all rows from old table into new table but in sorted order I might be wrong, but if you don't specify a sort column, you will get the rows out in PRIMARY KEY order, irrespective of how you loaded the data. Therefore, you will need to do something a little more interesting with your loading statement to perhaps exclude the primary key and let the insert re-generate them. Just a thought. Steve -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] rg]On Behalf Of Paul Smith Sent: 30 June 2005 12:23 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Insert all rows from old table into new table but in sorted order I can insert all rows of existing table into new table having same columns using query : Insert into NEWTABLE select * from OLDTABLE But I want all rows of NEWTABLE sorted by field No, So I used query Insert into NEWTABLE select * from OLDTABLE order by no desc But it is not giving me sorted output as new table? Can you tell me where I am wrong ??? You can't do that. The 'Insert' may (I'm not sure..) insert the data into 'NEWTABLE' in the right order , but then, when you do an unordered query on 'NEWTABLE', the results are returned in an undefined order - not necessarily in the order they were inserted into the table You should do the sorting when you read 'NEWTABLE' So, instead of Insert into NEWTABLE select * from OLDTABLE order by no desc select * from NEWTABLE do Insert into NEWTABLE select * from OLDTABLE select * from NEWTABLE order by no desc PaulVPOP3 - Internet Email Server/Gateway [EMAIL PROTECTED] http://www.pscs.co.uk/
[sqlite] Error : Library routine called out of sequence
Hello there, I am facing one weird problem using SQLite. Many times I get an error Library routine called out of sequence while executing query Create table SOURCEFEED (SOURCEFEED,STATUS) values ( ABC,0) , I'm not getting the reason behind this. Why am I getting this error? My application is a multithread application and executes query from a worker thread. Let me know the possible solutions. Regards Ajay Sonawane
RE: [sqlite] Error : Library routine called out of sequence
Yaa You are right; I solved the problem by using some of synchronization objects for thread access. Thanks for giving me direction. -Original Message- From: Will Leshner [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 28, 2005 7:07 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Error : Library routine called out of sequence On Jun 28, 2005, at 5:08 AM, Ajay wrote: I am trying to insert values into table and executing that query from thread. There are 2 more threads that accesses same table. Do all the threads use the same sqlite handle? I believe that can cause problems.
[sqlite] UNIQUE Constraint but case sensitive
Hi, I need to create a unique constraint on a column of type varchar, but it is not case sensitive by default. Does any one know how to make a unique constraint case sensitive? Thanks and best regards, Ajay Sonawane
[sqlite] UNICODE Support
Hello there, Does SQLite support UNICODE? Can I store some Arabic or Chinese text in database? If it does not support UNICODE, Is there any workaround for that? Regards, Ajay Sonawane
RE: [sqlite] UNICODE Support
But what about the SQLite Function's parameters whose data type is LPSTR ? Let me know the details to support wide char ? Regards, Ajay Sonawane -Original Message- From: Martin Engelschalk [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 08, 2005 6:48 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] UNICODE Support Hi, See http://www.sqlite.org/pragma.html, search for 'PRAGMA encoding' /Martin Ajay schrieb: Hello there, Does SQLite support UNICODE? Can I store some Arabic or Chinese text in database? If it does not support UNICODE, Is there any workaround for that? Regards, Ajay Sonawane
[sqlite] ON_DELETE_CASCADE constraint is not working
Hello there, I have two tables and used constraint ON_DELETE_CASCADE in one of the table to delete records from second table if records from first table are deleted which foreign key points to first table. I'm using SQLiteExplorer to manipulate tables. But it is not working properly. I found all records of first table as it is though record of foreign key is deleted from second table. Let me know solution. Regards, Ajay Sonawane
RE: [sqlite] ON_DELETE_CASCADE constraint is not working
Hello there, Will you please tell me when SQLite will be supporting FOREIGN KEYS and ON_DELETE_CASCADE? Where can I get more information about version and what's new feature? As well as Can someone guide me about how to write and fire trigger that could do work of ON_DELETE_CASCADE Regards, Ajay Sonawane Hello there, I have two tables and used constraint ON_DELETE_CASCADE in one of the table to delete records from second table if records from first table are deleted which foreign key points to first table. I'm using SQLiteExplorer to manipulate tables. But it is not working properly. I found all records of first table as it is though record of foreign key is deleted from second table. Let me know solution. Unfortunately, foreign keys are not supported yet. You can add triggers to achieve the same functionality, although it is not as conveniant. http://www.sqlite.org/omitted.html __ Discover Yahoo! Stay in touch with email, IM, photo sharing and more. Check it out! http://discover.yahoo.com/stayintouch.html