Re: [sqlite] INSERT vs BEGIN

[Rob Richardson]

I didn't know it is possible to insert multiple rows into a table using a
command like this.  Is this just an SQLite feature, or is this part of the
SQL standard?

RobR

On Mon, Sep 2, 2019 at 8:14 AM Dominique Devienne 
wrote:

> On Mon, Sep 2, 2019 at 12:52 PM Simon Slavin  wrote:
>
> > > One must know that I am obliged to use "BEGIN DEFERRED TRANSACTION"
> > because others threads needs to access to tables.
> > SQLite copes very well when you have one connection writing to the
> > database and other connections reading.  The problems come when you have
> > two connections writing to the database at once.
> >
>
> In WAL mode only! Otherwise readers are blocked when the writer is active,
> and readers prevent the writer from proceeding. --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

Re: [sqlite] Tracking item history using SQLite

[Rob Richardson]

Why are you storing first_seen in every record?  To avoid searching for it
when reports are generated?

On Sat, Aug 31, 2019 at 6:24 AM Rob Willett 
wrote:

> Hi,
>
> We have a very similar system that captures traffic incident information
> such as accidents, roadworks, traffic jams and sends personalised
> information to drivers based on their routes.
>
> We realised after a few years that our original design for the database
> was inefficient as we had a single table that was about 60GB, circa 500M
> rows, that consisted of traffic incidents and uptakes. The inefficiency
> was our design and not SQLite.
>
> This inefficiency showed when we used to run a daily report that
> summarised the traffic information for historical trends, e.g. we wanted
> to know the average time for a road accident to be cleared on a stretch
> of road on a specific day or time of day. This report mean we read in
> circa 500M rows and 60GB of data each night and took circa 90 mins to
> run as we did a lot of calculations.
>
> We were not able to change the structure of the database too much (as it
> was too far down the line). So we spent a long time looking at the data
> with cold, wet towels on our foreheads in darkened rooms :) After we
> analysed the data we realised that most of our 'updates' were not
> actually updates at all an all were were actually interested in was the
> start and end times of incidents, all the 'stuff' in the middle was just
> noise.  The problem we had was that disruptions could last a long time
> and there was no actual end of incident marker, just that there wasn't
> another disruption with that unique identifier and a later timestamp.
> This sounds similar to your situation. Basically we constructed a simple
> state model based on time.
>
> What we now do is to each night we run a Perl script each night to
> remove anything that is between the first and end incidents. We know
> that the end incident may move on the next day, but at that point in
> time it is still the last/end/most recent indicator. Our data is
> structured around time so we always have a time of insertion indicator
> in the field as things may not be ordered by row id.
>
> Once we had this insight, we took our database down from 60GB to 800MB
> and the report that ran from 90 mins to 90 secs. We have to give credit
> to this mailing list for the patient and courteous way that helped and
> assisted with our often stupid questions.
>
> The lessons we learnt from this are:
>
> 1. Get your database schema right from the go. We didn't. In hindsight
> we should have spent longer looking at this.
>
> 2. Put in more information than you think you may need just in case. We
> did that. We put time information in at per second granularity.
>
> 3. Don't worry too much about disk space unless you are on a constrained
> device. In hindsight we could have stored even more data :)
>
> 4. Think hard about solutions that are simple. We love simplicity as
> this makes it easier to go back and change things. Too many dependencies
> would be too difficult to unpick. Keeping it simple also works for us as
> we're not experts :)
>
> 5. SQLite seems to be able to do anything we want it to. We know the
> 'limitations' of it not being a full client/server database, just to be
> clear, we do not consider this a limitation at all but rather a virtue.
> Other people seem worried about the 'lack' of some datatypes, we do
> masses of data and date conversations as needed and it's never been a
> speed issue or any issue.
>
> 6. Ask this group for help. We realised that the more information we
> provided in a clear and concise manner in our help email, the more help
> we got, it was a virtuous circle. The sum help of the people in this
> group far, far exceeds our own knowledge. As a rule of thumb, if we ask
> for help, we expect it to take 1-2 hours as a minimum for us to write
> the email. That may be because we're not SQL experts. Also follow up
> every email as if people have taken the time to reply to us, they
> deserve a reply themselves. As we are English, we could end up in a
> thanks for the reply endless loop :) The group has an exceptionally
> signal to noise ratio and is invariably courteous.
>
> I would be astonished if you can't get SQLite to do what you want to do.
> We have never managed to touch the sides of the system and suspect we
> never will.
>
> More than happy to answer more questions as this group helped us and
> it's only fair we offer the help back. I will state that we are not SQL
> (or SQLite) experts :)
>
> Rob
>
> On 30 Aug 2019, at 21:44, Random Coder wrote:
>
> > First off, if this sort of "code review" style question is
> > inappropriate for this list, please feel free to reply to me directly
> > and tell me to stop, and I'll know to not do this again.
> >
> > That said, I have a question on the use of SQLite.  At the risk of
> > falling into the XY problem, I'll give some details on what i'm doing,
> > and then how I'm doing 

Re: [sqlite] How to round to an Integer

[Rob Richardson]

I don't think the cast rounds, though.  It just truncates.  Am I wrong?

RobR

On Thu, Oct 18, 2018 at 4:13 PM Richard Hipp  wrote:

> On 10/18/18, John Harney  wrote:
> > Recently figured this out.  Seems to work fine
> >
> > trim(trim(round(1.111,0),'0'),'.')   = 1
> >
>
> CAST(1.111 AS integer)
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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

Re: [sqlite] Using SQLite in C# without System.Data.SQLite

[Rob Richardson]

What target platforms have you found on which it is unreliable?  I haven't
had problems on Win7, but I haven't used it all that much.

RobR

On Thu, Oct 18, 2018 at 2:57 PM Jordy Deweer  wrote:

> Larry Brasfield wrote: "Jordy Deweer asks: “Is there a way to use a
> SQLite database in a C# project, without
> > installing / configuring / depending on the System.Data.SQLite libraries?
> >
> > I really hope there is.”
> >
> > If you do a web search for the combination of terms “SQLite”, “C#” and
> “library”, you will find several alternative libraries.  Some of them are
> thinner wrappers (of SQLite) or provide less complete exposure of SQLite’s
> API.
> >
> > You have the option of fashioning your own wrapper using the C# P/Invoke
> mechanism, or you may find the ‘NativeMethods’ functionality of interest.
> However, doing this robustly requires enough expertise that you should be
> wary of tackling the project."
>
>
>
> Thank you so much for your reply. I sill surely look at the options you
> gave me.
>
>
> I ask because I run into errors a lot, using the System.Data.SQLite
> libraries. It easily crashes, trhows exceptions and similar issues... I
> found out that it matters which target platform is selected...
>
>
> So thanks once again.
>
>
> Regards, Jordy
>
> ___
> 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

Re: [sqlite] Using SQLite in C# without System.Data.SQLite

[Rob Richardson]

Why don't you want to use System.Data.SQLite?

On Thu, Oct 18, 2018 at 11:04 AM Jordy Deweer  wrote:

> Dear all
>
> I am new to this group and this is my first question here.
>
> My question is as follows:
>
> Is there a way to use a SQLite database in a C# project, without
> installing / configuring / depending on the System.Data.SQLite libraries?
>
> I really hope there is.
>
> Thank you so much in advance for your help.
>
> Best wishes
>
> Jordy
>
> ___
> 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

Re: [sqlite] Unique Constraint Failed

[Rob Richardson]

Dumb question:  are you sure you're only inserting one record at a time?
Is it possible you're inserting records so fast that the timestamp is the
same for two of them?


On Fri, Sep 14, 2018 at 3:30 PM Andrew Stewart 
wrote:

> Hi,
> I am having problems with a database reporting Unique
> Constraint Failed when doing an insert.
> Table consists of 3 columns:
> ID, DateTime, data
> Constraint is on ID,DateTime.
>
> DateTime trying to enter is current time.
>
> File is 200+ GB.
>
> I have tested this same to a 1.4TB file, but have updated
> my copy of SQLite source since that test.
>
> Thanks,
> Andrew Stewart
> Software Designer
>
> ARGUS CONTROLS
> 18445 53rd Avenue | Surrey, BC | V3S 7A4 | Canada
> t +1.604.538.3531  ext. 108 | +1.800.667.2090 | f +1.604.538.4728
> www.arguscontrols.com
>
> Notice: This electronic transmission contains confidential information,
> intended only for the person(s) named above. If you are not the intended
> recipient, you are hereby notified that any disclosure, copying,
> distribution, or any other use of this email is strictly prohibited. If you
> have received this transmission by error, please notify us immediately by
> return email and destroy the original transmission immediately and all
> copies thereof.
> ___
> 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

Re: [sqlite] [SQLITE]select from a table and use its data to select from another one

[Rob Richardson]

Double quotes can be used to specify that you mean a database object when
the name of the object might be confused with a keyword.  For example, my
company's database models a production system with various recipes.  We
call them "cycles".  But the word "cycle" appears to have some specific
meaning inside SQL (or at least, inside PostgreSQL).  So, although the
query
   SELECT * FROM cycle
works, we should probably use
SELECT * FROM "cycle"
to avoid any possible ambiguity.

RobR

On Fri, Sep 14, 2018 at 2:12 PM Rob Richardson 
wrote:

> The use of single quotes instead of double quotes in database queries is
> not limited to SQLite.  That's part of the SQL standard.
>
> RobR
>
> On Fri, Sep 14, 2018 at 2:05 PM David Raymond 
> wrote:
>
>> Small typo:
>>
>> SELECT * FROM table2 JOIN table1
>> ON table1.rowid = table2.rowid
>> WHERE table1.name LIKE '%smth%'
>>
>>
>> -Original Message-
>> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
>> On Behalf Of Simon Slavin
>> Sent: Friday, September 14, 2018 1:59 PM
>> To: SQLite mailing list
>> Subject: Re: [sqlite] [SQLITE]select from a table and use its data to
>> select from another one
>>
>> On 14 Sep 2018, at 6:50pm, Maziar Parsijani 
>> wrote:
>>
>> > I have 2 tables with the same rowid now I want to :
>> > select rowid from table1 where table1 like "%smth%"
>> > select * from table2 where rowid =(selected rows before)
>> >
>> > I mean if I could do it in a same query.
>>
>> This is what JOIN is for.
>>
>> SELECT * FROM table2
>> JOIN table1.rowid = table2.rowid
>> WHERE table1.name LIKE '%smth%'
>>
>> Note that SQLite uses single quotes ' for text strings, not double quotes
>> ".
>>
>> Simon.
>> ___
>> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [SQLITE]select from a table and use its data to select from another one

[Rob Richardson]

The use of single quotes instead of double quotes in database queries is
not limited to SQLite.  That's part of the SQL standard.

RobR

On Fri, Sep 14, 2018 at 2:05 PM David Raymond 
wrote:

> Small typo:
>
> SELECT * FROM table2 JOIN table1
> ON table1.rowid = table2.rowid
> WHERE table1.name LIKE '%smth%'
>
>
> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Simon Slavin
> Sent: Friday, September 14, 2018 1:59 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] [SQLITE]select from a table and use its data to
> select from another one
>
> On 14 Sep 2018, at 6:50pm, Maziar Parsijani 
> wrote:
>
> > I have 2 tables with the same rowid now I want to :
> > select rowid from table1 where table1 like "%smth%"
> > select * from table2 where rowid =(selected rows before)
> >
> > I mean if I could do it in a same query.
>
> This is what JOIN is for.
>
> SELECT * FROM table2
> JOIN table1.rowid = table2.rowid
> WHERE table1.name LIKE '%smth%'
>
> Note that SQLite uses single quotes ' for text strings, not double quotes
> ".
>
> Simon.
> ___
> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How can I lock a database?

