OpenOffice, Go-OO, ODBC, Offline Data Entry

Hello,

I am new to the mailing list here, so I'd like to introduce myself
quickly. My name is Casey Quibell, and I am also rather new to the
concept of programming a database (Or in this case, more specifically
using an ODBC connector to tie OpenOffice into it.)

Just a bit of technical preamble. While I am currently implementing
this solution using Go-OO and MySQL, I am not tied to them. I like
PostgreSQL as well, and there is little wrong with OpenOffice. Hell, if
there are recommendations to be made even beyond these software
selections I am listening.

I have a project I have been commissioned to complete, and while the
majority of it seems very simple and straight-forward to me, there
remains an issue that has me concerned. Simply put, this agency which
needs a database, has a large number of paper forms they have to fill
out on a regular basis. Many of these forms ask the same questions over
and over again. Using an ODBC connector and OpenOffice, I am able to
create .pdf files that resemble very closely the forms they are already
used to. By entering information into these forms, they are able to have
the information parse directly into a SQL database which they can then
create reports from later (A very important ability for them).

In addition they are able to bring up an empty form to fill out, and if
the relevant individual being processed is already in the system, it
will fill out most of the 2nd form's fields for them; leaving only the
remaining fields which are new and specific to the 2nd form. All of this
is a massive time saver for them.

Here is the catch. The SQL database itself exists at a 'home office',
and these workers are wanting to be able to enter this data remotely; in
an 'offline mode' as it were. Because they are required to fill out
these forms away from the office, I have a logistical problem. If they
could have WIFI or Internet access while away from the office, I would
simply configure a VPN solution for them. However this is not something
I can do; where they must go frequently has no Internet access at all,
and a cell phone tether with a dataplan would just be inordinately
expensive for this particular group.

What options do I have available to me? I realize that while these
workers are on-site, there is likely very little that can be done as far
as the forms 'pulling' data from the office SQL database to auto-fill
fields, but what about the other way around? I.e. Them going off-site,
filling out the necessary forms, and then once they can get back to the
office, upload the data to the database then?

I don't know if maybe having a local SQL database running on each
worker laptop which could somehow 'sync' the data would be a viable
option or not... That is something I have never tried before. One option
as far as that route goes that I have found is an application called
Pervasync: http://www.pervasync.com/ which claims to be able to sync
database material in this manner. However, it is commercial, and I am
quite devoted to using only Open Source software.

So far, it almost seems like I might have to tell the clients that it
is simply not even possible what they are asking for. That they may have
to enter the data twice: Once on-site, and a 2nd time when they get back
to the office. Essentially copying the offline data, into the 'live'
forms essentially.

Thank you for your time and consideration. I appologize if my post was
a little long, but I really wanted to try and be as clear as I could be
as to my over-all intent, as the possible solutions are so very vague to
me.

Regards,
Casey Quibell,



--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: OpenOffice, Go-OO, ODBC, Offline Data Entry

Hi Casey,

What you are asking to do is, as you say, difficult. Replication won't =
work as you can only update to the master rather than to the roaming =
slaves. On the other hand, MySQL Cluster might be an option. Check out =
http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster.html and see if =
it's potentially suitable.

Technically I think your request is probably off-topic as this is the =
list for MyODBC, the MySQL ODBC connector - but you may find some =
helpful responses all the same.

Kind regards,

Al McNicoll
Integritec (UK) Limited


-----Original Message-----
From: Lord_Devi [mailto:lorddevi@gmail.com]=20
Sent: 18 August 2010 06:44
To: myodbc@lists.mysql.com
Subject: OpenOffice, Go-OO, ODBC, Offline Data Entry

Hello,

I am new to the mailing list here, so I'd like to introduce myself
quickly. My name is Casey Quibell, and I am also rather new to the
concept of programming a database (Or in this case, more specifically
using an ODBC connector to tie OpenOffice into it.)

Just a bit of technical preamble. While I am currently implementing
this solution using Go-OO and MySQL, I am not tied to them. I like
PostgreSQL as well, and there is little wrong with OpenOffice. Hell, if
there are recommendations to be made even beyond these software
selections I am listening.

