[sqlite] Help Creating Tables

2008-08-10 Thread jonwood
I'm creating SQLite tables from code and could use help with the following issues: 1. I'd like some tables to include foreign keys. These should be indexed for speed, but they are neither primary keys or unique. But I don't see any option to index a column without it being one of those two? 2.

Re: [sqlite] Help Creating Tables

2008-08-10 Thread jonwood
Yeah, I'd seen both those links but guess I missed the specific explanation. Also, I had seen CREATE INDEX but didn't know I needed a separate statement to add an index to a table column. Many thanks! Igor Tandetnik wrote: > > "jonwood" <[EMAIL PROTECTED]> wrote in

[sqlite] Currency Issues

2008-08-14 Thread jonwood
I've done very little programming related to currency (dollars, etc.) and I'm wondering if I need to worry about rounding errors. Since SQLite doesn't appear to have a currency type, I had planned on using REAL instead. But I have a lot of reports to print out and I could see rounding errors with

Re: [sqlite] Currency Issues

2008-08-17 Thread jonwood
That's the plan then. Thanks. Dennis Cote wrote: > > jonwood wrote: >> I've done very little programming related to currency (dollars, etc.) and >> I'm >> wondering if I need to worry about rounding errors. Since SQLite doesn't >> appear to have a currency typ

Re: [sqlite] Currency Issues

2008-08-17 Thread jonwood
Brad Stiles-2 wrote: > > That's a very real possibility. I'll second your idea, and Dennis' > recommendation, to use an integer type for this (unless you are using a > compiler that has a scaled integer or dedicated currency type). In > addition, you might consider storing at least one more

Re: [sqlite] Currency Issues

2008-08-17 Thread jonwood
John Stanton-3 wrote: > > I implemented a decimal type in Sqlite. It uses fixed point and correct > rounding rules. To my mind it is an essential component if one is using > Sqlite for commerical purposes and must have accurate financial > information. > And, just to be clear: when you

[sqlite] Default Column Value to Local Time

2008-09-01 Thread jonwood
Okay, I give up. I've been searching for a while now. I've found a number of discussions about how CURRENT_DATE returns the date in UTC, and that it can be converted to local time. But I've yet to find one article that clearly states how one might do this. I would love to have a table column

Re: [sqlite] Default Column Value to Local Time

2008-09-01 Thread jonwood
Derrell Lipman wrote: > > I think this is the page you're looking for: > http://www.sqlite.org/lang_datefunc.html > > In particular, a query that returns the current time in the local (to > sqlite) time zone is: >SELECT datetime('now', 'localtime'); > or for just the date: >SELECT

Re: [sqlite] Default Column Value to Local Time

2008-09-01 Thread jonwood
P Kishor-3 wrote: > > Don't think it is possible. As you noted, the docs say very clearly > (http://www.sqlite.org/lang_createtable.html) -- " The DEFAULT > constraint specifies a default value to use when doing an INSERT. .. > default value may also be one of the special case-independant

[sqlite] Help Using RowID

2008-09-06 Thread jonwood
I have a table where the primary key consists of two columns. I'm doing this because these two columns combined must be unique, while each column is not unique on its own. Because of the increased complexity of a dual-column primary key for some operations, I'd like to use ROWID. But the

Re: [sqlite] Help Using RowID