[Rob Richardson]

I have a program reads data from 17 PLCs and writes it into SQLite databases 
every minutes.  Every midnight, it deletes old data and vacuums the databases.  
It's behaving strangely after that.  I think the problem begins because the 
vacuum operation is still going on at 12:01, when the next read is scheduled.  
SQlite throws a "database is locked" error.  

I tried to replicate this by writing a little program that vacuums one SQLite 
database repeatedly for three minutes.  The first time I tried to use it, the 
main program behaved as expected, showing the same odd behavior.  But the next 
two times I tried, my program sailed right through the "database is locked" 
error, running as it was designed with no problems.  

So, instead of running vacuum over and over again, I would like to do something 
that would lock my database against writing once, and then not release it for 
three minutes.  How can I do that?

Thank you very much.

RobR
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference between localtime and utc is 8 hours, but should be 4

[Rob Richardson]

That makes sense.  Thank you very much.

RobR

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Stephen Chrzanowski
Sent: Friday, April 21, 2017 4:29 PM
To: SQLite mailing list
Subject: Re: [sqlite] Difference between localtime and utc is 8 hours, but 
should be 4

Let me clarify;

What you store in the database is just a number.  There is no indication to 
what timezone that references.  So when you convert UTC to UTC, you're taking a 
time already set for UTC and converting it to another 4 hours earlier (Or 
later? -- i hate time math).  When I say "UTC to UTC", the first UTC isn't 
actually UTC, but assumed local.

From https://sqlite.org/lang_datefunc.html under Modifiers section:

The "localtime" modifier (12) assumes the time string to its left is in 
Universal Coordinated Time (UTC) and adjusts the time string so that it 
displays localtime. If "localtime" follows a time that is not UTC, then the 
behavior is undefined. The "utc" modifier is the opposite of "localtime".
"utc" assumes that the string to its left is in the local timezone and adjusts 
that string to be in UTC. If the prior string is not in localtime, then the 
result of "utc" is undefined.




On Fri, Apr 21, 2017 at 4:24 PM, Stephen Chrzanowski <pontia...@gmail.com>
wrote:

> Because you're converting your UTC time to UTC.
>
> On Fri, Apr 21, 2017 at 4:03 PM, Rob Richardson 
> <rdrichard...@rad-con.com>
> wrote:
>
>> Hello!
>>
>> I'm in the Eastern US time zone, in daylight savings time.  I am four 
>> hours earlier than UTC time.  I have a column that stores UTC times 
>> as Julian times (floating-point numbers).  The latest data point in 
>> the table was stored at about 8:41 this morning (4/21).
>>
>> I am getting strange results from this query:
>> select max(value_timestamp),
>> datetime(max(julianday(value_timestamp)), 'localtime'), 
>> datetime(max(julianday(value_timestamp)), 'utc') from trend_data
>>
>> The results are:
>> 2457864.86179398
>> 2017-04-21 04:40:59
>> 2017-04-21 12:40:59
>>
>> How is it that switching from local time to UTC gives an eight-hour 
>> difference?
>>
>> Thank you very much.
>>
>> RobR
>> ___
>> 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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Difference between localtime and utc is 8 hours, but should be 4

[Rob Richardson]

Hello!

I'm in the Eastern US time zone, in daylight savings time.  I am four hours 
earlier than UTC time.  I have a column that stores UTC times as Julian times 
(floating-point numbers).  The latest data point in the table was stored at 
about 8:41 this morning (4/21).  

I am getting strange results from this query:
select max(value_timestamp), 
datetime(max(julianday(value_timestamp)), 'localtime'),
datetime(max(julianday(value_timestamp)), 'utc') from trend_data

The results are: 
2457864.86179398
2017-04-21 04:40:59
2017-04-21 12:40:59

How is it that switching from local time to UTC gives an eight-hour difference?

Thank you very much.

RobR
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to use parameterized queries in SQLite.Net

[Rob Richardson]

To answer my own question:  this works:

using (SQLiteCommand command = m_conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "INSERT INTO trend_data (tag_key, value, 
value_timestamp) VALUES (?, ?, ?)";
SQLiteParameter param;
param = new SQLiteParameter();
param.Value = 2;
command.Parameters.Add(param);
param = new SQLiteParameter();
param.Value = 234.56;
command.Parameters.Add(param);
param = new SQLiteParameter();
param.Value = DateTime.Now;
command.Parameters.Add(param);
rowsAffected = command.ExecuteNonQuery();
}

RobR

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Rob Richardson
Sent: Monday, March 13, 2017 2:23 PM
To: General Discussion of SQLite Database (sqlite-users@mailinglists.sqlite.org)
Subject: [sqlite] How to use parameterized queries in SQLite.Net

Hello again.

Since my attempt to find the official answer for myself has hit a snag, I'll 
just ask here.

The examples I've seen for parameterized queries used with the SQLiteCommand 
class have shown named parameters, and the names usually begin with an "@" 
character.  Is that character required for named parameters?  Is that the 
correct leading character?  Is it required to include that leading character in 
the name given to the SQLiteParameter object?

I'm used to using the System.Data.ODBC classes, which do not support named 
parameters, but they do support unnamed parameters, represented by question 
marks.  The order in which the parameters are attached to the command object 
determines the association between the parameter object and the query 
parameter.  Unnamed parameters would be easier for me to work with than named 
ones.  Does SQlite.Net support unnamed parameters?

Thank you.

RobR


___
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

Re: [sqlite] Help file has no information

[Rob Richardson]

Thank you.  That worked.  (The button was labelled "unblock", not "unlock")

RobR

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Graham Holden
Sent: Monday, March 13, 2017 2:26 PM
To: SQLite mailing list
Subject: Re: [sqlite] Help file has no information

Either don't store it on a network drive or right click, select properties and 
unlock (see 
https://social.technet.microsoft.com/Forums/en-US/20700886-2000-4c52-b706-aa1fb32d3dfb/cant-view-chm-file-contents?forum=W8ITProPreRel).
 I think.
Graham

Sent from my Samsung Galaxy S7 - powered by Three
 Original message From: Rob Richardson 
<rdrichard...@rad-con.com> Date: 13/03/2017  18:14  (GMT+00:00) To: "General 
Discussion of SQLite Database (sqlite-users@mailinglists.sqlite.org)" 
<sqlite-users@mailinglists.sqlite.org> Subject: [sqlite] Help file has no 
information The help file that is linked from the System.Data.SQLite home page 
appears to have a table of contents but no information.  No matter what page I 
select, the page does not appear.

RobR
___
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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to use parameterized queries in SQLite.Net

[Rob Richardson]

Hello again.

Since my attempt to find the official answer for myself has hit a snag, I'll 
just ask here.

The examples I've seen for parameterized queries used with the SQLiteCommand 
class have shown named parameters, and the names usually begin with an "@" 
character.  Is that character required for named parameters?  Is that the 
correct leading character?  Is it required to include that leading character in 
the name given to the SQLiteParameter object?

I'm used to using the System.Data.ODBC classes, which do not support named 
parameters, but they do support unnamed parameters, represented by question 
marks.  The order in which the parameters are attached to the command object 
determines the association between the parameter object and the query 
parameter.  Unnamed parameters would be easier for me to work with than named 
ones.  Does SQlite.Net support unnamed parameters?

Thank you.

RobR


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Help file has no information

[Rob Richardson]

The help file that is linked from the System.Data.SQLite home page appears to 
have a table of contents but no information.  No matter what page I select, the 
page does not appear.

RobR
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Where is the official document for System.Data.SQLite?

[Rob Richardson]

Blindness cured.  I had visited that page but missed the super-sized, bold 
headline that said "Documentation for System.Data.SQLite".  

Thanks for opening my eyes.

RobR

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Donald Griggs
Sent: Monday, March 13, 2017 1:57 PM
To: SQLite mailing list
Subject: Re: [sqlite] Where is the official document for System.Data.SQLite?

On Mon, Mar 13, 2017 at 1:44 PM, Rob Richardson <rdrichard...@rad-con.com>
 wrote:

> Where is the official document for System.Data.SQLite?  And is there a 
> better list than this one to ask questions about System.Data.SQlite?


Hi Rob,

I don't use system.data.sqlite, but I believe you'll find the documentation
at:

http://system.data.sqlite.org

>
>
___
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

Re: [sqlite] Why isn't my time formatting working?

[Rob Richardson]

Thank you.

The https://www.sqlite.org/lang_datefunc.html page doesn't seem to make it 
clear what strftime() returns.  The specification it gives for strftime() is:

strftime(format, timestring, modifier, modifier, ...)

Given the lack of an indication of the return type, it seemed to me to be 
reasonable to assume that since I'm passing in a string as one of the 
arguments, I'd get a datetime object out.  It did not seem reasonable to me to 
merely pass in a string of a forced format, the ISO standard format Dr. Hipp 
mentioned, to get a string in some other format.  

But I've been burned before by data types, or lack thereof, in SQLite.  I 
usually work in C# and PostgreSQL, where variables and data columns always have 
definite data types, and, if I remember correctly (it's been a couple of years 
since I worked with SQLite), SQLite does things differently.  I know there's 
nothing stopping me from putting any value into a field, regardless of the type 
of data other records have for that field.

RobR

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Donald Griggs
Sent: Wednesday, March 08, 2017 2:40 PM
To: SQLite mailing list
Subject: Re: [sqlite] Why isn't my time formatting working?

Hi Rob,

The format string of '%d/%m/%Y %H:%M:%S' describes what you want as output, not 
what you're supplying as input.

You can use substr() and concatenation || to mash up your original string into 
the ISO format (which is much easier to handle anyway.) 
___
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

Re: [sqlite] Why isn't my time formatting working?

[Rob Richardson]

But the strftime() function is supposed to work with whatever format I give it, 
isn't it?  According to the documentation, %d is a two-digit day, %m is a 
two-digit month, and so on.   Is there truly no way to convert my original 
string into a datetime object?

RobR

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Hipp
Sent: Wednesday, March 08, 2017 2:28 PM
To: SQLite mailing list
Subject: Re: [sqlite] Why isn't my time formatting working?

On 3/8/17, Rob Richardson <rdrichard...@rad-con.com> wrote:
> Hello!
>
> I have a table with times stored as strings.  I massaged them into a 
> form that strftime() should be able to work with, but it's not 
> working.  Here's a little query using the string as it is currently formatted:
>
> select strftime('%d/%m/%Y %H:%M:%S', '03/07/2017 13:06:03')

SQLite uses ISO-8601 dates:  -MM-DD
--
D. Richard Hipp
d...@sqlite.org
___
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] Why isn't my time formatting working?

[Rob Richardson]

Hello!

I have a table with times stored as strings.  I massaged them into a form that 
strftime() should be able to work with, but it's not working.  Here's a little 
query using the string as it is currently formatted:

select strftime('%d/%m/%Y %H:%M:%S', '03/07/2017 13:06:03')

This query returns null.  Why?  

(Hmmm...  I see that it should return July 3rd, 2017 when I want March 7th, but 
that doesn't explain why it doesn't give me anything.)

Thanks for your help.

RobR
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Unable to run SQLite programs: error 0xC000007b

[Rob Richardson]

Greetings!

I used SQLite in a successful program a few years ago.  I am trying to 
resurrect that program for a new customer, but it isn't working, and I'm having 
a hellish time trying to debug it.

I have a Windows 7 XP1 OS, and I'm using Visual Studio 2008, which is what the 
original program was built with.

The original program builds an ActiveX control.  I have a web page (HTML file) 
that includes the control.  I can build the control in debug mode, load the web 
page in Internet Explorer, and the control works.  It opens an SQLite database, 
tries to prepare a statement, and throws an error complaining that a table does 
not exist, even though it does.  The SQL statement and the SQLite file are 
unchanged from the version that is running at the old customer site.  

