Re: [sqlite] Can you perform an ATTACH-type operation just using DB handles ?
On Thu, 16 Oct 2008 17:54:52 + (UTC), MikeW <[EMAIL PROTECTED]> wrote: >My code has two pre-opened DB connection handles, and would like to >perform an SQL operation over the two databases. >The DB filenames are not available at this point, hence an ATTACH is >not feasible. >Is there any way to achieve this by applying an ATTACH at the handle level ? I don't think so, but check into "PRAGMA database_list" (See http://www.sqlite.org/pragma.html ). JAB -- John A. Belli Software Engineer Refrigerated Transport Electronics, Inc. http://www.rtelectronics.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can you perform an ATTACH-type operation just using DB handles ?
My code has two pre-opened DB connection handles, and would like to perform an SQL operation over the two databases. The DB filenames are not available at this point, hence an ATTACH is not feasible. Is there any way to achieve this by applying an ATTACH at the handle level ? Regards, MikeW ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_SCHEMA question
In case anyone's following along and would care to comment it seems like what's happening is: 1. One connection does a DROP TABLE then CREATE TABLE, in back-to-back calls to sqlite3_exec. 2. Another connection does a sqlite3_get_table, which calls sqlite3_exec, which calls sqlite3_prepare, which calls sqlite3LockAndPrepare, which calls sqlite3Prepare, which calls schemaIsValid. 3. schemaIsValid compares the db's cookies to the connection's copy of those cookies and returns 0 because of the DROP TABLE / CREATE TABLE. 4. sqlite3Prepare calls sqlite3ResetInternalSchema to clear it up for next time, and returns the SQLITE_SCHEMA error. 5. The while loop in sqlite3_exec tries once more and so calls sqlite3_prepare again. I would expect it to have succeeded this time, because of the call to sqlite3ResetInternalSchema, but perhaps the DROP TABLE is happening before the first call and the CREATE TABLE is happening between the calls somehow? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign Key Triggers: ABORT, FAIL, or ROLLBACK
On Tue, 14 Oct 2008 22:10:12 +0700, Dan <[EMAIL PROTECTED]> wrote: >ABORT seems right to me. Causes the current statement to have no >effect, but does not rollback the current transaction. On Tue, 14 Oct 2008 11:16:17 -0500, Stephen Woodbridge <[EMAIL PROTECTED]> wrote: >I'm not sure there is a "standard" answer. The "right" answer probably >depends on the needs of the application and whether it is robsut enough >to deal with various situations that might occur that would trigger a >conflict. > >For example: > >1) under what conditions might a conflict occur? >2) does the application code doing the insert have a recovery if it fails. >3) is the application communicating back to a real user that can decide >how to handle the situation? >4) is this an automated process that needs to work or fail leaving the >DB unchanged on failure? After considering both of your inputs, I have changed the triggers to "ABORT." I always check my returns and issue a ROLLBACK on errors, so this is really the best choice. JAB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite & ISO8859-x characters (Linux, C)
Wow, a whole lot of material... thanks. Starting to read it all... -- pozdrawiam / regards Zbigniew Baniewski ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_SCHEMA question
I found that it is actually a call to sqlite3_get_table that is returning this error, not sqlite3_exec as I had originally thought. Does that make the cause of getting SQLITE_SCHEMA any easier to pinpoint? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Malformed schema error on sqlite3odbc.dll with version 0.79
I'm not 100% sure about this, but I think SQLiteODBC is a third party project. You might have better luck contacting the original developer (http://www.ch-werner.de/sqliteodbc/) // -- Enrique Ramirez Irizarry Lead Developer Indie Code Labs http://www.indiecodelabs.com On Wed, Oct 15, 2008 at 2:30 PM, Richard Kim <[EMAIL PROTECTED]> wrote: > > Hi guys, > > We are using sqlite as our database and noticed some weird odbc driver > problem. > > "malformed database schema (deleteUserGroupsByUsers) - no such > table: main.Users (11)" > > If I open the database with other tool such as sqlite studio, or firefox > plug in, there is no problem - the schema check seems to be okay, and > all the tables are there.. etc. > Also If I use S3ODBC.dll, we don't see ths issue of the malformed > database schema either. > > It happens only if I use sqlite3odbc.dll with version 0.79. > > Is this known bug ?? > > Thanks > > Richard K > > ___ > 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] Error:"Expression cannot be evaluated" withsqlite3_exec()
On Thu, 16 Oct 2008 08:23:10 + (GMT), Alberto Tellaeche
<[EMAIL PROTECTED]> wrote:
>int ActualizarBBDDAlumnos(char *dni, float nota)
>{
>char *errorMsg;
>
>char *orden_SQL = sqlite3_mprintf("update ALUMNO set nota=%0.1f where
> dni=%Q;",nota,dni);
>sqlite3_exec(db,orden_SQL,0,0,);
>sqlite3_free(errorMsg);
>sqlite3_free(orden_SQL);
>return(0);
>}
"db" appears to be a global. Are you certain that is has been properly
initialized with sqlite3_open(), sqlite3_open16() or sqlite3_open_v2()
before calling this function?
JAB
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_SCHEMA question
On Oct 15, 2008, at 10:11 PM, Roger Binns rogerb-at-rogerbinns.com | sqlite| wrote: > The main cause of them failing is if the statement becomes > invalid. One example would be if it uses a collation that was > unregistered. I only use sqlite3_exec, so I don't keep prepared statements around. So does this mean that the schema change has to be happening between sqlite3_exec preparing and executing the statement? I also don't do any collation, and definitely no unregistration of collation. All the occurrences that I see are for the same "SELECT f1 AS f1, f2 AS f2 FROM t1" query. Since the application has lots of queries I'd expect to see this schema problem happen on some of the others, but at least so far I have not. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Questions about "analyze"
Hi to all,
I have a table with many record, that have a field normally with value '0'.
Occasionally i update some records by setting the field to '1' to mark
it, and after in a background job, i localized them for working on it.
For that, i have an index on that field.
My problem: if i run an "analyze" when all records have the field equal
to '0', the localization (select .. where field != 0) don't use the
index, and do a full-table-scan.
If at least one record is set to '1', the index are used.
It's seem that "analyze" discover that all records have same field
value, so mark the index "useless".
Solutions that i know:
- run "analyze" after updating to '1' some records. :(
- maintain at least one fake record with '1' :(
- never launch "analyze" :(
Or... (please help!)
- exists some options to control this behiavour of analyze?
- it's possible to force the use of the index?
Thanks for feedback!
-- Test case:
-- Creating test table
CREATE TABLE os_test
(
codeint NOT NULL,
markint NOT NULL,
PRIMARY KEY (code)
);
-- Creating an index
CREATE INDEX os_test_index_mark ON os_test (mark);
-- Filling with some data
insert into os_test values ('a',0);
insert into os_test values ('b',0);
insert into os_test values ('c',0);
-- Testing indexes
explain query plan
select code from os_test where mark == 1
-- Result: "TABLE os_test WITH INDEX os_test_index_mark"
-- Right.
-- Launch "analyze"
analyze
-- Re-Testing indexes
explain query plan
select code from os_test where mark == 1
-- Result: "TABLE os_test"
-- Wrong.
-- Updating one record
update os_test set mark=1 where code='a'
-- Re-Testing indexes
explain query plan
select code from os_test where mark == 1
-- Result: "TABLE os_test"
-- Wrong.
-- Re-Analyze
analyze
-- Re-Testing indexes
explain query plan
select code from os_test where mark == 1
-- Result: "TABLE os_test WITH INDEX os_test_index_mark"
-- Right.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Compiling with mingw32/msys, and using options
You can add it as an option to either configure or make. For a makefile generated from configure, you would do something like: make OPTS=-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT=1 TCL is needed to build the amalgamation, as TCl scripts are used to generate the "amalgamated" file. ActiveTCL works. On Wed, Oct 15, 2008 at 2:40 PM, John Belli <[EMAIL PROTECTED]>wrote: > Perhaps I just don't know how to use configure and make, but I can't > figure out how to use, for example, SQLITE_ENABLE_UPDATE_DELETE_LIMIT > when compiling with msys. Do I add it somehow when calling configure, > when calling make, or do I edit the Makefile after configure? I am > having no problem getting "sqlite.exe" out by following the README, or > with options that show up in "configure --help". > > Also, is tcl required to produce an amalgamation? All this started > when I found the amalgamation does not support the DELETE ... LIMIT > syntax. If so, can I use ActiveState tcl or should I build from > source? > > > JAB > > ___ > 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] Syntax error
"Darryl Hebbes" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > > I am getting a syntax error like this : > > near ".": syntax error > > when I run the following code. > > > UPDATE site_profile > SET site_profile.title = (select site_profiletemp.title from Make it SET title = (select site_profiletemp.title from The SET clause of UPDATE statement requires a plain unqualified column name. > site_profiletemp where site_profile.title = site_profiletemp.title), > site_profile.title = (select site_profiletemp.title from > site_profiletemp where site_profile.title = site_profiletemp.title) You have the exact same assignment twice. Drop one of them. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error:"Expression cannot be evaluated"withsqlite3_exec()
"Alberto Tellaeche" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > after sqlite3_mprintf() function, I obtain the string like this: " > update ALUMNO set nota=0.2 where dni='12345678J' " > After sqlite3_exec() errorMsg=NULL (0x Bad ptr), and the > command is not exectuted in table ALUMNO, the value is not changed. How do you know the value has not changed? How do you check it? Do you, by any chance, open a transaction (by executing a BEGIN statement) but don't commit it? If you close the database or exit your application while the transaction is still open, it will roll back. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with inserting and integer primary key
On Wed, 15 Oct 2008 12:43:00 -0400, "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: >Be aware that, in order to create a column that aliases ROWID (and thus >has special properties in SQLite, such as getting an automatically >assigned unique integer) it has to be spelled precisely INTEGER PRIMARY >KEY. Note that INT PRIMARY KEY is not sufficient, which may be useful. JAB -- John A. Belli Software Engineer Refrigerated Transport Electronics, Inc. http://www.rtelectronics.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compiling with mingw32/msys, and using options
Perhaps I just don't know how to use configure and make, but I can't figure out how to use, for example, SQLITE_ENABLE_UPDATE_DELETE_LIMIT when compiling with msys. Do I add it somehow when calling configure, when calling make, or do I edit the Makefile after configure? I am having no problem getting "sqlite.exe" out by following the README, or with options that show up in "configure --help". Also, is tcl required to produce an amalgamation? All this started when I found the amalgamation does not support the DELETE ... LIMIT syntax. If so, can I use ActiveState tcl or should I build from source? JAB ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Syntax error
Hi, I am getting a syntax error like this : near ".": syntax error when I run the following code. UPDATE site_profile SET site_profile.title = (select site_profiletemp.title from site_profiletemp where site_profile.title = site_profiletemp.title), site_profile.title = (select site_profiletemp.title from site_profiletemp where site_profile.title = site_profiletemp.title) WHERE EXISTS (select * from site_profiletemp where site_profile.title = site_profiletemp.title); I want to update one table with data from another. Many Thanks Darryl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error:"Expression cannot be evaluated" withsqlite3_exec()
Hello Igor;
I have modified the code to avoid string writting problems now, it is:
int ActualizarBBDDAlumnos(char *dni, float nota)
{
char *errorMsg;
char *orden_SQL = sqlite3_mprintf("update ALUMNO set nota=%0.1f where
dni=%Q;",nota,dni);
sqlite3_exec(db,orden_SQL,0,0,);
sqlite3_free(errorMsg);
sqlite3_free(orden_SQL);
return(0);
}
after sqlite3_mprintf() function, I obtain the string like this: " update
ALUMNO set nota=0.2 where dni='12345678J' "
After sqlite3_exec() errorMsg=NULL (0x Bad ptr), and the command is not
exectuted in table ALUMNO, the value is not changed.
If I type exactly the same order from the sqlite3 command line, it works
perfectly.
The database and tables where created folloeing the examples in
http://www.sqlite.org/sqlite.html ,with CREATE TABLE commands and INSERT INTO
TABLE commands.
I still don´t understand what is happening, so, as always, thank you very much.
Regards,
Alberto
- Mensaje original
De: Igor Tandetnik <[EMAIL PROTECTED]>
Para: sqlite-users@sqlite.org
Enviado: miércoles, 15 de octubre, 2008 22:18:48
Asunto: Re: [sqlite] Error:"Expression cannot be evaluated" withsqlite3_exec()
Alberto Tellaeche <[EMAIL PROTECTED]>
wrote:
> Tank you very much for the response.
> the array is as expected, sprintf is woking fine. Also I get the same
> error if I write the SQL command directly in the sqlite3_exec()
> function, so this is not the error...
Show the CREATE TABLE statement for ALUMNO table. Show the exact text of
the statement you run, after sprintf.
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
