Re: [sqlite] DateTime Objects
Hi Jonathon, You may already be aware of this, but I didn't see it here in the discussion so I thought I'd chime in. SQLite doesn't really provide a native datetime type. The data type documentation lays out what's going on in detail (link below), but I'm pretty certain that your datetime column is actually a TEXT type. http://sqlite.org/datatype3.html If this poses an efficiency problem, others have suggested some good work-arounds earlier. I suspect that for the general use-cases of SQLite, storing date-time information in this way isn't really going have too bad of an impact. And, as others have pointed out, you'll almost always have to do some kind of mapping in your application from how the db stores date-times to how your platform likes to do it (i.e. NSDate), and then you'll be manipulating those objects at presentation time for the end-user. So I would just rock it and not worry about it too much. If you're really that hard up on saving CPU cycles, they might be better gained elsewhere. Cheers, Billy On Sat, Feb 28, 2009 at 8:30 PM, jonwood <nab...@softcircuits.com> wrote: > > > John Stanton-3 wrote: > > > > Use the Sqlite date storage format and support. With that approach > > which is astronomivally correct you can deliver any date format or > > manipulwtion, You may need some custom written functions. to get week > > number according to national rules etc, but the method is sound. It is > > also compatible with different date systems. > > > > Thanks, but I'm not sure what this means. "SQLite date storage format and > support" doesn't appear to be a specific term (at least, it didn't turn up > anything specific on Google). Is there a link that would describe what > you're referring to and how it'd help my situation? > > Thanks. > > Jonathan > > > -- > View this message in context: > http://www.nabble.com/DateTime-Objects-tp22264879p22268988.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Billy Gray wg...@zetetic.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
> > Are there any plans to enhance SQLite to support some of Oracle's > parallel processing or partitioning capabilities? > Malcolm, I realized that you're asking Richard, and not the peanut gallery, but I figured I might as well ask out of curiosity: why do you want to see these features in SQLite? Cheers, Billy On Mon, Feb 23, 2009 at 3:54 PM, <pyt...@bdurham.com> wrote: > Dr. Hipp, > > When you say "SQLite is way faster than Oracle in a single-user > applications" do you mean that SQLite can be faster than Oracle even > when Oracle's parallel processing features are being used? For example > Oracle's support for parallelization can speed up table loading from an > external data source, certain SQL selects, and certain indexing > operations. > > Are there any plans to enhance SQLite to support some of Oracle's > parallel processing or partitioning capabilities? > > Thank you, > Malcolm > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Billy Gray wg...@zetetic.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Any concept of row number in SQLite?
Might I suggest using MD5 (or other) hashing algorithms in your application as a way to check whether data in your application's memory is different from what's in the database? There are also locking mechanisms you can use to avoid (or create!) concurrency problems. If you're not worried about two processes operating on the same row(s), this all shouldn't really be much of an issue for you. In any event, I think you're looking to implement some means for your application to tell whether the data is dirty or not, and ROWNUM or a similar feature would not be more helpful than a primary key. For the record, you can do a lot of convenient things using ROWNUM in Oracle :-) Cheers, Billy On Thu, Feb 19, 2009 at 9:09 PM, His Nerdship <slgdoug...@optusnet.com.au>wrote: > > OK, thanks for the info. > I will just do what I said before, namely read the whole table (it won't be > too big) and extract the required row from the returned array. > The reason I wanted a row ID was that all the fields in the display grid > can > be edited, so by the time I come to process it, any of them might have > changed from the original in the database so I can't use them in a WHERE > clause. > At least I know now > Thanks again > > > P Kishor-3 wrote: > > > > On Thu, Feb 19, 2009 at 6:54 PM, His Nerdship > > <slgdoug...@optusnet.com.au> wrote: > >> > >> Hi, > >> I am converting a program from Paradox (stop laughing, please) to > SQLite. > >> Paradox has a useful feature where you can specify the actual index of a > >> row > >> in the table. This is handy when the table is displayed in a grid and > >> you > >> want the record corresponding to a row in that grid - you can just > >> specify > >> the index, say 28, of that grid row and it will get the record no 28 > from > >> the table. It spares the need for a SELECT statement, and is a lot more > >> efficient. > >> As a SQLite newbie, the only way I can see to do this is to read the > >> whole > >> table with sqlite3_get_table() and then get the required row from the > >> returned array. This seems overkill when I just want a single record. > > > > There is the rowid, but I am not sure what you want to do... are you > > expecting a database table to be a linear list of entries? Generally > > one uses a spreadsheet for that kind of stuff. A SQL database doesn't > > have an internal concept of order. You specify a criteria and the db > > returns a SET of rows or records. You can constrain the SET by > > specifying criteria (the WHERE clause), and you can impose an order on > > the returned rows by specifying an ORDER clause. > > > > If you want just one specific row, just do a > > > > SELECT cols FROM table WHERE some_primary_key = ? > > > > If you don't want to specify and control your own primary key, you can > > use the rowid which is something the db uses internally for its own > > shenanigans. > > > > > >> Is there a more compact way of doing this? > >> Thanks in advance etc. > >> Sholto > > > > > > > > -- > > Puneet Kishor http://www.punkish.org/ > > Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ > > Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ > > Sent from: Madison Wisconsin United States. > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > View this message in context: > http://www.nabble.com/Any-concept-of-row-number-in-SQLite--tp22112862p22113562.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Billy Gray wg...@zetetic.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRIGGER to catch UPDATES without a WHERE clause
Hi Puneet, I don't know if you can intercept a query with a trigger the way you're intending (maybe this is possible in sqlite?), but if I may make a suggestion, get in the habit of starting your every session on a crucial database with a transaction. The moment you login: sqlite> BEGIN; Take a good hard look at the number of rows affected after every operation, check all the data before you end your session, and then either commit it, or rollback the changes: sqlite> COMMIT; sqlite> ROLLBACK; Hope that helps (Time Machine certainly rocks, yeah?) Cheers, Billy On Fri, Feb 6, 2009 at 8:06 AM, P Kishor <punk.k...@gmail.com> wrote: > I had asked this a while back but I think my query got Warnocked. > > I would like to create a TRIGGER (or a CONSTRAINT) that throws an > error if an UPDATE query is run *without* a WHERE clause. This would > prevent accidentally mucking up the entire database, which, believe > you me, I have done more than once, and have been saved only by the > magic of Time Machine. > > The TRIGGER would allow a full UPDATE of the table only by using some > special syntax in the query... like say, by adding something like > WHERE 0=0; > > > > -- > Puneet Kishor > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Billy Gray wg...@zetetic.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insertion and Updation using sqlite3_prepare()
I second that motion, there's a possibility here that you might be mixing up integers and strings either when you bind for the insert or when you bind for the select later to check if the insert worked! On Fri, Feb 6, 2009 at 12:22 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > "hussainfarzana" > <hussainfarz...@gmail.com> wrote in > message > news:21866676.p...@talk.nabble.com<news%3a21866676.p...@talk.nabble.com> > > Below is the query we used where all the fields are numeric and it is > > prepared once: > > > > INSERT INTO ShopDataNum values(?,?,?,?,?,?,?,?,?,?,?) > > > > When we bind the values the steps were returning the correct value > > but the record is not found in the DB. > > > > Then we tried by passing the static value for the first field in > > sqlite3_prepare and we bind the values. > > INSERT INTO ShopDataNum values(960002,?,?,?,?,?,?,?,?,?,?) > > The steps were executed correctly and the record is inserted > > correctly in the DB. > > > > Again we tried the first query and assigned the static value for the > > first field while binding the values, the steps were executed > > correctly but the record is not found in the DB. > > Can you show a small complete code sample that reproduces the problem? > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Billy Gray wg...@zetetic.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Resolving some compiler warnings when closing db
Ah, good thinking.
The Apple-supplied sqlite3.h in Mac OS X (/usr/include/sqlite3.h) doesn't
actually declare it (wtf?) -- although I do have it in
/usr/local/include/sqlite3.h), and I'm statically linking against my own
build. Which I guess is why it builds just fine. Getting the search paths to
cooperate correctly in XCode is a bit of a nightmare.
Thanks!
Billy
On Tue, Feb 3, 2009 at 11:17 AM, Simon Davies <
simon.james.dav...@googlemail.com> wrote:
> 2009/2/3 Billy Gray <wg...@zetetic.net>:
> > Hi all,
> >
> .
> .
> .
> > #import
> > ...
> > - (void) closeDb {
> >// first loop thru any existing statements and kill'em
> >sqlite3_stmt *pStmt;
> >while( (pStmt = sqlite3_next_stmt(db, 0)) != 0 ){
> >sqlite3_finalize(pStmt);
> >}
> >
> >int result = sqlite3_close(db);
> >if (result != SQLITE_OK) {
> >NSAssert1(0, @"Failed to close database, returned error code %d",
> > result);
> >}
> >db = nil;
> > }
> >
> > This produces two warnings for the sqlite3_next_stmt line:
> >
> > - Implicit declaration of sqlite3_next_stmt (which is bizarre...)
>
> The compiler is telling you that there is no declaration of
> sqlite3_next_stmt. Have a search in sqlite3.h and see if it is lying.
>
> Supplementary question: are you using a version of sqlite that
> implements sqlite3_next_stmt?
>
> Rgds,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Billy Gray
wg...@zetetic.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Resolving some compiler warnings when closing db
Hi all,
Forgive me if this has been discussed here already, I tried searching for
the answer and came up empty.
I'm trying to work out a couple of compiler warnings I get when I close my
sqlite3 connection as described in the manual. I know I could just silence
them, but I'm a bit of a stickler for stuff like this. Here's the relevant
code:
#import
...
- (void) closeDb {
// first loop thru any existing statements and kill'em
sqlite3_stmt *pStmt;
while( (pStmt = sqlite3_next_stmt(db, 0)) != 0 ){
sqlite3_finalize(pStmt);
}
int result = sqlite3_close(db);
if (result != SQLITE_OK) {
NSAssert1(0, @"Failed to close database, returned error code %d",
result);
}
db = nil;
}
This produces two warnings for the sqlite3_next_stmt line:
- Implicit declaration of sqlite3_next_stmt (which is bizarre...)
- Assignment makes pointer from integer without a cast
Obviously, I'm working in Objective-C, I'm justing GNU compiler via Apple's
XCode. I've tried resolving the assignment issue like this, and while it
cures the warning, not sure it's entirely appropriate:
int next_stmt;
sqlite3_stmt *pStmt;
while( (next_stmt = sqlite3_next_stmt(db, 0)) != 0 ){
pStmt = (sqlite3_stmt *)next_stmt;
sqlite3_finalize(pStmt);
}
And as far as the 'implicit declaration' issue, I really have no idea what's
causing that, since the other sqlite3 functions aren't giving me the same
problem.
Anybody else ever have to work this out?
Cheers!
--
Billy Gray
wg...@zetetic.net
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