I have stripped out the SQLite code into a separate project so that I can run 
it by itself, and hopefully step through it in a debugger.  However, when I try 
to run it, I get the following message:

"The application was unable to start correctly (0xc07b).  Click OK to close 
the application."

I have a feeling this is related to 32-bit vs 64-bit applications, but I'm not 
sure.  What do I need to do to get this little test program to run?

Thank you very much.

Rob Richardson
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Did A Recent Windows & Update Affect SQLite???

[Rob Richardson]

I have to challenge one of your statements.  Adobe Camera Raw is not
nearly identical to Lightroom.  ACR is Lightroom's editing engine.
When you are editing an image in Lightroom, you are using ACR.  ACR
has no need for a database of any kind, unless the XMP file contains a
very tiny database.  ACR certainly doesn't have a major database
engine built into it, as Lightroom does with SQLite.

RobR, who would love to see the SQLite schema Lightroom uses

On Tue, Mar 31, 2015 at 9:45 PM, Joe Mistachkin  
wrote:
>
> Mark Romero wrote:
>>
>> A few months ago one of the programs that I use (Lightroom) started to
>> have long periods where it would stop responding. It wouldn't crash,
>> but it would just keep my CPU pegged at 100% and the program would stop
>> responding. This would happen at random times.
>>
>
> When I see unexpected behavior like this from an application, the first
> thing I normally do is check the Windows Event Log for serious errors,
> especially those related to the disk drive(s) in the system.
>
> Next, I would run "chkdsk" and some other diagnostic tools (e.g. the
> Windows Memory Diagnostic tool).
>
> Next, I would run a Microsoft Security Essentials "full scan".
>
> If all of the above are OK, you might want to see if the application
> and/or Windows have any more available updates.
>
> --
> Joe Mistachkin
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite subqueries

[Rob Richardson]

A Google search for "USS Yorktown" turned up the following:

"On September 21, 1997, a division by zero error on board the USS Yorktown 
(CG-48) Remote Data Base Manager brought down all the machines on the network, 
causing the ship's propulsion system to fail."

RobR

-Original Message-


To eliminate the need to reference a table would require combining  300 tables 
into one table. A user editing entries for one space could crash the whole 
system. That's basically what happened aboard the Yorktown in 1997. A cook 
trying to enter an item into the lunch menu killed the engines on the ship.

Re: [sqlite] Sqlite querie delete bug

[Rob Richardson]

This doesn't sound like an SQLite problem to me.  Instead it sounds like a 
problem with whatever visualization tool you are using.  What tool are you 
using?  If you can find a user's group for that tool, you may get more helpful 
answers there.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Oto _
Sent: Wednesday, July 30, 2014 8:53 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Sqlite querie delete bug

There is an issue that if entry is selected in Table view then editing in Query 
view and clicking delete key then it doesn't delete query but asks to delete 
database entry which is not expected behavior.
___
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] how to write this commands?

[Rob Richardson]

Igor,

It took me a bit of looking, but I think I understand your query.  One question 
remains:  why did you use the max() function?

Thanks!

RobR

-Original Message-

update adla1 set PFLOPF=(
   select case count(*)=1 then max(adl.pflopf) else adla1.pflopf end
   from adl where adla1.ref=adl.ref);

This says: for each row in adla1, if adl has exactly one row with the same ref 
value, then set adla1.pflopf to adl.pflopf taken from that one matching row. 
Otherwise, leave adla1 row unchanged.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in division?

[Rob Richardson]

I don't know if it's in the SQL standard or not, but the C, C++ and C# 
languages all act this way.  The result of mathematical operations on integers 
is always an integer.  If you want the result to be a floating-point number, 
you have to force at least one of the operands to be a floating-point number.

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Colons (was: RE: Groups and members)

[Rob Richardson]

In Igor's post below, what is the meaning of the colon in front of mypid?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Wednesday, November 06, 2013 8:45 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Groups and members

On 11/6/2013 6:55 AM, Ulrich Goebel wrote:
> Now my problem: For a given person (that is a given p_id) I would like 
> to get a list of all (!) groups, marked wether p_id is a member of it 
> or not. If p_id=1 and this person is member of groups 4, 8 and 9,
>
> SELECT g_id, p_id, p_is_member_of_g ...
>
> should give
>
> g_id  p_id  is_member
> 4 1 yes
> 8 1 yes
> 9 1 yes
> 1 1 no
> 2 1 no
> 3 1 no
>
> Note that the groups with p_id in it come first and are sorted, then 
> come the groups with p_id not in it, sorted too.

select g_id, :mypid,
   (case when r.p_id is null then 'no' else 'yes' end) is_member from g left 
join r on (g.g_id = r.g_id and r.m_id = :mypid); order by (r.p_id is null), 
g_id;

On an unrelated note, your text fields should have the type of "text", not 
"string". The latter has no special meaning to SQLite, but the former does. For 
details, see http://sqlite.org/datatype3.html
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] updating using a value from another table

[Rob Richardson]

Thank you.  Now, can you show us sample data from your tables before this query 
is run?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of dean gwilliam
Sent: Monday, October 07, 2013 10:45 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] updating using a value from another table

Simon, Rob
Thanks very much for your responses.
I downloaded and used the shell...it gives the same results i.e. the first 
std_itm fld in aliases irrespective of what the value of raw_fld is in itms


SQLite version 3.8.0.2 2013-09-03 17:11:13
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE 
raw_nm = x
.raw_nm);
sqlite> select std_nm from itms limit 10
...> ;
sales
sales
sales
sales
sales
sales
sales
sales
sales
sales
sqlite>

___
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] updating using a value from another table

[Rob Richardson]

Your query looks good to me, which probably means I'm missing the same thing 
you are.  What happens when you run this query?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of dean gwilliam
Sent: Monday, October 07, 2013 7:48 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] updating using a value from another table

Here it is
tbl itms (std_nm text, raw_nm text)
tbl aliases (std_nm text, raw_nm text)

Id like to whip through table itms which has a blank col std_nm and fill in as 
many fields as I can by reading each itms' raw_nm fld finding any matching rec 
(with the same raw_nm value) in aliases updating the std_nm fld in itms with 
corresponding std_nm value in the matching fld in aliases

Here's my miserable attempt
gDb eval "UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm 
= x.raw_nm);"

Any help much appreciated
___
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] GUI for SQLite

[Rob Richardson]

Not all of us.  Thanks for the list.

RobR, SQLiteSpy user and about to find Navicat.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite NULL or 0-Length

[Rob Richardson]

Simon,

Is there a danger here if firstname is NULL and the LENGTH() function is called 
first?  

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Thursday, May 23, 2013 2:54 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite NULL or 0-Length


On 23 May 2013, at 7:06pm, Jill Rabinowitz  wrote:

> Does anyone know how I can check the column and set it to a value if 
> it has nothing in it?

UPDATE myTable SET firstname='xxx' WHERE LENGTH(firstname) = 0 OR firstname IS 
NULL

Simon.
___
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] (no subject)

[Rob Richardson]

First idea: include a subject line.

I'm not 100% clear on your message.  You said:
" For those entries in table1 where there is a null in t2..."
I'm guessing you wanted to say:
" For those entries in table1 where there is a null in t1..."

Is that right?

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] looking up records with terms in a table

[Rob Richardson]

I think you need wildcards:

SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE %T2.Terms%

RobR, not guaranteeing correct syntax

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Gert Van Assche
Sent: Tuesday, March 26, 2013 12:53 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] looking up records with terms in a table

Clemens, doesn't seem to work...

The terms are just a part of the sentence, not a full match.
Your query does find full matches.

thanks

gert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Mapping Sqlite INTEGER field to C# Nullable

[Rob Richardson]

Seems like a strange thing for VS Designer to do.  NULL is not 0.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Quanren Xiong
Sent: Thursday, January 03, 2013 12:30 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Mapping Sqlite INTEGER field to C# Nullable

Hi Joe,

Just curious. Do you know how the Visual Studio Designer works?
In designer, the Null value is converted to 0. 
I would like to something similar. 

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple SQLite-based spreadsheet?

[Rob Richardson]

There's several GUI-based SQLite tools available now.  Maestro was already 
mentioned.  My favorite is SQLiteSpy because it's fast, even though you can't 
insert or update data in its grid view (you have to write insert or update 
queries to do that, which I admit is a pain, but most of the time I'm just 
looking at data).  There are others.  What would this new tool do that those 
don't?

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] memory leak in transactions

[Rob Richardson]

Do you have your inserts wrapped in a single transaction?  It used to be that I 
wasn't worrying about transactions in my projects, but I noticed things were 
very slow.  I realized that it was creating and committing one transaction for 
each insert I was doing.  When I wrapped all inserts into a single transaction, 
speed jumped dramatically.

But, of course, this doesn't address your memory issue.  Unless the increased 
memory is somewhere in the transaction management logic.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of rui
Sent: Thursday, November 15, 2012 12:21 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] memory leak in transactions

Hi,

I am seeing explosive memory growth when i am using transactions using 
System.Data.SQLite.

I see this when the transactions which were before taking fraction of second, 
taking 15 sec for 50 row insert into a table.

I have tried to use sqlite3_db_release_memory but that only frees upto 2mb 
after every transaction.

After some time say half an hour - i do see memory usage drop but that's not 
acceptable.

All the object are properly disposed from trasnsaction to command etc.

The SQLiteConnection is kept open for the life time of the session, which could 
span hours.

I would really appreciate if somebody can help me in getting the reason for 
such excessive memory usage, i have seen working set(memory)go up from 70 mb to 
400 mb in 1 minute where three transactions only doing 50 insert in a table.

Regards,
Raj
___
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] VC++ and SQLite

[Rob Richardson]

I always turn pre-compiled headers off for every VC++ project.  In my opinion, 
they are artifacts from a time when processors were a few hundred times slower 
than they are now.  The benefit in time saved now is far less than the 
confusion they cause when something goes wrong.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Adam DeVita
Sent: Monday, November 12, 2012 10:49 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] VC++ and SQLite

It isn't VS2010 specific. Even going back to VS6 writing your own C++ wrapper 
and including the.c file you had to tell it to not use precompiled headers for 
that file. (Both Debug and Release builds)

You should tell VS that this file will not ever be using precompiled headers.

On VS2012 Professional Edition one can:
Right click on the file within VS10, select Properties.
Open the C/C++ tree.
Select Precompiled Headers.
Set Precompiled Header to Not Using Precompiled Headers.


Adam

On Mon, Nov 12, 2012 at 10:17 AM, John Drescher  wrote:
>>> I know this question is not a SQLite question, but I am hoping that 
>>> someone here has had a similar experience and/or can point me to the 
>>> right place to ask this question.
>>>
>>> After years or using Code::Blocks and Dev-Cpp, I have recently 
>>> installed Visual Studio 10 Express; it is the first time I am using 
>>> it, in my Windows
>>> 7 machine.
>>>
>>> I have written, with the help of this mailing list a wrapper class 
>>> for the latest SQLite3 library using C::B as my development 
>>> platform, now that I want to switch to VS10, there were a lot of gcc 
>>> specific code that I had to repair and after clearing all the C++ 
>>> discrepancies between MinGW's g++ and MS's VC++ I have been left with this 
>>> error message:
>>>
>>> fatal error C1853: 'Debug\sql.pch' precompiled header file is from a 
>>> previous version of the compiler, or the precompiled header is C++ 
>>> and you are using it from C (or vice versa
>>>
>>>
>>>
>>> Does anyone know how to resolve this issue or perhaps a VS10 
>>> specific
>
> You can like the other poster said disable PCH in visual studio or 
> just delete all the PCH files and have VS rebuild them. The second is 
> what I do in Visual Studio retail versions when I get this error.
>
> John
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



--
VerifEye Technologies Inc.
905-948-0015x245
151 Whitehall Dr, Unit 2
Markham ON, L3R 9T1
Canada
___
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] newest 3 entries

[Rob Richardson]

Select id, eventdate, eventtype,  FROM eventlog WHERE eventtype in
('special')  order by eventdate desc limit 3

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of deltagam...@gmx.net
Sent: Thursday, November 08, 2012 3:10 PM
To: sqlite-users
Subject: [sqlite] newest 3 entries

I have a select statement like:
"Select id, eventdate, eventtype,  FROM eventlog WHERE eventtype in
('special')  "

Now I like to receive only the newest 3 entries in the table eventlog where the 
eventtype is 'special'


___
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] c++ - Tell SQLite3, read the subsequent rows

[Rob Richardson]

Igor, you didn't read ArbolOne's incomprehensible code correctly.  Here it is 
with reasonable formatting:



void mySQLite3::setStmt(const Glib::ustring& s)

{

SQLStatement = s;

if (mystmt == NULL)

{

rc = sqlite3_prepare_v2(db, 
SQLStatement.c_str(), -1, ,NULL);

}

if(rc != SQLITE_OK)

{

...

}

rc = sqlite3_step(mystmt);

}



const int mySQLite3::read_int(int pos)

{

   if(rc == SQLITE_ROW )

   {

   apint = sqlite3_column_int(mystmt,pos);

   }

   return apint;

}



const Glib::ustring& mySQLite3::read_str(const int pos)

{

   if(rc == SQLITE_ROW )

   {

   apstr = (const char*)sqlite3_column_text(mystmt,pos);

   }

   return apstr;

}





You don't show where these functions are being called, but you are doing one of 
two things.  You are calling setStmt inside a loop, in which case you are 
rebuilding the statement every time and then reading the first row each time 
(completely ignoring Igor's message about opening a book, reading the first 
page, closing the book, repeating that entire sequence, and wondering why you 
never finish the book), or you are calling setStmt once, in which case you open 
the book, read the first page, close the book, and wonder why you never finish 
the book.



RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reducing size

[Rob Richardson]

Why are you concerned about the size of sqlite3.o? 

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - WHERE clause - update

[Rob Richardson]

No, it is absolutely not a trick question.  Just tell us the number that 
sqlite3_step() returned.

It is a question about what the actual value was that was returned by the call 
to sqlite3_step().   The function returns a number that indicates whether it 
worked or not.  There are values defined in sqlite3.h (or whatever the header 
file is) that tell you what the numbers mean.  One number (I don't know off 
hand which) is labeled SQLITE_ROW, which you are using.  Another is 
SQLITE_DONE.  Another is SQLITE_MISUSE.  There are several of them.  Your 
program should be able to handle all of them.  Your program should also be able 
to tell you which one it received.  

RobR, trying hard to be tactful

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Arbol One
Sent: Thursday, September 06, 2012 9:19 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] C++ - WHERE clause - update

Is this a tricky question?
int sqlite3_step(sqlite3_stmt*);

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Richardson
Sent: Thursday, September 06, 2012 12:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] C++ - WHERE clause - update

What is the value returned from sqlite3_step()?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Arbol One
Sent: Thursday, September 06, 2012 12:14 PM
To: 'General Discussion of SQLite Database'
Subject: [sqlite] C++ - WHERE clause - update

rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
The code, in this case, does not process this statement!!?? <<--
apstr = (const char*)sqlite3_column_text(mystmt,pos);
std::cout << apstr << std::endl;  //<<-- this is not executed }
Table:
id | tile | fname | mname | lname |
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] C++ - WHERE clause - update

[Rob Richardson]

What is the value returned from sqlite3_step()?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Arbol One
Sent: Thursday, September 06, 2012 12:14 PM
To: 'General Discussion of SQLite Database'
Subject: [sqlite] C++ - WHERE clause - update

rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
The code, in this case, does not process this statement!!?? <<--
apstr = (const char*)sqlite3_column_text(mystmt,pos);
std::cout << apstr << std::endl;  //<<-- this is not executed 
}
Table:
id | tile | fname | mname | lname |
--- 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Rob Richardson]

Many thanks to all of you who took the time to correct my misunderstanding of 
basic SQL.

I ran a little test in PostgreSQL (which is the quickest thing I have to play 
with), and of course, you are all correct and the query does work as designed.  
I was trying to figure out how to think about it, and I think I understand now. 
  In general, the database engine will walk through the target table,  evaluate 
the right side of the equal sign, and assign the result to the target column 
specified in the left side.  Simple.  I don't know why it didn't seem so simple 
yesterday.

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] classic update join question

[Rob Richardson]

Well, I think you want a where clause on your main UPDATE query.  What you 
wrote will set the frequency of every record in the alpha table to the value 
from the beta table, for every record in the beta table that matches an alpha 
record.  (It's late, I'm tired and that's incoherent.  I hope you followed it.) 
 If you have 1000 records in the two tables that match each other, every record 
in the alpha table will be updated 1000 times.

Or am I merely demonstrating my ignorance?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of E. Timothy Uy
Sent: Wednesday, September 05, 2012 4:11 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] classic update join question

Dear Igor,

Are you saying that

UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term = 
alpha.term)

is just as efficient as it gets and equivalent to an update using join (in 
other dbs)? That would helpful to know. I do kind of imagine some kind of black 
magic... ;)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] why no such column in sqlite3 ?

[Rob Richardson]

Put single quotes around Testitem:

  sprintf( sqlquery, "INSERT INTO tblTest ( CINDEX, CDATE, CDESCR, CAMOUNT 
) VALUES ( 5, 2012-08-29, 'Testitem', 300 )");

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] to table update

[Rob Richardson]

Are you certain there exist rows in tb1 and tb2 that satisfy the condition?  
What happens when you try?  Is any error message or number returned?  Can you 
run the same query inside an SQLite management tool like SQLite Spy?  Does it 
work there?  Please provide us ALL of the relevant information when you post.

But in this case, it's fairly easy.  (Easy enough for me to get it right?  
We'll see.)  The subquery is returning more than one value.  That's illegal.  
And I hope you mean to update every single row in tb1, because that's what your 
query will do.

And your main query references tb2 without defining it.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of yanhong.ye
Sent: Thursday, August 23, 2012 7:32 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] to table update

update tb1 set col1=(select col1 from tb2 ) where tb1.co2=tb2.co2;

it couldn't work

___
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] Select rows where a column is not unique

[Rob Richardson]

Igor,

Which of those would be fastest?  Or don't you have enough information to tell?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Monday, August 06, 2012 9:14 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Select rows where a column is not unique

Tilsley, Jerry M.  wrote:
> I'm sure this is a pretty lame question, but my thinking hat is 
> malfunctioning this morning.  How can I select all rows from a table where a 
> specific column is NOT UNIQUE?

select * from MyTable where SpecificColumn in (
  select SpecificColumn from MyTable
  group by SpecificColumn
  having count(*) > 1);

-- or

select * from MyTable t1 where exists (
  select 1 from MyTable t2
  where t2.SpecificColumn = t1.SpecificColumn and t2.rowid != t1.rowid);

-- or

select * from MyTable t1 where 1 < (
  select count(*) from MyTable t2
  where t2.SpecificColumn = t1.SpecificColumn);

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] AUTO_INCREMENT error

[Rob Richardson]

Don't you have to specify a column type for test_1?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Brandon Pimenta
Sent: Friday, August 03, 2012 9:09 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] AUTO_INCREMENT error

I cannot use AUTO_INCREMENT. Here's my query:

CREATE TABLE test (
test_1 NOT NULL AUTO_INCREMENT
);

Running this query gives me "SQL error: near "AUTO_INCREMENT": syntax error". 
What does this mean?

SQLite 3.6.12
___
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] sqlite3_stat problem (YAN HONG YE)

[Rob Richardson]

OK for now, but for how long?  I still think that 140 is going to come back and 
bite you in the rear end sometime when you're not expecting it.  As I 
understand it, you have a short collection of bytes that is the result of 
encrypting a short string.  You have much less than 140 bytes of data to store. 
 You are storing four bytes (or whatever) of encrypted data, followed by 136 
bytes of random crud.  When it finally comes time to extract the encrypted data 
from the database and unencrypt it, your unencryption algorithm isn't going to 
know what to with those extra 136 bytes, and you'll get junk.

And as I said in my last message, NULL is not a valid choice for the fifth 
argument.  The fact that it works does not change the fact that it is not 
valid.  Please choose an appropriate value as listed in the documentation for 
that parameter.

Always write code as though the next person to look at it just graduated from 
college and never wrote a line of professional code in his life.  For one 
thing, you may come back to it in two years and ask yourself, "Why the heck did 
I do THAT?"

RobR


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of YAN HONG YE
Sent: Thursday, August 02, 2012 3:20 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3_stat problem (YAN HONG YE)

sqlite3_bind_text(stat,1,uu2,140,NULL); 
change to 
sqlite3_bind_blob(stat,1,uu2,140,NULL);
it's ok! thank you!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_STATIC and temporary data

[Rob Richardson]

Is "acceptable" good enough?  I admit I haven't played with this function 
(actually, I never heard of it until today), but from what I read in the 
documentation, the case described looked dangerous to me.  SQLITE_STATIC seemed 
to me to imply that the contents of the memory used by the sqlite statement 
would never change over the life of the statement.

But please keep in mind that in this case (as in many other cases), my opinion 
likely to be worth exactly what you have paid for it.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jay A. Kreibich
Sent: Wednesday, August 01, 2012 12:16 PM
To: General Discussion of SQLite Database
Cc: c...@iki.fi
Subject: Re: [sqlite] SQLITE_STATIC and temporary data

On Wed, Aug 01, 2012 at 08:49:19PM +1000, Yose Widjaja scratched on the wall:
> Dear Friends,
> 
> So SQLITE_STATIC is meant to be used for data that is static. However, 
> would it still be safe when it is used with data that expires after 
> the
> sqlite3_step() function?
> 
> For example:
> 
> string hello = "hello world";
> 
> sqlite3_bind(statement, 1, hello.c_str(), hello.size(), 
> SQLITE_STATIC);
> 
> sqlite3_step(statement);
> 
> hello = "moo";
> 
> Would there be anything that can potentially go wrong? I mean, since 
> SQLITE_STATIC is meant to imply static stuff, would sqlite cache the 
> value in such a way that subsequence SELECT_ statements actually use 
> this static value that was passed in through the bind function?

  It would be better to keep the value valid until _reset() or
  _finalize() is called, but, yes... this use of SQLITE_STATIC is
  acceptable (and somewhat common).

   -j


--
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,  but showing 
it to the wrong people has the tendency to make them  feel uncomfortable." -- 
Angela Johnson ___
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] sqlite3_stat problem

[Rob Richardson]

Return values are your friends.  Use them.  Store the error code from every 
sqlite function call, and if the error code is not SQLITE_OK (NOTE:  Check that 
that is the correct name.), then display what the error code is.  In 
particular, what is the return value of your sqlite3_bind_text() function call?

If the documentation of a function states that a parameter requires a named 
special value, use that name.  The fifth argument should be a destructor 
function pointer, SQLITE_STATIC or SQLITE_TRANSIENT.  Read the documentation to 
understand what each one means.

Learn to cringe every time you put a constant value into your code.  People 
often call hard-coded constants "magic numbers" because they apparently showed 
up magically, since there's no other explanation of where they came from.  
Where did "140" come from?  Why do you need it?  The parameter requires the 
length of a string.  So use strlen() (again, check the correctness.  It's been 
so long since I've used these functions, the name could be wrong) to calculate 
the actual length of the string.  The documentation states that if the fourth 
argument is not negative, it is the byte offset where the null terminator 
should be, and any null terminators before that are included in the bound 
string, and if any null terminators are included in the bound string, behavior 
is undefined.  That's exactly the situation you have.  

And when you use strlen() or whatever you use to calculate the length of the 
string, be careful to take into account the difference between bytes and 
characters.  The sqlite3_bind_text() call requires bytes, but your characters 
may be one or two bytes.

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] no such function problem

[Rob Richardson]

This is a C question, not an SQLite question.  But I'll answer it anyway.

You included the function call in your quoted string, so your compiler thinks 
it's just random text.  You need:

sprint(sql, "insert into student select 3, %s, 22;", ldll("bb"));

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] MIN() for a timedelta?

[Rob Richardson]

See below.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Thursday, July 26, 2012 8:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] MIN() for a timedelta?


On 27 Jul 2012, at 12:04am, C M  wrote:

> On Thu, Jul 26, 2012 at 6:45 PM, Nico Williams  wrote:
>> 
>> 
>> Just use CASE to add the missing zero as necessary, something like this:
>> 
>> SELECT strftime('%s', (SELECT CASE WHEN '9:12:32' LIKE '0%' THEN 
>> '9:12:32' ELSE '0' || '9:12:32' END));
>> 
>> Replace '9:12:32' there with whatever expression, probably a column name.
> 
> Thanks but I can't figure out how to use that to get the MIN() 
> timedelta.  For example, if I try this (the table is called 
> Durations...the column is duration):
> 
> SELECT  MIN(CASE WHEN duration LIKE '0%' THEN duration ELSE '0' || 
> duration END) FROM Durations WHERE duration != ''
> 
> it returns:
> 01:00:00:00
> 
> which is definitely not the min timedelta in the table.

As near as I can tell, your query should have worked.  What is the minimum 
timedelta in the table?  Or, if the table's too big to answer that, can you 
please give us an example a timedelta that should have been smaller than this?

Also, can you get rid of the MIN and add ORDER BY Durations and see what data 
your CASE statement generates?

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syatem.Data.SQLite Exception/Error handling viaTry...Catch

[Rob Richardson]

I have some vague memory that exception handling is screwy inside constructors, 
and it's better to create a naked object and then fill it in in an Init() 
method.  But I have no idea where I read that, if it's correct, or even which 
language it applies to.

Good luck!

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] cann't work

[Rob Richardson]

Does SQLite care about the use of double quotes instead of single quotes?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Davies
Sent: Wednesday, July 11, 2012 5:10 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] cann't work

On 11 July 2012 10:00, YAN HONG YE  wrote:
> two same structure tables, when use this sql:
> insert into table2  values(select * from table1 where filename like "%55");
>
> but show error: sqlite error 1 - near "select": syntax error

insert into table2 select * from table1 where filename like "%55";

http://www.sqlite.org/lang_insert.html

Regards,
Simon
___
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] ADO.NET Provider, targeting any cpu

[Rob Richardson]

-Original Message-

The System.Data.SQLite managed-only assembly, when the native library 
pre-loading code is enabled (which it is by default starting with release 
1.0.80.0), will now attempt to detect the processor architecture of the process 
it is being loaded into and then it will attempt to load the native interop 
library from an appropriately named subdirectory (e.g.
"x86", "x64", etc). In order to take advantage of this feature, the 
System.Data.SQLite managed-only assembly should be deployed with the other 
managed binaries in your application and the native interop libraries should be 
deployed in platform-specific sub-directories underneath that directory, as 
follows:

\YourApp.exe
\System.Data.SQLite.dll (managed-only assembly)
\x86\SQLite.Interop.dll (x86 native-only interop library)
\x64\SQLite.Interop.dll (x64 native-only interop library)

If this feature does not work properly in your environment, it can be disabled 
by setting the "No_PreLoadSQLite" environment variable prior to loading and/or 
using the System.Data.SQLite assembly.

--
Joe Mistachkin
= end of original message

This is the first I have heard of this feature or requirement or whatever this 
is.  This statement seems to be saying that the "System.Data.SQLite 
managed-only assembly" is different from the "System.Data.SQLite assembly".  Is 
that true?  When I download a new version of the System.Data.SQLite 
installation package, how will I tell the difference between the two?

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How do you access a specific row of a resultset?

[Rob Richardson]

The other thing you are not thinking about is that you never know the order of 
data in a set.  You may think that you want the 5th record that was ever 
inserted into the table, but you have no guarantee that a select statement will 
return records in the order in which they were inserted.  The 5th oldest record 
could come up first in the result set, or last, or anywhere else.

So, you either have to walk through the set from the beginning, checking every 
record to see if it's the one you want, or you're going to have to build a more 
precise select statement.

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Details on New Features

[Rob Richardson]

Gabor Grothendieck mentioned a new feature of SQLite in 3.7.11:
Queries of the form: "SELECT max(x), y FROM table" returns the value of 
y on the same row that contains the maximum x value.

Is that standard SQL behavior?  I'd have expected that to return one row for 
every row in the table.  To get the behavior described above, I'd use "SELECT 
x, y FROM table WHERE x = (SELECT max(x) FROM table)".

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] auto-incrementing integer in composite primary key

[Rob Richardson]

If you deleted  record on New Year's Day, you want a query for data on New 
Year's Eve to find the record but you don't want a query for data on January 
2nd to find it.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Pavel Ivanov
Sent: Monday, April 16, 2012 4:43 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] auto-incrementing integer in composite primary key

> 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than 
> `created_on <= :provided_date`?

What if there are several versions created before your provided_date?
Not all queries will allow to add `order by created_on desc limit 1`.


Pavel


On Mon, Apr 16, 2012 at 4:37 PM, Puneet Kishor  wrote:
>
> On Apr 16, 2012, at 2:45 PM, Petite Abeille wrote:
>
>>
>> On Apr 16, 2012, at 8:29 PM, Puneet Kishor wrote:
>>
>>> I am trying to create a data versioning system so that a query done at a 
>>> particular time can be reproduced identically as to the original query even 
>>> if the data have been modified in the interim time.
>>
>> My 2¢ worth.
>>
>> (1) Proper historization/versioning is not a piece of cake
>> (2) Most constraint mechanisms do not help with it
>>
>> Regarding (1), I would suggest a relatively straightforward setup where all 
>> you versioned tables include a date range specifying the point in time a 
>> record is valid. This is more conveniently expressed as two fields, along 
>> the lines of valid_from and valid_to, so you can then query it with a 
>> between clause.
>>
>> Each DML operations need to maintain that date range so it stays logically 
>> consistent (e.g. no overlaps, not gaps, no delete, etc).
>>
>> At the end of the day, you should be able to query your data for any point 
>> in time consistently:
>>
>> select  *
>> from    foo
>>
>> join    bar
>> on      bar.bar_key = foo.bar_key
>>
>> where   foo.foo_key = 1
>> and     julianday( ... ) between foo.valid_from and foo.valid_to and     
>> julianday( ... ) between bar.valid_from and bar.valid_to
>>
>>
>> Regarding (2), I would suggest to forgo traditional integrity constraint 
>> mechanisms (primary, unique, referential, etc) as they simply don't play 
>> well with (1). For example, one cannot express a meaningful, and useful, 
>> primary, nor unique key on versioned data. Ditto for referential 
>> constraints. Which also means you have to re-implement  all of the above by 
>> yourself. Which is a pain and rather error prone.
>>
>
>
> Thanks for your wise words. I am not at all under any illusion that 
> this is going to be easy, but it is worthy of an honest try. Two 
> reactions --
>
> 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than 
> `created_on <= :provided_date`? The latter requires storing only a single 
> date value for every row. Although, after some studying of the Pg timetravel 
> docs, seems like they too use a start and stop date.
>
> 2. Yes, most constraint mechanisms might be useless or difficult to 
> implement, but I do need a PK.
>
>
> --
> Puneet Kishor
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Integer Truncation Issue

[Rob Richardson]

Disclaimer:  I am not in any way an expert!

As I understand things, in SQLite, any value can be stored in any column, 
regardless of type.  But in C#, the System.Data.SQLite library isn't that 
flexible.  If the table definition says the column contains integers, then all 
data in the table is processed as integers.

I'm sure others will give more complete and more accurate responses.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Jay Howard
Sent: Friday, April 06, 2012 9:30 AM
To: 'sqlite-users@sqlite.org'
Subject: [sqlite] Integer Truncation Issue

I have a strange issue,



I have a table from a 3rd part piece fo software that I am trying to debug...



The schema is below



CREATE TABLE [energy_history] (

[addr] INT  NULL,

[year] INT  NULL,

[month] INT  NULL,

[day] INT  NULL,

[hour] INT  NULL,

[min] INT  NULL,

[ch1_amps_avg] INT  NULL,

[ch1_kw_avg] INT  NULL,

[ghg] INT  NULL,

[cost] INT  NULL,

[ch1_amps_min] INT  NULL,

[ch1_amps_max] INT  NULL,

[ch1_kw_min] INT  NULL,

[ch1_kw_max] INT  NULL,

[dt] DATETIME  NULL,

PRIMARY KEY ([addr],[year],[month],[day],[hour],[min])

)



When I use the sqllite data browser tools to view the data  is stored as 
numeric values eg .3092 (the values I am looking for) When I get the values out 
of the db using c# the dataset is created with a datatype of int32 so all the 
data truncates.

Am I missing something?



Hope someone can help



Thanks,

Jay

___
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] table names

[Rob Richardson]

In SQLite Expert, I created table "12-345" with no problem, but I noted that 
when the new table was displayed, its name was wrapped in square brackets: 
"[12-345]".  Perhaps you could wrap numeric names in brackets similarly.

Good luck!

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Visual Studio 2008 Express and sqlite3.c

[Rob Richardson]

This leads me to agree with the person who suggested that a #define someplace 
is telling the precompiler to change "not" to something else.  There is a 
compiler setting that will generate a file with an extension of ".i" that is 
the output of the precompiler.  You could try doing that and seeing what that 
line is in there.  Unfortunately, I don't remember what that compiler setting 
is.

I also like the idea of compiling sqlite3.c into a separate library.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Marco Bambini
Sent: Friday, March 30, 2012 8:33 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Visual Studio 2008 Express and sqlite3.c

Please note that if I use the same compiler to compile sqlite3.c as a single c 
file than everything is compiled without errors.
Errors occurs only when sqlite3.c is part of a c++ project.

I also manually set sqlite3.c to be compiled as C file instead of Default 
without any luck.
Seems like something confuse the Visual C++ compiler.

Any idea?
--
Marco Bambini
http://www.sqlabs.com








On Mar 30, 2012, at 1:08 PM, Nick Shaw wrote:

