[sqlite] Comparing a field between two databases?
Hello I have two SQLite files, each with the exact same structure. I need to check the value in a field in both tables, and check if it's identical. Is there a way to use sqlite3.exe to open two databases, and perform a SELECT to compare the value of a field in the databases? Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparing a field between two databases?
Hi, attach is probably what you want: http://www.sqlite.org/lang_attach.html Alex. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [UnicodeEncodeError] Don't know what else to try
On Fri, 14 Nov 2008 10:59:23 +0100, Michael Schlenker [EMAIL PROTECTED] wrote: A tool like chardet helps: http://chardet.feedparser.org/ Thanks for the link. Turns out some data were Unicode, while others were 1252. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3Explorer Sqlite Report Designer
You can download the program here http://www.realpointscorp.com/MGFAQUERY1.rar I use this program myself in my applications. I have some ideas of how to increase it's functions but I do not want to waste my time if no-one else is interested as I use my application software to provide the extra functions. I am willing to work with anyone who is willing to try this software and give me feedback. Have a super one! Merv Rajesh Nair wrote: Hi Merv Thanks for your sharing If you can share it through this dissussion forum then that will be counted as a great work from you. Because those who are using Sqlite with windows and visualstudio 6 will face the problem of reporting. They have to depend up on Crystall Reports or some other tools which will require ODBC/ ADO. I have tested OOFile but was not worth enough. So please share what you have with all of us. I am tired of searching such a thing. Rajesh Nair - Original Message - From: Merv [EMAIL PROTECTED] To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Saturday, November 15, 2008 9:03 PM Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer I have a program for Windows that may help you. It is not open-source but it is free to use and distribute. It has a simple report function that I am willing to modify based on input from users. We can continue to communicate this way or you can contact me directly at the above address or call 705-646-1076. Have a super one! Merv Rajesh Nair wrote: Thank You.. Java report builders are available on sourceforge.net and i have gone through it long before. What I am looking is the one which any one can use with VisualStudio 6 or ealier products. It should be handy. Not a complecated one. Simple like just design and port the report file and then just view and print. Do you have any link which could help all of us. - Original Message - From: Timothy A. Sawyer [EMAIL PROTECTED] To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Saturday, November 15, 2008 10:24 AM Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer If you want something that is Windows based like you describe, why not use Java to build a Swing GUI? Delphi and Pascal are a little antiquated so if you need to revise you may have a problem finding a resource. Also consider the platform independence if that is a concern Timothy A. Sawyer, CISSP Managing Director MBD Solutions Phone: (603) 546-7132 Web: http://www.mybowlingdiary.com Email: [EMAIL PROTECTED] -Original Message- From: Rajesh Nair [EMAIL PROTECTED] Date: Sat, 15 Nov 2008 09:23:46 To: General Discussion of SQLite Databasesqlite-users@sqlite.org Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer Thanks So I can't use it... Ok ... But I got some source code of FastReport ( some ealier version ) from the internet and it is in Dephi/Pascal. I don't know both of the tools. Can you use the same code to build a dll which can be used with other windows programing languages? ( So that no one can raise their finger aganist me or YOU ). I don't want a very efficient-high-end report designer like FastReport. My clients require very simple reports, which does not contain much caculations etc If you can help me please. It won't be just for me. Those who are using SqLite with windows will be happy with a simple report deisgner and viewer. Also my company is not willing to puchase any product for just reporting, since our programs are supplied freely along with some of our products. So an extra cost is not feasible. A SIMPLE DISIGNER/VIEWER FOR WINDOWS USERS - Original Message - From: Cariotoglou Mike [EMAIL PROTECTED] To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Friday, November 14, 2008 7:14 PM Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer I am the author of sqlite3Explorer. Sorry I did not answer you previous post, reason is I did not motice it... :( I don't think what you want is feasible. the reason is this: the Dll expects a dataset which sqlite3Explorer builds internally, and the report engine takes it from there (Design, run, export etc). the dll itself does not contain data retrieval code, and relies on the host for this. this is ok, but the catch is here: the dataset format that the dll understands is a proprietary format developed by and only used by the company I work for. the dll engine is one the prime design/execute engines for our commercial projects. thus I cannot open-source it. and, since the dataset format in question cannot be produced without having access to the proprietary libraries we use here, what you want cannot be done. sorry... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rajesh Nair Sent: Friday, November 14,
[sqlite] Database Locked
I am writing a php script to back up mysql to sqlite(3) via PDO My process is : -- Create SQLite database connection Loop several tables Describe mysql table Drop sqlite table if exists, Create matching sqlite table query mysql table for recordset. insert recordset into sqlite table End Loop Kill SQLite connection This process works fine if the database is a new file otherwise I get an error : SQLSTATE[HY000]: General error: 6 database table is locked; What am I missing here - perhaps the tables are left open ? *Ben Marchbanks* www.magazooms.com http://www.magazooms.com/ Signature Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Phone: (864) 284.9918 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Escaped values throws error
Abridged sample of the insert statement that produces an error when trying to insert escaped text. INSERT INTO pageWords (word) VALUES ( \nitch\ ); SQLSTATE[HY000]: General error: 1 near nitch: syntax error' Using PHP Version 5.2.4 PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2 2007/03/23 14:30:00 wez Exp $ SQLite 3.4.2 -- *Ben Marchbanks* www.magazooms.com http://www.magazooms.com/ Signature Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Phone: (864) 284.9918 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escaped values throws error
On 11/16/08, Ben Marchbanks [EMAIL PROTECTED] wrote: Abridged sample of the insert statement that produces an error when trying to insert escaped text. INSERT INTO pageWords (word) VALUES ( \nitch\ ); SQL text strings are delimited by single quotes. If your really want to insert nitch (including the double quotes), try ... VALUES ('nitch'); Alternatively, check out the ESCAPE clause in SQLite docs. SQLSTATE[HY000]: General error: 1 near nitch: syntax error' Using PHP Version 5.2.4 PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2 2007/03/23 14:30:00 wez Exp $ SQLite 3.4.2 -- *Ben Marchbanks* www.magazooms.com http://www.magazooms.com/ Signature Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Phone: (864) 284.9918 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escaped values throws error
On Sun, Nov 16, 2008 at 01:19:27PM -0500, Ben Marchbanks scratched on the wall: Abridged sample of the insert statement that produces an error when trying to insert escaped text. INSERT INTO pageWords (word) VALUES ( \nitch\ ); It is throwing an error because this is not valid SQL. In SQL, string literals use single quotes, so you want: INSERT INTO pageWords (word) VALUES (' nitch '); If you're not familiar with SQL syntax, you might want to read this: http://www.sqlite.org/lang_expr.html In specific, on string literals: A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trigger commands don't update new record?
I have an events table where SET events for a given device_id and event_code are followed by CLR events. CREATE TABLE device_event ( event_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, device_id INTEGER NOT NULL REFERENCES device, event_date INTEGER NOT NULL, event_code INTEGER NOT NULL REFERENCES event_code, event_state INTEGER NOT NULL REFERENCES event_state, event_text TEXT, exported INTEGER DEFAULT 0, created_date INTEGER NOT NULL, event_correlation_id INTEGER ); I want to set event_correlation_id when a CLR is inserted, so that the SET and CLR events reference each other. CREATE TRIGGER update_event_correlation_id AFTER INSERT ON device_event WHEN new.event_state = 1 BEGIN UPDATE device_event SET event_correlation_id = ( SELECT event_id FROM device_event WHERE device_id = new.device_id AND event_code = new.event_code AND event_state = 2 ORDER BY event_date DESC LIMIT 1 ) WHERE event_id = new.event_id; UPDATE device_event SET event_correlation_id = new.event_id WHERE event_id = new.event_correlation_id; END; My first version of this trigger failed entirely when I omitted the AFTER keyword. I wish the default *trigger-time* were documented. The second UPDATE in the trigger still fails, though; it seems that the newrecord does not get modified by the first UPDATE. If this is true, perhaps it could also be documented. I ended up having to run the SELECT a second time to make the second UPDATE work: CREATE TRIGGER update_event_correlation_id AFTER INSERT ON device_event WHEN new.event_state = 1 BEGIN UPDATE device_event SET event_correlation_id = ( SELECT event_id FROM device_event WHERE device_id = new.device_id AND event_code = new.event_code AND event_state = 2 ORDER BY event_date DESC LIMIT 1 ) WHERE event_id = new.event_id; UPDATE device_event SET event_correlation_id = new.event_id WHERE event_id = ( SELECT event_id FROM device_event WHERE device_id = new.device_id AND event_code = new.event_code AND event_state = 2 ORDER BY event_date DESC LIMIT 1 ); END; I am afraid this will get too slow, and can do without the second update (setting event_correlation_id for the earlier SET event), but I would like to know if there's a more efficient way to do both. Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escaped values throws error
An SQL literal uses single quotes. Ben Marchbanks wrote: Abridged sample of the insert statement that produces an error when trying to insert escaped text. INSERT INTO pageWords (word) VALUES ( \nitch\ ); SQLSTATE[HY000]: General error: 1 near nitch: syntax error' Using PHP Version 5.2.4 PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2 2007/03/23 14:30:00 wez Exp $ SQLite 3.4.2 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escaped values throws error
Thanks - I did find a solution since I was not successful using sqlite_escape_string. I think that the SQLite implementation in PHP via PDO is the culprit. I wound up using double escape - str_replace(\,\\,$value); *Ben Marchbanks* www.magazooms.com http://www.magazooms.com/ Signature Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Phone: (864) 284.9918 P Kishor wrote: On 11/16/08, Ben Marchbanks [EMAIL PROTECTED] wrote: Abridged sample of the insert statement that produces an error when trying to insert escaped text. INSERT INTO pageWords (word) VALUES ( \nitch\ ); SQL text strings are delimited by single quotes. If your really want to insert nitch (including the double quotes), try ... VALUES ('nitch'); Alternatively, check out the ESCAPE clause in SQLite docs. SQLSTATE[HY000]: General error: 1 near nitch: syntax error' Using PHP Version 5.2.4 PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2 2007/03/23 14:30:00 wez Exp $ SQLite 3.4.2 -- *Ben Marchbanks* www.magazooms.com http://www.magazooms.com/ Signature Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Phone: (864) 284.9918 ___ 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
Re: [sqlite] Escaped values throws error
Hmmm - thanks for straightening me out guys - somehow I thought that SQLite used double quotes *Ben Marchbanks* www.magazooms.com http://www.magazooms.com/ Signature Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Phone: (864) 284.9918 John Stanton wrote: An SQL literal uses single quotes. Ben Marchbanks wrote: Abridged sample of the insert statement that produces an error when trying to insert escaped text. INSERT INTO pageWords (word) VALUES ( \nitch\ ); SQLSTATE[HY000]: General error: 1 near nitch: syntax error' Using PHP Version 5.2.4 PDO SQLite - version 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.2 2007/03/23 14:30:00 wez Exp $ SQLite 3.4.2 ___ 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
Re: [sqlite] Escaped values throws error
Now I remember why I wasn't using single quotes in SQLite statements - because escaped single quoted values like 'Billy\'s bike' cause it to fail. Again I think its related to PHP-PDO SQLite implementation. Have you considered using prepared statements and bound parameters? That way, you never have to worry about character escaping, or SQL injection problems. ~Eric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escaped values throws error
This is my first foree into PDO-SQLite and I saw samples using binding, I am using prepare() though without binding. So you think that had I used binding my escape issue would not be an issue ? Correct. Though I've not used PDO-SQLite (I use the C interface), I would think that using bound parameters would eliminate your string escaping issues. I use bound parameters almost exclusively: You never have to worry about escaping or sanitizing your strings-- just let the Db engine do the work for you. A related classic: http://xkcd.com/327/ ~Eric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite3Explorer Sqlite Report Designer
here is an idea : I might be able to modify the dll to accept datasets in a different, standard format, like XML. this would imply that your product would need to retrieve data in whatever format it needs, transform it to my format, pass it to the dll which will do the design and reporting. it will not as fast as it is now, but it is definately a solution. you would not have the source of the dll, but you could use it freely, no IR issues. for this to work , the following assumptions must prove correct: 1. I originally designed the DLL with portability in mind, so it *coul* be transformed as I described. since that time, otuehr developers have improved it. I need to check that they have not introduced dependencies and design decisions that would nullify its portability. 2. that you (or somebody else) actually would benefit from this approach. f.e do you *like* the report designer (as seen and used from sqlite3Explorer), or is it just something you came across and thought to give a try? I only point this out, because it will take me at least 10-20 hours to do this conversion, and it would be a big waste of time (mine) to do this just as an intellectual exercise. I am not asking for financial compensation here, just to know that I am actually doing something useful... PS another format that would work, besides XML, and that I already have a lot of code to support would be the ADO recordset. is this an option for you ? what language are you planning to use in order to host the DLL, and what database ? From: [EMAIL PROTECTED] on behalf of Rajesh Nair Sent: Sat 15/11/2008 5:53 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer Thanks So I can't use it... Ok ... But I got some source code of FastReport ( some ealier version ) from the internet and it is in Dephi/Pascal. I don't know both of the tools. Can you use the same code to build a dll which can be used with other windows programing languages? ( So that no one can raise their finger aganist me or YOU ). I don't want a very efficient-high-end report designer like FastReport. My clients require very simple reports, which does not contain much caculations etc If you can help me please. It won't be just for me. Those who are using SqLite with windows will be happy with a simple report deisgner and viewer. Also my company is not willing to puchase any product for just reporting, since our programs are supplied freely along with some of our products. So an extra cost is not feasible. A SIMPLE DISIGNER/VIEWER FOR WINDOWS USERS - Original Message - From: Cariotoglou Mike [EMAIL PROTECTED] To: General Discussion of SQLite Database sqlite-users@sqlite.org Sent: Friday, November 14, 2008 7:14 PM Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer I am the author of sqlite3Explorer. Sorry I did not answer you previous post, reason is I did not motice it... :( I don't think what you want is feasible. the reason is this: the Dll expects a dataset which sqlite3Explorer builds internally, and the report engine takes it from there (Design, run, export etc). the dll itself does not contain data retrieval code, and relies on the host for this. this is ok, but the catch is here: the dataset format that the dll understands is a proprietary format developed by and only used by the company I work for. the dll engine is one the prime design/execute engines for our commercial projects. thus I cannot open-source it. and, since the dataset format in question cannot be produced without having access to the proprietary libraries we use here, what you want cannot be done. sorry... -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rajesh Nair Sent: Friday, November 14, 2008 2:12 PM To: General Discussion of SQLite Database Subject: [sqlite] Sqlite3Explorer Sqlite Report Designer Hi all How can I use the mkFrxEngine.DLL supplied along with the Sqlite3Explorer. The dll is a report designer and viewer. Please help me to use that dll if any one knows. I asked the same before 10-14 days ago, but didn't get any reply. If even the author of the same is reading this please help me. I want to use it as a report viewer for my sqlite3 database. -- Regards Rajesh Nair ___ 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-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
Re: [sqlite] sqlite3_bind_text problem
-- Original Message -- To: (sqlite-users@sqlite.org) From: Igor Tandetnik ([EMAIL PROTECTED]) Subject: Re: [sqlite] sqlite3_bind_text problem Date: 14/11/2008 11:47:54p And it return SQLITE_OK, however the value in the DB is NULL, or an empty string. I don't see any obvious problems in the code you've shown. The problem must lie in the code you haven't shown. Can you post a small sample that reproduces the problem? Alright I tried it on the Mac/GCC and the same error persists... so it looks like it's my fault. So as suggested I made a minimal example of my code that exhibits the problem... it's about 2 pages of code... mostly cut and pasted straight from a large app, I did have to rewrite a few functions not to use internal classes, but the bug happens in the code to. http://www.memecode.com/temp/Sqlite_Test.zip [921kb] This is a vc6 project that compiles and produces 'Database.sqlite' in the current folder. I've zipped up the example database from my testing, but you should delete that before running the code. Thanks for any time to take to look at this, I do appreciate it. -- Matthew Allen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Escaped values throws error
Ben Marchbanks [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Now I remember why I wasn't using single quotes in SQLite statements - because escaped single quoted values like 'Billy\'s bike' cause it to fail. You probably want 'Billy''s bike'. Backslashes don't have any special meaning in SQL. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_text problem
Matthew Allen [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] http://www.memecode.com/temp/Sqlite_Test.zip [921kb] #define SERIALIZE_STR(Var, Col) \ if (Write) \ { \ (1) if (Var) \ (2) if (!Check(sqlite3_bind_text(Stmt, Col+1, (const char*)Var, -1, SQLITE_STATIC))) \ return false; \ (3) else if (!Check(sqlite3_bind_null(Stmt, Col+1))) \ return false; \ } \ else \ Var = (char*)sqlite3_column_text(Stmt, Col); Despite what the indentation might suggest, the else clause at (3) corresponds to the if statement at (2), not the if statement at (1). Thus, your code calls sqlite3_bind_text and then immediately sqlite3_bind_null for the same parameter. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recovery program
Manoj M wrote: Hi, I am working with a project that requires a SQLite recovery module. I have a corrupted database(see the link below) where if I am issuing a query like select * from objects where dbname='information_schema' leads to application crash(even sqlite commandline program). As I was looking for possible ways, I saw sqlite command-line program is able to dump my sample corrupted database and then able to create a new database from this(using .read filename). Is this(.dump) is the standard for recovering a corrupted database? what is special with .dump commad? can we also follow the same method for crash recovery? Is there any other better method to do it(using some exception handling)? sample corrupted db attached https://rcpt.yousendit.com/624254893/4d5c40a04a2eb6cdf2468bd999485ef2 Thanks in advance! Regards, Manoj Marathayil hi, the .dump command isn't reliable if the database is corrupt. i also needed the ability to restore an sqlite3 database after a corruption. my approach was to have the application record all database inserts and updates into a history table within the database and to text/log file as well. my backup script would test for database corruption (with sqlite3 dbpath 'pragma integrity_check') before doing the backup (with sqlite3 dbpath .dump). if the database is corrupt, it would restore the previous good backup, then it would run a recovery program which identifies the difference between the history table and the history log file and reapply any inserts or updates that are present in the log file but not in the history table. of course, you need all of this set up in advance, before your first corruption. cheers, raf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY KEY and triggers
On Nov 16, 2008, at 2:46 AM, Craig Smith wrote: I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id fields in my SQLite projects, yet I would like to try some triggers as well. Of course, every time I add a trigger that accesses a table with these types of id fields, all sorts of odd things happen. I read in the documentation about simply using PRIMARY KEY instead of INTEGER PRIMARY KEY, but then I lose my AUTOINCREMENT ability. To clarify, do you mean that an automatically generated autoincrement value is not available in BEFORE INSERT triggers? Presently, it is only available in AFTER INSERT triggers. Dan. Does anyone have a solution that both provides AUTOINCREMENT (or a reasonable facsimile) to a PRIMARY KEY field and allows the usage of triggers on those tables? Thank you very much. Craig Smith ___ 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
Re: [sqlite] Escaped values throws error
On Nov 17, 2008, at 1:25 AM, P Kishor wrote: On 11/16/08, Ben Marchbanks [EMAIL PROTECTED] wrote: Abridged sample of the insert statement that produces an error when trying to insert escaped text. INSERT INTO pageWords (word) VALUES ( \nitch\ ); SQL text strings are delimited by single quotes. If your really want to insert nitch (including the double quotes), try ... VALUES ('nitch'); Alternatively, check out the ESCAPE clause in SQLite docs. The ESCAPE clause is part of the syntax for LIKE expressions. Despite what it intuitively sounds like it should do, it cannot be used to escape characters in string literals. The passage that describes how to form string literals is hidden in this page: http://www.sqlite.org/lang_expr.html It reads: A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INTEGER PRIMARY KEY and Triggers
On Nov 17, 2008, at 12:45 AM, [EMAIL PROTECTED] wrote: I am addicted to using INTEGER PRIMARY KEY AUTOINCREMENT as the id fields in my SQLite projects, yet I would like to try some triggers as well. Of course, every time I add a trigger that accesses a table with these types of id fields, all sorts of odd things happen. I read in the documentation about simply using PRIMARY KEY instead of INTEGER PRIMARY KEY, but then I lose my AUTOINCREMENT ability. To clarify, do you mean that an automatically generated autoincrement value is not available in BEFORE INSERT triggers? Presently, it is only available in AFTER INSERT triggers. Dan. Dan: Yes, I was using the AFTER INSERT on the trigger. Please see my followup posting in Issue 49, message number 1. I posted a clarification of the odd behavior I am observing. Does anyone have a solution that both provides AUTOINCREMENT (or a reasonable facsimile) to a PRIMARY KEY field and allows the usage of triggers on those tables? Thank you very much. Craig Smith Craig Smith [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recovery program
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 raf wrote: i also needed the ability to restore an sqlite3 database after a corruption. What exactly are you doing that corrupts the database in the first place? If the machine you are using is randomly corrupting data then having your own journal won't help that much! If you are doing things like turning synchronous off then it is easier to have two database files (use ATTACH). Have one be the defaults (ie correctness, no corruption) at the expense of speed and the second be turned for speed at the expense of possible corruption. You can copy the data back and forth as necessary. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkkhGJgACgkQmOOfHg372QRtLACbBSRJL5I2F7MNiJbQOWufavhx a4YAn0ji1eUJI2cGg5Qo0ZwAOiZTOBoq =xOxr -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_prepare memory leak
Hi, Sherief, thank you for our kind help. You're welcome :). I really did use globals. I found a way to run DumpMemorLeaks after all globals and static variables are deallocated, I used _CrtSetDbgFlag(_CRTDBG_ALLOC_MEM_DF | _CRTDBG_LEAK_CHECK_DF); I should've RTFMed harder :(. I made some malloc and new leaks around the code I'm interested in to see if it works - worked great, but I found NO leaks in sqlite sqlite3_prepare. As expected. I'm not sure if it means that there is an error in Deleaker or CrtDumpMemoryLeaks doesn't report mallocs from DLL ( i bet on Deleaker because it claims that there is a leak in heap so CrtDumpMemoryLeaks should show this ). How do you think? Bartek The behavior of _CrtDumpMemoryLeaks() is a bit tricky across module barriers. Though not in MSDN, but from a simple app/dll combination I built I found out that _CrtDumpMemoryLeaks() reports memory leaks caused by the calling module only (exe or dll: if dll leaks and exe calls it, no leak will be reported). Also, it doesn't watch memory not allocated by the CRT, such as via Heap*() functions. How much do you know about the DLLs you're using? Are they reliable? Does your leak checker support some sort of stack-trace option to show where the leaked memory was allocated? How does it handle non-CRT allocations (not malloc() and new)? - Sherief ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users