Re: [sqlite] Script perl e python, I can't open database from sqlite
"D. Richard Hipp" wrote: > ... > All core features have been thoroughly tested. I'm looking for problems > with the API. Do we need new parameters on some functions? Do we need > to change the semantics of some functions? Do we need to change the > semantics of some of the SQL. > ... Please re-introduce the SQLite API function sqlite_libversion() as sqlite3_libversion() in SQLite3. It might be needed for Win32 DLLs. Best regards, Christian
Re: [sqlite] Script perl e python, I can't open database from sqlite
Ara.T.Howard wrote: > On Tue, 24 Aug 2004, D. Richard Hipp wrote: >> >> The more people use SQLite version 3, the faster it will leave beta >> status > > > in particular, which features would you say need tested? i have many uses > for sqlite, perhaps i may be able to start using 3 for some of my projects. > All core features have been thoroughly tested. I'm looking for problems with the API. Do we need new parameters on some functions? Do we need to change the semantics of some functions? Do we need to change the semantics of some of the SQL. Once the code comes out of beta, the API is frozen. Any deficiencies we'll just have to live with. So if there is anything you think needs to be added which cannot be added in a backwards compatible way, you need to speak up now. Typically these kinds of problems are only discovered after heavy use, which is why I'm asking for more users before I move out of beta. The question of Host Parameter Names and their format is the kind of thing that needs to be resolved now, before leaving beta. I just changed the parsing of Host Parameter Names (a.k.a. bind parameters) from ":NNN:" where NNN is a number to ":AAA" where AAA is any alphanumeric text. Those kind of changes need to be identified soon before the wrong implementation gets frozen into the design forever. Thanks for your help. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] Python bindings for sqlite 3
On Tue, Aug 24, 2004 at 08:43:47PM -0700, David M. Cook wrote: > > * DBAPI compliance is important to me. sqlite is only one of the DBs I'd > like to support in my apps. Do you know what's currently lacking in conformance? > * I use pyformat pretty heavily. I like being able to use dictionaries as a > data format, and pyformat makes this really easy. But it would probably not > be too hard to create a wrapper for this (there's already a cookbook > recipe for this.) Right. Writing a wrapper wouldn't be too hard. But I would like to encourage the use of standard format SQL and keep the binding as light-weight as possible. I don't see a problem implementing this at a higher level as a wrapper. > * A dictionary-like row object would be nice (pyPgSQL has this). Currently > pysqlite has read-only row objects. But his is not that important as it > is easy to convert tuples to dicts. pysqlite already had that, as does pysqlite3 (see the Row class). > * I'd like to have more transparent handling of bools. Example? > * I do like the current pysqlite's transparent handling of date/time types. How could it be better? (of course, pysqlite3's handling is non existent...) > * Not having having the "--types" hack is not a loss IMO, as I find it > rather awkward. You have to build a string of types for each possible > query you might do, and it's very specific to pysqlite. Yes, definitely. I haven't needed user-defined types in any code I've written. Some method to allow user-defined types to be registered would be nice, but it's going to be an pysqlite-specific extension, which would be good to avoid...
Re: [sqlite] bind variables
Okay Richard, I now have a more specific/official answer. The SQL-2003 02 Foundation (2003-09) document that I referred you to says on its page 152 (part of 5.4 "Names and identifiers", p151) this: ::= The portion is also used by, and often entirely comprises, things like table and column names. So read this as "same as table name but with leading colon". It also means that whether or not you allow delimited identifiers for table names etc determines whether or not you do for host parameter names too. And yes, "host parameter name" is the official term to refer to application bind variable names. More generally, "host parameter" refers to the binding variables; there are several other "" mentioned in SQL-2003 also. I think it's in SQL-1999 too, but I haven't checked; in any event, one of the purported advances in 2003 over 1999 is that a whole bunch of bugs were fixed, so it's probably best to refer to just the newer standard's docs. -- Darren Duncan -- Previously I said... At 9:20 PM -0400 8/24/04, D. Richard Hipp wrote: Darren Duncan wrote: It would help me and a lot of other people the most if you simply supported the ":foo" format for named bind variables. Perhaps someone can enlighten me as to exactly what that format is? A colon followed by any number of alphanumerics? What about underscores? Does there need to be a second colon terminator? Where can I find specifics? The short answer to this, I believe, is that this format involves a colon followed by any of the exact same characters that are allowed in standard SQL identifiers such as table or view or column or trigger names. In your case, just make it the same as what you allow for table names, etc, but there is a single leading colon. I'm looking up a longer answer now, with references in the SQL standard docs. Meanwhile, look here: http://www.wiscorp.com/SQLStandards.html Click on the link "SQL:2003 Documents", which is a zip file of about 1MB in size, containing the full SQL:2003 spec in PDF format. -- Darren Duncan
Re: [sqlite] bind variables
On Tue, Aug 24, 2004 at 07:10:55PM -0700, Darren Duncan wrote: > > Meanwhile, look here: http://www.wiscorp.com/SQLStandards.html ISO/IEC 9075-2:2003 (E) (DRAFT) 5.4 Names and identifiers ::= Also see 4.29 "Host parameters" all in 5WD-02-Foundation-2003-09.pdf Cheers, Matt
Re: [sqlite] Script perl e python, I can't open database from sqlite
On Tue, 24 Aug 2004, D. Richard Hipp wrote: Mauricio Piacentini wrote: I will update SQLiteBrowser to use version 3 when it leaves beta status The more people use SQLite version 3, the faster it will leave beta status in particular, which features would you say need tested? i have many uses for sqlite, perhaps i may be able to start using 3 for some of my projects. regards. -a -- === | EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov | PHONE :: 303.497.6469 | A flower falls, even though we love it; | and a weed grows, even though we do not love it. | --Dogen ===
Re: [sqlite] bind variables
At 9:20 PM -0400 8/24/04, D. Richard Hipp wrote: Darren Duncan wrote: It would help me and a lot of other people the most if you simply supported the ":foo" format for named bind variables. Perhaps someone can enlighten me as to exactly what that format is? A colon followed by any number of alphanumerics? What about underscores? Does there need to be a second colon terminator? Where can I find specifics? The short answer to this, I believe, is that this format involves a colon followed by any of the exact same characters that are allowed in standard SQL identifiers such as table or view or column or trigger names. In your case, just make it the same as what you allow for table names, etc, but there is a single leading colon. I'm looking up a longer answer now, with references in the SQL standard docs. Meanwhile, look here: http://www.wiscorp.com/SQLStandards.html Click on the link "SQL:2003 Documents", which is a zip file of about 1MB in size, containing the full SQL:2003 spec in PDF format. -- Darren Duncan
Re: [sqlite] Script perl e python, I can't open database from sqlite
D. Richard Hipp wrote: Mauricio Piacentini wrote: I will update SQLiteBrowser to use version 3 when it leaves beta status The more people use SQLite version 3, the faster it will leave beta status Oh, ok. Good to know. I will then try to release a beta version asap, to help testing. Can you rig SQLiteBrowser so that it links against both 2.8 and 3.0 and uses whichever is appropriate for the database it is reading? I already have a version linked to both versions of the library. I was planning to make it able to convert from 2 -> 3, but operate in 3.x format only from now on. I will investigate what it takes to make it able to operate with both formats, however. Regards, Mauricio
Re: [sqlite] Script perl e python, I can't open database from sqlite
SQLite version 3.0.4 Enter ".help" for instructions qlite> .databases Error: file is encrypted or is not a database sqlite> .tables Error: file is encrypted or is not a database But if I open this database with sqlitebrowser I don't have errors. Why sqlite doesn't open it ??? This error is under linux and windows. Your Perl module and SqliteBrowser are both still using SQLite version 2.8.x, so they produce databases in the SQLite 2.1 format. You are trying to read these with SQLite version 3, that's the reason why it does not work. I will update SQLiteBrowser to use version 3 when it leaves beta status, probably after the next release. Regards, Mauricio
[sqlite] Script perl e python, I can't open database from sqlite
If I create db with script perl and python after if I try to open db
with sqlite I've this error:
Error: file is encrypted or is not a database
This is the script on windows, I use last perl's modules and last sqlite
version :
#!c:\perl\bin\perl.exe
use DBI;
use strict;
my $dbh=DBI->connect ("dbi:SQLite:dbname=c:\\sqlite\\dizio.sql") or die
"$!";
my $sth = $dbh->do("CREATE TABLE di (indice int, dato char(10))");
my $sth = $dbh->prepare("INSERT INTO di VALUES (?, ?)");
$sth->execute(1, 'ciao');
$sth = $dbh->prepare("select * from di;");
$sth->execute();
while ( my @row=$sth -> fetchrow_array() )
{
print "uno: $row[0], due: $row[1]\n";
}
$dbh -> disconnect();
If I try:
sqlite dizionario.sql
SQLite version 3.0.4
Enter ".help" for instructions
qlite> .databases
Error: file is encrypted or is not a database
sqlite> .tables
Error: file is encrypted or is not a database
But if I open this database with sqlitebrowser I don't have errors. Why
sqlite doesn't open it ??? This error is under linux and windows.
Re: [sqlite] bind variables
Darren Duncan wrote: It would help me and a lot of other people the most if you simply supported the ":foo" format for named bind variables. Perhaps someone can enlighten me as to exactly what that format is? A colon followed by any number of alphanumerics? What about underscores? Does there need to be a second colon terminator? Where can I find specifics? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] bind variables
It would help me and a lot of other people the most if you simply supported the ":foo" format for named bind variables. Not only is this format standard in many databases including Oracle, but as far as I know the ANSI/ISO SQL standard also defines this as a standard format. Generally speaking, you should do what the ANSI/ISO SQL 2003 standard says where at all possible. I strongly suggest keeping the $ sigils out of the SQL and leave it to individual language binding interfaces, or the languages themselves, to support such things as that. -- Darren Duncan
Re: [sqlite] Python bindings for sqlite 3
> db eval {UPDATE t1 SET value=$bigblob WHERE rowid=$id}
Is this more efficient than
db eval "UPDATE t1 SET value='$bigblob' WHERE rowid=$id" ?
In particular, does it save a copy of the character data?
Regards
[sqlite] Re: [Pysqlite-devel] Python bindings for sqlite 3
Matt Wilson wrote: Hi. I've been working on some refactoring of the Python bindings for sqlite. I now have a working Python binding for sqlite 3 which is fairly different than the bindings for sqlite 2. [...] I've been doing development in our Conary CVS repository. You can browse the repository here: http://cvs.specifixinc.com/viewcvs/viewcvs.cgi/conary/pysqlite3/ I'm not trying to hijack the work by Gerhard - but our project needed new features that sqlite 3 brings, and we couldn't wait for a few months while new support was added. I welcome feedback, and am more than happy to throw this work away if a better Python binding for sqlite emerges. Nice work, Matt :-) As far as I could see in a glance, you based your work on the current PySQLite codebase. What I currently have here is a proof-of-concept of a PySQLite rewrite, completely in C. Most design decisions are already formed in my head, and only need implementation ;-) The benefits compared to the current PySQLite would be significant improvements in performance and memory consumption and a lean and mean API. From this proof-of-concept on I planned to develop the next generation of PySQLite. Perhaps I could reuse some of your code as well. Btw. as I'm currently unemployed I would accept sponsorship of the PySQLite rewrite and could help with other Python or Python extension programming projects :-) -- Gerhard
Re: [sqlite] Replacement for "Copy"
On Tue, 24 Aug 2004, Marc Pitoniak wrote: >I understand that the "Copy" command is no longer available in sqlite3. It was felt it was redundant. > >Is there anything comparable to "Copy" that will allow me to import text >files using an sql statement from my program? > >Putting it another way, using sqlite3.exe and the command ".import >import.txt sample" works just fine. Whats wrong with using sqlite3.exe? It's a perfectly valid way to initialise the database. > >Is there something comparable that I can do programmatically using SQL >syntax in my program? > >(I've tried various combinations of "insert into sample as select from >'import.txt'" with no success) Browse the source of sqlite3.exe, and copy the .import command from there if you're not happy using sqlite3.exe as is. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] Replacement for "Copy"
I've been plodding along making some progress with sqlite3.lib in my C++ program. Right now I'm trying to import a large text file with no success. I understand that the "Copy" command is no longer available in sqlite3. Is there anything comparable to "Copy" that will allow me to import text files using an sql statement from my program? Putting it another way, using sqlite3.exe and the command ".import import.txt sample" works just fine. Is there something comparable that I can do programmatically using SQL syntax in my program? (I've tried various combinations of "insert into sample as select from 'import.txt'" with no success) TIA, -Marc
Re: [sqlite] Re: [Pysqlite-devel] Re: [sqlite] Python bindings for sqlite 3
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, We already have ?, ?nnn and :nnn: IIRC. Adding $xyz, %xyz, @xyz and possible other ones in parallel isn't a good thing, I think. Maybe :xyz: is good enought and binding language neutral. Maybe @xyz. How this is handled in other engines? Maybe there is a 'semi-standard'? Michael Roth -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBK7XMSIrOxc3jOmoRAhbIAJ456znYumHZz43KVKlDAYd4OflacQCeMeke bywD0q9S8HkNGFArviopUHI= =t/Sv -END PGP SIGNATURE-
Re: [sqlite] Re: [Pysqlite-devel] Re: [sqlite] Python bindings for sqlite 3
On Tue, 24 Aug 2004, D. Richard Hipp wrote:
Matt Wilson wrote:
Normally python programmers would like to see named arguments in
dictionary substation format:
d = { 'blob': 'a\0b', 'id': 2 }
cursor.execute("UPDATE t1 SET value=%(bigblob)s WHERE rowid=%(id)d", d)
I'd be willing to extend the lexer/parser of SQLite to accept this kind
of thing. The only problem here is that '%' is already used to mean the
remainder-after-integer-division operator, like in C. Could another
character be used instead? "@" perhaps? Or maybe two "%%" instead of
just '%'?
perhaps
cursor.execute("UPDATE t1 SET value=#{ bigblob }s WHERE rowid=#{ id }d", d)
'#' commences the escape iff the next char is
[{(|"'!
the next balancing closing char
]})|"'!
respectively, closes the sequence.
it's really nice to be able to select the bracketing char (and not that had to
implement) for complex escape nightmares in various langs.
2cts.
cheers.
-a
--
===
| EMAIL :: Ara [dot] T [dot] Howard [at] noaa [dot] gov
| PHONE :: 303.497.6469
| A flower falls, even though we love it;
| and a weed grows, even though we do not love it.
| --Dogen
===
Re: [sqlite] bind variables
On Tue, Aug 24, 2004 at 03:15:30PM -0400, Andrew Piskorski wrote: > Btw, I've used these database APIs and know that they all use ':' to > indicate a named bind variable which then maps to a Tcl variable, in > very much the same scheme you've explained above: > > - AOLserver's Oracle and PostgreSQL drivers. (Which is then used and > further extended by the OpenACS db api.) > - nstcl (which wraps Oratcl, Pgtcl, etc.) Oh, and Oracle's sqlplus interface also uses ':' for bind variables, I forgot to add that one. I've no idea whether the SQL standard says anything about bind variables or not. -- Andrew Piskorski <[EMAIL PROTECTED]> http://www.piskorski.com/
[sqlite] Re: [Pysqlite-devel] Re: [sqlite] Python bindings for sqlite 3
On Tue, Aug 24, 2004 at 03:37:15PM -0400, D. Richard Hipp wrote:
> >
> >d = { 'blob': 'a\0b', 'id': 2 }
> >cursor.execute("UPDATE t1 SET value=%(bigblob)s WHERE rowid=%(id)d", d)
> >
>
> I'd be willing to extend the lexer/parser of SQLite to accept this kind
> of thing. The only problem here is that '%' is already used to mean the
> remainder-after-integer-division operator, like in C. Could another
> character be used instead? "@" perhaps? Or maybe two "%%" instead of
> just '%'?
It's awfully Python-specific. I think adopting something that other
SQL engines use (":name") would be more generic. SQLServer uses
"@name" iirc.
Cheers,
Matt
[sqlite] Re: [Pysqlite-devel] Re: [sqlite] Python bindings for sqlite 3
Matt Wilson wrote:
Normally python programmers would like to see named arguments in
dictionary substation format:
d = { 'blob': 'a\0b', 'id': 2 }
cursor.execute("UPDATE t1 SET value=%(bigblob)s WHERE rowid=%(id)d", d)
I'd be willing to extend the lexer/parser of SQLite to accept this kind
of thing. The only problem here is that '%' is already used to mean the
remainder-after-integer-division operator, like in C. Could another
character be used instead? "@" perhaps? Or maybe two "%%" instead of
just '%'?
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] bind variables
On Tue, Aug 24, 2004 at 03:15:30PM -0400, Andrew Piskorski wrote: > > Btw, I've used these database APIs and know that they all use ':' to > indicate a named bind variable which then maps to a Tcl variable, in > very much the same scheme you've explained above: Using : to name the variable would work fine, then the parameter binding code in the python binding just needs to iterate through the names and retrieve the item from the dictionary that matches. Cheers, Matt
Re: [sqlite] bind variables
Andrew Piskorski wrote:
On Tue, Aug 24, 2004 at 02:55:51PM -0400, D. Richard Hipp wrote:
In this way, I get to specify TCL variables directly in the
SQL statement, not as parameters added to the end. For
example:
db eval {UPDATE t1 SET value=$bigblob WHERE rowid=$id}
Dr. Hipp, this is a great little feature, but is there some reason you
chose to use '$' to indicate a bind variable rather than the more
typical ':' symbol?
The expression part of a TCL "if" or "while" statement uses '$' in
exactly the same way. TCL programmers are used to using '$' like this.
Note that SQLite accepts full TCL variable name syntax. So you can
do complex stuff like this:
db eval {UPDATE t1 SET value=$::main::blob1(label) WHERE rowidid=$id}
^
I would have allowed arbitrary TCL code using "[...]" except for
the fact that square brackets are already used as a quoting character
by SQL Server. I may yet go in and add this capability as an option.
As currently implemented in SQLite, the ":NNN:" wildcards require
a number in between the two colons. And that number becomes the
number that the sqlite3_bind_...() statement refers to. So if
you have ":10:" in your SQL, you bind it as index 10.
But I can change that so that you can have arbitrary text in between
the two colons, if doing so will help anybody's interfacing efforts.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
[sqlite] Re: [Pysqlite-devel] Re: [sqlite] Python bindings for sqlite 3
On Tue, Aug 24, 2004 at 02:55:51PM -0400, D. Richard Hipp wrote:
>
> I do not know if this new technique will be helpful to Python
> or not, but I thought I would bring it to your attention, just
> in case it is. Please note that the changes to support this
> are in CVS but have not be added to a "release". Also note
> that these changes are still considered experimental and are
> subject to change as more experience with the design becomes
> available.
Normally python programmers would like to see named arguments in
dictionary substation format:
d = { 'blob': 'a\0b', 'id': 2 }
cursor.execute("UPDATE t1 SET value=%(bigblob)s WHERE rowid=%(id)d", d)
but I don't know if this will map to named parameters. The binding
could transform %(bigblob)s into $bigblob and store the 's' format
character to do type checking at binding time. It seems like a lot of
work, though.
Cheers,
Matt
[sqlite] bind variables
On Tue, Aug 24, 2004 at 02:55:51PM -0400, D. Richard Hipp wrote:
> In this way, I get to specify TCL variables directly in the
> SQL statement, not as parameters added to the end. For
> example:
>
>db eval {UPDATE t1 SET value=$bigblob WHERE rowid=$id}
Dr. Hipp, this is a great little feature, but is there some reason you
chose to use '$' to indicate a bind variable rather than the more
typical ':' symbol?
At least for Tcl, '$' seems like a particularly poor choice, as it
means you'll have to escape it whenever using double quoted strings in
Tcl. E.g.:
db eval "UPDATE t1_SET value=\$bigblob WHERE rowid=\$id $and_clause"
Is there some advantage to using '$' here that I've missed?
Btw, I've used these database APIs and know that they all use ':' to
indicate a named bind variable which then maps to a Tcl variable, in
very much the same scheme you've explained above:
- AOLserver's Oracle and PostgreSQL drivers. (Which is then used and
further extended by the OpenACS db api.)
- nstcl (which wraps Oratcl, Pgtcl, etc.)
I don't know what other db APIs do or don't do with bind variables.
--
Andrew Piskorski <[EMAIL PROTECTED]>
http://www.piskorski.com/
Re: [sqlite] Python bindings for sqlite 3
Matt Wilson wrote:
1) Wildcards in the SQL passed to cursor.execute() now use the sqlite
native '?' or ':N:' format. Previously Python syntax was allowed.
Making this change lets us bind parameters to compiled SQL
statements natively, without converting them to strings. This will
also allow us to re-use a compiled statement by rebinding
parameters later.
I've recently checked in change to SQLite that help with the
TCL interface. These changes are still experimental, but they seem
to be working out well. These changes might also be useful
to Python. (Or not. I'll let the Python experts make that
judgement.)
SQLite now accepts as a wildcard, a dollar sign '$' followed by
a TCL variable name. The resulting wildcard is a place-holder,
just like "?" or ":NNN:". The only difference is you get to use
a descriptive name for the place-holder.
After the statement is compiled using sqlite3_prepare(), you can
find out how many wildcards are in the original statement using
sqlite3_bind_parameter_count(). Once you know how many wildcards
there are, you can find the name of each wildcard using
sqlite3_bind_parameter_name() - a new API.
In the TCL interface, I use this as follows:
* Compile the SQL statement using sqlite3_prepare().
* For each wildcard:
+ Look up the name of the wildcard using
sqlite3_bind_parameter_name().
+ Bind the value of the TCL variable with the
same name.
* Use sqlite3_step() to execute the SQL statement.
In this way, I get to specify TCL variables directly in the
SQL statement, not as parameters added to the end. For
example:
db eval {UPDATE t1 SET value=$bigblob WHERE rowid=$id}
I do not know if this new technique will be helpful to Python
or not, but I thought I would bring it to your attention, just
in case it is. Please note that the changes to support this
are in CVS but have not be added to a "release". Also note
that these changes are still considered experimental and are
subject to change as more experience with the design becomes
available.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] insert or replace - bug or intended behaviour?
> there might be a bug in the replace-conflict handler of sqlite-2.8.15... What you are seeing is the correct behavior; the entire row is replaced. Perhaps you are confusing insert with update. Regards
[sqlite] Python bindings for sqlite 3
Hi. I've been working on some refactoring of the Python bindings for sqlite. I now have a working Python binding for sqlite 3 which is fairly different than the bindings for sqlite 2. I created a quick test case that creates a new table, inserts 500,000 rows, then selects all of them. Memory utilization is printed after the rows have been inserted, after the transaction is committed, after the SELECT is executed, and after the result set has been iterated through. The test can be seen here: http://www.specifixinc.com/~msw/t4.py results: --- sqlite 2 --- [EMAIL PROTECTED] conary]$ rm -f test.db; time python2.3 t4.py 8656 5968 40520 40520 real0m36.269s user0m35.520s sys 0m0.310s --- sqlite3 --- [EMAIL PROTECTED] conary-work]$ rm -f test.db; time python2.3 t4.py 8084 5840 5840 8084 real0m28.635s user0m28.330s sys 0m0.130s The biggest difference in memory utilization comes from storing a compiled statement in the cursor object and fetching new rows only when cursor.fetch*() is called. The old python binding retrieved the entire result set at cursor.execute() time. Using a cursor as an iterator allows us to traverse the result set while using constant memory. Some other major changes: 1) Wildcards in the SQL passed to cursor.execute() now use the sqlite native '?' or ':N:' format. Previously Python syntax was allowed. Making this change lets us bind parameters to compiled SQL statements natively, without converting them to strings. This will also allow us to re-use a compiled statement by rebinding parameters later. 2) Currently the code that does parameter binding does not handle as many types as the old quoting code did, nor does it allow one to register their own converters for custom types. None of the old mx.DateTime works anymore. Newer versions of Python will have the objects required built-in, then adding support is trivial. 3) The pysqlite-specific magic "-- types" SQL statement is no longer supported. Results are returned according to the schema definition or sqlite_column_type() results. 4) Multiple statements in one cursor.execute() call were supported in the old binding. This won't work with the new compiled-statement method, so an exception will be raised. A _PRELIMINARY_ snapshot of the binding can be found here: http://www.specifixinc.com/~msw/pysqlite3-0.0.0.tar.gz I've been doing development in our Conary CVS repository. You can browse the repository here: http://cvs.specifixinc.com/viewcvs/viewcvs.cgi/conary/pysqlite3/ I'm not trying to hijack the work by Gerhard - but our project needed new features that sqlite 3 brings, and we couldn't wait for a few months while new support was added. I welcome feedback, and am more than happy to throw this work away if a better Python binding for sqlite emerges. Cheers, Matt
Re: [sqlite] Problems with the C interface
On Tue, Aug 24, 2004 at 06:58:32PM +0200, Holger Brunck wrote: > > In my case I skip the sqlite_step() command, but I assume that the > sqlite_compile() command is the important one. You have to call sqlite_step(), or the database engine never does anything. Cheers, Matt
RE: [sqlite] Problems with the C interface
Ah sorry, my mistake.
I think you need to actually call sqlite_step for the DELETE to take
effect though.
-Original Message-
From: Holger Brunck [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 24, 2004 5:59 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Problems with the C interface
> You need a call to sqlite_exec() too.
Why do I need this ? I am using the second possibility to execute sql
commands instead of the sql_exec() command and in other cases it works
very well:
sqlite_compile();
sqlite_step();
sqlite_finalize();
In my case I skip the sqlite_step() command, but I assume that the
sqlite_compile() command is the important one.
> -Original Message-
> From: Holger Brunck [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 24, 2004 5:44 PM
> To: [EMAIL PROTECTED]
> Subject: [sqlite] Problems with the C interface
>
> Hi,
> I'm using sqlite 2.8.12 and have a problem in deleting rows from a
> database.
> The sql statement which don't work in my C++ code was:
> C++ Code Segment:
> sql = "DELETE FROM table_1 WHERE
> path='/daten2/documents/my_file.txt';";
> if(SQLITE_OK != sqlite_compile(db, sql, , , ))
{
> cout << "Error:" << dberr << endl;
> free (dberr);
> }
> else
> {
>cout << "Success:" << endl;
>sqlite_finalize(dbcursor, NULL); }
>
> The code segment returns a Success, but the matching file won't be
> deleted from the database. Other sql statements are working verry
well.
> When I try to use the sqlite program instead of invoking the command
> via the C interface and enters the command per command line it was
> removed out of the
> database:
> sqlite> DELETE FROM table_1 WHERE
> sqlite> path='/daten2/documents/my_file.txt';
>
> Know the file was removed out of the database.
> Any hints are welcome.
>
> Kind Regards
> Holger
Re: [sqlite] Problems with the C interface
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Holger Brunck wrote: |>You need a call to sqlite_exec() too. | | Why do I need this ? I am using the second possibility to execute sql commands | instead of the sql_exec() command and in other cases it works very well: | sqlite_compile(); | sqlite_step(); | sqlite_finalize(); | | In my case I skip the sqlite_step() command, but I assume that the | sqlite_compile() command is the important one. Maybe you should assume nothing and instead follow the api reference? | After a virtual machine has been generated using sqlite_compile | it is executed by one or more calls to sqlite_step. -BEGIN PGP SIGNATURE- Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBK3iJSIrOxc3jOmoRAsE+AJ9gvoYOb4NmbuSrVCtMIRD8DUee+wCdGeLL 76CNOTCYylncT6RplV1Za94= =xtOh -END PGP SIGNATURE-
Re: [sqlite] Problems with the C interface
> You need a call to sqlite_exec() too.
Why do I need this ? I am using the second possibility to execute sql commands
instead of the sql_exec() command and in other cases it works very well:
sqlite_compile();
sqlite_step();
sqlite_finalize();
In my case I skip the sqlite_step() command, but I assume that the
sqlite_compile() command is the important one.
> -Original Message-
> From: Holger Brunck [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 24, 2004 5:44 PM
> To: [EMAIL PROTECTED]
> Subject: [sqlite] Problems with the C interface
>
> Hi,
> I'm using sqlite 2.8.12 and have a problem in deleting rows from a
> database.
> The sql statement which don't work in my C++ code was:
> C++ Code Segment:
> sql = "DELETE FROM table_1 WHERE path='/daten2/documents/my_file.txt';";
> if(SQLITE_OK != sqlite_compile(db, sql, , , )) {
> cout << "Error:" << dberr << endl;
> free (dberr);
> }
> else
> {
>cout << "Success:" << endl;
>sqlite_finalize(dbcursor, NULL);
> }
>
> The code segment returns a Success, but the matching file won't be
> deleted from the database. Other sql statements are working verry well.
> When I try to use the sqlite program instead of invoking the command via
> the C interface and enters the command per command line it was removed
> out of the
> database:
> sqlite> DELETE FROM table_1 WHERE path='/daten2/documents/my_file.txt';
>
> Know the file was removed out of the database.
> Any hints are welcome.
>
> Kind Regards
> Holger
[sqlite] Problems with the C interface
Hi,
I'm using sqlite 2.8.12 and have a problem in deleting rows from a database.
The sql statement which don't work in my C++ code was:
C++ Code Segment:
sql = "DELETE FROM table_1 WHERE path='/daten2/documents/my_file.txt';";
if(SQLITE_OK != sqlite_compile(db, sql, , , ))
{
cout << "Error:" << dberr << endl;
free (dberr);
}
else
{
cout << "Success:" << endl;
sqlite_finalize(dbcursor, NULL);
}
The code segment returns a Success, but the matching file won't be deleted
from the database. Other sql statements are working verry well.
When I try to use the sqlite program instead of invoking the command via the C
interface and enters the command per command line it was removed out of the
database:
sqlite> DELETE FROM table_1 WHERE path='/daten2/documents/my_file.txt';
Know the file was removed out of the database.
Any hints are welcome.
Kind Regards
Holger
Re: [sqlite] Upgrading SQLite versions
Very weird... I thought so they would be compatible. But when I delete the old database and restart the program it works fine... (recreation of a new database with the new version)... Nothing else has changed except the sqlite-version (2.8.13->2.8.15). I work with UTF8 1 and ISO8991 0 (or something like that). The two ways I have tried it are: 1/ same box, same program, 2 accounts (via winxp fast user switching)... The 1st one opens the program, the second one opens the program and tries to insert a new adress. No constant polling of the database is done etc?? 2/ 2 computers, common share on windows server... 1 person opens the program on 1 client, 2nd person on 2nd client, 2nd tries to insert an adress... Both setups give 'database is locked'. :S Sorry for the long delay (no internet for 4 days -> arrgh). At 10:15 20/08/2004, you wrote: Steven Van Ingelgem wrote: Hi, 1/ I am currently using 2.8.13 and I wanted to upgrade it to 2.8.15... Now when I do that, my program crashes when I try to open (sqlite_open) the database on the following function: rc = sqliteBtreeFactory(db, zFilename, 0, MAX_PAGES, >aDb[0].pBt); Is that "normal" behaviour (for instance: no binary compatibilities between 2.8.x versions?). Of course that would be better then to immediatly upgrade to 3.0 if i have to convert all the databases ofcourse. But then the same question stays... Is there compatibility between those versions? (again I mean: just update the source & I can still work on the same databases without doing something special). 2/ I use wxSQLite to access databases. When 1 person has my program open (just open, that means not doing anything). Then another person opens a program from a different location, and access the same databases. He can read it without a problem, but he can't do any insert/update/delete ? (so I think he can acquire readlock, but no writelock). [using 2.8.13]. Is this a feature or a bug? (as far as I understood, sqlite locks the database file when it is needed, but not all the time?). Could someone answer those questions, I would be very gratefull. BTW, if I am unclear somewhere, please request more information! Thx, KaReL (aka Steven) *Main Webpage* : http://www.karels0ft.be/ *ICQ #* : 35217584Sqlite minor versions should have compatible database formats, so if you're having crashes, there's probably a problem somewhere else. For the other question, how are the two people accessing the database file? NFS, samba, or just different terminals on the same box? This can make a difference, as locking on network filesystems has varying levels of success(although it is never reccomended). John LeSueur KaReL (aka Steven) Main Webpage : http://www.karels0ft.be/ ICQ # : 35217584
[sqlite] "Malformed database schema"
hi,
I work on Sqlite for Symbian OS, and I have a problem on device.
When I open a new database, I have this message : "malformed database schema -
library routine called out of sequence".
myDB = sqlite_open("//system//programs//demo.sql", 0, );
Who can explain this error ? the same code work fine on emulator.
regards.
Jean
[sqlite] insert or replace - bug or intended behaviour?
Dear all, there might be a bug in the replace-conflict handler of sqlite-2.8.15, but I'm not sure about it. Here is what I do: -- > CREATE TABLE tbl(key integer primary key, value integer, other integer); > INSERT INTO tbl VALUES(1, 1, 3); > INSERT OR REPLACE INTO tbl (key, value) values(1, 2); -- The manual states: "When a UNIQUE constraint violation occurs, the pre-existing rows that are causing the constraint violation are removed prior to inserting or updating the current row." Therefore, the row of key '1' is removed and reinserted with the new value of 'value' - but what happens to the 'other'-data? I would expect, since I explicitly specified the columns to insert-or-replace, that the 'other'-data is still available. Actually, it is lost =( -- > select * from tbl; key|value|other 1|2| -- Bug, or intended behaviour? If the latter, how may I avoid it? Regards Daniel -- Dipl.-Math. (FH) Daniel Franke Institut fuer Medizinische Biometrie und Statistik Medizinische Universität zu Luebeck Ratzeburger Allee 160, Haus 4 23538 Luebeck Telefon: 0451-500-2786 Telefax: 0451-500-2999 [EMAIL PROTECTED] /"\ \ / ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
