Re: [sqlite] stored procedures implementation for SQLite
I know this is an old thread, but shortly after I read it, I attempted to implement stored procedures in SQLite! I only did it to see if I could, not because I necessarily think it's a good idea... It's very experimental and not fully implemented, but if anyone is interested, I checked in my work on GitHub, including pre-compiled binaries for MacOS and Linux. http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended Regards, Chris Wolf BareFeetWare wrote: On 13/11/2010, at 11:14 AM, Scott Hess wrote: On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare list@barefeetware.com wrote: IMO, if you're implementing database logic (ie constraints and triggers) in application code, then you're reinventing the wheel, making your package unnecessarily complex and grossly inefficient. If you're just using SQLite to store your data but doing all the logic outside of SQLite, then you might as well just be saving your data to a CSV file or XML. See my previous post for reasoning behind this. From http://www.sqlite.org/about.html : Think of SQLite not as a replacement for Oracle but as a replacement for fopen() The full paragraph from that page reads: SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen() So I think it's referring to how SQLite stores its data in a local file, rather than on a remote server with which it communicates indirectly. ie SQLite does not have a separate server process. In that way, SQLite is like fopen rather than Oracle. The same paragraphs mentions SQLite triggers, and views, freely copying a [self contained] SQLite database between architectures, which allude to my point about putting the logic in the database itself so you can move the whole database between architectures. So, yes, you might as well just be saving your data to a CSV or XML file. And I'm sure if you had a package to do that, someone would be arguing about whether your XML should allow for embedded transforms. What do you gain by implementing database logic in the application layer, when it could be done far more efficiently and reliably in the SQL schema? The only thing I can think of is avoiding the (shallow) learning curve. Why re-invent and roll your own integrity checking etc when it's already available and in a way much closer to the data than your application code can get? See my previous post for the contrary argument: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html Basically, database logic in the database itself is much faster, causes less error, optimises queries, makes the database portable between application environments or GUI apps. What's not to love? Thanks, Tom BareFeetWare -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] stored procedures implementation for SQLite
Thanks for bringing that to my attention - that sample was left over from when I was trying to use APSW rather then sqlite2. The actual test program, sqlite-3.7.3/src/createproc_test.c, is correct. I updated the blog page to reflect the proper code. -Chris On Jan 26, 2011, at 12:55 PM, Jim Wilcoxson wrote: It looks interesting. Should your except stmt reference apsw? -Jim -- HashBackup: easy onsite and offsite Unix backup http://www.hashbackup.com On Wed, Jan 26, 2011 at 11:38 AM, Chris Wolf cw10...@gmail.com wrote: I know this is an old thread, but shortly after I read it, I attempted to implement stored procedures in SQLite! I only did it to see if I could, not because I necessarily think it's a good idea... It's very experimental and not fully implemented, but if anyone is interested, I checked in my work on GitHub, including pre-compiled binaries for MacOS and Linux. http://chriswolf.heroku.com/articles/2011/01/26/adding-stored-procedures-to-sqlite/#extended Regards, Chris Wolf BareFeetWare wrote: On 13/11/2010, at 11:14 AM, Scott Hess wrote: On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare list@barefeetware.com wrote: IMO, if you're implementing database logic (ie constraints and triggers) in application code, then you're reinventing the wheel, making your package unnecessarily complex and grossly inefficient. If you're just using SQLite to store your data but doing all the logic outside of SQLite, then you might as well just be saving your data to a CSV file or XML. See my previous post for reasoning behind this. From http://www.sqlite.org/about.html : Think of SQLite not as a replacement for Oracle but as a replacement for fopen() The full paragraph from that page reads: SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. Think of SQLite not as a replacement for Oracle but as a replacement for fopen() So I think it's referring to how SQLite stores its data in a local file, rather than on a remote server with which it communicates indirectly. ie SQLite does not have a separate server process. In that way, SQLite is like fopen rather than Oracle. The same paragraphs mentions SQLite triggers, and views, freely copying a [self contained] SQLite database between architectures, which allude to my point about putting the logic in the database itself so you can move the whole database between architectures. So, yes, you might as well just be saving your data to a CSV or XML file. And I'm sure if you had a package to do that, someone would be arguing about whether your XML should allow for embedded transforms. What do you gain by implementing database logic in the application layer, when it could be done far more efficiently and reliably in the SQL schema? The only thing I can think of is avoiding the (shallow) learning curve. Why re-invent and roll your own integrity checking etc when it's already available and in a way much closer to the data than your application code can get? See my previous post for the contrary argument: http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2010-October/025096.html Basically, database logic in the database itself is much faster, causes less error, optimises queries, makes the database portable between application environments or GUI apps. What's not to love? Thanks, Tom BareFeetWare -- ___ 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] pragma foreign_key_list deprecated in 3.7.4?
On Dec 8, 2010, at 2:44 PM, Richard Hipp wrote:
On Wed, Dec 8, 2010 at 2:35 PM, Petite Abeille petite.abei...@gmail.com
wrote:
Hello,
The pragma foreign_key_list appears to be deprecated in 3.7.4:
http://www.sqlite.org/pragma.html#pragma_foreign_key_list
Any reason for such deprecation?
Now that foreign key constraints are enforced natively, why would
you want
to have a list of them? Why should the foreign_key_list pragma
continue to
consume code space and developer maintenance time?
Why would a developer NOT want to be able to query any part of the
data dictionary
to see any aspect of the schema? In addition to the info in
sqlite_master, it would
be nice to be able to access column information as a query rather then
a pragma.
For example, on Oracle you can get a list of foreign keys, like:
select
owner
,constraint_name,constraint_type,table_name,r_owner,r_constraint_name 2
from all_constraints where constraint_type in ('P','U')
Sybase provides the sp_fkeys system stored proc for the same purpose.
If the point is that SQLite is meant to be be small and embedable, maybe
the extra data dictionary querying capability can be provided by a
separate,
laodable extension.
-Chris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] connection-specific user data in extensions
Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/22/2010 06:58 PM, Chris Wolf wrote: Hmm, if you mean apsw, Yes, I did. (I'm the author of APSW if that wasn't clear.) that's actually one of the things I wanted to do: change the Connection signature to accept an already open database connection as an alternative to always opening a database file. It has been a ticket for a while: http://code.google.com/p/apsw/issues/detail?id=79 Going the other way - handing a sqlite3 pointer from APSW to other code is easy and there is an existing function to do that although it will cause problems if the other code directly closes the connection or does silly multi-threading tricks. Please update the ticket if you have use cases that wouldn't have a large probability of memory corruption or crashes. I updated the ticket and provided a patch to connection.c. Also, making APSW be a loadable extension means a separate compilation since all calls have to go via a function pointer table not directly to SQLite so it can't simultaneously be a Python extension and a SQLite extension, although I guess the pointer table could be faked. If the extension code is linked with sqlite3.o then sqlite api calls from the extension are dispatched via sqlite3_api* but sqlite calls from apsw are direct. In any case it works for me. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzraOAACgkQmOOfHg372QQaDwCfYg/Zrst9SQNgxpmqrk/oe56o xEMAoJ0CaVxUVgz76conZemoRZ6m8pOC =INqG -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] Recursive calls to sqlite3_exec
Can sqlite3_exec and/or sqlite3_prepare be re-entered? I notice that data dictionary updates are done directly with the Vdbe engine rather then the public API - will I have to resort to that? I tried using sqlite3NestedParse, but changes to the db don't seem to be reflected immediately. I want to implement a custom function that may conditionally run DML/DDL. Thanks, -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Recursive calls to sqlite3_exec
Igor Tandetnik wrote: Chris Wolf cw10...@gmail.com wrote: Can sqlite3_exec and/or sqlite3_prepare be re-entered? sqlite3_prepare can't be reentered since it doesn't call back. You can have several prepared statements doing traversal with sqlite3_step at the same time, interleaved, if that's what you are asking. Running sqlite3_exec from inside a callback of another sqlite3_exec should work. Great, just what I needed, thanks. I notice that data dictionary updates are done directly with the Vdbe engine rather then the public API - will I have to resort to that? I'm not sure what you mean by data dictionary. data dictionary is a generic term for how/where the schema is stored/defined. e.g. sqlite_master, sqlite_temp_master, (on Oracle, dba_* tables, on Sybase sybobjects , etc. ) I guess I should have just said updates to sqlite_master ;) Schema changes can't be done while there are queries outstaning. ok. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Just compiled SQLite in Visual Studio
Doug wrote:
Igore didn't mean there is no difference between C and C++. He was just
saying a 'project' isn't C or C++. In other words, C and C++ have different
(though similar) compiler rules, syntax, etc. By default, the compiler will
compile a '.c' file using the C rules, and a '.cpp' file with the C++ rules.
And you can mix .c and .cpp files in the same project.
As for what the difference is? That's a big question. Objects exist in
C++, and they don't in C. That's the tip of the iceberg.
Doug
It depends on your definition of object. I hate to nit-pick, but for
me, object may
contain data or code or both data and code. So with this definition, C
implements objects
without code.
For example, in C, a struct is a template used to instantiate objects
which only implement data,
whereas in C++, a struct is a template used to instantiate objects
which implements both data and code.
You can just replace the class keyword with struct:
Compiles only with C++:
struct foo {
int a;
void m() {int b=0; b++;}
};
Compiles with both C and C++ compilers:
struct foo {
int a;
/*void m() {int b=0; b++;}*/
};
Of course, in addition to template classes, C++ has (generic) class
templates and many other features, as you stated.
-Chris
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bob Keeland
Sent: Tuesday, November 30, 2010 5:02 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Just compiled SQLite in Visual Studio
OK then I have a newbee question that is actually out of the scope of
SQLite. If the only difference between C and C++ is the file extension, then
what is the difference between C and C++? I'm thinking of adding a language
other than the Visual Basic that I kind of know and would like to know the
difference. I've been thinking about Java, but am not sure yet.
Bob Keeland
--- On Tue, 11/30/10, john darnell john.darn...@walsworth.com wrote:
From: john darnell john.darn...@walsworth.com
Subject: Re: [sqlite] Just compiled SQLite in Visual Studio
To: General Discussion of SQLite Database sqlite-users@sqlite.org
Date: Tuesday, November 30, 2010, 10:25 AM
Thanks Igor.
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Monday, November 29, 2010 7:11 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Just compiled SQLite in Visual Studio
john darnell john.darn...@walsworth.com wrote:
I just added it to a Visual Studio 8 project, turned off the use of
precompiled headers (the project is a C++ project) and compiled the
SQLite.c file without any errors.
There is no such thing as a C++ project. A project in Visual Studio can
happily contain both C and C++ files. By default, file extension determines
whether the file is compiled with C or C++ compiler (.c would indicate C),
and this could also be overridden in project settings on a per-file basis.
--
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
__ Information from ESET NOD32 Antivirus, version of virus signature
database 5662 (20101130) __
The message was checked by ESET NOD32 Antivirus.
http://www.eset.com
___
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] Just compiled SQLite in Visual Studio
Igor Tandetnik wrote:
Chris Wolf cw10...@gmail.com wrote:
It depends on your definition of object. I hate to nit-pick, but for
me, object may
contain data or code or both data and code. So with this definition, C
implements objects
without code.
Is there really a fundamental difference between
struct X {
int data;
void DoSomething();
};
X x;
x.DoSomething();
and
struct X {
int data;
};
void X_DoSomething(X* pThis);
X x;
X_DoSomething(x);
I bet both fragmens would produce nearly identical machine code. In this very
simple case, even syntactical sugar sprinkled over the first fragment doesn't
seem to make a huge difference. Of course, once you get into virtual
functions and multiple inheritance and so on, simulating equivalent behavior
in C gets progressively more unwieldly
With GNU, you can investigate via g++/gcc -O0 -no-dead-strip -S foo.c,
but unless you use the structure, the compiler won't
instantiate an instance even with -O0 and -no-dead-strip. In any case,
even without virtual functions, at the machine code level,
there's still name mangling and calling convention differences.
(but still possible: in fact, compilers exist that take C++ code and produce
equivalent C code).
Yes, I dabbled with one called CFront from ATT on Sun-3 back in the 80's.
-Chris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] cascade-delete doesn't seem to work
I am trying to get child records to automatically delete upon deletion
of their parent record,
as described here:
http://www.sqlite.org/foreignkeys.html#fk_actions
Unfortunately, it's not deleting the child records. Either with pragma
foreign_keys on or off.
Thanks for any help,
-Chris
Here's the sample I'm working with:
drop table if exists artist;
CREATE TABLE artist(
artistidINTEGER PRIMARY KEY,
artistname TEXT
);
drop table if exists track;
CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER REFERENCES artist(artistid) ON DELETE CASCADE
);
insert into artist (artistname) values('Dean Martin');
insert into artist (artistname) values('Frank Sinatra');
insert into track values(11, That's Amore, 1);
insert into track values(12, Christmes Blues, 1);
insert into track values(11, My Way, 2);
delete from artist where artistid=2;
select * from track;
select 'My Way should have been cascade-deleted.';
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cascade-delete doesn't seem to work
Simon Slavin wrote: On 28 Nov 2010, at 4:23pm, Chris Wolf wrote: Unfortunately, it's not deleting the child records. Which version of SQLite are you using ? It's 3.7.3 on MacOs 10.5 but I have two versions on my Mac - 3.4.0 which comes out-of-the box and a dev version I'm hacking around with, 3.7.3. I just forgot to qualify the shell name with ./. Sorry about that. It works with 3.7.3 and pragma foreign_keys=on ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] connection-specific user data in extensions
Is there a way for me to store one or more pointers to data structures as user data in a sqlite3 structure (i.e. the connection)? I want to allocate some structures specific to the extension I'm thinking of and do this in sqlite3_extension_init(...) but have other routines have access without resorting to global variables. Thanks, -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] connection-specific user data in extensions
Chris Wolf wrote: Is there a way for me to store one or more pointers to data structures as user data in a sqlite3 structure (i.e. the connection)? I want to allocate some structures specific to the extension I'm thinking of and do this in sqlite3_extension_init(...) but have other routines have access without resorting to global variables. Thanks, -Chris ...and not by registering functions and using sqlite3_user_data(). Unless that's the only way. Thanks. -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] connection-specific user data in extensions
Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/22/2010 11:31 AM, Chris Wolf wrote: Is there a way for me to store one or more pointers to data structures as user data in a sqlite3 structure (i.e. the connection)? Nope. It is also a problem if you want to share a connection between different libraries. Currently the only way to find out that the connection is being closed is to register a function or collation with a nonsensical name and hook the xDelete method. There have been requests for my Python library to accept a sqlite3 pointer Hmm, if you mean apsw, that's actually one of the things I wanted to do: change the Connection signature to accept an already open database connection as an alternative to always opening a database file. I'm also finding the degree of encapsulation difficult to work with (at least for dynamically loadable extensions, which can't include sqliteInt.h without linking with the whole amalgamation, but then may as well statically link, like your apsw) made from elsewhere (eg the application) but I can't implement it without great risk of memory corruption as I can't control what elsewhere does with the pointer. There are also some data structures that are not reference counted such as the VFS so you don't know if it is safe to remove or change at any point. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzq5dcACgkQmOOfHg372QSBbgCfYDtH6vFpY0eM4UqiCcDg6eCO AWgAn18QhsYDicO4ZQFZEP0/OUjV53ox =3KPy -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
Re: [sqlite] How to disable and enable constraints in SQLite ?
Roger, I tried building your APSW wrapper. It built fine, but running python setup.py test failed: ERROR: Verify VFS functionality -- Traceback (most recent call last): File /Users/cwolf/src/apsw-3.6.23.1-r1/tests.py, line 3526, in testVFS vfs=ObfuscatedVFS() File /Users/cwolf/src/apsw-3.6.23.1-r1/tests.py, line 3521, in __init__ apsw.VFS.__init__(self, self.vfsname, self.basevfs) ValueError: Base vfs implements version 2 of vfs spec, but apsw only supports version 1 I am building apsw-3.6.23.1-r1 against sqlite3-3.7.3 (amalgamation pulled down via fetch) on MacOS 10.5. Does this mean I won't be able to do virtual table from Python? Thanks, -Chris Roger Binns wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 11/19/2010 05:52 AM, Andreas Mucha wrote: i have to copy data from some database into an SQLite database. The fasted way for me would be to copy simply all tables. To do so i have to disable all SQLite - constraints, copy data and finally enable the constraints again. How can this be done ? Disabling normal constraints doesn't make any sense. For example if you have a 'not null' constraint then disabling while loading would allow null values in the data. Foreign keys can be disabled with PRAGMA foreign_keys=OFF; and you would have to do so if you had any. There are basically two ways of getting at the data. One would be to implement virtual tables where the backend talks to your existing database system. Copies can then be done 'insert into ... select ... from ...' or 'create table ... as select ... from ...'. This approach would also let you only copy some of the data into SQLite while letting the rest remain in your existing database system and you could transparently use both at the same time. The second approach is to create a dump - a text file with raw SQL statements in it. This is easy to develop as you keep reading the SQL text until you are happy with it. That text is also a good way of doing backups, comparisons between databases etc. The SQLite shell includes a .dump command so you can get some idea of what they should look like. If you are just doing this copy data process once then you'll do great. If you will be repeating the process many times then you'll need to make your dump more robust. Here is what my dump code outputs when dumping a SQLite database to give you an idea: - - Disables foreign keys if they are used (this must be done outside a transaction) - - BEGIN TRANSACTION For each table: - - DROP TABLE IF EXISTS name - - CREATE TABLE name ... - - INSERT for each row data - - Creates any indices - - Creates any triggers - - Creates any views last as they could involve multiple tables - - Populates the sqlite_sequence table if any tables have autoincrement primary keys - - Runs analyze for any tables that had that before - - COMMIT TRANSACTION - - Enable foreign keys If you are happy with Python as a language then I'll toot the benefits of my APSW wrapper around SQLite. You can very easily implement virtual tables. It also has a builtin shell that produces nicer dumps than the SQLite shell (eg there are comments in it) and you can easily add your own commands if you want to provide a more interactive experience doing the data importing. http://apidoc.apsw.googlecode.com/hg/vtable.html http://apidoc.apsw.googlecode.com/hg/shell.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkzn6U0ACgkQmOOfHg372QStPQCeLzOanb7LHlqLtBO8qV33+3j/ Xt4An0SKbgdGWqJ43FUr8B09V3XP8JK6 =AZUr -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] Cannot get bind to work
I am trying to familiarize myself with the lower-level routines for
queries, so started with the
example here:
http://www.sqlite.org/quickstart.html
...which uses sqlite3_exec() and I replaced that with
prepare/bind/step. When there are no
bind parameters in the sql statement, the revised program works - but
even putting just one
place-holder ('?') and trying to bind at position 1, I get a
SQLITE_RANGE error. I have
no clue what I'm doing wrong, if any one can help. I am taking the
liberty of including
the program in-line since it's less then 100 lines, if that's ok.
Invocation:
working:
./client test.sqlite select * from emp where ename = 'fred'
not working - error 25, range erorr:
./client test.sqlite select * from emp where ename = '?' fred
Thanks,
-Chris
#include stdio.h
#include stdlib.h
#include string.h
#include sqlite3.h
void
print_row(const char *aColData[], const int nNumCols) {
int i;
for(i=0; inNumCols; i++)
(void)printf(inNumCols-1?%s,:%s,
aColData[i]?aColData[i]:null);
printf(\n);
}
int
main(int argc, char **argv){
sqlite3 *db;
sqlite3_stmt *pStmt;
char *zErrMsg = 0;
const char *pzTail = 0;
int rc;
int nRows = 0;
if( argc3 ){
fprintf(stderr, Usage: %s db sql [bind-param1,
bind-param2...]\n, argv[0]);
exit(1);
}
if((rc = sqlite3_open_v2(argv[1], db, SQLITE_OPEN_READONLY, 0)) !=
SQLITE_OK) {
fprintf(stderr, %d: Can't open database: %s\n, sqlite3_errmsg(db),
__LINE__);
sqlite3_close(db);
exit(1);
}
printf(%s\n, argv[2]);
if((rc = sqlite3_prepare_v2(db, argv[2], -1, pStmt, pzTail)) !=
SQLITE_OK) {
fprintf(stderr, %d: SQL error: %d - %s\n, rc, sqlite3_errmsg(db),
__LINE__);
exit(1);
}
if (argc 3) {
int j;
for(j=0; jargc; j++) {
printf(%d: %s\n, j+1, argv[3+j]);
if((rc = sqlite3_bind_text(pStmt, j+1, argv[3+j], -1,
SQLITE_TRANSIENT))
!= SQLITE_OK) {
fprintf(stderr, %d: SQL error: %d - %s\n, __LINE__, rc,
sqlite3_errmsg(db));
exit(1);
}
}
}
int nCols = sqlite3_column_count(pStmt);
const char *aColData[nCols];
int i;
for (i=0; inCols; i++)
aColData[i] = strdup((char *)sqlite3_column_name(pStmt, i));
print_row(aColData, nCols);
for (i=0; inCols; i++)
free((void *)aColData[i]);
while(rc != SQLITE_DONE) {
rc = sqlite3_step(pStmt);
switch(rc) {
case SQLITE_ROW:
nRows++;
for (i=0; inCols; i++)
aColData[i] = (char *)sqlite3_column_text(pStmt, i);
print_row(aColData, nCols);
break;
case SQLITE_DONE:
printf(\n%d rows processed.\n, nRows);
break;
default:
fprintf(stderr, %d: SQL error: %d - %s\n,__LINE__, rc,
sqlite3_errmsg(db), __LINE__);
exit(1);
/*NOTREACHED*/
break;
}
}
sqlite3_close(db);
return 0;
}
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cannot get bind to work
Jay A. Kreibich wrote: On Wed, Nov 17, 2010 at 10:20:22AM -0500, Chris Wolf scratched on the wall: I am trying to familiarize myself with the lower-level routines for queries, so started with the example here: not working - error 25, range erorr: ./client test.sqlite select * from emp where ename = '?' fred This is not a place-holder. This is a single-character string-literal that consists of a question mark. Lose the quotes. The quotes are part of the string-literal specification, not the value itself. They're not needed for place-holders. Consider this statement if you were binding a integer, or something other than a text value. -j that solved it, thanks so much. -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Cannot get bind to work
Simon Davies wrote:
On 17 November 2010 15:27, Jay A. Kreibich j...@kreibi.ch wrote:
On Wed, Nov 17, 2010 at 10:20:22AM -0500, Chris Wolf scratched on the wall:
I am trying to familiarize myself with the lower-level routines for
queries, so started with the
example here:
not working - error 25, range erorr:
./client test.sqlite select * from emp where ename = '?' fred
This is not a place-holder. This is a single-character
string-literal that consists of a question mark.
Lose the quotes. The quotes are part of the string-literal
specification, not the value itself. They're not needed for
place-holders. Consider this statement if you were binding
a integer, or something other than a text value.
-j
Furthermore, in your binding, loop from 0 to argc-3 rather than argc
for(j=0; jargc-3; j++) {
// for(j=0; jargc; j++) {
printf(%d: %s\n, j+1, argv[3+j]);
if((rc = sqlite3_bind_text(pStmt, j+1, argv[3+j], -1,SQLITE_TRANSIENT))
!= SQLITE_OK) {
fprintf(stderr, %d: SQL error: %d - %s\n, __LINE__,
rc,sqlite3_errmsg(db));
exit(1);
}
}
Thanks for that - I also fixed the fprintf(stderr...) where the __LINE__
args were in the wrong position.
-Chris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] uncertainty how load_extension is supposed to work
Richard Hipp wrote:
On Fri, Nov 12, 2010 at 12:08 PM, Chris Wolf cw10...@gmail.com wrote:
I tried to explicitly load an extension via:
sqlite select load_extension('mylib');
SQL error: no such function: load_extension
Use the .load command in the sqlite3.exe command-line shell.
sqlite .load mylib
unknown command or invalid arguments: load. Enter .help for help
...and it's not listed with .help
I have release 3.4.0 on Mac OS 10.5.
-Chris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] uncertainty how load_extension is supposed to work
Richard Hipp wrote: On Mon, Nov 15, 2010 at 7:39 AM, Chris Wolf cw10...@gmail.com wrote: sqlite .load mylib unknown command or invalid arguments: load. Enter .help for help ...and it's not listed with .help I have release 3.4.0 on Mac OS 10.5. Bummer. I guess Leopard compiled with -DSQLITE_OMIT_LOAD_EXTENSION. I suggest downloading a pre-compiled binary from the SQLite website and using that instead. There was no precompiled binary for Mac - only sqlite-analyzer. It was easy enough to build from amalgam source. I you want, I can make a binary build for MacOS - not just a tar/zip but an installer package wrapped in a dmg to post on your site. Otherwise I'm good to go - thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] last row count 'C' API?
I am looking here: http://www.sqlite.org/c3ref/funclist.html ...and I don't see an API to return the last query's row count, analogous to sqlite2_changes() for update row counts. Or would I just count the number of invocations of productive sqlite3_step() calls (or count callback invocations via sqlite3_exec())? Thanks, -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] last row count 'C' API?
Jay A. Kreibich wrote: On Mon, Nov 15, 2010 at 11:29:49AM -0500, Chris Wolf scratched on the wall: I am looking here: http://www.sqlite.org/c3ref/funclist.html ...and I don't see an API to return the last query's row count, analogous to sqlite2_changes() for update row counts. Such a function does not exist for queries. Or would I just count the number of invocations of productive sqlite3_step() calls (or count callback invocations via sqlite3_exec())? Correct. Thanks for the confirmation... -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
Olaf Schmidt wrote: Chris Wolf schrieb I can't resist adding my little opinion to yet another business logic in stored procs vs. app layer holy war... ... yeah, seems this thread is evolving nicely in this regard ...g I usually prefer keeping the business logic in the application layer and leaving the DB tier to be just a data provider. In actual practice, this is not always practical. Let's say you implement a service where the client can retrieve a set of top-level records, each has an id; then for each id, you get additional detail records from numerous detail/line-item tables. If you implement this as a collection of fine-grained services, i.e. each piece is a round trip from client, through web services layer, through to db layer; and for each top-level id in the result set - the performance will be abysmal. With Sybase stored procs, you can stack multiple result sets in one call, so in the above scenario, you invoke the lookup proc for each top-level id and the proc performs all the secondary detail queries and stacks it all together in a multiple-results result-set, such that there's only one round-trip through the tiers for each top-level id in the set. But that is a common problem, which can be solved directly in the business-layer-code at the serverside as well, also achieving a stacked serialisation of resultsets in one roundtrip. One can either code such a thing (a stacked serialization) by hand (based on XML for example, which is well-nestable) - or on a given platform (e.g. on Windows) one can avoid these hand-coded parts by making use of one of the already mentioned DB-abstraction-helpers. E.g. ADO does support so called shaped, hierarchical Recordsets for a long time now ... over the DataShape- Provider which is part of ADO (and plays together with a lot of OLEDB-providers from different vendors... not sure if the Sybase-Provider is one of those, which is Shape-Provider-capable). http://support.microsoft.com/kb/189657 If you request the construction of such a nested Recordset at the serverside (over the Shape- Provider), then there are no network-roundtrips involved, in case the DB-Server and the AppServer do run on the same machine. But we digress ... ;-) In your scenario, here, even though the middle-tier (business-layer-code) is collocated with the database, it looks to me like ADO is still a client-server technology (I'm relatively unfamiliar with it) you still need a Connection object to connect with the database, so I assume there's still a protocol stack, through which, the client-based SHAPE mechanism must make multiple (local) round trips. Even though the ADO Connection is not as heavy-weight as HTTP, or even TCP/IP (I'm assuming for local connections it may be via named pipes) There's still serialization/deserialization of the client-server protocol stack. With stored procedures, the multiple open cursors to different tables are right there, running in the same process/thread space of that stored proc - no connection, no protocol stack, so it's going to be much faster. -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] uncertainty how load_extension is supposed to work
I tried to explicitly load an extension via:
sqlite select load_extension('mylib');
SQL error: no such function: load_extension
Is this because of the default setting of enable_load_extension?
http://www.sqlite.org/c3ref/enable_load_extension.html
If so, does that mean we can't use the out-of-the box shell and must
re-compile
with a 'C' code change to invoke this? If that's true, why wasn't this
simply
controlled via an environment variable?
Assuming this *had* worked, I assume the shared library naming convention is
that of the dlopen library call, although this behavior may not jive
with Windows. Maybe it's like Java's LoadLibrary? i.e. ignore
the file extension (.so, .dll, .dylib) and drop the lib prefix.
e.g. to load libmylib.dylib on Mac, it's:
select load_extension('mylib');
...which would correspond to libmylib.dll on Windows, etc.
Is that how it is?
Can it be documented on this page? :
http://www.sqlite.org/lang_corefunc.html#load_extension
Thanks,
-Chris
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question about SQLite features.
Olaf Schmidt wrote: Petite Abeille schrieb On Nov 11, 2010, at 8:30 PM, Olaf Schmidt wrote: If such an encapsulation of business-rules is sitting in the DB itself - written in a proprietary DB-dialect, then you cannot call such a thing a business-layer anymore. Nonsense :)) Of course... ;-) Nah, seriously... you know, how I meant that and what the context was ... *if* somebody decides to handle DB-Interaction (and his set of business-rules) *not* directly in the client (or alternatively in the DB-Server) - then he obviously does so, to decouple the Client-Application from the DB(-Backend) - allowing then (if done right), to connect this intermediate layer to different clientside Implementations (GUIs) - as well as different DB-Backends. Encapsulated in a Dll with the right interfaces, one can use it either serverside (e.g. behind a WebServer, to talk to Browser-Clients - delivering JSON- or XML- serialized Resultset-Content) - or behind a real AppServer, to talk to Fat Clients (delivering Resultset-Content in a serialized Object-Container - as for example disconnected ADO-Recordsets on Windows, which are then understood by a large Set of languages, easily bindable with mostly only one line of code to a DataGrid or whatever GUI-Widget). Heck, you can put such a layer-Dll even at the clientside, to support a standalone App or alternatively a more Client/Server-like approach, capable to work against a different set of DB-engines even then (in the standalone App for example, against SQLite). [Helsinki Declaration(s)...] Cannot disagree more with these articles, sorry. From my experience his main-assumption is just not true, that it is more difficult to develop such a layer with modern languages or environments - compared with a proprietary DB-dialect and some DB-specific enhancements or features. Perhaps you should give an example of a certain stored procedure (not too complex, to keep things more simple), describe what it does - and then compare it with the implementaion-code, done in a normal language, which does use ODBC/JDBC/ADO or whatever and is using only common SQL-statements, to achieve the same thing in a DB-engine-independent way? Olaf I can't resist adding my little opinion to yet another business logic in stored procs vs. app layer holy war... I usually prefer keeping the business logic in the application layer and leaving the DB tier to be just a data provider. In actual practice, this is not always practical. Let's say you implement a service where the client can retrieve a set of top-level records, each has an id; then for each id, you get additional detail records from numerous detail/line-item tables. If you implement this as a collection of fine-grained services, i.e. each piece is a round trip from client, through web services layer, through to db layer; and for each top-level id in the result set - the performance will be abysmal. With Sybase stored procs, you can stack multiple result sets in one call, so in the above scenario, you invoke the lookup proc for each top-level id and the proc performs all the secondary detail queries and stacks it all together in a multiple-results result-set, such that there's only one round-trip through the tiers for each top-level id in the set. I don't see how this is pertinent to SQLite, since it's generally not used in multi-user client-server configurations. -Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] accessing table column names other then via pragma, or...
Sam Roberts wrote: On Tue, Nov 9, 2010 at 9:08 PM, Chris Wolf cw10...@gmail.com wrote: ...can pragma result sets be accessed in other sql statements? I wish to use SQLite to perform some data re-formatting, as such, I need to output the results in CSV format. Unfortunately, the .output command does not emit the column names in the first row. Does the .headers command help? Silly me - yes it does. I missed that some how. Thanks! I still believe that there are use-cases where one may want to access columns names in a sql statement. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] accessing table column names other then via pragma, or...
...can pragma result sets be accessed in other sql statements? I wish to use SQLite to perform some data re-formatting, as such, I need to output the results in CSV format. Unfortunately, the .output command does not emit the column names in the first row. I was hoping that something like this would work: .separator ',' .ouput results.csv select name from pragma table_info(mydata) union select * from mydata; ...but apparently pragmas can't participate in a sql statement. I don't see anything in sqlite_master that would allow DML access to table's column names.So is it really impossible to get column names into the first row of the csv file without resorting to writing some crazy 'C' code? Thanks, Chris Wolf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
