Re: [sqlite] some help with datetime

[Igor Tandetnik]

"Gene Allen"  wrote in
message
news:!&!AAAuAKY/0q6/55xppdwmexdclhybajwnk16tjt5kmprugns9ymeaem8aabafb8zhbqucrj+kez0vnifiaqaaa...@bystorm.com
> I'm missing something simple since I can't seem to get it to work.
> Here is an example of how I'm trying to get the week number:
>
> date(datetime(eventtime, 'unixepoch', 'localtime'), '%W')
>
> based on http://www.sqlite.org/lang_datefunc.html

Your construct doesn't remotely resemble anything described on that 
page. You want something like

strftime('%W', eventtime, 'unixepoch', 'localtime');

Igor Tandetnik 



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "COMMIT"

[Igor Tandetnik]

"Joanne Pham" 
wrote in message news:760656.34138...@web90308.mail.mud.yahoo.com
> I have read the sqlite document and document stated that:
> The SQL command "COMMIT" does not actually commit the changes to
> disk. It just turns autocommit back on.

You omitted the next sentence: "Then, at the conclusion of the command, 
the regular autocommit logic takes over and causes the actual commit to 
disk to occur." For all practical purposes, COMMIT immediately writes to 
disk.

> The question is the default of database open connection is
> "autocommit" and if my function has :
> sqlite3_exec(pDb,"BEGIN;", NULL, 0, );
> sqlSt = sqlite3_step(pStmt);
> ..
> sqlSt = sqlite3_exec(pDb,"END;", NULL, 0, );
>
> Then Do I need to sqlSt = sqlite3_exec(pDb,"COMMIT;", NULL, 0,
> ) to turn on the autocommit again.

COMMIT and END are synonyms - two names for the same command.

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The details of the behavior of the sqlite3_step() interface

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Joanne Pham wrote:
> I read the SQLite document about the sqlite3_step() as below:
> The details of the behavior of the sqlite3_step() interface depend on 
> whether the statement was prepared using the newer "v2" interface 
> sqlite3_prepare_v2() and sqlite3_prepare16_v2() or the older legacy interface 
> sqlite3_prepare() and sqlite3_prepare16.
> 
> So what is the detaill behavior of sqlite3_step() interface when:
> 1) The statement using sqlite3_prepare() vs
> 2) sqlite3_prepare_v2()

That information is given in the very next paragraph on the page you
quoted as well as a bolded parapgraph at the bottom.

http://www.catb.org/~esr/faqs/smart-questions.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkn+bssACgkQmOOfHg372QRsHQCeIB7h2MLl1yk/gBgqBWS6WhTk
zzgAoKDwQwOwvd5YFUhYUfZCeXM/GQD6
=Gz7F
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "COMMIT"

[Joanne Pham]

Hi All,
I have read the sqlite document and document stated that:
    The SQL command "COMMIT" does not actually commit the changes to disk. It 
just turns autocommit back on. 
The question is the default of database open connection is "autocommit" and if 
my function has :
            sqlite3_exec(pDb,"BEGIN;", NULL, 0, );
           sqlSt = sqlite3_step(pStmt);
                .. 
        sqlSt = sqlite3_exec(pDb,"END;", NULL, 0, );

Then Do I need to  sqlSt = sqlite3_exec(pDb,"COMMIT;", NULL, 0, ) to 
turn on the autocommit again. 
 Thanks
JP


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to create a table from a text file via c/c++ interface?

[Griggs, Donald]

Hi, Feng,

I don't think that "load data from" is part of the sql standard -- I may
be wrong.

At any rate, the source code to the command line utility is freely
available.

When you wrote that "Using ' insert into values' ... is very time
comsuming" did you mean time consuming to write the program, or
time-consuming to run?  If it is taking a very long time to RUN compare
to the sqlite3 ".import" command, then be sure you're surrounding the
import (or at least every 1000 lines or so) in a TRANSACTION.  Expect a
large increase in speed.

http://www.sqlite.org/lang_transaction.html

Perhaps this helps,
   Donald Griggs

 

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of knightfeng
Sent: Sunday, May 03, 2009 10:41 PM
To: sqlite-users
Subject: [sqlite] How to create a table from a text file via c/c++
interface?

Hi,
   
   '.import' can be used to create a table from text file in the command
line version , but how can I do that using C/C++ interface? Using '
insert into values' by reading a text file line by line is very time
comsuming and it seems that there is no such SQL command "LOAD DATA
FROM" supported by MySql in sqlite.  

Many thanks.

Zhixing Feng




2009-05-04 



knightfeng
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] some help with datetime

[Gene Allen]

Hello all, 

 

I have a table with a unixepoch column in it. 

 

