[sqlite] System.Data.SQLite, Need alternate way to get field length

[Clemens Ladisch]

William Drago wrote:
> I am trying to determine the number of bytes in a blob. According to the help 
> file under SQLiteDataReader.GetBytes Method in the Remarks section:
>
> "To determine the number of bytes in the column, pass a null value for the 
> buffer. The total length will be returned."
>
> I'm working in VEE and VEE doesn't have a null keyword, so I tried 
> System.DBNull.Value and I get an error (signatures don't match).

If VEE (whatever that is) does not allow you to specify something that
ends up as a null reference, the you cannot use this mechanism.

> So, are there any other ways to get the number of bytes in a blob?

SELECT length(MyLittleBlob) FROM ...


Regards,
Clemens

[sqlite] Multiple instances of the same program accessing the same db file

[Richard Hipp]

On 5/2/15, Scott Doctor  wrote:
>
> Is the PRAGMA value the retry interval, or the timeout where it
> aborts and reports a failure?
>

The timeout.

-- 
D. Richard Hipp
drh at sqlite.org

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Doctor]

hmm, I am using sqlite as a project file that keeps track of a variety 
of information (usually a couple dozen megabytes in size per project). 
My initial post assumed a single user with a couple windows open. The 
file might be accessed by another user on a local area network. Usually 
no more than a few people at the same time for a specific project file. 
My program runs on windows and mac with a local area network connecting 
the computers.  Each computer has its own copy of the program, but may 
share the database file which may be located on any of the computers. So 
I am wondering whether I should implement my own locking logic in my 
program.

-
Scott Doctor
scott at scottdoctor.com
-

On 5/2/2015 6:10 PM, Scott Robison wrote:
> Since I'm not clear on whether your two or more
> processes are running on the same machine accessing a local drive or on
> multiple machines or over a network, keep in mind that network file systems
> are notoriously bad at the things SQLite needs (locking).

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Robison]

On Sat, May 2, 2015 at 7:03 PM, Scott Doctor  wrote:

>
> To review, after opening the database, issue the PRAGMA busy_timeout =  x,
> with x being however long I want to wait before aborting. I can keep both
> database handles open at the same time, but need to make sure I finalize
> the operation before the timeout happens (assuming the other program is
> also trying to access the database at the same time).
>

I haven't seen anyone give the typical warning about not doing this on a
network file system. Since I'm not clear on whether your two or more
processes are running on the same machine accessing a local drive or on
multiple machines or over a network, keep in mind that network file systems
are notoriously bad at the things SQLite needs (locking). If you haven't
yet read https://www.sqlite.org/whentouse.html it might be a good thing to
review. If I missed something and this is all old hat, my apolgoies for the
repetition.

-- 
Scott Robison

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Doctor]

To review, after opening the database, issue the PRAGMA busy_timeout =  
x, with x being however long I want to wait before aborting. I can keep 
both database handles open at the same time, but need to make sure I 
finalize the operation before the timeout happens (assuming the other 
program is also trying to access the database at the same time).

-
Scott Doctor
scott at scottdoctor.com
-

[sqlite] Possible bug with locking/retying

[Peter Aronson]

If you look here: http://beets.radbox.org/blog/, you can see the blog 
entry is dated August 24th, 2012.

Peter

On 5/2/2015 5:18 PM, Simon Slavin wrote:
> In searching for something else I came across this:
>
> 
>
> I don't like the fact that it's undated.  For all I know this is about a 
> three year old bug which has long since been patched.
>
> I understand the description, but not what SQLite does internally, and I 
> don't know whether it was ever reported to the SQLite dev team.  Would 
> someone like to take a look ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Doctor]

Is the PRAGMA value the retry interval, or the timeout where it 
aborts and reports a failure?



Scott Doctor
scott at scottdoctor.com
--

On 5/2/2015 5:08 PM, Simon Slavin wrote:
> On 3 May 2015, at 12:55am, J Decker  wrote:
>
>> Yes, it really requires only a little additional work on application side.
>> The native open will open it in read/write share allow, and handle
>> interlocking.
>>
>> if you get a result of SQLITE_BUSY you need to retry the operation after a
>> short time.
> Just to update J a little, you no longer need to handle the retry in your own 
> code.  SQLite has its own exponential-backoff-and-retry feature.  You set it 
> up using either C code or a PRAGMA, which have identical result:
>
> 
>
> 
>
> Do this with your connection handle after you open the database.  Set it to a 
> couple of minutes, or however long you want your program to keep retrying 
> before failing and reporting an error to the user.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>

[sqlite] SQLite using internally by Windows 10

[Gert Van Assche]

Great! Congrats!

2015-05-01 5:24 GMT+02:00 Richard Hipp :

> https://twitter.com/john_lam/status/593837681945092096
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Multiple instances of the same program accessing the same db file

[J Decker]

Yes, it really requires only a little additional work on application side.
The native open will open it in read/write share allow, and handle
interlocking.

if you get a result of SQLITE_BUSY you need to retry the operation after a
short time.

On Sat, May 2, 2015 at 4:52 PM, Scott Doctor  wrote:

>
> I am somewhat new to sqlite and am trying to decide an issue with the
> program I am writing (cross platform, written in C/C++). After reading
> through the sqlite documentation, I am still unsure about the issue how to
> implement multiple instances of the same program.
>
> Consider a program that may have more than one instance of the same
> program open at the same time. Both instances need to read/write the same
> sqlite database file.
>
> Can both instances open the same database file at the same time?
> Another way to word the question is whether sqlite will properly handle
> two independent programs accessing the same sqlite database file at the
> same time where both programs will be reading/writing to the database? Or
> do I need to implement or more complex strategy for accessing the sqlite
> file?
>
> --
>
> -
> Scott Doctor
> scott at scottdoctor.com
> -
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] Multiple instances of the same program accessing the same db file

