Re: [sqlite] WPF, VS 2010 and System.Data.SQLite 1007200

[Anthony Graham]

I thought support was missing for design time for now (I'm new around here,
so I could well be wrong). Are there nightly builds or betas with these
features?

- Anthony

On Tue, Jun 28, 2011 at 1:55 AM, Wamiduku  wrote:

> On May 23, 5:52 pm, "Christoph P.U. Kukulies" 
> wrote:
> > Am 23.05.2011 17:02, schrieb Drew Hohmann:
> >
> > > Hello,
> > > What steps do I need to take to get SQLite to appear in the list of
> Data
> > > Providers in Visual Studio 2010?  I've installed the latest
> > > System.Data.SQLite (1007200), and when I select a Data Source the only
> > > options for a data provider are
> > > .NET Framework Data Provider for Microsoft SQL Server Compact 3.5
> > > .NET Framework Data Provider for ODBC
> > > .NET Framework Data Provider for OLE DB
> > > .NET Framework Data Provider for Oracle
> > > .NET Framework Data Provider for SQL Server
> >
> > > To reproduce:
> > > Create A WPF project.
> > > Add a Data Source to the application (menu, Data, Add New Data Source),
> > > I select the Database option, click next and select Dataset, click next
> > > and click on New Connection...
> >
> > > Thanks,
> > > Drew
> >
> > Hi Drew,
> >
> > I had similar problems in getting System.Data.Sqlite into my project.
> >
> > First, do an Add Reference to your project and locate
> > System.Data.Sqlite.dll in your file system. Load it. It's in \program
> > files (x86)\Sqlite.NET\bin (Windows 7 at least, for me here).
> >
> > Did you tick VS2008 andVS2010both during  the install. I also had
> > problems and I'm nit sure whether
> > the installer works when both are ticked. Just guessing wildly.
> >
> > --
> > Christoph
>
>
> I have the exact same problem. When installing, I didn't even see any
> checkboxes for VS2008 or VS2010, so I couldn't tick them. I'm running
> VS2010 (Vs2008 not installed) on XP SP3.
>
> -John
>
> (This may be a double post, since I might have messed up in Google
> groups.)
> ___
> 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] Compile sqlite3 with g++? Do I need a wrapper?

[Phong Cao]

Hello everybody,

I am trying to use g++ to compile my C++ application, which uses sqlite3.
After googling for several hours this is what I tried:

g++ -g /home/phongcao/main.cc -o -lsqlite3 /home/phongcao/main `pkg-config
--cflags --libs gtkmm-2.4`

However, the program was not compiled. I also read on some forums saying
that sqlite3 must be compiled with gcc. But since I am using gtkmm and C++
code for my project I wonder if there is anyway possible to compile sqlite3
using g++? If so, please tell me how.

Thank you for reading my message! Have a good day!


Best regards,

-- 
Phong V. Cao
phn...@gmail.com
ca...@rider.edu
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] WPF, VS 2010 and System.Data.SQLite 1007200

[Wamiduku]

On May 23, 5:52 pm, "Christoph P.U. Kukulies" 
wrote:
> Am 23.05.2011 17:02, schrieb Drew Hohmann:
>
> > Hello,
> > What steps do I need to take to get SQLite to appear in the list of Data
> > Providers in Visual Studio 2010?  I've installed the latest
> > System.Data.SQLite (1007200), and when I select a Data Source the only
> > options for a data provider are
> > .NET Framework Data Provider for Microsoft SQL Server Compact 3.5
> > .NET Framework Data Provider for ODBC
> > .NET Framework Data Provider for OLE DB
> > .NET Framework Data Provider for Oracle
> > .NET Framework Data Provider for SQL Server
>
> > To reproduce:
> > Create A WPF project.
> > Add a Data Source to the application (menu, Data, Add New Data Source),
> > I select the Database option, click next and select Dataset, click next
> > and click on New Connection...
>
> > Thanks,
> > Drew
>
> Hi Drew,
>
> I had similar problems in getting System.Data.Sqlite into my project.
>
> First, do an Add Reference to your project and locate
> System.Data.Sqlite.dll in your file system. Load it. It's in \program
> files (x86)\Sqlite.NET\bin (Windows 7 at least, for me here).
>
> Did you tick VS2008 andVS2010both during  the install. I also had
> problems and I'm nit sure whether
> the installer works when both are ticked. Just guessing wildly.
>
> --
> Christoph