I have a project I have been commissioned to complete, and while the
majority of it seems very simple and straight-forward to me, there
remains an issue that has me concerned. Simply put, this agency which
needs a database, has a large number of paper forms they have to fill
out on a regular basis. Many of these forms ask the same questions over
and over again. Using an ODBC connector and OpenOffice, I am able to
create .pdf files that resemble very closely the forms they are already
used to. By entering information into these forms, they are able to have
the information parse directly into a SQL database which they can then
create reports from later (A very important ability for them).

In addition they are able to bring up an empty form to fill out, and if
the relevant individual being processed is already in the system, it
will fill out most of the 2nd form's fields for them; leaving only the
remaining fields which are new and specific to the 2nd form. All of this
is a massive time saver for them.

Here is the catch. The SQL database itself exists at a 'home office',
and these workers are wanting to be able to enter this data remotely; in
an 'offline mode' as it were. Because they are required to fill out
these forms away from the office, I have a logistical problem. If they
could have WIFI or Internet access while away from the office, I would
simply configure a VPN solution for them. However this is not something
I can do; where they must go frequently has no Internet access at all,
and a cell phone tether with a dataplan would just be inordinately
expensive for this particular group.

What options do I have available to me? I realize that while these
workers are on-site, there is likely very little that can be done as far
as the forms 'pulling' data from the office SQL database to auto-fill
fields, but what about the other way around? I.e. Them going off-site,
filling out the necessary forms, and then once they can get back to the
office, upload the data to the database then?

I don't know if maybe having a local SQL database running on each
worker laptop which could somehow 'sync' the data would be a viable
option or not... That is something I have never tried before. One option
as far as that route goes that I have found is an application called
Pervasync: http://www.pervasync.com/ which claims to be able to sync
database material in this manner. However, it is commercial, and I am
quite devoted to using only Open Source software.

So far, it almost seems like I might have to tell the clients that it
is simply not even possible what they are asking for. That they may have
to enter the data twice: Once on-site, and a 2nd time when they get back
to the office. Essentially copying the offline data, into the 'live'
forms essentially.

Thank you for your time and consideration. I appologize if my post was
a little long, but I really wanted to try and be as clear as I could be
as to my over-all intent, as the possible solutions are so very vague to
me.

Regards,
Casey Quibell,





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

Re: OpenOffice, Go-OO, ODBC, Offline Data Entry

On 8/18/2010 1:44 AM, Lord_Devi wrote:
> Hello,
>
> I am new to the mailing list here, so I'd like to introduce myself
> quickly. My name is Casey Quibell, and I am also rather new to the
> concept of programming a database (Or in this case, more specifically
> using an ODBC connector to tie OpenOffice into it.)
>
> Just a bit of technical preamble. While I am currently implementing
> this solution using Go-OO and MySQL, I am not tied to them. I like
> PostgreSQL as well, and there is little wrong with OpenOffice. Hell, if
> there are recommendations to be made even beyond these software
> selections I am listening.
>
> I have a project I have been commissioned to complete, and while the
> majority of it seems very simple and straight-forward to me, there
> remains an issue that has me concerned. Simply put, this agency which
> needs a database, has a large number of paper forms they have to fill
> out on a regular basis. Many of these forms ask the same questions over
> and over again. Using an ODBC connector and OpenOffice, I am able to
> create .pdf files that resemble very closely the forms they are already
> used to. By entering information into these forms, they are able to have
> the information parse directly into a SQL database which they can then
> create reports from later (A very important ability for them).
>
> In addition they are able to bring up an empty form to fill out, and if
> the relevant individual being processed is already in the system, it
> will fill out most of the 2nd form's fields for them; leaving only the
> remaining fields which are new and specific to the 2nd form. All of this
> is a massive time saver for them.
>
> Here is the catch. The SQL database itself exists at a 'home office',
> and these workers are wanting to be able to enter this data remotely; in
> an 'offline mode' as it were. Because they are required to fill out
> these forms away from the office, I have a logistical problem. If they
> could have WIFI or Internet access while away from the office, I would
> simply configure a VPN solution for them. However this is not something
> I can do; where they must go frequently has no Internet access at all,
> and a cell phone tether with a dataplan would just be inordinately
> expensive for this particular group.
>
> What options do I have available to me? I realize that while these
> workers are on-site, there is likely very little that can be done as far
> as the forms 'pulling' data from the office SQL database to auto-fill
> fields, but what about the other way around? I.e. Them going off-site,
> filling out the necessary forms, and then once they can get back to the
> office, upload the data to the database then?
>
> I don't know if maybe having a local SQL database running on each
> worker laptop which could somehow 'sync' the data would be a viable
> option or not... That is something I have never tried before. One option
> as far as that route goes that I have found is an application called
> Pervasync: http://www.pervasync.com/ which claims to be able to sync
> database material in this manner. However, it is commercial, and I am
> quite devoted to using only Open Source software.
>
> So far, it almost seems like I might have to tell the clients that it
> is simply not even possible what they are asking for. That they may have
> to enter the data twice: Once on-site, and a 2nd time when they get back
> to the office. Essentially copying the offline data, into the 'live'
> forms essentially.
>
> Thank you for your time and consideration. I appologize if my post was
> a little long, but I really wanted to try and be as clear as I could be
> as to my over-all intent, as the possible solutions are so very vague to
> me.
>
> Regards,
> Casey Quibell,
>
>
>

