General

Sqlite & Silverlight 4.0

tobi88 — Fri, 03 Sep 2010 13:51:57 GMT

Hi all,

although i read the articles found here i am still not sure about the situatuin concerning SilverLight and this wrapper.

As far as i can see i have no chance to use the Sqlite dll in my clientside, that's clear,

but using it by a webservice(not runnin on clientside) should be possible, right ?

I tried to do it by

1) Adding the .sqlite file

2) Creating a Connection with the Connectionstring to the .slite file

3) executing some SqliteCommands....

but however it didn't work

Is it possible in this scenario at all ?

Lookin forward to your answers, i would be really thankful

Best Regards,

Tobi

how read/write from remote DB

marco — Sun, 05 Sep 2010 16:18:02 GMT

Hello,

I was wondering if there is a possibility to connect to a sqlite db
remotely from an application that runs on your desktop.
Let me better explain.
let's say that I create a Sqlite db (myDb.db3), the fill it, then load on a Web site http://mySitoWeb.com/
At this point the db would be found http://mySitoWeb.com/myDb.db3

In yours opinion is possible to read / write from a desktop application?
I experimented by changing the connection string but unfortunately failed.
If is possible please explain how to do it.

marco

Platform independency of encryped databases

cervengoc — Fri, 03 Sep 2010 15:20:22 GMT

Dear developers,

I would like to ask about platform independency of the encryption concept. There is a note on the front page which states that encrypted databases can be accesed only on Windows platforms. I'd like to know that for example assuming that I'm on Windows platform, can I access the enrypted SQLite file from native c++, Java, or from any other platform? In other words how much is this encryption depending on .NET Framework?

I've read about SEE (SQLite Encryption Extension), and it seems to be completely platform independent. Is the provided encryption wrapping some known tool for SQLite, or has it its own algorythm depending on .NET Framework?

I hope I was clear enough, any clarification would be appreciated. Thank you in advance, regards:

Zoltan Tamasi

Trouble downloading current version

LarryDev — Sun, 05 Sep 2010 20:18:30 GMT

Hello, I am new to System.Data.SQLLite and I am trying to download the newest release from SourceForge.Net so I can test it out and the site is not coming up. is System.Data.SQLLite available for download anywhere else?

How to check multiple foreign keys' cases?

cane_c — Sun, 05 Sep 2010 11:40:49 GMT

Hello,

I'm new with SqLite but everything was quite easy to understand except foreign keys. Here is my problem, maybe someone could explain it...

I've created two tables:

Create table Table1 (
                ID_pk Text PRIMARY KEY,
                CHECK(ID_pk like 'P%'));

Create table Table2 (
                ID_pk Text PRIMARY KEY,
                CHECK(ID_pk like 'M%'));

I would like to create the third table which would have references to foreign keys of these first tables. However, I wanted that fk references would depend on the first letter. Smth like that:

Create table Table3 (
                ID Text PRIMARY KEY,
                ID_fk Text,
          CASE
              WHEN (ID_fk like P%)
                THEN
                      (FOREIGN KEY (ID_fk) REFERENCES Table1 (ID_pk)),
                      WHEN (ID_fk like M%)
             THEN
              (FOREIGN KEY (ID_fk) REFERENCES Table2 (ID_pk)),
                 ELSE RAISE(ABORT)
        END);