I have the exact same problem. When installing, I didn't even see any
checkboxes for VS2008 or VS2010, so I couldn't tick them. I'm running
VS2010 (Vs2008 not installed) on XP SP3.

-John

(This may be a double post, since I might have messed up in Google
groups.)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best solution to import big DBF file?

[Gilles Ganault]

On Mon, 27 Jun 2011 18:05:41 +0200, Dominique Pellé
 wrote:
>Spatialite (extension of SQLite) implements VirtualDbf and VirtualShp.

Thanks for the info. I'm not sure I understand what Spatialite is
("The SpatiaLite extension enables SQLite to support spatial data too
[aka GEOMETRY], in a way conformant to OpenGis specifications")  but
I'll give it a shot.

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

Re: [sqlite] Breaking a string into two

[BareFeetWare]

> BareFeetWare  wrote:
>> I have some source data that includes a "Silo And Region" column of two 
>> words appended together, such as 'NorthPlains',
>> 'SouthPlains', 'NorthSlopes', 'SouthSlopes' etc. I want to split them into 
>> two columns. 
>> 
>> How can I do this in SQLite? A regex or offset/position and replace function 
>> would take care of this (by looking for the second
>> uppercase letter), but there's no regex or offset/position function 
>> provided. 

On 16/06/2011, at 10:11 PM, Igor Tandetnik wrote:

> You could do something like
> 
> ltrim(substr("Silo And Region", 2), 'abc...xyz')
> 
> to extract the second part (spell out the rest of the alphabet in place of 
> '...'). The first part is left as an exercise for the reader.

Great advice, Igor, thank you. Works well.

I maintain that we really need some string searching ability, built into 
SQLite. It's a major omission that is often an issue.

Thanks,
Tom
BareFeetWare

 --
Comparison of SQLite GUI tools:
http://www.barefeetware.com/sqlite/compare/?ml
 --
iPhone/iPad/iPod and Mac software development, specialising in databases
develo...@barefeetware.com

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

Re: [sqlite] How to check foreign keys

[Kees Nuyt]

On Mon, 27 Jun 2011 07:13:14 +0200, Cecil Westerhof
 wrote:

>2011/6/26 Kees Nuyt 
>
>> I think Cecil was referring to the fact that some program can
>> forget to switch foreign_keys to on, and delete rows that are
>> referenced, or change primary keys.
>>
>
>Yes, that is what I mend.
>
>
>So, his question is not about locking, but about verifying all
>> references are still pointing to existing rows.
>>
>> To answer that question, suppose the following schema:
>>
>> CREATE TABLE T1 (
>>id1 INTEGER PRIMARY KEY NOT NULL,
>>contents TEXT
>> );
>>
>> CREATE TABLE T2 (
>>id2 INTEGER PRIMARY KEY NOT NULL,
>>id1 INTEGER REFERENCES T1(id1)
>>ON UPDATE CASCADE ON DELETE CASCADE
>> );
>>
>> Then one could detect missing keys in T1 with:
>>
>> SELECT DISTINCT T2.id1
>>FROM T2
>>LEFT OUTER JOIN T1 ON T2.id1 == T1.id1
>>WHERE T1.id1 IS NULL
>>ORDER BY T2.id1
>> );
>>
>
>Okay, so it can only be done manually?

Not necessarily. You could script it using another nice PRAGMA:

sqlite> .head on
sqlite> PRAGMA foreign_key_list(Statistics);
id|seq|table|from|to|on_update|on_delete|match
0|0|PhysicalLinkStatuss|pl_id|pl_id|CASCADE|CASCADE|NONE
1|0|WANAccessTypes|wa_id|wa_id|CASCADE|CASCADE|NONE
2|0|LastConnectionErrors|le_id|le_id|CASCADE|CASCADE|NONE
3|0|ConnectionStatuss|cs_id|cs_id|CASCADE|CASCADE|NONE
:

As you see, it lists all tables and keys some table references.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ISNULL in sqlite

[logan...@gmail.com]

Yes, I'm checking for empty string, but it wasn't working for null. The
suggestions by kind people here helped resolve it. Thank you!

On Mon, Jun 27, 2011 at 11:16 AM, Gerry Snyder wrote:

