Re: Concurrent database access in SQL 2005 Mobile

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: Manipulating MS Access records with excel VBA + ADO
    ... While I cannot see how a working example of a connection to a database is ... Select the control button view dataor edit query. ... Look for SQL button to get the SQL ...
    (microsoft.public.excel.programming)
  • 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)