Disconnected Remote Issues

VB400 · Jan 20, 2000

This is an issue that started in an earlier thread (probably the best thread I've seen so far). The issue is what happens when in a multi-tier environment, the front-end (client workstation) is disconnected from the Data-Tier (Application Server). We would like to keep the application running so that we're not losing valuable business while communication lines are down. 
 
If you have any experience or thoughts on this issue, please feel free to post them so others can learn. 
 
Thanks!

VB400 · Jan 20, 2000

Steve Meier, 
 
... Continued from another thread 
 
Obviously, some part of your application will need to know whether to get the data from the "central" database or from the "temporary local" database. In which tier will this take place? 
 
You're definitely closer to it than I'm but how about generating the unique id at the time you sync -- you wouldn't need to check out any numbers! 
 
Thanks!

Snaggs · Jan 20, 2000

VB400, 
 
Funny that you mention that because that was my first thought. Just resolve the issue when you sync the two together. However this brought up a valid discussion that went something like this: 
 
The field personnel log an incident report from a caller. The caller is given an incident number (not from the pool of numbers checked out) and their account is serviced. At the time of syncing to the master, the number is changed so now there is no reference of the incident number. 
 
You can get around this a couple of ways. One way is to check the numbers out prior to going on the road, or not use a primary key as the incident number. If you don't use the PK as the incident number, then you have to have some way of uniquely generating a key, based on the specific machine. Maybe use the name of the machine for the ID, however that makes for funny incident report numbers. 
 
In the case where the ID is unimportant, I feel that your proposed solution would be feasible. 
 
As far as saving the data, MS-Access with a similar scaled down schema as the master would have to reside on the users machine. The only problem with this is that now you have to setup ODBC DSN's or move your Data Centric layer to the users machine. I'm just thinking out loud here. 
 
Since we haven't gotten to this phase of the project yet, all these ideas are speculative. If you come up with something better than that, let me know since a lot of this is just theory that hasn't been tested. 
 
 
Steve 
<A HREF="mailto:sdmeier@jcn1.com">sdmeier@jcn1.com</A>

calahans · Jan 21, 2000

VB400, 
 
I was wondering, are you inserting new data into your database or are you amending existing records? If you are inserting new records have you thought about using disconnected recordsets? 
 
Open a connection to your original source when connected. Then break the connection by setting actriveconnection = nothing. Write the recordset to the hard drive. Then when your guys go out on site open the recordset from the harddrive start inserting and close the recordset (by saving to hard drive again. 
 
When the guys are back in the office again they can update the live database from their disconnect recordset. There are a few problems with this, ie unique identifiers - but the unique ID doesn't have to be an autonumber. 
 
If you are looking for code I can post it here. 
 
Cal

Snaggs · Jan 21, 2000

Cal, 
 
Sure, go ahead and post the code if you'd like. Who knows what might come of it. In our project, we did use the PK as the ID for the entity. However, it has several Foreign keys in many other related tables. We would be inserting new records and amending current ones. This makes things a little more tricky. I'd still like to look at the code though. Thanks for the offer! 
 
Steve 
<A HREF="mailto:sdmeier@jcn1.com">sdmeier@jcn1.com</A>

VB400 · Jan 21, 2000

Hi Steve, 
 
It sounds to me like you're on the right track. 
 
In earlier posts in the other thread, I mentioned that we use a Proxy tier that resides on the user's desktop and acts as a messenger between the Data-Centric and UI-Centric tiers. All it does (initially) is receive the request from the UI-Centric object and route the request to the Data-Centric counterpart and vice-versa. This Proxy tier also acts as a data caching "server". 
 
This tier can also be used to call a "local version" of the Data-Centric tier. For example, 
 
If ConnectionIsAlive then 
Set mobjPersist = CreateObject("MainDB.MyObjectPersist", SERVERNAME) 
Else 
Set mobjPersist = CreateObject("LocalDB.MyObjectPersist", MYMACHINE) 
End If 
 
Basically, if the connection to the server is alive then get the data from there; otherwise, get it from the local workstation. 
 
Of course, this would mean that you would have to have a version of the Data-Centric tier on the user's workstation, but that shouldn't be a problem. 
 
The beauty of this is that the UI and the UI-Centric tiers are uneffected by all of this. 
 
If this sound reasonable to you then let me know so that we could possibly explore the details together -- two heads are better then one, mine anyway

As with your situation, we're not doing this yet but will start the design soon and would like to start on the right path. 
 
Thanks! 
Tarek

VB400 · Jan 21, 2000

Cal, 
 
We have not started the design of this process yet. I like your idea to use disconnected recordsets and I will have to think about that solution. Go ahead and post part of the code so that we can get a clearer picture. 
 
One question: which tiers do you use to write and read the disconnected recordsets? 
 
I haven't worked with disconnected recordsets before but I'll take a guess on how this works. In the following section I'm ignoring the saving and retrieval of the disconnected recordset and just taking a stab at how the general flow of disconnected recordsets work -- correct me where I'm wrong: 
 
Data-Centric tier (application server machine): 
 
- Create the recordset 
- Disconnect the recordset 
- Serialize the data into a string variable 
- Send the string variable to the caller 
 
UI-Centric tier or Proxy tier (user machine): 
 
- Create an empty recordset 
- Deserialize the string variable into the recordset 
- Convert the recordset data into the Business Object (class) 
 
Tarek

calahans · Jan 28, 2000

Hi, 
 
In relation to the design model you outlined, its pretty much the design I would outline, however I would not serialise the string for passover between layers. I would propose the follwing: 
Data-Centric tier (application server machine): 
 
- Create the recordset 
- Disconnect the recordset 
- Send the recordset to the caller 
 
UI-Centric tier or Proxy tier (user machine): 
 
- Convert the recordset data into the Business Object (class) 
 
The recordset is disconnect, so the data would move between components in much the same manner as the string would. It would not have to call back to the component continuosly as the data is static. 
 
A small sample of code: 
 
 
'DAL 
 
Dim Cn As New ADODB.Connection 
Dim rs As New ADODB.Recordset 
 
' Open connection. 
Cn.Open "pubs", "sa" 
 
' Open titles your table. 
rs.Open "select * from titles", Cn, adOpenDynamic, adBatchOptimistic 
 
'Connection now broke 
set rs.ActiveConnection = nothing 
 
'pass your recordset back 
functionname = rs 
 
'''''UI 
CLIENT SIDE 
 
'call function is an ADODB.Recordset 
callfunction = DAL.getrecords() 
 
'save your recordset locally 
callfunction.Save "c:\myrecfs.txt" 
 
'''''''''''''''''''''''''' 
 
 
later on 
 
'''' 
youradodbrecordset.Open "c:\myrecs.txt" 
youradodbrecordset.MoveFirst 
..... 
'reconnect 
youradodbrecordset.ActiveConnection = newconn 
youradodbrecordset.UpdateBatch adAffectAll 
 
'''''' 
 
 
I know this code is ROUGH but you get the idea. 
 
C

VB400 · Jan 31, 2000

Hello, 
 
Thanks Calahans for your input. This seems to be a very nice simple process -- seems very clean. 
 
Two questions: 
 
1. How's the speed? 
 
2. In the Business Objects tier, you mention that we would convert the returned recordset into business objects. If the entire application changes to use a different database (say from SQL Server to Oracle), would that impact the Business Objects tier? I guess my "real" question is this: do recordsets "look" different depending on the database being used (e.g. does an Access recordset work the same as a SQL Server recordset or an Oracle recordset)? I would imagine that they all would have pretty much the same methods, MoveFirst, MoveNext, .EOF etc. In the Business Objects tier, would I need to change my declaration of the recordset depending on the database being used? 
 
Thanks!

calahans · Feb 1, 2000

Hi VB400, 
 
Speed is very faast (unless you are over using it - the users won't know). 
 
No recordsets don't look different. If you think about it by the time your business object is beinginitialsed all you are loking at is a VB rs - with the same methods and properties. As long as you keep the same table structure you should not come accross this kind of problem. 
 
You could use ADO and just change the connection string based on a tick box where the user could signal if the are out of office (as an idea). 
 
I know johnk says that his offcie develop with access and scale to SQL Server on the back. He seems to have no trouble - maybe he could comment. John? 
 
HTH 
 
C

VB400 · Feb 1, 2000

Is John using disconnected recordsets or is he passing serialized strings? 
 
Tarek

johnk · Feb 1, 2000

Tarek & Calahans, 
 
Remember, my real techie knowledge is obsolete. If any of this doesn't track let me know & I'll get some good techie help on it here. 
 
We do all our development with Access RDBMS. We deploy to either Access or SQL Server. We think we could equally deploy with zero changes using Oracle, Informix, Sybase, DB2/400 & maybe others. We use ADO and Parameterized Command Objects along with Provider software which is specific to a particular RDBMS. Our application programmers no longer write any SQL statements. They pass coded commands with parameters to our data access module which sets up the commad objects. 
 
We read disconnected recordsets (to avoid any locking), then pass the data in multi-dimensioned arrays as variant variables. Very fast. Very easy for our code to work with. We gave up bound data controls and had to master a good 3rd party grid control. 
 
We reread single rows at the time to update. Of course we lock during these operations. Code supplied by our generators keeps track of any changes in data that might occur between the original read and the reread. 
 
We give up some performance. No stored procedures, although we hope to add that in the future. At that time we expect to give up having a single data access module which can be set to any RDBMS by setting a variable in an .ini file (should be in the registry, I know). 
 
Hope this helps. I know it all can be terribly confusing. 
 
JohnK

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Disconnected Remote Issues

VB400

Programmer

VB400

Programmer

Snaggs

Programmer

calahans

Programmer

Snaggs

Programmer

VB400

Programmer

VB400

Programmer

calahans

Programmer

VB400

Programmer

calahans

Programmer

VB400

Programmer

johnk

MIS

Similar threads

Part and Inventory Search

Sponsor