[sqlite] [SQLite] Variations in DLLs

[mswarm]

My simple Delphi test of libSQL worked fine, creating a database 
file, executing some SQL, returning the DLL version.

Used the same code in my app framework, however, and everything kept 
coming up dead: no file created, no error reply, no version message.

The difference turned out to be the DLL. I had installed the Aducom
controls, which turned out to be fatter and more complicated than 
I wanted. I desinstalled the controls, but left the DLL in place.

The DLL which worked (dated 10/11/04) turned out to be 218K, while 
the Aducom DLL--which did not work with the libSQL calls--is 382K. 

Any idea what the extra code (debug?) is doing and why didn't it 
work? (I presume the calls have been changed.)

Re: [sqlite] SQLite 3.0.8 dates

[Randall Fox]

On Fri, 4 Feb 2005 12:09:59 -0800 (PST), you wrote:

>I've been agonizing over the best way to store date/time
>values in my databases (perl will be the app platform).
>
>So, my question is, true or false:, if I want to use
>SQLite's date/time functions against field values, my only
>real option is to store -MM-DD HH:MM:SS time strings. 
>Any other value, whether MMDD, julian day number,
>MM/DD/, etc. (with or without the time portion), would
>leave me with having to do all format conversions in my app
>code.


Store as Julian, and then when you select out of SQLITE, have it do
the conversion for you.  You can also convert when inserting or
updating if you want.

I prefer the julian because it smaller, and I have no worries about
sorting by that column.  Anything fancy and  I use SQLITE to do the
format conversions, which is rare.

Randall Fox

Re: [sqlite] still struggling with "Database schema has changed" errors

[Randall Fox]

>Do mind sharing or explaining your changes?

No..  But I should have gave more information.  My changes were for
the TCL SQLITE code, and just affected the way TCL accesses the
library.

I was also getting the schema errors when I was using a single
threaded app.  I was opening two instances but only one was in a write
operation (or read for that matter ) at any given time.

What you should do is check the operation specifically for this error
and when you encounter it, try to re-execute the command repeatedly
until it goes through.  I found when I did get the error, retrying the
same operation was successful -- and that is the same advice I saw
earlier from the author of SQLITE.  Personally, I would probably put a
fail safe in the code to prevent an infinite loop.

In fixing my problem, I did not use that method.  Instead I passed in
the pointer for the SQLITE DB I already had open to the TCL
interpreter, and had TCL use the already opened DB pointer instead of
opening another instance.  This seems to take care of the problem, but
is a poor choice on a multi-threaded app.

If you are using TCL, let me know I can send you the code, which is
short and just adds another command line switch for the TCL sqlite3
instruction.

Randall Fox

Re: [sqlite] still struggling with "Database schema has changed" errors

[Kervin L. Pierre]

Wow that's a lot different than what I
read in some of the messages I read in
the archive.  I read that we're not
suppose to see SQLITE_SCHEMA errors
in SQLite3 unless something very wrong
happened.
Thanks,
Kervin
Dan Kennedy wrote:
There's a little bit written about SQLITE_SCHEMA errors here:
http://www.sqlite.org/faq.html#q17
--- "Kervin L. Pierre" <[EMAIL PROTECTED]> wrote:

Hi,
Thanks for your response.  I'm at wit's end
with this thing.
I do open the database multiple times, but
exactly once per thread.  I then keep the
sqlite3* on thread local storage and only
use that pointer on the thread it was created
on.  This is how I interpreted the documentation.
Is that correct?
I wrote a function to get a sqlite3* pointer.
Any method that requires SQLite API calls
GetDatabaseHandle() first...
EnterCriticalSection(_sqlite_cs);
databaseHandle = (sqlite3 *)TlsGetValue(otlkcon_tlsIndex);
if( databaseHandle == NULL )
{
// Thread does not have a db handle yet
sqlRes = sqlite3_open(databaseFilename, );
if (  sqlRes != SQLITE_OK )
{
// error code and exit...
}
sqlRes = TlsSetValue( otlkcon_tlsIndex, databaseHandle );
}
LeaveCriticalSection(_sqlite_cs);
http://cvs.sourceforge.net/viewcvs.py/otlkcon/otlkcon0/mstore/O_IProp.cpp?view=markup
Tls* functions provide thread local storage.
By my reckoning, this should garantee a strict
sqlite3*<->thread relationship.
Do mind sharing or explaining your changes?
Thanks,
Kervin
Randall Fox wrote:
On Thu, 03 Feb 2005 14:15:52 -0500, you wrote:

Hello,
I am using SQLite 3.0.8 in a Win32 threaded
environment.
I keep getting random "Database schema has changed"
errors even though I am using thread local
storage to make sure sqlite3_open() gets called
on each thread and a there is a sqlite3* per thread.
Has anyone had any luck with resolving SQLITE_SCHEMA
errors in a threaded environment?

How are you accessing the database?  Do you open it multiple times and
write to it?
I had the same problem with the schema errors.  I was opening the
database twice, and when I would create a table with one of the open
instances, the other would get the schema error the next time I
started a write operation. 

I did end up fixing it by rewriting part of the SQLITE code.
Randall Fox




		
__ 
Do you Yahoo!? 
Yahoo! Mail - Helps protect you from nasty viruses. 
http://promotions.yahoo.com/new_mail

Re: [sqlite] compiled features at runtime? - Re: [sqlite] still struggling with "Database schema has changed" errors

[Kervin L. Pierre]

Jeff Thompson wrote:
The way THREADSAFE works currently, is that it must be defined, but
also must have a value assigned to it. The compiler switch /D
THREADSAFE won't cause sqlite to have thread safe code included, whiel
/D THREADSAFE=1 will. Ensure you're using the latter format, or
That was at least part of my problem,
thanks a lot for that tip.
I was using '/D THREADSAFE' to turn on
multi-thread support.
-
Kervin

Re: [sqlite] still struggling with "Database schema has changed" errors

[Dan Kennedy]

There's a little bit written about SQLITE_SCHEMA errors here:

http://www.sqlite.org/faq.html#q17


--- "Kervin L. Pierre" <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> Thanks for your response.  I'm at wit's end
> with this thing.
> 
> I do open the database multiple times, but
> exactly once per thread.  I then keep the
> sqlite3* on thread local storage and only
> use that pointer on the thread it was created
> on.  This is how I interpreted the documentation.
> Is that correct?
> 
> I wrote a function to get a sqlite3* pointer.
> Any method that requires SQLite API calls
> GetDatabaseHandle() first...
> 
> EnterCriticalSection(_sqlite_cs);
>  databaseHandle = (sqlite3 *)TlsGetValue(otlkcon_tlsIndex);
>  if( databaseHandle == NULL )
>  {
>  // Thread does not have a db handle yet
>   sqlRes = sqlite3_open(databaseFilename, );
>   if (  sqlRes != SQLITE_OK )
>   {
>   // error code and exit...
>   }
>  sqlRes = TlsSetValue( otlkcon_tlsIndex, databaseHandle );
>  }
> LeaveCriticalSection(_sqlite_cs);
> 
> http://cvs.sourceforge.net/viewcvs.py/otlkcon/otlkcon0/mstore/O_IProp.cpp?view=markup
> 
> Tls* functions provide thread local storage.
> By my reckoning, this should garantee a strict
> sqlite3*<->thread relationship.
> 
> Do mind sharing or explaining your changes?
> 
> Thanks,
> Kervin
> 
> 
> Randall Fox wrote:
> > On Thu, 03 Feb 2005 14:15:52 -0500, you wrote:
> > 
> > 
> >>Hello,
> >>
> >>I am using SQLite 3.0.8 in a Win32 threaded
> >>environment.
> >>
> >>I keep getting random "Database schema has changed"
> >>errors even though I am using thread local
> >>storage to make sure sqlite3_open() gets called
> >>on each thread and a there is a sqlite3* per thread.
> >>
> >>Has anyone had any luck with resolving SQLITE_SCHEMA
> >>errors in a threaded environment?
> > 
> > 
> > How are you accessing the database?  Do you open it multiple times and
> > write to it?
> > 
> > I had the same problem with the schema errors.  I was opening the
> > database twice, and when I would create a table with one of the open
> > instances, the other would get the schema error the next time I
> > started a write operation. 
> > 
> > I did end up fixing it by rewriting part of the SQLITE code.
> > 
> > Randall Fox
> > 
> > 
> > 
> > 
> 
> 




