Re: Concurrent database access in SQL 2005 Mobile

Thank you very much... I'll see if somebody can help... in the meantime I'll
try with the newer versions of SQLCE and in case they work fine we'll have to
discuss it with the commercial staff and with the customer... In the meantime
if there's anyone else who has faced a similar issue, I would appreciate any
help.

Thanks again for everything Ginny

"Ginny Caughey MVP" wrote:

Thanks for your detailed answers. Perhaps somebody with more OleDb
experience will spot something. I'm afraid that I don't. Your approach of
opening one connection for the lifetime of the app and keeping it open seems
to me that it should work to improve performance, and unless you aren't
committing your changes made on the other connection(s) I don't understand
why the changes aren't visible when you execute a new query. I don't recall
if this was a problem that has been fixed in a newer version of SQL CE, but
I do understand the issue of testing and rolling out that kind of change to
many different types of devices.

--

Ginny Caughey
Device Application Development MVP


"sardo007" <sardo007@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6B492C56-AF76-416F-A42E-697E55FD0382@xxxxxxxxxxxxxxxx
Hi Ginny thanks for the reply... I answer each question individually.

1. What version of SQL CE are you using? I'm assuming 3.1. If so, there
is
also 3.5 that is available, and 3.5 sp1 which is in beta and due to be
released very soon.

I'm using SQL CE 3.0 (2005 Mobile edition) I could have tried using newer
versions but we have more than one thousand users splitting more than one
country, with difficulties to connect (the application allows them to work
online and offline depending on the connection availability) and it would
not
be practical to change the database version. In case there's no other way
we'd have to assume the costs, but that would be the last resort. They use
more than 10 types of terminals with older and newer versions of Windows
Mobile 2005 and the siwtch would be very painful.

2. Are you accessing the database from an app written in C++? The OleDb
implementation for SQL CE is not a complete implementation and you'd get
more functionality using the managed code provider if you are writing in
a
managed code language.

Yes, the application is written in C++ Managed code is just not practical
for the application... It's been working since PocketPC 2003, we tried to
migrate it to CFW for the 2005 version and its just too slow in some of
the
devices we have in our customer's users. So it has to be just native code.
I
know Microsoft is encouraging everyone to move to .NET and we have done
son
in our web and PC applications, but mobile devices are still too slow
running
complex applications with .NET Wrapping the managed code driver into a COM
that I can use in the C++ application would eat up all the memory of the
device. I also posted an issue here (still not solved) about SQL CE eating
device's memory slowly through the application cycle, but since it seems
there's no solution we have worked around the biggest issues. I know the
OLEDB driver for SQL CE does not implement every function, but one of the
things it claims to solve is the multiuser access, so I hope this is
addresses correctly. I don't know it it's a bug or I'm just missing
something.

3. Are you accessing the database from multiple threads in any of the
apps?

The main application has more than one thread accessing the database, but
even disabling the second thread and leaving one thread per application
the
problem persists. The notifying application has only one thread accessing
data.

4. Are all the apps accessing the database on the same physical machine?

Yes, both applications are located in the same mobile device. The main
application is a form-based user-entry application. The notifying
application
receives data via remote connections, writes it to the database and makes
any
changes requested by the host application (deletions and updates)

5. How is the main app "looking" to see changes? Is it issuing a new
query
for example?

Yes. I uses a plain windows timer, since receiving notifications is not a
realtime issue. The user just has to know it has available data. When the
timer message is processed the application runs a query again the
notification table and in case it has new data, it notifies de user. The
user
then opens his work forms (they are normaly listviews) and it's in those
forms in which he still sees deleted data or does not see newly inserted
records. The notifying loop is also failing to detect the presence of new
notifications, but we had worked around it with a named event and a
blocking
thread.

It wouldn't seem to me that you'd need a new connection to
issue a new query, but I don't use OleDb against SQL CE data so I might
have
missed something.

The pplication was written in the SQCE 2003 style: only one connection and
everything done on it. So the connection was open at the beginning and
kept
that way. Both applications exchanged messages to close and open the
connection since they could not open it at the same time. Since SQLCE 2005
does not have that problem we changed the model and now they both use
their
own connections. One of the well-behaving things we learned was: open the
connection, make your tasks and then close it again. The problem with that
issue (it is the only one working well) is that's too slow (just on some
devices) while keeping one connection open all the time for everything or
just using a dummy connection and opening/closing the others triggers the
data visibility problem.

We can now reproduce it in a simpler way:

1- Open the main application
2- Delete some rows in a table using isql
3- Open the data form in main application... changes are invisible
(deleted
rows still there)

I had neveer seen this behaviour before...

Thank you very much



.

Relevant Pages

  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... So it is a kind of asynchronous commit which happens every 10 ... I am not sure of this claim as we have many applications built on SQL CE ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... Please stick to having an "dummy" connection in Open State. ... SQL CE/Mobile has a background thread that flushes the changes ... they run for long hours and we have not seen memory leaks so far. ... then opens his work forms and it's in ...
    (microsoft.public.sqlserver.ce)
  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
  • Re: User not associated with trusted SQL Server connection
    ... > using the osql utility. ... > associated with a trusted SQL Server connection. ... > database or is it just for that instance. ...
    (microsoft.public.sqlserver.security)
  • Re: Is it just me or are there BIG problems with SQLCE 3.0?
    ... has been done with parameterised SQL, ... bit of code into my test which closes and reopens the database ... opening and closing the DB connection is not a good idea. ... // Display all error messages ...
    (microsoft.public.sqlserver.ce)