Hello Casey,

You need to have two sets of your data. Central and Remote. The Remote
data will have all of the field information in it but possibly none of
the filled out forms (to save space). Then as the field person adds data
to their forms, they start filling in their local copy of the dataset.

Normal MySQL replication will not help you get the data from your Remote
databases back into your Central database. The flow would be wrong.

What you will need to design is a "resync" script/process that the
remote worker uses when they return to the home network. This would then
upload and merge any new forms to the Central dataset along with any
corrections to the personal data that they may have needed to make in
the field. That way the next time the remote worker leaves the home
network, their "fill in the blanks" information will be as complete as
possible.

You might consider conditionally loading certain rows of completed form
data into the remote worker's dataset before they need to "go remote"
just so that they have that data for reference in the field.

How you designate new or changed rows of data is completely up to you.
Some rely on timestamps, some rely on version numbers, some rely on a
flag (0=unchanged, 1=changed). You may need a combination of these
techniques (timestamps for the Central data, flag for the remote data).
Find out what works best for your situation.

Overall, it comes down to a balancing act between what your remote
agents need away from the central system, how much space you want to
consume on their local devices, and how much exposure you want to risk
should a remote machine fall into the wrong hands (you may want to
encrypt part or all of the remote dataset using a key provided by the
remote agent during login to their application, for example)

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

RE: OpenOffice, Go-OO, ODBC, Offline Data Entry

Don't know if I completely understand what you are doing here but here
is what occurs to me. When the client is on-site/off-line perhaps the
form filling application can allow them to enter as much as they can but
not the stuff that could be filled in from the database if they had
access. They then save the partial details (Just data not the full form)
in some form like CSV, XML etc.

When back in the "office" and having access to the database, provide a
facility where they can call up the saved data entered off-line, fill in
the form, retrieve as necessary from the database to complete it.

John Bonnett

-----Original Message-----
From: Lord_Devi [mailto:lorddevi@gmail.com]=20
Sent: Wednesday, 18 August 2010 3:14 PM
To: myodbc@lists.mysql.com
Subject: OpenOffice, Go-OO, ODBC, Offline Data Entry

Hello,

I am new to the mailing list here, so I'd like to introduce myself
quickly. My name is Casey Quibell, and I am also rather new to the
concept of programming a database (Or in this case, more specifically
using an ODBC connector to tie OpenOffice into it.)

Just a bit of technical preamble. While I am currently implementing
this solution using Go-OO and MySQL, I am not tied to them. I like
PostgreSQL as well, and there is little wrong with OpenOffice. Hell, if
there are recommendations to be made even beyond these software
selections I am listening.

I have a project I have been commissioned to complete, and while the
majority of it seems very simple and straight-forward to me, there
remains an issue that has me concerned. Simply put, this agency which
needs a database, has a large number of paper forms they have to fill
out on a regular basis. Many of these forms ask the same questions over
and over again. Using an ODBC connector and OpenOffice, I am able to
create .pdf files that resemble very closely the forms they are already
used to. By entering information into these forms, they are able to have
the information parse directly into a SQL database which they can then
create reports from later (A very important ability for them).