__ 
Do you Yahoo!? 
Yahoo! Mail - Helps protect you from nasty viruses. 
http://promotions.yahoo.com/new_mail

Re: [sqlite] compiled features at runtime?

[Jeff Thompson]

On Fri, 04 Feb 2005 16:54:31 -0500, Kervin L. Pierre
<[EMAIL PROTECTED] > wrote:
 Thanks for your response Chris.
 Is there a way to find out what components are
 compiled in at runtime?
I don't believe the way the THREADSAFE define is handled today would
allow you to check anything at run-time. I sent the following patch to
the mailing list just yesterday. I got a goofy bounce message, but I
thought the message had been sent to the list, but nobody's commented,
so maybe it never made it... :)
Edit: This post bounced too, so I've unsubscibed my gmail address for 
the time being!!!

This patch will enable sqlite thread safe processing on windows with
the MSVC compiler, if the symbol _MT is defined. IMHO, this is a
better way to handle this, since it's automatic.
--- os_win.c17 Nov 2004 00:21:38 -  1.1
+++ os_win.c4 Feb 2005 04:10:02 -   1.2
@@ -21,6 +21,16 @@
/*
** Macros used to determine whether or not to use threads.
*/
+#if defined(_MSC_VER)
+#  if defined(_MT)
+#  define SQLITE_W32_THREADS 1
+#  pragma message("sqlite3 thread-safe support enabled.")
+#  else
+#  undef SQLITE_W32_THREADS
+#  pragma message("sqlite3 thread-safe support disabled.")
+#  endif
+#endif
+
#if defined(THREADSAFE) && THREADSAFE
# define SQLITE_W32_THREADS 1
#endif
 I built sqlite myself and I did turn on that
 macro, but I want to double check everything
 at runtime.  Maybe throw an error if
 multi-threading support is not available in
 the DLL.
The way THREADSAFE works currently, is that it must be defined, but
also must have a value assigned to it. The compiler switch /D
THREADSAFE won't cause sqlite to have thread safe code included, whiel
/D THREADSAFE=1 will. Ensure you're using the latter format, or
alternatively, us the patch I posted to have this detected
automatically when you're doing a multi-threaded build in MSVC vs.
single-threaded.
 Is there a way to detect multi-threading
 support at runtime?
You could do something like:
#if defined(THREADSAFE) && THREADSAFE
int g_isThreadSafe = 1;
#else
int g_isThreadSafe = 0;
#endif
and then check g_isThreadSafe in your code at run-time.
Hope that helps.
--
Jeff Thompson

Re: [sqlite] compiled features at runtime? - Re: [sqlite] still struggling with "Database schema has changed" errors

[Jeff Thompson]

On Fri, 04 Feb 2005 16:54:31 -0500, Kervin L. Pierre
<[EMAIL PROTECTED]> wrote:
> Thanks for your response Chris.
> 
> Is there a way to find out what components are
> compiled in at runtime?
> 

I don't believe the way the THREADSAFE define is handled today would
allow you to check anything at run-time. I sent the following patch to
the mailing list just yesterday. I got a goofy bounce message, but I
thought the message had been sent to the list, but nobody's commented,
so maybe it never made it... :)