> -Original Message-
>> I am trying to compile the latest sqlite 3.7.11 amalgamation C file within a 
>> Visual Studio 2008 Express C++ project.
>> sqlite3.c is correctly set as to be compiled as C code but I am unable to 
>> find out a way to fix some compilation errors:
>> 
>> Compiling...
>> sqlite3.c
>> ..\Sources\sqlite3.c(107831) : error C2143: syntax error : missing ':' 
>> before '!'
>> ..\Sources\sqlite3.c(107831) : error C2059: syntax error : ';'
>> ..\Sources\sqlite3.c(107832) : error C2059: syntax error : '}'
>> ..\Sources\sqlite3.c(107994) : error C2079: 'yy318' uses undefined struct 
>> 'LikeOp'
>> ..\Sources\sqlite3.c(110530) : error C2224: left of '.eOperator' must have 
>> struct/union type
>> ..\Sources\sqlite3.c(110530) : error C2059: syntax error : '!'
>> ..\Sources\sqlite3.c(110534) : error C2224: left of '.eOperator' must have 
>> struct/union type
>> ..\Sources\sqlite3.c(110534) : error C2059: syntax error : '!'
>> ..\Sources\sqlite3.c(110538) : error C2275: 'ExprList' : illegal use of this 
>> type as an expression
>>..\Sources\sqlite3.c(8133) : see declaration of 'ExprList'
>> ..\Sources\sqlite3.c(110541) : error C2224: left of '.eOperator' must have 
>> struct/union type
>> ..\Sources\sqlite3.c(110541) : error C2198: 'sqlite3ExprFunction' : too few 
>> arguments for call
>> ..\Sources\sqlite3.c(110542) : error C2059: syntax error : '!'
>> ..\Sources\sqlite3.c(110554) : error C2224: left of '.eOperator' must have 
>> struct/union type
>> ..\Sources\sqlite3.c(110554) : error C2198: 'sqlite3ExprFunction' : too few 
>> arguments for call
>> ..\Sources\sqlite3.c(110555) : error C2059: syntax error : '!'
> 
> Looks like something's not right with the definition of the LikeOp struct in 
> your copy of the file.  I've got the exact same amalgamation in my VS2008 
> project and it builds fine.  Can you check what you have for the LikeOp 
> structure definition?  Mine looks like this, and starts on line 107829:
> 
> struct LikeOp {
>  Token eOperator;  /* "like" or "glob" or "regexp" */
>  int not; /* True if the NOT keyword is present */
> };
> 
> 
> Thanks,
> Nick.
> -- 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite error message

[Rob Richardson]

You may want to add "FailIfMissing=true" to your connection string.  It always 
surprises me when I successfully connect to a database and then an attempt to 
read from a table that I know durn well is there fails.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Simon Slavin
Sent: Friday, March 23, 2012 2:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sqlite error message


On 23 Mar 2012, at 5:46pm, Waltênio de Bessa Mendes  
wrote:

> I got a error message "could not prepare statement (1 no such table:
> LivroDaBiblia) (Code 5)"

Make sure you're opening the correct database file.  Try specifying the full 
path to the file.

Simon.
___
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] No error message generated by sqlite_exec()

[Rob Richardson]

Thank you, Dr. Hipp.  I understand.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, March 13, 2012 7:07 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] No error message generated by sqlite_exec()

SQLite3_exec() should always generate an error message, but with two
exceptions:

(1) When malloc() fails trying to allocate space to hold the error message
(2) In an SQLITE_MISUSE situation because the malloc() subsystem might not even 
be initialized.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] No error message generated by sqlite_exec()

[Rob Richardson]

Thank you very much for your quick reply, Dr. Hipp, and I will fix my code.  
Probably I was trying to execute a statement before opening the database.  

But the question remains:  Should sqlite_exec() always generate an error 
message, or should I be prepared to handle the case in which it does not?  
(Which I've already done: not hard and cleaner code anyway.)

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Tuesday, March 13, 2012 6:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] No error message generated by sqlite_exec()

On Tue, Mar 13, 2012 at 6:43 PM, Rob Richardson <rdrichard...@rad-con.com>wrote:

> Hello!
>
> I am sending a badly formed query string to sqlite_exec().  It is 
> giving me error 21 (misuse of library), but it is not giving me an error 
> message.
>  The value of the pointer sent as the fifth argument of sqlite_exec() 
> is not changed.  Is this expected behavior?  Is there something I have 
> to do to tell sqlite that I want the error message to be generated?
>

If you are getting back SQLITE_MISUSE, that probably means you are calling
sqlite3_exec() with a database connection that has previously been closed.
SQLite tries to catch that situation and report the SQLITE_MISUSE error, but 
there are no guarantees that doing so will work.  Depending on how
malloc() is implemented on your system, it might segfault instead.

So:  Don't call sqlite3_exec() with a closed database connection.

General rule:  You should never do anything to SQLite that causes it to return 
SQLITE_MISUSE.  If you do, there is an error in your code.  Fix your code.


>
> Thank you very much.
>
> RobR
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



--
D. Richard Hipp
d...@sqlite.org
___
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] No error message generated by sqlite_exec()

[Rob Richardson]

Hello!

I am sending a badly formed query string to sqlite_exec().  It is giving me 
error 21 (misuse of library), but it is not giving me an error message.  The 
value of the pointer sent as the fifth argument of sqlite_exec() is not 
changed.  Is this expected behavior?  Is there something I have to do to tell 
sqlite that I want the error message to be generated?

Thank you very much.

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best LINQ provider for SQLite?

[Rob Richardson]

Greetings!

What is the best LINQ provider for SQLite?

I'm sorry for posting a question that must have been asked several times in the 
past, but the archives of this group do not appear to be searchable.  If there 
is a way to search the archives, could someone please show me?

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What do people think of SQLite Root?

[Rob Richardson]

We're running 1.5.5.  We don't keep up with updates very well.  We're lucky 
we're not using MS Visual Studio 6 any more.  We're up to 2008!  Woohoo!

I can't get to www.yunqa.de now.  I tried in IE8 and FireFox.

RobR


-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Ralf Junker
Sent: Monday, March 05, 2012 10:37 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] What do people think of SQLite Root?

On 05.03.2012 16:11, Rob Richardson wrote:

> With the latest version, I think you can.

SQLiteSpy grid editing has been around for years, it is available since version 
1.6.0., 7 Feb 2007.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] What do people think of SQLite Root?

[Rob Richardson]

With the latest version, I think you can.  But I haven't been able to get the 
latest version.  The link you provided does not work for me.  Do you know any 
site other than yunqa.de where the latest SQLite can be downloaded?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Ralf Junker
Sent: Monday, March 05, 2012 9:48 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] What do people think of SQLite Root?

On 05.03.2012 14:50, Rob Richardson wrote:

> I keep hoping to find something better, because SQLiteSpy does not 
> offer the ability to edit a table inside a grid.

Not quite true. SQLiteSpy can edit table cells inside a grid.

Double-click the table-icon in the schema treeview to display the table.
When you then select a table cell in the grid and press F2, the edit window 
will pop up.

See feature item 2 and the screenshot at 
http://yunqa.de/delphi/doku.php/products/sqlitespy/index

Ralf
___
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] What do people think of SQLite Root?

[Rob Richardson]

Our company typically uses SQLite Spy for managing SQLite databases.  I keep 
hoping to find something better, because SQLite Spy does not offer the ability 
to edit a table inside a grid.  The only way to update data is to use an SQL 
UPDATE statement.  But nothing else offers the speed of displaying data for a 
decent-sized table.  The table we most often have to show contains three 
columns, and data taken once a minute and saved for thirty days.

I've seen a couple of announcements recently about SQLite Root, and it looks 
like it may be worth trying next time I get a little time.  But in the 
meantime, I'm wondering what others think.  Is it a good product?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Fabio Spadaro
Sent: Monday, March 05, 2012 3:51 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Sqlite Root also available for Linux

Announcement of the release Sqlite Root   now 
available for Linux.
Any feedback is appreciated.

--
Fabio Spadaro
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Views and Performance

[Rob Richardson]

What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not specified? 
 

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Duquette, William H (318K)
Sent: Friday, March 02, 2012 11:23 AM
To: Discussion of SQLite Database
Subject: [sqlite] Views and Performance

Howdy!

Suppose I have two related tables, t1 and t2, and I write a view like this:

CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column);

If I am querying data just from t1, is there a performance penalty for using 
myview in the query?  Or will the query planner generate approximately the same 
bytecode as it would if I'd simply queried t1?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] accessing multiple databases

[Rob Richardson]

IIRC, there's a connection string option that will choose between creating an 
empty database and throwing an exception if you try opening a database that 
doesn't exist.  Perhaps if that option is set to throw an exception, then the 
ATTACH command would fail.  Or not.  

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLiteDataAdaptor Missing

[Rob Richardson]

In another library, I had to specify "x86" processor because it wasn't designed 
for 64-bit machines.  Might you need to do that?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Electric Eddy
Sent: Monday, February 20, 2012 10:26 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLiteDataAdaptor Missing

I performed the following steps:

1) Checked out the latest local copy using the command 'fossil update'
2) I opened a Visual Studio command prompt, changed into the tools installer 
folder and executed the command MSBuild Installer.2008.csproj
3) I changed into the root/bin/2008/Debug/bin folder and executed the following 
one line command:

Installer.exe -install true -wow64 true -installFlags All -tracePriority Lowest 
-verbose true -noCompact true -noNetFx40 true -noVs2008 true
-noVs2010 true -whatIf false -confirm true

This then popped up an error message dialogue box stating:

Invalid core file image runtime version.




On Mon, Feb 20, 2012 at 1:59 PM, Joe Mistachkin wrote:

>
> Electric Eddy wrote:
> >
> > Maybe not going as far as including updates in the installer for 
> > support for 2005 but at least a manual process for getting it 
> > working in 2005
> would
> > be very helpful.
> >
>
> I've just checked-in preliminary support to the design-time component 
> installer for Visual Studio 2005; however, I currently have no way to 
> test it.
>
> If you wish to use and/or test it, here is the command line you will 
> need after building "Installer.exe" (which can be built using MSBuild 
> on the command
> line):
>
>Installer.exe -install true -wow64 true -installFlags All
>-tracePriority Lowest -verbose true -noCompact true -noNetFx40 true
>-noVs2008 true -noVs2010 true -whatIf false -confirm true
>
> All of that needs to be on one line.
>
> --
> 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] A faster way to insert into a keyless table?

[Rob Richardson]

Many thanks for your response.  That solved the problem.  Now I update data for 
all 124 bases in about eight seconds.

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Petite Abeille
Sent: Tuesday, February 14, 2012 2:18 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] A faster way to insert into a keyless table?


On Feb 14, 2012, at 8:01 PM, Rob Richardson wrote:

>  What would be the best way to speed this up?

wrap all your inserts in one transaction. commit at the end.

___
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] A faster way to insert into a keyless table?

[Rob Richardson]

Greetings!