> On 6/25/2011 12:33 PM, logan...@gmail.com wrote:
> > Hello,
> >
> > How do I check for a null or empty string in SQLite.
>
> In addition to the other replies you have received, you need to be made
> aware that an empty string and a NULL are very different, and (perhaps)
> both have to be checked for, depending on how the data gor into the table.
>
>
> Gerry
> ___
> 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] System.TypeInitializationException in SQLiteFactory constructor...

[John D. Marinuzzi]

Has anybody seen this before?  I use System.Data.SQLite for a project and
the program will not run on a client's computer.  This is the exception
listed in the event viewer.

 

Thanks,

 

John

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

Re: [sqlite] ISNULL in sqlite

[Gerry Snyder]

On 6/25/2011 12:33 PM, logan...@gmail.com wrote:
> Hello,
>
> How do I check for a null or empty string in SQLite.

In addition to the other replies you have received, you need to be made 
aware that an empty string and a NULL are very different, and (perhaps) 
both have to be checked for, depending on how the data gor into the table.


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

Re: [sqlite] ISNULL in sqlite

[logan...@gmail.com]

Thank you everyone!!

On Sat, Jun 25, 2011 at 9:27 PM, Jay A. Kreibich  wrote:

> On Sat, Jun 25, 2011 at 04:55:13PM -0400, Igor Tandetnik scratched on the
> wall:
> > logan...@gmail.com wrote:
> > > How do I check for a null or empty string in SQLite. SQL server has
> ISNULL
> > > but it doesn't seem to be supported in SQLite.
> >
> > where MyField is null
>
>   where MyField isnull is also supported.
>
>  http://www.sqlite.org/lang_expr.html  ("is null" is just a standard
>  "is" with the right side expression being a literal NULL.)
>
>
>
>   -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] PRAGMA bug in 3.7.7 (but fine in 3.7.6.3)

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 06/26/2011 03:52 PM, Richard Hipp wrote:
> The bug is that it is returning
> SQLITE_SCHEMA instead of SQLITE_OK.  If you wanted to, you could just ignore
> the error and keep going and everything would work.

Will there be a point release soon fixing this?

If not I'll have to also add a workaround which is fairly difficult due to
the use of a statement cache and the prepare and step code being very separated.

I use v1 prepare interface because v2 is worse - it gives different error
semantics requiring manual reprepare anyway and saves another copy of the
SQL (not useful since I have to reprepare again and already have a copy due
to statement cache).

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk4IxYMACgkQmOOfHg372QTbVwCcCqIxXJbcSRurvjDiXcczDQJP
7mYAn1tk+JQGHoNL9+wtVl2am+Grw5Zh
=L68m
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best solution to import big DBF file?

[Dominique Pellé]

Gilles Ganault  wrote:

> Hello
>
> I have a 87MB file in DBF format that I'd like to import into SQLite.
>
> What is the best tool for this, free or affordable?
>
> Thank you.


Spatialite (extension of SQLite) implements VirtualDbf and VirtualShp.

To create a virtual table from a DBF file "foo.dbf" using UTF-8 as
encoding for TEXT columns:

spatialite> CREATE VIRTUAL TABLE virt_tbl USING VirtualDbf(foo.dbf, UTF-8);

If you want to create a real Sqlite table (not virtual), you can
copy the content of the virtual table into a real table:

spatialite> CREATE TABLE tbl AS SELECT * FROM virt_tbl;


The Spatialite shell also provides the following dot command for convenience:

spatialite> .loaddbf foo.dbf tbl UTF-8

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

Re: [sqlite] sqlite-users Digest, Vol 42, Issue 27

[Pete]

Thanks Michael, that solves the problem.
Pete




