headers
markus ruettimann | 15 Aug 2012 17:19

SQLITE in a Win7 Service Application

Hi,
We are facing problems when the sqlite database () is used within an 
application that runs as a Win7 service under the local system account. 
The application is written in Java and we use the sqlite-jdbc-3.7.2 JDBC 
driver.

How ever we can create a new database, reading and writing to it withount 
any problem. The only thing that is not working is the ATTACH DATABASE SQL 
command. We constantly receive a CAN_NOT_OPEN error code.

When we start the same application under a local user everything works 
fine. I have to add the both databases that are to be attached are in the 
same folder and have been created by the Application itself. There are no 
access restrictions on the database folders or files.

Does anyone know about this and / or has a solution?

Kind regards
Markus
Permalink | Reply |
headers
Simon Slavin | 15 Aug 2012 17:33

Re: SQLITE in a Win7 Service Application


On 15 Aug 2012, at 4:19pm, markus ruettimann
<markus.ruettimann@...> wrote:

> How ever we can create a new database, reading and writing to it withount 
> any problem. The only thing that is not working is the ATTACH DATABASE SQL 
> command. We constantly receive a CAN_NOT_OPEN error code.

Thank you for your good description of the problem, and what similar things /do/ work.

The usual cause of this is that you specify the database filename only, not which folder it's in, and the
application is using a folder you weren't expecting.  Is there a chance that this is your problem ?  Can you
temporarily change the application to specify the full path, from 'C:' on down, just to check that it is
looking in the right folder ?

Another cause is that inside your ATTACH command you have used the wrong quotes, or no quotes at all around
the filename.  For instance

ATTACH "second.sqlite" AS db2

is wrong, you should be using single quotes, not double quotes.

Simon.
Permalink | Reply |
headers
Mohd Radzi Ibrahim | 15 Aug 2012 17:35
Picon

Re: SQLITE in a Win7 Service Application

On Wed, Aug 15, 2012 at 11:19 PM, markus ruettimann <
markus.ruettimann@...> wrote:

> Hi,
> We are facing problems when the sqlite database () is used within an
> application that runs as a Win7 service under the local system account.
> The application is written in Java and we use the sqlite-jdbc-3.7.2 JDBC
> driver.
>
> How ever we can create a new database, reading and writing to it withount
> any problem. The only thing that is not working is the ATTACH DATABASE SQL
> command. We constantly receive a CAN_NOT_OPEN error code.
>
> When we start the same application under a local user everything works
> fine. I have to add the both databases that are to be attached are in the
> same folder and have been created by the Application itself. There are no
> access restrictions on the database folders or files.
>
> Does anyone know about this and / or has a solution?
>
>
Could you check the security issue or path for the attached database.
Permalink | Reply |
headers
Teg | 15 Aug 2012 17:36
Favicon

Re: SQLITE in a Win7 Service Application

Hello markus,

Every Windows programmer needs to have "Procmon" installed on their
system so, they can watch their program's disk IO. I'd suggest
installing it, then using a filter to watch just your service. Then,
you can simply watch what disk IO is failing. I use it on a daily
basis. Almost as much as my debugger.

It'll probably show you a permission problem.

C