2008-09-06 Thread jonwood
Scott Hess wrote: > > CREATE TABLE t ( > id INTEGER PRIMARY KEY, > a TEXT NOT NULL, > b INTEGER NOT NULL, > UNIQUE (a, b) > ); > > (a,b) will be just as unique as in the first case, but now you can use > id as a stable alias for rowid. There will be the same number of > btrees (one

[sqlite] CURRENT_DATE Behavior

2008-10-20 Thread jonwood
Greetings, Okay, I understand that the designer of SQLite felt it was important that fields with a default value of CURRENT_DATE should be initialized to the current date in a DIFFERENT time zone. Setting aside for now that I've read all the reasons for this and am very much against the

Re: [sqlite] CURRENT_DATE Behavior

2008-10-20 Thread jonwood
D. Richard Hipp wrote: > > CREATE TABLE whatever( > > timestamp DATE DEFAULT (datetime('now','localtime')), > ... > ); > Really? I can do that? Great! Thanks! -- View this message in context: http://www.nabble.com/CURRENT_DATE-Behavior-tp20075044p20082173.html Sent from

Re: [sqlite] CURRENT_DATE Behavior

2008-10-21 Thread jonwood
mikewhit wrote: > > Using UTC in the DB stops you going mad when something happens > on the DST changeover (localtime hours vanish, or happen twice), > or you have systems running in or across different countries. > > It also means you can subtract two times and always get the right answer >

Re: [sqlite] CURRENT_DATE Behavior

2008-10-21 Thread jonwood
cmartin-2 wrote: > > I suspect it is quite common, e.g., web apps built on SQLServer backends > are quite likely to use UTC. At any rate, it is definitely a design > decision, if one expects that local times will always work in all > scenarios, but all means use local times. If design

Re: [sqlite] CURRENT_DATE Behavior

2008-10-22 Thread jonwood
P Kishor-3 wrote: > > I thought it was very clearly indicated in one of the answers in this > thread on how to do that. Here it is again... > > datetime('now', 'localtime') > Yes, that was the answer. And it was implemented in my application within moments of being posted. The discussion has

Re: [sqlite] CURRENT_DATE Behavior

2008-10-22 Thread jonwood
P Kishor-3 wrote: > > 1. I don't fully understand how to convert UTC to local time? > 2. on a Website > For reasons I don't understand, you split one question in half and called it two questions. P Kishor-3 wrote: > > b. On the other hand, if you want to display on the web page, time >

[sqlite] How to Modify Table

2008-11-12 Thread jonwood
My database contains the following table: m_Database.ExecNonQuery(_T("CREATE TABLE Vehicles (") _T("VehicleID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,") _T("FK_CustomerID INTEGER NOT NULL,") _T("VehicleNumber INTEGER NOT NULL,") _T("VehicleTitle TEXT,") _T("VehicleMake TEXT,")

Re: [sqlite] How to Modify Table

2008-11-13 Thread jonwood
Slater, Chad wrote: > > Sqlite does not support modifying the unique constraints on a table: > > http://www.sqlite.org/lang_altertable.html > > So you probably need to do it the 'ol fashioned way: > Perfect--Just what I figured I'd need to do! Thanks! -- View this message in context:

[sqlite] DateTime Objects

2009-02-28 Thread jonwood
Greetings, I have a SQLite table that contains a DATETIME value. However, the database does not appear to provide enough control over how that value is formatted. For example, I'd like a two-digit year, to eliminate leading zeros, and to show time using AM/PM format. Is the ONLY way to

Re: [sqlite] DateTime Objects

2009-02-28 Thread jonwood
Derrell Lipman wrote: > > http://sqlite.org/lang_datefunc.html > Exactly. No 2-digit year format, no AM/PM format, and no way to eliminate leading zeros, etc. Just as I pointed out in my original post. Jonathan -- View this message in context:

Re: [sqlite] DateTime Objects

2009-02-28 Thread jonwood
D. Richard Hipp wrote: > > The date & time come out in an easily parseable format: -MM-DD > HH:MM:SS. So you call: > > sscanf(zDateStr, "%d-%d-%d %d:%d:%d", , , , , , ); > sprintf(zNewDate, "%d/%d/%d %d:%d%s", m, d, y%100, (H-1)%12+1, M, > H>=12 ? "pm" : "am"); > > Is that

Re: [sqlite] DateTime Objects

2009-02-28 Thread jonwood
Doug-4 wrote: > > I personally store my times as ints (__time64_t, or time_t). When I read > it > back my app formats it however I want. Simple :) > I think that's the conclusion I'm coming to as well. Thanks. Jonathan -- View this message in context:

Re: [sqlite] DateTime Objects

2009-02-28 Thread jonwood
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

Re: [sqlite] DateTime Objects

2009-02-28 Thread jonwood
>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

Re: [sqlite] DateTime Objects

2009-02-28 Thread jonwood
>Storing dates with a two-digit year is... The deja vu, the deja vu! >Why, oh why re-create y2k?! No one's talking about storing them that way. As far as printouts, I'm not overly concerned about this client's reports being ambiguous in the year 2100. Jonathan -- View this message in

[sqlite] Any Way to Peak at Next Row?

2009-03-10 Thread jonwood
I'm creating some reports with some SQLite data. One report groups data by one column and subtotals each group. It's working well but it really doesn't look right when the last item in a group is at the bottom of the page and the subtotal is then orphaned on the start of the next page. I really

Re: [sqlite] Any Way to Peak at Next Row?

2009-03-10 Thread jonwood
> What you want is to fetch the data into memory first, and then process > it later. They way you describe the goal, you'd only have to keep 1 > record in memory. Yeah, I've been toying with that. It makes things a bit more complex than I'd like but sounds like that can't be avoided. > Also, if

Re: [sqlite] Query Doesn't Find Record

2009-03-11 Thread jonwood
Doug Currie-2 wrote: > > Note the '/'s > What does this mean? What does DATE('2009-1-1') or DATE('2009/1/1') return? Does DATE() simply have no effect whatsoever? -- View this message in context: http://www.nabble.com/Query-Doesn%27t-Find-Record-tp22469520p22469578.html Sent from the SQLite

Re: [sqlite] Query Doesn't Find Record

2009-03-13 Thread jonwood
P Kishor-3 wrote: > > why don't you try it? See below -- > What are you folks using to type these queries? I've yet to find any good utilities that do this for the Windows platform. Thanks. Jonathan -- View this message in context:

Re: [sqlite] Query Doesn't Find Record

2009-03-13 Thread jonwood
Kees Nuyt wrote: > >>What are you folks using to type these queries? I've yet to find any good >>utilities that do this for the Windows platform. > > sqlite3.exe in a CMD window. > > And SQLiteSPy for browsing databases. > Thanks! Jonathan -- View this message in context:

[sqlite] Error Binding Parameter to Compiled Statement

2009-04-17 Thread jonwood
Greetings, I'm getting an error compiling a parameter to a compiled statement. Unfortunately, since I'm using a customer wrapper class around sqlite, posting my code won't be much help. Here's what I'm doing: I create a compiled statement (two, actually). Then, each time through a loop, I bind

Re: [sqlite] Error Binding Parameter to Compiled Statement

2009-04-17 Thread jonwood
SimonDavies wrote: > > Is sqlite3_reset() being called before looping around? > Nope. And calling it resolved the issue. I didn't realize that was necessary. Thanks much! Jonathan -- View this message in context: