[sqlite] Detect nullable fields?
Hi, is there a function to detect nullable fields? Thanks Lothar -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Foreign key support
Hi, I know there is no real support for foreign keys, but I read about the genfkey tool in the shell. This is probably based on the fk tool from Cody Pisto <cpi...@gmail.com> that I am also using. I added support to use the fk source as library in my code to rewrite DDL statements on the fly. Now I get into trouble because I like to recreate the database tables over existing tables. Doing this usually requires to delete tables and recreate them. The fk tool was not implemented to parse DROP TABLE rules, DELETE and SELECT statements. So when I use those statements in my application it will fail. My problem: I want to backport my enhancements, but: Is there a need for such a backport? Is it worth to enhance the fk tool to read the DROP, DELETE and SELECT statements to fully rewrite any SQL statements? I think the DROP statement is simple, but with SELECT and DELETE I see some problems with the complexity of sub queries and my experience with Lex & Jacc. I mean, I use the on the fly rewrite to avoid administrative tools like the Sqlite shell to create databases. Until now I create DDL scripts from UML models by using XSLT template. So my fallback would be rewriting the XSLT template instead. Another simple solution would be deleting the database file before recreating it, but this will break any plans for an upgrade functionality. I thought I could add the fk rewrite code to another library to enable foreign key support, but as a library it would break existing code, if the mentioned SQL statements are not handled correctly. (wxDatabaseLayer) How do you think about adding foreign key support by on the fly rewriting SQL statements? (By adding a compile time flag to activate this) Thanks Lothar -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Encryption and decryption functionality ?
Hi, I have got any information that sqlite supports encryption and decryption. Does it ? If these are extensions, where to get ? Alternative variants available ? Thanks Lothar -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locking issue
Hi, now I have tried the actual version 3.6.3 as of sqlite- amalgamation-3.6.3.tgz, but still get database locks I cannot explain. Brief information: I use two database files. One (lbDMF.db3) for system informations such as what my application should show in forms of a database (Template Designer.db3). The system database therefore is required to store sql queries of the application database (Template Designer.db3). Looking at the files after I have the lock situation (after a successfull close I also get a lock when reopened) I have determined content in Template Designer.db3-journal that shows words (of columns or tables from lbDMF.db3. How could it be, that there is content in that file that fas nothing to do with that database ? Here is the content: Ÿ’˘ °c◊ öà ts/CPP/Test/GUI/wxWrapper/Template Designer .db3 ö Ö V 8 ¸ › æ ü Å c E . ‡ ¿ †åxdJ0 Ú Œ ™ ã l M " 3 IdAnwendungsparameter ! 3 idAnwendungsparameter 3 IDAnwendungsparameter " = IdFormularaktionenzuordnen " = idFormularaktionenzuordnen " = IDFormularaktionenzuordnen ) IdUebersetzungen ) idUebersetzungen ) IDUebersetzungen IdAktionen idAktionen IDAktionen 5 IdAnwendungenFormulare ú Ïÿƒ∞ú LiliLili Lili LuluLulu Lulu LoloLolo Lolo LalaLala Lala TestTest Test öà The words Anwendungsparameter, Formularaktionenzuordnen, Uebersetzungen, Aktionen and AnwendungenFormulare are columns or tables from lbDMF. I am confused !! Also I could rename these files while the application claims they are locked. (This happens while an update attempt I'll give a retry with a shortly closed database to get rid of the lock, but the lock remains) Any hints are welcome. Thanks, Lothar Am 19.09.2008 um 17:37 schrieb Lothar Behrens: > > Am 19.09.2008 um 17:03 schrieb Ken: > >> Try it with the latest full build say version 3.6.2 and see what >> happens instead of a "patched" >> > > I'll give that a try. Could the current code be compiled with Open > Watcom as A DLL ? > (I haven't seen these __declspec(dllexport) and the opposite stuff in > the actual code) > > Lothar > >> > > -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de > Lothar Behrens > Heinrich-Scheufelen-Platz 2 > 73252 Lenningen > > > > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locking issue
Am 19.09.2008 um 17:03 schrieb Ken: > Try it with the latest full build say version 3.6.2 and see what > happens instead of a "patched" > I'll give that a try. Could the current code be compiled with Open Watcom as A DLL ? (I haven't seen these __declspec(dllexport) and the opposite stuff in the actual code) Lothar > -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Database locking issue
Hi,
I am using Version 3.5.2 of Sqlite with the changes of the following
CVS checkin numbers: 4543 and 5243 to get the sqlite3_sql and
sqlite3_next_stmt functions
into my API.
I have patched my files due to the need to figuring out what
statements were unfinalized. But now I get 'database is locked' errors
right after reopening the
database (the closing of the database works as I try to keep my
statements open as short as possible thus closing now works).
Due to this new error ('database is locked') I added reporting
statemens in use. The result of this is a report of the insert
statement itself that causes the error for
this report.
So I am a little confused.
If a database is locked, is there always a statement that causes this
lock, or is the lock keeping even the statement gets finalized ?
Or did I have to apply other patches related to them above ?
Thanks
Lothar
-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Closing database fails due to unfinalized statements
Am 16.09.2008 um 16:51 schrieb Dan: > > On Sep 16, 2008, at 4:44 PM, Lothar Behrens wrote: > >> Hi, >> >> I do have any unfinalized statements in my application when compiled >> on Windows, but not on Mac OS X. >> >> Is there any difference I am missing to attent for ? >> >> How could I see, wich statement (statement handle or SQL query to be >> used in that statement) is unfinalized ? > > Use sqlite3_next_stmt() to find unfinalized statements. sqlite3_sql() > to determine the SQL used to prepare them. Thanks, thats what I need for figuring out what's wrong. Lothar > > > Dan. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Closing database fails due to unfinalized statements
Yes, that's what I expected, since my debugging attempts into my sqlite library version always failed. And it makes it clear why there are these differences. Thanks Lothar Am 16.09.2008 um 17:00 schrieb Jay A. Kreibich: > On Tue, Sep 16, 2008 at 11:44:24AM +0200, Lothar Behrens scratched > on the wall: >> Hi, >> >> I do have any unfinalized statements in my application when compiled >> on Windows, but not on Mac OS X. >> >> Is there any difference I am missing to attent for ? >> >> How could I see, wich statement (statement handle or SQL query to be >> used in that statement) is unfinalized ? >> >> I do not have any differences in my compiler switches (defines). > > Since Mac OS X now comes with a copy of the SQLite libs installed > (that are somewhat out of date), be very careful you're linking > against > your development libs and not the system libs. > > IIRC, there was a behavior change a few versions back having to do > with how unfinalized statements were handled when you attempt to > close the database. > > If you're linking against a recent download on Windows and an older > system lib on the Mac, that might explain the different behavior. > > -j > > -- > Jay A. Kreibich < J A Y @ K R E I B I.C H > > > "Our opponent is an alien starship packed with atomic bombs. We have > a protractor." "I'll go home and see if I can scrounge up a ruler > and a piece of string." --from Anathem by Neal Stephenson > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Closing database fails due to unfinalized statements
Hi, I do have any unfinalized statements in my application when compiled on Windows, but not on Mac OS X. Is there any difference I am missing to attent for ? How could I see, wich statement (statement handle or SQL query to be used in that statement) is unfinalized ? I do not have any differences in my compiler switches (defines). Thanks Lothar -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Am 10.09.2008 um 17:37 schrieb Dennis Cote: > Lothar Behrens wrote: >> What is the function to rollback a transaction or commit ? >> I want also to break into these functions. If there is no way I try >> to implement the rollback and commit callbacks. >> Also the closing of the database would be interesting, or analysing >> the data in the jornal. > > The journal file is closed when a transaction ends. This is done by > the function pager_end_transaction() at line 28880 of the > amalgamation. Note this function is called for both a rollback or a > commit. > Hi, I now have seen that many of my simple select statements automatically does a rollback on behalv of OP_Halt. Also I have seen that an insert, update or delete statement does automatically a commit in some circumstances as: * One VDBE is running only * the statement hits an ON FAIL and have to commit in that case * other circumstances I do not understand yet If I do understand all this correctly I have one case I may stuck into: A select statement (not readonly) is still open (having sqlite3_step() returning SQLITE_ROW) and then I have created an insert statement that is committed but the outer transaction as of the select statement does a rollback if closed later. Thus, this results in readable (just inserted) data but loses these data because the outer rollback occurs. Right ? If so, then I have to redesign something as of this may be the case in my usage of the database API :-) My database form opens a statement to select some data and navigates to one (the first, the next or any other) and leaves the statement open in a transaction I think (form A, database A) as of a call to sqlite3_step() returning SQLITE_ROW. Then I open another database form (form B, database A) and try to add some data. There is no Commit (sqlite3BTreeCommitPhaseOne) or rollback (sqlite3BTreeRollback), so I assume the running transaction from form A is causing this. Then when I close my application the transaction (form A, database A) is rolled back and this loses my data changes. Right ? So my solution would be this: Don't leave sqlite3_step() operations in SQLITE_ROW state. Better try to finish until SQLITE_DONE to close the transaction. I can do this because: * I mostly read only the primary keys of a table (there it is done automatically) to prepare for lazy load (pattern). * I read the full data row for a specific primary key as of any cursor activity. (That way I have simulated full cursor support) I hope with that I get solved this problem. Please comment, If there is something still wrong in my understanding. Thanks Lothar > HTH > Dennis Cote > -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Am 09.09.2008 um 22:49 schrieb Dennis Cote: > Lothar Behrens wrote: >> >> But when you say, that, if jornal files are open, transactions are >> opened, I would set a >> breakpoint at the line of code the transaction opens these jornal >> file >> and I could look >> arount there from who the transaction comes. >> >> Is that an option ? >> >> What function in the sqlite library does this ? >> > > Yes, that is an option if you are using a source code version of > SQLite, > either the individual source files or the amalgamation file, > sqlite3.c. > > The journal file is opened by the function pager_open_journal() at > line > 30868 in the amalgamation source for version 3.6.2. > Yes, It passes the opening of the jornal file as an Op_Transation block (I think so inside of VDBE) of code (Insert). And if I start my application, the first transaction is started as of an select statement to give me back my localized messages (jornal not opened in select statements) I have also checked the cleanup of the prepared statements. They would be finalized as assumed. What is the function to rollback a transaction or commit ? I want also to break into these functions. If there is no way I try to implement the rollback and commit callbacks. Also the closing of the database would be interesting, or analysing the data in the jornal. Thanks Lothar > HTH > Dennis Cote > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Am 09.09.2008 um 20:46 schrieb Dennis Cote:
> Lothar Behrens wrote:
>>
>> I have added this function right after sqlite3_step, that does the
>> prepared insert statement.
>>
>> int nReturn = sqlite3_step((sqlite3_stmt*)(*start));
>>
>>int autocommit = sqlite3_get_autocommit(m_pDatabase);
>>
>>if (autocommit == 0) {
>> wxLogError(_("Warning: Database is not in autocommit mode.\n"));
>>}
>>
>> autocommit is always 1. Also I have thested the following:
>>
>> Open the application and opening the form to display first row -> no
>> jornal file is opened, because no write is yet done.
>>
>> Adding some rows and navigating forward and backbackward -> jornal
>> file is opened and I can see my data in the application.
>>
>
> The fact that a journal file exists at this point implies that you are
> still in a transaction. If you close the database without committing
> this transaction, the changes that you can see in your application
> will
> be rolled back and lost (see H12019 at
> http://www.sqlite.org/c3ref/close.html).
>
> Can you add a function to check the auto commit status in your main
> line
> code (i.e. where you are navigating and viewing the data)?
>
Hmm,
I can add such a function beside the others to update my status line
for sample.
But I don't believe, that I do start any transaction. This is because
if I simply open
only this database form that makes these problems all additions will
be stored and if
I restart my application the data is still there as inserted.
I only start a transaction, when I create tables, because I rewrite
some statements.
But this only happens, when the database is freshly created. Also the
code is tested
and the transaction is committed, otherwise the shema wouldn't exist
after a restart.
But when you say, that, if jornal files are open, transactions are
opened, I would set a
breakpoint at the line of code the transaction opens these jornal file
and I could look
arount there from who the transaction comes.
Is that an option ?
What function in the sqlite library does this ?
Lothar
> Dennis Cote
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Fwd: Insert statement is ok but after reopening the db data is missing ?
> Prior post was too big :-( Here the short anser to my last try to figure out the current file name: > Now I have the result. It is the database file I am thinking to be in. > > seq name file > - > Warning: Unknown column: > 0 Warning: Unknown column: > main Warning: Unknown column: > /Users/lothar/develop/Projects/CPP/Test/GUI/wxWrapper/Template > Designer.db3 > > So, hmm, what's now the problem ? > It is the file I like to have opened. Lothar -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insert statement is ok but after reopening the db data is missing ?
Am 09.09.2008 um 17:20 schrieb Dennis Cote:
> Jay A. Kreibich wrote:
>>
>> Everything you describe sounds exactly as if a transaction has been
>> started, but is not committed. When you close the database, the
>> transaction is automatically (and correctly) rolled back. This will
>> also delete the journal file.
>>
>> I know you said you weren't trying to start a transaction, but you
>> might double check that. Set a breakpoint right after the INSERT is
>> finished and check to see if you have a journal file or not. You
>> could also try issuing a "BEGIN" right after the INSERT. If you get
>> an error, you're already inside a transaction.
>>
>
> An easier and more accurate way to check may be to add a call to
> sqlite3_get_autocommit() after your insert is complete. It will return
> zero if there is an active transaction, and 1 if there is not (i.e. it
> it in autocommit mode).
>
Ok,
I have added this function right after sqlite3_step, that does the
prepared insert statement.
int nReturn = sqlite3_step((sqlite3_stmt*)(*start));
int autocommit = sqlite3_get_autocommit(m_pDatabase);
if (autocommit == 0) {
wxLogError(_("Warning: Database is not in autocommit mode.\n"));
}
autocommit is always 1. Also I have thested the following:
Open the application and opening the form to display first row -> no
jornal file is opened, because no write is yet done.
Adding some rows and navigating forward and backbackward -> jornal
file is opened and I can see my data in the application.
Now I will try to use pragma database_list; but I need to restart my
box.
So I will mail the result later :-(
Lothar
-- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de
Lothar Behrens
Heinrich-Scheufelen-Platz 2
73252 Lenningen
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Insert statement is ok but after reopening the db data is missing ?
Hi, I am still struggle with my insert statement with bound parameters that - followed step by step in the VDBE - seems to be inserted but is not available after closing the database. (Checked with sqliteman) I have also created a small test application using my database API wrappers. In this sample I could insert the data, thus I cannot recreate the problem in a small application. Problem: I prepare an insert statement and bind the parameters that afterwards get executed with sqlite_step(). This function then returns SQLITE_DONE and I prepare a new select statement wich let me display the just inserted data in my database forms (database file not closed while that). I do a close of the database, have a breakpoint after that to see what sqliteman tells me: The table is empty and no jornal file is opened ! How can it be, that 1.) The insert statement succeeds (have been able to select them with a new statement while database is still opened) ? 2.) The engine doesn't store these changes, even I have seen these changes (I do not manually start a transaction that wouldn't be committed) ? Does someone have any more ideas how to narrow the problem ? (After the insert statement until to closing of that file) Thanks in advance, Lothar -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Urgent: sqlite3_step and SQLITE_DONE ?
That is done by the wrapper class wrapping the sqlite API calls - as I understand. It seems really that I don't have any data, because on failure a simple query without where clause also fails. As posted here, I'll see what the PRAGMA database_list gives me back. Thanks, Lothar Am 07.09.2008 um 20:17 schrieb John Stanton: > You get ROW if there is a row available, DONE if there are no rows, or > all the rows have been extracted. Your program needs to test for both > states. > > Lothar Behrens wrote: >> Hi, >> >> I am struggling with the following situation: >> >> I have a table where two rows are inserted. I create a new prepared >> select statement to get a resultset. >> >> After getting the resultset I issue a sqlite3_step to see, if I hava >> any data. Thus of two rows I assume to get >> SQLITE_ROW. >> >> But I get SQLITE_DONE. >> >> My questions: >> >> After getting SQLITE_DONE, do I still have a row in my result set as >> the last row for any sqlite3_step calls ? >> >> Is so, am I allowed to read the columns of this row after a >> sqlite3_reset has been issued ? >> >> Is SQLITE_DONE a flag to be handled as the following SQLITE_ROW + >> 'have the last row now' ? >> >> Thanks >> >> Lothar >> >> -- | Rapid Prototyping | XSLT Codegeneration | http:// >> www.lollisoft.de >> Lothar Behrens >> Heinrich-Scheufelen-Platz 2 >> 73252 Lenningen >> >> >> >> >> >> >> >> >> ___ >> 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 > -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Urgent: sqlite3_step and SQLITE_DONE ?
It is a bit too complex to show my code, because the sqlite2_* functions are wrapped two layers deeper than my code is using. Also the code is in a GUI that supports 'cursors'. Basically I can use the application and the table whose statement will fail to get any rows later, shows me the expected data before re-issuing the query. But this re-issuing is also done on other places in my application with no failure. That makes me wonder. Now I have tried to remove existing where clauses in case of no data and do report an error message when then also no data is available. It fails too. I also tried to figure out if I am in the right database file, but that was ok by the way I checked that. A question: Could I query for the database file from the current sqlite database handle ? Thanks Lothar PS. If nothing helps, I'll try to setup a test case for this with plain sqlite commands, as they appear in my program flow. Am 07.09.2008 um 14:17 schrieb Martin Engelschalk: > Hello Lothar, > > sorry, i did not read your questions fully. > > No, after you get SQLITE_DONE, you do not have any data in your > result set. > After issuing sqlite_reset, you can not read any data, the statement > > Use the statement like this: > - sqlite3_prepare the statement. > - Use sqlite3_bind_xxx to set your bind variables > - execute sqlite_step() while it returns SQLITE_ROW and read the data. > If it returns SQLITE_DONE, > - sqlite3_finalize() your statement or sqlite3_reset() ist to > execute it > again, perhaps with other bind variable values. > > Martin > > Lothar Behrens wrote: >> Hi, >> >> I am struggling with the following situation: >> >> I have a table where two rows are inserted. I create a new prepared >> select statement to get a resultset. >> >> After getting the resultset I issue a sqlite3_step to see, if I hava >> any data. Thus of two rows I assume to get >> SQLITE_ROW. >> >> But I get SQLITE_DONE. >> >> My questions: >> >> After getting SQLITE_DONE, do I still have a row in my result set as >> the last row for any sqlite3_step calls ? >> >> Is so, am I allowed to read the columns of this row after a >> sqlite3_reset has been issued ? >> >> Is SQLITE_DONE a flag to be handled as the following SQLITE_ROW + >> 'have the last row now' ? >> >> Thanks >> >> Lothar >> >> -- | Rapid Prototyping | XSLT Codegeneration | http:// >> www.lollisoft.de >> Lothar Behrens >> Heinrich-Scheufelen-Platz 2 >> 73252 Lenningen >> >> >> >> >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > -- > > * Codeswift GmbH * > Traunstr. 30 > A-5026 Salzburg-Aigen > Tel: +49 (0) 8662 / 494330 > Mob: +49 (0) 171 / 4487687 > Fax: +49 (0) 12120 / 204645 > [EMAIL PROTECTED] > www.codeswift.com / www.swiftcash.at > > Codeswift Professional IT Services GmbH > Firmenbuch-Nr. FN 202820s > UID-Nr. ATU 50576309 > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Urgent: sqlite3_step and SQLITE_DONE ?
Hi, I am struggling with the following situation: I have a table where two rows are inserted. I create a new prepared select statement to get a resultset. After getting the resultset I issue a sqlite3_step to see, if I hava any data. Thus of two rows I assume to get SQLITE_ROW. But I get SQLITE_DONE. My questions: After getting SQLITE_DONE, do I still have a row in my result set as the last row for any sqlite3_step calls ? Is so, am I allowed to read the columns of this row after a sqlite3_reset has been issued ? Is SQLITE_DONE a flag to be handled as the following SQLITE_ROW + 'have the last row now' ? Thanks Lothar -- | Rapid Prototyping | XSLT Codegeneration | http://www.lollisoft.de Lothar Behrens Heinrich-Scheufelen-Platz 2 73252 Lenningen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Locked database problem
Hi, I have s question about avoiding locked database errors by closing a connection in these circumstances. In my test application this works and I also can see that the related data has been inserted. But in my main application I also close the connection without success. Is it possible, that there are some open connections around, opened with char* databaseNameBuffer = "mydb"; int nReturn = sqlite3_open(databaseNameBuffer, _pDatabase1); nReturn = sqlite3_open(databaseNameBuffer, _pDatabase2); Doing some reading on m_pDatabase1 Doing some changing on m_pDatabase2 nReturn = sqlite3_close(m_pDatabase1); and then reopen nReturn = sqlite3_open(databaseNameBuffer, _pDatabase1); to change other data while m_pDatabase2 has locked the database to be the cause of locked database ? Or will m_pDatabase2 be invalid after m_pDatabase1 has been closed ? I have trouble with closing the database and still locking problems. How could I see, if I have such dangling open database handle m_pDatabase2 ? Any help ? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE WHEN sample ?
Am 11.05.2008 um 22:25 schrieb Igor Tandetnik: > "Lothar Behrens" > <[EMAIL PROTECTED]> wrote in > message news:[EMAIL PROTECTED] >> Unique index and using replace into ... changes the primary key value, >> what I cannot handle yet, because I am using triggers >> to force foreign key referential integrity and updating is not yet >> implemented. > > Use simple INSERT, it will fail if uniqueness is violated, preserving > your id. > Now I am using that and INSERT OR IGNORE INTO. Thanks, Lothar > 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
Re: [sqlite] CASE WHEN sample ?
Thanks,
I will answer to both postings:
Unique index and using replace into ... changes the primary key value,
what I cannot handle yet, because I am using triggers
to force foreign key referential integrity and updating is not yet
implemented.
Am 11.05.2008 um 04:23 schrieb Harold Wood & Meyuni Gani:
> Better would be
> Insert into tablea(ida, value1a)
> Select idb, value1b
> from tableb
> where idb not in(select ida from tablea);
>
I do not understand this. Do I have to use a second table, tempory
table or alias ?
If my table is Application, is this then correct ?
INSERT INTO "Application" (id, name) SELECT id, 'lbDMF Manager' from
"Application" where id not in (SELECT id from "Application")
It works syntactically (in sqliteman) but has no effect.
Thanks
Lothar
> Woody
> from his pda
>
> -Original Message-
> From: Lothar Behrens <[EMAIL PROTECTED]>
> Sent: Saturday, May 10, 2008 2:23 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] CASE WHEN sample ?
>
> Hi,
>
> I am searching for a sample that uses the case when expression.
> I like to insert values into a table when these values are not in that
> table before.
>
> My tries to read, understand and try the documentation of expressions
> failed.
>
> Executing this statemen twice creates two rows:
>
> replace into anwendungen (name) values ('lbDMF Manager')
>
> This is pseudo code:
>
> if not exsists (select id from mytable where name = 'some name')
> insert into mytable (name) values ('some name')
>
> Is this possible ?
>
> Thanks
>
> Lothar
>
> ___
> 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
[sqlite] CASE WHEN sample ?
Hi,
I am searching for a sample that uses the case when expression.
I like to insert values into a table when these values are not in that
table before.
My tries to read, understand and try the documentation of expressions
failed.
Executing this statemen twice creates two rows:
replace into anwendungen (name) values ('lbDMF Manager')
This is pseudo code:
if not exsists (select id from mytable where name = 'some name')
insert into mytable (name) values ('some name')
Is this possible ?
Thanks
Lothar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Simpler samples as FTS ?
Hi, I have read about the FTS extension. It would propably a base for my own extension function returning a result set (propably from a virtual table). Is there a more simple example code how to use a extension function that returns multiple rows such as a usual query ? Or are there any documents out describing that at a howto level (not API level docs) ? Thanks, Lothar -- Lothar Behrens | Rapid Prototyping ... Heinrich-Scheufelen-Platz 2 | 73252 Lenningen | www.lollisoft.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Foreign keys
Am 29.12.2007 um 17:00 schrieb Kees Nuyt: Better try it in the sqlite3 command line tool. That's common ground to everyone of us. Maybe no problem in the sqlite api. I will build create table scripts on the fly from XML via XSLT, that way I could also create the triggers. Second problem: The constraint is really ignored. (Referencing a non existent table) Indeed it is ignored. the syntax is parsed, that's all. I need the meta information. Not really forcing foreign key constraints. My application will do that. But when contributing to another library I should also create the required triggers. I have no idea how to catch this while creating the tables (triggers are not possible on system tables). You have several options: 1- Add the required CREATE TRIGGER statements to the schema source by hand. (easiest, that's what I do) Metainformation as above mentioned is required. It has nothing to do with the triggers. 2- Build a tool which parses the schema when you create a database and generates the required CREATE TRIGGER statements before you pipe the CREATE statements into the database. (relatively easy) XML -> XSLT will do that for me - at least. If the engine would create these triggers, I should have to omit that in my script. 3- Build a tool which parses the schema when you open a database and CREATE the triggers if they aren't in place. (more difficult) My currently preferred workaround :-) And it is not really a big issue, because there are existing samples using a Lex and Yacc parser. I tend to borrow parts of that code and build an extension library. Parts of the code would go in the open database code to optionally create the triggers and the rest goes to a function that creates a resultset for the foreign keys of a given table, 4- Change the SQLite source to implement foreign key constraints yourself. (very hard) I then have to more deeply look into the Sqlite source. There are more experienced developers for Sqlite :-) 5- Wait until it is implemented in SQLite. Implementing foreign key constraints is on the ToDo list of the developers (my way) I don't like to wait too long. 3 is better for me. I may create a consistency check while opening the database. That's too late, the database would already be inconsistent. You really need to implement the FK constraint, using triggers or otherwise. The triggers per table are required, I agree, but creating triggers on dangling references could be avoided. Doing a little more than ignoring the foreign constraint would help. Any ideas on this ? You could create a hook on every schema change (look for updates of sqlite_master in the sqlite library source) which checks if the required triggers are already CREATEd and if not, create them. Maybe the best way for both, detecting dangling references and the point to create the triggers. Is a separate system table for relations possible ? If so, the hook function could fill that table while creating the triggers. Also it would be easier for me to query for the foreign keys. Lothar Thanks, Lothar HTH -- ( Kees Nuyt ) c[_] --- -- To unsubscribe, send email to [EMAIL PROTECTED] --- -- -- Lothar Behrens | Rapid Prototyping ... Heinrich-Scheufelen-Platz 2 | 73252 Lenningen | www.lollisoft.de - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Foreign keys
Am 29.12.2007 um 13:59 schrieb Kees Nuyt: Hi Lothar, On Sat, 29 Dec 2007 13:13:04 +0100, Lothar Behrens <[EMAIL PROTECTED]> wrote: The only implementation I'm aware of is the one using triggers, but the creation is not implemented as an extension. http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers http://www.sqlite.org/contrib (second entry) http://www.justatheory.com/computers/databases/sqlite/ foreign_key_triggers.html http://www.rcs-comp.com/site/index.php/view/Utilities- SQLite_foreign_key_trigger_generator Thanks, Lothar In general this will help. But I have encountered a problem: create table IF NOT EXISTS regressiontest ( id int primary key, test char(100) ); create table IF NOT EXISTS test ( id int primary key, id_reg int, constraint fk_reg foreign key (id_reg) references regression (id) ); First problem (propably only in sqliteman): The statement could not be executed at once ?? Second problem: The constraint is really ignored. (Referencing a non existent table) I have no idea how to catch this while creating the tables (triggers are not possible on system tables). I may create a consistency check while opening the database. Any ideas on this ? Thanks, Lothar -- Lothar Behrens | Rapid Prototyping ... Heinrich-Scheufelen-Platz 2 | 73252 Lenningen | www.lollisoft.de - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Foreign keys
Hi all, now I have got more information on how to implement foreign key support. My plan is to use extension functions to do it by parsing the system table's sql statement and returning a result set array or the like. Before I start implementing the extension, has someone done such an implementation that could be reused ? (public domain or LGPL is preferred) Thanks, Lothar -- Lothar Behrens | Rapid Prototyping ... Heinrich-Scheufelen-Platz 2 | 73252 Lenningen | www.lollisoft.de - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Foreign keys ?
Hi, I am new to the list and to Sqlite. Reading over the archive didn't helped me much. It was only a 'flight' over the result searching for 'foreign'. What I have captured is the ability to define fireign keys in the tables and with the help of Cody Pisto creating the constraints as triggers. My question: Is there an API function to get the foreign keys per table ? Or must I use always the code from Cody to get the information ? If there is no solution, is it possible to automate this by modifying the CREATE TABLE code to trigger the code of Cody and hold the information in some 'system tables' (DROP TABLE could remove these informations again) ? Then the API could be extended by looking in these tables for foreign keys, what would be reasonably fast. Thanks, Lothar -- Lothar Behrens | Rapid Prototyping ... Heinrich-Scheufelen-Platz 2 | 73252 Lenningen | www.lollisoft.de