Message: 1
> Date: Sun, 26 Jun 2011 12:01:43 +
> From: "Black, Michael (IS)" 
> Subject: Re: [sqlite] Substring question
> To: General Discussion of SQLite Database 
> Message-ID: <71635118-DC41-416E-AE85-F788177BBC96@mimectl>
> Content-Type: text/plain; charset="iso-8859-1"
>
> It's not obvious but this works
>
>
>
> CREATE TABLE x (s string);
> INSERT INTO "x" VALUES('ab:cdef');
> INSERT INTO "x" VALUES('ghij:klmn');
> sqlite> select
> ltrim(ltrim(s,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'),':')
> from x;
> ltri
> 
> cdef
> klmn
>
> Just make sure your char set contains all possible chars left of the :
>
>
>
>
>
> Michael D. Black
>
> Senior Scientist
>
> NG Information Systems
>
> Advanced Analytics Directorate
>
>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Selecting indexes to use & NOT INDEXED

[Igor Tandetnik]

On 6/27/2011 11:18 AM, Mohit Sindhwani wrote:
> We felt that the query was a bit slow:
>
> select * FROM Objects, Objects_Index
> WHERE Objects.id = Objects_Index.id
>   AND minx<= 668632 + 250 AND maxx>= 668632 - 250
>   AND  miny<= 1518661 + 250 AND maxy>= 1518661 - 250
>   AND CAT=25;
>
> Doing an explain query plan revealed that SQlite was scanning Objects
> with the IDX_OBJ_CAT first and then using the R-Tree.  Without
> restricting by CAT, we found the query was much faster and was using
> only the virtual R-Tree index.

You can suppress the index on CAT with a unary plus operator, like this: 
   ... AND +CAT=25;
-- 
Igor Tandetnik

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

Re: [sqlite] How to recover the SQLITE_CORRUPT error in C programming ?

[Jan Hudec]

On Mon, Jun 27, 2011 at 16:15:16 +0800, baiydavid wrote:
> Thanks for you help.
> 
> when an SQLITE_CORRUPT error is returned, we can use ".dump" command to 
> exports the data into a sql script, and then exec this sql in a new database,
> But how to dump the data into a sql script with C programming Interface? 
> Is there any other better method to solve this problem?

You shouldn't be doing automatically. When you get SQLITE_CORRUPT error, it
means something has gone very, very wrong and you should definitely
investigate what. Besides since the database is corrupt, you may not be able
to recover all data, so you have to carefuly check the dump, so again need
manual intervention.

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

Re: [sqlite] Functions and index

[Simon Davies]

On 27 June 2011 16:16, hilaner  wrote:
> I have hundred thousands of records in this table:
>
> CREATE TABLE days (
>        day_id INTEGER NOT NULL PRIMARY KEY,
>        day_date DATE
> );
>
> CREATE INDEX day_i ON days (day_date ASC);
>
> And then if I run such query:
>
> EXPLAIN QUERY PLAN
> SELECT JULIANDAY(MAX(day_date)) FROM days;
> 0|0|0|SCAN TABLE days (~100 rows)
>
> it will do full table scan instead of use an index.
>
> But:
>
> EXPLAIN QUERY PLAN
> SELECT MAX(day_date) FROM days;
> 0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows)
>
> so it is as I expected.
>
> Is there any other way to rewrite such kind of queries with functions?
> The query below uses an index but it is much longer:
>
> EXPLAIN QUERY PLAN
> SELECT JULIANDAY(day_date) FROM days
>   WHERE day_date IN (SELECT MAX(day_date) FROM days);
> 0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (day_date=?) (~250 rows)
> 0|0|0|EXECUTE LIST SUBQUERY 1
> 1|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows)

select julianday( ( select max( day_date ) from days ) );

>
> Best regards,
> Adam

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

Re: [sqlite] Selecting indexes to use & NOT INDEXED

[Mr. Puneet Kishor]

On Jun 27, 2011, at 11:18 AM, Mohit Sindhwani wrote:

> select * FROM Objects, Objects_Index
> WHERE Objects.id = Objects_Index.id
> AND minx <= 668632 + 250 AND maxx >= 668632 - 250
> AND  miny <= 1518661 + 250 AND maxy >= 1518661 - 250
> AND CAT=25;
> 
> Doing an explain query plan revealed that SQlite was scanning Objects 
> with the IDX_OBJ_CAT first and then using the R-Tree.  Without 
> restricting by CAT, we found the query was much faster and was using 
> only the virtual R-Tree index.
> 
> We wanted to get SQLite to use the R-Tree first, so we tried a few 
> different things - eventually, we killed the IDX_OBJ_CAT index and the 
> query became (yes, wait for it) almost 100x faster!


Try something like

SELECT * 
FROM (
SELECT * FROM Objects, Objects_Index
WHERE Objects.id = Objects_Index.id
AND minx <= 668632 + 250 AND maxx >= 668632 - 250
AND  miny <= 1518661 + 250 AND maxy >= 1518661 - 250
)
WHERE CAT=25;

You might have to prefix the columns with the appropriate table names (I can't 
tell which table cat and the bounds are coming from). If the bounds are a part 
of the Objects table, you could try