[Scott Doctor]

I am somewhat new to sqlite and am trying to decide an issue with the 
program I am writing (cross platform, written in C/C++). After reading 
through the sqlite documentation, I am still unsure about the issue how 
to implement multiple instances of the same program.

Consider a program that may have more than one instance of the same 
program open at the same time. Both instances need to read/write the 
same sqlite database file.

Can both instances open the same database file at the same time?
Another way to word the question is whether sqlite will properly handle 
two independent programs accessing the same sqlite database file at the 
same time where both programs will be reading/writing to the database? 
Or do I need to implement or more complex strategy for accessing the 
sqlite file?

-- 

-
Scott Doctor
scott at scottdoctor.com
-

[sqlite] Thoughts about enhancing "PRAGMA quick_check" speed

[Simon Slavin]

On 2 May 2015, at 12:14pm, Jean-Marie CUAZ  wrote:

> Yes, a few years ago on a test db, used for developpment and testing purpose, 
> "PRAGMA quick_check" reported some anomalies

I would not continue to use any system which occasionally caused quick_check -- 
or more importantly integrity-check -- to fail.  If this happens you could lose 
many rows of a table or even multiple tables.  Worse still, pointers may be 
pointing at the wrong rows, meaning that a future UPDATE or DELETE FROM much 
affect the wrong rows.

In any properly working system even integrity-check (more thorough than 
quick-check) never fails, and neither should be necessary in anything except a 
rarely-used maintenance procedure.  They are both diagnostic tools for faults, 
not part of a smoothly running system.

If your quick-check reports a fault, you need to replace your hardware or 
investigate the problem until you've found a cause.  If it doesn't, then 
there's no point in running it and you can save your users from having to wast 
their time.

Simon.

[sqlite] Thoughts about enhancing "PRAGMA quick_check" speed

[Jean-Marie CUAZ]

Thank you for your answers.

To reply to Mr. Hipp and Mr. Morras :

Yes your are right, verifications made,  the process is I/O bound. (CPU 
activity jumps from 1% - 2% at iddle time to 5-6 % when a "PRAGMA 
quick_check" is processed). Thank you for pointing to me the right track !

About downloading latest trunk version with enhancements on this 
subject, thanks, the offer is appreciated, but unfortunately there is no 
skills in C langage here, so will wait the next release.

I can confirm mmap brings big improvements in speed processing of 
"PRAGMA quick_check" (roughly a 40% gain in time consumming).

"Login" in the app. is effectively the startup process. There is no 
client-server logic in the usual sense. If multi-user capability is 
needed (in the context of a small workgroup) on the same installation, 
each user launches a new and separate instance of the same application 
sharing the same db. (efforts are done to prevent concurency pb).


To answer to Mr Slavin :

Yes, a few years ago on a test db, used for developpment and testing 
purpose, "PRAGMA quick_check" reported some anomalies (didn't had the 
time to investigate further - but hardware failure suspected). What 
surprised me was, despite the presence of (few) anomalies reported, the 
apparent behaviour seemed "normal". I suppose that if the corrupted 
pages are not visited by the library (for example data rarely processed 
like "historical" data), everything seems "OK". I concluded that a 
"running" db with some pb at the file level can live (for a random 
limited time) with no visible issue .

Your question on the good time to process a check on the db file sounds 
like a "phylosophical" question not easy to answer.
Why doing this check before calculus processing is preferable to, for 
example, before/after an update on an employee's personnal address ? 
Would the db be more inusable only in the first case ?

Here, the checks are done at minima and guided by top-most priorities 
(backup and restore use the Tcl API of Sqlite and are managed at 
application level, as some process ask for a backup made before) :
1) garantee a restore operation allways gives an sane db
2) don't allow a backup if db file have pb (wasted time + dangerous 
illusion)
3) check the db file at minima once per usage.

I agree that a db check at user log-out time is logically equivalent as 
processed during log-in time and provides a better user experience.

But we have several pb here :
- there is no garantee that the user exiting the application will be the 
first one who logs in on next startup.
- the application can process automatic log-out (depending on elapsed 
time of user inactivity).
- In case the result of a quick_check will not be OK, it is desired that 
someone takes appropriate action as soon as possible.


Jean-Marie

[sqlite] SQLite using internally by Windows 10

[Jim Callahan]

Any details on SQLite in Windows 10?

Don't remember anything in MS Build video cast last week.
Closest, I could find were these two old posts:

1. USING SQLITE IN WINDOWS 10 UNIVERSAL APPS
http://igrali.com/2015/05/01/using-sqlite-in-windows-10-universal-apps/

2. SQLite.Net-PCL  (Portable Code Library fork)
https://github.com/oysteinkrog/SQLite.Net-PCL

Jim Callahan

On Thu, Apr 30, 2015 at 11:24 PM, Richard Hipp  wrote:

> https://twitter.com/john_lam/status/593837681945092096
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] System.Data.SQLite, Need alternate way to get field length

[William Drago]

All,

I am trying to determine the number of bytes in a blob. 
According to the help file under SQLiteDataReader.GetBytes 
Method in the Remarks section:

"To determine the number of bytes in the column, pass a null 
value for the buffer. The total length will be returned."

I'm working in VEE and VEE doesn't have a null keyword, so I 
tried System.DBNull.Value and I get an error (signatures 
don't match).

I've tried a few other things, but VEE rejects everything 
that isn't a byte array or pointer to a byte array. I don't 
know if I can blame VEE. The method signature says byte[], 
so VEE is expecting to see a byte array or pointer to a byte 
array.

So, are there any other ways to get the number of bytes in a 
blob?

Thanks,
-Bill