I need to do some fancy calculation based on that time, hourly summaries,
weekly averages, standard dev, etc..

 

Right now..I'm only using it for reporting and simple analysis and it's
often formatted like this: datetime(eventtime, 'unixepoch', 'localtime')

 

What I need is a count all the records grouped by week or by day or by month
so I can do some more advanced processing on it.  

 

I'm missing something simple since I can't seem to get it to work.  Here is
an example of how I'm trying to get the week number:

date(datetime(eventtime, 'unixepoch', 'localtime'), '%W')

based on http://www.sqlite.org/lang_datefunc.html

it seemed to me that the date function needs a string so I passed in a
formatted string.  No joy.

 

Can someone point out the error in my ways?

 

Thank you,

 

Gene

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to create a table from a text file via c/c++ interface?

[knightfeng]

Hi,
   
   '.import' can be used to create a table from text file in the command line 
version , but how can I do that using C/C++
interface? Using ' insert into values' by reading a text file line by line is 
very time comsuming and it seems that there is no such SQL command "LOAD DATA 
FROM" supported by MySql in sqlite.  

Many thanks.

Zhixing Feng




2009-05-04 



knightfeng 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] The details of the behavior of the sqlite3_step() interface

[Joanne Pham]

Hi All,
I read the SQLite document about the sqlite3_step() as below:
    The details of the behavior of the sqlite3_step() interface depend on 
whether the statement was prepared using the newer "v2" interface 
sqlite3_prepare_v2() and sqlite3_prepare16_v2() or the older legacy interface 
sqlite3_prepare() and sqlite3_prepare16.

So what is the detaill behavior of sqlite3_step() interface when:
    1) The statement using sqlite3_prepare() vs
    2) sqlite3_prepare_v2()
Thanks,
JP


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_finalize(sqlite3_stmt) is this call clean up thememory

[Joanne Pham]

Thanks a lot Igor for respond my email.
JP





From: Igor Tandetnik 
To: sqlite-users@sqlite.org
Sent: Sunday, May 3, 2009 7:05:52 PM
Subject: Re: [sqlite] sqlite3_finalize(sqlite3_stmt) is this call clean up 
thememory

"Joanne Pham" 
wrote in message news:111052.72599...@web90308.mail.mud.yahoo.com
> Is sqlite3_finalize(sqlite3_stmt) cleaning up the memory which is
> allocated by sqlite_prepare()?
> I checked the database statement handle before calling
> sqlite3_finalize and after calling this sqlite3_finalize the address
> is the same.

int* p = new int;
printf("Before: %p\n", p);
delete p;
printf("After: %p\n", p);

Try this code - you'll see that it prints the same address twice. Does 
this surprise you? Would you take it as a sign that this code somehow 
fails to deallocate all the memory it allocates?

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_finalize(sqlite3_stmt) is this call clean up thememory

[Igor Tandetnik]

"Joanne Pham" 
wrote in message news:111052.72599...@web90308.mail.mud.yahoo.com
> Is sqlite3_finalize(sqlite3_stmt) cleaning up the memory which is
> allocated by sqlite_prepare()?
> I checked the database statement handle before calling
> sqlite3_finalize and after calling this sqlite3_finalize the address
> is the same.

int* p = new int;
printf("Before: %p\n", p);
delete p;
printf("After: %p\n", p);

Try this code - you'll see that it prints the same address twice. Does 
this surprise you? Would you take it as a sign that this code somehow 
fails to deallocate all the memory it allocates?

Igor Tandetnik



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_finalize(sqlite3_stmt) is this call clean up the memory

[Joanne Pham]

Hi All,
Is sqlite3_finalize(sqlite3_stmt) cleaning up the memory which is allocated by 
sqlite_prepare()?
I checked the database statement handle before calling sqlite3_finalize and 
after calling this sqlite3_finalize the address is the same.
I was wordering if the memory of database statement handle is cleaning up.
Thanks,
JP


  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] OT: how best to convert sqlite3_int64 to and from string in a cross platform fashion?

[Martin Engelschalk]

Sorry, i forgot: my INT64 is the same as sqlite_int64