I am working on updating an application that has been around for years, 
originally written by someone who knew SQLite exists but had very little idea 
of how to use it.  The application monitors OPC information for 124 bases, with 
7-10 tags per base.  (Don't worry about what a base is.)  Each base has a 
separate SQLite database file associated with it.  Every minute, I have to 
write the latest tag value for each tag into the correct database.  Here is the 
create statement for the table into which I am writing data:
  CREATE TABLE trend_data( tag_key integer, value integer, value_timestamp 
datetime );
There is no primary key specified for this table.

The application is written in C#, and uses a .Net provider for SQLite.  

The code to insert tag data is very crude.  I build an INSERT query as a 
string, and then I call the database object's Execute() method.  

The average time to update a base is half a second.  Since I've got 124 bases, 
it takes almost exactly a minute to update each one.  But I would like the 
entire update process, for all bases, to be repeated every minute.  Therefore, 
I'd like to get the update time per base down to at most a quarter second.  
What would be the best way to speed this up?

Thanks very much!

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Able to differentiate between No query and empty results?

[Rob Richardson]

-Original Message

Also, Stephan is quite right: it's not the columns but the values which have 
datatypes, and if you're looking at some random SQLite database that wasn't 
carefully created the value in r1c1 may be an integer but the value in r2c1 may 
be text.

Simon.
--
A fact that has driven me up nearby walls on several occasions, especially when 
working with date/times.

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database locked in multi process scenario

[Rob Richardson]

Isn't it almost a requirement of a transaction that only one be open at a time 
in a database?  If there could be more than one transaction, then transaction 1 
might start, transaction 2 starts, transaction 1 fails, transaction 1 is rolled 
back, and what happens to transaction 2?  One could imagine one transaction 
working an table 1 and a second working on table 2 which has no connection, but 
then someone comes along and adds a trigger to table 1 that updates table 2.  
Now we have two simultaneous independent transactions working on table 2.  

RobR, who has been struggling for months with a program that might open the 
same SQLite file at the same time from two points in the program, and who has 
realized that the program is not well designed.

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Sreekumar TP
Sent: Friday, February 10, 2012 8:52 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Database locked in multi process scenario

In the real code, there is no sleep/wait or pause. It so happens that the write 
of the app2 is scheduled in between.

What you are suggesting is that at any point of time only one process can have 
a transaction open in a database?


Sreekumar
On Feb 10, 2012 7:12 PM, "Simon Slavin"  wrote:

>
> On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote:
>
> > well, the 'wait' is a simulation of what happens in the real code.
> >
> > The error is fatal to the application as it never ever recovers from 
> > it even though the writer has finalized and terminated.
>
> In a multi-process environment I recommend that you do not pause for 
> such a long time between the first _step() and the _reset() or 
> _finalize().  You can _bind() a statement then wait a long time to 
> execute it, but once you have done your first _step() you want to get 
> through the data and release the database for other processes.
>
> If you still have the database locked and another process tries to 
> modify it, one process or the other will have to deal with a BUSY, or 
> a LOCKED, or something like that.  In your own setup, it turns out to 
> be process 1.  But a slightly different setup would make process 2 see a BUSY 
> instead.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can I check the auto-vacuum setting on an SQLite database?

[Rob Richardson]

My deepest apologies for forgetting to change the subject line in my
last post, and thus accidentally hijacking a thread.  Here is the
message again, this time with the correct subject.

On Wed, Feb 8, 2012 at 8:44 AM, Rob Richardson <cedriccic...@gmail.com> wrote:
> Greetings!
>
> Is there a way to verify the auto-vacuum state of an SQLite database?
> I am hoping that auto-vacuum will replace the need for periodic VACUUM
> calls.  If the files are getting too big, I need to be able to verify
> the auto-vacuum setting before trying to come with some other scheme
> to prevent unbridled file growth.
>
> Thank you very much!
>
> RobR



-- 
Please do not copy or forward this message or any attachments without
my permission.  Remember, asking permission is a great way to get me
to visit your site!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How complicated can trigger statements be?

[Rob Richardson]

The example of a CREATE TRIGGER statement from the help page is:

CREATE TRIGGER update_customer_address UPDATE OF address ON customers
  BEGIN
UPDATE orders SET address = new.address WHERE customer_name = old.name;
  END;

The use of BEGIN and END to wrap the statement leads me to believe that it's 
possible to have more than one statement between them:


CREATE TRIGGER update_customer_address UPDATE OF address ON customers

  BEGIN

UPDATE orders SET address = new.address WHERE customer_name = old.name;

INSERT INTO orders_history (customer_name, address) VALUES 
(old.customer_name, old.address);

  END;


And is it possible to have a conditional statement?

CREATE TRIGGER record_big_order AFTER INSERT ON orders
  BEGIN
IF new.value > 100 THEN
  INSERT INTO big_orders (customer_name, salesman_id, value)
  VALUES (new.customer_name, new.salesman_id, new.value)
END IF;
  END;

Thank you very much.

RobR

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Major memory leak

[Rob Richardson]

I'm thinking whether this is a memory leak or not sort of depends on
your definition.  If a process is designed to remain open for long
periods of time with little activity, and it ends up taking up 1
gigabyte of memory, that looks an awful lot like a leak to me.  There
are likely to be at least three instances of this application running,
and after they all run for a month, they're likely to be consuming 5
gigabytes of memory.  This is not acceptable.  If SQLite's sorted
query is taking up 2.5 megabytes of memory every time this piece of
the application is invoked, I need to know how to ensure that that
memory is released.

Here's a brief description of the application.  My company, Rad-Con,
Inc., is a major supplier of annealing furnaces and related equipment
and software to metal processors worldwide.  The application monitors
the annealing process on a customer's site.  There could be well over
a hundred annealing bases.  The applicaton's first screen displays an
overview of all of the bases, whether they have furnaces, if the
furnaces are turned on, and so on.  A user can double-click on base to
see details.  A button on the detail screen calls up a trend display.
Trend data is stored in SQLite database files, one per base.  The
application executes the query I described to find when the last row
was written to the table, and uses that to calculate the times that
will be displayed on the graph.  Then, the application reads the
entire table and plots the data.  When the user is finished, he closes
the trend screen.  My requirement is to ensure that the amount of
memory allocated to my application before the trend screen is
displayed and after the trend screen is closed is the same.  If more
memory is allocated after it is closed, that is a leak, by my
definition.


RobR


On 3/23/08, Christian Smith <[EMAIL PROTECTED]> wrote:
> On Fri, Mar 21, 2008 at 10:41:10AM -0400, Rob Richardson wrote:
> > My SQLite library is built from the single translation unit
> > sqlite.c/sqlite.h.  That file contains the version number 3.3.17.
> >
> > I do not have valgrind, but circumstantial evidence that this is a
> > SQLite problem is strong.  When stepping through my code, I see that
> > my application's memory jumps by over 2.5 megabytes when the
> > sqlite3_step() method is called when using either the sorted query or
> > the query using max().  The unsorted query doesn't show any memory
> > jump.  Also, the difference in memory consumption before this part of
> > the code is executed and after it is left is the same size as the jump
> > in memory when sqlite3_step() is called.
>
>
> When doing a sorted query, the result set is formed in a temporary database
> somewhere defined by the environment. In your case, it sounds like the
> temporary database is memory based. Once the result set is done with, SQLite
> may return the memory to the OS using free, but that will show under the
> process's virtual memory footprint.
>
> You can tell SQLite to use a disk based temporary database using:
> http://sqlite.org/pragma.html#pragma_temp_store
>
> Using this, your memory usage will probably be more stable.
>
> However, this certainly isn't a memory leak.
>
>
> >
> > RobR
> >
>
> Christian
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Please do not copy or forward this message or any attachments without
my permission.  Remember, asking permission is a great way to get me
to visit your site!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Major memory leak

[Rob Richardson]

My SQLite library is built from the single translation unit
sqlite.c/sqlite.h.  That file contains the version number 3.3.17.

I do not have valgrind, but circumstantial evidence that this is a
SQLite problem is strong.  When stepping through my code, I see that
my application's memory jumps by over 2.5 megabytes when the
sqlite3_step() method is called when using either the sorted query or
the query using max().  The unsorted query doesn't show any memory
jump.  Also, the difference in memory consumption before this part of
the code is executed and after it is left is the same size as the jump
in memory when sqlite3_step() is called.

RobR


On 3/20/08, Ken <[EMAIL PROTECTED]> wrote:
> It might be helpful to include the version of sqlite.
>
> Have you run your code through a memory analysis routine such as valgrind, to 
> validate that the leak is not occuring in your application code?
>
>
> HTH,
> Ken
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Major memory leak

[Rob Richardson]

Greetings!

I must be doing something wrong.  I've got a simple table with three
columns, a key column, a value column and a timestamp column.  There
are 357,000 rows.  The timestamps are stored as floating-point numbers
(Julian dates), and the other two fields contain integers.  I open the
table, read one record, and close it.  If I do not sort the data,
there is no memory loss.  Here's the query:

select datetime(value_timestamp) AS latest_time from trend_data

If I sort the data and ask for only the first record, I leak over 2
megabytes of data.  Here's the query:

select datetime(value_timestamp) AS latest_time from trend_data  order
by value_timestamp desc limit 1

I got the same result when the query used the max() function instead
of ordering the recordset.

My program use sqlite3_prepare16_v2, followed by sqlite3_step,
followed by sqlite3_finalize.

Is there something I've left out?  The application in which these
calls are made is designed to be started and left alone.  At one
point, I checked TaskManager on the customer's computer and found that
one instance of this program was using over 950 megabytes of memory!
At that point, I advised the customer to restart the program once a
day, but I'd really like to be able to tell them they don't have to do
that any more.

Thanks very much!

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Major memory leak

[Rob Richardson]

Greetings!

I must be doing something wrong.  I've got a simple table with three
columns, a key column, a value column and a timestamp column.  There
are 357,000 rows.  The timestamps are stored as floating-point numbers
(Julian dates), and the other two fields contain integers.  I open the
table, read one record, and close it.  If I do not sort the data,
there is no memory loss.  Here's the query:

select datetime(value_timestamp) AS latest_time from trend_data

If I sort the data and ask for only the first record, I leak over 2
megabytes of data.  Here's the query:

select datetime(value_timestamp) AS latest_time from trend_data  order
by value_timestamp desc limit 1

I got the same result when the query used the max() function instead
of ordering the recordset.

My program use sqlite3_prepare16_v2, followed by sqlite3_step,
followed by sqlite3_finalize.

Is there something I've left out?  The application in which these
calls are made is designed to be started and left alone.  At one
point, I checked TaskManager on the customer's computer and found that
one instance of this program was using over 950 megabytes of memory!
At that point, I advised the customer to restart the program once a
day, but I'd really like to be able to tell them they don't have to do
that any more.

Thanks very much!

RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Wrong column type being returned

[Rob Richardson]

Greetings!

I have an application that writes a small SQLite database (well, several
small databases), and an ActiveX control that reads them.  One of the
tables has three columns: a foreign key into another table, a value
(which should be a double, but in the file I looked at, they're all
integers for some unknown reason), and a timestamp.  In early versions
of these applications, the timestamp was written as a floating-point
number containing a date in Julian form (number of days since 1/1/1601,
I think it is).  But with the latest SQLite library, the writing
application suddenly started writing the dates in human-readable form
("2008-02-01 11:35:00").  I don't know why.  Of course, this broke the
reading control.  But since I had cleverly written the reading
application using classes that wrap the various field types, all I had
to do was create a JulianDate class to wrap a date field.  In that
class, I'd check the column type I'm reading.  If it's a double, I'd
just return that, and if it's text, I'd convert it into a Julian date
and return that.  This worked just fine when I first wrote it a week or
two ago.  But now it's not working.  

My code uses sqlite3_column_type to determine the column type of the
date column.  If it's SQLITE_FLOAT, then sqlite3_column_double() is used
to retrieve the value.  Otherwise, I assume the column is text and use
sqlite3_column_text16() to retrieve the data.  The problem I'm having is
that my sqlite3_column_type() call is returning 1, which means the
column is an integer.  It isn't.  When I open the file in SQLiteSpy, I
see that all values in the date column are floating point numbers, as
expected.  And as I step through my code, since it tries to call
sqlite3_column_text16(), I see that the data to the right of the decimal
point is getting truncated when I read it.  Can anyone tell me why
SQLite suddenly thinks this column of floating-point data holds only
integers?

Thanks very much!

Rob Richardson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sharing an in-memory database between applications

[Rob Richardson]

Greetings!

We are using an SQLite database to store process data that will
eventually be displayed on a graph.  The database design is simple,
including only six tables, but the table containing the data points for
the graph could contain a few million records.  By using the simplest
possible query and asking for the bare minimum of data I need at any one
point, I've managed to get the time to display the graph down from a few
minutes to about 15 seconds for a sample database with 1.3 million
records.  

But I'm wondering if I can use an in-memory database to improve this
dramatically.  The data is collected by a Windows service that collects
data and adds it to the database once a minute.  If the service would
also store the data into an in-memory database, and the graphing
application could somehow read the same database, I ought to be able to
get unbelievable speed.  Is this feasible?  If so, how would I set it
up?

Another possibility might be to read the entire database from disk into
an in-memory database when the graphing application starts up, if
there's a way to do that that is much faster than a set of INSERT INTO
newtable SELECT * FROM oldtable (or whatever -- you get the idea)
statements.

Thank you very much.

Rob Richardson
RAD-CON INC.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How can I convert from Julian time to a tm structure?

[Rob Richardson]

Dr. Hipp,

Thank you very much for the link to the source for the Julian date
calculations.  I did pull the code out of date.c, and I'm using it.  But
the code seems to rely on implicit conversions between floating-point
numbers and integers that I assume must be intentional, but it looks
error-prone to me.  While I am quite confident that you and your
collaborators checked this code carefully, I would like to see an
explanation of this algorithm to understand it more fully.  Maybe I'll
see if I can get the book through an inter-library loan someplace.

Rob Richardson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] How can I convert from Julian time to a tm structure?

