Re: [sqlite] Column values
On Mon, Oct 06, 2008 at 10:57:22PM -0400, Merv scratched on the wall: > Hi > > Will someone please tell me how I can retrieve the value of a column, > i.e. is if integer or varchar etc. > I am accessing an unknown file. If you have an unknown SQLite database file you're trying to poke around in, you'll want to take a look at the sqlite_master table. That has all the SQL used to create the parts of the database. Also useful are commands like PRAGMA table_info(). See http://www.sqlite.org/pragma.html#schema Understand, however, that columns don't have "types" in SQLite, but only type affinities. Each individual value in a column can (and does) have its own unique type. -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
Re: [sqlite] Column values
On 10/6/08, Merv <[EMAIL PROTECTED]> wrote: > Hi > > Will someone please tell me how I can retrieve the value of a column, > i.e. is if integer or varchar etc. > I am accessing an unknown file. typeof(X) http://www.sqlite.org/lang_corefunc.html > Have a super one! > > Merv > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Column values
You're asking for the column type, not the value. If you open the database with sqlite3 there is a command that you can use that shows the SQL commands that created the table. The .help functionality is very descriptive though the actual command escapes me at the moment. Hope this helps --Original Message-- From: Merv Sender: [EMAIL PROTECTED] To: sqlite-users@sqlite.org ReplyTo: General Discussion of SQLite Database Sent: Oct 6, 2008 22:57 Subject: [sqlite] Column values Hi Will someone please tell me how I can retrieve the value of a column, i.e. is if integer or varchar etc. I am accessing an unknown file. Have a super one! Merv ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Timothy A. Sawyer, CISSP Managing Director MBD Solutions Phone: (603) 546-7132 Web: http://www.mybowlingdiary.com Email: [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Column values
Hi Will someone please tell me how I can retrieve the value of a column, i.e. is if integer or varchar etc. I am accessing an unknown file. Have a super one! Merv ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] calculating cached page size for SQLITE_CONFIG_PAGECACHE
Hello list
I wrote a function that attempts to calculate the cached page size for a
given page size, to be passed as the sz parameter for
sqlite3_config(SQLITE_CONFIG_PAGECACHE, buf, sz, N). It appears to work
so far (with 3.6.3) but I'd appreciate if anyone could go over it and
see if they can find any holes, or if it is likely to be broken by
future SQLite releases.
static size_t _get_cached_page_size(size_t page_size)
{
sqlite3 *db = 0;
int rc, size, size_hi;
unsigned char *mem;
/* ALLOCATE MEMORY FOR 3 DOUBLE-SIZE PAGES */
mem = malloc(page_size * 6);
/* CONFIGURE SQLITE PAGE CACHE FOR 3 DOUBLE-SIZE PAGES */
rc = sqlite3_config(SQLITE_CONFIG_PAGECACHE, mem, page_size * 2, 3);
/* INITIALISE SQLITE LIBRARY */
rc = sqlite3_initialize();
/* OPEN TEMP DATABASE */
rc = sqlite3_open(":memory:", &db);
/* SET PAGE SIZE (calls sqlite3_exec() with "PRAGMA
page_size=") */
rc = _pragma_set_int(db, "page_size", (int)page_size);
/* CREATE TEMP TABLE */
rc = sqlite3_exec(db, "CREATE TABLE _temp (data int)", 0, 0, 0);
/* GET LARGEST MEMORY SIZE REQUESTED TO PAGE CACHE */
rc = sqlite3_status(SQLITE_STATUS_PAGECACHE_SIZE, &size, &size_hi,
0);
/* SHUT DOWN SQLITE LIBRARY */
rc = sqlite3_shutdown();
/* FREE MEMORY AND RETURN SIZE ROUNDED UP TO NEAREST 8 BYTES */
free(mem);
size_hi = (size_hi + 7) & ~7;
return size_hi;
}
Cheers,
Dave.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory Resident Database
Ribeiro, Glauber wrote: You probably want to open your in-file database, open the :memory: database, and copy all the data from the file to memory, do your manipulations in memory, then copy back to file when you're done. http://www.sqlite.org/cvstrac/wiki?p=InMemoryDatabase has a simple TCL program to copy a database, which you could adapt to C# If this database is read-only, maybe pragma journal_mode = off would get you enough performance? Oh, of course, I misunderstood the original question. My apologies. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Floating point numbers
> Does anyone knows why floating point numbers are truncated when they are > written or read from the database?! SQLite stores real numbers as 8 byte IEEE floats, which can hold approximately 16 significant digits. See: http://www.sqlite.org/datatype3.html You could get slightly more significant digits by using scaled 64 bit integers-- approximately 18-19 decimal digits. (This comes at the expense of additional code complexity and loss of range.) If you need more than that, you'll need to store numbers as strings (or blobs), and do your own arbitrary precision math as needed. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory databases ...
Mihai Limbasan wrote: > Yes - use ":memory:" (without the double quotes) as the database name > when opening it. Be aware, though, that all tables and their contents > will disappear once you close the database connection. Actually, this brings up the question of the ability to mmap a file, and then point SQLite to the image ... In order to obtain a persistant image, is it possible to to associate a :memory: database with an mmap'ed blob which can be saved between sessions?? Surely this would be useful, no?? Just wondering ... 8-) Cheers, Rob Sciuk ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Floating point numbers
Hi,
Does anyone knows why floating point numbers are truncated when they are
written or read from the database?!
The following code truncates the number so that precision is lost!
rc = sqlite3_exec(db, "create table test_tab (num_row real)", callback, 0,
&zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
rc = sqlite3_exec(db, "insert into test_tab (num_row)
values(1.12345678901234567890)", callback, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
rc = sqlite3_exec(db, "select num_row from test_tab", callback, 0,
&zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
}
The result ist "1.12345678901235"!? Is there any fixed size for the
printf's??? Can I change it without changing the sourcecode?!
Best regards, Andreas Terganov
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite garbage collection in PHP
If I'm doing, as it might be, the following in PHP:
$dbh = new PDO ("sqlite:mydb");
$res1 = $dbh->query ("select ...");
$res11 = $res1->fetchAll (PDO::FETCH_ASSOC);
Does anyone know at what point the variable $res1 becomes free for
re-use? Is it immediately after the assignment to $res11 above
(assuming I don't want to fetch the result set again)?
In re-using these variables ($res1 and $res11), if I simply re-assign
to them, does that cause a memory loss or is the garbage collection
automatic? Or do I need to free up the space by setting them to null
first, or via some function call?
(I had a look at php.net but the doc is a bit sketchy).
Thanks,
--
-- tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite syntax diagrams
I love the diagrams. They are much easier to read (for me) than plain text. To make them searchable, perhaps alt-text would suffice? RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Klemens Friedl Sent: Monday, October 06, 2008 1:59 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite syntax diagrams 2008/10/3 D. Richard Hipp <[EMAIL PROTECTED]>: > http://www.sqlite.org/draft/syntaxdiagrams.html > http://www.sqlite.org/draft/lang.html The diagrams are nice and for some people probably easier to read than plain text BNF* syntax. Although, images have two disadvantages: 1) web search engines cannot extract and therefor not index its content 2) the neither the browser's in-page search function nor a desktop search engine or a grep like tool work either. For example a search for "sqlite SELECT NATURAL JOIN" on your favorite web search engine will list you "http://www.sqlite.org/lang_select.html"; hopefully in one of the first search results. As e.g. "natural" is not in the text content of the new draft (http://www.sqlite.org/draft/lang_select.html) it won't be indexed if this draft become the reality. I suggest to offer both, the images and the BNF syntax. Best regards, Klemens * Backus-Naur Form ___ 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] Memory Resident Database
You probably want to open your in-file database, open the :memory: database, and copy all the data from the file to memory, do your manipulations in memory, then copy back to file when you're done. http://www.sqlite.org/cvstrac/wiki?p=InMemoryDatabase has a simple TCL program to copy a database, which you could adapt to C# If this database is read-only, maybe pragma journal_mode = off would get you enough performance? -Original Message- From: Mihai Limbasan [mailto:[EMAIL PROTECTED] Sent: Monday, October 06, 2008 1:10 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Memory Resident Database marcglennjamon wrote: > Hello guys, > > Is there an option in SQLite to make the database file reside in the > memory during sql transactions for faster access? > I am using the C# language under Mono. > > Thanks in advance, > Marc Glenn > Yes - use ":memory:" (without the double quotes) as the database name when opening it. Be aware, though, that all tables and their contents will disappear once you close the database connection. HTH, Mihai Limbasan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacement for sqlite3_expired?
On Oct 6, 2008, at 1:01 AM, Michael Grigoriev wrote: > Moderator, please feel free to disregard previous email. I figured > it out. Oops. Sorry for letting this through. My moderation policy is to pass any emails that have "SQLite" somewhere in the subject line. I didn't look at the content before approving it 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] SQLite syntax diagrams
I find those diagrams less readable than text version. There are other problems besides my personal preferences: - text cannot be copied from pictures - syntax cannot be viewed with text-only browsers - site loads a little longer (there are still people that don't use broadband) -- Dzwon taniej na zagraniczne komorki! Sprawdz >> http://link.interia.pl/f1f26 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Replacement for sqlite3_expired?
Moderator, please feel free to disregard previous email. I figured it out. Thanks, Michael. On Mon, Oct 6, 2008 at 00:42, Michael Grigoriev <[EMAIL PROTECTED]> wrote: > Hi, > > I noticed that sqlite3_expired is now marked as deprecated. What is > the new suggested mechanism for finding out if a cached statement has > expired? > > PS. I think it would be generally useful to update the Deprecated > Functions page on the website to indicate the replacement for each > deprecated function is. > > Thanks in advance, > Michael. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Replacement for sqlite3_expired?
Hi, I noticed that sqlite3_expired is now marked as deprecated. What is the new suggested mechanism for finding out if a cached statement has expired? PS. I think it would be generally useful to update the Deprecated Functions page on the website to indicate the replacement for each deprecated function is. Thanks in advance, Michael. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [Index] Listing 6001 after 601 and not after 801?
On Sun, 5 Oct 2008 08:52:21 -0600, "Dennis Cote" <[EMAIL PROTECTED]> wrote: >It sounds like you want the account numbers to be sorted as text rather than >numerically, so cast the values to text in the order by clause. > > ... order by cast(account_number as text) ... Thanks guys. An easier solution was simply to change the column definition from INTEGER to VARCHAR, since I didn't actually need the column to be numeric. Sorting works OK now. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compile SQLite3 for MS Windows Driver Kit user-mode application
Hello, Has anybody tried to compile SQLite3 with the MS WDK? The Win32 DLL is not compatible as far as I understand and recompiling with the WDK is necessary. But using the source code as is results in many warnings (mostly conversion errors). The WDK does not tollerate these. Best regards, Björn _ News, entertainment and everything you care about at Live.com. Get it now! http://www.live.com/getstarted.aspx ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
