Re: [sqlite] Go to specific row in database
Apologies for not being too clear. Normally, I'd just query a database based on "where" criteria to get results, but that is based on a continually expanding and dynamically changing database. In this case, I have a static (book records) database and I want to select a specific row based on the premise that I might have omitted id numbers so I cannot query the records that way. MySQL and msSql can "seek" to a specific row (line number in a database) and this must be based on some sort of database API that I am unfamiliar with and I wondered to know if there was a generic sql statement way of doing this that would work with sqlite or indeed, any database engine. > To get the row number, you just: select rowid from MyTable where > Condition; "select * from addresses where rowid=1" did the trick for me. Thank you so much for the help. Hope I can help back some day -- View this message in context: http://www.nabble.com/Go-to-specific-row-in-database-tp16367156p16367317.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Go to specific row in database
Hi flakpit, > MySql and msSql have seek commands to go to a( or return) a specific > row in a database base don the internal row number rather than an id > or other identifier. > > Is there a generic SELECT statement that can do this for sqlite or > indeed, any other sql variant? SQL is (of course) a relational database language. As such, it deals with sets and subsets of data, which are inherently unordered, ie order is irrelevant. Most of the time wen someone asks "how do I get row n of the data" it indicates that they're not making the best use of SQL. So, first, my question is: Can you explain a bit ore of the context, preferably with some sample SQL code, in case others on this list can save you a lot of bother by optimizing your SQL and removing the need for row numbers at all. Having said that, there are rare occasions when you do need the row number, usually when interfacing with some other programming environment (and as above this can usually be avoided by doing more in SQL first). To get the row number, you just: select rowid from MyTable where Condition; Tom BareFeet ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Go to specific row in database
MySql and msSql have seek commands to go to a( or return) a specific row in a database base don the internal row number rather than an id or other identifier. Is there a generic SELECT statement that can do this for sqlite or indeed, any other sql variant? Regards. -- View this message in context: http://www.nabble.com/Go-to-specific-row-in-database-tp16367156p16367156.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "Unsupported File Format" from 3.1.3
Someone sent me an sqlite database file. Opening in Mac OS 10.5 with the built-in sqlite 3.4.0, no problems. Opening in Mac OS 10.4 with the built-in sqlite 3.1.3, any query returns sqlite error 1, "unsupported file format". Similar files from other users open in either Mac OS/sqlite version. I'd thought that sqlite3 databases were generally backward- compatible. Is there any way to find out what is "unsupported" by sqlite 3.1.3 in this database? Thanks, Jerry Krinock ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TCL & tester.tcl question
On Mar 28, 2008, at 4:35 PM, Noah Hart wrote:
> A general question for the TCL experts
>
> There is no problem, I'm just trying to understand how TCL works.
>
> In tester.tcl,v 1.79, at line 60, there is the following section:
>
> # Create a test database
> #
> catch {db close}
> file delete -force test.db
> file delete -force test.db-journal
> sqlite3 db ./test.db
> set ::DB [sqlite3_connection_pointer db]
>
> My question is: Why is the last line not "set ::DB db"
>
> What does sqlite3_connection_pointer do?
> Since this is not a tcl verb, or defined by sqlite3, where does this
> get
> defined?
>
There are countless new TCL verbs implemented in C code and found
in SQLite source files whose names begin with "test". The
sqlite3_connection_pointer verb is but one of many.
(BTW, in TCL lingo, one would normally call this a "command" not a
"verb". But if you are more comfortable with "verb", that terminology
works for me too.)
The sqlite3_connection_pointer verb is an anachronism. The various
new TCL verbs that interface to SQLite (example: sqlite3_prepare,
sqlite3_steo, etc.) often require a database connection as a parameter.
Originally, the implementations of these commands required that the
parameter be the hexadecimal representation of the actually sqlite3*
pointer. But when you use the "sqlite3" command to open a database
connection, you get back a TCL object, not a pointer. The
sqlite3_connection_pointer verb would translate the TCL database
object into the appropriate pointer.
The statement:
set ::DB [sqlite3_connection_pointer db]
translates the TCL database object "db" into a hexadecimal pointer
value and stores that value in the global variable "DB". Subsequent
commands in the same script would then use the value as "$::DB".
This is all an anachronism because at this point, most of the other
TCL commands have been upgraded and can accept the TCL database
object directly. So instead of saying:
sqlite3_prepare $::DB ...
we can now say:
sqlite3_prepare db ...
which is much more convenient. However, the test scripts have been
generated incrementally over the past 8 years and most of them have not
been upgraded to take advantage of the new syntax. So there are still
many calls to [sqlite3_connection_pointer] and uses of $::DB even though
they are not needed.
D. Richard Hipp
[EMAIL PROTECTED]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TCL & tester.tcl question
On Fri, Mar 28, 2008 at 01:35:01PM -0700, Noah Hart wrote: I'm not an expert (yet... ;) - but I'm doing some TCL. > sqlite3 db ./test.db > set ::DB [sqlite3_connection_pointer db] > > My question is: Why is the last line not "set ::DB db" It seems, that there's an attempt to assign to global variable "DB" some kind of pointer (which is string value, actually), returned by some function. > What does sqlite3_connection_pointer do? Look for the definition of that function - because it's function, which is using "db" as its parameter, and "[sqlite3_connection_pointer db]" means: "value returned by function sqlite3_connection_pointer, which has processed db". > Since this is not a tcl verb, or defined by sqlite3, where does this get > defined? Somewhere else in tester.tcl? In any other script, "source"-d by tester.tcl? -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TCL & tester.tcl question
A general question for the TCL experts
There is no problem, I'm just trying to understand how TCL works.
In tester.tcl,v 1.79, at line 60, there is the following section:
# Create a test database
#
catch {db close}
file delete -force test.db
file delete -force test.db-journal
sqlite3 db ./test.db
set ::DB [sqlite3_connection_pointer db]
My question is: Why is the last line not "set ::DB db"
What does sqlite3_connection_pointer do?
Since this is not a tcl verb, or defined by sqlite3, where does this get
defined?
Regards - Noah
CONFIDENTIALITY NOTICE:
This message may contain confidential and/or privileged information. If you are
not the addressee or authorized to receive this for the addressee, you must not
use, copy, disclose, or take any action based on this message or any
information herein. If you have received this message in error, please advise
the sender immediately by reply e-mail and delete this message. Thank you for
your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_MEMORY_SIZE=nnn
I think I found the answer to my own question: (1) Pick an arbtrary, huge initial value for nnn in the compilation option SQLITE_MEMORY_SIZE=nnn; (2) Let my app run for awhile; (3) Call sqlite_memory_highwater() to find out the maximum amount of memory that has ever been allocated (i.e. the high water mark); (4) Recompile with SQLITE_MEMORY_SIZE = high_ water_mark + 20% (or whatever). - Richard Richard Klein wrote: > I've been porting 3.5.6 to my company's embedded > platform. I've implemented a custom VFS, and a > custom Mutex subsystem, and these seem to work > just fine. > > I tried to implement a custom Memory subsystem, > but I see that as of 3.5.2 the compilation option > SQLITE_OMIT_MEMORY_ALLOCATION is no longer supported. > > Instead, it is recommended that I use the compilation > option SQLITE_MEMORY_SIZE=nnn, which will cause SQLite > to allocate memory from a static block of storage that > is nnn bytes in size. > > My question is: What is a reasonable value for nnn? > > Thanks, > - Richard > ___ > 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] SQLite with PHP and Apache C Module
My web application is a mix of PHP code and an Apache C Module. Both access the same SQLite3 database. Because of this, I am using the shared version of the PHP SQLite library and the Apache C module is dynamically linking, as well. The Apache C module only connects to the database during an admin request, which does not happen all that much, normally the Apache C module is checking access and serving up images. One of my customers is experiencing a major performance problems. I am running on a Pentium IV 2.8 GHz machine and it can serve up 12 images real fast, my customer has a Duo Core 1.8GHz machine and it is taking 45 seconds! I am thinking that maybe it is the SQLite code that is causing problems. Once I open the database in the Apache C Module, should I keep it open for the life of Apache? I am not right now, I open and close for every query. Is there anything special I should be doing in PHP to optimize the performance? Sam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conversion string -> date
Zbigniew Baniewski <[EMAIL PROTECTED]> wrote:
> On Fri, Mar 28, 2008 at 02:39:23PM -0400, Igor Tandetnik wrote:
>
>> date() function produces a string. You are trying to subtract a
>> number from a string, at which point SQLite converts the string to
>> number ('2008-03-28' becomes 2008) and performs the subtraction
>> (2008 - 14 == 1994).
>
> I don't know the conversion routines details - but shouldn't be more
> proper to convert to "Julian Day" first, and then to make a
> subtraction (when "date involved" has been detected), and - finally -
> to convert back to "Gregorian Day"?
It's just a string. How is SQLite supposed to know this string is
intended to represent a date?
But of course, if you wanted to perform arithmetic on julian days, you
can always use julianday() function - just as I showed.
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conversion string -> date
On Fri, Mar 28, 2008 at 02:39:23PM -0400, Igor Tandetnik wrote:
> date() function produces a string. You are trying to subtract a number
> from a string, at which point SQLite converts the string to number
> ('2008-03-28' becomes 2008) and performs the subtraction (2008 - 14 ==
> 1994).
I don't know the conversion routines details - but shouldn't be more proper
to convert to "Julian Day" first, and then to make a subtraction (when "date
involved" has been detected), and - finally - to convert back to "Gregorian
Day"?
Actually... yes, one can live without that.
--
pozdrawiam / regards
Zbigniew Baniewski
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faulty "date" function
On Fri, Mar 28, 2008 at 11:37:10AM -0700, Scott Baker wrote:
> I think what you want is date math:
>
> sqlite> SELECT date('now','-14 days');
> 2008-03-14
Yes, I wanted date 2 weeks ago. I made a comparison using PostgreSQL, where
it returns the expected date value, and my guess was, that it should have
been working exactly the same way, as the "official syntax" (above), when
I typed "SELECT date('now') - 14" - if it does any date-math anyway, instead
of reporting error.
> Since '2008-03-28' is a string, and you're trying to subtract from that
> it converts it to a integer. '2008-03-28' converts to 2008 as an integer.
H... yes, actually every output of SQLite is a string - but the
conversion - if there's an attempt to convert a value - IMHO could be working
a bit smarter way.
--
pozdrawiam / regards
Zbigniew Baniewski
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faulty "date" function
Zbigniew Baniewski <[EMAIL PROTECTED]> wrote:
> #v+
> SQLite version 3.5.6
> Enter ".help" for instructions
> sqlite> select date('now');
> 2008-03-28
> sqlite> select date('now')-14;
> 1994
date() function produces a string. You are trying to subtract a number
from a string, at which point SQLite converts the string to number
('2008-03-28' becomes 2008) and performs the subtraction (2008 - 14 ==
1994).
You want
select date('now', '-14 days');
or
select date(julianday('now') - 14)
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faulty "date" function
Zbigniew Baniewski wrote:
> #v+
> SQLite version 3.5.6
> Enter ".help" for instructions
> sqlite> select date('now');
> 2008-03-28
> sqlite> select date('now')-14;
> 1994
> sqlite>
I think what you want is date math:
sqlite> SELECT date('now','-14 days');
2008-03-14
All well documented on the wiki:
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Faulty "date" function
Zbigniew Baniewski wrote:
> #v+
> SQLite version 3.5.6
> Enter ".help" for instructions
> sqlite> select date('now');
> 2008-03-28
> sqlite> select date('now')-14;
> 1994
> sqlite>
No I think it's doing exactly what it should. You're asking a very
vague question. My guess is SQLite returns '2008-03-28' for now(),
and then you tell it to -14. Since '2008-03-28' is a string, and
you're trying to subtract from that it converts it to a integer.
'2008-03-28' converts to 2008 as an integer.
2008 - 14 = 1994.
--
Scott Baker - Canby Telcom
RHCE - System Administrator - 503.266.8253
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Faulty "date" function
#v+
SQLite version 3.5.6
Enter ".help" for instructions
sqlite> select date('now');
2008-03-28
sqlite> select date('now')-14;
1994
sqlite>
#v-
Of course, proper answer should be: "2008-03-14" - shouldn't it?
--
pozdrawiam / regards
Zbigniew Baniewski
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] 2.8.17 --> 3.5.6 performance regression
I would guess PRAGMA synchronous. Per documentation: "In SQLite version 2, the default value is NORMAL. For version 3, the default was changed to FULL." Try setting it to NORMAL for v3 tests and see what that does. -- Mike On Thu, Mar 27, 2008 at 11:06 PM, Richard Klein <[EMAIL PROTECTED]> wrote: > I've recently upgraded from SQLite 2.8.17 > to 3.5.6. > > Upon running a test program that measures > the execution time of SQL statements typical > for my application, I've noticed a definite > performance degradation: > > INSERT: 34% slowdown > UPDATE: 47%" > DELETE: 50%" > > Has anyone else noticed this? I haven't done > any profiling of the code (yet) to see where > the time is being spent. > > - Richard > > ___ > 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
