[sqlite] Can't insert timestamp field with value CURRENT_TIME
Hi, I'm trying to add a timestamp field to an existing table using the following statement: ALTER TABLE test ADD COLUMN lunchtime TIMESTAMP NOT NULL DEFAULT CURRENT_TIME this fails with the following error: Cannot add a column with non-constant default Ok that makes sense - but why can we CREATE a table with a timestamp field whose default is CURRENT_TIME, but not alter one with that as the default value? I may be misusing the syntax - Thanks, Mark ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Suggests for improving the SQLite website
Not a terribly useful comment but was just glancing through the new look and noticed a typo: http://sqlite.hwaci.com/about.html We believe that General Electric uses SQLite in some product or another because they twice wrote the to SQLite developers .. wrote the to On Nov 9, 2007 1:22 PM, Joe Wilson [EMAIL PROTECTED] wrote: --- [EMAIL PROTECTED] wrote: There is a new look up on the demo site at http://sqlite.hwaci.com/ It looks good on Firefox and Safari, but IE6 renders The 'T' in 'SUPPORT' in the horizontal toolbar is cut off in my Linux Firefox 2.0.0.8 browser. I have a screen resolution of 1600x1200. It takes time to get all popular browsers working, but it leaves a good first impression with potential users of your software. __ 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] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
I think 4 looks great, Mark On Nov 9, 2007 1:45 PM, [EMAIL PROTECTED] wrote: Mark Wyszomierski [EMAIL PROTECTED] wrote: Not a terribly useful comment but was just glancing through the new look and noticed a typo: http://sqlite.hwaci.com/about.html We believe that General Electric uses SQLite in some product or another because they twice wrote the to SQLite developers .. wrote the to Thanks, Mark. I am going to go through and clean all that up. I'm focused on the layout right now, though. I put up 4 variations. Please, everyone, offer your opinions: (1) http://sqlite.hwaci.com/v1/ No CSS of any kind. (2) http://sqlite.hwaci.com/v2/ CSS menus with rounded corners (3) http://sqlite.hwaci.com/v3/ CSS menus with square corners (4) http://sqlite.hwaci.com/v4/ CSS font specification only (2) and (3) do not work on IE6. (1) has ugly fonts, I am told. That leaves me with (4). I suppose we could go with (4) now and change it later -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] unresolved external symbol _TryEnterCriticalSection
Hi, I'm trying to upgrade to the latest version of sqlite (3.5.1) for windows. Compiling the sqlite library is fine, but linking to it I always get one unresolved symbol: unresolved external symbol _TryEnterCriticalSection There seems to be one entry on it in the archives but I don't understand what the solution is? I'm already linking to kernel32.lib, Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] unresolved external symbol _TryEnterCriticalSection
Thanks guys. On 10/20/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Mark Wyszomierski [EMAIL PROTECTED] wrote: Hi, I'm trying to upgrade to the latest version of sqlite (3.5.1) for windows. Compiling the sqlite library is fine, but linking to it I always get one unresolved symbol: unresolved external symbol _TryEnterCriticalSection http://www.sqlite.org/cvstrac/tktview?tn=2685 http://www.sqlite.org/cvstrac/chngview?cn=4471 -- D. Richard Hipp [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite ODBC?
Hi, Are there are ODBC drivers out there for sqlite on win32? I'm already using an ODBC library and wanted to see if I could get around rewriting everything using the native sqlite API. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: sqlite ODBC?
I suppose this is it: http://www.ch-werner.de/sqliteodbc/ is this the official driver to use though? The readme file says: The driver is usable but may contain lots of memory leaks and all other kinds of bugs. Use it on your own risk. Anyone have any bad experiences with it? Thanks, Mark On 10/3/07, Mark Wyszomierski [EMAIL PROTECTED] wrote: Hi, Are there are ODBC drivers out there for sqlite on win32? I'm already using an ODBC library and wanted to see if I could get around rewriting everything using the native sqlite API. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] where is SQLITE_OPEN_READONLY defined?
Hi, I wanted to open a sqlite database read-only - where is: SQLITE_OPEN_READONLY defined? Thanks - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: where is SQLITE_OPEN_READONLY defined?
Ah shoot didn't realize that, thanks. Do you happen to know by any chance if opening in read-only mode improves performance at all? Or is it really just a safe-guard feature. Thanks, Mark On 9/23/07, Igor Tandetnik [EMAIL PROTECTED] wrote: Mark Wyszomierski [EMAIL PROTECTED] wrote: I wanted to open a sqlite database read-only - where is: SQLITE_OPEN_READONLY defined? It's new in SQLite v3.5 . Which version are you using? Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] dates and times in sqlite
Hi, I've made a text field called timestamp which has dates in the form: -MM-DD HH:MM:SS I want to test if the day portion is a Tuesday for example - something like: SELECT * FROM my_table WHERE DAY(timestamp) = TUESDAY is something like that at all possible? I've looked at the extended functions here: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions but am unsure of how to take advantage of them. Thank you, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: dates and times in sqlite
Oh I forgot to put quotes around the result, thanks Igor,
Mark
On 9/20/07, Igor Tandetnik [EMAIL PROTECTED] wrote:
Mark Wyszomierski [EMAIL PROTECTED]
wrote:
I've made a text field called timestamp which has dates in the form:
-MM-DD HH:MM:SS
I want to test if the day portion is a Tuesday for example -
something like:
SELECT * FROM my_table WHERE DAY(timestamp) = TUESDAY
select * from my_table
where strftime('%w', timestamp) = '2';
Igor Tandetnik
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] remote access to sqlite db?
Hi, Does sqlite offer the ability to connect to a sqlite db file on a remote machine? I've been using it locally for awhile and it's great. Wanted to see if it could be used remotely for some simple tasks. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] starting with unicode
Hi Nikki,
Why do we want to use sqlite3_reset() in the for loop? If I leave it
in, the loop keeps running forever since I guess it really does keep
resetting the sql statement. Do we need it though inside the while
loop when SQLITE_BUSY is returned though? Is that the proper way to
try again after being busy, or will it again reset the entire
statement and throw back to an infiinite loop?
Thanks,
Mark
On 8/15/06, Nikki Locke [EMAIL PROTECTED] wrote:
Mark Wyszomierski wrote:
strSql.Format(_T(SELECT * FROM test));
sqlite3_stmt *pStmt;
const char *pszTailPointer;
int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
pStmt, pszTailPointer);
if (nRetVal != SQLITE_OK) {
TRACE(prepare fails!! [%i] [%s]\n, nRetVal, sqlite3_errmsg(db));
return false;
}
nRetVal = sqlite3_step(pStmt);
while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
Sleep(100);
// Try again.
nRetVal = sqlite3_step(pStmt);
// How do I get the information out of this returned record?
// By the way, why would we want a reset() in here?
// sqlite3_reset(pStmt);
}
Your error checking is all wrong. Try something like this...
int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(), pStmt,
pszTailPointer);
if (nRetVal != SQLITE_OK) {
TRACE(prepare fails!! [%i] [%s]\n, nRetVal, sqlite3_errmsg(db));
return false;
}
for ( ; ; ) {
while((nRetVal = sqlite3_step(pStmt)) == SQLITE_BUSY) {
Sleep(100);
sqlite3_reset(pStmt); // Not sure if this is needed
}
if (nRetVal != SQLITE_ROW)
break; // No more rows, or an error
// How do I get the information out of this returned record?
sqlite3_reset(pStmt); // Ready for the next sqlite3_step
}
--
Nikki Locke, Trumphurst Ltd. PC Unix consultancy programming
http://www.trumphurst.com/
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] starting with unicode
Hi Cory,
Alright I gave it a shot from the docs but I'm not handling the
prepare statement correctly. I'm trying the ASCI version first. The
prepare statement returns an error. Here is the code snippet I'm
trying:
strSql.Format(CREATE TABLE test (something TEXT, something_else TEXT,
primary key(something)));
sqlite3_stmt *pStmt;
const char *pszTailPointer;
int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
pStmt, pszTailPointer);
while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
Sleep(100);
// Try again.
nRetVal = sqlite3_step(pStmt);
TRACE(ret val was [%i]\n, nRetVal);
}
switch (nRetVal) {
case SQLITE_DONE:
TRACE(Done ok\n);
break;
case SQLITE_ERROR:
TRACE(ERROR\n);
break;
case SQLITE_MISUSE:
TRACE(MISUSE\n);
break;
default:
break;
}
sqlite3_finalize(pStmt);
return true;
Any hints?
Thanks,
Mark
On 8/13/06, Cory Nelson [EMAIL PROTECTED] wrote:
On 8/13/06, Mark Wyszomierski [EMAIL PROTECTED] wrote:
Hi,
I have been using sqlite on windows for a few months, it is great. I
need to switch over to unicode support now though, and I am confused
how to do this with sqlite.
1) First, when I compiled the original sqlite project, I have the
character set to MBCS. Should I switch this to Unicode and recompile?
I think SQLite explicitly calls CreateFileW but I could be wrong.
Might as well compile as Unicode anyway.
2) I have been using sqlite3_exec() to execute my sql statements, but
I see that there is no sqlite3_exec16() equivalent to take a unicode
string. I think I'm supposed to use sqlite3_prepare16() but I have no
idea what the last two parameters of that function are?
Check the docs, they explain how to use prepared statements.
3) To escape my sql statements I was using sqlite3_mprintf() - is
there a unicode equivalent?
With prepared statements you put placeholders like ? into your sql
and bind data to the placeholders - no escaping required.
Thanks for any information,
Mark
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Cory Nelson
http://www.int64.org
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] starting with unicode
John, Cory, thank you very much. I got execute plain statements ok by
modifying my earlier posting a bit. I was able to create a table using
the prepare statement.
Previously I was using sqlite3_exec() to execute my statements and I
could pass it a callback function which I could use to fetch data for
SELECT statements. How do we do that now with the prepare() and step()
statements? Just some pseudocode like before would be fine. Here in my
test table I have two dummy records and the while loop correctly
iterates over both of them - but how to get the values in each row?
// Table test looks like:
// something | something_else
// --
// hello | there
// bye| guy
strSql.Format(_T(SELECT * FROM test));
sqlite3_stmt *pStmt;
const char *pszTailPointer;
int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
pStmt, pszTailPointer);
if (nRetVal != SQLITE_OK) {
TRACE(prepare fails!! [%i] [%s]\n, nRetVal, sqlite3_errmsg(db));
return false;
}
nRetVal = sqlite3_step(pStmt);
while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
Sleep(100);
// Try again.
nRetVal = sqlite3_step(pStmt);
// How do I get the information out of this returned record?
// By the way, why would we want a reset() in here?
// sqlite3_reset(pStmt);
}
switch (nRetVal) {
case SQLITE_DONE:
TRACE(Done ok\n);
break;
case SQLITE_ERROR:
TRACE(ERROR\n);
break;
case SQLITE_MISUSE:
TRACE(MISUSE\n);
break;
default:
break;
}
sqlite3_finalize(pStmt);
Thanks,
Mark
On 8/14/06, John Stanton [EMAIL PROTECTED] wrote:
Mark Wyszomierski wrote:
Hi Cory,
Alright I gave it a shot from the docs but I'm not handling the
prepare statement correctly. I'm trying the ASCI version first. The
prepare statement returns an error. Here is the code snippet I'm
trying:
strSql.Format(CREATE TABLE test (something TEXT, something_else TEXT,
primary key(something)));
sqlite3_stmt *pStmt;
const char *pszTailPointer;
int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
pStmt, pszTailPointer);
while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
Sleep(100);
// Try again.
nRetVal = sqlite3_step(pStmt);
TRACE(ret val was [%i]\n, nRetVal);
}
switch (nRetVal) {
case SQLITE_DONE:
TRACE(Done ok\n);
break;
case SQLITE_ERROR:
TRACE(ERROR\n);
break;
case SQLITE_MISUSE:
TRACE(MISUSE\n);
break;
default:
break;
}
sqlite3_finalize(pStmt);
return true;
Any hints?
Thanks,
Mark
On 8/13/06, Cory Nelson [EMAIL PROTECTED] wrote:
On 8/13/06, Mark Wyszomierski [EMAIL PROTECTED] wrote:
Hi,
I have been using sqlite on windows for a few months, it is great. I
need to switch over to unicode support now though, and I am confused
how to do this with sqlite.
1) First, when I compiled the original sqlite project, I have the
character set to MBCS. Should I switch this to Unicode and recompile?
I think SQLite explicitly calls CreateFileW but I could be wrong.
Might as well compile as Unicode anyway.
2) I have been using sqlite3_exec() to execute my sql statements, but
I see that there is no sqlite3_exec16() equivalent to take a unicode
string. I think I'm supposed to use sqlite3_prepare16() but I have no
idea what the last two parameters of that function are?
Check the docs, they explain how to use prepared statements.
3) To escape my sql statements I was using sqlite3_mprintf() - is
there a unicode equivalent?
With prepared statements you put placeholders like ? into your sql
and bind data to the placeholders - no escaping required.
Thanks for any information,
Mark
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Cory Nelson
http://www.int64.org
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
A prepare just compiles the statment, and does not get busy. You do
it to get ready for your execution loop.
You execute the compiled statement with step and then call reset to
check for errors and intialize the compiled statement ready for the next
step. At the end of your processing you finalize the statement to
tidy
Re: [sqlite] starting with unicode
I suppose this is correct:
strSql.Format(_T(SELECT * FROM test));
char szSomething[500];
int nTest = sqlite3_bind_text(pStmt, 1, szSomething, 500, SQLITE_STATIC);
if (nTest != SQLITE_OK) {
TRACE(sqlite3_bind_text fails!! [%i] [%s]\n, nTest, sqlite3_errmsg(db));
}
but can I use the string SELECT * FROM test, if not how do I use
those question marks or AAA variable identifiers in the string itself
to achieve binding?
Thanks,
Mark
On 8/14/06, Mark Wyszomierski [EMAIL PROTECTED] wrote:
John, Cory, thank you very much. I got execute plain statements ok by
modifying my earlier posting a bit. I was able to create a table using
the prepare statement.
Previously I was using sqlite3_exec() to execute my statements and I
could pass it a callback function which I could use to fetch data for
SELECT statements. How do we do that now with the prepare() and step()
statements? Just some pseudocode like before would be fine. Here in my
test table I have two dummy records and the while loop correctly
iterates over both of them - but how to get the values in each row?
// Table test looks like:
// something | something_else
// --
// hello | there
// bye| guy
strSql.Format(_T(SELECT * FROM test));
sqlite3_stmt *pStmt;
const char *pszTailPointer;
int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
pStmt, pszTailPointer);
if (nRetVal != SQLITE_OK) {
TRACE(prepare fails!! [%i] [%s]\n, nRetVal, sqlite3_errmsg(db));
return false;
}
nRetVal = sqlite3_step(pStmt);
while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
Sleep(100);
// Try again.
nRetVal = sqlite3_step(pStmt);
// How do I get the information out of this returned record?
// By the way, why would we want a reset() in here?
// sqlite3_reset(pStmt);
}
switch (nRetVal) {
case SQLITE_DONE:
TRACE(Done ok\n);
break;
case SQLITE_ERROR:
TRACE(ERROR\n);
break;
case SQLITE_MISUSE:
TRACE(MISUSE\n);
break;
default:
break;
}
sqlite3_finalize(pStmt);
Thanks,
Mark
On 8/14/06, John Stanton [EMAIL PROTECTED] wrote:
Mark Wyszomierski wrote:
Hi Cory,
Alright I gave it a shot from the docs but I'm not handling the
prepare statement correctly. I'm trying the ASCI version first. The
prepare statement returns an error. Here is the code snippet I'm
trying:
strSql.Format(CREATE TABLE test (something TEXT, something_else TEXT,
primary key(something)));
sqlite3_stmt *pStmt;
const char *pszTailPointer;
int nRetVal = sqlite3_prepare(db, strSql, strSql.GetLength(),
pStmt, pszTailPointer);
while (nRetVal == SQLITE_BUSY || nRetVal == SQLITE_ROW) {
Sleep(100);
// Try again.
nRetVal = sqlite3_step(pStmt);
TRACE(ret val was [%i]\n, nRetVal);
}
switch (nRetVal) {
case SQLITE_DONE:
TRACE(Done ok\n);
break;
case SQLITE_ERROR:
TRACE(ERROR\n);
break;
case SQLITE_MISUSE:
TRACE(MISUSE\n);
break;
default:
break;
}
sqlite3_finalize(pStmt);
return true;
Any hints?
Thanks,
Mark
On 8/13/06, Cory Nelson [EMAIL PROTECTED] wrote:
On 8/13/06, Mark Wyszomierski [EMAIL PROTECTED] wrote:
Hi,
I have been using sqlite on windows for a few months, it is great. I
need to switch over to unicode support now though, and I am confused
how to do this with sqlite.
1) First, when I compiled the original sqlite project, I have the
character set to MBCS. Should I switch this to Unicode and recompile?
I think SQLite explicitly calls CreateFileW but I could be wrong.
Might as well compile as Unicode anyway.
2) I have been using sqlite3_exec() to execute my sql statements, but
I see that there is no sqlite3_exec16() equivalent to take a unicode
string. I think I'm supposed to use sqlite3_prepare16() but I have no
idea what the last two parameters of that function are?
Check the docs, they explain how to use prepared statements.
3) To escape my sql statements I was using sqlite3_mprintf() - is
there a unicode equivalent?
With prepared statements you put placeholders like ? into your sql
and bind data to the placeholders - no escaping required.
Thanks for any information,
Mark
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
Cory Nelson
http://www.int64.org
-
To unsubscribe, send email to [EMAIL PROTECTED
[sqlite] starting with unicode
Hi, I have been using sqlite on windows for a few months, it is great. I need to switch over to unicode support now though, and I am confused how to do this with sqlite. 1) First, when I compiled the original sqlite project, I have the character set to MBCS. Should I switch this to Unicode and recompile? 2) I have been using sqlite3_exec() to execute my sql statements, but I see that there is no sqlite3_exec16() equivalent to take a unicode string. I think I'm supposed to use sqlite3_prepare16() but I have no idea what the last two parameters of that function are? 3) To escape my sql statements I was using sqlite3_mprintf() - is there a unicode equivalent? Thanks for any information, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] translate time comparison statement
Hi all,
I'm trying to translate some MySQL to sqlite. Ran into a problem with this
time comparison statement. I translated as best I could, it seems to work,
is this alright? (I'm trying to see if any records are more than 7 days old,
based on the value of 'arrival_date' field):
Original:
SELECT school_name from schools WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY)
arrival_date
To sqlite:
SELECT school_name from schools WHERE julianday('now') -
julianday(arrival_date) 7
And an example of the date value stored in the 'arrival_date' field looks
like:
'2006-01-10 16:14:19'
Thanks,
Mark
Re: [sqlite] translate time comparison statement
Cool thanks,
Mark
On 1/11/06, Kurt Welgehausen [EMAIL PROTECTED] wrote:
You may want
WHERE julianday(date('now')) - julianday(date(arrival_date)) 7
so that time of day isn't part of the comparison; otherwise,
you're correct.
Regards
[sqlite] specifying field type, any benefit?
Hi all,
I switched to sqlite from mysql awhile ago, I maintained the field types
in my sqlite implementation such as:
create table students (first_name TEXT, age INTEGER);
I'm just wondering if there is any point to specifying the field type as if
I try adding a string type into the age field, it seems to be accepted ok:
insert into students values('hello');
Does sqlite have any problem regarding setting a field defined as INTEGER
from a text string (any limits etc?), are there any performance gains to be
had with specifying the field type?
Thanks,
Mark
Re: [sqlite] specifying field type, any benefit?
Got it, thank you very much all,
Mark
On 1/4/06, Henry Miller [EMAIL PROTECTED] wrote:
On Wednesday 04 January 2006 02:54 pm, Mark Wyszomierski wrote:
Hi all,
I switched to sqlite from mysql awhile ago, I maintained the field types
in my sqlite implementation such as:
create table students (first_name TEXT, age INTEGER);
I'm just wondering if there is any point to specifying the field type as
if
I try adding a string type into the age field, it seems to be accepted
ok:
insert into students values('hello');
Does sqlite have any problem regarding setting a field defined as
INTEGER
from a text string (any limits etc?), are there any performance gains to
be
had with specifying the field type?
sqlite does not care about types. You can insert BLOBs into INTEGER
fields
if it makes you happy. sqlite will not care.
I recommend you place them in your definitions anyway, for two reasons.
First, you might want to switch to a different database latter. Second,
it
is sometimes helpful to tell your successors what you intend a field to
be.
Even though sqlite doesn't care, it is a good idea to be more strict
yourself.
As the other response said, sqlite used NULL for the unspecified
parameters.
[sqlite] primary, secondary keys
Hi all, Does sqlite allow multiple keys? When I created a table I did: CREATE TABLE test (name, address, fav_color, primary key(name, address)) Is that valid? I would like to not have any duplicate name + address entries. There was no error reported but later I tried modifying the table in SQLite Database Browser and it complained that the table has multiple primary fields. Thanks, Mark
Re: [sqlite] primary, secondary keys
Thanks Dennis On 9/30/05, Dennis Cote [EMAIL PROTECTED] wrote: Mark Wyszomierski wrote: Hi all, Does sqlite allow multiple keys? When I created a table I did: CREATE TABLE test (name, address, fav_color, primary key(name, address)) Is that valid? I would like to not have any duplicate name + address entries. There was no error reported but later I tried modifying the table in SQLite Database Browser and it complained that the table has multiple primary fields. Thanks, Mark Mark, Yes, that is valid SQL. The error message means that you are trying to insert or update a row so that its primary key (the combination of name and address) is the same as some other row that already exists in the table. SQL requires that the primary key of each row be unique. HTH Dennis Cote
[sqlite] tracing memroy leak
Hi all,
Is there anyway to track down memory leaks with using sqlite? I have 2 apps
sharing one database file. Application #1 only ever uses a single thread, so
I just open the db struct at startup, and close it at shutdown. Closing down
returns SQLITE_OK. My second app opens a handle to the database at startup,
closes it at shutdown. However, it sometimes spawns threads, and at the
beginning of each thread, I get a new handle to the database, and close it
when the thread is killed. So I'm following the rule of not sharing handles
between threads. Now when I close app #1, and app #2 has accessed the
database in a multithreaded way, I get this dump:
Detected memory leaks!
Dumping objects -
{25360} normal block at 0x05E13788, 19 bytes long.
Data: database is lock 64 61 74 61 62 61 73 65 20 69 73 20 6C 6F 63 6B
{25326} normal block at 0x05E13738, 19 bytes long.
Data: database is lock 64 61 74 61 62 61 73 65 20 69 73 20 6C 6F 63 6B
Is there anyway to tell how this is happening? Otherwise cleanup is working
fine. I don't have the message database is lock anywhere in my app, so I
guess this must be from sqlite.
Thanks,
Mark
Re: [sqlite] tracing memroy leak
I traced this error down a bit, it only appears when using windows' PostMessage() to communicate between applications. Both applications have their own database handles for sure. When one app gets some data, it simply uses PostMessage() to inform the other app that some data has been received. It is at this point that if I try accessing the database in this message handler, the memory leak occurrs. I even took the additional precaution of creating an entirely new database handle inside the message handler itself. If I replace PostMessage() with SendMessage(), no leak occurrs. However, replacing PostMessage() with SendMessage() would be a huge penalty. Any ideas why there is a problem here? Thanks, Mark On 9/15/05, Reid Thompson [EMAIL PROTECTED] wrote: Jay Sprenkle wrote: The premier analysis tool that I know about is valgrind: http://valgrind.org/info/about.html splint may be of interest also http://www.splint.org/ reid
Re: [sqlite] tracing memroy leak
@ Jay:
So those two functions are in separate processes, and in the start of each
of them, I am creating a new database handle, which both open with return
value of 0, and close with return value of 0 - they aren't actually sharing
anything except the database file itself, but for which they each have their
own unique handles.
@ Dennis:
Yeah my LPARAM and WPARAM are just 0, I know sending pointers through could
be disastrous. So I checked those are both just 0 for now. The posted
message contains no other data, it's just meant to be a signal to my app to
go ahead and do some stuff. I'm running my application through VC++ .NET, so
when I exit the application, it just dumps all those memory leaks. Of course
the memory leaks only happen in this post message instance, not elsewhere
where I make heavy use of the database, and threads! My dumps come out in
trace statements in the debugger. Maybe I could get more info if it told me
where the leaks were occurring, specifically in the sqlite code itself, so
I'll try setting those Crt flags to hopefully get more information. The
other thing I did was close the database handle in app #2 before it uses
PostMessage() - then there are no memory leaks. But opening and closing the
database everytime I have to post a message may be too burdensome,
Thank you!
On 9/15/05, Dennis Jenkins [EMAIL PROTECTED] wrote:
Mark Wyszomierski wrote:
app1:
SomeThread()
{
sqlite3 *db = opendatabase();
writesomestuff();
PostMessage(my_other_app, 0, 0);
closedatabase(db);
return 0;
}
app2:
MessageHandlerInMainThread()
{
sqlite3 *db = opendatabase();
ReadDatabaseStuff(db);
closedatabase();
return 0;
}
Thanks!
Mark
hmmm... that should work. Since in your message LPARAM and WPARAM are
0, I assume that they convey no pointers to memory buffers. Does the
posted message contain any data other than the message id?
(From memory) If you use SendMessage to send a message to a window
owned by a different process, then it will behave like PostMessage, but
block until the other process has dispatched your message. Let me check
the MSDN real quick...
Oops. It looks like what I posted earlier was in error. From the Feb
2003 platform SDK on SendMessage:
Applications that need to communicate using HWND_BROADCAST should
use the RegisterWindowMessage function to obtain a unique message
for inter-application communication.
The system only does marshalling for system messages (those in the
range 0 to WM_USER). To send other messages (those above *WM_USER*)
to another process, you must do custom marshalling.
If the specified window was created by the calling thread, the
window procedure is called immediately as a subroutine. If the
specified window was created by a different thread, the system
switches to that thread and calls the appropriate window procedure.
Messages sent between threads are processed only when the receiving
thread executes message retrieval code. The sending thread is
blocked until the receiving thread processes the message. However,
the sending thread will process incoming nonqueued messages while
waiting for its message to be processed. To prevent this, use
SendMessageTimeout with SMTO_BLOCK set. For more information on
nonqueued messages, see Nonqueued Messages.
I'm at a loss right now. I supose that I'd need to learn more about
your code.
What mechanism are you using to track memory leaks? Many years ago I
used memcheck. It worked great, but the company (Stratosware) seems
to be out of business now.
I call this function first thing in my WinMain() to turn on the
Microsoft run time library memory checking stuff:
void SetDebugging(void)
{
// Turn on extream heap memory checking. This will slow down the
system considerably.
_CrtSetDbgFlag(_CRTDBG_LEAK_CHECK_DF|_CRTDBG_ALLOC_MEM_DF|_CRTDBG_CHECK_ALWAYS_DF);
_CrtSetReportMode( _CRT_ASSERT, _CRTDBG_MODE_FILE );
_CrtSetReportMode( _CRT_WARN, _CRTDBG_MODE_FILE );
_CrtSetReportMode( _CRT_ERROR, _CRTDBG_MODE_FILE );
_CrtSetReportFile( _CRT_ASSERT, _CRTDBG_FILE_STDERR );
_CrtSetReportFile( _CRT_WARN, _CRTDBG_FILE_STDERR );
_CrtSetReportFile( _CRT_ERROR, _CRTDBG_FILE_STDERR );
}
I also redirect stdout and stderr to a console created with
AllocConsole().
I assume that you do something similar?
[sqlite] create database file at runtime
Hi everyone, Two quick questions: 1) Is there anything special the supplied sqlite3.exe does to create a database file. I want to programatically create a database file at runtime, so can I just create an empty xxx.db file, and go ahead with create table statements to populate it? The documentation says to create a sqlite database you should use the supplied app (sqlite3.exe). Is just doing that myself ok? 2) While creating tables, I've been specifying the data type after the field name: create table test(field_1 integer, field_2 text); The only advantage of this is that the database could possible store the data in a more compact form, correct? If that's not a concern, leaving it all as text would not make a difference? Thanks, Mark
[sqlite] how to store date/time in sqlite?
Hi all, Moving from a mysql database to sqlite. I had some date/time fields in my mysql database. I would just populate them using the now() function. How could I achieve the same in my new sqlite database? Thanks! Mark
Re: [sqlite] how to store date/time in sqlite?
Ah excellent, thanks Jay, Mark On 9/13/05, Jay Sprenkle [EMAIL PROTECTED] wrote: See the wiki section of the documentation on the web site. There's a page devoted to this. On 9/13/05, Mark Wyszomierski [EMAIL PROTECTED] wrote: Hi all, Moving from a mysql database to sqlite. I had some date/time fields in my mysql database. I would just populate them using the now() function. How could I achieve the same in my new sqlite database? Thanks! Mark -- --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