However it doesnt work.. Is it possible to do this? If so, should I use CASE (which means in this case I'm making some errors) or it is possible to solve this problem just with triggers?

Hope somebody could help.. I would really appreciate it...

BadImageFormatException, x86 multi-platform, managed-only version

Zalbor — Fri, 27 Aug 2010 15:26:39 GMT

Hello,

I've developed my application using Mono under a Linux system. I use System.Data.SQLite and because I wanted it to work in both Linux under Mono and Windows under .NET, I used the managed-only version.

When I run the program with Mono/Linux, everything works fine.

For Windows, I've also added the sqlite3.dll file to the same directory with the binaries (but not added it as a reference to the solution). Every time I attempt to run the program, I get a BadImageFormatException on the command that opens the SQL connection to the database.

All systems I've tested the program on are x86, so the numerous threads I found about problems when running under x64 don't apply to me... Is it something that can be resolved easily?

Any chance that System.Data.SQLite will be updated with native SQLite 3.7.2?

ssteo — Thu, 02 Sep 2010 16:55:45 GMT

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

Seems quite a number of critical bugs:

2010 August 24 (3.7.2)

Fix an old and very obscure bug that can lead to corruption of the database free-page list when incremental_vacuum is used.

2010 August 23 (3.7.1)

Added new commands SQLITE_DBSTATUS_SCHEMA_USED and SQLITE_DBSTATUS_STMT_USED to the sqlite3_db_status() interface, in order to report out the amount of memory used to hold the schema and prepared statements of a connection.
Increase the maximum size of a database pages from 32KiB to 64KiB.
Use the LIKE optimization even if the right-hand side string contains no wildcards.
Added the SQLITE_FCNTL_CHUNK_SIZE verb to the sqlite3_file_control() interface for both unix and windows, to cause database files to grow in large chunks in order to reduce disk fragmentation.
Fixed a bug in the query planner that caused performance regresssions relative to 3.6.23.1 on some complex joins.
Fixed a typo in the OS/2 backend.
Refactored the pager module.
The SQLITE_MAX_PAGE_SIZE compile-time option is now silently ignored. The maximum page size is hard-coded at 65536 bytes.

2010 August 04 (3.7.0.1)

Fix a potential database corruption bug that can occur if version 3.7.0 and version 3.6.23.1 alternately write to the same database file. Ticket [51ae9cad317a1]
Fix a performance regression related to the query planner enhancements of version 3.7.0.

2010 July 22 (3.7.0)

Added support for write-ahead logging.
Query planner enhancement - automatic transient indices are created when doing so reduces the estimated query time.
Query planner enhancement - the ORDER BY becomes a no-op if the query also contains a GROUP BY clause that forces the correct output order.
Add the SQLITE_DBSTATUS_CACHE_USED verb for sqlite3_db_status().
The logical database size is now stored in the database header so that bytes can be appended to the end of the database file without corrupting it and so that SQLite will work correctly on systems that lack support for ftruncate().

Running ANALYZE Command using the ADO provider

mort — Wed, 01 Sep 2010 10:43:43 GMT

Should we be able to call the 'ANALYZE' command using the ExecuteScalar method on a SqliteCommand. Currently when we attempt this we get the following message:

Could not find stored procedure 'ANALYZE'

The command runs successfully using the Sqlite3.exe and improves performance no end.

We are using version 1.0.66.0 of System.Data.SQLite.

This issue asside we are well impressed by SQLite and the ADO provider. So fast.....

System.Data.SQLite and Windows Phone 7

simplyTom — Fri, 19 Feb 2010 16:16:14 GMT

Hello Robert,
let's say one thing first (like Wayne and Garth would say):
I am not worthy! I am not worthy!....

I really do love System.Data.SQLite, the day I found this Project I was smilling when I was driving home from work.
This is 2 years ago and I still know it ....
There is a lot of rumble in the jungle in the last couple of days.
There are rumours around stating that Windows Phone 7 will restrict both native development and Pinvoke native code.
http://www.theregister.co.uk/2010/02/18/windows_phone_compatability/
I love working with System.Data.SQLite on Windows Mobile because it is by far the best DB on this platform.
Way better then SQL Server Compact or however this crap is named today.
Do you know a little bit more?

Best regards,
Tom

SQLite optimization

yaronn01 — Tue, 31 Aug 2010 11:34:18 GMT

I consider to use System.Data.SQLite in a desktop application to persist my model. I plan to load all data to model classes when the user opens a project and write it again when the user saves it. I will write all data and not just the delta that changed (since it is hard for me to tell). The data may contain thousands of rows which I will need to insert. I am afraid that consecutive insertion of many rows will be slow (and a preliminary tests proves it). Are there any optimization best practices / tricks for such a scenario?

When to use SQLite (.Net)?

yaronn01 — Tue, 31 Aug 2010 18:14:45 GMT

Hi I build a desktop application which needs to save its state to disk. What are the advantages of using System.Data.SQLite over .Net serialization?

Strange Error in Linux, not Windows

Tink — Tue, 31 Aug 2010 14:45:09 GMT

Hello,

I have a really strange one I am hoping somebody with more Linux via Mono knowledge can help me with.

This code:

da.Update(ds, "Devices")

(da is the SQLite data adapter, ds is a DataSet)

Caused an error: Abort due to constraint violation
Devices.name may not be NULL

So here is the deal - the table (Devices) has a field called Name - it is an old object being saved, older than when 'Name' was a reserved word in many things, so I am not ignoring that it may be related to that, but there is another table in the same database with the same column name, and it works fine.

So your first thought is, "it has a null value, duh!" but that is not the case...

I compared the two tables and found that the only difference was that where the error happens, I have the "NOT NULL" on the column and in the other one I do not, but as I said this works FINE under Windows, just not on Linux via Mono.

So I am assuming (Robert) that you switch to Mono when it runs under that environment, and so the problem is likely in the binary distribution of SQLite for Linux, but I could not find anything in a search yesterday so I am hoping that some of the veterans here can help.

Thanks

Full Text Search

ali_salami — Thu, 26 Aug 2010 09:31:47 GMT

Im ALI AL SALAMI

I write very desktop applications with SQlite and C# or VB.NET

and I Understand that it's very nice

but in full text search has problem

Table1

-----------------

MySchool

School

OurSchool

in sql server if you have : MySchool and search

SELECT * FROM Table1 WHERE CONTAINS ( C1 , ' * School * ' )

return for you all record that in C1 has MySchool , OurSchool and ... too or Like this

but in SQLite

SELECT * FROM Table1 WHERE C1 MATCH ( ' * School * ' )

return only Shool

please Help me !!

Insert row through firewall

Chebron — Wed, 25 Aug 2010 22:41:49 GMT

Hi,

My problem is:

I'm tryin to register a new user (grab their name and when they installed a trial version) of a c# application. I have a sqlite db on a hosted web server however using sqliteconnection's encounter problems with firewalls.

Is there any way in sqlite I can insert a new row into a db over port 80 or by manipulating a http string?

about "Can't find PInvoke DLL 'SQLite.Interop.066.DLL'" issue on MIPS device

hobbybobo — Tue, 24 Aug 2010 14:35:38 GMT

I am trying to use SQLite.NET dowloaded from

http://sourceforge.net/projects/sqlite-dotnet2/files/SQLite%20for%20ADO.NET%202.0/1.0.66.0/SQLite-1.0.66.0-binaries.zip/download?use_mirror=nchc

in an application running on MIPS device , and sure have made all files correctly deployed, but always getting the error "Can't find PInvoke DLL 'SQLite.Interop.066.DLL'".

I suspect a 'SQLite.Interop.066.DLL' for MIPS must be complied for that use, isn't it?

Is there any other solution ?

Database locking and threads - exception

sten — Mon, 23 Aug 2010 09:11:36 GMT

I have a database that is accessed by two threads. One thread writes to the database and regular intervals while the other thread performs read operations. Each thread has its own connection pointer, and I have synchronized access even further by using lock. In short:

lock (syncObject)
{
using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
{
...
}
}

SyncObject is shared by the two threads.

Based on this I think I have followed what should be done when using multiple thread access to the database. However, from time to time I get an SQLite exception telling me that its trying to write an readonly database. Im guessing that the writer thread is trying to write a database that is still opened by the reader thread. The question is how is this possible?

Searching FAST on TEXT fields

Elisa — Mon, 23 Aug 2010 19:58:07 GMT

Hello, I have to do a search on about 3000 TEXT fields in one table. But It could also be a search on the last 10 years so its 30000 TEXT fields. Depends on the user behavior. The data that is in that TEXT field is a RichTextFormat string. How can I do that the fastest way?

Support for SQLite 3.7 and WAL?

ados34 — Mon, 23 Aug 2010 09:00:54 GMT

Hi,

I am testing the latest version of SQLite (3.7.0.1) and specifically the PRAGMA JOURNAL_MODE=WAL feature. When using ADO.NET if I set this pragma in the database, my app gives the error: "File opened that is not a database file, file is encrypted or is not a database". I assume this is because ADO.NET 1.0.66.0 does not support this new feature. If this is the case, I was wondering if there are any plans to release a new ADO.NET which will support WAL?

Cheers,

Ados

mcafee warning when downloading sqlite.net

biggles — Mon, 23 Aug 2010 08:59:42 GMT

Hi there

When I attempt to download SQLite.net from sourceforge my mcafee antivirus warns me that it is a potentially dangerous download. Other users have apparently flagged it as containing malware or adware. Has anyone else had this problem?

Thanks

biggles

DataType Mappings

Robert Simpson — Wed, 21 Sep 2005 14:51:30 GMT

Since the SQLite engine is inherently typeless and ADO.NET is much more strongly typed, the ADO.NET wrapper must make certain decisions about the columns returned in any given SQL query. One of the deciding factors is the underlying datatype declared in the CREATE TABLE statement.

The following lists the datatypes and mappings the SQLite ADO.NET wrapper understands. If there is anything missing, wrong, or needs adding, please let me know:

COUNTER	DbType.Int64
AUTOINCREMENT	DbType.Int64
IDENTITY	DbType.Int64
LONG	DbType.Int64
TINYINT	DbType.Byte
INTEGER	DbType.Int64
INT	DbType.Int32
VARCHAR	DbType.String
NVARCHAR	DbType.String
CHAR	DbType.String
NCHAR	DbType.String
TEXT	DbType.String
NTEXT	DbType.String
STRING	DbType.String
DOUBLE	DbType.Double
FLOAT	DbType.Double
REAL	DbType.Single
BIT	DbType.Boolean
YESNO	DbType.Boolean
LOGICAL	DbType.Boolean
BOOL	DbType.Boolean
NUMERIC	DbType.Decimal
DECIMAL	DbType.Decimal
MONEY	DbType.Decimal
CURRENCY	DbType.Decimal
TIME	DbType.DateTime
DATE	DbType.DateTime
TIMESTAMP	DbType.DateTime
DATETIME	DbType.DateTime
BLOB	DbType.Binary
BINARY	DbType.Binary
VARBINARY	DbType.Binary
IMAGE	DbType.Binary
GENERAL	DbType.Binary
OLEOBJECT	DbType.Binary
GUID	DbType.Guid
UNIQUEIDENTIFIER	DbType.Guid
MEMO	DbType.String
NOTE	DbType.String
LONGTEXT	DbType.String
LONGCHAR	DbType.String
SMALLINT	DbType.Int16
BIGINT	DbType.Int64
LONGVARCHAR	DbType.String
SMALLDATE	DbType.DateTime
SMALLDATETIME	DbType.DateTime

SQLiteDataAdapter.Fill Select from view

slepiec — Thu, 19 Aug 2010 13:35:48 GMT

hello, I have a problem with SQLiteDataAdapter.Fill when I select some data from view.
code: sqlString = @" SELECT Sprzet_ID,Grupa,NrRejestrowy,Nazwa,Stan_ID FROM Sprzet_view; ";
DataSet ds = new DataSet();
SQLiteDataAdapter da = new SQLiteDataAdapter(sqlString, DBConnection);
da.Fill(ds);
it's throw exception : SQLite error no such table: Sprzet_view but Sprzet_view is a view not a table and it exist. could someone help with this ?

I'd like to contribute my class and hope it included in future release ...

percyboy — Thu, 19 Aug 2010 08:48:42 GMT

Hi, Robert !

I'm trying to use the FTS extension. As you know, it is not supported Eastern-Asian language by default, unless to implement myself a custom Tokenizer according to 'ext/fts3/fts3_tokenizer.h' interface. I don't have much experience on C/C++. So I wrote it in C#, and finally it worked.

I wrote an abstract class something like below, and packed all P/Invokes internally:

public abstract class SQLiteFtsTokenizer
{
   public virtual string Name { get; }     //tokenizer name
   public void RegisterMe(SQLiteConnection connection) { ... }     //register tokenizer
   protected virtual void OnCreate(string tokenizerArgument) {}     //when tokenizer inits, the argument is from CREATE VIRTUAL TABLE
   protected virtual void OnDestroy() {}     //when tokenize destroy itself
   protected abstract void PrepareToStart();     //before tokenize starts
   protected abstract bool MoveNext();     //try to find next token, return false to indicate no token founded.
   protected string InputString { get; }     //the input string for tokenize
   protected string Token { get; set; }     //the finded token
   protected int TokenIndexInString { get; set; }     //token index in the input string
   protected int NextIndexInString { get; set; }     //the index where to tokenize from next-time
}

To implement a tokenizer, just need to inherit this class and fill custom codes in it.
To use a tokenizer, just register it after connection opened:

MyTokenizer t = new MyTokenizer();
SQLiteConnection connection = new SQLiteConnection("Data Source=xxxxx");
connection.Open();
t.RegisterMe(connection);
....

I'd like to contribute this class and hope it could be included in future release.
How do you think of it ?

Problem on Interop.dll

kumander — Wed, 18 Aug 2010 11:46:52 GMT

hello guys

i been developing windows mobile 5.0 vs.2005 c# and i got this error upon opening a connection using the sqllite.net "System.MissingMethodException: Can't find PInvoke DLL" i installed the sqlite.net version 1.0.66 and when i run my application that's the error i copied two files sqlllite.data.dll and the sqllite.interop.066.dll on the bin folder but still i got no luck..anyone suggestion

thanks

ADO RecordSet

epedder — Sat, 03 Jul 2010 03:55:47 GMT

Is it possible to get an ADO RecordSet from SQLite which is bi-directional and supports bookmarks?

Thanks,

- Elric

Unable to open database file

mk39 — Sun, 15 Aug 2010 21:52:02 GMT

Hi,

I have code that I am trying to open sqlite database, I am getting error "Unable to open database file". My path to database is correct. I am working on Windows mobile,

I am using VS 2008.

My code:
string s = "Data Source=" + AppPath() + "\\DB\\test.sqlite";
SQLiteConnection conn = new SQLiteConnection();
conn.ConnectionString = s ;
try
{
conn.Open(); //Error raises here
}
catch(Exception ex)
{

}

If any permission issue is there then please guide me how to do it?

Please help me to solve issue