[sqlite] Journal deletion: no directory fsync?
After finishing a transaction and deleting the rollback journal, no directory fsync is performed. If the system crashes immediately after a commit returns to the caller, the unlink may not have been flushed to disk, causing the journal to be rolled back and undoing a commit that had already returned. There's no danger of corruption here, but doesn't this break durability? -- Glenn Maynard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_prepare_v2 and error reporting
On Sun, Jul 20, 2008 at 11:50:20PM -0400, Glenn Maynard wrote: > Why does the attached test case show the expected "cannot rollback - > no transaction is active" error with the v1 interface, but the > generic "SQL logic error or missing database" error when using the v2 > interface? > > If I add a sqlite3_reset to the v2 interface case (treating it like > v1), I get the expected error message. > > (tested on 3.6.0) I guess this is a bug, then... Easy enough to work around (reset the statement like the v1 API, which is usually a good thing to do anyway), just a pain to diagnose if you don't know about it. -- Glenn Maynard ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Convert the CURRENT_TIMESTAMP
Thanks Igor! It worked. JP - Original Message From: Igor Tandetnik <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Friday, July 25, 2008 9:21:26 PM Subject: Re: [sqlite] Convert the CURRENT_TIMESTAMP "Joanne Pham" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi all , > I have the following statement to convert the CURRENT_TIMESTAMP to > format as > TUE JULY 25 23:11:13 2008 but I got the empty string. Can you help me > why the empty string is returned. > Below is sql statement: > select > (case strftime('%w', d) when 0 then 'SUN' when 1 then 'MON’ when 2 > then ‘TUE’ when 3 then ‘WED’ when 4 then ‘THUR’ when 5 then ‘FRI’ > when 6 then ‘SAT’ end) || ‘ ‘ || > (case strftime('%m', d) when 1 then 'JAN' when 2 then ‘FEB’ when 3 > then ‘MAR’ when 4 then ‘APR’ when 5 then ‘MAY’ when 6 then ‘JUN’ when > 7 then ‘JUL’ when 8 then “AUG’ when 9 then ‘SEP’ when 10 then ‘OCT’ > when 11 then ‘NOV’ when 12 then 'DEC' end) >>> ' ' || > strftime('%d %H:%M:%S %Y', d) > from (select CURRENT_TIMESTAMP as d); Change strftime('%w', d) to CAST(strftime('%w', d) as INTEGER), and similarly for strftime('%m', d). strftime returns a string, which doesn't match any condition in the CASE statement, so the statement produces NULL, and then the whole expression becomes NULL. To avoid this, the result of strftime needs to be converted to integer. And fix smart quotes ‘’ to plain apostrophe ' Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] empty result sets without column names?
"Markus Hoenicka" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > according to my experience, sqlite3_get_table() does not return a row > containing the column names and sets numcols to zero if a query > returns zero rows. This is in contrast to other database engines like > MySQL, PostgreSQL, and Oracle which return the column information even > if there are no rows in the result set. Is this a deliberate design > decision? Would there be a way to change it? Don't use sqlite3_get_table. Use sqlite3_column_name, sqlite3_column_origin_name et al. You can call these on a prepared statement without even having to run it, so it doesn't matter whether it returns any rows or not. If all you want is to get column names of one table, see also PRAGMA table_info. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] empty result sets without column names?
Hi, according to my experience, sqlite3_get_table() does not return a row containing the column names and sets numcols to zero if a query returns zero rows. This is in contrast to other database engines like MySQL, PostgreSQL, and Oracle which return the column information even if there are no rows in the result set. Is this a deliberate design decision? Would there be a way to change it? libdbi (http://libdbi.sourceforge.net) is a database abstraction layer for C which supports a variety of database engines including SQLite. One of our users recently noticed the inconsistency between database engines. He tried to use this query: SELECT * FROM table WHERE 0=1 to test for the existence of a particular table and to retrieve its column information at the same time if it does exist. This may be a bit of a shortcut but I thought I'd ask to see whether SQLite deliberately suppresses the column information, or whether simply no one saw this as a limitation yet. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with "mhoenicka") http://www.mhoenicka.de ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users