[sqlite] Functions
Dear All , Was looking on the functions code. Wondered if their's any way I can do something like for select from table do something here loop the field have a array of data may be single row but only single column -- With Best Regards, Vishal Kashyap. http://vishalkashyap.tk
[sqlite] Functions
Hi *, Where I can find the list of the internal functions? for example: datetime() strftime() tks -- Marco Antonio J. Victor Fone: 11 6977-5406 Fax: 11 6973-9772 www.tactor.com.br
Re: [sqlite] Functions
> Was looking on the functions code. Wondered if their's any way I can > do something like > > for select from table > > do something here > > loop > > the field have a array of data may be single row but only single column Hi Vishal -- I'm not sure I understand your syntax. Maybe a more complete example of what you are hoping to do would help? If my guess is right about what you mean, it should be easy to do using a user-defined function and a sub-select. In general, if you can form the loop as part of a SQL statement, the loop part should be easy. And for "do something here", you can define an aggregate function to do what you want. But an actual example would help. --nate
[sqlite] Functions & Keywords
Hello, It is straightforward to define a function in SQLite...such as MY_FUNCTION( ). Is there an equally simple way to define a keyword, i.e. exactly like the function above but without the parentheses? I am trying to expand SQLite, using user-defined functions, to more closely resemble Oracle SQL features. Thanks in advance, Steve Stephen Drew Stephen Drew Stephen Drew Front Office Development Team Front Office Development Team Front Office Development Team Beauchamp Financial Technology Limited tel: +44 (0)20 7469 8636 email: [EMAIL PROTECTED] web: www.bftl.com 85 Gracechurch Street, London, EC3V 0AA, United Kingdom - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Functions
marco wrote: Hi *, Where I can find the list of the internal functions? for example: datetime() strftime() All but the date/time functions are listed in func.c in a table that's part of sqlite3RegisterBuiltinFunctions(); the date/time functions are listed in a similar table in sqlite3RegisterDateTimeFunctions in date.c.
[sqlite] Functions and index
I have hundred thousands of records in this table: CREATE TABLE days ( day_id INTEGER NOT NULL PRIMARY KEY, day_date DATE ); CREATE INDEX day_i ON days (day_date ASC); And then if I run such query: EXPLAIN QUERY PLAN SELECT JULIANDAY(MAX(day_date)) FROM days; 0|0|0|SCAN TABLE days (~100 rows) it will do full table scan instead of use an index. But: EXPLAIN QUERY PLAN SELECT MAX(day_date) FROM days; 0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows) so it is as I expected. Is there any other way to rewrite such kind of queries with functions? The query below uses an index but it is much longer: EXPLAIN QUERY PLAN SELECT JULIANDAY(day_date) FROM days WHERE day_date IN (SELECT MAX(day_date) FROM days); 0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (day_date=?) (~250 rows) 0|0|0|EXECUTE LIST SUBQUERY 1 1|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows) Best regards, Adam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
RE: [sqlite] Functions & Keywords
-Original Message- From: Drew, Stephen [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 5:05 AM To: '[EMAIL PROTECTED]' Subject: [sqlite] Functions & Keywords Hello, It is straightforward to define a function in SQLite...such as MY_FUNCTION( ). Hi Drew, Please excuse the silly question, but how is that done? I spent a while looking for it in the docs the other day, but couldn't find it. And <http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql> http://www.sqlite.org/cvstrac/wiki?p=UnsupportedSql says stored procedures aren't supported, so I gave up the search, but I'd love to know if/how it can be done. -Ken
Re: [sqlite] Functions & Keywords
> Please excuse the silly question, but how is that done? It can't be done in SQL code. You can do it quite easily using C language API calls. These are documented in section 4 of the API documentation at http://www.hwaci.com/sw/sqlite/c_interface.html All of the built in functions in SQLite are implemented using this interface, so the SQLite source provides good examples of implementing real functions using this API. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Functions & Keywords
> -Original Message- > From: Dennis Cote [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 22, 2004 1:58 PM > To: Williams, Ken; 'Drew, Stephen'; [EMAIL PROTECTED] > Subject: Re: [sqlite] Functions & Keywords > > > > Please excuse the silly question, but how is that done? > > It can't be done in SQL code. > > You can do it quite easily using C language API calls. These > are documented > in section 4 of the API documentation at > http://www.hwaci.com/sw/sqlite/c_interface.html Great, it looks fairly simple. I was indeed looking for something I could do using the C API. So after I write & compile the code, how can I tell SQLite to load it? Or do I need to compile it into the sqlite libraries? -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Functions & Keywords
> So after I write & compile the code, how can I tell SQLite to load it? Or > do I need to compile it into the sqlite libraries? No, you install your user defined function using the sqlite_create_function() or sqlite_create_aggreate() calls at runtime. These calls require function pointer arguments. You need to pass a pointer to your C language function(s). Your functions are then called by SQLite when it needs to execute one of your new SQL functions. Again, see the SQLite source, in particular the file func.c, for examples of how to use these calls. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Functions & Keywords
> -Original Message-
> From: Dennis Cote [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 22, 2004 5:23 PM
> To: Williams, Ken; [EMAIL PROTECTED]
> Subject: Re: [sqlite] Functions & Keywords
>
>
> Again, see the SQLite source, in particular the file func.c,
> for examples of
> how to use these calls.
Hmm - all of those functions seem to depend only on their argument input,
making no external calls to the database. How would I implement, for
example, a stored procedure that performs several inserts/selects on several
database tables?
I tried a little experiment with the Perl interface (which uses the
sqlite_create_function() call under the hood), and the function I created
just hangs when it's called. Is SQLite not re-entrant in this way, or is
this a peculiarity of the Perl interface? My code is below.
-Ken
use strict;
use DBI;
my $dbh = DBI->connect('dbi:SQLite:dbname=test', undef, undef,
{RaiseError => 1});
$dbh->do("CREATE TABLE foo (a, b)");
$dbh->do("INSERT INTO foo VALUES (1, 2)");
$dbh->do("INSERT INTO foo VALUES (2, 4)");
$dbh->func( 'get_b', 1,
sub {
warn "Called the function";
my ($x) = $dbh->selectrow_array
("SELECT b FROM foo WHERE a=$_[0]");
return $x
}, 'create_function');
my $a = $dbh->selectall_arrayref
("SELECT a, get_b(a) FROM foo");
print "@$_\n" foreach @$a;
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Functions & Keywords
Williams, Ken wrote: Hmm - all of those functions seem to depend only on their argument input, making no external calls to the database. How would I implement, for example, a stored procedure that performs several inserts/selects on several database tables? I tried a little experiment with the Perl interface (which uses the sqlite_create_function() call under the hood), and the function I created just hangs when it's called. Is SQLite not re-entrant in this way, or is this a peculiarity of the Perl interface? My code is below. That capability was added on 2004-Jan-07. See check-in [1166]. I have no idea whether or not Perl supports the new capability. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] Functions & Keywords
Hello again, So is there no way of replicating the SYSDATE keyword in SQLite? I am not too concerned about the underlying mechanics of what it will do, but it would very useful to be able to call user-defined functions taking no parameters without the parentheses... Thanks, Steve -Original Message- From: Drew, Stephen [mailto:[EMAIL PROTECTED] Sent: Thursday, January 22, 2004 11:05 AM To: '[EMAIL PROTECTED]' Subject: [sqlite] Functions & Keywords Hello, It is straightforward to define a function in SQLite...such as MY_FUNCTION( ). Is there an equally simple way to define a keyword, i.e. exactly like the function above but without the parentheses? I am trying to expand SQLite, using user-defined functions, to more closely resemble Oracle SQL features. Thanks in advance, Steve Stephen Drew Stephen Drew Stephen Drew Front Office Development Team Front Office Development Team Front Office Development Team Beauchamp Financial Technology Limited tel: +44 (0)20 7469 8636 email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> web: www.bftl.com <http://www.bftl.com/> 85 Gracechurch Street, London, EC3V 0AA, United Kingdom
RE: [sqlite] Functions & Keywords
> -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 22, 2004 5:57 PM > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] Functions & Keywords > > > Williams, Ken wrote: > > > > Hmm - all of those functions seem to depend only on their > argument input, > > making no external calls to the database. How would I > implement, for > > example, a stored procedure that performs several > inserts/selects on several > > database tables? > > > > I tried a little experiment with the Perl interface (which uses the > > sqlite_create_function() call under the hood), and the > function I created > > just hangs when it's called. Is SQLite not re-entrant in > this way, or is > > this a peculiarity of the Perl interface? My code is below. > > > > That capability was added on 2004-Jan-07. See check-in [1166]. > > I have no idea whether or not Perl supports the new capability. Hi, I just downloaded sqlite-2.8.11, ran the DBD::SQLite getsqlite.pl script, and tried my test again. It worked. Matt, it would be great if you would issue another release of DBD::SQLite with an updated sqlite distribution. These latest changes make it possible to write some rather effective stored procedures in Perl. -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Functions and index
On 27 June 2011 16:16, hilaner wrote: > I have hundred thousands of records in this table: > > CREATE TABLE days ( > day_id INTEGER NOT NULL PRIMARY KEY, > day_date DATE > ); > > CREATE INDEX day_i ON days (day_date ASC); > > And then if I run such query: > > EXPLAIN QUERY PLAN > SELECT JULIANDAY(MAX(day_date)) FROM days; > 0|0|0|SCAN TABLE days (~100 rows) > > it will do full table scan instead of use an index. > > But: > > EXPLAIN QUERY PLAN > SELECT MAX(day_date) FROM days; > 0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows) > > so it is as I expected. > > Is there any other way to rewrite such kind of queries with functions? > The query below uses an index but it is much longer: > > EXPLAIN QUERY PLAN > SELECT JULIANDAY(day_date) FROM days > WHERE day_date IN (SELECT MAX(day_date) FROM days); > 0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (day_date=?) (~250 rows) > 0|0|0|EXECUTE LIST SUBQUERY 1 > 1|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows) select julianday( ( select max( day_date ) from days ) ); > > Best regards, > Adam Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Functions and index
On 2011-06-27 17:34 Simon Davies wrote: > select julianday( ( select max( day_date ) from days ) ); Of course I tried this, but with a single bracket I got a syntax error. With double bracket it works. Thanks! Adam ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] functions that return tables
Perhaps related to the recent questions about converting rows to
columns, I'm finding the need for user defined aggregate functions
that can return multiple values, or ideally multiple rows of values.
Assume you to determine the highest N values from column. You'd want
a aggregate function like max_n(col, n) that makes one pass through
the table, saving the highest values it sees, and then returning them.
SELECT max_n(col, 3) FROM test;
max
---
10
9
8
My current workaround is to have my function return a comma separated
list of values ("10,9,8"), parse this string in my application, and
generate a new query, but ideally I'd like to do this in one step.
Is there any reasonable way to accomplish this? Or am I left with
defining a new function type that returns a handle to a temp table,
and new parsing logic to wrap the right OP codes around that function?
Thanks!
Nathan Kurz
[EMAIL PROTECTED]
[sqlite] Functions affecting table structure ?
Hi, I am trying to add some additional data with the table structure stored in SQLite. Everything seems to be working fine but when I do an alter query it seems to drop the table. I have made changes in the following functions to handle the addition of the data: sqlite3AlterFinishAddColumn() , sqlite3EndTable(), I have made changes in the following functions to remove the changes during runtime only (sqlite feels its a normal table) sqlite3InitCallback(), execExecSql() I am trying to understand the ALTER queries and which function handles them. Any hint would be immensely appreciated. I have wasted a day or two trying to figure out why is the database dropping the structure when I add a column to an existing table (modified by my code) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Functions embedded in SQL statements
I have the book SQLite by Chris Newman and it has examples of
functions embedded within SQL statements.
For example on page 38 the ifnull() function is used.
SELECT code, ifnull(due_date, 'Ongoing') FROM projects;
Also, on page 81 the strftime() function is used.
SELECT strftime('%m/%d/%Y', '2004-10-31');
The problem I am having is that I cannot find any documentation
on the SQLite documentation page that explains these functions.
What other functions exist? What is the syntax? Where can they
be used in SQL statements? Is there any documentation on these
functions?
My apologies if I have overlooked it.
Thanks.
Re: [sqlite] functions that return tables
Nathan Kurz wrote:
Perhaps related to the recent questions about converting rows to
columns, I'm finding the need for user defined aggregate functions
that can return multiple values, or ideally multiple rows of values.
Assume you to determine the highest N values from column. You'd want
a aggregate function like max_n(col, n) that makes one pass through
the table, saving the highest values it sees, and then returning them.
SELECT max_n(col, 3) FROM test;
max
---
10
9
8
My current workaround is to have my function return a comma separated
list of values ("10,9,8"), parse this string in my application, and
generate a new query, but ideally I'd like to do this in one step.
Is there any reasonable way to accomplish this? Or am I left with
defining a new function type that returns a handle to a temp table,
and new parsing logic to wrap the right OP codes around that function?
Thanks!
Nathan Kurz
[EMAIL PROTECTED]
Hello Nathan,
why not a vector of values ?
Regards,
--
Noël Frankinet
Gistek Software SA
http://www.gistek.net
Re: [sqlite] functions that return tables
On Fri, Nov 18, 2005 at 05:43:01PM +0100, Noel Frankinet wrote:
> >My current workaround is to have my function return a comma separated
> >list of values ("10,9,8"), parse this string in my application, and
> >generate a new query, but ideally I'd like to do this in one step.
>
> why not a vector of values ?
A vector of values (I presume you mean returning a BLOB that is
actually an array of values?) would be great if there was any way to
use these values as part of a JOIN:
SELECT * FROM other_table JOIN (SELECT max_n(col, n) FROM test);
Is there any existing syntax that would allow this? Or would I still
need a new token (EXPLODE) that would convert from the vector to the
component values at the VDBE level?
Also, for my real application I would actually need a vector of rows
(ie, a table), since I want to be able to return not just the value of
max but some identifying id. Here's closer to my actual query:
SELECT matrix_match(base.uid, base.vector, test.uid, test.vector)
FROM vectors AS test, (SELECT uid, vector FROM vectors) AS base
GROUP BY base.uid;
1|123,456,789
2|234,567,890
...
--nate
Re: [sqlite] functions that return tables
Nathan Kurz wrote:
Perhaps related to the recent questions about converting rows to
columns, I'm finding the need for user defined aggregate functions
that can return multiple values, or ideally multiple rows of values.
Assume you to determine the highest N values from column. You'd want
a aggregate function like max_n(col, n) that makes one pass through
the table, saving the highest values it sees, and then returning them.
SELECT max_n(col, 3) FROM test;
max
---
10
9
8
My current workaround is to have my function return a comma separated
list of values ("10,9,8"), parse this string in my application, and
generate a new query, but ideally I'd like to do this in one step.
Is there any reasonable way to accomplish this? Or am I left with
defining a new function type that returns a handle to a temp table,
and new parsing logic to wrap the right OP codes around that function?
Thanks!
Nathan Kurz
[EMAIL PROTECTED]
Nathan,
I don't know of a way to do what you want with a user defined function,
but your example can be solved quite simply using SQL. The following
query will return a table with the required results.
select * from test order by col desc limit 3;
If you have an index on col then it will also be very fast regardless of
the size of the table, if not it will do a single table scan to find
the three maximum values.
HTH
Dennis Cote
Re: [sqlite] functions that return tables
On Fri, Nov 18, 2005 at 04:25:12PM -0700, Dennis Cote wrote: > >Is there any reasonable way to accomplish this? Or am I left with > >defining a new function type that returns a handle to a temp table, > >and new parsing logic to wrap the right OP codes around that function? > > I don't know of a way to do what you want with a user defined function, > but your example can be solved quite simply using SQL. The following > query will return a table with the required results. > > select * from test order by col desc limit 3; > > If you have an index on col then it will also be very fast regardless of > the size of the table, if not it will do a single table scan to find > the three maximum values. Thanks Dennis. Unfortunately, I was only using the max() function as a simplified case to try to show what I wanted. In reality, I need to try to find which which N rows are most similar to a given row in the table based on a custom vector similarity function that compares a blob in the test field against a blob in each of the other rows (as well as some joined in parameters). So a precalculated index is out. Also, I think the part that Igor was pointing out was the "if not it will do a single table scan". His point (which I think is true) is that in if no index is available with the information then a temporary copy of the entire table has to be created and sorted. The 'complex heap approach' is indeed what I'm doing in my user defined aggegate function, as the cost of the temp table creation and sorting is the reason I can't do this as a simple function with an 'ORDER BY'. So I'm still looking for how it could be done with user defined functions, even if that means significantly extending the way that user defined functions are handled in SQLite. Advice on how to do that (or suggestions on better alternatives) greatly appreciated. --nate
[sqlite] implement a few sqlite functions...
Hi guys, first thank you a lot for the great SQLite database. I just want to introduce my work: At the moment I write a little cgi library with C++. The following thins work fine: - parse GET/POST (Query String) - (un)set cookies - file functions - string functions - html output optimation - a little template system - a search engine class Now I want to include some SQLite functions. I started with this [1] piece of code. I added the following includes: #include // for the exit() function which is used by the example #include // for the basic in/output stuff I downloaded this [2] and put it into /usr/lib/sqlite I compiled my programm with this: $ gcc sqltest.cpp -I/usr/lib/sqlite and got the following result: ## linux:/home/christian/Desktop # gcc sqltest.cpp -I/usr/lib/sqlite /tmp/ccsdAdPd.o(.text+0xda): In function `main': : undefined reference to `sqlite_open' /tmp/ccsdAdPd.o(.text+0x127): In function `main': : undefined reference to `sqlite_exec' /tmp/ccsdAdPd.o(.text+0x157): In function `main': : undefined reference to `sqlite_close' /tmp/ccsdAdPd.o(.eh_frame+0x11): undefined reference to `__gxx_personality_v0' collect2: ld returned 1 exit status linux:/home/christian/Desktop # ## Whats wrong? Thanks - Christian [1] http://sqlite.org/quickstart.html (the C programm) [2] http://sqlite.org/sqlite_source.zip -- Linux is like a wigwam - no gates, no windows and an apache inside. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Functions affecting table structure ?
SQLite detects the metadata changes (via the database version cookie) and reloads the schema from the db file, removing (at least) all internal structures it deems outdated or no longer required. -Ursprüngliche Nachricht- Von: Raheel Gupta [mailto:raheel...@gmail.com] Gesendet: Freitag, 06. Dezember 2013 11:47 An: General Discussion of SQLite Database Betreff: [sqlite] Functions affecting table structure ? Hi, I am trying to add some additional data with the table structure stored in SQLite. Everything seems to be working fine but when I do an alter query it seems to drop the table. I have made changes in the following functions to handle the addition of the data: sqlite3AlterFinishAddColumn() , sqlite3EndTable(), I have made changes in the following functions to remove the changes during runtime only (sqlite feels its a normal table) sqlite3InitCallback(), execExecSql() I am trying to understand the ALTER queries and which function handles them. Any hint would be immensely appreciated. I have wasted a day or two trying to figure out why is the database dropping the structure when I add a column to an existing table (modified by my code) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. 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] Functions embedded in SQL statements
The C/C++ documentation can be found here: http://sqlite.org/capi3ref.html#sqlite3_create_function For those of us in the mortal category, various wrappers can make life better. For example, http://initd.org/pub/software/pysqlite/doc/usage-guide.html#creating-user-defined-functions HTH, Christopher L. Smith Have you pondered the meaning of life today?
RE: [sqlite] Functions embedded in SQL statements
Thank you, but I am not looking for C/C++ functions. I am using Tcl. I am looking for the functions that appear to extend the SQL syntax. I did not see either of the two functions I mentioned (ifnull, strftime) on those pages. bb --- Christopher Smith <[EMAIL PROTECTED]> wrote: > The C/C++ documentation can be found here: > http://sqlite.org/capi3ref.html#sqlite3_create_function > > For those of us in the mortal category, various wrappers can > make life > better. For example, > http://initd.org/pub/software/pysqlite/doc/usage-guide.html#creating-user-defined-functions > > HTH, > Christopher L. Smith > Have you pondered the meaning of life today? > > >
Re: [sqlite] Functions embedded in SQL statements
Bud Beacham <[EMAIL PROTECTED]> wrote:
> I have the book SQLite by Chris Newman and it has examples of
> functions embedded within SQL statements.
>
> For example on page 38 the ifnull() function is used.
>
> SELECT code, ifnull(due_date, 'Ongoing') FROM projects;
>
> Also, on page 81 the strftime() function is used.
>
> SELECT strftime('%m/%d/%Y', '2004-10-31');
>
> The problem I am having is that I cannot find any documentation
> on the SQLite documentation page that explains these functions.
> What other functions exist? What is the syntax? Where can they
> be used in SQL statements? Is there any documentation on these
> functions?
>
> My apologies if I have overlooked it.
>
> Thanks.
The info you're looking for can all (probably) be reached
from . Start with the
'expression' section; then read the other sections to see
where expressions can be used.
Regards
Re: [sqlite] Functions embedded in SQL statements
On 6/18/06, Bud Beacham <[EMAIL PROTECTED]> wrote: My apologies if I have overlooked it. I also had trouble finding them first few times, even though I knew they were there somewhere. Anyway, here's what you're looking for: http://www.sqlite.org/lang_expr.html -- Nemanja Corlija <[EMAIL PROTECTED]>
RE: [sqlite] Functions embedded in SQL statements
Bud, I could well be educated on the point, but I think that this is exactly how you go about adding functions for use in the SQL. R, Christopher L. Smith Have you pondered the meaning of life today? From: Bud Beacham <[EMAIL PROTECTED]> Reply-To: sqlite-users@sqlite.org To: sqlite-users@sqlite.org, [EMAIL PROTECTED] Subject: RE: [sqlite] Functions embedded in SQL statements Date: Sun, 18 Jun 2006 14:50:23 -0700 (PDT) Thank you, but I am not looking for C/C++ functions. I am using Tcl. I am looking for the functions that appear to extend the SQL syntax. I did not see either of the two functions I mentioned (ifnull, strftime) on those pages. bb --- Christopher Smith <[EMAIL PROTECTED]> wrote: > The C/C++ documentation can be found here: > http://sqlite.org/capi3ref.html#sqlite3_create_function > > For those of us in the mortal category, various wrappers can > make life > better. For example, > http://initd.org/pub/software/pysqlite/doc/usage-guide.html#creating-user-defined-functions > > HTH, > Christopher L. Smith > Have you pondered the meaning of life today? > > >
Re: [sqlite] Functions embedded in SQL statements
Bud Beacham <[EMAIL PROTECTED]> wrote: > I cannot find any documentation > on the SQLite documentation page that explains these functions. > What other functions exist? What is the syntax? Where can they > be used in SQL statements? Is there any documentation on these > functions? > http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions http://www.sqlite.org/lang_expr.html#corefunctions I will admit that the SQLite website is not particularly well indexed for human browsing. But you can always find things easily enough using google. Search examples: site:sqlite.org ifnull site:sqlite.org strftime -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Functions embedded in SQL statements
[EMAIL PROTECTED] wrote: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions http://www.sqlite.org/lang_expr.html#corefunctions I will admit that the SQLite website is not particularly well indexed for human browsing. But you can always find things easily enough using google. Search examples: site:sqlite.org ifnull site:sqlite.org strftime Isn't it time (hehe) to include the date/time function documentation in the distribution rather than just the wiki?
Re: [sqlite] Functions embedded in SQL statements
Eric Bohlman <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions > > http://www.sqlite.org/lang_expr.html#corefunctions > > > > I will admit that the SQLite website is not particularly > > well indexed for human browsing. But you can always find > > things easily enough using google. Search examples: > > > > site:sqlite.org ifnull > > site:sqlite.org strftime > > Isn't it time (hehe) to include the date/time function documentation in > the distribution rather than just the wiki? > I'm thinking that all documentation is better placed in a wiki. The current CVSTrac wiki is not really up to that task. I (and others) are experimenting with some new SCM designs that integrate versioning, wiki, and tickets all together into a single easy-to-use program. In the meantime, I'm trying to gain experience with documenting a software project using a wiki by putting as much new SQLite documentation as seems appropriate in the wiki rather than in the CVS tree. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Functions embedded in SQL statements
[EMAIL PROTECTED] wrote: I'm thinking that all documentation is better placed in a wiki. Hmmm. The problem I see is that it makes access to the full documentation contingent on connectivity to a possibility ephemeral external site. Maybe the solution is to incorporate wiki snapshots into the distribution somehow (maybe by including an embedded server in the distribution, something like Sean Burke's Podwebserver that enables me to have the complete Perl documentation, in HTML format, sitting in a tab on my browser even if my Internet connection goes down). I guess I just have a problem with the "everything you need to know is on someone else's machine" attitude that seems to be spreading.
Re: [sqlite] Functions embedded in SQL statements
>>I'm thinking that all documentation is better placed in >>a wiki. > >Hmmm. The problem I see is that it makes access to the full documentation >contingent on connectivity to a possibility ephemeral external site. Quite true. I very much consider it a feature of SQLite that each version ships with its own definite help. This is especially valuable for all all who need to work with older versions of the SQLite because their environment has not yet updated to the latest release. It can be very unfortunate for them to find updated information which might be incorrect or even wrong (at least partially) for their older releases. Ralf
Re: [sqlite] Functions embedded in SQL statements
[EMAIL PROTECTED] wrote: Eric Bohlman <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > I'm thinking that all documentation is better placed in > > a wiki. > > Hmmm. The problem I see is that it makes access to the full > documentation contingent on connectivity to a possibility ephemeral > external site. Maybe the solution is to incorporate wiki snapshots into > the distribution somehow (maybe by including an embedded server in the > distribution, something like Sean Burke's Podwebserver that enables me > to have the complete Perl documentation, in HTML format, sitting in a > tab on my browser even if my Internet connection goes down). > > I guess I just have a problem with the "everything you need to know is > on someone else's machine" attitude that seems to be spreading. > This is a very reasonable point. The new SCM I (and others) are working on will allow you to quickly and easily download the entire source code/wiki/ticket repository and/or synchronize your local repository with remote changes. So ultimately this will not be an issue. But all that is still in the future. Perhaps you can suggest minor changes or enhancements to CVSTrac (http://www.cvstrac.org/) that will allow use to do something similar with the wiki right away - some way to download the whole wiki collection and run it locally. CVSTrac already includes its own web server, so that part is done for you already. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Functions embedded in SQL statements
Ralf Junker <[EMAIL PROTECTED]> wrote: > > This is especially valuable for all all who need to work with older versions > of the SQLite because their environment has not yet updated to the latest > release. It can be very unfortunate for them to find updated information > which might be incorrect or even wrong (at least partially) for their older > releases. > Another good point. And yet I am still very interested in moving toward wiki-style user editable documentation. I agree that the current arrangement is inadequate. Please offer suggestions on how it can be improved. Perhaps it would be sufficient to take snapshots of the wiki and ship that with each release? -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Functions embedded in SQL statements
The new SCM I (and others) are working on will allow you to quickly and easily download the entire source code/wiki/ticket repository and/or synchronize your local repository with remote changes. So ultimately this will not be an issue. But all that is still in the future. Is this available publically anywhere? Another suggestion is you may want to look at MediaWiki. It has an extension mechanism that lets you provide handlers for anything between tags of your choice. Eg you could have the following in the source page: In your handler you can generate raw HTML, or you can generate wikitext markup. We are planning on moving all of our doc into MediaWiki for the BitPim project and then generate help docs from that. I'll even be able to make it in CHM for Windows, AppleHelp for Mac and plain html for Linux/Unix. Roger
Re: [sqlite] Functions embedded in SQL statements
D.Richard Hipp <[EMAIL PROTECTED]> wrote: >Perhaps it would be sufficient to take snapshots of the wiki and >ship that with each release? Yes, shipping wiki snapshots with each build should be fine. Even better: A versioned wiki - so users of legacy versions can edit and improve documentation for "their" version. This can be especially usefull if new versions pose problems to older releases, for example the PRAGMA legacy_file_format issue. Downloadable versions of documentation would of course be nice, too. Ralf
Re: [sqlite] Functions embedded in SQL statements
"Roger Binns" <[EMAIL PROTECTED]> wrote: > > The new SCM I (and others) are working on will allow you to > > quickly and easily download the entire source code/wiki/ticket > > repository and/or synchronize your local repository with remote > > changes. So ultimately this will not be an issue. But all that > > is still in the future. > > Is this available publically anywhere? No code. Just some notes. http://fossil-scm.hwaci.com/ > > Another suggestion is you may want to look at MediaWiki. It has > an extension mechanism that lets you provide handlers for anything > between tags of your choice. Eg you could have the following in > the source page: > > > > In your handler you can generate raw HTML, or you can generate > wikitext markup. > > We are planning on moving all of our doc into MediaWiki for > the BitPim project and then generate help docs from that. > I'll even be able to make it in CHM for Windows, AppleHelp > for Mac and plain html for Linux/Unix. > I want the user-interaction flexibility of MediaWiki, but I want radically simpler setup and administration (no webserver required, zero-configuration) and I also want to support software versioning and bug reports within the same system. I'm aiming for all the best features of MediaWiki, Trac/CVSTrac, and monotone, in a small zero-configuration package that is ridiculously simple to use. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] implement a few sqlite functions...
> Whats wrong? Problem solved: I have compiled sqlite from source and compiled the example like this: g++ sqltest.cpp -L/usr/local/lib -lsqlite ;) Greets -- Linux is like a wigwam - no gates, no windows and an apache inside. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Dynamically load all the sqlite functions
Hi all, I need to dynamically load all the functions inside a sqlite shared library. What I am trying to do is to execute the same code just swapping a pointer from libsqlite1 and libsqlite2 (two different version of the sqlite library). Normally I should dlopen the library, load all functions with dlsym and once done call dlclose. I noticed that all function prototypes are already defined inside sqlite3ext.h but I wondering if there is a function I could use to all the symbols from the lib without manually process all the function names. Any clue? Thanks. -- Marco Bambini ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Functions to convert dates with non-English month name?
Hello, Before I go ahead and write a script to loop through all the rows, I was wondering if SQLite supports functions to convert DD MM into the MySQL-friendly -MM-DD, and whether those functions are localized so that it understands month names in languages other than English? Here's an example: SELECT dateinscription, dateconnexion FROM membres LIMIT 1; 26 Mai 2007|17 Août 2009 - 09h20 I'd like to update the row into "2007-05-26" and "2009-08-17 09:20", respectively. Thank you. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite Functions: "That assembly does not allow partially trusted"
Hello After migration from Windows Server 2003 to Windows Server 2012R2 the following error occurs in the C# library SQLite (version 1.0.98.0): (NuGet Package) "That assembly does not allow partially trusted" The server is shared and there is no possibility to change system settings. The program have to use ASP.NET 4.0 C# library under IIS and Visual Studio 2015 Any help is greatly appreciated. Eduard Laederach Kernkraftwerk Goesgen-Daeniken AG CH-4658 Daeniken, Switzerland Diese Nachricht (inkl. Anhaenge) beinhaltet moeglicherweise vertrauliche oder gesetzlich geschuetzte Daten oder Informationen. Zum Empfang derselben ist (sind) ausschliesslich die genannte(n) Person(en) bestimmt. Falls Sie diese Nachricht irrtuemlicherweise erreicht hat, sind Sie hoeflich gebeten, diese unter Ausschluss jeder Reproduktion zu vernichten und den Absender umgehend zu benachrichtigen. Besten Dank.
Re: [sqlite] Functions to convert dates with non-English month name?
Gilles Ganault wrote: > Before I go ahead and write a script to loop through all the rows, I > was wondering if SQLite supports functions to convert DD MM into > the MySQL-friendly -MM-DD, and whether those functions are > localized so that it understands month names in languages other than > English? SQLite supports such functions in the sense that you can write a custom function that does anything you want, and use it in your statements. > Here's an example: > > SELECT dateinscription, dateconnexion FROM membres LIMIT 1; > 26 Mai 2007|17 Août 2009 - 09h20 > > I'd like to update the row into "2007-05-26" and "2009-08-17 09:20", > respectively. If you need to do it once, you can do something like update membres set dateinscription= substr(dateinscription, -4) || '-' || (case substr(dateinscription, 4, length(dateinscription) - 8) when 'January' then '01' when 'February' then '02' ... when 'December' then '12' end) || '-' || substr(dateinscription, 1, 2); Substitute correct month names. Converting dateconnexion is left as an exercise for the reader. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Functions to convert dates with non-English month name?
On Mon, 17 Aug 2009 07:44:15 -0400, "Igor Tandetnik" wrote: >update membres set dateinscription= >substr(dateinscription, -4) || '-' || >(case substr(dateinscription, 4, length(dateinscription) - 8) > when 'January' then '01' when 'February' then '02' ... > when 'December' then '12' end) || '-' || >substr(dateinscription, 1, 2); > >Substitute correct month names. Converting dateconnexion is left as an >exercise for the reader. Thanks, I'll give it a shot. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What is the right order of the main SQLite functions?
I know this is basic and should be in the documentation, but it is not quite clear to me. Basically I have 3 type of procedures: 1. Getting values from a table. For this I have the following steps: (Open) Prepare Then in a loop: Step ColumnInt, ColumnDouble, ColumnText After the loop: Finalize 2. Putting values in a table: (Open) Start Transaction Prepare Then in a loop: Bind Step Reset After the loop: Clearbindings Finalize Commit Transaction 3. Non looped procedures, eg, delete and update: (Open) Prepare Step Reset Finalize Is this all how it should be? Where/when should I call sqlite3_close? Does Clearbindings come indeed before finalize? Thanks for any clarification. RBS
[sqlite] What is the right order of the main SQLite functions?
On 3/19/2015 7:48 PM, Bart Smissaert wrote: > I know this is basic and should be in the documentation, but it is not > quite clear to me. > Basically I have 3 type of procedures: > > 1. Getting values from a table. For this I have the following steps: > > (Open) > Prepare > > Then in a loop: > > Step > ColumnInt, ColumnDouble, ColumnText > > After the loop: > > Finalize Looks good to me. > 2. Putting values in a table: > > (Open) > Start Transaction > Prepare These two could be in any order > Then in a loop: > Bind > Step > Reset > > After the loop: > > Clearbindings > Finalize > Commit Transaction It's pointless to clear bindings right before finalize. And commit could happen either before or after. > 3. Non looped procedures, eg, delete and update: > > (Open) > Prepare > Step > Reset > Finalize You don't need reset before finalize. > Is this all how it should be? It'll work. There are a few redundancies, but they are harmless. > Where/when should I call sqlite3_close? When you no longer need the connection, of course. > Does Clearbindings come indeed before finalize? I have yet to find a reason to call sqlite3_clear_bindings. It's needed very rarely, if ever. It's absolutely pointless right before finalize. -- Igor Tandetnik
[sqlite] What is the right order of the main SQLite functions?
Thanks, that is very useful. Maybe this should be more clearly (and simple) in the documentation somewhere. RBS On Fri, Mar 20, 2015 at 12:23 AM, Igor Tandetnik wrote: > On 3/19/2015 7:48 PM, Bart Smissaert wrote: > >> I know this is basic and should be in the documentation, but it is not >> quite clear to me. >> Basically I have 3 type of procedures: >> >> 1. Getting values from a table. For this I have the following steps: >> >> (Open) >> Prepare >> >> Then in a loop: >> >> Step >> ColumnInt, ColumnDouble, ColumnText >> >> After the loop: >> >> Finalize >> > > Looks good to me. > > 2. Putting values in a table: >> >> (Open) >> Start Transaction >> Prepare >> > > These two could be in any order > > Then in a loop: >> Bind >> Step >> Reset >> >> After the loop: >> >> Clearbindings >> Finalize >> Commit Transaction >> > > It's pointless to clear bindings right before finalize. And commit could > happen either before or after. > > 3. Non looped procedures, eg, delete and update: >> >> (Open) >> Prepare >> Step >> Reset >> Finalize >> > > You don't need reset before finalize. > > Is this all how it should be? >> > > It'll work. There are a few redundancies, but they are harmless. > > Where/when should I call sqlite3_close? >> > > When you no longer need the connection, of course. > > Does Clearbindings come indeed before finalize? >> > > I have yet to find a reason to call sqlite3_clear_bindings. It's needed > very rarely, if ever. It's absolutely pointless right before finalize. > -- > Igor Tandetnik > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] What is the right order of the main SQLite functions?
On 19 Mar 2015, at 11:48pm, Bart Smissaert wrote: > Is this all how it should be? Apart from the following, nothing I see looks like it will cause problems. You should be checking the result codes of all the calls you do to make sure you are getting SQLITE_OK (==0) returned from them and not an error. > Where/when should I call sqlite3_close? Once you know you don't need access to the database any more. Before your application quits. As above, check to see that you are getting SQLITE_OK back to indicate that your connection to the database was still working correctly when you closed it. > Does Clearbindings come indeed before finalize? You do not need to clear bindings before _finalize(), since _finalize() releases all the bindings for you as part of releasing all resources used by the statement. You might want to clear bindings before or after a _reset(), since it can help you to find errors when you start binding for the next use of the statement. Simon.
[sqlite] What is the right order of the main SQLite functions?
Thanks, all working beautifully now. RBS On Fri, Mar 20, 2015 at 1:27 AM, Simon Slavin wrote: > > On 19 Mar 2015, at 11:48pm, Bart Smissaert > wrote: > > > Is this all how it should be? > > Apart from the following, nothing I see looks like it will cause > problems. You should be checking the result codes of all the calls you do > to make sure you are getting SQLITE_OK (==0) returned from them and not an > error. > > > Where/when should I call sqlite3_close? > > Once you know you don't need access to the database any more. Before your > application quits. As above, check to see that you are getting SQLITE_OK > back to indicate that your connection to the database was still working > correctly when you closed it. > > > Does Clearbindings come indeed before finalize? > > You do not need to clear bindings before _finalize(), since _finalize() > releases all the bindings for you as part of releasing all resources used > by the statement. > > You might want to clear bindings before or after a _reset(), since it can > help you to find errors when you start binding for the next use of the > statement. > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
