Re: [sqlite] why doesn't this work? (fts rowids)
fts1 and fts2 have a design flaw which assumes semantics for the standard SQLite rowid which aren't actually provided by SQLite across calls to VACUUM. fts3 will fix this, either making the rowid be a persistent idenfier across VACUUM, or by exposing a new id (or docid) column which operates as a persistent identifier. Either way, you could reliably tie an fts3 table to some other table. Ideally, you could hide this all behind a view, but I'm not sure that the MATCH operator can be exposed through a view. The problem with your ideal solution is that there are likely to be three or four such solutions which don't have significant overlap. I would like to avoid generalizing fts too much, and keep things focussed very tightly on the text-indexing part, leaving higher-level stuff to SQLite proper, where possible. This may leave fts feeling a little bit grafted on, but ... well, it _is_ a little bit grafted on, that's a feature! -scott On 8/15/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > With FTS3 can you specify the rowid to use in SQL or is it always automatic? > It seems like most commonly you'd want the FTS data to match up with a real > table using the same key and not have to store the FTS key in a separate > table. Ideally I'd want to be able to include a single foreign key indexed > integer field so a FTS table would like like: > > OID > FK_ID > FTS_FIELDS > > So you can associate multiple FTS records with a single standard record > without having to create a linking table (linking tables typically define a > many-to-many relationship and here we have a one-to-many relationship). > > FTS's job isn't to enforce constraints, but it allowing users to link FTS > data to regular data. > > Sam > > > --- > We're Hiring! Seeking a passionate developer to join our team building Flex > based products. Position is in the Washington D.C. metro area. If interested > contact [EMAIL PROTECTED] > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite crash in two lines...
Ok great - I guess I need to check for upgrades more often. And as for what I was doing - well I have a program that generates queries (not very well it appears) and it would occasionally crash - when I tracked the bug down I was able to simplify it to two lines. Samuel R. Neff wrote: I get a crash with 3.3.12 but correct syntax error in 3.4.2 on Windows XP using exe's downloaded from sqlite.org. Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\sam>sqlite3 SQLite version 3.3.12 Enter ".help" for instructions sqlite> create table test(field text); sqlite> select * from test group by distinct field; Crash. Windows creates a dmp file but I can't copy it or do anything with it (says in use). All I can do is send it to Microsoft--think they'd help? ;) Downloaded newer exe and... Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\sam>sqlite3 SQLite version 3.4.2 Enter ".help" for instructions sqlite> create table test(field text); sqlite> select * from test group by distinct field; SQL error: near "distinct": syntax error sqlite> No crash. Not sure what the OP is even trying to accomplish though.. it's certainly not valid SQL. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] why doesn't this work? (fts rowids)
With FTS3 can you specify the rowid to use in SQL or is it always automatic? It seems like most commonly you'd want the FTS data to match up with a real table using the same key and not have to store the FTS key in a separate table. Ideally I'd want to be able to include a single foreign key indexed integer field so a FTS table would like like: OID FK_ID FTS_FIELDS So you can associate multiple FTS records with a single standard record without having to create a linking table (linking tables typically define a many-to-many relationship and here we have a one-to-many relationship). FTS's job isn't to enforce constraints, but it allowing users to link FTS data to regular data. Sam --- We're Hiring! Seeking a passionate developer to join our team building Flex based products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Sqlite crash in two lines...
I get a crash with 3.3.12 but correct syntax error in 3.4.2 on Windows XP using exe's downloaded from sqlite.org. Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\sam>sqlite3 SQLite version 3.3.12 Enter ".help" for instructions sqlite> create table test(field text); sqlite> select * from test group by distinct field; Crash. Windows creates a dmp file but I can't copy it or do anything with it (says in use). All I can do is send it to Microsoft--think they'd help? ;) Downloaded newer exe and... Microsoft Windows XP [Version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp. C:\Documents and Settings\sam>sqlite3 SQLite version 3.4.2 Enter ".help" for instructions sqlite> create table test(field text); sqlite> select * from test group by distinct field; SQL error: near "distinct": syntax error sqlite> No crash. Not sure what the OP is even trying to accomplish though.. it's certainly not valid SQL. Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite crash in two lines...
I was using sqlite3.exe on windows xp when I discovered it. Then, for confirmation, I ran the same set of commands on the same version of sqlite3 (a version I think I compiled) on a linux machine and got the same thing. Then, for further confirmation, I *also* ran it on a FreeBSD machine (where it was compiled from the ports collection) and got the same problem. I can send you the coredump from each machine if you want. [EMAIL PROTECTED] wrote: "Christopher J. McKenzie" <[EMAIL PROTECTED]> wrote: Try this: $ sqlite3 SQLite version 3.4.0 Enter ".help" for instructions sqlite> create table test (field text); sqlite> select * from test group by distinct field; Segmentation fault $ I get: SQLite version 3.4.0 Enter ".help" for instructions sqlite> create table test(field text); sqlite> select * from test group by distinct field; SQL error: near "distinct": syntax error sqlite> So did you compile SQLite yourself or did you use a prebuilt binary? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Sqlite crash in two lines...
"Christopher J. McKenzie" <[EMAIL PROTECTED]> wrote: > Try this: > > $ sqlite3 > SQLite version 3.4.0 > Enter ".help" for instructions > sqlite> create table test (field text); > sqlite> select * from test group by distinct field; > Segmentation fault > $ > I get: SQLite version 3.4.0 Enter ".help" for instructions sqlite> create table test(field text); sqlite> select * from test group by distinct field; SQL error: near "distinct": syntax error sqlite> So did you compile SQLite yourself or did you use a prebuilt binary? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Sqlite crash in two lines...
Try this: $ sqlite3 SQLite version 3.4.0 Enter ".help" for instructions sqlite> create table test (field text); sqlite> select * from test group by distinct field; Segmentation fault $ oops... Any idea? ~chris. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] why doesn't this work? (fts rowids)
At this time I do not plan to provide a way to distinguish "INTEGER
PRIMARY KEY AUTOINCREMENT" and "INTEGER PRIMARY KEY" for fts3. fts is
in the business of doing a fulltext index, not enforcing constraints!
If you require that level of operation, the appropriate solution would
be to have a parallel table defined the way you want it to be, which
is used to generate the rowids for fts. For instance:
CREATE VIRTUAL TABLE x USING fts3;
CREATE TABLE y (id INTEGER PRIMARY KEY AUTOINCREMENT);
-- ...
BEGIN;
INSERT INTO y VALUES (null);
INSERT INTO x (rowid, content) VALUES (LAST_INSERT_ROWID(), 'Some text');
COMMIT;
This is similar to how SQLite creates a sequence generator when you
use AUTOINCREMENT. It is likely that I'll expose a docid column, to
prevent confusion around how rowid works, I'm still thinking about
this. The virtual table interface recently aquired the ability to
export HIDDEN columns, which would be useful for exporting such a
column.
-scott
On 8/15/07, Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- Adam Megacz <[EMAIL PROTECTED]> wrote:
> > Hello, I need to create a perpetually-unique column in an FTS2 table.
> > For example:
> >
> > create virtual table t using fts2(c);
> > insert into t (c) values ('a');
> > select rowid,c from t;
> > 1|a
> > delete from t;
> > insert into t (c) values ('b');
> > select rowid,c from t;
> > 1|b
> >
> > How can I get the last query to return some value other than "1" (the
> > column need not be called ROWID; any name will work)?
>
> Presumably fts3 will support a column along the lines of:
>
> id INTEGER PRIMARY KEY AUTOINCREMENT
>
>
>
>
>
> Got a little couch potato?
> Check out fun summer activities for kids.
> http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] why doesn't this work? (fts rowids)
--- Adam Megacz <[EMAIL PROTECTED]> wrote:
> Hello, I need to create a perpetually-unique column in an FTS2 table.
> For example:
>
> create virtual table t using fts2(c);
> insert into t (c) values ('a');
> select rowid,c from t;
> 1|a
> delete from t;
> insert into t (c) values ('b');
> select rowid,c from t;
> 1|b
>
> How can I get the last query to return some value other than "1" (the
> column need not be called ROWID; any name will work)?
Presumably fts3 will support a column along the lines of:
id INTEGER PRIMARY KEY AUTOINCREMENT
Got a little couch potato?
Check out fun summer activities for kids.
http://search.yahoo.com/search?fr=oni_on_mail&p=summer+activities+for+kids&cs=bz
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] why doesn't this work? (fts rowids)
Hello, I need to create a perpetually-unique column in an FTS2 table.
For example:
create virtual table t using fts2(c);
insert into t (c) values ('a');
select rowid,c from t;
1|a
delete from t;
insert into t (c) values ('b');
select rowid,c from t;
1|b
How can I get the last query to return some value other than "1" (the
column need not be called ROWID; any name will work)?
- a
--
PGP/GPG: 5C9F F366 C9CF 2145 E770 B1B8 EFB1 462D A146 C380
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
If you have only one thread accessing the file. Then you shouldn't need to do any type of locking per se. I would leave the file locks. I would not induce your own mutex. Sqlites locking should be adequate. I have a system where there are two threads sharing a single db and each thread createing and release access to various DB's all without using any mutexes for sqlite synchronization. Is this a single database file with each thread having its own connection? If that is the case then sure you should expect and handle the sqlite_busy. Did you configure/compile with:configure --enable_threadsafe ? Mark Brown <[EMAIL PROTECTED]> wrote: No, not a soft link. :) Based on other posts I have read about threading performance and SQLite, it seems like most people like to use a single thread. I'm going to change our application to use a system-wide mutex for thread synchronization and see if that improves our results. I'm still thinking our problems may be low-level file i/o bugs with our OS, so perhaps taking out the file-based .lock scheme will help. Thanks, Mark > -Original Message- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 15, 2007 12:39 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > > > It should not. > > As long as those two connections are not used across threads > and point to truely different databases. > > They wouldn't be a soft link would they? I > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
No, not a soft link. :) Based on other posts I have read about threading performance and SQLite, it seems like most people like to use a single thread. I'm going to change our application to use a system-wide mutex for thread synchronization and see if that improves our results. I'm still thinking our problems may be low-level file i/o bugs with our OS, so perhaps taking out the file-based .lock scheme will help. Thanks, Mark > -Original Message- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 15, 2007 12:39 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > > > It should not. > > As long as those two connections are not used across threads > and point to truely different databases. > > They wouldn't be a soft link would they? I > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: INDEXES and PRIMARY KEY
Igor Mironchick <[EMAIL PROTECTED]> wrote: Can anybody explain me for what PRIMARY KEY needed? PRIMARY KEY is roughly equivalent to UNIQUE NOT NULL. That is, you get an index on this column, the database enforces that no two rows have the same value, and doesn't allow NULLs in this column. In addition, a column declared as INTEGER PRIMARY KEY has a special meaning in SQLite. For more details, see http://sqlite.org/autoinc.html For example, is there some pluses using PRIMARY KEY insted of a simple INTEGER column (when I connect two tables by values of this column in SELECT queries)? An index would likely make this operation faster (of course, you can explicitly create an index on plain INTEGER column). And is PRIMARY KEY auto increment his value when inserting new value in a table? Yes, a unique value will be automatically generated for an INTEGER PRIMARY KEY column if none is specified in the INSERT statement. I mean can I use INTEGER PRIMARY KEY and set him by himself? Yes. Note that you will get an error if this value already exists in the table. And for what a INDEX? http://en.wikipedia.org/wiki/Index_%28database%29 Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA writable_schema=ON;
--- Scott Hess <[EMAIL PROTECTED]> wrote: > On 8/15/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > > If you find a way to get sqlite3 to re-parse the schema after your direct > > sqlite_master change, please post it to the list. I don't think it can > > be done without modifying the code or making a new connection. > > You could probably manage it by doing some other schema change. Yeah, you may be able to force a reload of a schema related to a specific tbl_name with ALTER TABLE xxx RENAME TO yyy (twice, presumably). But that's an even worse hack. :-) CREATE TABLE will do a schema reload, but that's self-defeating. I can't see anything that will force a reload of view or trigger definitions, barring dropping and recreating them, which again defeats the purpose. This is just a philosophical exercise - it's all hacking territory anyway, as Dan put it. Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us. http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] INDEXES and PRIMARY KEY
Hi, guys. Can anybody explain me for what PRIMARY KEY needed? For example, is there some pluses using PRIMARY KEY insted of a simple INTEGER column (when I connect two tables by values of this column in SELECT queries)? And is PRIMARY KEY auto increment his value when inserting new value in a table? I mean can I use INTEGER PRIMARY KEY and set him by himself? And for what a INDEX? P.S. Sorry for so newbies questions :) -- Regards, Igor Mironchick, Intervale © #ICQ 492-597-570 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY.
I'd love to do fts2_1, because it implies fts1_1, but, really, 2_1 implies that the data would be backward-compatible, and maybe there's just a new feature exposed or something. -scott On 8/14/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: > > +1 for fts3 or fts2_1 :-) > > --- > We're Hiring! Seeking a passionate developer to join our team building > products. Position is in the Washington D.C. metro area. If interested > contact [EMAIL PROTECTED] > > -Original Message- > From: Scott Hess [mailto:[EMAIL PROTECTED] > Sent: Tuesday, August 14, 2007 8:22 PM > To: [EMAIL PROTECTED] > Cc: sqlite-users@sqlite.org > Subject: Re: [sqlite] ALTER TABLE and INTEGER PRIMARY KEY. > > On 8/14/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > "Scott Hess" <[EMAIL PROTECTED]> wrote: > > > I was getting ready to checkin the rowid-versus-fts2 fix, and wanted > > > to add one last bit, to upgrade older tables. > > > > > > Unfortunately, code of the form: > > > > > >ALTER TABLE x_segments ADD id INTEGER PRIMARY KEY; > > > > > > is documented as not supported. > > > http://www.sqlite.org/lang_altertable.html . As far as I can tell, > > > this means that there is no option to do a cheap schema upgrade to get > > > the correct semantics. Am I missing a trick? > > > > It appears that you can set > > > >PRAGMA writable_schema=ON; > > > > Then do a manual UPDATE of the sqlite_master table to insert > > an "id INTEGER PRIMARY KEY" into the SQL for the table definition. > > I tried it and it seems to work. But it is dangerous. If you > > mess up, you corrupt the database file. > > Ooh, I think that tips me away from fixing fts2, because it's scary > and Google Gears disables PRAGMA. > > At least Joe threw in a vote for just versioning things to fts3 - > anyone want to vote against? > > -scott > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Help with performance...
Joe, each of the tables involved also had a parent table. that was also being copied. It turned out that the parent table copy (insert .. select) was taking over 50% of the time. So I flattened the tables including the neccessary fields into the children tables. This doubled the throughput in 2 places, actually creating the original and for the copy component. Thanks for your patience and allowing me to bounce ideas. Ken Joe Wilson <[EMAIL PROTECTED]> wrote: Forget about the alternate insert statements I suggested. Assuming "id" is declared INTEGER PRIMARY KEY in all tables, you can't get better performance than this in a single insert statement: insert into x select x1.* from a.x x1, y where x1.id = y.id; Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out. http://answers.yahoo.com/dir/?link=list&sid=396545433 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
It should not. As long as those two connections are not used across threads and point to truely different databases. They wouldn't be a soft link would they? I Mark Brown <[EMAIL PROTECTED]> wrote: Hi John- There is a .lock file for each database. From my understanding, that should prohibit 2 connections from using the same database at the same time. However, that is not the situation I am wondering about. I am specifically wondering if database activity on a connection to DB 1 would have any effect on database activity on a different connection to DB2. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
--- Mark Brown <[EMAIL PROTECTED]> wrote: > There is a .lock file for each database. From my understanding, that should > prohibit 2 connections from using the same database at the same time. > However, that is not the situation I am wondering about. I am specifically > wondering if database activity on a connection to DB 1 would have any effect > on database activity on a different connection to DB2. Try your sqlite concurrency test under UNIX/Linux on a local filesystem to see if it produces the same serialized access. Based on my limited knowledge of sqlite, I think separate connections to different databases should not impede each other. Would the SQLite developers care to give the definitive statement on this? Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. http://get.games.yahoo.com/proddesc?gamekey=monopolyherenow - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA writable_schema=ON;
On 8/15/07, Joe Wilson <[EMAIL PROTECTED]> wrote: > If you find a way to get sqlite3 to re-parse the schema after your direct > sqlite_master change, please post it to the list. I don't think it can > be done without modifying the code or making a new connection. You could probably manage it by doing some other schema change. Maybe create a table then drop the table as two separate transactions. You might need to do it in a different connection, though, which isn't much better than simply closing and re-opening the current connection. -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] random(*), randomblob(N), etc.
Now thats a good idea! Thanks! On 8/15/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > "Shane Harrelson" <[EMAIL PROTECTED]> wrote: > > > > Other than the normal caveats for using customized versions of the > SQLite > > code, does this sound like it would work? > > > > Why not just write your own version of random() and register > it using sqlite3_create_function()? > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] PRAGMA writable_schema=ON;
--- T&B <[EMAIL PROTECTED]> wrote: > > Even if you got the sqlite_master table entries right, the in- > > memory sqlite schema data structures would not be in sync. > > Yes, but my point/question was, would that not only apply to tables > and indexes? I expect that views and triggers have no data structures > (eg rootpage = 0) so there's nothing to get out of sync. Change the SQL column for a VIEW in sqlite_master and see for yourself that sqlite3 does not pick it up. You have to drop the connection and make a new connection to see it. > > You'd be better off using the normal DROP/CREATE SQL statements and > > published sqlite APIs to do this sort of thing. > > Ideally, yes. But I've come across a few situations (such as the one > I gave as an example) where being able to write to the sqlite_master > tables would permit a solution that could be done totally in SQL, or > which would save parsing schema. If you find a way to get sqlite3 to re-parse the schema after your direct sqlite_master change, please post it to the list. I don't think it can be done without modifying the code or making a new connection. You would also be at the mercy of future incompatible internal SQLite changes. Only using the public API protects you from such future breakage. Pinpoint customers who are looking for what you sell. http://searchmarketing.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Altering views, keeping triggers
Hi Joe, You've got the right idea. Just make use of sqlite_master.tbl_name. select sql || ';' from sqlite_master where type = 'trigger' and tbl_name = 'MyView'; sqlite_master.name is the name of the table/view/index/trigger itself, and sqlite_master.tbl_name is what it acts on. I think that's mostly right. tbl_name "is what it acts on" for an index and trigger, but not for a view. Unfortunately, for a view, tbl_name is just the name of the view, not the table (or view) that it acts on. That's partly understandable since a view could act on more than one table. But I had overlooked the fact that tbl_name will tell me what a trigger acts on. So thanks a lot for your thoughtful posting. That will help :-) Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA writable_schema=ON;
Hi Joe, Even if you got the sqlite_master table entries right, the in- memory sqlite schema data structures would not be in sync. Yes, but my point/question was, would that not only apply to tables and indexes? I expect that views and triggers have no data structures (eg rootpage = 0) so there's nothing to get out of sync. You'd be better off using the normal DROP/CREATE SQL statements and published sqlite APIs to do this sort of thing. Ideally, yes. But I've come across a few situations (such as the one I gave as an example) where being able to write to the sqlite_master tables would permit a solution that could be done totally in SQL, or which would save parsing schema. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
Hi John- There is a .lock file for each database. From my understanding, that should prohibit 2 connections from using the same database at the same time. However, that is not the situation I am wondering about. I am specifically wondering if database activity on a connection to DB 1 would have any effect on database activity on a different connection to DB2. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] random(*), randomblob(N), etc.
"Shane Harrelson" <[EMAIL PROTECTED]> wrote: > > Other than the normal caveats for using customized versions of the SQLite > code, does this sound like it would work? > Why not just write your own version of random() and register it using sqlite3_create_function()? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] random(*), randomblob(N), etc.
Thanks for the response. I'm not sure either will work since my random table can be created at many different points in my application. I looked at the source, and I think that if I expose the state structure ( prng) in randomByte (remove static and move it outside of the function), I can save off the state structure before I generate my table for the first time, and then use this saved state to "re-seed" randomByte when I need to recreate the random table. (Basically I allow the users to save off "views" of some static tables, and one view option allows the rows to presented in a random order. I only store the options used to create the view. If they save this view and the go back to it later, I'd like the random order to be the same). Other than the normal caveats for using customized versions of the SQLite code, does this sound like it would work? On 8/15/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > "Shane Harrelson" <[EMAIL PROTECTED]> wrote: > > I have a table that has the rows sorted by using a column which is > filled > > with values from random(*). > > > > Is there a PRAGMA or other API which allows me to set the "seed" used by > > random(*) such that > > I can reproduce the same random sequence each time? > > > > If you compile with -DSQLITE_TEST then the same seed is used > every time and the sequence will be reproducible. You can also > overload the RandomSeed() interface in the OS module. (The > techniques for doing that are going to change in version 3.5, > so anything you do there will not be portable moving forward.) > Otherwise, no, there is no good way to set the seed for the PRNG. > There is no pragma. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > >
Re: [sqlite] random(*), randomblob(N), etc.
"Shane Harrelson" <[EMAIL PROTECTED]> wrote: > I have a table that has the rows sorted by using a column which is filled > with values from random(*). > > Is there a PRAGMA or other API which allows me to set the "seed" used by > random(*) such that > I can reproduce the same random sequence each time? > If you compile with -DSQLITE_TEST then the same seed is used every time and the sequence will be reproducible. You can also overload the RandomSeed() interface in the OS module. (The techniques for doing that are going to change in version 3.5, so anything you do there will not be portable moving forward.) Otherwise, no, there is no good way to set the seed for the PRNG. There is no pragma. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] random(*), randomblob(N), etc.
I have a table that has the rows sorted by using a column which is filled with values from random(*). Is there a PRAGMA or other API which allows me to set the "seed" used by random(*) such that I can reproduce the same random sequence each time? Thanks. -Shane
Re: [sqlite] SQLITE_BUSY error in multi-threaded environment
My guess is that you will find your problem in the way file locking is implemented on your system. Is there a global file lock rather than locks associated with each file? A simple test program will resolve the issue. Mark Brown wrote: Hi Andre- After rereading your post, I wanted to confirm something. In your example below, are thread1 and thread2 connected to the same database, or different databases? In my scenario, the threads are connected to different databases, so I'm not sure if it is the same situation. Thanks, Mark -Original Message- From: Andre du Plessis [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 15, 2007 5:05 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment Being a newbie to SQLite I've had the same problems working with SQLite so maybe I can help, It does not matter how well your database is synchronized, a common pitfall I had was that I would have a query object with an open cursor which prevents any other statement from committing to the database. So for example: THREAD1 THREAD2 LOCK QUERY UNLOCK LOCK (Step through query)BEGIN TRANSACTION INSERTS COMMIT <- SQLite busy error here UNLOCK - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
Hi Andre- After rereading your post, I wanted to confirm something. In your example below, are thread1 and thread2 connected to the same database, or different databases? In my scenario, the threads are connected to different databases, so I'm not sure if it is the same situation. Thanks, Mark > -Original Message- > From: Andre du Plessis [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 15, 2007 5:05 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > > > Being a newbie to SQLite I've had the same problems working > with SQLite > so maybe I can help, > It does not matter how well your database is synchronized, a common > pitfall I had was that I would have a query object with an open cursor > which prevents any other statement from committing to the database. > > So for example: > THREAD1 THREAD2 > LOCK > QUERY > UNLOCK LOCK > (Step through query)BEGIN TRANSACTION > INSERTS > COMMIT <- SQLite busy error here > UNLOCK > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem loading extension in Delphi (FTS2)
--- Ralf Junker <[EMAIL PROTECTED]> wrote: > >Does it support external sqlite loadable extensions? > > Loadable extensions are currently omitted. FTS1 and FTS2 extensions are > provided as built-in > modules. User-aware collations sequences using the Windows sorting functions > are provided in > place of the ICU extension. Full functionality is therefore available. Your product is not useful to a few users like me who require custom sqlite functions for their databases. Of course such users are in the minority. But it's something to think about. Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV. http://tv.yahoo.com/ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Storing monetary values and calculations
Mikey C wrote: John Stanton wrote: We have added a DECIMAL type to Sqlite. This stores fixed point numbers in ASCII display format and performs accurate artithmetic and presents nicely from HTML. In which release was the DECIMAL affinity added to SQLite? Can you specify the precision and scale as per SQL-92 standard? e.g. DECIMAL(9,2) ? It was not added to Sqlite releases. We added the DECIMAL type using the regular DECIMAL(n,m) notation defining precision and scale. It takes advantage of the Sqlite declared type logic, but needed a slight change to stop Sqlite treating these columns as numeric and flipping them to floating point storage. We did not integrate the decimal arithmetic into the regular SQL arithmetic, instead defining seperate decimal functions and aggregate functions. We were using Sqlite for a commercial system and needed an appropriate fixed point number format and accurate arithmetic for money values. John Stanton wrote: If you store money as an integer with an "implied decimal point" (a familiar method for old-time COBOL programmers) it will work. We have added a DECIMAL type to Sqlite. This stores fixed point numbers in ASCII display format and performs accurate artithmetic and presents nicely from HTML. Floating point numbers for money is a perennial trap for young players. Mikey C wrote: Hi there, Currently I am using a SQLite 3.x database that stores and calculates currency values using the column type NUMERIC (which I believe has a FLOAT affinity). However this leads to errors in storing values values in floating point representation. I guess there is no planned support for direct fixed point types, so what is the best approach? Store the monetary values in an INTEGER column and multiply all values up by 100 to store in pence/cents? Any advice? Thanks, Mike - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
Hi Andre- Thank you for your insight. Looks like we have some redesign scheduled for today. :) Thanks, Mark > -Original Message- > From: Andre du Plessis [mailto:[EMAIL PROTECTED] > Sent: Wednesday, August 15, 2007 5:05 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] SQLITE_BUSY error in multi-threaded environment > > > Being a newbie to SQLite I've had the same problems working > with SQLite > so maybe I can help, - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PROBLEMS BUILDING 3.4.2 using Ms Visual Studio 2005
On 8/15/07, [EMAIL PROTECTED] <[EMAIL PROTECTED] > wrote: > > > > See http://www.sqlite.org/cvstrac/tktview?tn=2574 > > > > Apparently VC++ does not like for you to declare a constant > > with file scope before the constant is defined. I do not > > know how to work around this problem. Perhaps someone who > > better understands the quirks of VC++ can help. > > VC doesn't like static array with unknown size (it's fine with const). I > think C standart doesn't like it either. > Both C0x and C++0x seem to allow static (file scope) unknown array size forward declarations. gcc appears to anticipate this.
Re: [sqlite] PRAGMA writable_schema=ON;
T&B <[EMAIL PROTECTED]> wrote: > >> Now that is interesting. I didn't realize we could change > >> sqlite_master directly, but have often thought it could be very > >> handy. > > > > Warning: If you mess up, your database becomes unreadable and > > unrepairable. This is a very dangerous feature. If you use it and > > you lose data: no tears. > > Let me clarify. What I'm asking is whether editing the view and > trigger entries in sqlite_master is safer than editing table and > index entries (see below). > No. Any mistake, however slight, in either triggers or views, will probably leave the database unreadable and unrepairable. SQLite is unforgiving of errors in the sqlite_master table. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PROBLEMS BUILDING 3.4.2 using Ms Visual Studio 2005
> See http://www.sqlite.org/cvstrac/tktview?tn=2574 > > Apparently VC++ does not like for you to declare a constant > with file scope before the constant is defined. I do not > know how to work around this problem. Perhaps someone who > better understands the quirks of VC++ can help. VC doesn't like static array with unknown size (it's fine with const). I think C standart doesn't like it either. -- Prowadzisz firmê? Zobacz jak mozemy Ci pomoc. >>>http://link.interia.pl/f1b50 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA writable_schema=ON;
Now that is interesting. I didn't realize we could change sqlite_master directly, but have often thought it could be very handy. Warning: If you mess up, your database becomes unreadable and unrepairable. This is a very dangerous feature. If you use it and you lose data: no tears. Let me clarify. What I'm asking is whether editing the view and trigger entries in sqlite_master is safer than editing table and index entries (see below). I'd greatly appreciate any educated insight, not necessarily a guarantee. :-) Thanks, Tom From: T&B <[EMAIL PROTECTED]> Date: 15 August 2007 11:50:53 AM To: sqlite-users@sqlite.org Subject: [sqlite] PRAGMA writable_schema=ON; [EMAIL PROTECTED] wrote: It appears that you can set PRAGMA writable_schema=ON; Then do a manual UPDATE of the sqlite_master table to insert I tried it and it seems to work. But it is dangerous. If you mess up, you corrupt the database file. Now that is interesting. I didn't realize we could change sqlite_master directly, but have often thought it could be very handy. I've often read from it (ie select from sqlite_master), but not written (ie update or insert). I imagine that writing to a table or index entry would be disastrous, eg: update sqlite_master set sql = 'create table MyTable( Col1, Col2 ) where name = 'MyTable' and type = 'table' ; since I think SQLite wouldn't set up the required table data. Correct? But what about triggers and views? Since (AFAIK, since rootpage = 0) there's no data structure created by them in the file, can we safely manipulate them directly in sqlite_master? For an example, I'll use the predicament from my earlier message "Altering views, keeping triggers". As a possible solution to keeping triggers when a view is changed, would it be safe to either: 1. Update the view in sqlite_master directly, thereby avoiding SQLite's deletion of associated triggers: update sqlite_master set sql = 'create view MyView as select Col1, Col2 from MyTable' where name = 'MyView' and type = 'view' ; or: 2. Cache the triggers before changing the view, then insert them directly into sqlite_master: begin immediate ; create temporary table Cache as select * from sqlite_master where type = 'trigger' ; drop view 'MyView' ; insert into sqlite_master select * from Cache where name not in ( select name from sqlite_master where type = 'trigger' ) ; commit; I guess "insert or ignore" could be used instead of testing for existence, if sqlite_master enforces a unique( type, name), but I don't know if this is safe to assume. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PROBLEMS BUILDING 3.4.2 using Ms Visual Studio 2005
Would it help if you didn't have the "const" in the declaration? (I haven't tried - just an idea...) Dennis [EMAIL PROTECTED] wrote: "Cariotoglou Mike" <[EMAIL PROTECTED]> wrote: I am having problems building 3.4.2 from the amalgamated source,using Microsoft Visual Studio 2005 (this is the first time I am trying to use the amalgamated source). I get the following errors: Error 1 error C2133: 'sqlite3UpperToLower' : unknown size Error 37 error C2133: 'sqlite3OpcodeNames' : unknown size Error 184 error C2133: 'sqlite3IsIdChar' : unknown size strangely, I can build fine When using the separate source files. I do not know enough C/C++ to understand why the error occurs. I suspect it has to do with order of declarations in the source file. Note that all three errors have to do with internally declared arrays. in the separate source, they are declared as "extern", but in the amalgamated source they become "static". can somebody help please ? See http://www.sqlite.org/cvstrac/tktview?tn=2574 Apparently VC++ does not like for you to declare a constant with file scope before the constant is defined. I do not know how to work around this problem. Perhaps someone who better understands the quirks of VC++ can help. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Static linking under Kylix Delphi
I'm trying to develop a cross platform application (Wn32 / Linux). I use
Borland (Codegear) developer tools - Borland Delphi & Kylix.
I have already developed SQLite components with option to use SQLite
dynamically (DLL) or statically (link OBJs to app). I would prefer static
linking in my application. Both versions (static dynamic) work well in
borland delphi (win32). Also dynamic version works well with Kylix. But I
cannot compile static version in Kylix.
On Windows I use copliler bcc32.exe that comes with Borland C Builder to
build sqlite3.obj from sqlite amalgamation source. Then I use another tool -
tlib.exe - to extract further required obj files from cw32.lib.
On Linux I use gcc to compile SQLite amalgamation to get sqlite3.o. To link
it to app i use:
{$LINK sqlite3.o}
But when trying to compile, i'm getting the error:
Illegal reference to symbol 'Close' in object file '/.../sqlite3.o'
I am no linux guru. I don't know where can I get some further object files
if required (similar to CW32 under Win). Or am I missing something else?
--
View this message in context:
http://www.nabble.com/Static-linking-under-Kylix-Delphi-tf4272555.html#a12160368
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] PROBLEMS BUILDING 3.4.2 using Ms Visual Studio 2005
"Cariotoglou Mike" <[EMAIL PROTECTED]> wrote: > I am having problems building 3.4.2 from the amalgamated source,using > Microsoft Visual Studio 2005 > > (this is the first time I am trying to use > the amalgamated source). I get the following errors: > > Error 1 error C2133: 'sqlite3UpperToLower' : unknown size > Error 37 error C2133: 'sqlite3OpcodeNames' : unknown size > Error 184 error C2133: 'sqlite3IsIdChar' : unknown size > > strangely, I can build fine When using the separate source files. I do > not know enough C/C++ to understand > why the error occurs. I suspect it has to do with order of declarations > in the source file. > > > Note that all three errors have to do with internally declared arrays. > in the separate source, they are declared > as "extern", but in the amalgamated source they become "static". > > can somebody help please ? > See http://www.sqlite.org/cvstrac/tktview?tn=2574 Apparently VC++ does not like for you to declare a constant with file scope before the constant is defined. I do not know how to work around this problem. Perhaps someone who better understands the quirks of VC++ can help. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] PROBLEMS BUILDING 3.4.2 using Ms Visual Studio 2005
I am having problems building 3.4.2 from the amalgamated source,using Microsoft Visual Studio 2005 (this is the first time I am trying to use the amalgamated source). I get the following errors: Error 1 error C2133: 'sqlite3UpperToLower' : unknown size Error 37 error C2133: 'sqlite3OpcodeNames' : unknown size Error 184 error C2133: 'sqlite3IsIdChar' : unknown size strangely, I can build fine When using the separate source files. I do not know enough C/C++ to understand why the error occurs. I suspect it has to do with order of declarations in the source file. Note that all three errors have to do with internally declared arrays. in the separate source, they are declared as "extern", but in the amalgamated source they become "static". can somebody help please ? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] PRAGMA writable_schema=ON;
T&B <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > > It appears that you can set > > > > PRAGMA writable_schema=ON; > > > > Then do a manual UPDATE of the sqlite_master table to insert > > > I tried it and it seems to work. But it is dangerous. If you mess > > up, you corrupt the database file. > > Now that is interesting. I didn't realize we could change > sqlite_master directly, but have often thought it could be very handy. Warning: If you mess up, your database becomes unreadable and unrepairable. This is a very dangerous feature. If you use it and you lose data: no tears. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE_BUSY error in multi-threaded environment
Being a newbie to SQLite I've had the same problems working with SQLite so maybe I can help, It does not matter how well your database is synchronized, a common pitfall I had was that I would have a query object with an open cursor which prevents any other statement from committing to the database. So for example: THREAD1 THREAD2 LOCK QUERY UNLOCK LOCK (Step through query)BEGIN TRANSACTION INSERTS COMMIT <- SQLite busy error here UNLOCK As you can see here that even thought there are Global critical sections or Mutexes that completely locks on a global level without any other interferences (external connections) The query is busy stepping and has an open cursor, so commit or (spillover) of inserts will fail. In situations where this can be expected, I fetch all data into memory inside the lock and reset the query (sqlite3_reset) releases cursor lock. Then step through data in memory. The other solution you may hear is to use BEGIN IMMEDIATE before performing an operation, this will give any thread an immediate error when trying to begin the same transaction level, however I think that if you have separate database connections then they might not know this until they try to get an exclusive lock on the file for committing. Solution: THREAD1 THREAD2 LOCK QUERY (Read rows into memory) SQLite3_reset UNLOCK LOCK BEGIN TRANSACTION INSERTS COMMIT (no error) UNLOCK Hope this helps my implementation is running smoothly but it's not as concurrent as I would like it to be, but because SQLite is so fast, you can lock globally get in and out as soon as you can, and you should still be happy with the speed. -Original Message- From: Mark Brown [mailto:[EMAIL PROTECTED] Sent: 14 August 2007 10:25 PM To: sqlite-users@sqlite.org Subject: [sqlite] SQLITE_BUSY error in multi-threaded environment Hi- I've got an application that has many different SQLite databases. Each database connection is opened in its own thread. Each database has only one connection. I created some test cases that create a database and schema on the fly and perform various SELECT, INSERTS, UPDATES on it. The tests execute while the rest of the system is running normally. What I am seeing is that while I only have one database connection to my test case database, and my operations on this database are done sequentially, I have seen at random times a return of SQLITE_BUSY on either a prepare or execute of a statement. On a guess, I decided to stop all other database activity going on in the system (db activity on different threads on different databases), and so far, my test cases pass just fine. What I was wondering is if there is any chance that database activity into SQLite from other db connections could somehow influence my db activity on my test database in returning a SQLITE_BUSY error. I'm using SQLite 3.4.1 with the dotlock mechanism for thread protection on a vxWorks custom hardware configuration. With other problems I have had, they turned out to be some file i/o method failing due to our custom h/w, so most likely this is the problem, but just thought I would ask. Thanks, Mark - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Need help linking into Delphi Application
John Elrick-2 wrote:
>
> I've been using the Delphi ASGSqlite components with static linking for
> some time with version 3.3.13. I'd like to move on up to 3.4.0,
> however, no one seems to have documented how to do this yet.
>
> I tried compiling the Amalgamation with Borland C++ 5.0 and it generates
> the obj file nicely. However, when I attempt to link the obj into my
> application, I am getting an "unsatisfied forward declaration __streams".
>
> I'm a Delphi programmer and it is more than frustrating attempting to
> figure out what libraries are missing and how to even find them in the
> wide, wonderful world. I programmed in C back in the '80's, so my skill
> set there is beyond rusty.
>
> Can someone point me to resources so I can learn enough to solve these
> types of issues on my own in the future? I've tried Google and it
> hasn't given me anything of value, but I could be asking the wrong
> questions.
>
>
> John Elrick
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
I have my own static linking script. It is based upon Aducom experience:
make_obj.bat
@ECHO OFF
SET COMPILER_PATH=C:\Progra~1\Borland\CBuilder5\Bin\
REM compile SQLite3 amalgamation:
"%COMPILER_PATH%bcc32.exe" -c -6 -pc -O -w- -RT- -DNO_TCL
-DSQLITE_ENABLE_COLUMN_METADATA sqlite3.c
REM extract more OBJs from CW32.LIB (to current directory):
"%COMPILER_PATH%tlib.exe" %COMPILER_PATH%..\Lib\cw32.lib *files *is
*mbsnbicm *ftol *memset *gmtime *ltolower *strcmp *strncmp *strlen *sprintf
*_ll *memcpy *strcpy *memcmp *atol *hrdir_mf *hrdir_r *strncpy *_ftoul
*ltoupper *initcvt *streams *isctype *tzdata *timefunc *clower *clocale
*vprinter *mbisspc *hrdir_s *cupper *realcvt *scantod *realcvtw *scanwtod
*files2 *allocbuf *fflush *fputn *bigctype *timedata *mbctype *int64toa
*cvtentry *mbyte1 *hrdir_b *realloc *cvtfak *getinfo *xcvt *hugeval *qmul10
*fuildq *_pow10 *ldtrunc *cvtfakw *xcvtw *wcscpy *wis *exit *xfflush
*flushout *lputc *__write *_tzset *tzset *mbisdgt *mbsnbcpy *errormsg
*wcslen *virtmem *heap *memmove *fxam *fuistq *qdiv10 *errno *ctrl87
*wmemset *iswctype *_cfinfo *handles *perror *fputs *patexit *initexit
*__lseek *_write *ioerror *setenvp *calloc *globals *mbsrchr *ermsghlp
*platform *getenv *mbisalp *deflt87 *_cfinfo *__isatty *mbsrchr
in my app, i have unit SQLiteStatic.pas:
unit SQLiteStatic;
interface
// Turn off hints caused by static linking objects.
{$HINTS OFF}
uses
{$IFDEF MSWINDOWS}
Windows,
{$ENDIF} // MSWINDOWS
SQLiteClasses;
//
*
// *** Linked objects
**
//
*
{$LINK 'OBJ/sqlite3.obj'}
{$LINK 'OBJ/_ftoul.obj'}
{$LINK 'OBJ/files.obj'}
{$LINK 'OBJ/strlen.obj'}
{.$LINK 'OBJ/assert.obj'}
{$LINK 'OBJ/memcmp.obj'}
{$LINK 'OBJ/memcpy.obj'}
{$LINK 'OBJ/memset.obj'}
{$LINK 'OBJ/strcmp.obj'}
{$LINK 'OBJ/strcpy.obj'}
{.$LINK 'OBJ/strcat.obj'}
{$LINK 'OBJ/strncmp.obj'}
{$LINK 'OBJ/strncpy.obj'}
{.$LINK 'OBJ/strncat.obj'}
{$LINK 'OBJ/sprintf.obj'}
{.$LINK 'OBJ/fprintf.obj'}
{$LINK 'OBJ/_ll.obj'}
{$LINK 'OBJ/ltoupper.obj'}
{$LINK 'OBJ/ltolower.obj'}
{$LINK 'OBJ/atol.obj'}
{$LINK 'OBJ/ftol.obj'}
{.$LINK 'OBJ/longtoa.obj'}
{$LINK 'OBJ/hrdir_r.obj'}
{$LINK 'OBJ/gmtime.obj'}
{$LINK 'OBJ/tzdata.obj'}
{$LINK 'OBJ/initcvt.obj'}
{$LINK 'OBJ/streams.obj'}
{$LINK 'OBJ/files.obj'} // DUPLICATE !
{$LINK 'OBJ/scantod.obj'}
{$LINK 'OBJ/scanwtod.obj'}
{$LINK 'OBJ/allocbuf.obj'}
{$LINK 'OBJ/bigctype.obj'}
{$LINK 'OBJ/clocale.obj'}
{$LINK 'OBJ/clower.obj'}
{$LINK 'OBJ/cupper.obj'}
{$LINK 'OBJ/fflush.obj'}
{$LINK 'OBJ/fputn.obj'}
{$LINK 'OBJ/hrdir_s.obj'}
{$LINK 'OBJ/mbisspc.obj'}
{$LINK 'OBJ/mbsrchr.obj'}
{$LINK 'OBJ/realcvt.obj'}
{$LINK 'OBJ/realcvtw.obj'}
{$LINK 'OBJ/timefunc.obj'}
{$LINK 'OBJ/vprinter.obj'}
{$LINK 'OBJ/hugeval.obj'}
{$LINK 'OBJ/cvtfak.obj'}
{$LINK 'OBJ/getinfo.obj'}
{$LINK 'OBJ/qmul10.obj'}
{$LINK 'OBJ/fuildq.obj'}
{$LINK 'OBJ/_pow10.obj'}
{$LINK 'OBJ/ldtrunc.obj'}
{$LINK 'OBJ/cvtfakw.obj'}
{$LINK 'OBJ/wis.obj'}
{$LINK 'OBJ/xfflush.obj'}
{$LINK 'OBJ/flushout.obj'}
{$LINK 'OBJ/lputc.obj'}
{$LINK 'OBJ/hrdir_b.obj'}
{$LINK 'OBJ/realloc.obj'}
{$LINK 'OBJ/mbctype.obj'}
{$LINK 'OBJ/xcvt.obj'}
{$LINK 'OBJ/xcvtw.obj'}
{$LINK 'OBJ/wcscpy.obj'}
{$LINK 'OBJ/errno.obj'}
{$LINK 'OBJ/ctrl87.obj'}
{$LINK 'OBJ/timedata.obj'}
{$LINK 'OBJ/int64toa.obj'}
{$LINK 'OBJ/cvtentry.obj'}
{$LINK 'OBJ/mbyte1.obj'}
{$LINK 'OBJ/errormsg.obj'}
{$LINK 'OBJ/exit.obj'}
{$LINK 'OBJ/iswctype.obj'}
{$LINK 'OBJ/heap.obj'}
{$LINK 'OBJ/memmove.obj'}
{$LINK 'OBJ/fxam.obj'}
{$LINK 'OBJ/fuistq.obj'}
{$LINK 'OBJ/qdiv10.obj'}
{$LINK 'OBJ/wmemset.obj'}
{$LINK 'OBJ/wcslen.obj'}
{$LINK 'OBJ/_tzset.obj'}
{$LINK 'OBJ/deflt87.obj'}
{.$LINK 'OBJ/mbschr.o
Re: [sqlite] [Delphi] Escaping quote?
I'm not an expert on Aducom SQLite components, but anyway i'll try to help.
Maybe you should consider using parameters in your query. Parameters in
SQLite start with ':', '@' or '?', however ASGSQlite supports only ':' in my
opinion. Your SQL query should look like this:
INSERT INTO Stuff (Title) VALUES (:Title);
To use params in ASGSQLite:
with TASQLite3query.Create(nil) do
try
Connection := ASQLite3DB1;
// Force parsing of SQL. You don't have to do this since this property is
False by default.
// It's only to emphasize that this property must be set to False.
RawSQL := False;
// Set command text (it automatically parses SQL into Params collection).
SQL.Text := 'INSERT INTO Stuff (Title) VALUES (:Title)';
// Set param values
Params.ParamByName('Title') := 'Let''s meet at the pub tonight!';
// execute SQL
ExecSQL;
finally
Free;
end;
Another way is to execute SQL directly with SQLite3_Execute() method of
TASQLite3DB. If so you have to create Params collection by your own.
Never compiled or tested the code above, use it at your own risk. I hope I
helped a bit.
Gilles Ganault wrote:
>
> Hello
>
> I'm having a problem saving strings into a colum from a Delphi
> application
> because they might contain the ( ' ) single quote character:
>
> =
> // Input := 'Let's meet at the pub tonight!';
> MyFormat := 'insert into stuff (title) values ('''%s')';
> SQL := Format(MyFormat, Input);
>
> try
> ASQLite3DB1.Database := db;
> ASQLite3DB1.DefaultDir := ExtractFileDir(Application.ExeName);
> ASQLite3DB1.Open;
>
> ASQLite3DB1.SQLite3_ExecSQL(SQL);
> ASQLite3DB1.Close;
> except
> ShowMessage('Bad');
> end;
> =
>
> Is there a function I should call either in SQLite or Delphi before
> running
> the SQL query?
>
> Thank you.
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
--
View this message in context:
http://www.nabble.com/-Delphi--Escaping-quote--tf3983235.html#a12158672
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Problem loading extension in Delphi (FTS2)
Hello Joe Wilson, >Does it support external sqlite loadable extensions? Loadable extensions are currently omitted. FTS1 and FTS2 extensions are provided as built-in modules. User-aware collations sequences using the Windows sorting functions are provided in place of the ICU extension. Full functionality is therefore available. >The register calling convention may be a problem there. It can remapped so cdecl extensions could be used. Ralf - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Unknown SQLITE_ERROR problem
Hi, Rarely (I have 4 debug logs from all our beta testers) executing 'COMMIT' statement returns SQLITE_ERROR + 'SQL logic error or missing database'. Analysis of the debug logs and source codes doesn't show any problem, there simply begins a transaction, some SQL statements are executed and COMMIT should finish it - but it doesn't. Unfortunatelly SQLITE_ERROR is quite a generic term that doesn't tell much. I tried to go through the places SQLite returns this message (I don't have any deeper understanding of SQLite sources) and one place that seems to be related to my problem is in sqlite3PagerCommitPhaseTwo(), namely: if( pPager->state