...
Test.exe 6312 ReadFile        E:\Test\Db.db3    SUCCESS Offset: 0, Length: 100, Priority: Normal
Test.exe 6312 ReadFile        E:\Test\Db.db3    SUCCESS Offset: 0, Length: 4,096, I/O Flags: Non-cached, Paging
I/O, Priority: Normal
Test.exe 6312 QueryOpen       E:\Test\Db.db3    SUCCESS CreationTime: 6/19/2012 9:06:58 PM, LastAccessTime:
Test.exe 6312 LockFile        E:\Test\Db.db3    SUCCESS Exclusive: True, Offset: 1,073,741,824, Length: 1, Fail
Immediately: True
Test.exe 6312 LockFile        E:\Test\Db.db3    SUCCESS Exclusive: False, Offset: 1,073,741,826, Length: 510,
Fail Immediately: True
Test.exe 6312 UnlockFileSingle E:\Test\Db.db3    SUCCESS Offset: 1,073,741,824, Length: 1
Test.exe 6312 QueryOpen       E:\Test\Db.db3-journal    SUCCESS CreationTime: 6/19/2012 9:06:58 PM,
Test.exe 6312 QueryStandardInformationFile E:\Test\Db.db3    SUCCESS AllocationSize: 106,496,
EndOfFile: 106,496, NumberOfLinks: 1,
Test.exe 6312 QueryOpen       E:\Test\Db.db3-wal  NAME NOT FOUND
Test.exe 6312 QueryStandardInformationFile    E:\Test\Db.db3    SUCCESS AllocationSize: 106,496,
EndOfFile: 106,496,
Test.exe 6312 ReadFile        E:\Test\Db.db3    SUCCESS Offset: 0, Length: 8,192
...
(Continue reading)

Permalink | Reply |
headers
Robert Myers | 15 Aug 2012 17:39

Re: SQLITE in a Win7 Service Application

If you're using impersonation, you'll run into problems doing any 
updates during the impersonation when sqlite tries to create any 
temporary files or reopen any it has closed.

You can use Process Monitor from Sysinternals (now Microsoft) to see 
exactly what the failure is with what file.

On 8/15/2012 10:19 AM, markus ruettimann wrote:
> Hi,
> We are facing problems when the sqlite database () is used within an
> application that runs as a Win7 service under the local system account.
> The application is written in Java and we use the sqlite-jdbc-3.7.2 JDBC
> driver.
>
> How ever we can create a new database, reading and writing to it withount
> any problem. The only thing that is not working is the ATTACH DATABASE SQL
> command. We constantly receive a CAN_NOT_OPEN error code.
>
> When we start the same application under a local user everything works
> fine. I have to add the both databases that are to be attached are in the
> same folder and have been created by the Application itself. There are no
> access restrictions on the database folders or files.
>
> Does anyone know about this and / or has a solution?
>
> Kind regards
> Markus
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@...
(Continue reading)

Permalink | Reply |
headers
Keith Medcalf | 15 Aug 2012 22:31

Re: SQLITE in a Win7 Service Application

Make sure account SYSTEM or NT SYSTEM AUTHORITY has change access to the directories containig the
database files.  Note also that local system has no acces to network shares drives or files

Sent from Samsung Mobilemarkus ruettimann <markus.ruettimann <at> trapezegroup.com> wrote:
_______________________________________________
sqlite-users mailing list
sqlite-users <at> sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Permalink | Reply |
headers
markus ruettimann | 29 Aug 2012 09:30

Re: SQLITE in a Win7 Service Application [SOLVED]

Hi,
Thank you for your assistance. We have solved the below mentioned problem:

The reason was that the temp directory of the service did not exist - so 
sqlite was not able to create temp files in that non-existing directory.

We checked the 'TMP' and/or 'TEMP' environment variables of the service 
and noticed that the configured directory did not exist. 
Our installer does now ensure that the temp directory of the service user 
exists during the installation of our application.

=> For Java JDBC users: Please note that it is NOT sufficient to set the 
temp directory in the 'java.io.tmpdir'  settings since sqlite is an 
attached dll and uses the %TMP% or %TEMP% environment variable for that 
purpose. 

Hope this helps others with the same problem

Regards Markus

Von:    markus ruettimann <markus.ruettimann@...>
An:     sqlite-users@...
Datum:  15.08.2012 17:20
Betreff:        [sqlite] SQLITE in a Win7 Service Application
Gesendet von:   sqlite-users-bounces@...

Hi,
We are facing problems when the sqlite database () is used within an 
application that runs as a Win7 service under the local system account. 
The application is written in Java and we use the sqlite-jdbc-3.7.2 JDBC
(Continue reading)

Permalink | Reply |

Gmane