[sqlite] how to use the sqlite under "strict memory manage" OS ?
Hi, everyone: When using the Sqlite (version for Linux 2.8.11) under Symbian I found it always cannot free the memory allocated itself and I have to do it myself. For instance, we must use the sqlitefree() after each sqliteMalloc(), otherwise my Symbian UI will report to me an error :unknown software error! But there is so many situations in it that I find it's not a easy work. Does somebody have the sqlite which version is for Symbian or just the version for the OS which is strict with the memory manage or some good advice to me? Best regards Steven
[sqlite] create table question
I am trying to create a table after creating a select statement which
selects records from an existing table. Is this possible? I get a
SQLITE_LOCKED / "database table is locked" error when I attempt this.
In psuedo code the logic is as follows:
select_stmt = db.execute("SELECT * FROM people")
# use some, but not all of the rows in select_stmt
create_stmt = db.execute("CREATE TABLE other (a,b)") # error: database
table is locked
Why does this happen?
Anyway around this?
-Charlie
Re: Re: [sqlite] How do I efficiently copy tables to another db within dll (sqlite3+)
Thanks much - one more question though... I'm attempting to copy data from an in memory database to another db (file or otherwise). Is it possible to attach an existing in-memory database to another existing db connection? (attach database :MEMORY: would just seem to create another in mem db). I'm trying to do this in hopes of being able to use the destination dbs existing connection - otherwise i have to do a lock/unlock close/open on the destination db - which i'd like to avoid. Thanks Get your own "800" number Voicemail, fax, email, and a lot more http://www.ureach.com/reg/tag On Wed, 6 Apr 2005, Jay ([EMAIL PROTECTED]) wrote: > > -- de f <[EMAIL PROTECTED]> wrote: > > Thanks Jay. > > > > Great! This could work... > > > > I'm wondering, though if you know whether the limitation below > > (from sqlite documentation) could be safely circumvented if > > attaching the source db with an alias name. It seems to work > > when i try it but I'm concerned that there might be other > > unknown issues. > > > > "You cannot create a new table with the same name as a table in > > an attached database, but you can attach a database which > > contains tables whose names are duplicates of tables in the main > > database. It is also permissible to attach the same database > > file multiple times." > > I wrote a c++ replicator for my own database. I did this: > > open db1; > > SELECT sql FROM sqlite_master WHERE type = 'table' AND name = > 'yourtable'; > > open db2; > > execute sql from step 2 against db2 to create a duplicate blank table; > > close db2; > > attatch db2 to db1; > > insert into db2.yourtable > select * from db1.yourtable; > > close all; > > > > - > > You a Gamer? If you're near Kansas City, ask me about the Recruits and Conquest conventions. > - > > The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call > http://www.lulu.com/content/77264 > > > > __ > Yahoo! Messenger > Show us what our next emoticon should look like. Join the fun. > http://www.advision.webevents.yahoo.com/emoticontest > >
RE: [sqlite] what is the difference between TEXT and BLOB data type?
It is a limitation of sqliteExplorer. > -Original Message- > From: jack wu [mailto:[EMAIL PROTECTED] > Sent: Thursday, April 07, 2005 7:05 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] what is the difference between TEXT and > BLOB data type? > > > other than encoded and not encoded. > > i did not find the length limitations in the documentation > but seems like TEXT can only hold less than 250 chars. is it > true or is it SqliteExplorer that only shows 250 chars. Thanks. > > > >
Re: [sqlite] Column Names (revisited)
I think from recent discussions, it became clear that supporting these extra pragmas was becoming a big headache. I don't know that tsupport has been deliberately dropped, but it may have fallen by the wayside. The reasons for this are that the behavior now matches all the other RDBMSes in widespread use (mysql, postgresql, oracle) and previous behavior can be gotten by using aliases. Personally, I like that. I don't want to use the pragmas, I don't want Dr Hipp spending time maintaining them instead of more useful work. For what you're doing, it appears that you don't want to change the sql that you already have, probably because there's a lot of it and very spread out. So you may have differnet priorities. If however, you do decide to change it, keep in mind that you can do the following to fix your problem: for query 1: select a.col1 as "a.col1", a.col2 as "a.col2", b.col1 as "b.col1", b.col2 as "b.col2" from a_table a inner join b_table b on a.col1 = b.col1 Anyway, just some thoughts John Drew, Stephen wrote: Hello, Has anyone else had any problems getting the column names pragmas working in SQLite 3.x ? I can't seem to get them to work, and can't find anything relating to the latest version on the website. Thanks, Steve -Original Message- From: Drew, Stephen Sent: 06 April 2005 11:57 To: sqlite-users@sqlite.org Subject: [sqlite] Column Names (revisited) Hello, I've been looking into upgrading to 3.2.1 from 2.8.15 and am still running into problems with returned column names. I've written a piece of code to test four scenarios with 2.8.15 and 3.2.1 and these are the results: Two tables: CREATE TABLE A_TABLE (col1 INTEGER, col2 INTEGER) CREATE TABLE B_TABLE (col1 INTEGER, col2 INTEGER) Two queries: 1. SELECT A.*, B.* FROM A_TABLE, B_TABLE WHERE A.col1 = B.col1 2. SELECT * FROM A_TABLE The column names are returned as follows (with the relevant column names pragmas). --- TEST PRAGMAS2.8.15 3.2.1 --- 1 (short=0, full=0) A.col1, B.col1, ... col1, col1, ... 2 (short=0, full=0) col1, col2, ... col1, col2, ... 1 (short=1, full=0) col1, col1, ... col1, col1, ... 2 (short=1, full=0) col1, col2, ... col1, col2, ... 1 (short=0, full=1) A_TABLE.col1, B_TABLE.col1, ... col1, col1, ... 2 (short=0, full=1) A_TABLE.col1, A_TABLE.col2, ... col1, col2, ... --- In other words, the pragmas seem to have NO effect when the same piece of code compiled against 2.8.15 is compiled against 3.2.1. I ALWAYS get returned just the column names. All I need to do is guarantee that the column names will turn out like the first two rows of the table !! Can anyone offer any help? I'm using standard sqlite_step functionality, which is working as expected in 2.8.15. Regards, Steve
Re: [sqlite] what is the difference between TEXT and BLOB data type?
jack wu said: > i did not find the length limitations in the > documentation but seems like TEXT can only hold less > than 250 chars. is it true or is it SqliteExplorer > that only shows 250 chars. Thanks. That's not a limitation of SQLite. I'm regularly storing strings larger than that in a TEXT field. Remember that the typing is only a suggestion in SQLite, and doesn't actually restrict the data stored. Clay -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: [sqlite] how to get the INTEGER PRIMARY KEY for the row just inserted?
Jay wrote: Oh. Nifty. I would therefore be safe doing this: begin immediate; insert into master_table() values(); insert into related_table( master_id, data ) select last_insert_rowid(), 'stuff'; commit; But it would fail if I had multiple related tables? begin immediate; insert into master_table() values(); insert into related_table( master_id, data ) select last_insert_rowid(), 'stuff'; insert into related_table2( master_id, data ) select last_insert_rowid(), 'wrong id inserted here'; commit; The last_insert_rowid() would be the id for the last related table not the master table. Yes, that's how it would work. This function is normally intended to be used from C code using the API function, or in SQL triggers. If you need to use the value to link multiple tables on an insert, you need to save it somewhere safe. You could use a dedicated table with a single row like this. create table last_inserted_master (master_id integer); insert into last_inserted_master values(NULL); insert into master_table values(...); update last_inserted_master set master_id = select last_insert_rowid(); insert into related_table1(master_id, data) values((select master_id from last_inserted_master), ...); insert into related_table2(master_id, other_data) values((select master_id from last_inserted_master), ...); The last_insert_rowid value is not updated by inserts that occur inside a trigger. Well, actually it is, and those values are available inside the trigger, but the initial value (before the trigger executed) is restored when the trigger ends. When a trigger starts it can read the value of the last_insert_rowid from its calling context (usually the mainline code, but triggers can be nested as well). So, this update code could all be moved into a trigger that fires immediately after the insert. This can be useful for audit trail tables etc., where the information to be stored in the related table can be determined from existing tables and the new row. HTH Dennis Cote
Re: [sqlite] Column Names (revisited)
Drew, Stephen wrote: Hello, Has anyone else had any problems getting the column names pragmas working in SQLite 3.x ? My experience has been that they don't work as advertised in the documentation in recent versions. However, I don't normally use them, so it hasn't been a problem for me. I suspect some changes may be in the works based on some of the past mailing list chatter, but that's pure speculation. Dennis Cote
[sqlite] what is the difference between TEXT and BLOB data type?
other than encoded and not encoded. i did not find the length limitations in the documentation but seems like TEXT can only hold less than 250 chars. is it true or is it SqliteExplorer that only shows 250 chars. Thanks.
Re: [sqlite] Re: using triggers to lock records
On Apr 7, 2005 7:44 AM, Kervin L. Pierre <[EMAIL PROTECTED]> wrote: > I know your pain :) . Finer grain locking would be > great. Yes. We may simply be trying to use SQLite for something it just wasn't designed for. On the other hand, if you are using SQLite as your single-user database, it would be just so cool if you could "drop" those same databases on a server and turn them into multi-user databases. > The only problem I see is that you'd have to call > that trigger on every write. To at least check for > a lock. Would that be an acceptable performance > hit for you? Yes, performance is my big concern. But I'm wondering if triggers aren't fairly optimized. I guess I'm going to find out :) > Another solution, if it suits your environment would > be to create a 'record' object or struct and give > each object a mutex. That is also a very interesting idea. Thanks.
[sqlite] malformed sqlite 2.1 DB
Hi there, 1. i get the following error; c:\>sqlite.exe 1.db Unable to open database "1.db": database disk image is malformed so i can't execute pragma integrity_check. 2. this is the corrupted file: 00: 2A 2A 20 54 68 69 73 20 ? 66 69 6C 65 20 63 6F 6E ** This file con 10: 74 61 69 6E 73 20 61 6E ? 20 53 51 4C 69 74 65 20 tains an SQLite 20: 32 2E 31 20 64 61 74 61 ? 62 61 73 65 20 2A 2A 00 2.1 database ** 30: 28 75 E3 DA 00 00 00 00 ? 00 00 0C 00 00 00 04 00 (uÒ? ? ? 40: 00 00 D0 07 00 00 02 00 ? 00 00 00 00 00 00 00 00?. ? this is my normal db: 00: 2A 2A 20 54 68 69 73 20 ? 66 69 6C 65 20 63 6F 6E ** This file con 10: 74 61 69 6E 73 20 61 6E ? 20 53 51 4C 69 74 65 20 tains an SQLite 20: 32 2E 31 20 64 61 74 61 ? 62 61 73 65 20 2A 2A 00 2.1 database ** 30: 28 75 E3 DA 00 00 00 00 ? 00 00 00 00 F6 03 00 00 (uÒ?÷? 40: 04 00 00 00 D0 07 00 00 ? 02 00 00 00 00 00 00 00 ? ?. ? here after 0x34 it seemed to me that there is a 2 bytes shift left so i added 2 null bytes, but it didn't work. c:\>sqlite.exe 1.db Unable to open database 1.db now what? any suggestions? Mustafa TUFAN
Re: [sqlite] Re: using triggers to lock records
Will Leshner wrote: On Apr 6, 2005 10:43 AM, Will Leshner <[EMAIL PROTECTED]> wrote: After thinking about this a bit more, I realized that writing a trigger that didn't trigger for the locker is pretty simple. So I'm thinking that triggers would work in this situation, but I'm wondering if anybody has ever done anything like this before. I know your pain :) . Finer grain locking would be great. The only problem I see is that you'd have to call that trigger on every write. To at least check for a lock. Would that be an acceptable performance hit for you? Another solution, if it suits your environment would be to create a 'record' object or struct and give each object a mutex. If you maintain that that object is the only way to modify a row and that your application keeps track of all objects ( as to not create multiples on the same row ) then that would effectively lock the record. Should be faster to since you don't have to hit the database to find out the row state. Good luck, Kervin
Re: [sqlite] Re: using triggers to lock records
> After thinking about this a bit more, I realized that writing a > trigger that didn't trigger for the locker is pretty simple. So I'm > thinking that triggers would work in this situation, but I'm > wondering if anybody has ever done anything like this before. > That really seems like a kludgey hack. Do you really need it? In my application I ensure everything that needs locks finishes as quickly as possible and accept the locking mechanism already implemented. I understand the airline reservations operating system is written so if your program doesn't complete in 1/10th second it's assumed it's broken and it automatically aborts it! If you need something that sophisticated is Sqlite the appropriate choice for a database? - You a Gamer? If you're near Kansas City, ask me about the Recruits and Conquest conventions. - The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264 __ Do you Yahoo!? Yahoo! Personals - Better first dates. More second dates. http://personals.yahoo.com
RE: [sqlite] Column Names (revisited)
Hello, Has anyone else had any problems getting the column names pragmas working in SQLite 3.x ? I can't seem to get them to work, and can't find anything relating to the latest version on the website. Thanks, Steve -Original Message- From: Drew, Stephen Sent: 06 April 2005 11:57 To: sqlite-users@sqlite.org Subject: [sqlite] Column Names (revisited) Hello, I've been looking into upgrading to 3.2.1 from 2.8.15 and am still running into problems with returned column names. I've written a piece of code to test four scenarios with 2.8.15 and 3.2.1 and these are the results: Two tables: CREATE TABLE A_TABLE (col1 INTEGER, col2 INTEGER) CREATE TABLE B_TABLE (col1 INTEGER, col2 INTEGER) Two queries: 1. SELECT A.*, B.* FROM A_TABLE, B_TABLE WHERE A.col1 = B.col1 2. SELECT * FROM A_TABLE The column names are returned as follows (with the relevant column names pragmas). --- TEST PRAGMAS2.8.15 3.2.1 --- 1 (short=0, full=0) A.col1, B.col1, ... col1, col1, ... 2 (short=0, full=0) col1, col2, ... col1, col2, ... 1 (short=1, full=0) col1, col1, ... col1, col1, ... 2 (short=1, full=0) col1, col2, ... col1, col2, ... 1 (short=0, full=1) A_TABLE.col1, B_TABLE.col1, ... col1, col1, ... 2 (short=0, full=1) A_TABLE.col1, A_TABLE.col2, ... col1, col2, ... --- In other words, the pragmas seem to have NO effect when the same piece of code compiled against 2.8.15 is compiled against 3.2.1. I ALWAYS get returned just the column names. All I need to do is guarantee that the column names will turn out like the first two rows of the table !! Can anyone offer any help? I'm using standard sqlite_step functionality, which is working as expected in 2.8.15. Regards, Steve