This patch will enable sqlite thread safe processing on windows with
the MSVC compiler, if the symbol _MT is defined. IMHO, this is a
better way to handle this, since it's automatic.

--- os_win.c17 Nov 2004 00:21:38 -  1.1
+++ os_win.c4 Feb 2005 04:10:02 -   1.2
@@ -21,6 +21,16 @@
/*
** Macros used to determine whether or not to use threads.
*/
+#if defined(_MSC_VER)
+#  if defined(_MT)
+#  define SQLITE_W32_THREADS 1
+#  pragma message("sqlite3 thread-safe support enabled.")
+#  else
+#  undef SQLITE_W32_THREADS
+#  pragma message("sqlite3 thread-safe support disabled.")
+#  endif
+#endif
+
#if defined(THREADSAFE) && THREADSAFE
# define SQLITE_W32_THREADS 1
#endif

> I built sqlite myself and I did turn on that
> macro, but I want to double check everything
> at runtime.  Maybe throw an error if
> multi-threading support is not available in
> the DLL.
> 

The way THREADSAFE works currently, is that it must be defined, but
also must have a value assigned to it. The compiler switch /D
THREADSAFE won't cause sqlite to have thread safe code included, whiel
/D THREADSAFE=1 will. Ensure you're using the latter format, or
alternatively, us the patch I posted to have this detected
automatically when you're doing a multi-threaded build in MSVC vs.
single-threaded.

> Is there a way to detect multi-threading
> support at runtime?
> 

You could do something like:

#if defined(THREADSAFE) && THREADSAFE
int g_isThreadSafe = 1;
#else
int g_isThreadSafe = 0;
#endif

and then check g_isThreadSafe in your code at run-time.

Hope that helps.

-- 
Jeff Thompson
[EMAIL PROTECTED]

Re: [sqlite] still struggling with "Database schema has changed" errors

[Kervin L. Pierre]

Hi,
Thanks for your response.  I'm at wit's end
with this thing.
I do open the database multiple times, but
exactly once per thread.  I then keep the
sqlite3* on thread local storage and only
use that pointer on the thread it was created
on.  This is how I interpreted the documentation.
Is that correct?
I wrote a function to get a sqlite3* pointer.
Any method that requires SQLite API calls
GetDatabaseHandle() first...
EnterCriticalSection(_sqlite_cs);
databaseHandle = (sqlite3 *)TlsGetValue(otlkcon_tlsIndex);
if( databaseHandle == NULL )
{
// Thread does not have a db handle yet
sqlRes = sqlite3_open(databaseFilename, );
if (  sqlRes != SQLITE_OK )
{
// error code and exit...
}
sqlRes = TlsSetValue( otlkcon_tlsIndex, databaseHandle );
}
LeaveCriticalSection(_sqlite_cs);
http://cvs.sourceforge.net/viewcvs.py/otlkcon/otlkcon0/mstore/O_IProp.cpp?view=markup
Tls* functions provide thread local storage.
By my reckoning, this should garantee a strict
sqlite3*<->thread relationship.
Do mind sharing or explaining your changes?
Thanks,
Kervin
Randall Fox wrote:
On Thu, 03 Feb 2005 14:15:52 -0500, you wrote:

Hello,
I am using SQLite 3.0.8 in a Win32 threaded
environment.
I keep getting random "Database schema has changed"
errors even though I am using thread local
storage to make sure sqlite3_open() gets called
on each thread and a there is a sqlite3* per thread.
Has anyone had any luck with resolving SQLITE_SCHEMA
errors in a threaded environment?

How are you accessing the database?  Do you open it multiple times and
write to it?
I had the same problem with the schema errors.  I was opening the
database twice, and when I would create a table with one of the open
instances, the other would get the schema error the next time I
started a write operation. 

