Re: [sqlite] How to maintain EXCLUSIVE access to database continuously ?
Thanks for alternative ideas. In my app, user need Exclusive mode occassionally. But when uses Exclusive mode, user may enter/import some data or may change permissions for other users, or some report generation. User may use Exclusive mode for few minutes to may be 1-2 hours. That time no other user should be able to access the database. I tried to use extra file (non-database file, same filename) to maintain exclusive access. When that extra file is present/locked, other user can not access that database (company). But I think, there must be some way/trick to maintain EXCLUSIVE access to database continuously (need to COMMIT data in-between) ? i.e. to maintain EXCLUSIVE access, and still commiting data in-between ? Rohit -- View this message in context: http://www.nabble.com/How-to-maintain-EXCLUSIVE-access-to-database-continuously---tf2326092.html#a6479634 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How long can I keep a prepared statement around?
Hello Michael, Thanks. Best regards, Kervin --- Michael Ruck <[EMAIL PROTECTED]> wrote: > Use the function sqlite3_expired to determine, when > you need to > recompile a prepared statement. That's the approach > I use. > > Mike > > Am 24.09.2006 um 20:48 schrieb Kervin L. Pierre: > > > Hello, > > > > I have a few queries that are executed very > > often. I would like to keep them around as > > much as possible. > > > > The problem is, I don't know what > > 'invalidates' a prepared statement. In > > other words, when can I expect to have to > > 're-'prepare a statement? > > > > How long can I keep a prepared statement? > > Can they be passed between threads? I take > > it they are tied to a specific sqlite3_db* > > handle? Do starting new transactions, or > > transaction rollbacks, etc. affect them? > > > > Ideally, for instance, for simple queries > > such as 'BEGIN' and 'COMMIT', I'd like to > > keep those prepared statements for the > > lifetime of the application if possible. > > > > Any information would be appreciated. > > > > Best regards, > > Kervin > > > > > -- > > > --- > > To unsubscribe, send email to > [EMAIL PROTECTED] > > > -- > > > --- > > > > > - > To unsubscribe, send email to > [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How long can I keep a prepared statement around?
Hello Igor, Thanks for the info. Best regards, Kervin --- Igor Tandetnik <[EMAIL PROTECTED]> wrote: > Kervin L. Pierre > > wrote: > > The problem is, I don't know what > > 'invalidates' a prepared statement. In > > other words, when can I expect to have to > > 're-'prepare a statement? > > > > How long can I keep a prepared statement? > > Can they be passed between threads? I take > > it they are tied to a specific sqlite3_db* > > handle? Do starting new transactions, or > > transaction rollbacks, etc. affect them? > > Prepared statements are tied to a connection > (sqlite* handle). Since > SQLite connection cannot be shared between threads, > prepared statements > cannot either. A prepared statement is valid for the > lifetime of a > connection, with one exception: it becomes invalid > when database schema > changes, that is, when tables are created, altered > or dropped, when > triggers are created or dropped, and so on. > > Igor Tandetnik > > > - > To unsubscribe, send email to > [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Disconnected machine with database, SELECT still works !
On 9/23/06, Martin Alfredsson <[EMAIL PROTECTED]> wrote: Hi ! Tried to solve a problem and what I did was to open the database over the net and then physically disconnect the machine. I can still SELECT ! It's probably cached on your local machine. Funny huh? :) - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Locking problems
On 9/23/06, Martin Alfredsson <[EMAIL PROTECTED]> wrote: > > Windows XP, SQLite 3.3.4. > > /Martin > ma1999ATjmaDOTse Martin, did anyone mention virus scanners can lock your database file? Do you have any other processes that might be reading the database file? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Queries fail - I can't figure out why
Michael Ruck
wrote:
I have the tables of the following style:
CREATE TABLE Objects (ObjectID TEXT PRIMARY KEY, Class TEXT)
And I'm executing the following statement in the sqlite3 shell:
SELECT * FROM Objects WHERE ObjectID =
'{08021C17-46DD-4d83-A6FE-DDF0F7EC0AAE}'
In the shell this query succeeds. However if I try to do the same
thing via
sqlite3_prepare, sqlite3_bind_text16 and sqlite3_step, then
sqlite3_step
always returns 101 (SQLITE_DONE.)
The query you show does not have any parameters - what are you using
sqlite3_bind_text16 for? You are probably using a different query in
your program - show it.
As a wild guess, does your query look anything like this:
SELECT * FROM Objects WHERE ObjectID = '?'
(with question mark in quotes)? Note that '?' is a string literal
consisting of one question mark character, not a parameter placeholder.
The correct parameterized query is
SELECT * FROM Objects WHERE ObjectID = ?
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: Re[2]: [sqlite] Bug in the precision of strftime function
--- Slava Tutushkin <[EMAIL PROTECTED]> wrote:
>
> MJ> -4713-11-24 GC, which is -4712-01-01 JC" so datetime(0) does correctly
> MJ> convert Julian Day 0 to a (proleptic) Gregorian date but not to a Julian
> MJ> Date as (I think) I thought. That'll teach me to post follow-ups when
>
> I see no problem here while, because it does not matters, what day
> julianday() references to,
> all we need - stable pivot point. I'm using REAL julianday() value for
> internal storage in
> sqlite only, after reading from DB I'm converting to boost::ptime.
> But strftime was not returning precise values sometimes.
>
> My post was about floating point problems with the milliseconds in the
> strftime. I was
> investigated it a little bit longer, and found out that even the query
> "select strftime('%Y-%m-%d %H:%M:%f', '2006-09-24T10:50:26.046');"
> is returning 2006-09-24 10:50:26.045.
>
> Anyway, somebody (thank you!) posted a patch in the ticket, solving this
> problem. I was not
> tested it for now, but seems it looks ok.
> URL for ticket is:
> http://www.sqlite.org/cvstrac/tktview?tn=1991
I had missed the boundary case for 59.+ seconds.
Please use the latest version of the patch.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] How long can I keep a prepared statement around?
Hello, I have a few queries that are executed very often. I would like to keep them around as much as possible. The problem is, I don't know what 'invalidates' a prepared statement. In other words, when can I expect to have to 're-'prepare a statement? How long can I keep a prepared statement? Can they be passed between threads? I take it they are tied to a specific sqlite3_db* handle? Do starting new transactions, or transaction rollbacks, etc. affect them? Ideally, for instance, for simple queries such as 'BEGIN' and 'COMMIT', I'd like to keep those prepared statements for the lifetime of the application if possible. Any information would be appreciated. Best regards, Kervin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re[2]: [sqlite] Bug in the precision of strftime function
MJ> -4713-11-24 GC, which is -4712-01-01 JC" so datetime(0) does correctly
MJ> convert Julian Day 0 to a (proleptic) Gregorian date but not to a Julian
MJ> Date as (I think) I thought. That'll teach me to post follow-ups when
I see no problem here while, because it does not matters, what day julianday()
references to, all we need - stable pivot point. I'm using REAL julianday()
value for internal storage in sqlite only, after reading from DB I'm converting
to boost::ptime.
But strftime was not returning precise values sometimes.
My post was about floating point problems with the milliseconds in the
strftime. I was investigated it a little bit longer, and found out that even
the query
"select strftime('%Y-%m-%d %H:%M:%f', '2006-09-24T10:50:26.046');"
is returning 2006-09-24 10:50:26.045.
Anyway, somebody (thank you!) posted a patch in the ticket, solving this
problem. I was not tested it for now, but seems it looks ok.
URL for ticket is:
http://www.sqlite.org/cvstrac/tktview?tn=1991
--
Slava Tutushkin, http://aloner.ru
mailto:[EMAIL PROTECTED]
ICQ: 55463183
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Bug in the precision of strftime function
Martin Jenkins wrote: Looks like I'm getting more confused than I thought I was. :( According to http://www.hermetic.ch/cal_stud/jdn.htm a Julian Date can be a date in the Julian calendar *or* a Julian Day Number. Section 9 of that page states that "Julian day number 0 corresponds to -4713-11-24 GC, which is -4712-01-01 JC" so datetime(0) does correctly convert Julian Day 0 to a (proleptic) Gregorian date but not to a Julian Date as (I think) I thought. That'll teach me to post follow-ups when the MiL is chattering about knitting. :( Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How to maintain EXCLUSIVE access to database continuously ?
On Sun, 24 Sep 2006 04:41:55 -0700 (PDT), you wrote: >User (Client App) connects to middle-tier application-server and then user >may access any one company in normal or exclusive mode. Only >application-server communicates with database. If user wants exclusive >access, Application-Server needs to maintain that exclusive access to >database for that user. Hello I don't understand, how you get a real advantage, if you lock a Database (in daily processing) exclusive... I think, that isn't really necessary. But, if you want it nonetheless, then write and read a Lock-State-Table. I do it this way in my App. Every User do a Login in a specially Table and also a Lockout, if endet his work. If an exclusive Mode wanted by User, then write a adequate Info to the Lockstate-Table. Then check, that not another User has earlier rights. If this successful, the User can work in exlusive mode, is not, kick him out ;-) The real advantage by this way is, an Administrator is never blocked by a silly User, who has forgotten to logout and is gone to weekend at wednesday. My opinion is, never do a hard lock to a Database, except, it is done by the Database himself. Best Regards Thomas www.thlu.de - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Queries fail - I can't figure out why
Hi,
I have the tables of the following style:
CREATE TABLE Objects (ObjectID TEXT PRIMARY KEY, Class TEXT)
And I'm executing the following statement in the sqlite3 shell:
SELECT * FROM Objects WHERE ObjectID =
'{08021C17-46DD-4d83-A6FE-DDF0F7EC0AAE}'
In the shell this query succeeds. However if I try to do the same thing via
sqlite3_prepare, sqlite3_bind_text16 and sqlite3_step, then sqlite3_step
always returns 101 (SQLITE_DONE.) I've opened the database using
sqlite3_open16. No schema changes, no other connections are open at this
point. However the query always fails, even though the same query succeeds
in the shell. I've even tried to insert a row and immediately query for the
same row, but even that fails.
I have this issue with several tables. With several different code places -
all have the same style and probably the same bug.
Can anyone give me a hint at what's wrong?
Thanks,
Mike
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Bug in the precision of strftime function
Slava Tutushkin wrote:
I think, I found a bug in the strftime function.
When I executed the next query: select strftime('%Y-%m-%d %H:%M:%f',
julianday('2006-09-24T10:50:26.047')); it returned 2006-09-24
10:50:26.046
At the same time, I can clearly see, that julianday for that time for
.046 and .047 points is returning different numbers. It seems, there
is a bug in the strftime function.
I think this is down to floating point rounding errors. I tried a couple
of juliandate queries in the sqlite shell - only 8 decimal places are
returned (on my 32 bit XP box) and when you get down to the millisecond
range you run out of decimal places. For the (dozen or so) dates I tried
there was a repeatable error for the following number of milliseconds:
5 7 9 11 13 15 17 22 24 26 28 30 32 34 41 43 45 47 49 51 53 58 60 62 64
66 68 70 77 79 81 83 85 87 89 94 96 98
After looking at the source, the juliandayFunc wrapper returns a double
and as one millisecond = 1.15740740325e-008 days I think your problem is
down to rounding errors.
Also, I note two other problems:
1) The "Battle of Hastings" example query on the website
SELECT julianday('now') - julianday('1066-10-14','gregorian');
doesn't work with sqlite shell 3.3.4 or 3.3.6 - the 1066 date returns
nothing with the gregorian modifier.
Looking at the source, the text "gregorian" only appears in comments in
the 3.3.4, 3.3.5 and 3.3.7 trees. juliandate() calls isDate() and then
parseModifier() - "localtime" and "utc" are both listed in the comments
for parseModifier() but "gregorian" is not. The comments and docs state
that Julian dates are used internally which suggests the example given
is a bug in the documentation rather than in the library.
2) The Julian day docs state that a Julian date is "the number of days
since noon in Greenwich on November 24, 4714 B.C" but when I probed
around this date to check the resolution, I got a JD of -365 for that
date and 0 for "select julianday('-4713-11-24T12:00:00.%03d')"
I can't claim to understand the algorithm used in computeJD() and don't
have the reference, but the relevant area in date.test is:
> # Negative years work. Example: '-4713-11-26' is JD 1.5.
> #
> datetest 9.1 {julianday('-4713-11-24 12:00:00')} {0.0}
> datetest 9.2 {julianday(datetime(5))} {5.0}
To me, it looks like this code is testing 24-11-4713BC against JD 0 and
this is confirmed by the following session:
SQLite version 3.3.7
Enter ".help" for instructions
sqlite> select datetime(0);
datetime(0)
-4713-11-24 12:00:00
sqlite>.quit
So, it looks like the documentation for juliandate() is wrong for the
base year.
I did do a bit of research and there seems to be some confusion (or at
least, lack of precision when talking about) about the start date.
*Wikipedia:* http://en.wikipedia.org/wiki/Julian_date
The Julian day or Julian day number (JDN) is the (integer) number of
days that have elapsed since Monday, January 1, 4713 BC in the proleptic
Julian calendar 1. That day is counted as Julian day zero. [snip]
The Julian Date (JD) is the number of days (with decimal fraction of the
day) that have elapsed since 12 noon Greenwich Mean Time (UT or TT) of
that day. Rounding to the nearest integer gives the Julian day number.
*US Navy:*
http://aa.usno.navy.mil/data/docs/JulianDate.html has a calculator
which gives Jan 1st 4713 BCE 12:00:00 for JD 0
*Doug Welch:*
http://wwwmacho.mcmaster.ca/JAVA/JD.html has a calculator which gives
-4712-01-13 12:00:00 for JD 0
*Wolfram Research:*
http://scienceworld.wolfram.com/astronomy/JulianDate.html has a page
which defines the Julian day as "The number of days since noon on
January 1, -4712, i.e., January 1, 4713 BC".
The last page links to http://scienceworld.wolfram.com/astronomy/BC.html
which explains why Doug Welch has "-4712" and the others have "4713 BC"
(news to me!) and based on that it appears that there might be a year
off bug in juliandate() :(
Martin
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] How to maintain EXCLUSIVE access to database continuously ?
use your chosen OS's reader/writer locks per user. Some additional info User (Client App) connects to middle-tier application-server and then user may access any one company in normal or exclusive mode. Only application-server communicates with database. If user wants exclusive access, Application-Server needs to maintain that exclusive access to database for that user. Rohit - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How to maintain EXCLUSIVE access to database continuously ?
Hi SQLite experts/users In my multi-user app (Win32, SQLite3.3.4), one database file for each company (accounts). User can access company accounts in two modes, normal mode and exclusive mode. In exclusive mode, only one user will have access, no other user should be able to access that company database. I know that BEGIN EXCLUSIVE allows such access. User may enter info even in exclusive mode. But when user enters and saves some info, I need to COMMIT and again need to issue BEGIN EXCLUSIVE (because I can't wait till last for commiting). Before issuing BEGIN EXCLUSIVE again, in-between, if other user gets access to database, then first user (with exclusive mode) will not be able to maintain his/her exclusive access. How to maintain EXCLUSIVE access to database continuously (need to COMMIT data in-between) ? i.e. is there any way to maintain EXCLUSIVE access, and still commiting data in-between ? Thanks for any guidance. Rohit -- View this message in context: http://www.nabble.com/How-to-maintain-EXCLUSIVE-access-to-database-continuously---tf2326092.html#a6471314 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Bug in the precision of strftime function
I think, I found a bug in the strftime function.
When I executed the next query:
select strftime('%Y-%m-%d %H:%M:%f', julianday('2006-09-24T10:50:26.047'));
it returned 2006-09-24 10:50:26.046
At the same time, I can clearly see, that julianday for that time for .046 and
.047 points is returning different numbers.
It seems, there is a bug in the strftime function.
--
Slava Tutushkin, http://aloner.ru
mailto:[EMAIL PROTECTED]
ICQ: 55463183
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Wish to store a C structure in sqlite column
On Sep 21, 2006, at 7:18 AM, Narendran wrote:
as far as my knowledge SQLITE allows me to declare the column types
suppoted by the programming languare or say i am using blob . My
requirement
is i wish to store a structure in the SQLite column.
Instead of storing the structure in a single column, create a
separate table to represent the structure, and then use a column to
reference a row in that table via a foreign key.
Given the extremely lightweight nature of the structure that you
later posted:
typedef struct ethernetcard1
{
char port[10];
char ipaddress[20];
char mask[20];
int bandwidth;
}
as well as the fact that sooner or later you're likely to want to
query on it, you may as well just store it as real data rather than
as a BLOB:
CREATE TABLE 'ETHERNETCARD' (
ID INTEGER PRIMARY KEY,-- SQLite does this implicitly as ROWID
PORT VARCHAR(10),
IPADDRESS VARCHAR(20),
MASK VARCHAR(20),
BANDWIDTH INTEGER
);
Of course, you might also want to encode your IP address and netmask
into network-byte-order integers rather than store them as strings,
but I think the above gives you an idea of what I mean. And if
"port" refers to a physical port name (such as "en1") you might even
want to have a separate table for ports, and have the port column
just contain a foreign key referencing that table...
If you start to decompose your use of SQLite in this fashion, you'll
actually be using the database *as* a database, and you'll be much
better able to leverage it to do new and interesting things in the
future.
-- Chris
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