[Rob Richardson]

All right.  Smack me upside the head again.  I deserve it. SQLite is
open source, so I just had to look in the source code.

RobR

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] How can I convert from Julian time to a tm structure?

[Rob Richardson]

Greetings!

I am trying to speed up an application that queries a table with three
columns and around a million records, and one of the fields is a
timestamp.  One thing I want to do is to move the conversion of the
timestamp from a Julian time to a human-readable time from the query to
my application.  (By the way, this is a C++ app written in MS Visual
Studio 6.)  I could build a query and have SQLite execute it, something
like "SELECT datetime(123456.789) AS timestring", but that has the
overhead of preparing the query, executing it and finalizing it, plus
the overhead of converting from a string representation into the tm
structure once I get the result of the query.

I didn't see any little utility function in the SQLite library that just
exposes whatever routine SQLite uses to do the conversion.  Does one
exist?

There must be plenty of algorithms out there to do this conversion.  A
quick search revealed a few, but they were obviously faulty (assuming
every year has 365.25 days, for instance) or not precise enough
(returning only the day).  I need an algorithm that is accurate to the
nearest second.  What should I use?  Or is the SQLite query the best I'm
going to do?

Thank you very much.

Rob Richardson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] What happens when a table with an active statement is changed?

[Rob Richardson]

It seems this was discussed just a few days ago.  A recent upgrade to
SQLite allows inserts, updates and deletes on tables that are also open
for selection.  The changes may or may not appear as I call
sqlite3_step(), but I can live with that.

RobR

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] What happens when a table with an active statement is changed?

[Rob Richardson]

Greetings!

In the ADO world, I can have a recordset object that holds data from a
table.  I can call AddNew() to add a record to the table, Update() to
change data in the current record, or Delete() to delete the current
record.  But in the SQLite world, insertions, updates and deletions can
only be done by preparing the appropriate SQL statement and calling
sqlite3_exec() on the database (or sqlite3_prepare()/step()/finalize()).


So, what happens if I need to change a record as I am walking through a
result set?  If I have one sqlite statement that is pointing to a record
and a different sqlite statement deletes that record, will
sqlite3_step() on the first statement have a problem?  What if the
second sqlite statement deletes the record that the next sqlite3_step()
on the first statement would have retrieved on its next call?  What if
the second sqlite statement adds a record?  Will the repeated
sqlite3_step() calls on the first statement eventually get the new
record?  Or do I just need to be careful that I never have two active
sqlite statements referencing the same table?

Rob Richardson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Re: Why is there no sqlite3_exec16() method?

[Rob Richardson]

Igor,

Thank you very much for your reply.  My naïve impression was that 
sqlite3_prepare/step/finalize are used for SELECT statements, where there would 
be a result set one would want to step through, and that one would use 
sqlite3_exec() for statements where no result set is expected, such as UPDATE, 
DELETE or INSERT.  

So, let's say we want to delete a record using "DELETE FROM my_table WHERE 
my_key = my_unwanted_value".  I would just pass that string into 
sqlite3_prepare16(), then call sqlite3_step() to actually do the deletion (and 
return SQLITE_DONE), and then clean up by calling sqlite_finalize?

Rob Richardson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Why is there no sqlite3_exec16() method?

[Rob Richardson]

Greetings!

Our shop writes in Visual C++ 6, and our applications are all developed
with _UNICODE defined.  But if we want to use sqlite3_exec(), we are
forced to convert the SQL statements to single-byte characters.  Why is
there no sqlite3_exec16() method?  Or is there a version of the SQLite
API that does have that method?

Thank you very much.

Rob Richardson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] DSN-less connection string

[Rob Richardson]

Greetings!

I am trying to connect to a SQLite database from inside a C++ program
(written in Visual Studio 6) using ADO without using a DSN.  The
database file is c:\program files\wincaps\trend01.trd.  The library
developed in house to wrap ADO calls contains an Open() method that
accepts a string of the form "DSN=SomeDSNName".  The method prepends
"Provider=MSDASQL;" to that and sends it to an ADO Connection object.  I
tried to pass in "Data Source=c:\Program Files\WinCaps\Trend01.trd" to
it instead.  I got an error complaining that the data source name is too
long.  So I copied the file into my C:\Misc folder and passed in "Data
Source=c:\Misc\Trend01.trd".  The final connection string was
"Provider=MSDASQL;Data Source=c:\misc\trend01.trd".  The Microsoft ODBC
Manager complained that the data source was not found and no default
driver was specified.  

I downloaded and installed the ADO.Net provider, but I did not see
anywhere anything telling me what provider name to use with it.  

I can't use a DSN because the database to be opened must be selected by
the user at run time.

How do I do this?

Thank you very much.

Rob Richardson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Why does "SELECT julianday('some_date') AS dateNumber" get me a string via ODBC?

[Rob Richardson]

Greetings!

 

I am trying to talk to a small SQLite database through ADO and an ODBC
driver.  I want to convert a date to a Julian day before using the date
in a query of a table that could have over a million records.  In
SQliteSpy, the query "SELECT julianday('2007-06-05 12:34:56',
'localtime') AS dateNumber" gives me a floating-point number.  But in
ODBC, I use that query to create a recordset and examine the first value
in the recordset, I see that it is a variant of type VT_BSTR containing
"2345678.9123" (or whatever), and I have to use strtod() to convert it
to the number I want.  Is there a way to write the query to ensure that
it will give me a number instead of a string?

 

RobR

 

P.S.  Since I am going to turn around and use the value in another
query, I think I actually do want it in a string, but I'd still like to
know the answer for future reference.

[sqlite] How can I get my query to run as fast as SQLiteSpy?

[Rob Richardson]

Greetings!

I have a simple table with five columns and 450,000 rows.  In SQLiteSpy,
I can run "SELECT * FROM trend_data" and get all 450,000 rows in 4.5
seconds.  But in my program, if I use sqlite3_prepare() and
sqlite3_step() until I run out of data, it takes 55 seconds to get
through all rows.  A test with hard-coded junk data showed that my
program is account for only 2 seconds of that.  If I use
sqlite3_get_table(), I can cut my time in half, which is nice, but I'm
still taking 25 seconds to get the same data SQLiteSpy is getting in 4.
How is SQLiteSpy doing it, and can I use the same trick?

Also, my version of the sqlite library code only has an
sqlite3_get_table() method that uses single-byte characters.  Does the
latest code have a Unicode version of this method?

Thank you very much.

Rob Richardson


-Original Message-
From: Will Leshner [mailto:[EMAIL PROTECTED] 
Sent: Thursday, May 24, 2007 11:39 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] building/upgrading on Mac OS X

On 5/23/07, Alessandro de Manzano <[EMAIL PROTECTED]> wrote:

> That's my question too, and I'm glad to hear it works fine for you :)
> (may I ask you which version of OS X are you using ? the latest 10.4.9
> ?)

Yes. I'm building on a MacBook Pro running 10.4.9.

> I don't understand why on the Wiki's building instructions, Mac OS X
> section, it esplicity says to use --disable-shared for some problem
> with libtool. Maybe some old issue no more applicable ?

I'm not sure about that either. I've been building SQLite "out of the
box" on Mac OS X for several years now and I don't think I've ever had
to do anything more than simply configure and make.

> yep, I tried building "normally" but not tried to install that dynlib
> yet, I'll try as soon as possible :)

I think it should work. At least I hope so :)


-
To unsubscribe, send email to [EMAIL PROTECTED]

-


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Rob Richardson]

For the life of me, I can't figure out how to open a database in
DBManager.  I looked at it before, tried for an hour, and erased it.  I
just tried again, with the same result.  If I can't figure out how to do
such an easy thing, I can't trust the program.

RobR



-Original Message-
From: COS [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, February 07, 2007 12:26 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Is there a SQLiteSpy-like thing that will let me
change data from a grid?

Hi Rob,

You can try DBManager Standard or Enterprise Editions. The first one is
free
for personal use, the second is a commercial product. The complete list
of
features can be found at http://www.dbtools.com.br/EN/dbmanagerpro. You
can
edit your tables data and also manipulate images in blob fields.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Rob Richardson]

I can't get SQLiteExplorer to work with my databases.  It always gives
me an "unknown file format" error.  I believe it's been quite a while
since it was updated.  

RobR


-Original Message-
From: Griggs, Donald [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, February 06, 2007 3:14 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Is there a SQLiteSpy-like thing that will let me
change data from a grid?

Regarding:  "If I want to change data ."

Sqlite3Explorer is free software, and works as you describe.  I imagine
there are several others.

http://www.singular.gr/sqlite/  (Not sure if there will be future
releases of this or not) 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?

[Rob Richardson]

Greetings!

The only tool I have for examining and manipulating SQLite databases is
SQLiteSpy.  If I want to change data in that program, I have to write an
SQL statement.  Every other database editor I've seen lets a use do
simple things from a grid.  Open a table and data is loaded into a grid,
click on a cell and type in a value and it gets written to the table,
select a row and click a Delete button and the row is deleted from the
table, and so on.  There must be a tool somewhere that will do that for
a SQLite database, or there's some feature of SQLiteSpy I don't know
about.  Can somebody please point me to one or the other?

Thank you very much.

Rob Richardson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] SQlite3.exe .dump doesn't do anything for me

[Rob Richardson]

Greetings!

I have a small database (seven tables with no more than 20 rows per
table) that I want to dump, since I want to create similar databases.
However, sqlite3 mydatabase.db ".dump" just gives me:

BEGIN TRANSACTION;
COMMIT;

What would cause sqlite3 not to be able to see any tables in my
database?

Thank you very much.

Rob Richardson

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Creating a database from inside a program

[Rob Richardson]

Greetings!

 

I need to be able to create a database with a known schema from inside a
program.  I used sqlite3.exe's .schema command to export the SQL needed
to create the schema for my new database.  I can create the database by
the following steps:

 

C:\: sqlite3 newdatabase.db

Sqlite3> .read schemafile.txt

 

At that point, my database is ready to go.  

 

But, sqlite3 is supposed to be able to accept commands on its command
line.  For example, the schema file was generated using this command:

 

C:\: sqlite3 existingdatabase.db .schema > schemafile.txt

 

So, I thought I could just issue the following command from inside my
program:

 

Sqlite3 newdatabase.db .read schemafile.txt

 

But, when I issue this command from the DOS prompt,  it gives me an
error message saying that there is no command named "read".  (Note the
lack of a leading period.)  If this won't work from a DOS prompt, I'm
sure it won't work from my program.  So, what is the recommended way to
create a new database and its schema from inside a program?  In case it
matters, I'll be using Visual C# 2005 and the SQLite.net.dll file from
SourceForge.

 

Thank you very much!

 

Rob Richardson

RAD-CON INC.

[sqlite] Can primary key columns be altered to use autoincrement?

[Rob Richardson]

I have a small database in which tables have columns that were created
as integer primary key, but not autoincrement.  I would like to make
sure keys are never reused, so I want to add autoincrement to the column
definition.  Is that possible?  

As near as I could tell from the docs, it's not possible to alter a
column at all.  There's no ALTER COLUMN clause available for the ALTER
TABLE statement.  Is that correct?

Thank you.

Rob Richardson
RAD-CON INC.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-