I did end up fixing it by rewriting part of the SQLITE code.
Randall Fox

Re: [sqlite] SQLite 3.0.8 dates

[Lawrence Chitty]

Clark Christensen wrote:
 I've been agonizing over the best way to store date/time values in my
 databases (perl will be the app platform).
 So, my question is, true or false:, if I want to use SQLite's
 date/time functions against field values, my only real option is to
 store -MM-DD HH:MM:SS time strings. Any other value, whether
 MMDD, julian day number, MM/DD/, etc. (with or without the
 time portion), would leave me with having to do all format
 conversions in my app code.

Take a closer look at the unixepoch time. This is the same 
representation that Perl uses, so you can either use sqlites built in 
functions to parse and format dates to unixepoch numbers, or if you need 
a bit more power, use Perls fuctions such as Date::Parse to parse the 
date to the correct value to be store in the database etc.

Lawrence
|

Re: [sqlite] SQLite 3.0.8 dates

[Doug Currie]

Friday, February 4, 2005, 3:09:59 PM, Clark Christensen wrote:

> So, my question is, true or false:, if I want to use
> SQLite's date/time functions against field values, my only
> real option is to store -MM-DD HH:MM:SS time strings. 
> Any other value, whether MMDD, julian day number,
> MM/DD/, etc. (with or without the time portion), would
> leave me with having to do all format conversions in my app
> code.

False.

You may store the date/time in any of these text formats:

 -MM-DD
 -MM-DD HH:MM
 -MM-DD HH:MM:SS
 -MM-DD HH:MM:SS.SSS
 HH:MM
 HH:MM:SS
 HH:MM:SS.SSS

or you may store the date/time as a number. The number can be a Julian
Day Number, a unixepoch, in local or gmt. There are many other numeric
options that require a bit of SQL pre-processing to be used by...

You can format the date in a variety of ways within SQL queries.

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

e

[sqlite] SQLite 3.0.8 shell error handling

[Clark Christensen]

Is there any error handling available in the SQLite 3.0.8
command shell?  Using sqlite3 test.db "read my.sql" from
the commandline, I don't see a way to embed an exit into
the script file in case something goes wrong with one step
or another.

I want to use the shell read a script like this from
my.sql:

   create temp table tmp_tbl (...);
   .import somefile.txt
   --exit here if the import fails
   ...

How do I test for the failed import, and exit?

Thanks!

 -Clark

[sqlite] SQLite 3.0.8 dates

[Clark Christensen]

I've been agonizing over the best way to store date/time
values in my databases (perl will be the app platform).

So, my question is, true or false:, if I want to use
SQLite's date/time functions against field values, my only
real option is to store -MM-DD HH:MM:SS time strings. 
Any other value, whether MMDD, julian day number,
MM/DD/, etc. (with or without the time portion), would
leave me with having to do all format conversions in my app
code.

TIA

 -Clark

[sqlite] insert

[Alain Duc]

I am currently using a sqlite 2.8 database with an SQLite.NET Provider (from 
http://www.eggheadcafe.com/articles/20040714.asp). When I insert data with my 
application (which use an connectionString specifying UTF8Encoding=True) 
everything goes well. But if I try to insert data with a script using ".read 
" from a commandline, special text like é,è,à... is not inserted 
in the correct format and therefore does not appear correctly in my application.

Does anybody have a solution?
Thanks
Alain

[sqlite] grabbing an in-memory database

[Will Leshner]

I wonder if it would be cool to be able to stream an in-memory
database to a single chunk of memory, and to have SQLite initialize an
in-memory database from a chunk of memory (perhaps, though, that isn't
possible). That would allow you to do things like suck the entire
contents of a file into memory and then use it as an in-memory
database. And when you are done, you could write the whole thing back
out to the file.

To be honest, I'm looking for workarounds for the problem of not being
able to use SQLite databases from shared volumes on Mac OS X.