Martin Engelschalk wrote:
> Hi,
>
> This is what i do, works for me. No OSX hovever, sorry.
>
> #ifdef _WIN32
> #define INT64 __int64
> #else
> #define INT64 long long
> #endif
>
> // From INT64 to String
>
> INT64 iOther;
> char mBuffer[64];   
>
> #ifdef _WIN32
> sprintf(mBuffer, "%I64d", iOther);
> #else
> sprintf(mBuffer, "%lld", iOther);
> #endif
>
> // From String to INT64
>
> #ifdef _WIN32
> iOther = _atoi64(mBuffer);
> #elif defined(AIX5)
> iOther = strtoll(mBuffer, 0, 10);
> #elif defined(HPUX)
> iOther = __strtoll(mBuffer, 0, 10);
> #else
> iOther = atoll(mBuffer);
> #endif
>
> Martin
>
> Sam Carleton wrote:
>   
>> I am current developing a system only on Windows, but I do plan to port it
>> to OSX someday.  I am passing ID's as strings to keep maximum flexibility
>> between databases and the existing system.  So how do I convert a
>> sqlite3_int64 to a string and a string to a sqlite3_int64 in a cross
>> platform fashion?
>>
>> Sam
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>   
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] OT: how best to convert sqlite3_int64 to and from string in a cross platform fashion?

[Martin Engelschalk]

Hi,

This is what i do, works for me. No OSX hovever, sorry.

#ifdef _WIN32
#define INT64 __int64
#else
#define INT64 long long
#endif

// From INT64 to String

INT64 iOther;
char mBuffer[64];   

#ifdef _WIN32
sprintf(mBuffer, "%I64d", iOther);
#else
sprintf(mBuffer, "%lld", iOther);
#endif

// From String to INT64

#ifdef _WIN32
iOther = _atoi64(mBuffer);
#elif defined(AIX5)
iOther = strtoll(mBuffer, 0, 10);
#elif defined(HPUX)
iOther = __strtoll(mBuffer, 0, 10);
#else
iOther = atoll(mBuffer);
#endif

Martin

Sam Carleton wrote:
> I am current developing a system only on Windows, but I do plan to port it
> to OSX someday.  I am passing ID's as strings to keep maximum flexibility
> between databases and the existing system.  So how do I convert a
> sqlite3_int64 to a string and a string to a sqlite3_int64 in a cross
> platform fashion?
>
> Sam
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] OT: how best to convert sqlite3_int64 to and from string in a cross platform fashion?

[Sam Carleton]

I am current developing a system only on Windows, but I do plan to port it
to OSX someday.  I am passing ID's as strings to keep maximum flexibility
between databases and the existing system.  So how do I convert a
sqlite3_int64 to a string and a string to a sqlite3_int64 in a cross
platform fashion?

Sam
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [newbie] How to upgrade sqlite3 in Ubuntu?

[Derrell Lipman]

On Sun, May 3, 2009 at 12:32 PM, scientist scientist <
scientist92...@yahoo.com> wrote:

> Thank you for your fast answer, but my problem still exists after I
> followed your instructions.
>
> Firstly I removed the default sqlite3 using sudo apt-get remove sqlite3
> command.
> After that I moved to the sqlite-3.6.13 folder which was extracted from
> sqlite-amalgamation-3.6.13.tar.gz and executed the 3 commands:
> ../configure --prefix=/usr/local
> make
> sudo make install
> I did check the PATH variable and It did contain /usr/local/bin.
> However, when I entered
> sqlite3
> the output was still 3.4.2
> The weird thing is that, after executing all of these above commands, and
> then sudo apt-get remove sqlite3, I got the following message:
> Package sqlite3 is not installed, so not removed
> I can't understand what's going on.
>

Did you type "which sqlite3" to figure out where it's find it? Wherever that
one is, you need to remove it.

Derrell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [newbie] How to upgrade sqlite3 in Ubuntu?

[scientist scientist]

Thank you for your fast answer, but my problem still exists after I followed 
your instructions.

Firstly I removed the default sqlite3 using sudo apt-get remove sqlite3 command.
After that I moved to the sqlite-3.6.13 folder which was extracted from 
sqlite-amalgamation-3.6.13.tar.gz and executed the 3 commands:
../configure --prefix=/usr/local
make
sudo make install
I did check the PATH variable and It did contain /usr/local/bin.
However, when I entered
sqlite3
the output was still 3.4.2
The weird thing is that, after executing all of these above commands, and then 
sudo apt-get remove sqlite3, I got the following message:
Package sqlite3 is not installed, so not removed
I can't understand what's going on.


--- On Sun, 5/3/09, Derrell Lipman  wrote:

From: Derrell Lipman 
Subject: Re: [sqlite] [newbie] How to upgrade sqlite3 in Ubuntu?
To: "General Discussion of SQLite Database" 
Date: Sunday, May 3, 2009, 8:56 AM

On Sun, May 3, 2009 at 11:37 AM, scientist scientist <
scientist92...@yahoo.com> wrote:

> Hi all,
> My current Ubuntu version is 8.04 and it has sqlite3 3.4.2 by default. Now
> I want to upgrade sqlite3 to its newest version.
> I've already downloaded sqlite-amalgamation-3.6.13.tar.gz, uncompressed the
> package and run the following commands:
> ../configure
> make
> sudo make install
> But when I enter
> sqlite3
> the output result is still version 3.4.2
>
> How can I upgrade sqlite3 to its latest version 3.6.13?
>

The amalgamation probably installed into some directory not in your path.
You should look at where it installed (re-run ../configure and look at its
output, which should tell you where it will install to. For Ubuntu, you
almost certainly want it to install into /usr/local with the executable
going into /usr/local/bin. If it chose some path other than /usr/local, you
probably want to remove it from wherever it installed to.

Next, remove the Ubuntu-provided version of sqlite3 since you won't need it
any longer:

  sudo apt-get remove sqlite3

Now rerun configure like this, to specify the install prefix, and then
rebuild and reinstall:

  ../configure --prefix=/usr/local
  make
  sudo make install

If you're running csh type "rehash" to rescan your path.

Ensure that /usr/local/bin is in your path:

  echo $PATH

If not, add /usr/local/bin to your path (typically before /usr/bin in the
list) in your shell start-up file.

You can also type "which sqlite3" to see which version your shell is finding
first in the PATH.

Cheers,

Derrell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3_create_collation_v2 and SQLITE_UTF16_ALIGNED

[D. Richard Hipp]

On May 3, 2009, at 11:15 AM, Florian Weimer wrote:

> The documentation suggests that I can pass SQLITE_UTF16_ALIGNED.
> However, the logic in main.c:createCollation() assumes that
> SQLITE_UTF16_ALIGNED is ORed with another encoding flag value
> (presumably SQLITE_UTF16).  If I specify SQLITE_UTF16_ALIGNED alone, I
> end up with a crash due to a double free bug inside SQLite.
>
> (This has been observed with SQLite 3.6.13.)

This has already been fixed.  See http://www.sqlite.org/cvstrac/chngview?cn=6558


D. Richard Hipp
d...@hwaci.com



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [newbie] How to upgrade sqlite3 in Ubuntu?

[Derrell Lipman]

On Sun, May 3, 2009 at 11:37 AM, scientist scientist <
scientist92...@yahoo.com> wrote:

> Hi all,
> My current Ubuntu version is 8.04 and it has sqlite3 3.4.2 by default. Now
> I want to upgrade sqlite3 to its newest version.
> I've already downloaded sqlite-amalgamation-3.6.13.tar.gz, uncompressed the
> package and run the following commands:
> ../configure
> make
> sudo make install
> But when I enter
> sqlite3
> the output result is still version 3.4.2
>
> How can I upgrade sqlite3 to its latest version 3.6.13?
>

The amalgamation probably installed into some directory not in your path.
You should look at where it installed (re-run ../configure and look at its
output, which should tell you where it will install to. For Ubuntu, you
almost certainly want it to install into /usr/local with the executable
going into /usr/local/bin. If it chose some path other than /usr/local, you
probably want to remove it from wherever it installed to.

Next, remove the Ubuntu-provided version of sqlite3 since you won't need it
any longer:

  sudo apt-get remove sqlite3

Now rerun configure like this, to specify the install prefix, and then
rebuild and reinstall:

  ../configure --prefix=/usr/local
  make
  sudo make install

If you're running csh type "rehash" to rescan your path.

Ensure that /usr/local/bin is in your path:

  echo $PATH

If not, add /usr/local/bin to your path (typically before /usr/bin in the
list) in your shell start-up file.

You can also type "which sqlite3" to see which version your shell is finding
first in the PATH.

Cheers,

Derrell
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] [newbie] How to upgrade sqlite3 in Ubuntu?

[scientist scientist]

Hi all,
My current Ubuntu version is 8.04 and it has sqlite3 3.4.2 by default. Now I 
want to upgrade sqlite3 to its newest version. 
I've already downloaded sqlite-amalgamation-3.6.13.tar.gz, uncompressed the 
package and run the following commands:
../configure
make
sudo make install
But when I enter
sqlite3
the output result is still version 3.4.2

How can I upgrade sqlite3 to its latest version 3.6.13? 

Thanks in advanced!




  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] sqlite3_create_collation_v2 and SQLITE_UTF16_ALIGNED

[Florian Weimer]

The documentation suggests that I can pass SQLITE_UTF16_ALIGNED.
However, the logic in main.c:createCollation() assumes that
SQLITE_UTF16_ALIGNED is ORed with another encoding flag value
(presumably SQLITE_UTF16).  If I specify SQLITE_UTF16_ALIGNED alone, I
end up with a crash due to a double free bug inside SQLite.

(This has been observed with SQLite 3.6.13.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users