SELECT * 
FROM (
SELECT * FROM Objects 
WHERE  minx <= 668632 + 250 AND maxx >= 668632 - 250
AND  miny <= 1518661 + 250 AND maxy >= 1518661 - 250
) foo, Objects_Index
WHERE foo.Id = Objects_Index.id AND CAT=25;

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

[sqlite] Selecting indexes to use & NOT INDEXED

[Mohit Sindhwani]

Hi All,

Sorry for the long email, but the background is probably needed :)

1. We have a geographical query that is supported by an R-Tree index.  A 
join is required to filter by category while restricting by the bounding 
box.  On the main table (objects), we have an index IDX_OBJ_CAT on 
Objects(cat) and we have a virtual index on the RTree table (Objects_Index).

We felt that the query was a bit slow:

select * FROM Objects, Objects_Index
WHERE Objects.id = Objects_Index.id
 AND minx <= 668632 + 250 AND maxx >= 668632 - 250
 AND  miny <= 1518661 + 250 AND maxy >= 1518661 - 250
 AND CAT=25;

Doing an explain query plan revealed that SQlite was scanning Objects 
with the IDX_OBJ_CAT first and then using the R-Tree.  Without 
restricting by CAT, we found the query was much faster and was using 
only the virtual R-Tree index.

We wanted to get SQLite to use the R-Tree first, so we tried a few 
different things - eventually, we killed the IDX_OBJ_CAT index and the 
query became (yes, wait for it) almost 100x faster!

So, now we want to go about fixing our schema and queries to benefit 
from this.

What do you recommend?  What would be the best way to make SQLite3 use 
the R-Tree?  We're a bit unsure about dropping the category index since 
we are not sure which other queries will be affected.

2. While searching around, I found INDEXED BY and NOT INDEXED and NOT 
INDEXED worked very well for this query.  But, an email from 2009 said: 
"many are of the opinion that this feature is prone to misuse."

> There is the "INDEXED BY" clause. But many are of the opinion that
> this feature is prone to misuse.
>
> http://www.sqlite.org/lang_indexedby.html
>
> Dan.

What's the current wisdom?

Thanks,
Mohit.



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

[sqlite] Functions and index

[hilaner]

I have hundred thousands of records in this table:

CREATE TABLE days (
day_id INTEGER NOT NULL PRIMARY KEY,
day_date DATE
);

CREATE INDEX day_i ON days (day_date ASC);

And then if I run such query:

EXPLAIN QUERY PLAN
SELECT JULIANDAY(MAX(day_date)) FROM days;
0|0|0|SCAN TABLE days (~100 rows)

it will do full table scan instead of use an index.

But:

EXPLAIN QUERY PLAN
SELECT MAX(day_date) FROM days;
0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows)

so it is as I expected.

Is there any other way to rewrite such kind of queries with functions?
The query below uses an index but it is much longer:

EXPLAIN QUERY PLAN
SELECT JULIANDAY(day_date) FROM days
   WHERE day_date IN (SELECT MAX(day_date) FROM days);
0|0|0|SEARCH TABLE days USING COVERING INDEX day_i (day_date=?) (~250 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
1|0|0|SEARCH TABLE days USING COVERING INDEX day_i (~1 rows)

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

Re: [sqlite] Basebase Locked Error

[Simon Slavin]

On 27 Jun 2011, at 3:36pm, Jason Gray wrote:

> Hello, I found the problem.  The 
> "[Transactional(TransactionalTypes.TransactionScope)]" decoation was keeping 
> a lock on the database. So that prompts another question.. What is the best 
> way to implement Transactions?

Can you just execute the appropriate SQL commands, 'BEGIN' and 'COMMIT', at 
appropriate times ?

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

Re: [sqlite] Basebase Locked Error

[Jason Gray]

Hello, I found the problem.  The 
"[Transactional(TransactionalTypes.TransactionScope)]" decoation was keeping a 
lock on the database. So that prompts another question.. What is the best way 
to implement Transactions?
 
thanks,
 
Jason

From: Jason Gray 
To: "sqlite-users@sqlite.org" 
Sent: Monday, June 27, 2011 7:28:52 AM
Subject: [sqlite] Basebase Locked Error

Hello, I hope you can help me with a problem I'm having. I'm using CLSA 
business objects and SQLite as the datastore. Essentially this is what I'm 
trying to do, shouldn't this work?
 
[Transactional(TransactionalTypes.TransactionScope)]
    protected override void DataPortal_Insert()
    {
    using (SQLiteConnection conn = new SQLiteConnection(DataBase.Conn()))
    {   
    conn.Open();
    using (SQLiteCommand cmd = new SQLiteCommand(conn))
    {
    //do stuff

    cmd.ExecuteNonQuery();    
    
    }
   
    }
 
    using (SQLiteConnection conn2 = new SQLiteConnection(DataBase.Conn()))
    {
    conn2.Open();
    }
  }
 
I get the "Database is locked" error on conn2.Open(). Even if I call .Close() 
and .Dispose() is still get the same error.
 
What am i doing wrong?
 
thanks,
 
Jason
___
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] Best solution to import big DBF file?

