Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)
On 11/30/2018 11:20 PM, Dominique Devienne wrote:
> On Fri, Nov 30, 2018 at 3:03 PM Dominique Devienne
> wrote:
>
>> On Fri, Nov 30, 2018 at 2:44 PM Richard Hipp wrote:
>>
>>> On 11/30/18, Simon Walter wrote:
>>>> Thanks Dominique,
>>>>
>>>> Much appreciated. I can now stop pulling out my hair. I will do
>>>> something with sprintf.
>>>
>>> See https://www.sqlite.org/carray.html
>>
>>
>> Right. Any table-valued function would do too.[...]
>>
> In fact, it's probably possible right now via the
>> JSON1 extension, if your text value is JSON-formatted.
>>
>
> Yep, works fine, as expected. So that's another possibility too.
> Assuming you can use the JSON1 extension and it's enabled in the SQLite
> DDL. --DD
>
> sqlite> .header on
> sqlite> create table t (c, n);
> sqlite> insert into t values (1, 'one'), (2, 'two'), (3, 'three');
> sqlite> select n from t where c in (select value from json_each('[1, 3]'));
> n
> one
> three
> sqlite> select n from t where c in (select value from json_each('[]'));
> sqlite> select n from t where c in (select value from json_each('[2]'));
> n
> two
> sqlite>
>
I suppose an array of ints or an array of pointers to \000 terminated
char arrays or any other kind of array could be escaped correctly
provided the caller give some kind of hint as to what the type of data is.
To be honest, I am using apr_dbd as I would like to support more than
just SQLite. So I will need to play around with MySQL and PostgreSQL at
least and maybe branch if SQLite is in use. I have no idea yet if MySQL
and/or PostgreSQL can handle this scenario and how they do it. Though
the possibility for SQL injections is nil, as the comma separated list
is generated by the application and not user input... famous last words?
I must check again.
Interesting tidbit:
...WHERE id IN ("1") actually works. As soon as there is a comma, SQLite
returns 0 rows.
I will keep hacking. Thanks for the advice! Much appreciated.
Simon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_bind_text() and WHERE x IN (?)
Thanks Dominique, Much appreciated. I can now stop pulling out my hair. I will do something with sprintf. Best regards, Simon On 11/30/2018 10:37 PM, Dominique Devienne wrote: > On Fri, Nov 30, 2018 at 2:10 PM Simon Walter wrote: > >> How does one use WHERE x IN (?) with a prepared statement? What is the >> correct way to do this? >> > > You cannot do it. Must use WHERE x IN (?, ?, ?), i.e. an explicit and > known in advance > number of bind placeholders. Or not use binding at all, and "paste" your > text value before > preparing the statements. --DD > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_bind_text() and WHERE x IN (?)
I am trying to make a prepared statement that has a parameter such as
"1, 893, 121212". obviously it is text (a string) but it cannot be
quoted or the result will be:
SELECT id, data FROM val WHERE id IN ("1, 893, 121212");
I understand normally I would need the quotes, such as:
SELECT id, name, val FROM obj WHERE name = ?
I am not sure if this is happening. From a few tests, it seems to be
what is going on.
How does one use WHERE x IN (?) with a prepared statement? What is the
correct way to do this?
Thanks for your time.
Best regards,
Simon
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Repost: Having problems with Entity Framework code first db creation
We have not done this in the past. We are trying to create a SQLite database to use as a project file for software we're developing. The test project I linked to in the initial email is one we were able to first make work with MS SQL Compact, but it has limitations that we want to avoid. The test project steps are: 1) Create a new console app in VS2013 2) Manage NuGet packages and add the SQLite packages (which also install Entity Framework) 3) Copy in the schema class and main function (found in the link) 4) Run the program Walter Williams Senior Software Engineer Sawtooth Software, Inc. "Do, or do not. There is no try." -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Monday, February 09, 2015 4:01 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Repost: Having problems with Entity Framework code first db creation Walter Williams wrote: > > Nothing changed after installing it. > What steps in the IDE are you taking to get to that point? Have these steps worked in the past? I'm asking these questions because I'm not completely sure that the System.Data.SQLite provider supports the various "code first" scenarios with the Entity Framework. Some errors we have seen in the past are: https://system.data.sqlite.org/index.html/tktview/f37ca75 and https://system.data.sqlite.org/index.html/tktview/6b5ef4f -- Joe Mistachkin ___ 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] Repost: Having problems with Entity Framework code first db creation
Nothing changed after installing it. Walter Williams Senior Software Engineer Sawtooth Software, Inc. "Do, or do not. There is no try." -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Monday, February 09, 2015 3:17 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Repost: Having problems with Entity Framework code first db creation Walter Williams wrote: > > I am still getting the same error. > Do you have the following package installed? https://www.microsoft.com/en-us/download/details.aspx?id=40762 Whether or not the above is required for Visual Studio 2013 is unclear; however, I don't think it can hurt to try it. -- Joe Mistachkin ___ 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] Repost: Having problems with Entity Framework code first db creation
I am still getting the same error. Walter Williams Senior Software Engineer Sawtooth Software, Inc. "Do, or do not. There is no try." -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Friday, February 06, 2015 5:56 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Repost: Having problems with Entity Framework code first db creation I think I've figured out a way to solve the "config file issue". I've rebuilt all the pre-release packages, including the NuGet packages. Could you please try your scenario again? -- Joe Mistachkin ___ 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] Repost: Having problems with Entity Framework code first db creation
I tried it again with the 1.0.95 NuGet packages (even with a new project), and I get the exact same error. You said EF looks like it is trying to use SQL Server. I noticed in the configuration of App.config after the packages are installed, the defaultConnectionFactory refers to System.Data.Entity.Infrastructure.SqlConnectionFactory, and there is a provider System.Data.Entity.SqlServer.SqlProviderServices in addition to the SQLite provider. I am not experienced in this area. Does the default connection factory perhaps need to change? Walter Williams Senior Software Engineer Sawtooth Software, Inc. "Do, or do not. There is no try." -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Wednesday, February 04, 2015 9:22 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Repost: Having problems with Entity Framework code first db creation Walter Williams wrote: > > I received the same result. > Is that the same error message you posted before (i.e. "Unable to complete operation. The supplied SqlConnection does not specify an initial catalog or AttachDBFileName.")? That particular error message leads me to believe that the Entity Framework is trying to use the ADO.NET provider for SQL Server instead of SQLite. Also, could you try updating to the latest NuGet packages as well? They have not been pushed and are only [now] available for download from the site. -- Joe Mistachkin ___ 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] Repost: Having problems with Entity Framework code first db creation
I received the same result. Walter Williams Senior Software Engineer Sawtooth Software, Inc. "Do, or do not. There is no try." -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Joe Mistachkin Sent: Wednesday, February 04, 2015 12:07 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Repost: Having problems with Entity Framework code first db creation Walter Williams wrote: > > Is this a bug in the package or am I missing something? > It may be a "bug" in the System.Data.SQLite 1.0.94.0 packaging. Several people have reported issues; however, it seems to depend on the number of updates installed for Visual Studio 2012 / 2013. Could you please try your scenario again using a 1.0.95.0 pre-release package? https://system.data.sqlite.org/index.html/doc/preRelease/www/downloads.wiki -- Joe Mistachkin ___ 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] Repost: Having problems with Entity Framework code first db creation
I'm trying to use a code first model using the System.Data.SQlite NuGet (v1.0.94.1) package and Entity Framework. I'm using VS 2013. I have defined my objects, but when I try to create a new database file using them, I get an error "Unable to complete operation. The supplied SqlConnection does not specify an initial catalog or AttachDBFileName." I created a simple project which demonstrates the error. It can be downloaded from http://www.sawtoothsoftware.com/download/temp/SQLiteTest.zip. I was able to use the same code with SQL Server Compact 4.0 but for preference I'd like to use SQLite. Is this a bug in the package or am I missing something? ==== Walter Williams Senior Software Engineer Sawtooth Software, Inc. "Do, or do not. There is no try." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Having problems with Entity Framework code first db creation
I have uploaded a project to http://www.sawtoothsoftware.com/download/temp/SQLiteTest.zip. It contains the VS 2013 example. Walter Williams Senior Software Engineer Sawtooth Software, Inc. "Do, or do not. There is no try." -Original Message- Thank you for the chuckle. As to the actual question, when you say "when I try to create a new database file", do you mean that you are using the open command (or whatever in the wrapper tries to call the sqlite3_open* commands) with a valid filename (which need not exist, but must be a valid name) and then it fails with the mentioned message? The list unfortunately (or fortunately) does not permit attachments, could you use a file upload/sharing service kindly so we can access the file and (hopefully) better understand the question? - thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Having problems with Entity Framework code first db creation
I'm trying to use a code first model using the System.Data.SQlite NuGet (v1.0.94.1) package and Entity Framework. I'm using VS 2013. I have defined my objects, but when I try to create a new database file using them, I get an error "Unable to complete operation. The supplied SqlConnection does not specify an initial catalog or AttachDBFileName." The attached project has a sample of the code which demonstrates the error. For size the packages folder is not included. I was able to use the same code with SQL Server Compact 4.0 but for preference I'd like to use SQLite. Is this a bug in the package or am I missing something? ==== Walter Williams Senior Software Engineer Sawtooth Software, Inc. "Do, or do not. There is no try." ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Foreign Key errors
On Thu, 08 May 2014 13:15:54 +0300, Constantine Yannakopoulos wrote:
most if not all other databases do not implement this
$ psql
psql (9.3.4)
Type "help" for help.
misc=# create table parent
misc-# (a_id varchar(1),
misc(# constraint pk_parent primary key(a_id));
CREATE TABLE
misc=# create table child
misc-# (b_id varchar(1),
misc(# a_id varchar(1),
misc(# constraint fk_child_parent foreign key
misc(# (a_id) references parent);
CREATE TABLE
misc=# insert into parent values ('a');
INSERT 0 1
misc=# insert into child values ('1','a');
INSERT 0 1
misc=# insert into child values ('2','a');
INSERT 0 1
misc=# insert into child values ('1','b');
ERROR: insert or update on table "child" violates foreign key constraint
"fk_child_parent"
DETAIL: Key (a_id)=(b) is not present in table "parent".
misc=#
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] RPAD/LPAD
Richard Hipp wrote:
> On Thu, Mar 6, 2014 at 3:41 PM, Walter Hurry wrote:
>
>> A small enhancement request:
>>
>> It would be great if the RPAD and LPAD functions could be implemented in
>> sqlite.
>>
>
> The SQLite you can get the effect of RPAD(x,y) using PRINTF('%-*s',y,x).
> See http://www.sqlite.org/lang_corefunc.html#printf for details.
Thanks, but you snipped the relevant part of my post:
"I know I can easily achieve the equivalent ... but if the functions were
available natively it would avoid the need to hack third party SQL scripts."
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] RPAD/LPAD
A small enhancement request: It would be great if the RPAD and LPAD functions could be implemented in sqlite. I know I can easily achieve the equivalent by concatenating and TRUNCing, but if the functions were available natively it would avoid the need to hack third party SQL scripts. Dr. Hipp? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] EXPLAIN QUERY PLAN
On Tue, 12 Nov 2013 17:47:05 -0500, jose isaias cabrera wrote: > I am trying to speed up our queries and normalize our DB and I am > reading, > > http://www.sqlite.org/eqp.html > > But, I am missing a lot. Where do I read about the results and how to > make changes to the DB to speed up/enhance the DB response? Thanks. > Normalize first. Then work out what queries you need, and add appropriate indexes. How fast do you need it to be? If it's still not fast enough, consider denormalizing selectively, and what the overhead will be in maintaining redundant data. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Large Database Windows vs Linux
On Fri, 28 Jun 2013 15:22:57 -0600, Keith Medcalf wrote: > That would explain why the best thing to be done with System Destroyer > (System Restore) is the same as the best way to handle the Hardware > Destroyer (Power Management) in Windows. Disable it completely. > The best thing to do with Windows is format the drive and install Unix or FreeBSD or Linux. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] possible ordering issue since 3.7.15
On Sun, 14 Apr 2013 01:22:46 +0900, kenichi ishigaki wrote:
> Hi.
>
> I received a report that the result of the following SQL has changed
> since 3.7.15.
> I haven't looked into the sqlite source yet, but can we call this a bug?
>
> Regards,
>
> Kenichi Ishigaki
>
> -
> create table cd (id integer primary key, title unique, year);
> insert into cd (title, year) values ('foo', 2000);
> insert into cd (title, year) values ('bar', 2001);
> select * from cd;
> select title from cd;
If you don't specify "order by", *any* relational db will return the
results in any order it chooses.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need JDBC driver for SQLite
On Thu, 04 Apr 2013 11:30:51 +0530, Vinoth raj wrote: > Hello All, > > I was looking for an authoritative source from where I can get the > driver (jar) for SQLite. SQLite web site do not have any mention for > Java support. > > Can anyone help in getting the JDBC driver for SQLite (from trusted > source only)? > I don't know what you mean by "authoritative source", but I use this one with success: https://bitbucket.org/xerial/sqlite-jdbc It works perfectly for me with SQLWotkbench/J ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Wanted - simple DATA editor for sqlite tables
On Sat, 23 Mar 2013 15:53:14 +, cl-RxdKpHOThMg wrote: > I'm looking for a straightforward way to edit the *data* in sqlite > tables, or at least a simple GUI for creating forms to edit sqlite > tables. > > I don't need *any* database management and I don't want it in this > application either because I want to be able simply to issue a command > like:- > > app > > which will pop up a window with the editor ready to go, preferably in a > grid format so I can see the existing data. > > The tables to be edited only have a few columns so the app can show all > the columns with no problem. > > > What I'm after is simplicity when using the editor, no complex series of > menus to navigate to reach the point where one is editing data, no reams > of icons etc. in the GUI, just a line of boxes to enter the data. > > > I'm quite happy to design it myself given a few tools, even a non-GUI > approach would be acceptable if it's reasonably simple. > > What I have tried so far (with some comments) are:- > > kexi - it's almost there but you can't create a data entry form with > a datagrid on it and you can't get it to edit sqlite3 tables created > outside of kexi. > > sqledit/sqlkit - it looks almost ideal but it seems to me that it's > just a bit too buggy. I've tried both plain sqledit (ready made > app) and creating my own using sqlkit. > > wxglade - a python GUI toolkit, this has been closest to success so > far and I may eventually get to a good solution but it feels as if > I'm having to reinvent the wheel in programming lots of things that > someone must have done before. > > > Surely there must be something close to what I want out there! (By the > way while I'm basically after a desktop application a web one would be > acceptable as I run a full LAMP setup on my desktop machinee). Try SQLWorkbench/J. Use the JDBC driver from: https://bitbucket.org/xerial/sqlite-jdbc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite-users Digest, Vol 61, Issue 7
On 1/6/2013 7:10 PM, Walter wrote: sqlite3_prepare16_v2 (vMdb, ws.c_str (), ws.size (), &stmt, &tail); The third parameter of sqlite3_prepare16_v2 is the length of the string *in bytes*, not in characters. You are effectively passing only half the statement. -- Igor Tandetnik Thank you Igor and Yuriy Since I only use the English language I am going to stick ..prepare_v2 and friends have found a simple way to convert to and from the GUI. Hopefully it won't trip me up down the track ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with sqlite3prepare16_v2
Hi All If I do this std::string ss= "PRAGMA main.user_version"; sqlite3_stmt* stmt; const char *tail; sqlite3_prepare_v2 (vMdb, ss.c_str (), ss.size (), &stmt, &tail); alls well step and retrieve user version However when I do this std::wstring ws= L"PRAGMA main.user_version"; sqlite3_stmt* stmt; const void*tail; sqlite3_prepare16_v2 (vMdb, ws.c_str (), ws.size (), &stmt, &tail); syntax error near '.' Tried this std::string ss= "SELECT data FROM tble"; prepare_v2 Alls well. step and retrieve data Change that to wstring prepare16_v2 no such column 'dat' Was using 3.6.20 Down loaded 3.7.15.1 Same problem What to do Walter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] PRAGMA user_version
On 9/2/2011 11:02 PM, Walter wrote: > Is there any way to get the user_version from an Attached database PRAGMA attachedName.user_version; -- Igor Tandetnik Thank you Igor I had the database name but did not think of the the dot in between. Perhaps some one could update the documentation to show this Cheers everyone Walter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] PRAGMA user_version
Hi All Is there any way to get the user_version from an Attached database PRAGMA user_version only get it from the Opened database. If not perhaps a request for it. Thank you Walter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [OS/2] Update OS/2 support in sqlite 3.7.x
Just in case this has slipped thru cause it took a while for me to register, here is my reply again. On 10/09/29 23:41, Walter Meinl wrote: >> >> If the underlying VFS does not support shared-memory (which the OS/2 VFS >> does not) then SQLite simply will not go into WAL mode. No patching is >> needed for this. Everything should work as delivered. >> >> What exactly is malfunctioning? What is the problem that this patch >> attempts to fix? > The mozilla bug was originally filed against 3.7.1. In pager.c was a > function: static int pagerPagecount in an #ifndef SQLITE_OMIT_WAL block. > The last 3 hunks of the original patch moved the exclusion of omit_wal > further down, because all sqlite databases in a new profile had zero > size on OS/2 and a warning was issued from firefox that history and > bookmarks won't work, because the data bases were in use by another program. >>> https://bugzilla.mozilla.org/attachment.cgi?id=474575 > However, this issue has been fixed already in 3.7.2 [d1ed743b6e]. > (Hopefully mozilla will update to 3.7.2 soon). > > The rest of the patch contained (in fossil format) in >>>> https://bugzilla.mozilla.org/attachment.cgi?id=477692 > is intended to explicitly disable WAL on OS/2 > and bring the OS/2 related files up to date. > That involves: > - adding a new function, os2CurrentTimeInt64(), and refining the > existing os2CurrentTime(); > - adding new members to the "os2Vfs" structure, and explicitly > defining them as NULL pointers since they support WAL; > - updating the OS/2 semaphore documentation to match other platforms. > It would be nice if these changes could be considered to get checked-in. > Thanks, Walter > > ___ > 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] [OS/2] Update OS/2 support in sqlite 3.7.x
> > If the underlying VFS does not support shared-memory (which the OS/2 VFS > does not) then SQLite simply will not go into WAL mode. No patching is > needed for this. Everything should work as delivered. > > What exactly is malfunctioning? What is the problem that this patch > attempts to fix? The mozilla bug was originally filed against 3.7.1. In pager.c was a function: static int pagerPagecount in an #ifndef SQLITE_OMIT_WAL block. The last 3 hunks of the original patch moved the exclusion of omit_wal further down, because all sqlite databases in a new profile had zero size on OS/2 and a warning was issued from firefox that history and bookmarks won't work, because the data bases were in use by another program. >> https://bugzilla.mozilla.org/attachment.cgi?id=474575 However, this issue has been fixed already in 3.7.2 [d1ed743b6e]. (Hopefully mozilla will update to 3.7.2 soon). The rest of the patch contained (in fossil format) in >>> https://bugzilla.mozilla.org/attachment.cgi?id=477692 is intended to explicitly disable WAL on OS/2 and bring the OS/2 related files up to date. That involves: - adding a new function, os2CurrentTimeInt64(), and refining the existing os2CurrentTime(); - adding new members to the "os2Vfs" structure, and explicitly defining them as NULL pointers since they support WAL; - updating the OS/2 semaphore documentation to match other platforms. It would be nice if these changes could be considered to get checked-in. Thanks, Walter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [OS/2] Update OS/2 support in sqlite 3.7.x
This defect report is cloned from mozilla bug595599 RW: "This patch disables WAL journalling on OS/2 since that feature requires memory-mapped file i/o which OS/2 doesn't support, and makes other small changes where needed." The patch was originally created by Rich Walsh against the amalgamation file in the mozilla-tree (SQlite v 3.7.1) I've installed fossil and broke up the patch to apply against current trunk files (src/os.h, src/os_os2.c and src/mutex_os2.c) The patch can be downloaded from this link. https://bugzilla.mozilla.org/attachment.cgi?id=477692 The original patch: https://bugzilla.mozilla.org/attachment.cgi?id=474575 Additional question for future reports: Is it possible to add attachments to the mailing list? Thanks, Walter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Import feature requests
On Sun, Dec 13, 2009 at 10:31:20PM -0800, Roger Binns wrote > Hint: If you feel the need to get aggressive and abusive when > posting then you probably missed something! If SQLite was useless, > someone else would have noticed by now. I apologize for coming across that way; I din't intend to. Let's just say I'm rather frustrated with some defaults. If I felt SQLite was useless I wouldn't be using it. I read about the "loose typing" during import, in the docs. It was only via "the hard way" that I found out just *HOW* loose. -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Import feature requests
The following might be options (compile time, config file, set manually; I don't care), but they should be available... 1) import with strict typing. If I create a table with 3 numeric (real. integer, whatever) fields, then a CSV file containing... 2.345, 42, 27.7 should import as 3 numbers, not as 3 character strings 2) import adjacent commas in a CSV file as NULL, not as a zero-length string. As for the argument that SQLite can't read my mind... a) if it can't read my mind, I need to be able to tell it what I want b) why would I want a "zero-length string" to behave any differently from NULL? c) why on earth would I want a "zero-length string" in an *INTEGER* or *REAL* field??? That is a totally insane default. -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.21
On Tue, Dec 08, 2009 at 02:41:23AM +0100, Andreas Schwab wrote > It's still crashing due to undefined behaviour. > > $ ./sqlite3 :memory: 'create table test(integer)' > Segmentation fault Possibly a stupid question from a relative newbie; shouldn't that be... sqlite3 :memory: 'create table test(fieldname integer)' -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Using pivot table
Walter wrote: > > I have the following tables > > > > CREATE TABLE data( > > id INTEGER PRIMARY KEY NOT NULL, > > name TEXT DEFAULT '', > > titleTEXT DEFAULT '', > > > > ) > > > > > > CREATE TABLE pivot( > > id INTEGER PRIMARY KEY NOT NULL, > > link1INTEGER DEFAULT 0, > > link2INTEGER DEFAULT 0, > > rank INTEGER DEFAULT 0, > > mdateMYDATE DEFAULT 0, > > status TEXTDEFAULT '', > > ) > > > > link1 and link2 are id's from tbl1 > > > > With this sql I get half way to what I want > > > > SELECT name,title,pivot.id AS id,mdate,status > > FROM data,pivot > > WHERE data.id=pivot.link1 > > ORDER BY name,pivot.rank > > > > How do I get the name,title from data onto the same row > > > > WHERE data.id=pivot.link1 and pivot.link2=a different data.id > > this obviously does not work > select d1.name, d1.title, d2.name. d2.title, pivot.id, mdate, status from pivot join data d1 on (pivot.link1 = d1.id) join data d2 on (pivot.link2 = d2.id) order by d1.name, pivot.rank; Many thanks Igor Apart from a '.' where a ',' should have been it's working a treat Cheers Walter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Using pivot table
I have the following tables CREATE TABLE data( id INTEGER PRIMARY KEY NOT NULL, name TEXT DEFAULT '', titleTEXT DEFAULT '', ) CREATE TABLE pivot( id INTEGER PRIMARY KEY NOT NULL, link1INTEGER DEFAULT 0, link2INTEGER DEFAULT 0, rank INTEGER DEFAULT 0, mdateMYDATE DEFAULT 0, status TEXTDEFAULT '', ) link1 and link2 are id's from tbl1 With this sql I get half way to what I want SELECT name,title,pivot.id AS id,mdate,status FROM data,pivot WHERE data.id=pivot.link1 ORDER BY name,pivot.rank How do I get the name,title from data onto the same row WHERE data.id=pivot.link1 and pivot.link2=a different data.id this obviously does not work My knowledge of SQL is limited Cheers Walter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [SOLVED] How do I properly import numbers from CSV?
On Sun, Dec 06, 2009 at 11:26:42PM -0500, Walter Dnes wrote > I still don't understand why 2009, *WITHOUT QUOTES* would be forced > to text, i.e. ' 2009', when imported into a field that is declared > as integer in the create statement. There are actually 2 solutions... 1) Use tab-delimited if possible (not really CSV) 2) Get rid of leading/trailing spaces if you're using comma-separated input. E.g. this row results in text fields being imported... 2231,615HMAK, 2005, 3, 28, 8.0, , -1.0, , 3.5 ...while this one results in mostly numeric fields being imported... 2231,615HMAK,2005,3,28,8.0, ,-1.0, ,3.5 As an added bonus, the script that got rid of unnecessary spaces also knocked down the 11,143,911,240 byte CSV file to "only" 5,382,671,854 bytes. This allowed me to import the file in 3 sub-2-gigabyte pieces versus 6 pieces that the original required. -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How do I properly import numbers from CSV?
On Sun, Dec 06, 2009 at 08:15:46PM -0600, Jay A. Kreibich wrote > On Sun, Dec 06, 2009 at 07:54:45PM -0500, Walter Dnes scratched on the wall: > > > The last few rows of the query output are... > > > > 2009| 9| 21 > > 2009| 9| 22 > > 2009| 9| 23 > > 2009| 9| 24 > > 2009| 9| 25 > > 2009| 9| 26 > > 2009| 9| 27 > > 2009| 9| 28 > > 2009| 9| 29 > > 2009| 9| 30 > > > > So I *KNOW* that there are rows with local_year = 2009. But... > > No, there are rows with a TEXT value of ' 2009'. Note the space. > Your other columns have leading whitespace as well. sqlite> select local_year, local_day, local_month from dly.dly04 where ((national_identifier = '615HMAK') and (local_year = ' 2009') and (local_month = ' 9') and (local_day = ' 30')); ...gives the result... 2009| 30| 9 So you're right. One correction to my previous post; I imported as comma-delimited, i.e CSV. I still don't understand why 2009, *WITHOUT QUOTES* would be forced to text, i.e. ' 2009', when imported into a field that is declared as integer in the create statement. -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Not matching numbers in where clause
As part of "pandemic planning" at work, I'm trying to duplicate some Oracle functionality at home. I'm running into a problem with the "WHERE" clause that totally baffles me. The problem surfaced in a TCL program, but I can duplicate it from the sqlite3 command prompt, so I'll use that in this email. Here's the situation. I've created a table called dly04. Here are the first few columns from the create statement. Note the 2 columns "national_identifier varchar2(7), local_year integer". sqlite> select * from sqlite_master where name = 'dly04'; table|dly04|dly04|1042384|CREATE TABLE dly04(i_stnid integer, national_identifier varchar2(7), local_year integer, local_month integer, local_day integer, etc, etc I then imported data from work as tab-delimited. I can query with "where" using a character field... sqlite> select local_year, local_month, local_day from dly04 where (national_identifier = '615HMAK'); The last few rows of the query output are... 2009| 9| 21 2009| 9| 22 2009| 9| 23 2009| 9| 24 2009| 9| 25 2009| 9| 26 2009| 9| 27 2009| 9| 28 2009| 9| 29 2009| 9| 30 So I *KNOW* that there are rows with local_year = 2009. But... sqlite> select local_year, local_month, local_day from dly04 where (local_year = 2009); ...doesn't find any rows at all. This should be a no-brainer that works in any SQL-compliant implementation. Neither do I get any rows with alternatives like... = '2009' or == 2009 or == '2009' I'm totally stumped. I wonder if I'm doing something "the Oracle way" that isn't 100% SQL-compliant. One thing I've done is to create an index like so... index|d04_ndx_00|dly04|14555880|CREATE INDEX d04_ndx_00 on dly04 (national_identifier, local_year, local_month) Is that allowed, or would it screw things up? -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [solved] SQLite won't import 53, 066, 244 row 11 gigabyte CSV file
Thanks for the help. I eventually got it working. And I'm working on the next stage of my project. On Fri, Nov 27, 2009 at 06:31:03PM -0800, Roger Binns wrote > Alternatively use split to make the input a series of files each > less than 4GB in size. Or use a 64 bit host. [...] > If using a file smaller than 4GB fixes the issue for you then please > let me know and I'll add a ticket about large file support too. First, I tried... [d531][waltdnes][~/SQLite] split -l 1800 dly04.csv [d531][waltdnes][~/SQLite] ll xa* -rw-r--r-- 1 waltdnes users 378000 Nov 27 19:49 xaa -rw-r--r-- 1 waltdnes users 378000 Nov 27 19:52 xab -rw-r--r-- 1 waltdnes users 3583911240 Nov 27 19:54 xac ...and import failed with the same error. Then I tried... [d531][waltdnes][~/SQLite] split -l 900 dly04.csv [d531][waltdnes][~/SQLite] ll xa* -rw-r--r-- 1 waltdnes users 189000 Nov 27 20:06 xaa -rw-r--r-- 1 waltdnes users 189000 Nov 27 20:07 xab -rw-r--r-- 1 waltdnes users 189000 Nov 27 20:09 xac -rw-r--r-- 1 waltdnes users 189000 Nov 27 20:10 xad -rw-r--r-- 1 waltdnes users 189000 Nov 27 20:12 xae -rw-r--r-- 1 waltdnes users 1693911240 Nov 27 20:13 xaf ...and import worked. So it appears that the maximum csv file is probably around 2 gigabytes. > You can recompile the shell setting the flags (-D or #define): > _LARGE_FILE, _FILE_OFFSET_BITS=64 and _LARGEFILE_SOURCE I'm not a C programmer. And I'd have to repeat the changes each time there's an update in the Gentoo linux ebuild. Even something minor like 3.6.17 ==> 3.6.18. I've got 2 choices... 1) Pass your comments on to the Gentoo bugzilla, asking the Gentoo SQLite ebuild maintainer to put in a patch with your suggested change. 2) If the patching is going to be done upstream (i.e. by the SQLite people themselves) soon, then don't do anything. The Gentoo version will automatically inherit the large file support with the new version. -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite won't import 53,066,244 row 11 gigabyte CSV file
I'm running SQLite 3.6.18 on Gentoo linux. The filesystem is Reiserfs, so 11 gigs should not be a problem. Here's a screen scrape... === [d531][waltdnes][~/SQLite] ll dly04.csv -rw-r--r-- 1 waltdnes users 11143911240 Nov 27 15:34 dly04.csv [d531][waltdnes][~/SQLite] sqlite3 dlyxx.sqlite SQLite version 3.6.18 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .mode csv dly04 sqlite> .import dly04.csv dly04 cannot open file: dly04.csv sqlite> === Am I making some glaring noob error here? I've successfully imported, and tested out smaller files using the same method. The table dly04 has already been created, and it's just a matter of importing from the CSV file. Is there a known maximum line-count or file-size? If necessary, I can run "split -l dly04.csv", and import the separate (smaller) pieces. Am I correct in assuming that multiple ".import" statements will append to an existing table, rather than overwriting? (Yes, I am new to SQLite). -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Suggested user-defined-function example
Whilst trying to get a TCL script to create a function in SQLite I ran
into problems and did a lot of Googling. I got very tired of seeing the
same old same old...
proc sql_sqrt {x} {return [expr {sqrt($x)}]}
db function sqrt sql_sqrt
It didn't help me because it used only one parameter. It didn't say
anything about you
- *MUST NOT* have commas between parameters in the function definition
- *MUST* have commas between parameters when actually calling it
I spent several hours figuring this out. Here's a working example...
package require sqlite3
sqlite3 db :memory:
db eval {create table dual(x varchar(1))}
db eval {insert into dual values(' ')}
proc sql_addnum { a b } { return [expr { $a + $b }] }
db function addnum sql_addnum
db eval {select 'Hello world' as x from dual} {puts stdout "$x"}
db eval {select 999 as y from dual} {puts stdout "$y"}
db eval {select addnum(1, 2) as z from dual} {puts stdout "$z"}
db close
And the output is...
Hello world
999
3
Use this code as an example, and it may save someone else some time
down the road.
--
Walter Dnes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem passing SQLite field values to TCL function
I start off passing a central latitude ($lat_degrees) and longitude
($long_degrees) and a radius ($radius) to a TCL script. Using the
spherical cosine law to calculate distance, I want to select all sites
in a table within that given radius. Here are 2 code fragments from the
script...
===
sqlite3 db :memory:
# Note: GIS convention has longitude negative in the western hemisphere.
# But end-users will get annoyed at having to enter the minus sign all the
# time. So the conversion is done internally in the distance() function.
proc sql_distance { lat1 long1 lat2 long2 } {
set radian [expr 180 / 3.1415926]
set lat1 [expr $lat1 / $radian ]
set long1 [expr $long1 / $radian * (-1) ]
set lat2 [expr $lat2 / $radian ]
set long2 [expr $long2 / $radian ]
return [expr {
acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($long2 -
$long1)) * 6371}]}
db function distance sql_distance
...
db eval { create table temp2 as
select e_stnid, i_stnid, deci_lat, deci_long, elevation, stn_name,
distance( $lat_degrees, $long_degrees, deci_lat, deci_long) as dist
from cl.stations
where dist <= $radius}
===
$lat_degrees and $long_degrees are defined
deci_lat and deci_long are valid field names (type real) in table
stations in the attached database (alias "cl"). I get an error message
which tells me that deci_lat and deci_long are not defined. I've run a
separate test to confirm that every row has non-null numbers in deci_lat
and deci_long. So that's not the problem. Here's the error message...
missing operand at _...@_
in expression " _...@_/ 57.295780490442965"
(parsing expression " / 57.295780490442965")
invoked from within
"expr $lat2 / $radian "
(procedure "sql_distance" line 5)
invoked from within
"sql_distance 49.25 123 {} {}"
invoked from within
"db eval { create table temp2 as
select e_stnid, i_stnid, deci_lat, deci_long, elevation,
stn_name,
distance( $lat_degrees, $long..."
invoked from within
"if { $argc < 4 } {
puts "Error: This query requires at least 4 parameters, namely"
puts "Central Latitude, Central Longitude, Radius, and at least..."
(file "./tcltest.004" line 2
--
Walter Dnes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Disk activity on Linux
On Thu, Nov 12, 2009 at 11:24:58AM -0600, Bret Patterson wrote > We're seeing a lot more disk activity than expected on Linux when > using sqlite3. We've run this same series of test on windows and > the disk IO is much lower, which is the opposite of what I really > expected. Below is my scenario and perhaps someone can point out > what I can do to fix this problem. Since it seems to be a linux issue, what are the mount settings for the partitions? This may seem obvious, so please don't take this as an insult. Here goes... - "noatime" (for all file systems) will reduce disk I/O and speed things up - if using ReiserFS, use the "notail" option - the default is that all filesystems are mounted async, but check to make sure Then there are the hard drives. Use the hdparm utility to confirm that DMA is turned on. I assume the linux machine is a server, running in text mode, rather than a resource-heavy GUI. -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [SOLVED] Problems passing parameters between SQLite + TCL
On Wed, Nov 11, 2009 at 02:41:50AM -0500, Walter Dnes wrote
> proc sql_distance {lat1, long1, lat2, long2} {
It seems that "the TCL way" to pass multiple parameters is...
proc sql_distance {lat1 long1 lat2 long2} {
i.e. as a list without any commas.
--
Walter Dnes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] any keyword
On Wed, Nov 11, 2009 at 09:37:31AM +0100, Andrea Galeazzi wrote > Probably sqlite doesn't support 'any' keyword as I write it in the > following query: > SELECT G.id,name FROM Genre G > WHERE G.id = ANY (SELECT S.genre_id FROM Song S) > ORDER BY name ASC; Maybe I'm mis-understanding your query. Can you use a subquery... SELECT G.id,name FROM Genre G WHERE G.id IN ( SELECT genre_id FROM Song ) ORDER BY name ASC; -- Walter Dnes ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problems passing parameters between SQLite + TCL
There's a lot more to this program, but I've cut it down to the bare
minimum that illustrates my problem. I call a TCL script from the linux
commandline, and get an error message like so...
[waltdnes][~/SQLite] ./fragment 49.25 123 25
can't read "lat1": no such variable
while executing
"expr $lat1 / $radian "
(procedure "sql_distance" line 3)
invoked from within
"sql_distance 49.25 123 48.914 -123.7"
invoked from within
"db eval { select e_stnid, i_stnid, deci_lat, deci_long, elevation,
distance($lat_degrees, $long_degrees, deci_lat, deci_long) as dist
..."
(file "./fragment" line 21)
Here is the cut-down program...
#!/usr/bin/tclsh
set lat_degrees [expr [lindex $argv 0]]
set long_degrees [expr [lindex $argv 1]]
set radius [expr [lindex $argv 2]]
load /usr/lib/sqlite-3.6.17/libtclsqlite3.so
sqlite3 db :memory:
# Note: GIS convention has longitude negative in the western hemisphere.
# But end-users will get annoyed at having to enter the minus sign all the
# time. So the conversion is done internally in the distance() function.
proc sql_distance {lat1, long1, lat2, long2} {
set radian [expr 180 / 3.1415926]
set lat1 [expr $lat1 / $radian ]
set long1 [expr $long1 / $radian * (-1) ]
set lat2 [expr $lat2 / $radian ]
set long2 [expr $long2 / $radian ]
return [expr {
acos(sin($lat1) * sin($lat2) + cos($lat1) * cos($lat2) * cos($long2 -
$long1)) * 6371}]}
db function distance sql_distance
db eval {attach 'climate.sqlite' as cl}
db eval { select e_stnid, i_stnid, deci_lat, deci_long, elevation,
distance($lat_degrees, $long_degrees, deci_lat, deci_long) as dist
from cl.stations
where dist <= $radius} {puts stdout "$e_stnid, $i_stnid, $deci_lat,
$deci_long, $elevation, $dist"}
db close
I'm trying to do a select on all sites within a given radius of a
given point. I'm using the "spherical cosine law" to calculate distance.
The "sql_distance 49.25 123 48.914 -123.7" error message
shows that I successfully passed the first 2 command line parameters and
the last 2 came from an entry in table cl.stations.
Given that all 4 parameters hace been passed tothe distance()
function, why are they undefined in the proc?
--
Walter Dnes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do I output multi-word strings without braces?
On Tue, Nov 10, 2009 at 03:06:08AM -0500, Walter Dnes wrote
> Given the following code fragment...
>
> set xname [db eval { select name from elements where e_mtid = $element }]
> puts [format "Requested element ==> %s ==> %s" $element $xname]
Oops, I forgot to mention that this is the TCL interface to SQLite.
This is one of those "grey area" questions that could go to either TCL
or SQLite forums, because it's an interaction between the two of them.
I assume that some people here have dealt with this issue before.
--
Walter Dnes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How do I output multi-word strings without braces?
Given the following code fragment...
set xname [db eval { select name from elements where e_mtid = $element }]
puts [format "Requested element ==> %s ==> %s" $element $xname]
The "business rules" are such that I know I'll only get one row
returned. I get output like so...
Requested element ==> abcdef ==> {FOO BAR}
What I need is...
Requested element ==> abcdef ==> FOO BAR
What do I need to do to get rid of the braces around the output name?
And no, that's not how the data looked in the tab-delimited file it was
imported from.
--
Walter Dnes
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3WinOpenReadWrite() in os_win.c
I am posting this as a bug. Walter Meerschaert wrote: I am looking at the code in os_win.c for sqlite3WinOpenReadWrite() and have a question. The routine uses the windows API CreateFile() to open the file. First it checks to see if it can open it read/write and then if that fails, it tries read-only. Now, when it tries read/write, it uses FILE_SHARE_READ|FILE_SHARE_WRITE as the share parameter, and when it tries read-only, it uses FILE_SHARE_READ. According to logic and the windows documentation, this precludes the current process or any other process on this or any other machine from subsequently opening the file read/write. Is this the desired behavior? If so, why? I would think that there would be a normal state of affairs where some clients are read-only and some are read/write. If the above is true and I am not missing something, then in some instances, those with the access rights can sometimes open the database read/write (if a read-only process has not already opened it) and sometimes not.
[sqlite] sqlite3WinOpenReadWrite() in os_win.c
I am looking at the code in os_win.c for sqlite3WinOpenReadWrite() and have a question. The routine uses the windows API CreateFile() to open the file. First it checks to see if it can open it read/write and then if that fails, it tries read-only. Now, when it tries read/write, it uses FILE_SHARE_READ|FILE_SHARE_WRITE as the share parameter, and when it tries read-only, it uses FILE_SHARE_READ. According to logic and the windows documentation, this precludes the current process or any other process on this or any other machine from subsequently opening the file read/write. Is this the desired behavior? If so, why? I would think that there would be a normal state of affairs where some clients are read-only and some are read/write. If the above is true and I am not missing something, then in some instances, those with the access rights can sometimes open the database read/write (if a read-only process has not already opened it) and sometimes not.
Re: [sqlite] help with sqlite command
Dennis Cote wrote: To get every N'th row after deletions you need some way to assign a series of integers to the result rows. The easiest way I can think of is to create a temporary table from your initial query. Then you can use the modulus operator to select every N'th record from that table as you have suggested since the rowids will all be freshly assigned. You will also need to drop the temp table when you are done with it. create temp table temp_table as select * from my_table where ; select * from temp_table where rowid % N = 0; drop table temp_table; If the table rows are large, or if the number of rows is large, you might want to do this refinement: create temp_table as select rowid as source_rowid from my_table WHERE ...; select * from my_table, temp_table where temp_table.rowid%N=0 and source_rowid=my_table.rowid; drop table temp_table; Actually, this looks like a great way to implement many kinds of weird sorting/indexing schemes (percentile ranking, hi/low ordering, grouping). Such a case would be to find the decile rankings of an table (with numbers in it). In that case, N would be the count(*) / 10. and the original WHERE would describe the order over which the ranking is to be done. Or use count/2 to get at the median. (if N < 100, one might also need to interpolate).
Re: [sqlite] Why can i open a textfile?
D. Richard Hipp wrote: Most people who are using SQLite successfully have, I imagine, either written their own wrappers around the core API (which is not hard as I do provide you with a lot of helper routines such as sqlite3_vmprintf and friends) or they are using an existing wrapper written by someone else. Indeed I wrote a thin wrapper when I started using SQLite 6 months ago. I now retract my request for more control over the open function, as one of the features of SQLite that I really liked was that it didn't have an extensive set of "features". I can and will implement the read/only and exclusive tests in my own open wrapper, and return or throw where appropriate.
Re: [sqlite] Why can i open a textfile?
I agree, since that makes error/exception handling easier. On the subject of open(), I also would like it to have a read_only option, if that is possible. I am not even sure that a read-only state is tracked through the library, or if the writing attempts just fail with an file access error. Or is there already a way to open the database read only? Edwin Knoppert wrote: I haven't test on exactly 1kb file but larger and indeed an error is shown. May i stress again that the test should be during open() imo? And rather not using a 2nd function to examine the db.
[sqlite] how to get notice when database is free after it was busy
As a courtesy to my users, I wish to pop up a message thingy telling the user when the program is waiting for the database to not be busy. Right now I register a busy handler, and so I know how long I have been waiting, because we have the count parameter, when it is 1, I look at the clock, when the clock goes past 3 seconds or so it is time to tell the user tio cool his heels while we wait for some process to commit or rollback. As far as I can tell, there is no direct way to know when the database has begun responding again. The busy handler simply stops being called. Is there a direct way I fail to see? Or, perhaps there could be an addition to the api wherein we are told when the buy state is no more. Perhaps we could register a no_longer_busy_handler, which could take the same parameters as the busy handler but pass in -1 for the count.
