Re: [sqlite] datetime column type, how-to

[Jeff Thompson]

[EMAIL PROTECTED] wrote:
SQLiters:
What would be the best method for creating a Table with one column in the
format 03-Mar-2005 16:05:30?
i.e. dd-mmm-yy hh:mm:ss
 

I would store the date in UNIX timestamp format.
I will need to retrieve by date/time in proper order.  i.e. "select * from
'table' ORDER BY datetime-field".
The format you posted above won't sort correctly. Storing the timestamp 
in UNIX timestamp format would and you can format however you wish using 
the strftime sqlite function or your operating system's strftime function.

There's a date/time page at the sqlite wiki 
.

Hope that helps,
--
jthomps

Re: [sqlite] Windows Unicode Support

[Jeff Thompson]

D. Richard Hipp wrote:
On Sun, 2005-02-13 at 16:18 -0600, Jeff Thompson wrote:
 

sqlite3_exec
sqlite3_mprintf
I was wondering why there are no unicode versions 
of these (and possibly others) functions?

   

sqlite3_exec is implemented in using other published
APIs.  It is a convenience wrapper provided for historical
compatibility.  If you want a UTF-16 version, make a copy
of the UTF-8 version and adjust as necessary.
 

Understood... I did implement my own "exec" function in my application 
and it works fine. I was just wondering why no sqlite3_exec16 function, 
but as you pointed out, it's no big deal to handle on the app side.

I don't know how to make sqlite3_mprintf() UTF-16 aware.
 

Looking at printf.c, I'd tend to agree...
Thanks for the info.
--
jthomps

[sqlite] Windows Unicode Support

[Jeff Thompson]

Hi, I've recently gone through the work to convert my windows 
application to unicode. A ran across a couple of the sqlite3 API calls 
that don't seem to have unicode versions, namely:

sqlite3_exec
sqlite3_mprintf
I switched all of my sqlite3_mprintf/sqlite3_exec calls to 
sqlite3_prepare16/sqlite3_step calls and use sqlite3_bind_text16 to bind 
parameters instead of using the '%q' quoted string support provided by 
sqlite3_mprintf.

While this works fine, I was wondering why there are no unicode versions 
of these (and possibly others) functions?

Thanks,
jthomps

Re: [sqlite] sqlite thread safe builds

[Jeff Thompson]

Christian Smith wrote:
You must disclaim copyright on any patches you do to be even considered
for inclusion into SQLite. Check out:
http://www.sqlite.org/copyright.html
 

Thanks for the info Christian, sorry I didn't notice the requirement 
when submitting the original patch. :)

Here's the patch again, along with my copyright disclaimer:
/The author or authors of this code dedicate any and all copyright 
interest in this code to the public domain. We make this dedication for 
the benefit of the public at large and to the detriment of our heirs and 
successors. We intend this dedication to be an overt act of 
relinquishment in perpetuity of all present and future rights this code 
under copyright law.

/--- 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
/
/Please consider this patch for inclusion into sqlite3. The "pragma 
message" directives are completely optional and can be removed if you 
wish to prevent the display of the thread safe status of sqlite3 while 
being built with MSVC under windows.

Thanks,
--
Jeff Thompson
/
/

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

[Jeff Thompson]

Kervin L. Pierre wrote:
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.
 

Kevin, no problem, glad I could help... Don't ask me how I learned 
this...  :) 

--
Jeff Thompson
*** gmail accounts available, email me if you want one

Re: [sqlite] compiled features at runtime?

[Jeff Thompson]

On Fri, 04 Feb 2005 16:54:31 -0500, Kervin L. Pierre
<[EMAIL PROTECTED] <mailto:[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]

[sqlite] sqlite thread safe builds

[Jeff Thompson]

Hi, I'm a sqlite 3.0.8 user on windows only to date. I am using sqlite
in a multi-threaded application and have read and understand the
requirement to define THREADSAFE=1 in order to ensure that sqlite is
built correctly for multi-threaded execution.

Would the developer(s) of sqlite consider the following patch, which i
believe, automatically builds sqlite with multi-threading support if
the MSVC build target is a multi-threaded build?

--- 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

The "pragma message" directives are optional, of course. I added them
to ensure that my build output would tell me whether sqlite was being
built correctly for my project.

Since the patch checks for the _MSC_VER macro, it would only affect
windows builds, using Microsoft's compilers. There may be similar
methods for Borland, Intel, etc., but I'm afraid that I'm only
familiar with MS's compilers.

Thanks,

-- 
Jeff Thompson
[EMAIL PROTECTED]

Re: [sqlite] bogus output for strftime('%s', 'now')

[Jeff Thompson]

On Tue, 1 Feb 2005 06:51:49 -0800 (PST), teoh <[EMAIL PROTECTED]> wrote:
>
> sqlite3::reader reader=con.executereader("select *
> from each_transaction;");
> while(reader.read())
> {cout << reader.getcolname(0) << ": " <<
> reader.getstring(0) << endl;  }
> 
> I get output like this:
> 
> datetime: á " <- invalid
> 

This is a guess since I'm not familiar with sql3_plus, but it looks
like you may be printing the pointer to the character string instead
of the string itself... If reader.getstring(0) returns a std::string,
try the following:

cout << reader.getcolname(0) << ": " << reader.getstring(0).c_str() << endl;

-- 
Jeff Thompson
[EMAIL PROTECTED]

Re: [sqlite] wchar for sqlite prolem

[Jeff Thompson]

 sqlite3t_create_functionsqlite3_create_function
#define sqlite3t_create_function16  sqlite3_create_function16
#define sqlite3t_data_count sqlite3_data_count
#define sqlite3t_errcodesqlite3_errcode
#define sqlite3t_errmsg sqlite3_errmsg
#define sqlite3t_errmsg16   sqlite3_errmsg16
#define sqlite3t_exec   sqlite3_exec
#define sqlite3t_finalize   sqlite3_finalize
#define sqlite3t_free   sqlite3_free
#define sqlite3t_free_table sqlite3_free_table
#define sqlite3t_get_table  sqlite3_get_table
#define sqlite3t_interrupt  sqlite3_interrupt
#define sqlite3t_last_insert_rowid  sqlite3_last_insert_rowid
#define sqlite3t_libversion sqlite3_libversion
#define sqlite3t_mprintfsqlite3_mprintf
#define sqlite3t_open   sqlite3_open
#define sqlite3t_open16 sqlite3_open16
#define sqlite3t_preparesqlite3_prepare
#define sqlite3t_prepare16  sqlite3_prepare16
#define sqlite3t_progress_handler   sqlite3_progress_handler
#define sqlite3t_reset  sqlite3_reset
#define sqlite3t_result_blobsqlite3_result_blob
#define sqlite3t_result_double  sqlite3_result_double
#define sqlite3t_result_error   sqlite3_result_error
#define sqlite3t_result_error16 sqlite3_result_error16
#define sqlite3t_result_int sqlite3_result_int
#define sqlite3t_result_int64   sqlite3_result_int64
#define sqlite3t_result_nullsqlite3_result_null
#define sqlite3t_result_textsqlite3_result_text
#define sqlite3t_result_text16  sqlite3_result_text16
#define sqlite3t_result_text16besqlite3_result_text16be
#define sqlite3t_result_text16lesqlite3_result_text16le
#define sqlite3t_result_value   sqlite3_result_value
#define sqlite3t_set_authorizer sqlite3_set_authorizer
#define sqlite3t_step   sqlite3_step
#define sqlite3t_total_changes  sqlite3_total_changes
#define sqlite3t_trace  sqlite3_trace
#define sqlite3t_user_data  sqlite3_user_data
#define sqlite3t_value_blob sqlite3_value_blob
#define sqlite3t_value_bytessqlite3_value_bytes
#define sqlite3t_value_bytes16  sqlite3_value_bytes16
#define sqlite3t_value_double   sqlite3_value_double
#define sqlite3t_value_int  sqlite3_value_int
#define sqlite3t_value_int64sqlite3_value_int64
#define sqlite3t_value_text sqlite3_value_text
#define sqlite3t_value_text16   sqlite3_value_text16
#define sqlite3t_value_text16be sqlite3_value_text16be
#define sqlite3t_value_text16le sqlite3_value_text16le
#define sqlite3t_value_type sqlite3_value_type
#define sqlite3t_vmprintf   sqlite3_vmprintf
#endif

Using this method, I store all strings in tstring's and use the
sqlite3t_* version of any sqlite function call and it works for both
unicode and mbcs builds in windows.

Hope that helps, or at least gives you some ideas how to approach the
"problem"... :)

--
jthomps

On Sat, 15 Jan 2005 10:51:03 -0400, LIN, Ming Q. <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I am new to sqlite, I am using it for my unicode project in windows
> platform. In windows, Unicode uses wchar variable, but it looks like the
> sqlite only take char for parameters. Do anyone know how to store unicode
> (such as chinese, japanese) in sqlite. I am using visual studio 2003 and
> windows 2000 OS.
> 
> Thank you in advance.
> 
> Ming
> 


-- 
Jeff Thompson
[EMAIL PROTECTED]