[Gilles Ganault]

On Mon, 27 Jun 2011 17:14:52 +0400, Alexey Pechnikov
 wrote:
>1. sqlite3-dbf - converter of XBase / FoxPro tables to SQLite
>http://sqlite.mobigroup.ru/wiki?name=sqlite3-dbf

Thanks Alexey, worked the first time:

# dpkg -i sqlite3-dbf_2010.08.16_i386.deb
# sqlite3-dbf myfile.dbf | sqlite3 myfile.sqlite

Thanks everyone.

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

Re: [sqlite] Best solution to import big DBF file?

[Gilles Ganault]

On Mon, 27 Jun 2011 14:22:43 +0100, Carlos Rocha
 wrote:
>I think you'll find what you need here
>http://www.vlsoftware.net/

Thanks for the link.

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

Re: [sqlite] How to recover the SQLITE_CORRUPT error in C programming ?

[Michael Stephenson]

Perhaps you could look at the code in shell.c and see how it does the .dump,
then copy paste or something similar into your own app.  I'm not sure if
there might be issues with functions declared SQLITE_API vs SQLITE_INTERNAL,
which may require some additional working around, but hopefully not.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin
Sent: Monday, June 27, 2011 8:06 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] How to recover the SQLITE_CORRUPT error in C
programming ?


On 27 Jun 2011, at 9:15am, baiydavid wrote:

>when an SQLITE_CORRUPT error is returned, we can use ".dump" command to
exports the data into a sql script, and then exec this sql in a new
database,
>But how to dump the data into a sql script with C programming
Interface? Is there any other better method to solve this problem?

Sorry, David, but there is no built-in way to dump a SQLite database as SQL
commands.

If you need to worry about this, this suggests you are getting corrupt
databases very often.  This should not happen: SQLite should not be
corrupting its databases.  Perhaps you could investigate this problem with
your setup.

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] Best solution to import big DBF file?

[Carlos Rocha]

Hi

I think you'll find what you need here
http://www.vlsoftware.net/

> Hello
>
> I have a 87MB file in DBF format that I'd like to import into SQLite.
>
> What is the best tool for this, free or affordable?
>
> Thank you.
>
> ___
> 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] Best solution to import big DBF file?

[Simon Slavin]

On 27 Jun 2011, at 2:12pm, Gilles Ganault wrote:

>> As Simon said, the sqlite3 shell can import structured text files.
> 
> But I need to first convert the DBF file into CSV, right?

Your problem seems to be with opening the DBF file without the original app.  
So perhaps there's a general DBF manager application that'll export it to CSV.

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

Re: [sqlite] Best solution to import big DBF file?

[Alexey Pechnikov]

1. sqlite3-dbf - converter of XBase / FoxPro tables to SQLite
http://sqlite.mobigroup.ru/wiki?name=sqlite3-dbf

2. Or see the VirtualText extension from Spatialite project.
This extension can be builded with SQLite
http://sqlite.mobigroup.ru/dir?name=ext/virtualtext
http://sqlite.mobigroup.ru/dir?name=ext/iconv
I did support SQLite build with this some times ago (deb packages),
but the first solution is more useful for me now.

2011/6/27 Gilles Ganault :
> Hello
>
> I have a 87MB file in DBF format that I'd like to import into SQLite.
>
> What is the best tool for this, free or affordable?
>
> Thank you.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best solution to import big DBF file?

[Gilles Ganault]

On Mon, 27 Jun 2011 14:57:31 +0200, Stephan Beal
 wrote:
>Google suggests several:

Yes, but I'd like to save time by not having to evaluate them if
someone's done this recently.

