Re: [sqlite] sql statement to update the data in the table

2011-10-20 Thread Joanne Pham
SELECT AES_ENCRYPT(password, 'abcddsfddafdasfddasd');
is work!
I think I need to find out what is the data type and data lengh for storing the 
encrypt password 
Thanks,
JP



From: Simon Slavin <slav...@bigfraud.org>
To: Joanne Pham <joannekp...@yahoo.com>; General Discussion of SQLite Database 
<sqlite-users@sqlite.org>
Sent: Wednesday, October 19, 2011 6:24 PM
Subject: Re: [sqlite] sql statement to update the data in the table


On 20 Oct 2011, at 1:49am, Joanne Pham wrote:

> it seems like it didn't work.
> For example the password is 'password'. I ran the update statement below and 
> do the AES_DECRYPT the password is null instead of 'password'.

Try just

SELECT AES_ENCRYPT(password, 'abcddsfddafdasfddasd');

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


Re: [sqlite] sql statement to update the data in the table

2011-10-19 Thread Joanne Pham
Thanks,
Yes, That is what i want but it seems like it didn't work.
For example the password is 'password'. I ran the update statement below and do 
the AES_DECRYPT the password is null instead of 'password'.
Any idea?
JP



From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Wednesday, October 19, 2011 5:35 PM
Subject: Re: [sqlite] sql statement to update the data in the table

On 10/19/2011 7:23 PM, Joanne Pham wrote:
> update vpn set password = AES_ENCRYPT((select password from vpn) , 
> "abcddsfddafdasfddasd").

I suspect you want

update vpn set password = AES_ENCRYPT(password, 'abcddsfddafdasfddasd');

-- 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] sql statement to update the data in the table

2011-10-19 Thread Joanne Pham
Hi Igor,
 
update vpn set password = AES_ENCRYPT((select password from vpn) , 
"abcddsfddafdasfddasd").

Basically, I want to encrypt the password in vpn table so the passwords in this 
table are different. Above mysql statement still didn't work. Any idea.
Thanks,
JP




From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Wednesday, October 19, 2011 3:58 PM
Subject: Re: [sqlite] sql statement to update the data in the table

On 10/19/2011 6:34 PM, Joanne Pham wrote:
> Curently I had the table with the plain text and I want to  encrypt these 
> passwords by using the following sql statement but I got the error mesages.. 
> Any suggestion?
> update vpn set password = AES_ENCRYPT(select password from mytable, 
> "abcddsfddafdasfddasd").

Do you want vpn.password set to the same value in all rows? I would have 
expected a WHERE clause on the select statement that somehow correlates mytable 
with vpn.

Anyway, the immediate cause of the syntax errors is the fact that a subselect 
needs to be enclosed in parentheses:

update vpn set password = AES_ENCRYPT((select password from mytable), 
"abcddsfddafdasfddasd");

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


[sqlite] sql statement to update the data in the table

2011-10-19 Thread Joanne Pham
Hi all,
Curently I had the table with the plain text and I want to  encrypt these 
passwords by using the following sql statement but I got the error mesages. Any 
suggestion?
update vpn set password = AES_ENCRYPT(select password from mytable, 
"abcddsfddafdasfddasd").
Thanks in advance,
JP





 
___
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] PRAGMA auto_vacuum

2010-05-11 Thread Joanne Pham
Hi All,
I have the database which has a lot of insertion and deletion. Do you have any 
recomendation about what value that need to be set for auto_vacuum
in this case to improve the performance for deletion as well as insertion the 
new row to the database. (0 | NONE | 1 | FULL | 2 | INCREMENTAL;)
Thanks,
JP


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


Re: [sqlite] Indexes on the table

2009-07-22 Thread Joanne Pham
Thanks Simon for detail explaination about the indexes!
JP





From: Simon Slavin <slav...@hearsay.demon.co.uk>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, July 21, 2009 3:57:22 PM
Subject: Re: [sqlite] Indexes on the table


On 21 Jul 2009, at 11:12pm, Joanne Pham wrote:

> CREATE TABLE myTable(
>    startTime INTEGER ...
>    appId INTEGER
>    myId INTEGER ...
>    trafficType INTEGER
> ..
> )
> StartTime can be from 1...59
> appId can be from 1...256
> myId can be from 1...5000
> trafficType can be from 1..3
>
> I would like to create index for this table on these columns  
> StartTime ,appId, myId, trafficType as :
> create unique index myTableIndex on myTable(appId, myId,  
> trafficType, startTime).
> Is the order of the columns in the create index statement  
> importance? If yes then what is rule of thumb here?

You choose what indexes to create depending on what SELECT commands  
you're going to use.  So if none of your SELECT instructions use  
trafficType in the WHERE or ORDER BY clause there is no need for it in  
any index.

Once you know which fields you want in an index, the principle is to  
reject as many rows as you can as soon as you can.  This leaves the  
software fewer records to worry about at the next step, which means it  
needs less memory and has less processing to do.

Suppose you have a thousand records and want something like

SELECT * FROM myTable WHERE appId = 40 AND trafficType = 2

Suppose 1/3rd of your records have each traffic type, but 1/256th of  
your records have each appId.  Then selecting on trafficType first  
would reject 2 records out of every 3, meaning that the next step has  
to process just 333 records, which is good.  But selecting on appId  
first instead would reject 255 records out of every 256, meaning that  
the next step has to process just 4 records which is much better.

So in this case an index on (appId, trafficType) would be research in  
a faster SELECT than (trafficType, appId).

Simon.
___
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] Indexes on the table

2009-07-21 Thread Joanne Pham
Hi All,
I need to create the indexes on the tables and these indexes have 4 columns. 
Let say the table definition as below:
CREATE TABLE myTable(
    startTime INTEGER ...
    appId INTEGER
    myId INTEGER ...
    trafficType INTEGER
..
)
StartTime can be from 1...59
appId can be from 1...256
myId can be from 1...5000
trafficType can be from 1..3

I would like to create index for this table on these columns StartTime ,appId, 
myId, trafficType as :
create unique index myTableIndex on myTable(appId, myId, trafficType, 
startTime). 
Is the order of the columns in the create index statement importance? If yes 
then what is rule of thumb here?
Thanks 
JP


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


Re: [sqlite] NULL data .dump command

2009-06-17 Thread Joanne Pham
Hi All,
Any hints!
I have no problem with executing the followng sql : select * from signature
but if I run this : select * from sig order by peerid; then I got the error 
message:
    SQL error: database disk image is malformed
I have index on peerid and I don't know why the second select has problem?
Any idea!
Thanks,
JP





From: Joanne Pham <joannekp...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, June 16, 2009 3:41:17 PM
Subject: Re: [sqlite] NULL data .dump command

Hi All,
I ran two queries:
    1) select * from signature;
        I didn't see the "SQL error: database disk image is malformed"

    2) But if I ran the "select * from sig order by peerid;" then I have seen 
the malformed
        ...
        11020876449360377856|345049073990|1276|368|230383|1857|1245183730|2|0
        SQL error: database disk image is malformed
   
Is the index corruped some where?
Your help is greatly appreciated.
Thanks,
JP




________
From: Joanne Pham <joannekp...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, June 16, 2009 3:26:37 PM
Subject: [sqlite] NULL data .dump command

Hi All,
I have the table is defined as below:
CREATE TABLE `signature` (
  `sig` char(50) NOT NULL, 
  `id' bigint(20) default '0',

But I have ran the folowing command:
    .output mySelect
    select * from signature;
    then I didn't see NULL values in the mySelect file at all
But I ran the following command:
    .output myDump
    .dump signature
    then I viewed the file it has the following NULL values
       INSERT INTO "signature" 
VALUES('573535428650752000',345049073990,1294,365,230296,414,1245183707,2,'0');
            INSERT INTO "signature" 
VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
           even though sig is defined as NOT NULL but why I have NULL for some 
of these insert statement in my dump but not in select.
Thanks,
JP


      
___
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] NULL data .dump command

2009-06-16 Thread Joanne Pham
Hi All,
I ran two queries:
    1) select * from signature;
        I didn't see the "SQL error: database disk image is malformed"

    2) But if I ran the "select * from sig order by peerid;" then I have seen 
the malformed
        ...
        11020876449360377856|345049073990|1276|368|230383|1857|1245183730|2|0
        SQL error: database disk image is malformed
   
Is the index corruped some where?
Your help is greatly appreciated.
Thanks,
JP




________
From: Joanne Pham <joannekp...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, June 16, 2009 3:26:37 PM
Subject: [sqlite] NULL data .dump command

Hi All,
I have the table is defined as below:
CREATE TABLE `signature` (
  `sig` char(50) NOT NULL, 
  `id' bigint(20) default '0',

But I have ran the folowing command:
    .output mySelect
    select * from signature;
    then I didn't see NULL values in the mySelect file at all
But I ran the following command:
    .output myDump
    .dump signature
    then I viewed the file it has the following NULL values
       INSERT INTO "signature" 
VALUES('573535428650752000',345049073990,1294,365,230296,414,1245183707,2,'0');
            INSERT INTO "signature" 
VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
           even though sig is defined as NOT NULL but why I have NULL for some 
of these insert statement in my dump but not in select.
Thanks,
JP


      
___
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] NULL data .dump command