In addition they are able to bring up an empty form to fill out, and if
the relevant individual being processed is already in the system, it
will fill out most of the 2nd form's fields for them; leaving only the
remaining fields which are new and specific to the 2nd form. All of this
is a massive time saver for them.

Here is the catch. The SQL database itself exists at a 'home office',
and these workers are wanting to be able to enter this data remotely; in
an 'offline mode' as it were. Because they are required to fill out
these forms away from the office, I have a logistical problem. If they
could have WIFI or Internet access while away from the office, I would
simply configure a VPN solution for them. However this is not something
I can do; where they must go frequently has no Internet access at all,
and a cell phone tether with a dataplan would just be inordinately
expensive for this particular group.

What options do I have available to me? I realize that while these
workers are on-site, there is likely very little that can be done as far
as the forms 'pulling' data from the office SQL database to auto-fill
fields, but what about the other way around? I.e. Them going off-site,
filling out the necessary forms, and then once they can get back to the
office, upload the data to the database then?

I don't know if maybe having a local SQL database running on each
worker laptop which could somehow 'sync' the data would be a viable
option or not... That is something I have never tried before. One option
as far as that route goes that I have found is an application called
Pervasync: http://www.pervasync.com/ which claims to be able to sync
database material in this manner. However, it is commercial, and I am
quite devoted to using only Open Source software.

So far, it almost seems like I might have to tell the clients that it
is simply not even possible what they are asking for. That they may have
to enter the data twice: Once on-site, and a 2nd time when they get back
to the office. Essentially copying the offline data, into the 'live'
forms essentially.

Thank you for your time and consideration. I appologize if my post was
a little long, but I really wanted to try and be as clear as I could be
as to my over-all intent, as the possible solutions are so very vague to
me.

Regards,
Casey Quibell,




--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

Re: OpenOffice, Go-OO, ODBC, Offline Data Entry

I faced a similar problem a while back. The solution I came up with
is to have two extra columns - the first is a timestamp field, and the
second a last change field. The timestamp field just records the date
and time of the last change, and the last change field records the
type of change - (A)dd, (C)hange, or (D)elete. In this scenario,
deletes (if allowed) are just flagged but not done immediately, and
the active records are selected by simply doing a "SELECT * FROM blah
WHERE last_changed <> 'D'". Of course, ofr this to work, each record
has to have a unique key - adding an IDENTITY field works nicely.

Synchronization is trivial at this point. :)

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Re: OpenOffice, Go-OO, ODBC, Offline Data Entry

On Wed, 2010-08-18 at 09:07 -0400, Shawn Green (MySQL) wrote:
> Hello Casey,
>
> You need to have two sets of your data. Central and Remote. The Remote
> data will have all of the field information in it but possibly none of
> the filled out forms (to save space). Then as the field person adds data
> to their forms, they start filling in their local copy of the dataset.
>
> Normal MySQL replication will not help you get the data from your Remote
> databases back into your Central database. The flow would be wrong.
>
> What you will need to design is a "resync" script/process that the
> remote worker uses when they return to the home network. This would then
> upload and merge any new forms to the Central dataset along with any
> corrections to the personal data that they may have needed to make in
> the field. That way the next time the remote worker leaves the home
> network, their "fill in the blanks" information will be as complete as
> possible.
>
> You might consider conditionally loading certain rows of completed form
> data into the remote worker's dataset before they need to "go remote"
> just so that they have that data for reference in the field.
>
> How you designate new or changed rows of data is completely up to you.
> Some rely on timestamps, some rely on version numbers, some rely on a
> flag (0=unchanged, 1=changed). You may need a combination of these
> techniques (timestamps for the Central data, flag for the remote data).
> Find out what works best for your situation.
>
> Overall, it comes down to a balancing act between what your remote
> agents need away from the central system, how much space you want to
> consume on their local devices, and how much exposure you want to risk
> should a remote machine fall into the wrong hands (you may want to
> encrypt part or all of the remote dataset using a key provided by the
> remote agent during login to their application, for example)
>


Thank you very much for the advice Shawn. I see what you mean. At least
now I know it is possible to achieve. I have zero experience with this
kind of direct database programing myself, so this is likely to be a bit
of a challenge for me. I don't suppose you might be able to recommend
any online reading material to me which might speed along the learning
process for me in regards to the creation of such a script?


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org