There are only two converters listed on the site, and they seem to
require FoxPro for DOS
www.sqlite.org/cvstrac/wiki?p=ConverterTools

I tried opening the 87MB file in OpenOffice, but it still wasn't done
after running for 5mn :-/

>As Simon said, the sqlite3 shell can import structured text files.

But I need to first convert the DBF file into CSV, right?

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

Re: [sqlite] Best solution to import big DBF file?

[Stephan Beal]

On Mon, Jun 27, 2011 at 2:51 PM, Gilles Ganault wrote:

> Is there a way to open a DBF file and get a text dump?
>

Google suggests several:

http://www.google.de/search?sourceid=chrome=UTF-8=export+dbf+file+to+text

As Simon said, the sqlite3 shell can import structured text files.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Best solution to import big DBF file?

[Gilles Ganault]

On Mon, 27 Jun 2011 13:42:33 +0100, Simon Slavin
 wrote:
>Dump the contents as text and use the SQLite command-line shell to import it.

Thanks for the tip, but I don't have the DBase application, just the
file (it's actually three files: Two small ones with the column
definitions and their possible values, and the big one with the data).

Is there a way to open a DBF file and get a text dump?

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

Re: [sqlite] Best solution to import big DBF file?

[Simon Slavin]

On 27 Jun 2011, at 1:38pm, Gilles Ganault wrote:

> I have a 87MB file in DBF format that I'd like to import into SQLite.
> 
> What is the best tool for this, free or affordable?

Dump the contents as text and use the SQLite command-line shell to import it.



For the text format, if the data is simple and doesn't include tricky 
characters, you can use CSV format.  If that's not the case you might try 
dumping as SQL commands and using the command-line shell to '.read' them.

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

[sqlite] Best solution to import big DBF file?

[Gilles Ganault]

Hello

I have a 87MB file in DBF format that I'd like to import into SQLite.

What is the best tool for this, free or affordable?

Thank you.

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

Re: [sqlite] How to recover the SQLITE_CORRUPT error in C programming ?

[Simon Slavin]

On 27 Jun 2011, at 9:15am, baiydavid wrote:

>when an SQLITE_CORRUPT error is returned, we can use ".dump" command to 
> exports the data into a sql script, and then exec this sql in a new database,
>But how to dump the data into a sql script with C programming Interface? 
> Is there any other better method to solve this problem?

Sorry, David, but there is no built-in way to dump a SQLite database as SQL 
commands.

If you need to worry about this, this suggests you are getting corrupt 
databases very often.  This should not happen: SQLite should not be corrupting 
its databases.  Perhaps you could investigate this problem with your setup.

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

[sqlite] Basebase Locked Error

[Jason Gray]

Hello, I hope you can help me with a problem I'm having. I'm using CLSA 
business objects and SQLite as the datastore. Essentially this is what I'm 
trying to do, shouldn't this work?
 
[Transactional(TransactionalTypes.TransactionScope)]
    protected override void DataPortal_Insert()
    {
    using (SQLiteConnection conn = new SQLiteConnection(DataBase.Conn()))
    {   
    conn.Open();
    using (SQLiteCommand cmd = new SQLiteCommand(conn))
    {
    //do stuff

    cmd.ExecuteNonQuery();    
    
    }
   
    }
 
    using (SQLiteConnection conn2 = new SQLiteConnection(DataBase.Conn()))
    {
    conn2.Open();
    }
  }
 
I get the "Database is locked" error on conn2.Open(). Even if I call .Close() 
and .Dispose() is still get the same error.
 
What am i doing wrong?
 
thanks,
 
Jason
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Meaning of values in sqlite_stat1

[Richard Hipp]

On Mon, Jun 27, 2011 at 2:49 AM, Cecil Westerhof wrote:

> When giving:
>select * from sqlite_stat1;
> I get:
>tbl|idx|stat
>utilities|sqlite_autoindex_utilities_2|67 1
>utilities|sqlite_autoindex_utilities_1|67 1
>categories|sqlite_autoindex_categories_1|4 1
>weights|sqlite_autoindex_weights_1|228 57 1
>
> I am curious about the stat field. The first value seems the number of
> records (but could also be the last distributed key), the last could be the
> lowest key in the table. Is this correct?
>

http://www.sqlite.org/src/artifact/a425d62e8?ln=309-325



