Re: [sqlite] sql statement to update the data in the table
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 To: Joanne Pham ; General Discussion of SQLite Database 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
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 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
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 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
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
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
Thanks Simon for detail explaination about the indexes! JP From: Simon Slavin To: General Discussion of SQLite Database 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
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
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
To: General Discussion of SQLite Database
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
To: General Discussion of SQLite Database
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
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
To: General Discussion of SQLite Database
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
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
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 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 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
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
Thanks a lot Pavel. JP From: Pavel Ivanov To: General Discussion of SQLite Database 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 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
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
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 To: General Discussion of SQLite Database 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
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.
Thanks Igor, So you prefer #1 instead of #2. Thanks, JP From: Igor Tandetnik 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 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, &errMsg) ; > > 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.
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, &pstmt, 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, &errMsg) ;
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 yourtransaction
Thanks Igor! I am talking about "Insert". For Micorosft SQL server I don't need to "binding parameters " inside the loop. Only bind the first time(outside the loop) and inside the loop I just copied the new data to the variable(structure) that binded outside the loop. So for Microsoft SQL server no need to bind (sqlite3_bind_int64 ...) inside the loop only bind the first time which is outside the loop. For Sqlite the binding for insert statement must be inside the loop. Thanks, JP From: Igor Tandetnik To: sqlite-users@sqlite.org Sent: Tuesday, May 12, 2009 3:00:34 PM Subject: Re: [sqlite] Prepared statements must be generated inside yourtransaction Joanne Pham wrote: > 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. The term "bind" may have two different, unrelated meanings in DBMS literature. You can bind (assign values to) query parameters, and you can bind columns in the resultset. In the latter sense, you provide pointers to local variables for each column in the query; when you step through the resultset, values of those columns are stored in those variables. You seem to be thinking of this second meaning of "bind": you prepare a SELECT statement, bind variables to its columns outside the loop, then call Step inside the loop and column values for each row automatically appear in your variables. Note however that this doesn't make any sense for INSERT statements, which don't produce any resultset. As to binding parameters - of course you want to bind different parameters every time you perform an INSERT, unless you actually want to insert multiple rows with the same values (SQLite happily supports that: if you don't re-bind parameters, they retain their previous values). SQLite supports "bind" in the sense of binding parameters, but not in the sense of binding columns. For the latter, you retrieve column values in the current row using sqlite3_column_* API. 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] Prepared statements must be generated inside your transaction
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 To: General Discussion of SQLite Database 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 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
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
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 To: General Discussion of SQLite Database 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 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
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
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" To: General Discussion of SQLite Database 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
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 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 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
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
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" To: General Discussion of SQLite Database 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
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"
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, &errMsg); sqlSt = sqlite3_step(pStmt); .. sqlSt = sqlite3_exec(pDb,"END;", NULL, 0, &errMsg); Then Do I need to sqlSt = sqlite3_exec(pDb,"COMMIT;", NULL, 0, &errMsg) 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
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
Thanks a lot Igor for respond my email.
JP
From: Igor Tandetnik
To: sqlite-users@sqlite.org
Sent: Sunday, May 3, 2009 7:05:52 PM
Subject: Re: [sqlite] sqlite3_finalize(sqlite3_stmt) is this call clean up
thememory
"Joanne Pham"
wrote in message news:111052.72599...@web90308.mail.mud.yahoo.com
> Is sqlite3_finalize(sqlite3_stmt) cleaning up the memory which is
> allocated by sqlite_prepare()?
> I checked the database statement handle before calling
> sqlite3_finalize and after calling this sqlite3_finalize the address
> is the same.
int* p = new int;
printf("Before: %p\n", p);
delete p;
printf("After: %p\n", p);
Try this code - you'll see that it prints the same address twice. Does
this surprise you? Would you take it as a sign that this code somehow
fails to deallocate all the memory it allocates?
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_finalize(sqlite3_stmt) is this call clean up the memory
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
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
Again thanks Igor for detail information about autocommit for the sqlite database connection. Thanks, JP From: Igor Tandetnik 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 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
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 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" 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
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
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
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
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.
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
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
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
Thanks! I will do so. JP From: P Kishor To: General Discussion of SQLite Database 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 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 > To: General Discussion of SQLite Database > 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
I haven't test with different sizes at all? JP From: Roger Binns To: General Discussion of SQLite Database 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
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, &errMsg); 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.
Thanks a ton Igor!
It worked. Your help is greatly appreciated.
Thanks,
JP
From: Igor Tandetnik
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"
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.
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 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" 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.
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
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"
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.
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
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 To: sqlite-users@sqlite.org Sent: Tuesday, April 14, 2009 1:29:08 PM Subject: Re: [sqlite] PRAGMA read_uncommitted = 1 Joanne Pham 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
Hi Igor, I have another question : how to enabled shared-cache mode for the connection? Thanks, JP From: Igor Tandetnik To: sqlite-users@sqlite.org Sent: Tuesday, April 14, 2009 12:06:29 PM Subject: Re: [sqlite] PRAGMA read_uncommitted = 1 Joanne Pham 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
Re: [sqlite] PRAGMA read_uncommitted = 1
Hi Igor, 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? Thanks again for respond my email. JP From: Igor Tandetnik To: sqlite-users@sqlite.org Sent: Monday, April 13, 2009 9:00:12 PM Subject: Re: [sqlite] PRAGMA read_uncommitted = 1 "Joanne Pham" wrote in message news:432996.36668...@web90306.mail.mud.yahoo.com > I have set my database connection to "PRAGMA read_uncommitted = 1". > Is this allow the dirty read? 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
[sqlite] PRAGMA read_uncommitted = 1
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
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.
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.
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
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)
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 To: sqlite-users@sqlite.org Sent: Monday, April 6, 2009 8:10:58 PM Subject: Re: [sqlite] Database handle(sqlite3_stmt) "Joanne Pham" 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)
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 To: sqlite-users@sqlite.org Sent: Monday, April 6, 2009 4:02:32 PM Subject: Re: [sqlite] Database handle(sqlite3_stmt) Joanne Pham 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)
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
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
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] 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 ___ 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
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
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 To: General Discussion of SQLite Database 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" To: General Discussion of SQLite Database 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
It gave me the syntax error! JP From: "Adler, Eliedaat" To: General Discussion of SQLite Database 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
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.
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 To: General Discussion of SQLite Database 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 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.
From: Joanne Pham To: General Discussion of SQLite Database 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)
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()
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
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 *&pStmt)
{
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
To: General Discussion of SQLite Database
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
> To: General Discussion of SQLite Database
> 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
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 To: General Discussion of SQLite Database 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
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
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
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
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 To: General Discussion of SQLite Database 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 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
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"
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);
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 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 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);
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 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);
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.
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
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.
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
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.
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
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
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.
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
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.
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 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.
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, &errMsg);
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 Sent: Wednesday, September 10, 2008 10:46:17 AM Subject: Re: [sqlite] Crashed on sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, &errMsg); 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, &errMsg);
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 Sent: Tuesday, September 9, 2008 6:07:59 PM Subject: [sqlite] Crashed on sqlite3_exec(pDb, "PRAGMA synchronous=OFF ", NULL, 0, &errMsg); 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, &errMsg); 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, &errMsg);
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, &errMsg); 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
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
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
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
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
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
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
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
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, &pDb, 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)
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, &pDb, 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
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
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