2009-06-16 Thread Joanne Pham
Hi All,
I have the table is defined as below:
CREATE TABLE `signature` (
  `sig` char(50) NOT NULL, 
  `id' bigint(20) default '0',

But I have ran the folowing command:
    .output mySelect
    select * from signature;
    then I didn't see NULL values in the mySelect file at all
But I ran the following command:
    .output myDump
    .dump signature
    then I viewed the file it has the following NULL values
       INSERT INTO "signature" 
VALUES('573535428650752000',345049073990,1294,365,230296,414,1245183707,2,'0');
            INSERT INTO "signature" 
VALUES(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
           even though sig is defined as NOT NULL but why I have NULL for some 
of these insert statement in my dump but not in select.
Thanks,
JP


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


Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has mulitple threads using the same connection

2009-06-09 Thread Joanne Pham
Sorry Couldn't locate the email about Compite with DSQLITE_THREADSAFE=1 bu the 
application has multiple threads using the same connection?
Would you pleas direct me to any document that has this info.
Thanks,
JP





From: Kees Nuyt <k.n...@zonnet.nl>
To: sqlite-users@sqlite.org
Sent: Tuesday, June 9, 2009 12:52:47 PM
Subject: Re: [sqlite] Compite with DSQLITE_THREADSAFE=1 but application has 
mulitple threads using the same connection

On Tue, 9 Jun 2009 12:06:44 -0700 (PDT), Joanne Pham
<joannekp...@yahoo.com> wrote:

>
>
>Hi All,
>What was the problem with the SQLite library is builded 
>with DSQLITE_THREADSAFE=1 but the application is using 
>multiple threads with the same connection.
>Thanks,
>JP

Joannek,

I think this same issue was discussed very recently in the
mailing list. You may want to consult the archives.
-- 
  (  Kees Nuyt
  )
c[_]
___
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] Compite with DSQLITE_THREADSAFE=1 but application has mulitple threads using the same connection

2009-06-09 Thread Joanne Pham


Hi All,
What was the problem with the SQLite library is builded with 
DSQLITE_THREADSAFE=1 but the application is using multiple threads with the 
same connection.
Thanks,
JP


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


Re: [sqlite] How to change the default values option when build SQLite 3.6.14

2009-06-03 Thread Joanne Pham
Thanks a lot Pavel.
JP





From: Pavel Ivanov <paiva...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Wednesday, June 3, 2009 12:59:28 PM
Subject: Re: [sqlite] How to change the default values option when build SQLite 
3.6.14

You can do during configuration:

../sqlite/configure -DSQLITE_THREADSAFE=2


Pavel

On Wed, Jun 3, 2009 at 2:27 PM, Joanne Pham <joannekp...@yahoo.com> wrote:
> Hi All,
> I would like to build the SQLite 3.6.14 to following the steps as mentioned 
> in the document
>     tar xzf sqlite.tar.gz ;# Unpack the source tree into "sqlite"
>     mkdir bld ;# Build will occur in a sibling directory
>     cd bld ;# Change to the build directory
>     ../sqlite/configure ;# Run the configure script
>     make ;# Run the makefile.
>     make install ;# (Optional) Install the build products
> That is the build to use the default option but I want to change the of 
> SQLITE_THREADSAFE=1 to SQLITE_THREADSAFE=2. How to change this option at the 
> compiler time.
> Thanks,
> JP
>
>
>
> ___
> 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


[sqlite] How to change the default values option when build SQLite 3.6.14

2009-06-03 Thread Joanne Pham
Hi All,
I would like to build the SQLite 3.6.14 to following the steps as mentioned in 
the document
    tar xzf sqlite.tar.gz ;# Unpack the source tree into "sqlite"
    mkdir bld ;# Build will occur in a sibling directory
    cd bld ;# Change to the build directory
    ../sqlite/configure ;# Run the configure script
    make ;# Run the makefile.
    make install ;# (Optional) Install the build products
That is the build to use the default option but I want to change the of 
SQLITE_THREADSAFE=1 to SQLITE_THREADSAFE=2. How to change this option at the 
compiler time.
Thanks,
JP


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


Re: [sqlite] Should we upgrade the SQLite to 6.6.14.2 from 3.59

2009-06-01 Thread Joanne Pham
Thanks Roger for the "nice" respond.
I send this email to the group to ask the question just in case if someone in 
group has done the bench mark then it will save my time.
If I know the result by trying the newer SQLite than I won't ask this question 
right?

You don't need to ask me to read the "smart-questions". If you don't know the 
answer to the question please keep quiet.
Thanks
JP





From: Roger Binns <rog...@rogerbinns.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Monday, June 1, 2009 3:21:10 PM
Subject: Re: [sqlite] Should we upgrade the SQLite to 6.6.14.2 from 3.59

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Joanne Pham wrote:
> Do you think that upgrade the SQLite to newer version 3.6.14.2 from 3.5.9 
> will be help to improve the SQLite database operation like Read/Write?

What results did you see when you tried the newer SQLite against your
queries with your data on the platforms you use?

Please read this:

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


Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkokVFEACgkQmOOfHg372QTcbQCbB3MJbcS/6bIqQIZLpr0tBJ2H
gZ8AoLvnzMl89X1dYx76HZ47qka3Xhb9
=i988
-END PGP SIGNATURE-
___
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] Should we upgrade the SQLite to 6.6.14.2 from 3.59

2009-06-01 Thread Joanne Pham
Hi All,
We are currently using SQLite 3.59 for our product and We will have the release 
in middle of June. 
We have been facing a lot of problem regarding performance and next release we 
can to able to scale up to 4 times faster than previous release. 
Do you think that upgrade the SQLite to newer version 3.6.14.2 from 3.5.9 will 
be help to improve the SQLite database operation like Read/Write?
Your input is greatly appreciated.
Thanks,
JP


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


Re: [sqlite] Should use prepare/bind or just sqlite_exec.

2009-05-14 Thread Joanne Pham
Thanks Igor,
So you prefer #1 instead of #2.
Thanks,
JP





From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Thursday, May 14, 2009 11:13:23 AM
Subject: Re: [sqlite] Should use prepare/bind or just sqlite_exec.

Joanne Pham <joannekp...@yahoo.com> wrote:
> I would like to update the database and there are two ways to do it
> and I were wondering which way is better:
> 1) Way 1 - used the sqlite3_preare, sqlite3_bind , sqlite3_step ...
>
> 2) Way #2
> q = "UPDATE logTable SET stale = 1 WHERE id = ";
> sprintf(sqlStmt,"%s%d ",q,rpid);
> sqlSt = sqlite3_exec(pDb, sqlStmt, NULL, 0, ) ;
>
> Which way is better in term of performance.

I doubt you would detect any measurable performance difference on 
one-time execution. #1 is better for reasons other than performance. It 
also improves performance if you need to run the same query many times, 
perhaps with different parameters.

Realize that sqlite3_exec simply calls sqlite3_preare, sqlite3_step et 
al internally.

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


[sqlite] Should use prepare/bind or just sqlite_exec.

2009-05-14 Thread Joanne Pham
Hi all,
I would like to update the database and there are two ways to do it and I were 
wondering which way is better:
1) Way 1 - used the sqlite3_preare, sqlite3_bind , sqlite3_step ...
    q = "UPDATE logTable SET stale = ? WHERE id = ?";
     rc = sqlite3_prepare(updateSqli q, -1, , 0);
     if (rc != SQLITE_OK) {
         fprintf(stderr, "Error prepare: %s\n", __FUNCTION__);
        return -1;
     }
     ret = sqlite3_bind_int(pstmt, 1, 0);
     ret = sqlite3_bind_int64(pstmt, 2, rpid);
  rc = sqlite3_step(pstmt); 
     rc = sqlite3_reset(pstmt);
 rc = sqlite3_finalize(pstmt);
 
2) Way #2
q = "UPDATE logTable SET stale = 1 WHERE id = ";
 sprintf(sqlStmt,"%s%d ",q,rpid);
sqlSt = sqlite3_exec(pDb, sqlStmt, NULL, 0, ) ;
if (sqlSt != SQLITE_OK ) {
 // print out error message
   sqlite3_free(errMsg);
}
...
 
Which way is better in term of performance.
Thanks
JP


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


Re: [sqlite] Prepared statements must be generated inside your transaction

2009-05-12 Thread Joanne Pham
Hi,
Thanks for information!
Regarding "batch" insert why we need to put the column binding 
(sqlite3_bind...) before running sqlite3_step. For example:
 sqlite_prepare_v2 
 begin transaction
    loop thru all the changes
            sqlite3_bind 
            sqlite3_step.
    end loop
end transaction

For other database like Microsoft Sql server I only bind the column once(bind 
statement outside the loop to the data structure) in the loop I don't need to 
bind column again but just copy the new inserted row to the data structure that 
already binded outside of the loop. In this case we don't need to bind the 
columns in the loop. Why this way didn't work for SQLite3 database.
Thanks,
JP





From: John Stanton <jo...@viacognis.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, May 12, 2009 12:09:09 PM
Subject: Re: [sqlite] Prepared statements must be generated inside your 
transaction

The confusion is in the names.  When you "PREPARE" a statement you 
actually compile the SQL.  Compiling a program each time you run it is a 
waste of resources, and so is compiling the SQL each time you use it.

Prepare your statements once and use them many times, binding data to 
the compiled code at execution time.

The design of Sqlite is such that it is possible to store compiled SQL 
permanently and use it when you run your application.  I beleive there 
is, or was a version of Sqlite tailored for embedded use which does just 
that.

In our Sqlite programs we  like toprepare all SQL in an initialization 
phase and have two wins.  First we get faster execution and secondly we 
detect database errors or mismatches before entering the main functions 
of the program and avoid having to backtrack in error recovery.

Pavel Ivanov wrote:
> I believe, these matters are very specific for each database server
> (though I can't recall server for which it's true what you say). What
> specific server is talked about in this book? What's the name of this
> book?
>
> As long as SQLite is a concern, I prepare statements outside of
> transaction and then use them across different transactions without
> any problems but with huge performance improvement compared to when
> I've prepared statements before each transaction.
>
> Pavel
>
> On Tue, May 12, 2009 at 12:32 PM, Joanne Pham <joannekp...@yahoo.com> wrote:
>  
>> Hi All,
>> I have read one of the performance document and it stated that "prepared 
>> statements must be generated inside transaction". Is that correct.
>> The document also stated that " While trying to improve the performance of 
>> bulk imports in our C++ project, we found that creating the prepared 
>> statements was a large hit. Creating them once at the
>>        construction of our class, though, made the problem worse! It turns 
>>out that prepared statements that are generated before the transaction start 
>>do not work with the transaction. The fix was simply to
>>        create new prepared statements once per transaction."
>>
>> So I have to do this:
>>    begin transaction
>>        prepared statement
>>        ..
>>    end transaction.
>>
>> I though the prepare statement must be outside of the transaction. Can any 
>> one confirm this?
>> Thanks,
>> JP
>>
>>
>>
>> ___
>> 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



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


[sqlite] Program is crashed on sqlite3_free

2009-05-12 Thread Joanne Pham
Hi all,
I have the application and occasionally it is crashed on sqlite3_free when it 
callled sqlite3_finalized and I really didn't know what problem it was. 
Below is stack trace.
Do you have any idea what is caused this problem.? Any hints will be greatly 
appreciated.

#0  0xb5d90c31 in sqlite3_free () from /opt/phoenix/i386/lib/libsqlite3.so.0
#1  0xb5db69cd in sqlite3VdbeMemRelease () from 
/opt/phoenix/i386/lib/libsqlite3.so.0
#2  0xb5db3cd3 in sqlite3VdbeChangeToNoop () from 
/opt/phoenix/i386/lib/libsqlite3.so.0
#3  0xb5db49c8 in sqlite3VdbeHalt () from /opt/phoenix/i386/lib/libsqlite3.so.0
#4  0xb5db4c12 in sqlite3VdbeReset () from /opt/phoenix/i386/lib/libsqlite3.so.0
#5  0xb5db4d60 in sqlite3VdbeFinalize () from 
/opt/phoenix/i386/lib/libsqlite3.so.0
#6  0xb5db2b9d in sqlite3_finalize () from /opt/phoenix/i386/lib/libsqlite3.so.0
Thanks,
JP


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


Re: [sqlite] Prepared statements must be generated inside your transaction

2009-05-12 Thread Joanne Pham
Thanks for quick responde my email
This is sqlite documentation. Below is the link and last paragraph in this 
document has stated that.

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



From: Pavel Ivanov <paiva...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, May 12, 2009 9:43:01 AM
Subject: Re: [sqlite] Prepared statements must be generated inside your 
transaction

I believe, these matters are very specific for each database server
(though I can't recall server for which it's true what you say). What
specific server is talked about in this book? What's the name of this
book?

As long as SQLite is a concern, I prepare statements outside of
transaction and then use them across different transactions without
any problems but with huge performance improvement compared to when
I've prepared statements before each transaction.

Pavel

On Tue, May 12, 2009 at 12:32 PM, Joanne Pham <joannekp...@yahoo.com> wrote:
> Hi All,
> I have read one of the performance document and it stated that "prepared 
> statements must be generated inside transaction". Is that correct.
> The document also stated that " While trying to improve the performance of 
> bulk imports in our C++ project, we found that creating the prepared 
> statements was a large hit. Creating them once at the
>         construction of our class, though, made the problem worse! It turns 
> out that prepared statements that are generated before the transaction start 
> do not work with the transaction. The fix was simply to
>         create new prepared statements once per transaction."
>
> So I have to do this:
>     begin transaction
>         prepared statement
>    ..
>     end transaction.
>
> I though the prepare statement must be outside of the transaction. Can any 
> one confirm this?
> Thanks,
> JP
>
>
>
> ___
> 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


[sqlite] Prepared statements must be generated inside your transaction

2009-05-12 Thread Joanne Pham
Hi All,
I have read one of the performance document and it stated that "prepared 
statements must be generated inside transaction". Is that correct.
The document also stated that " While trying to improve the performance of bulk 
imports in our C++ project, we found that creating the prepared statements was 
a large hit. Creating them once at the   
        construction of our class, though, made the problem worse! It turns out 
that prepared statements that are generated before the transaction start do not 
work with the transaction. The fix was simply to 
        create new prepared statements once per transaction."
 
So I have to do this:
    begin transaction
        prepared statement
   ..
    end transaction.
 
I though the prepare statement must be outside of the transaction. Can any one 
confirm this?
Thanks,
JP


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


Re: [sqlite] How to check the healthy of database and the indexes ofthe tables

2009-05-05 Thread Joanne Pham
Thanks Donald,
Ran  "PRAGMA integrity_check;" and it turned "ok" but the select statement 
using the select return the error message that "...malformed".
I am using sqlite3.5.9.
Thanks,
JP





From: "Griggs, Donald" <donald.gri...@allscripts.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, May 5, 2009 1:39:08 PM
Subject: Re: [sqlite] How to check the healthy of database and the indexes 
ofthe tables

Hello Joanne,

Regarding:  "Is there any command to check if the index or database in good 
condition."

That's why I listed the pragma below in my email of last night and repeat email 
earlier.

You're in luck if only the index is corrupted of course.

SAVE a copy of your current database (very important).

Try dropping the index and rebuilding it.
Even if ok at that point, you might want to then run a VACUUM.

==

Joanne,

I couldn't say, but if I were you I'd probably

-- Hold tight to my backups of my data.
-- Run "PRAGMA integrity_check;" as soon as possible.
-- See if the problem can be reproduced using the command-line utility.
-- Is there an index defined on peerid?  Does "Explain query plan" show that it 
is used by the first query.  If so, perhaps the peerid index contains some 
corruption?
-- Does adding "peerid" to your second select run without error?

Regards,
  Donald
      

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joanne Pham
Sent: Tuesday, May 05, 2009 4:02 PM
To: General Discussion of SQLite Database
Subject: [sqlite] How to check the healthy of database and the indexes ofthe 
tables

Hi All,
I had the database and one of the index is not good condition. Every time I use 
the index by select ... group by .. the result only return few rows and the 
message print  out that "database disk image is malformed". Is there any 
command to check if the index or database in good condition.
Thanks,
JP


      
___
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] How to check the healthy of database and the indexes of the tables

2009-05-05 Thread Joanne Pham
Thanks,
I used sqlite 3.5.9 but when I ran PRAGMA integrity_check; it returns OK but 
one of the index has problem.
Thanks,
JP

 




From: Kees Nuyt <k.n...@zonnet.nl>
To: sqlite-users@sqlite.org
Sent: Tuesday, May 5, 2009 1:30:58 PM
Subject: Re: [sqlite] How to check the healthy of database and the indexes of 
the tables

On Tue, 5 May 2009 13:02:13 -0700 (PDT), Joanne Pham
<joannekp...@yahoo.com> wrote:

>Hi All,
>  I had the database and one of the index is
>not good condition. Every time I use the
>index by select ... group by .. the result
>only return few rows and the message print
>out that "database disk image is malformed".
>  Is there any command to check if the index
>or database in good condition.

PRAGMA integrity_check;
http://www.sqlite.org/pragma.html#debug

>Thanks,
>JP
-- 
  (  Kees Nuyt
  )
c[_]
___
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] How to check the healthy of database and the indexes of the tables

2009-05-05 Thread Joanne Pham
Hi All,
I had the database and one of the index is not good condition. Every time I use 
the index by select ... group by .. the result only return few rows and the 
message print  out that "database disk image is malformed". Is there any 
command to check if the index or database in good condition.
Thanks,
JP


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


Re: [sqlite] SQL error: database disk image is malformed

2009-05-04 Thread Joanne Pham
Thanks for respond my email.
Yes, There is  index defined on peerid. Second query used the index which is 
defined by peerid.
How to fix this corruped database.
Thanks,
JP





From: "Griggs, Donald" <donald.gri...@allscripts.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Monday, May 4, 2009 4:26:58 PM
Subject: Re: [sqlite] SQL error: database disk image is malformed



-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joanne Pham
Sent: Monday, May 04, 2009 2:51 PM
To: General Discussion of SQLite Database
Subject: [sqlite] SQL error: database disk image is malformed

Hi All,
I ran the following sql statement:
    select blobid, fbid from sig group by peerid; return about 10 rows
        22
   ...
   33
return about 10 rows and I got the error message:
 SQL error: database disk image is malformed

but when I ran the following sql statement:
    select blobid, fbid from sig;
I didn't see any error message. So why the first sql statement has problem but 
not the second.
Thanks
JP
==
==

Joanne,

I couldn't say, but if I were you I'd probably

-- Hold tight to my backups of my data.
-- Run "PRAGMA integrity_check;" as soon as possible.
-- See if the problem can be reproduced using the command-line utility.
-- Is there an index defined on peerid?  Does "Explain query plan" show that it 
is used by the first query.  If so, perhaps the peerid index contains some 
corruption?
-- Does adding "peerid" to your second select run without error?

Regards,
  Donald
      
___
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


[sqlite] SQL error: database disk image is malformed

2009-05-04 Thread Joanne Pham
Hi All,
I ran the following sql statement:
    select blobid, fbid from sig group by peerid;
return about 10 rows
        22
   ...
   33 
return about 10 rows and I got the error message:
 SQL error: database disk image is malformed

but when I ran the following sql statement:
    select blobid, fbid from sig;
I didn't see any error message. So why the first sql statement has problem but 
not the second.
Thanks
JP


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


[sqlite] "COMMIT"

2009-05-03 Thread 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


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

2009-05-03 Thread 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

2009-05-03 Thread Joanne Pham
Thanks a lot Igor for respond my email.
JP





From: Igor Tandetnik <itandet...@mvps.org>
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" <joannekp...@yahoo.com>
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


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

2009-05-03 Thread 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


[sqlite] sqlite3_mutex_enter(db->mutex) problem

2009-04-30 Thread Joanne Pham
Hi All,
I have the application and it is crashed on  at ../src/vdbeapi.c:538 
(sqlite3_mutex_enter(db->mutex);  by sqlite3_step.
I couldn't nail down what was the root cause of this problem. Why it crashed on 
sqlite3_mutex_enter API.
Would you please shed some light on this? 
Thank in advance,
JP

Below is core file:
sqlite3_step (pStmt=0x4012d0c3) at ../src/vdbeapi.c:538

at ../src/vdbeapi.c:538 is 
    sqlite3_mutex_enter(db->mutex);
    while( (rc = sqlite3Step(v))==SQLITE_SCHEMA



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


Re: [sqlite] Transaction control At the SQL Level

2009-04-30 Thread Joanne Pham
Again thanks Igor for detail information about autocommit for the sqlite 
database connection.
Thanks,
JP





From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Thursday, April 30, 2009 10:24:16 AM
Subject: Re: [sqlite] Transaction control At the SQL Level

Joanne Pham <joannekp...@yahoo.com> wrote:
> Just want to make sure that I am fully understand about the single
> database connection with multiple database statement handle here.
> For example I have one database connection and 2 database statement
> handle using the same connection. Using the first database statement
> handle I use to select the data from database but not yet
> sqlite3_reset nor sqlite3_finalize. The second database statement
> handle I use to delete the data from database and use sqlite3_reset
> or sqlite3_finalize for the second database handle statement. So
> "delete" statement of the second database statement handle doesn't
> commit to the database until the first database statement handle
> sqlite3_reset or sqlite3_finalize right.

Correct.

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] Transaction control At the SQL Level

2009-04-30 Thread Joanne Pham
Thanks a lot Igor respond my email.
Just want to make sure that I am fully understand about the single database 
connection with multiple database statement handle here.
For example I have one database connection and 2 database statement handle 
using the same connection. Using the first database statement handle I use to 
select the data from database but not yet 
sqlite3_reset nor sqlite3_finalize. The second database statement handle I use 
to delete the data from database and use sqlite3_reset or sqlite3_finalize for 
the second database handle statement.
So "delete" statement of the second database statement handle doesn't commit to 
the database until the first database statement handle sqlite3_reset or 
sqlite3_finalize right.
Once again thank for respond my email
JP




From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Wednesday, April 29, 2009 7:48:41 PM
Subject: Re: [sqlite] Transaction control At the SQL Level

"Joanne Pham" <joannekp...@yahoo.com>
wrote in message news:464293.67815...@web90308.mail.mud.yahoo.com
> 1) : If I have mulitple commands which are used the same SQL database
> connection then all commands after the first won't commit to the
> database if the first one is not completed"

Correct.

> 2) Is that sqlite3_reset will be the command to completed the
> statement?

That, or sqlite3_finalize.

> 3) From document above it seems like the "Autocommited" is for each
> SQLite database connection - So if I have serveral commands are using
> the same connection then is there any command that I can use to
> commit each individual command but not wait until the first statement
> finishes.

No.

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


[sqlite] Transaction control At the SQL Level

2009-04-29 Thread Joanne Pham
Hi All,
I read the document about "File Locking And Concurrency IN SQLite Version 3" 
about the "Transaction Control At The SQL Level" as below:
   "If multiple commands are being executed against the same SQLite database 
connection at the same time, the autocommit is deferred until the very last 
command completes. For example, if a SELECT statement is being executed, the 
execution of the command will pause as each row of the result is returned. 
During this pause other INSERT, UPDATE, or DELETE commands can be executed 
against other tables in the database. But none of these changes will commit 
until the original SELECT statement finishes"

So I have serveral questions:
    1) : If I have mulitple commands which are used the same SQL database 
connection then all commands after the first won't commit to the database if 
the first one is not completed"
    2) Is that sqlite3_reset will be the command to completed the statement?
    3) From document above it seems like the "Autocommited" is for each SQLite 
database connection - So if I have serveral commands are using the same 
connection then is there any command that I can use to commit each individual 
command but not wait until the first statement finishes.
Thanks,
JP


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


Re: [sqlite] How to check compiler options being used

2009-04-22 Thread Joanne Pham


Hi All,
It didn't work - I am using version 3.5.9 and my application is used the 
library libsqlite3.so.8.6 but I have no ideas what are the compiler option 
being used.
Any help please.
Thanks,
JP



From: Roger Binns 
To: General Discussion of SQLite Database 
Sent: Wednesday, April 22, 2009 4:24:15 PM
Subject: Re: [sqlite] How to check compiler options being used

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Roger Binns wrote:
> However the actual compiler flags (eg -O, -DXXX) are not recorded 
> by default in object files or libraries.

If you are using gcc 4.2 or later then you can add -frecord-gcc-switches
to the compile line and they will be recorded in a special section in
the object file.  They will also be combined in any resulting library.

For example:

$ objdump  --full-contents --section .GCC.command.line apsw.o

apsw.o: file format elf64-x86-64

Contents of section .GCC.command.line:
 2d492f75 73722f69 6e636c75 64652f70  -I/usr/include/p
0010 7974686f 6e322e36 002d492e 002d4973  ython2.6.-I..-Is
0020 716c6974 6533002d 49737263 002d445f  qlite3.-Isrc.-D_
0030 5245454e 5452414e 54002d44 45585045  REENTRANT.-DEXPE
0040 52494d45 4e54414c 002d4453 514c4954  RIMENTAL.-DSQLIT
0050 455f4445 42554700 2d445351 4c495445  E_DEBUG.-DSQLITE
0060 5f544852 45414453 4146453d 31002d44  _THREADSAFE=1.-D
[.. it goes on and on ..]

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

iEYEARECAAYFAknvpxsACgkQmOOfHg372QR/uACfZQVBZcaWWM0x2ioRVk2qKfzd
m6oAoJ16RZrAnEFbC/SP8SAkbSmXnq7p
=1F1J
-END PGP SIGNATURE-
___
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] How to check compiler options being used

2009-04-22 Thread Joanne Pham
Hi All,
I am current used the SQLite 3.5.9 and SQLite library are compiled by someone 
and I would like to check all what are the compiler options being used?
How to check the compiler options in this case?
Thanks,
JP



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


[sqlite] create the trigger to delete the data from other database

2009-04-22 Thread Joanne Pham
Hi All,
Can we have the trigger to delete to data from different database?
My application has 2 databases and when ever the application is deleting the 
rows in one of tables in DB1 I would like to have a trigger to delete the rows 
in table in DB2.
Is this possible?
Thanks,
JP



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


[sqlite] Columns in where clause and the index.

2009-04-22 Thread Joanne Pham
Hi All,
I was wondering if we need to have the order of columns in the where clause 
need to be matched with the order of the indexes.
Does the index will be used if the columns in the where clause didn't match 
with the columns in the defined index?
Thanks,
JP



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


[sqlite] Indexes questions

2009-04-21 Thread Joanne Pham
Hi All,
I have the table which has the following indexes:
CREATE INDEX Zactivate ON sig (peerid,flowid,fbid);
CREATE INDEX Zfbid ON sig (flowid,fbid);
CREATE INDEX Zsignature ON sig (peerid,Zsignature);
 
And below are where statements:
WHERE Zsignature = ? AND peerid = ?";
WHERE peerid = ?"
WHERE peerid = ?";
WHERE flowid = ? AND peerid = ?";
WHERE flowid = ? AND peerid = ?";
WHERE flowid = ? AND peerid = ?";
WHERE flowid = ? AND peerid = ?";
WHERE flowid=? AND fbid=? AND peerid=?";
WHERE peerid=? AND stale='2'";
WHERE peerid=? AND flowid=? AND stale='2'";
 
Should Ionly need two indexes. The second one should not be there. Now the 
write operation will be very slow.
Any ideas?
Thanks,
JP


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


[sqlite] Program is crashed on pager_end_transaction

2009-04-21 Thread Joanne Pham
Hi All,
My application is crashed with the stack trace as below:
            #0  pager_end_transaction (pPager=0x4031fdb8, hasMaster=0)
            at ../src/pager.c:1420
            1420    ../src/pager.c: No such file or directory.
   in ../src/pager.c
            (gdb) where
            #0  pager_end_transaction (pPager=0x4031fdb8, hasMaster=0)
            at ../src/pager.c:1420
            #1  0xb702375c in sqlite3PagerCommitPhaseTwo (pPager=0x4031fdb8)
            at ../src/pager.c:4811
            #2  0xb700761e in sqlite3BtreeCommitPhaseTwo (p=0x4031e1c0)
            at ../src/btree.c:2427
            #3  0xb70436e8 in sqlite3VdbeHalt (p=0x40f695f8) at 
../src/vdbeaux.c:1269
        #4  0xb703c145 in sqlite3VdbeExec (p=0x40f695f8) at ../src/vdbe.c:806
        #5  0xb7041948 in sqlite3_step (pStmt=0x40f695f8) at 
../src/vdbeapi.c:477

and below is the codes:

ret = sqlite3_bind_text(pstmt_is, 1, s, -1, SQLITE_STATIC); 
..
  ret = sqlite3_bind_int(pstmt_is, 9, ts);
  rc = sqlite3_step (pstmt_is);
  if (rc == SQLITE_DONE) {
    rc = 0;
  } else {
  }
  sqlite3_reset(pstmt_is);
  ret = sqlite3_exec(sqlite, "END", NULL, NULL, NULL);
  ret = sqlite3_exec(sqlite, "COMMIT", NULL, NULL, NULL);
Is that the problem with the "END" end of transaction following the "COMMIT" 
and should not have the sqlite3_reset before "END"?
Thanks,
JP


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


Re: [sqlite] what is the right cache_size for sqllite version 3.5.9

2009-04-20 Thread Joanne Pham
Thanks! I will do so.
JP





From: P Kishor <punk.k...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Monday, April 20, 2009 5:18:27 PM
Subject: Re: [sqlite] what is the right cache_size for sqllite version 3.5.9

On Mon, Apr 20, 2009 at 7:13 PM, Joanne Pham <joannekp...@yahoo.com> wrote:
> I haven't test with different sizes at all?

In other words, what Roger is gently trying to tell you is to test
yourself before asking. You are the best judge of your conditions,
your machines, your application. Testing is the surest way to find out
the most appropriate answer for your situation.

Change the cache_size then test. Then change again and test. Soon you
will know the answer. You can then come back and ask the list,
providing results of your test, and then folks might be able to guide
to a better solution.



> JP
>
>
>
>
> 
> From: Roger Binns <rog...@rogerbinns.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Monday, April 20, 2009 5:11:48 PM
> Subject: Re: [sqlite] what is the right cache_size for sqllite version 3.5.9
>
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> Joanne Pham wrote:
>> It it the right size for the cache_size? My applications have a lot of 
>> writes operations and can be up to millions rows per minutes.
>
> What results did you get when you did your testing with different sizes?
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkntDz0ACgkQmOOfHg372QShOgCghMA55nGhZJTj4EvjFNZ4sf7c
> XKQAnjL5MLb6W4rkNfBJu3mS6nEdCAtP
> =Vfdz
> -END PGP SIGNATURE-
> ___
> 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



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


Re: [sqlite] what is the right cache_size for sqllite version 3.5.9

2009-04-20 Thread Joanne Pham
I haven't test with different sizes at all?
JP





From: Roger Binns <rog...@rogerbinns.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Monday, April 20, 2009 5:11:48 PM
Subject: Re: [sqlite] what is the right cache_size for sqllite version 3.5.9

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Joanne Pham wrote:
> It it the right size for the cache_size? My applications have a lot of writes 
> operations and can be up to millions rows per minutes.

What results did you get when you did your testing with different sizes?

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

iEYEARECAAYFAkntDz0ACgkQmOOfHg372QShOgCghMA55nGhZJTj4EvjFNZ4sf7c
XKQAnjL5MLb6W4rkNfBJu3mS6nEdCAtP
=Vfdz
-END PGP SIGNATURE-
___
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] what is the right cache_size for sqllite version 3.5.9

2009-04-20 Thread Joanne Pham
Hi All,
I am currently using sqlite 3.5.9 and I have set the cache_size as below:
 sqlSt = sqlite3_exec(pDb, "PRAGMA cache_size = 2000 ", NULL, 0, ); 
It it the right size for the cache_size? My applications have a lot of writes 
operations and can be up to millions rows per minutes.
Thanks,
JP


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


Re: [sqlite] Need help with the SQL statement.

2009-04-16 Thread Joanne Pham
Thanks a ton Igor!
It worked. Your help is greatly appreciated.
Thanks,
JP





From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Thursday, April 16, 2009 4:52:28 AM
Subject: Re: [sqlite] Need help with the SQL statement.

"Joanne Pham" <joannekp...@yahoo.com>
wrote in message news:348376.69121...@web90302.mail.mud.yahoo.com
> 20657220 is number of minutes in GMT time zone.
> So we need to convert to second by 20657220 *60.
> select datetime(20657220*60, 'unixepoch','localtime' );
> will be 2009-04-11 00:00:00

In this case, this should work:

strftime('%s', date(startTime*60, 'unixepoch', 'localtime'), 'utc')/60

You convert your UTC timestamp to localtime, strip time portion (by way 
of date() function), then convert the result back to UTC (by way of 
strfrime(..., 'utc'). This way you'll get a UTC timestamp that 
corresponds to midnight local time of the same calendar date.

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] Need help with the SQL statement.

2009-04-16 Thread Joanne Pham
Hi Igor,
20657220  is number of minutes in GMT time zone.
So we need to convert to second by 20657220 *60.
select datetime(20657220*60, 'unixepoch','localtime' ); 
will be 2009-04-11 00:00:00
Thanks for the hlep Igor
JP





From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Wednesday, April 15, 2009 9:17:09 PM
Subject: Re: [sqlite] Need help with the SQL statement.

"Joanne Pham" <joannekp...@yahoo.com>
wrote in message news:872428.4795...@web90308.mail.mud.yahoo.com
> But the first row (20657220 1 2 101 -- this is 2009-04-11 00:00:00)
> may not be there in the dailyDataTable so min(startTime) won't work
> in this case. Any idea Igor?

I don't quite see how 20657220 can represent midnight (of any day) when 
it's not a multiple of 24*60=1440. What epoch are you counting from? 
This:

select datetime(20657220*60, 'unixepoch');

produces 2009-04-11 07:00:00 for me.

Normally, I'd expect something like "startTime / 1440 * 1440" to work 
(this simply rounds down to nearest multiple of 1440). But I guess I 
don't understand your time representation conventions.

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] Need help with the SQL statement.

2009-04-15 Thread Joanne Pham
Thanks a lot for respond my email!
But the first row (20657220 1 2 101 -- this is 2009-04-11 00:00:00) may not be 
there in the dailyDataTable so min(startTime) won't work in this case.
Any idea Igor?
Thanks
JP





From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Wednesday, April 15, 2009 7:44:48 PM
Subject: Re: [sqlite] Need help with the SQL statement.

"Joanne Pham" <joannekp...@yahoo.com>
wrote in message news:594788.4966...@web90305.mail.mud.yahoo.com
> Hi All,
> I have the following table(dailyDataTable) as defined below
> startTime INTEGER : number of minutes in GMT time
> appId INTEGER : application Id
> remoteId INTEGER : server id
> proxyCount INTEGER
> This table can have up to 24 hours as below: (this table can have
> only few rows but it can have up to 24 hours).
> startTime appId remoteId proxyCount
> 20657220 1 2 101 -- this is 2009-04-11 00:00:00
> 20657280 1 2 105 -- this is 2009-04-11 01:00:00|
> ...
> 20658540 1 2 101 -- this is 2009-04-11 22:00:00
> 20658600 1 2 105 -- this is 2009-04-11 23:00:00
>
> I need to take these data and insert into another
> table(weeklyDataTable) with the following sql statement:
> 1) login to weeklyDB
> 2) Run the following sql statement
> ATTACH DATABASE 'dailyDB' as DailyDB; insert into weeklyDataTable
> select (strftime('%s',date(startTime * 60,'unixepoch')))/60 , appId,
> remoteId, sum(proxyCount ) from DailyDB.dailyDataTable group by
> appId, remoteId ; DETACH DATABASE DailyDB; "
>
> Result below in weeklyDataTable
> 20656800 1 2 2386| -- this 2009-04-10 17:00:00
> But this is wrong I want to have this row below in the
> weeklyDataTable as below.
> 20657220 1 2 2386| -- this 2009-04-11 00:00:00

Perhaps something like this:

insert into weeklyDataTable
select min(startTime), appId, remoteId, sum(proxyCount )
from DailyDB.dailyDataTable
group by appId, remoteId;

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


[sqlite] Need help with the SQL statement.

2009-04-15 Thread Joanne Pham
Hi All,
I have the following table(dailyDataTable) as defined below
  startTime INTEGER : number of minutes in GMT time
  appId INTEGER  : application Id
  remoteId  INTEGER : server id
  proxyCount INTEGER 
This table can have up to 24 hours as below: (this table can have only few rows 
but it can have up to 24 hours).
 startTime   appId   remoteId  proxyCount 
20657220    1        2                 101                        
-- this is 2009-04-11 00:00:00
20657280    1        2          105                   
-- this is 2009-04-11 01:00:00|
...
20658540    12   101                    
   -- this is 2009-04-11 22:00:00
20658600   12       105                         
  -- this is 2009-04-11  23:00:00
 
I need to take these data and insert into another table(weeklyDataTable) with 
the following sql statement:
1) login to weeklyDB
2) Run the following sql statement 
ATTACH DATABASE 'dailyDB'  as DailyDB; insert into weeklyDataTable select 
(strftime('%s',date(startTime * 60,'unixepoch')))/60 , appId, remoteId, 
sum(proxyCount ) from DailyDB.dailyDataTable group by appId, remoteId ; DETACH 
DATABASE DailyDB; "
 
Result below in weeklyDataTable
 20656800   1        2        2386|   -- this 2009-04-10  17:00:00
But this is wrong I want to have this row below in the weeklyDataTable as below.
20657220   1        2        2386|   -- this 2009-04-11 00:00:00
 
Basically I want to have one row in the weeklyDataTable which have the 00:00:00 
for hourly part.
Please help. Your help is greatly appreciate.
Thanks in advance,
JP


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


Re: [sqlite] PRAGMA read_uncommitted = 1

2009-04-14 Thread Joanne Pham
Thanks Igor,
I called this function rc = sqlite3_enable_shared_cache(1);  in my main program 
to enable the share_cache so if this process is opened any connections then 
these connections will be using share-cache right? Sorry for too many questions.
Thanks,
JP





From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Tuesday, April 14, 2009 1:29:08 PM
Subject: Re: [sqlite] PRAGMA read_uncommitted = 1

Joanne Pham <joannekp...@yahoo.com> wrote:
> I have another question : how to enabled shared-cache mode for the
> connection? Thanks,

http://sqlite.org/sharedcache.html

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] PRAGMA read_uncommitted = 1

2009-04-14 Thread Joanne Pham
Hi Igor,
I have another question : how to  enabled shared-cache mode for the connection?
Thanks,
JP





From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Tuesday, April 14, 2009 12:06:29 PM
Subject: Re: [sqlite] PRAGMA read_uncommitted = 1

Joanne Pham <joannekp...@yahoo.com> wrote:
> I have read this document and it stated that if a database connectin
> in read-uncommitted mode does not attempt to obtain reaad-locks
> before reading from database tables.
> So in this case if the connection in read-uncommited modes then it
> allows the follwing :
> - concureently reads
> - also allows read if there is a write-lock
> right?

Yes. But this all only applies to connections that share cache.

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


[sqlite] PRAGMA read_uncommitted = 1

2009-04-13 Thread Joanne Pham
Hi All,
I have set my database connection to "PRAGMA read_uncommitted = 1".Is this 
allow the dirty read?
Thanks,
JP


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


[sqlite] when A table in the database is locked

2009-04-09 Thread Joanne Pham
Hi All,
I have seen the define for SQLITE_LOCKED as below
    #define SQLITE_LOCKED   6   /* A table in the database is locked */
I thought the SQLITE is database locking so I was wondering when the table in 
the database is locked?
Thanks,
JP



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


Re: [sqlite] what is the default for the commit when the connection is opened.

2009-04-09 Thread Joanne Pham
Thank a lot Jay





From: Jay A. Kreibich 
To: General Discussion of SQLite Database 
Sent: Wednesday, April 8, 2009 1:22:57 PM
Subject: Re: [sqlite] what is the default for the commit when the connection is 
opened.

On Wed, Apr 08, 2009 at 10:12:15PM +0200, Martin Engelschalk scratched on the 
wall:
> Hi,
> 
> sqlite does not know an "auto commit".

  Actually, that's what the default mode is called.  You're either in
  "autocommit" mode or you're in a transaction.  Starting a transaction
  turns off autocommit and committing/rolling back a transaction turns
  it back on.

  There is even an API call to ask what state things are in:

    int sqlite3_get_autocommit(sqlite3*);

  See: 
  http://www.sqlite.org/c3ref/get_autocommit.html
  http://www.sqlite.org/lockingv3.html#transaction_control

> If you do not call "begin transaction", then every insert/update/delete 
> statement is wrapped in its own transaction. This is like "auto commit"

  In the context of SQLite this *is* autocommit.  Each statement is
  wrapped in an automatic transaction.

> If you do call "begin transaction", you start a transaction which you 
> have to finish with "commit" or "rollback".

> This does not depend on how you open the database.

  No, but you always start out in autocommit mode, since you're not
  inside an explicit transaction.

  -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
a protractor."  "I'll go home and see if I can scrounge up a ruler
and a piece of string."  --from Anathem by Neal Stephenson
___
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] what is the default for the commit when the connection is opened.

2009-04-08 Thread Joanne Pham
Hi All,
When we use the sqlite3_open_v2 to open the database is this defautl to "Auto 
commit" ?
Thanks,
JP


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


[sqlite] Attach the data return an error message

2009-04-08 Thread Joanne Pham
Hi all,
I had the application using sqlite and executing the following sql statement:
executeStmt: Error in executing the statment database TCPADDB is already in 
use. Error St = 1 , stmt =   ATTACH DATABASE 
\'/opt/phoenix/monitor/TCPFlowCurDayDB\'  as TCPADDB; insert into tcpFlowTable 
select (strftime(\'%s\',date(startTime * 60,\'unixepoch\')))/60 , appId, 
remoteId, sum(ptFlowCountAgv) ,sum(proxyFlowCountAgv ), sum(ptFlowCountDiff) , 
sum(proxyRequestCountDiff) , sum(proxyFlowCountDiff) , 
sum(failedToProxyCountDiff ) from TCPADDB.tcpFlowTable  group by appId, 
remoteId ; DETACH DATABASE TCPADDB ; 
 
The error message return back is the database(TCPADDB) is alreay in use but I 
have checked the codes and didn't see any connection is opened for this 
database so what is the problem here. Please give some hints where to look in 
the codes to find this problem. I didn't see any connection is currently opened 
for this database at the time the application executing above sql statement. 
Any help is greatly appreciated.
Thanks,
JP


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


Re: [sqlite] Database handle(sqlite3_stmt)

2009-04-07 Thread Joanne Pham
Thanks Igor!
Just a question. I always used two different statements handle: one for insert 
and one for select for Sqlserver as well as Sqlite. 
Thanks again for responding my email.
JP





From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Monday, April 6, 2009 8:10:58 PM
Subject: Re: [sqlite] Database handle(sqlite3_stmt)

"Joanne Pham" <joannekp...@yahoo.com>
wrote in message news:677762.12434...@web90302.mail.mud.yahoo.com
> Can we use one statement handle for both insert and select?

Of course not. When you call sqlite3_prepare, you pass the query text 
and get back the statement handle. The query can't begin both with 
SELECT and with INSERT, obviously.

Why do you feel it would be beneficial to somehow "reuse" a statement 
handle for two different queries? What are you trying to achieve?

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] Database handle(sqlite3_stmt)

2009-04-06 Thread Joanne Pham
Sorry for not make it clear!
I am talking about statement handle not the database handle.
Can we use one statement handle for both insert and select?
Thanks,
JP





From: Igor Tandetnik <itandet...@mvps.org>
To: sqlite-users@sqlite.org
Sent: Monday, April 6, 2009 4:02:32 PM
Subject: Re: [sqlite] Database handle(sqlite3_stmt)

Joanne Pham <joannekp...@yahoo.com> wrote:
> Is sqlite allowing to use the same datbase handle(sqlite3_stmt) for
> both read and insert operation.

Are you talking about database handle (sqlite3*) or statement handle 
(sqlite3_stmt*)? Your question is confusing.

Yes, you can issue both SELECT and INSERT statements over the same 
connection. Of course, each individual statement is either SELECT or 
INSERT (or UPDATE or DELETE and so on), it can't be both (though INSERT 
statements can involve sub-selects).

> Basislly onehandle(sqlite3_stmt) is inside the transaction which
> hasn't commited yet and the same handle is used for the read of the
> same database. Is this ok to use one handle for both operations(read
> and write).

Yes.

> For Sqlserver it didn't allow one handle for both read and
> write(insert)

It's been a long time since I last dealt with MS SQL Server, but I don't 
believe this statement is correct.

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


[sqlite] Database handle(sqlite3_stmt)

2009-04-06 Thread Joanne Pham
Hi All,
Is sqlite allowing to use the same datbase handle(sqlite3_stmt) for both read 
and insert operation.
Basislly onehandle(sqlite3_stmt) is inside the transaction which hasn't 
commited yet and the same handle is used for the read of the same database. Is 
this ok to use one handle for both operations(read and write). 

For Sqlserver it didn't allow one handle for both read and write(insert) but I 
think the SQLite database will be the same behavior right?
Thanks,
JP



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


[sqlite] crash on sqlite3 mutex

2009-04-06 Thread Joanne Pham
Hi all,
I have the application is used sqlite 3.5.9 and the program is crashed with the 
following strack trace:
#0  0x46b35300 in pthread_mutex_lock () from /lib/libpthread.so.0
#1  0xb6def162 in sqlite3_mutex_enter (p=0x9aca00b6) at ../src/mutex_unix.c:192
#2  0xb6e1ab96 in sqlite3_exec (db=0xb621a8f3,
    zSql=0xb74117e9 " DETACH DATABASE  CDB ; ", xCallback=0, pArg=0x0,
    pzErrMsg=0xb62219d0) at ../src/legacy.c:50
#3  0xb73eef5e in MonDb::attachDetachDB (this=0xb6220b04,
    cStmt=0xb74117e9 " DETACH DATABASE  CDB ; ")
 ..
I don't know why it crashed on sqlite3_exec and why the pArg=0x0. Do you have 
any suggestion that I need to look at in the codes to able to nail down the 
problem.
Thanks,
JP


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


Re: [sqlite] select the first 2 rows

2009-04-01 Thread Joanne Pham
Thanks Eric.
Joanne

 




From: Eric Minbiole 
To: General Discussion of SQLite Database 
Sent: Wednesday, April 1, 2009 12:02:18 PM
Subject: Re: [sqlite] select the first 2 rows

> Hi all,
> I have a big table and I want only select the first 2 rows.
> I have tried this :
> select top 2 from table;
> but it doesn't work! Any help please.
> JP

Use a LIMIT clause instead of TOP:

SELECT * FROM table LIMIT 2;

http://www.sqlite.org/lang_select.html
___
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] Upgrade from 3.5.9 to 3.6.11

2009-03-13 Thread Joanne Pham
Hi All,
We have a application using SQLite 3.5.9 now and we will be releasing this 
product in June.
 I am think about upgrading SQLite from 3.5.9 to SQLite 3.6.11 but I don't know 
what are the impact for the application and is it worth to upgrade SQLite to 
newest one before the product is releaseed. Would like to have your input on 
this. 
Thank in advance,
JP


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


Re: [sqlite] select statement - Need help

2009-03-08 Thread Joanne Pham
Thanks! I worked!

Select remoteId, table.hostname, lastUpdateTime from table,
        ( Select hostname, Max(lastUpdateTime) max_utime, count(*) cnt from 
table                group by hostName) host_max
        where table.hostname = host_max.hostname
                and table.lastUpdateTime = max_utime
                and cnt > 1 ;

t.hostName should be table.hostName


Thanks a lot Adler for the help.
JP




From: Joanne Pham <joannekp...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Sunday, March 8, 2009 1:44:01 PM
Subject: Re: [sqlite] select statement - Need help

It gave me the syntax error!
JP





From: "Adler, Eliedaat" <ead...@nds.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Sunday, March 8, 2009 1:08:51 PM
Subject: Re: [sqlite] select statement - Need help

By side-effect the following statement should give those values:

        Select remoteId, hostName, max(lastUpdateTime) from
        (select * from table order by hostName, lastUpdateTime)
        Group by hostName having count(*) > 1 ;

The outer select will return the last row processed by aggregate function 
max(lastUpdateTime) - i.e. the last row for each group.
The internal select order guarantees that row will have max(lastUpdateTime)

The more correct SQL would be something like:

        Select remoteId, t.hostname, lastUpdateTime from table,
        ( Select hostname, Max(lastUpdateTime) max_utime, count(*) cnt from 
table                group by hostName) host_max
        where table.hostname = host_max.hostname
                and table.lastUpdateTime = max_utime
                and cnt > 1 ;

Eli


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joanne Pham
Sent: Sunday, March 08, 2009 9:45 PM
To: General Discussion of SQLite Database
Subject: [sqlite] select statement - Need help

Hi All,
I have the folowing table which has the following data for example:
    remoteId    hostName        lastUpdateTime 
    1                        host1                19
    2                      host1                11
    3                        host2                22
    4                        host3                33
    5                        host4                49
    6                        host4                44 So if I ran this 
statement below:
    select * from table group by hostName having count(*) > 1; I got the 
following rows:
    2 host1 11
  6 host4  44

But I want the rows which have bigger lastUpdateTime if hostName has duplicate 
row.
So I want to return:

  1 host1 19
  2 host4  49

Would like to have sql statement to return the rows above.
Thanks,
JP




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

This e-mail is confidential, the property of NDS Ltd and intended for the 
addressee only. Any dissemination, copying or distribution of this message or 
any attachments by anyone other than the intended recipient is strictly 
prohibited. If you have received this message in error, please immediately 
notify the postmas...@nds.com and destroy the original message. Messages sent 
to and from NDS may be monitored. NDS cannot guarantee any message delivery 
method is secure or error-free. Information could be intercepted, corrupted, 
lost, destroyed, arrive late or incomplete, or contain viruses. We do not 
accept responsibility for any errors or omissions in this message and/or 
attachment that arise as a result of transmission. You should carry out your 
own virus checks before opening any attachment. Any views or opinions presented 
are solely those of the author and do not necessarily represent those of NDS.

To protect the environment please do not print this e-mail unless necessary.

NDS Limited Registered Office: One London Road, Staines,Middlesex TW18 4EX, 
United Kingdom. A company registered in England and Wales Registered no. 
3080780 VAT no. GB 603 8808 40-00
___
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] select statement - Need help

2009-03-08 Thread Joanne Pham
It gave me the syntax error!
JP





From: "Adler, Eliedaat" <ead...@nds.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Sunday, March 8, 2009 1:08:51 PM
Subject: Re: [sqlite] select statement - Need help

By side-effect the following statement should give those values:

        Select remoteId, hostName, max(lastUpdateTime) from
        (select * from table order by hostName, lastUpdateTime)
        Group by hostName having count(*) > 1 ;

The outer select will return the last row processed by aggregate function 
max(lastUpdateTime) - i.e. the last row for each group.
The internal select order guarantees that row will have max(lastUpdateTime)

The more correct SQL would be something like:

        Select remoteId, t.hostname, lastUpdateTime from table,
        ( Select hostname, Max(lastUpdateTime) max_utime, count(*) cnt from 
table                group by hostName) host_max
        where table.hostname = host_max.hostname
                and table.lastUpdateTime = max_utime
                and cnt > 1 ;

Eli


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joanne Pham
Sent: Sunday, March 08, 2009 9:45 PM
To: General Discussion of SQLite Database
Subject: [sqlite] select statement - Need help

Hi All,
I have the folowing table which has the following data for example:
    remoteId    hostName        lastUpdateTime 
    1                        host1                19
    2                      host1                11
    3                        host2                22
    4                        host3                33
    5                        host4                49
    6                        host4                44 So if I ran this 
statement below:
    select * from table group by hostName having count(*) > 1; I got the 
following rows:
    2 host1 11
  6 host4  44

But I want the rows which have bigger lastUpdateTime if hostName has duplicate 
row.
So I want to return:

  1 host1 19
  2 host4  49

Would like to have sql statement to return the rows above.
Thanks,
JP




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

This e-mail is confidential, the property of NDS Ltd and intended for the 
addressee only. Any dissemination, copying or distribution of this message or 
any attachments by anyone other than the intended recipient is strictly 
prohibited. If you have received this message in error, please immediately 
notify the postmas...@nds.com and destroy the original message. Messages sent 
to and from NDS may be monitored. NDS cannot guarantee any message delivery 
method is secure or error-free. Information could be intercepted, corrupted, 
lost, destroyed, arrive late or incomplete, or contain viruses. We do not 
accept responsibility for any errors or omissions in this message and/or 
attachment that arise as a result of transmission. You should carry out your 
own virus checks before opening any attachment. Any views or opinions presented 
are solely those of the author and do not necessarily represent those of NDS.

To protect the environment please do not print this e-mail unless necessary.

NDS Limited Registered Office: One London Road, Staines,Middlesex TW18 4EX, 
United Kingdom. A company registered in England and Wales Registered no. 
3080780 VAT no. GB 603 8808 40-00
___
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] select statement - Need help

2009-03-08 Thread Joanne Pham
Hi All,
I have the folowing table which has the following data for example:
    remoteId hostName        lastUpdateTime 
    1        host1    19
    2   host111
    3host2    22
    4                        host3    33
    5                        host4                49
    6                    host4                44
So if I ran this statement below:
    select * from table group by hostName having count(*) > 1;
I got the following rows:
    2 host1 11
   6 host4  44

But I want the rows which have bigger lastUpdateTime if hostName has duplicate 
row.
So I want to return:

  1 host1 19 
  2 host4  49

Would like to have sql statement to return the rows above.
Thanks,
JP



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


Re: [sqlite] sql statement to concatinate two rows.

2009-02-17 Thread Joanne Pham
Thank a lot David!
It worked.
This is exatcly sql statement that I want to have.
Once again thanks a ton David,
JP





From: David Baird <dhba...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, February 17, 2009 10:22:22 PM
Subject: Re: [sqlite] sql statement to concatinate two rows.

On Tue, Feb 17, 2009 at 11:16 PM, Joanne Pham <joannekp...@yahoo.com> wrote:
> Hi All,
> I have the select statement as below
> sqlite> select remoteId, hostName , remoteWXType from remoteWXTable order by 
> hostName;
> and the output  is below:
>
> 1|HostName1-T432|2
> 2|HostName2-T421|2
> 3|HostName3-XP|2
> 4|HostName3-XP|2
>
> But I would like the sql statement to return as below:
> (HostName3 has two remoteId so I want to return as one row but two different 
> remoteId as below)
> 1|HostName1-T432|2
> 2|HostName2-T421|2
> 3,4|HostName3-XP|2
> Can you please help to change the sql statement to return the above result 
> set.
> Thanks,
> JP
>

No problem...

SELECT group_concat(remoteId, ','), hostName, remoteWXType
    FROM remoteWXTable
    GROUP BY hostName; -- or remoteWXType ...?

I think group_concat is only supported in moderately recent versions
of sqlite3, so make sure to not be using something 1 or 2 years old.

-David
___
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] sql statement to concatinate two rows.

2009-02-17 Thread Joanne Pham






From: Joanne Pham <joannekp...@yahoo.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, February 17, 2009 10:14:54 PM
Subject: [sqlite] (no subject)

Hi All,
I have the select statement as below 
sqlite> select remoteId, hostName , remoteWXType from remoteWXTable order by 
hostName;
and the output  is below:

1|HostName1-T432|2
2|HostName2-T421|2
3|HostName3-XP|2
4|HostName3-XP|2

But I would like the sql statement to return as below:
(HostName3 has two remoteId so I want to return as one row but two different 
remoteId as below)
1|HostName1-T432|2
2|HostName2-T421|2
3,4|HostName3-XP|2
Can you please help to change the sql statement to return the above result set.
Thanks,
JP


      
___
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] (no subject)

2009-02-17 Thread Joanne Pham
Hi All,
I have the select statement as below 
sqlite> select remoteId, hostName , remoteWXType from remoteWXTable order by 
hostName;
and the output  is below:

1|HostName1-T432|2
2|HostName2-T421|2
3|HostName3-XP|2
4|HostName3-XP|2

But I would like the sql statement to return as below:
(HostName3 has two remoteId so I want to return as one row but two different 
remoteId as below)
1|HostName1-T432|2
2|HostName2-T421|2
3,4|HostName3-XP|2
Can you please help to change the sql statement to return the above result set.
Thanks,
JP


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


[sqlite] sqlite3_finalize() vs sqlite3_reset()

2009-02-13 Thread Joanne Pham
Hi All,
sqlite3_reset()  function is called to reset a prepared statement object back 
to its initial state, ready to be re-executed. So if the sqlite3_step is return 
back SQL_BUSY we need to retry the execution again do I need to call 
sqlite3_reset() before retry to execute again.
 
And after completely fetch all the rows the sqlite3_finalize is need to be call 
to delete the prepare statement right. 
Thanks
JP


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


Re: [sqlite] sqlite3_finalize (pStmt=0x28) at ../src/vdbeapi.c:204

2009-02-13 Thread Joanne Pham
Thanks Dan!
I think I shouldn't call finalizeStmHandle(pReadStmt); before closeReportDB()

Here is the codes:

finalizeStmHandle(pReadStmt); 
 closeReportDB() ; (this function call closeDb().

Part of Stack trace:
#0sqlite3_finalize (pStmt=0x28) at ../src/vdbeapi.c:204
#1  0xb74484b3 in MonDb::closeDb (this=0xb6022e5c) at 
/builds/BLD_6.0B2.10/SRC/phoenix/src/wx/MonAgt/util/MonDb.cpp:80

Here is the functions finalizeStmHandle() and closeReportDB()
void MonDb::finalizeStmHandle(sqlite3_stmt  *)
{
 if (pStmt) {
   sqlite3_finalize(pStmt);
   pStmt = NULL;
 }
}
void closeReportDB() { closeDb(); };

bool MonDb::closeDb()
{
 int sqlSt;
 const char* errMsg;
// sqlite3_stmt *pStmt1;
 if (pDb != NULL) {
  //while( (pStmt = sqlite3_next_stmt(pDb, 0))!=0 ){
   if (pStmt) {
    sqlite3_finalize(pStmt);
    pStmt= NULL;
   }
  //}
  sqlSt = sqlite3_close(pDb);
  if(sqlSt != SQLITE_OK){ 
   errMsg = sqlite3_errmsg(pDb);
   WXLOGE(LOG_ALL_OPT_OFF, WX_MODULE_ID_MONSTATS, 
WX_SUBMOD_ID_MONSTATS_COLLECTOR,
  "%s: Error in closing Monitoring database name: %s. Sqlite error 
message: %s ",__FUNCTION__, name, errMsg);
#ifndef _WINDOWS
   sqlite3_free((char*) errMsg);
#endif
  }
  pDb=NULL;
 }
 return true;
}





From: Dan <danielk1...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Friday, February 13, 2009 9:14:20 AM
Subject: Re: [sqlite] sqlite3_finalize (pStmt=0x28) at ../src/vdbeapi.c:204


On Feb 13, 2009, at 11:24 PM, Joanne Pham wrote:

> Thanks for the respond!
> So how can we find out the pStmt is valid or not. I did check to see  
> if it is not NULL before passing this sqlite3_finalize (pStmt=0x28).  
> To find out the valid handle is touch because the problem couldn't  
> duplicate all the time.
> Your response is greatly appreciated.
> JP

The value 0x28 is almost certainly not a valid address.

Sounds like an uninitialized variable. If you're using linux,
run your program under valgrind and it will tell you the problem.




> 
> From: Dan <danielk1...@gmail.com>
> To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
> Sent: Thursday, February 12, 2009 9:03:12 PM
> Subject: Re: [sqlite] sqlite3_finalize (pStmt=0x28) at ../src/ 
> vdbeapi.c:204
>
>
> On Feb 13, 2009, at 11:49 AM, Joanne Pham wrote:
>
>> Hi All,
>> We have an application is used SQLite 3.5.9 and our program is
>> crashed on
>> "sqlite3_finalize (pStmt=0x28) at ../src/vdbeapi.c:204" and I don't
>> know why it crashed on this line.
>> Do you have any information about why it is crashed on
>> sqlite3_finalize at 204 vdbeapi.
>
> The argument passed to sqlite3_finalize() is invalid. Where is
> it being called from?
>
>
> ___
> 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



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


Re: [sqlite] sqlite3_finalize (pStmt=0x28) at ../src/vdbeapi.c:204

2009-02-13 Thread Joanne Pham
Thanks for the respond!
So how can we find out the pStmt is valid or not. I did check to see if it is 
not NULL before passing this sqlite3_finalize (pStmt=0x28). To find out the 
valid handle is touch because the problem couldn't duplicate all the time.
Your response is greatly appreciated.
JP





From: Dan <danielk1...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Thursday, February 12, 2009 9:03:12 PM
Subject: Re: [sqlite] sqlite3_finalize (pStmt=0x28) at ../src/vdbeapi.c:204


On Feb 13, 2009, at 11:49 AM, Joanne Pham wrote:

> Hi All,
> We have an application is used SQLite 3.5.9 and our program is  
> crashed on
> "sqlite3_finalize (pStmt=0x28) at ../src/vdbeapi.c:204" and I don't  
> know why it crashed on this line.
> Do you have any information about why it is crashed on  
> sqlite3_finalize at 204 vdbeapi.

The argument passed to sqlite3_finalize() is invalid. Where is
it being called from?


___
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] sqlite3_finalize (pStmt=0x28) at ../src/vdbeapi.c:204

2009-02-12 Thread Joanne Pham
Hi All,
We have an application is used SQLite 3.5.9 and our program is crashed on 
"sqlite3_finalize (pStmt=0x28) at ../src/vdbeapi.c:204" and I don't know why it 
crashed on this line.
Do you have any information about why it is crashed on sqlite3_finalize at 204 
vdbeapi.
Thanks in advance for your help.
Thanks,
Joanne


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


[sqlite] Find out what sql statement is locking the database

2009-02-11 Thread Joanne Pham
Hi All,
I have the database for one of our application and one for awhile the database 
is lock(SQL error: database is locked) and I couldn't find out why the datbase 
is locked. I used Sqlite 3.5.9.
So is there anyway to find why the database is locked.
Thanks,
JP:


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


[sqlite] database encrypted

2009-01-28 Thread Joanne Pham
Hi all,
One of our database had problem to run the "schema". The error message below: 
The question is how the database getting to this stats " Error: file is 
encrypted or is not a database"
Thanks,
JP

Below is the error message 

Enter ".help" for instructions
sqlite> .schema
Error: file is encrypted or is not a database
sqlite> 



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


Re: [sqlite] Open the database - Creating the empty database

2008-12-15 Thread Joanne Pham
Thanks for the respond.
I will test for the existence of the file before trying to open it.
Once again thanks,
JP






From: P Kishor <punk.k...@gmail.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Monday, December 15, 2008 10:43:57 AM
Subject: Re: [sqlite] Open the database - Creating the empty database

On 12/15/08, Joanne Pham <joannekp...@yahoo.com> wrote:
> Hi All,
>  I have this problem about open the database. Here is the detail about the 
>problem.
>
>  Our application have one process to create the database and another process 
>to open the database and creating the report.
>  The problem here is the database is not created but if the second process 
>has tried to access the database then the empty database is created which has 
>the size of 0. So the question is there any way the open database API should 
>return an error message instead of creating the empty database when the second 
>process opens the database.
>
>  Thanks,


A SQLite database is just a file on the hard disk. Test for the
existence of the file before trying to open it.
___
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] Open the database - Creating the empty database

2008-12-15 Thread Joanne Pham
Hi All,
I have this problem about open the database. Here is the detail about the 
problem.
 
Our application have one process to create the database and another process to 
open the database and creating the report.
The problem here is the database is not created but if the second process has 
tried to access the database then the empty database is created which has the 
size of 0. So the question is there any way the open database API should return 
an error message instead of creating the empty database when the second process 
opens the database.
 
Thanks,
JP


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


[sqlite] Error message "database disk image is malformed"

2008-11-17 Thread Joanne Pham
Hi All,
Suddenly on my server whenever I login to the database using the following 
command:
sqlite3 myDB
and
run the .schema I got the error message return back:
"Error: database disk image is malformed"
What is this problem and how to fix it.
Thanks in advance for your help.
Thanks,
Joanne

 




> ___
> 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] Program is crashed on sqlite3_finalize(pStmt);

2008-09-18 Thread Joanne Pham
Thanks Robert!
 
> So your solution is to NULL your pointer after calling finalize() and don't
> call finalize() again if your pointer is NULL.
I think I need to do so.
Again thanks,
JP





- Original Message 
From: Robert Simpson <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Thursday, September 18, 2008 8:54:49 AM
Subject: Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

You can't.  The memory pStmt points to is freed and invalid after the call
to finalize.  Worse, that freed memory could've already been reallocated for
some other purpose by the time the call to finalize() returns control to
you.

So your solution is to NULL your pointer after calling finalize() and don't
call finalize() again if your pointer is NULL.

Robert



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joanne Pham
Sent: Thursday, September 18, 2008 8:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Hi All,
How to check if the pStmt is not finalize so 
sqlite3_finalize(pStmt) can be call again. I think I called
sqlite3_finalize(pStmt) twice so my application is crashed.
Thanks,
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Wednesday, September 17, 2008 10:14:18 AM
Subject: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Hi All,
I have c++ application which is used SQLite 3.5.9.
Occasionally  the application is crash on  
 sqlite3_finalize(pStmt);
Is that true the sqlite3_finalize(pStmt) invoked twice?
How to avoid this crashed problem? How to check if the pStmt is not finalize
so 
sqlite3_finalize(pStmt) can be call again.
Thanks,
JP

 


___
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


___
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] Program is crashed on sqlite3_finalize(pStmt);

2008-09-18 Thread Joanne Pham
Hi All,
How to check if the pStmt is not finalize so 
sqlite3_finalize(pStmt) can be call again. I think I called 
sqlite3_finalize(pStmt) twice so my application is crashed.
Thanks,
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Wednesday, September 17, 2008 10:14:18 AM
Subject: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Hi All,
I have c++ application which is used SQLite 3.5.9.
Occasionally  the application is crash on  
 sqlite3_finalize(pStmt);
Is that true the sqlite3_finalize(pStmt) invoked twice?
How to avoid this crashed problem? How to check if the pStmt is not finalize so 
sqlite3_finalize(pStmt) can be call again.
Thanks,
JP

 


___
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


[sqlite] Program is crashed on sqlite3_finalize(pStmt);

2008-09-17 Thread Joanne Pham
Hi All,
I have c++ application which is used SQLite 3.5.9.
Occasionally  the application is crash on  
 
 


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

sqlite3_finalize(pStmt);
Is that true the sqlite3_finalize(pStmt) invoked twice?
How to avoid this crashed problem? How to check if the pStmt is not finalize so 
sqlite3_finalize(pStmt) can be call again.
Thanks,
JP


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


Re: [sqlite] Convert the MAC address from integer to characters.

2008-09-12 Thread Joanne Pham
Thank a ton Dennis. 
I will try with the view then,
JP



- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Friday, September 12, 2008 11:38:46 AM
Subject: Re: [sqlite] Convert the MAC address from integer to characters.

Joanne Pham wrote:
>  
> Can we convert these sql statement to function/store procedure so we can pass 
> in the number and the return value back the character format.
> For example : ConvertMAC(29672054730752  ) and the return value back : 
> 00:30:48:90:FC:1A

No, you can't create user defined functions in SQL, and SQLite does not 
support stored procedures.

You could create a view that returns the same columns as the base table 
with the mac address column converted to a string using this SQL 
expression.

Given

    create table t (id, mac integer, data text);

You could create a view like this

    create view tv as
    select id,
        substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1)
        as mac_addr,
        data
    from t;

Now you can use the view in all your queries and get the string form of 
the mac address when ever you need it.

You could also do a join to the view using the id column whenever you 
want do get the mac address string in a query that still needs to use 
the original mac address as an integer.

    select data, mac_addr
    from t
    join tv on tv.id = t.id
    where t.mac in (select ...)

HTH
Dennis Cote


___
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] Convert the MAC address from integer to characters.

2008-09-12 Thread Joanne Pham
Thanks a lot Dennis.It worked!
Sorry for asking one more question. I am new to SQLite so sorry for the 
question.
 
Can we convert these sql statement to function/store procedure so we can pass 
in the number and the return value back the character format.
For example : ConvertMAC(29672054730752  ) and the return value back : 
00:30:48:90:FC:1A
Thansk,
JP



- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Friday, September 12, 2008 11:12:32 AM
Subject: Re: [sqlite] Convert the MAC address from integer to characters.

Joanne Pham wrote:
> Sorry! the conversion is correct but it is in reverse order.
> The select statement return :
> 1A:FC:90:48:30:00
>  
> and I checked the MAC Address:
>  
>  00:30:48:90:fc:1a
> How to change it to correct order or may be the number 29672054730752  needs 
> to be reverse.
> Once again thanks for the help,

Simply rearrange the order of the byte pairs.

    select
        substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1)
        as 'MAC Address'
    from t;

Dennis Cote
___
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] Convert the MAC address from integer to characters.

2008-09-12 Thread Joanne Pham
Sorry! the conversion is correct but it is in reverse order.
The select statement return :
1A:FC:90:48:30:00
 
and I checked the MAC Address:
 
 00:30:48:90:fc:1a
How to change it to correct order or may be the number 29672054730752  needs to 
be reverse.
Once again thanks for the help,
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Friday, September 12, 2008 10:58:31 AM
Subject: Re: [sqlite] Convert the MAC address from integer to characters.

Thanks a lto Dennis!
But I got the value in reverse order and not correct with the 1A vs 1B.
Select statement return 
1A:FC:90:48:30:00

but When I checked the MAC address on the server the return value is
 00:30:48:90:fc:1b

May be the number is not correct  29672054730752 ?
Or our conversion is not correct.
Thanks,
JP


- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Friday, September 12, 2008 10:12:37 AM
Subject: Re: [sqlite] Convert the MAC address from integer to characters.

Joanne Pham wrote:
> Thanks a lot for quick respond.
> I would like to have the format as : 00:15:C5:F1:1D:45 
> Please help me how to convert this number 224577687400448 to 
> this format 00:15:C5:F1:1D:45 

This should do the trick. It's not pretty in SQL, and it might make more 
sense to do it in your application's programming language, but it does work.

    select
        substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1)
        as 'MAC Address'
    from t;

This assumes that the table t has an integer column mac that hods the 
mac address to be displayed.

HTH
Dennis Cote
___
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] Convert the MAC address from integer to characters.

2008-09-12 Thread Joanne Pham
Thanks a lto Dennis!
But I got the value in reverse order and not correct with the 1A vs 1B.
Select statement return 
1A:FC:90:48:30:00

but When I checked the MAC address on the server the return value is
 00:30:48:90:fc:1b

May be the number is not correct  29672054730752 ?
Or our conversion is not correct.
Thanks,
JP


- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Friday, September 12, 2008 10:12:37 AM
Subject: Re: [sqlite] Convert the MAC address from integer to characters.

Joanne Pham wrote:
> Thanks a lot for quick respond.
> I would like to have the format as : 00:15:C5:F1:1D:45 
> Please help me how to convert this number 224577687400448 to 
> this format 00:15:C5:F1:1D:45 

This should do the trick. It's not pretty in SQL, and it might make more 
sense to do it in your application's programming language, but it does work.

    select
        substr('0123456789ABCDEF', ((mac >> 44) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 40) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 36) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 32) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 28) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 24) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 20) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 16) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 12) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 8) & 15) + 1, 1) ||
        ':' ||
        substr('0123456789ABCDEF', ((mac >> 4) & 15) + 1, 1) ||
        substr('0123456789ABCDEF', ((mac >> 0) & 15) + 1, 1)
        as 'MAC Address'
    from t;

This assumes that the table t has an integer column mac that hods the 
mac address to be displayed.

HTH
Dennis Cote
___
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] Convert the MAC address from integer to characters.

2008-09-12 Thread Joanne Pham
Thanks a lot for quick respond.
I would like to have the format as : 00:15:C5:F1:1D:45 
Please help me how to convert this number 224577687400448 to 
this format 00:15:C5:F1:1D:45 
Once again thanks a ton,
JP



- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Friday, September 12, 2008 9:22:15 AM
Subject: Re: [sqlite] Convert the MAC address from integer to characters.

Joanne Pham wrote:
> I have this MAC Address as integer 224577687400448. Is there any
> buildin function in SQLite to convert this MAC Address from integer
> to character format (IP Address format) as
> ...

No, there is not, but you can do it using a simple (well maybe not so 
simple) expression using bit manipulation and concatenation.

Note, you say you have a MAC address (i.e. 48 bits) which are usually 
displayed as a set of 6 hex bytes (i.e. XX-XX-XX-XX-XX-XX) not in the 
dotted quad format used for IP addresses (which are only 32 bits in 
IPv4). Which do you really have, and what format do you really want to 
use to display it?

Dennis Cote
___
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] Convert the MAC address from integer to characters.

2008-09-12 Thread Joanne Pham
Hi All,
I have this MAC Address as integer 224577687400448. Is there any buildin 
function in SQLite to convert this MAC Address from integer to character 
format (IP Address format) as ...
Thanks,
JP





___
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] Crashed on sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, );

2008-09-10 Thread Joanne Pham


Hi,
I am currently using 3.5.9. Thanks for the respond
JP

- Original Message 
From: Dennis Cote <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Wednesday, September 10, 2008 10:46:17 AM
Subject: Re: [sqlite] Crashed on sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", 
NULL, 0, );

Joanne Pham wrote:
> Any idea about these error messages:
>  0xb6f67ca5 in enterMem () at ../src/mem1.c:66
>    66        sqlite3_mutex_enter(mem.mutex);
>    Current language:  auto; currently c

What version of sqlite are you using? Line 66 in mem.c is a comment in 
the current version.

Dennis Cote
___
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] Crashed on sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, );

2008-09-10 Thread Joanne Pham
Hi All,
Any idea about these error messages:
  0xb6f67ca5 in enterMem () at ../src/mem1.c:66
    66    sqlite3_mutex_enter(mem.mutex);
    Current language:  auto; currently c
Your help is greatly appreciated.
Thanks,
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Tuesday, September 9, 2008 6:07:59 PM
Subject: [sqlite] Crashed on sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 
0, );



Hi all,
I had a function to open the database and set some database properties as below:
 sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, ); 
and this function is crashed on some of the database but not all and return the 
message as below:
    Program received signal SIGSEGV, Segmentation fault.
    0xb6f67ca5 in enterMem () at ../src/mem1.c:66
    66    sqlite3_mutex_enter(mem.mutex);
    Current language:  auto; currently c
I have clue what is going on here.
Your help is greatly appreciated.
Thanks,
JP


___
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


[sqlite] Crashed on sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, );

2008-09-09 Thread Joanne Pham


Hi all,
I had a function to open the database and set some database properties as below:
 sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, ); 
and this function is crashed on some of the database but not all and return the 
message as below:
    Program received signal SIGSEGV, Segmentation fault.
    0xb6f67ca5 in enterMem () at ../src/mem1.c:66
    66    sqlite3_mutex_enter(mem.mutex);
    Current language:  auto; currently c
I have clue what is going on here.
Your help is greatly appreciated.
Thanks,
JP


___
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_bind_int64, sqlite3_bind_int, sqlite_uint64

2008-09-03 Thread Joanne Pham
Thanks Igor!
So I should use the function sqlite3_bind_int64 to bind the variable which has 
the datatype as "long long int" rigtht?
Thanks,
JP



- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, September 3, 2008 4:58:14 PM
Subject: Re: [sqlite] sqlite3_bind_int64, sqlite3_bind_int, sqlite_uint64

Joanne Pham <[EMAIL PROTECTED]> wrote:
> I have read the sqlite'document and found that there are two other
> binding function sqlite3_bind_int64, sqlite_uint64 to bind the
> columns.
> Which function (sqlite3_bind_int64, sqlite_uint64 ) for me to use if
> my datatype is long long int in C++

sqlite_uint64 is a type, not a function.

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


[sqlite] sqlite3_bind_int64, sqlite3_bind_int, sqlite_uint64

2008-09-03 Thread Joanne Pham
Hi All,
I have an application which is used one of the variable is "long long int". 
This variable is used for storing the big number and
I used  sqlite3_bind_int to bind this variable. One for while I have seen the 
negative number in the database for this variable.
It seems like this variable is overloaded. The sqlite3_bind_int didn't work in 
this case.
I have read the sqlite'document and found that there are two other binding 
function sqlite3_bind_int64, sqlite_uint64 to bind the columns.
Which function (sqlite3_bind_int64, sqlite_uint64 ) for me to use if my 
datatype is long long int in C++
Thanks,
JP


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


[sqlite] sqlite3_next_stmt in SQLite 3.5.9

2008-08-22 Thread Joanne Pham

Hi Igor,
I used SQLite versio n 3.5.9.
I read the SQLite online document and the suggession that we need to finalize 
all the prepare statement associated with database connection before closing 
the connection as below 

        while( (pStmt = sqlite3_next_stmt(pDb, 0))!=0 ){
          sqlite3_finalize(pStmt);
      }
      sqlSt= sqlite3_close(pDb);
but the codes didn't return the syntax for sqlite3_next_stmt. Is 
sqlite3_next_stmt is valid command in SQLite 3.5.9
Thanks
JP





___
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] sqlite3_close

2008-08-21 Thread Joanne Pham
Hi Igor,
I used SQLite versio n 3.5.9.
I read the SQLite online document and the suggession that we need to finalize 
all the prepare statement associated with database connection before closing 
the connection as below 

        while( (pStmt = sqlite3_next_stmt(pDb, 0))!=0 ){
          sqlite3_finalize(pStmt);
      }
      sqlSt= sqlite3_close(pDb);
but the codes didn't return the syntax for sqlite3_next_stmt. Is 
sqlite3_next_stmt is valid command in SQLite 3.5.9
Thanks
JP

- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, August 21, 2008 1:52:15 PM
Subject: Re: [sqlite] sqlite3_close

Joanne Pham <[EMAIL PROTECTED]> wrote:
> Is it necessary to call only sqlite3_close(pDb) before open another
> connection. Thanks,

No (though it's not clear why you would want multiple connections open 
at the same time). You can open several connections and close them in 
any order.

But in your program, you seem to store the database handle in the same 
global variable for each openDb call. If you call openDb twice, the 
second handle overwrites the first, so now there's no way to call 
sqlite3_close on the first handle. Hence the leak. The situation is not 
much different from this:

int* p = new int;
p = new int;
delete p;
// the first allocation leaks - the pointer to it is lost.

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_close

2008-08-21 Thread Joanne Pham
Got it!
Thanks a lot for your answer.
JP.



- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, August 21, 2008 1:52:15 PM
Subject: Re: [sqlite] sqlite3_close

Joanne Pham <[EMAIL PROTECTED]> wrote:
> Is it necessary to call only sqlite3_close(pDb) before open another
> connection. Thanks,

No (though it's not clear why you would want multiple connections open 
at the same time). You can open several connections and close them in 
any order.

But in your program, you seem to store the database handle in the same 
global variable for each openDb call. If you call openDb twice, the 
second handle overwrites the first, so now there's no way to call 
sqlite3_close on the first handle. Hence the leak. The situation is not 
much different from this:

int* p = new int;
p = new int;
delete p;
// the first allocation leaks - the pointer to it is lost.

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_close

2008-08-21 Thread Joanne Pham
Is it necessary to call only sqlite3_close(pDb) before open another connection.
Thanks,
JP



- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, August 21, 2008 12:03:58 PM
Subject: Re: [sqlite] sqlite3_close

Joanne Pham <[EMAIL PROTECTED]> wrote:
> I have a question related toSQLite db handle(pDb in my codes).
> I have the function below to open the database connection. I have to
> call sqlite3_close(sqlite3 *)(sqlite3_close(pDb) in my case)
> before open another database connection( by calling openDb) for
> releasing the memory which is used by previous
> sqlite3_open_v2(openDb). Otherwise the protential memory leak will be
> in the codes.
> Your response is greatly appreciated.

So, what's your question?

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


[sqlite] sqlite3_close

2008-08-21 Thread Joanne Pham
Sorry! Resend an email because no subject in previous email.
Again. Your help is greatly appreciated.
Thanks,
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Thursday, August 21, 2008 11:54:13 AM
Subject: [sqlite] (no subject)

Hi All,
I have a question related toSQLite db handle(pDb in my codes).
I have the function below to open the database connection. I have to  call 
sqlite3_close(sqlite3 *)(sqlite3_close(pDb) in my case)
before open another  database connection( by calling openDb) for releasing the 
memory which is used by previous sqlite3_open_v2(openDb). Otherwise the 
protential memory leak will be in the codes.
Your response is greatly appreciated.
JP
MonDb::openDb(const char *dbName){
  int sqlSt;
  const char* errMsg;  
  strcpy(name, dbName); //copy database name to the private name field
  sqlSt = sqlite3_open_v2( name, , SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_CREATE, 0); 
  if(sqlSt != SQLITE_OK){
    errMsg = sqlite3_errmsg(pDb);
    // print out the error message
    sqlite3_free((char*) errMsg);
    return false;
   }
   /* Set database properties for better performance */
   setDbProperties();
  return true;
}


      
___
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] (no subject)

2008-08-21 Thread Joanne Pham
Hi All,
I have a question related toSQLite db handle(pDb in my codes).
I have the function below to open the database connection. I have to  call 
sqlite3_close(sqlite3 *)(sqlite3_close(pDb) in my case)
before open another  database connection( by calling openDb) for releasing the 
memory which is used by previous sqlite3_open_v2(openDb). Otherwise the 
protential memory leak will be in the codes.
Your response is greatly appreciated.
JP
MonDb::openDb(const char *dbName){
  int sqlSt;
  const char* errMsg;  
  strcpy(name, dbName); //copy database name to the private name field
  sqlSt = sqlite3_open_v2( name, , SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_CREATE, 0); 
  if(sqlSt != SQLITE_OK){
    errMsg = sqlite3_errmsg(pDb);
    // print out the error message
    sqlite3_free((char*) errMsg);
    return false;
   }
   /* Set database properties for better performance */
   setDbProperties();
  return true;
}


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


Re: [sqlite] Convert the CURRENT_TIMESTAMP

2008-07-29 Thread Joanne Pham
Hi All,
I still have the problem to set the result of the below statement to variable 
so I can print out mulitple times without the executing the select statement 
over and over again.
If you have a solution/syntax to set variable please share with me.
Thank,
JP
 select '#device local time = ' ||
 (case strftime('%w', d) when '0' then 'SUN' when '1' then 'MON' when '2' then 
'TUE' when '3' then 'WED' when '4' then 'THUR' when '5' then 'FRI' when '6' 
then 'SAT' end)  || ' ' ||
(case strftime('%m', d) when '01' then 'JAN'  when '02' then 'FEB' when '03' 
then 'MAR' when '04' then 'APR' when '05' then 'MAY' when '06' then 'JUN' when 
'07' then 'JUL'  when '08' then 'AUG' when '09' then 'SEP' when '10' then 'OCT' 
when '11' then 'NOV'  when '12' then 'DEC' end) || ' ' ||
strftime('%d %H:%M:%S %Y', d,'localtime') || ', ' 
from (select CURRENT_TIMESTAMP as d)   ;




- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Monday, July 28, 2008 10:57:22 AM
Subject: Re: [sqlite] Convert the CURRENT_TIMESTAMP

Hi,
Thanks for the big help.
Finally I got it worked as expected and the sql statement below to return the 
format as: #device local time = MON JUL 28 10:57:30 2008.
Another question that I have is to set this select statement in the variable so 
I can repeated to print out the variable again and again in different section 
without repeat the long select statement
I would like to set the result of below statement to variable so I print it 
again without repeating the long select statement. Would you please help.
Thanks,
JP

 select '#device local time = ' ||
 (case strftime('%w', d) when '0' then 'SUN' when '1' then 'MON' when '2' then 
'TUE' when '3' then 'WED' when '4' then 'THUR' when '5' then 'FRI' when '6' 
then 'SAT' end)  || ' ' ||
(case strftime('%m', d) when '01' then 'JAN'  when '02' then 'FEB' when '03' 
then 'MAR' when '04' then 'APR' when '05' then 'MAY' when '06' then 'JUN' when 
'07' then 'JUL'  when '08' then 'AUG' when '09' then 'SEP' when '10' then 'OCT' 
when '11' then 'NOV'  when '12' then 'DEC' end) || ' ' ||
strftime('%d %H:%M:%S %Y', d,'localtime') || ', ' 
from (select CURRENT_TIMESTAMP as d)   ;



- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, July 25, 2008 9:21:26 PM
Subject: Re: [sqlite] Convert the CURRENT_TIMESTAMP

"Joanne Pham" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> Hi all ,
> I have the following statement to convert the CURRENT_TIMESTAMP to
> format as
> TUE JULY 25 23:11:13 2008 but I got the empty string. Can you help me
> why the empty string is returned.
> Below is sql statement:
> select
> (case strftime('%w', d) when 0 then 'SUN' when 1 then 'MON’ when 2
> then ‘TUE’ when 3 then ‘WED’ when 4 then ‘THUR’ when 5 then ‘FRI’
> when 6 then ‘SAT’ end) || ‘ ‘ ||
> (case strftime('%m', d) when 1 then 'JAN' when 2 then ‘FEB’ when 3
> then ‘MAR’ when 4 then ‘APR’ when 5 then ‘MAY’ when 6 then ‘JUN’ when
> 7 then ‘JUL’ when 8 then “AUG’ when 9 then ‘SEP’ when 10 then ‘OCT’
> when 11 then ‘NOV’ when 12 then 'DEC' end)
>>> ' ' ||
> strftime('%d %H:%M:%S %Y', d)
> from (select CURRENT_TIMESTAMP as d);

Change strftime('%w', d) to CAST(strftime('%w', d) as INTEGER), and 
similarly for strftime('%m', d). strftime returns a string, which 
doesn't match any condition in the CASE statement, so the statement 
produces NULL, and then the whole expression becomes NULL. To avoid 
this, the result of strftime needs to be converted to integer.

And fix smart quotes ‘’ to plain apostrophe '

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] Convert the CURRENT_TIMESTAMP

2008-07-28 Thread Joanne Pham
Hi,
Thanks for the big help.
Finally I got it worked as expected and the sql statement below to return the 
format as: #device local time = MON JUL 28 10:57:30 2008.
Another question that I have is to set this select statement in the variable so 
I can repeated to print out the variable again and again in different section 
without repeat the long select statement
I would like to set the result of below statement to variable so I print it 
again without repeating the long select statement. Would you please help.
Thanks,
JP

 select '#device local time = ' ||
 (case strftime('%w', d) when '0' then 'SUN' when '1' then 'MON' when '2' then 
'TUE' when '3' then 'WED' when '4' then 'THUR' when '5' then 'FRI' when '6' 
then 'SAT' end)  || ' ' ||
(case strftime('%m', d) when '01' then 'JAN'  when '02' then 'FEB' when '03' 
then 'MAR' when '04' then 'APR' when '05' then 'MAY' when '06' then 'JUN' when 
'07' then 'JUL'  when '08' then 'AUG' when '09' then 'SEP' when '10' then 'OCT' 
when '11' then 'NOV'  when '12' then 'DEC' end) || ' ' ||
strftime('%d %H:%M:%S %Y', d,'localtime') || ', ' 
from (select CURRENT_TIMESTAMP as d)   ;



- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, July 25, 2008 9:21:26 PM
Subject: Re: [sqlite] Convert the CURRENT_TIMESTAMP

"Joanne Pham" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> Hi all ,
> I have the following statement to convert the CURRENT_TIMESTAMP to
> format as
> TUE JULY 25 23:11:13 2008 but I got the empty string. Can you help me
> why the empty string is returned.
> Below is sql statement:
> select
> (case strftime('%w', d) when 0 then 'SUN' when 1 then 'MON’ when 2
> then ‘TUE’ when 3 then ‘WED’ when 4 then ‘THUR’ when 5 then ‘FRI’
> when 6 then ‘SAT’ end) || ‘ ‘ ||
> (case strftime('%m', d) when 1 then 'JAN' when 2 then ‘FEB’ when 3
> then ‘MAR’ when 4 then ‘APR’ when 5 then ‘MAY’ when 6 then ‘JUN’ when
> 7 then ‘JUL’ when 8 then “AUG’ when 9 then ‘SEP’ when 10 then ‘OCT’
> when 11 then ‘NOV’ when 12 then 'DEC' end)
>>> ' ' ||
> strftime('%d %H:%M:%S %Y', d)
> from (select CURRENT_TIMESTAMP as d);

Change strftime('%w', d) to CAST(strftime('%w', d) as INTEGER), and 
similarly for strftime('%m', d). strftime returns a string, which 
doesn't match any condition in the CASE statement, so the statement 
produces NULL, and then the whole expression becomes NULL. To avoid 
this, the result of strftime needs to be converted to integer.

And fix smart quotes ‘’ to plain apostrophe '

Igor Tandetnik 


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


Re: [sqlite] Convert the CURRENT_TIMESTAMP

2008-07-28 Thread Joanne Pham
Thank you so much Jon and Igor! Both are wokred!! Thanks a ton,
JP



- Original Message 
From: Jon Dixon <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Saturday, July 26, 2008 6:12:21 AM
Subject: Re: [sqlite] Convert the CURRENT_TIMESTAMP

Alternatively, enclosing the numbers in single quotes ( ... when '0' then 'SUN' 
when '1' then 'MON' ...) will give you the expected result.

Jon

-Inline Message Follows-

Igor Tandetnik wrote:

"Joanne Pham" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> Hi all ,
> I have the following statement to convert the CURRENT_TIMESTAMP to
> format as
> TUE JULY 25 23:11:13 2008 but I got the empty string. Can you help me
> why the empty string is returned.
> Below is sql statement:
> select
> (case strftime('%w', d) when 0 then 'SUN' when 1 then 'MON’ when 2
> then ‘TUE’ when 3 then ‘WED’ when 4 then ‘THUR’ when 5 then ‘FRI’
> when 6 then ‘SAT’ end) || ‘ ‘ ||
> (case strftime('%m', d) when 1 then 'JAN' when 2 then ‘FEB’ when 3
> then ‘MAR’ when 4 then ‘APR’ when 5 then ‘MAY’ when 6 then ‘JUN’ when
> 7 then ‘JUL’ when 8 then “AUG’ when 9 then ‘SEP’ when 10 then ‘OCT’
> when 11 then ‘NOV’ when 12 then 'DEC' end)
>>> ' ' ||
> strftime('%d %H:%M:%S %Y', d)
> from (select CURRENT_TIMESTAMP as d);

Change strftime('%w', d) to CAST(strftime('%w', d) as INTEGER), and 
similarly for strftime('%m', d). strftime returns a string, which 
doesn't match any condition in the CASE statement, so the statement 
produces NULL, and then the whole expression becomes NULL. To avoid 
this, the result of strftime needs to be converted to integer.

And fix smart quotes ‘’ to plain apostrophe '

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] Convert the CURRENT_TIMESTAMP

2008-07-27 Thread Joanne Pham
Thanks Igor! It worked.
JP




- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Friday, July 25, 2008 9:21:26 PM
Subject: Re: [sqlite] Convert the CURRENT_TIMESTAMP

"Joanne Pham" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> Hi all ,
> I have the following statement to convert the CURRENT_TIMESTAMP to
> format as
> TUE JULY 25 23:11:13 2008 but I got the empty string. Can you help me
> why the empty string is returned.
> Below is sql statement:
> select
> (case strftime('%w', d) when 0 then 'SUN' when 1 then 'MON’ when 2
> then ‘TUE’ when 3 then ‘WED’ when 4 then ‘THUR’ when 5 then ‘FRI’
> when 6 then ‘SAT’ end) || ‘ ‘ ||
> (case strftime('%m', d) when 1 then 'JAN' when 2 then ‘FEB’ when 3
> then ‘MAR’ when 4 then ‘APR’ when 5 then ‘MAY’ when 6 then ‘JUN’ when
> 7 then ‘JUL’ when 8 then “AUG’ when 9 then ‘SEP’ when 10 then ‘OCT’
> when 11 then ‘NOV’ when 12 then 'DEC' end)
>>> ' ' ||
> strftime('%d %H:%M:%S %Y', d)
> from (select CURRENT_TIMESTAMP as d);

Change strftime('%w', d) to CAST(strftime('%w', d) as INTEGER), and 
similarly for strftime('%m', d). strftime returns a string, which 
doesn't match any condition in the CASE statement, so the statement 
produces NULL, and then the whole expression becomes NULL. To avoid 
this, the result of strftime needs to be converted to integer.

And fix smart quotes ‘’ to plain apostrophe '

Igor Tandetnik 


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


  1   2   >