> But when there are three values –weights–, what is the meaning of the
> middle
> value?
>
> --
> Cecil Westerhof
> ___
> 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

Re: [sqlite] randomness issues on windows

[Max Vlasov]

On Sat, Jun 25, 2011 at 11:20 PM, bob  wrote:
> i'm getting an issue relating to the use of the random() function in a
> SQL query.
>
> i run 2 PHP scripts very quickly (less than 1 second apart).
>
> they use an SQL query like    SELECT * FROM table ORDER BY random() LIMIT 5;
>
> because the 2 scripts run so close together (in time)  i get the same
> values as a result.


Since sqlite initialize the seed only once you probably mean the case
when your two queries used on separately opened and closed
connections, right?

I tried to simulate this with a test table containing numbers from 1
to 5 and query Select value from testtable order by random(). Sqlite
gave different results most of the times. This was on a windows
machine where the time between connection was more than 1ms and also
on a machine when the time was < 1 ms. My tests can be wrong if sqlite
initializes the seed once for the library, not for single connection.
I tried to track this with loading sqlite dll for every connection,
but unfortunately I could not find a windows machine that can do all
these tasks in less than a 1 ms. But for tests > 1 ms the numbers were
still different

So you probably have to look in a different layer

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

[sqlite] How to recover the SQLITE_CORRUPT error in C programming ?

[baiydavid]

Thanks for you help.

when an SQLITE_CORRUPT error is returned, we can use ".dump" command to 
exports the data into a sql script, and then exec this sql in a new database,
But how to dump the data into a sql script with C programming Interface? Is 
there any other better method to solve this problem?

Thanks!
David chen

2011-06-27 



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

Re: [sqlite] PRAGMA bug in 3.7.7 (but fine in 3.7.6.3)

[Daniel Shahaf]

Greg Stein wrote on Sun, Jun 26, 2011 at 19:20:30 -0400:
> On Sun, Jun 26, 2011 at 18:52, Richard Hipp  wrote:
> > On Sun, Jun 26, 2011 at 3:59 PM, Greg Stein  wrote:
> >
> >> Hey all,
> >>
> >> There is a bug in 3.7.7 that affects Apache Subversion. We've verified
> >> that it is this latest release, as prior releases have worked just
> >> fine for us.
> >>
> >> Below is the reproduction script, thanks to Daniel Shahaf. With this
> >> pragma not working, we cannot even start up :-(
> >>
> >
> > Thanks for the test case.
> >
> > The pragma is actually working.  The bug is that it is returning
> > SQLITE_SCHEMA instead of SQLITE_OK.  If you wanted to, you could just ignore
> > the error and keep going and everything would work.
> 
> Okay. I'll add some code to ignore the result *if* we have 3.7.7 *and*
> it is SQLITE_SCHEMA. Thus, we can keep trapping other issues and on
> other releases.
> 

r1139971.

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

Re: [sqlite] Should i upgrade SQLITE for my system?

[Martin.Engelschalk]

Hi,

you can look at the changes doumented here

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

and determine if they are beneficial for your application.

Also, my own application runs faster with the newest version (I upgraded 
from version 3.2.5)

Martin

Am 27.06.2011 09:48, schrieb Hoang Linh Duong:
> Hi all,
>
> Currently i'm using SQLite 3.6.22 for my system. I know the latest SQLite is
> version 3.7.7; im just wondering if i should update SQLite in my system. Is
> there any significant change in the this latest SQLite?
>
> Please advise. Thanks.
>
> Linh
> ___
> 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] Should i upgrade SQLITE for my system?

[Hoang Linh Duong]

Hi all,

Currently i'm using SQLite 3.6.22 for my system. I know the latest SQLite is
version 3.7.7; im just wondering if i should update SQLite in my system. Is
there any significant change in the this latest SQLite?

Please advise. Thanks.

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

[sqlite] Meaning of values in sqlite_stat1

[Cecil Westerhof]

When giving:
select * from sqlite_stat1;
I get:
tbl|idx|stat
utilities|sqlite_autoindex_utilities_2|67 1
utilities|sqlite_autoindex_utilities_1|67 1
categories|sqlite_autoindex_categories_1|4 1
weights|sqlite_autoindex_weights_1|228 57 1

I am curious about the stat field. The first value seems the number of
records (but could also be the last distributed key), the last could be the
lowest key in the table. Is this correct?
But when there are three values –weights–, what is the meaning of the middle
value?

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