Thread: Data transfer between databases over the Internet
I have found myself somewhat trapped in a project where the software being used is essentially set in stone, and its limitations are strangling progress. It is a client-side app written in VB with an Access backend, and it has no reasonable data migration/synchronization ability. It was designed for a single user system, and cannot even hook up to a different backend. I have no confidence in the vendor's ability to fix this situation in a timely fashion. A lot of people are trying to tackle this (relatively simple) problem, and trying to use bulldozers to hammer a nail. I am hearing "Active Directory" and "Citrix" thrown around, and it scares me. I want to offer a clean, quick solution to consolidating this data as our field guys enter it without having to change the current work process (i.e. while continuing to use this software...I only have so much pull!) What I would like to do is set up a relational database at our site (preferably Postgres) which would ultimately house all of the data from the many different client machines. This database is extremely simple, only consisting of 5 tables, and I have already written a simple VB tool that is capable of merging these databases into one PostgreSQL backend, so logically my task is simple. What I need to do, however, is write a client-side app that can sit on my users' computers and accomplish this task over the Internet. I want to create a simple "check-in/check-out" type of system that my users can use to do the data upload and download themselves. As I said before, the database structure is exceedingly simple, so I will have no difficulties writing the logic. I don't want to re-invent the wheel, though, so I wanted to ask this list what would be the most sensible transfer mechanism. 1) Simply expose the PostgreSQL database backend to the Internet over an encrypted pipe and write my client-side app almost identical to my current VB app. This seems to be the most logically simple solution, however I question how fast the app will be since it has to do its work through ODBC over the Internet. Also, is opening up a port like this, even encrypted, considered a bad practice? 2) Write my own client and server program that manually transfer the data themselves over my own encrypted pipe. The client side would basically do queries to pull all of the data, I'd send it over the pipe to the server program, and it would insert or update all of the records. As far as the protocol for sending the data, I suppose I could wrap all of the data in XML, but that's pretty verbose and may "bulk up" the data. Is there any established wire protocol for doing this? This approach seems pretty labor intensive, and I feel like I'd be reinventing the wheel. 3) Perhaps there is some existing app, protocol, or combination of apps that people use for this purpose that I've never heard of. Are there any other approaches you guys have used when faced with this problem? John
John McCawley wrote: > I have found myself somewhat trapped in a project where the software > being used is essentially set in stone, and its limitations are > strangling progress. It is a client-side app written in VB with an > Access backend, and it has no reasonable data > migration/synchronization ability. It was designed for a single user > system, and cannot even hook up to a different backend. I have no > confidence in the vendor's ability to fix this situation in a timely > fashion. > I guess I would recommend you to upgrade your VB application to either vb.net or C#, then you can use the Postgresql .net data provider which requires no additional client libraries. This means you could write a nice compact application that would require one exe and the .net data provider assembly. http://npgsql.projects.postgresql.org/ Or you could use Turbo Delphi (http://www.turboexplorer.com/delphi) along with a native client access library such as PostgresDAC(http://www.microolap.com) or Zeos (http://www.zeoslib.net). You could also use ASP.net to(with npgsql) create a nice web application. For C# or vb.net, you don't need a M$ ide, you could use SharpDevelop (http://www.icsharpcode.com/OpenSource/SD/Default.aspx) which is excellent. Hope this helps, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
I'm not really sure what switching development platforms for the tool would buy me. Are you saying that the wire-protocol used by vb.net and C# etc. is substantially faster than what is available in VB? Regarding ASP.net, I don't really see how a web app would help here, as it has to run on hundreds of client machines...Are you suggesting that I install a webserver on them? Tony Caduto wrote: > I guess I would recommend you to upgrade your VB application to either > vb.net or C#, then you can use the Postgresql .net data provider which > requires no additional client libraries. > This means you could write a nice compact application that would > require one exe and the .net data provider assembly. > http://npgsql.projects.postgresql.org/ > > Or you could use Turbo Delphi (http://www.turboexplorer.com/delphi) > along with a native client access library such as > PostgresDAC(http://www.microolap.com) or Zeos (http://www.zeoslib.net). > > You could also use ASP.net to(with npgsql) create a nice web > application. > > For C# or vb.net, you don't need a M$ ide, you could use SharpDevelop > (http://www.icsharpcode.com/OpenSource/SD/Default.aspx) which is > excellent. > > Hope this helps, >
On 11/22/06, John McCawley <nospam@hardgeus.com> wrote: > I'm not really sure what switching development platforms for the tool > would buy me. Are you saying that the wire-protocol used by vb.net and > C# etc. is substantially faster than what is available in VB? Regarding no, the wire protocols are the same (dictated by the server). also, c# and vb.net are almost functionally the same language. > ASP.net, I don't really see how a web app would help here, as it has to > run on hundreds of client machines...Are you suggesting that I install a > webserver on them? no, i think he is suggesting you rewrite your app as server side application using asp.net. There are pros and cons to this argument but if you have a very short timeframe I would suggest going with what you know. This could be odbc based or whatever you are most comfortable with. Simple transfers can actually work quite well with vanilla insert statements via odbc/vbscript, especially if you use (8.1) multiple line insert statements. My suggestion would be to have your client app connect directly to the database and tranfer the data via standard sql...insert statements and such. You can connect directly via ssl or set up a ssh tunnel forwarding the port to the protected port on the server side...I prefer to tunnel because this allows me to compile postgresql without ssl support. What to do here is really a matter of style. It is perfectly safe as long as you take the proper precautions although I would use nonstandard ports regardless. merlin
John McCawley wrote: > I'm not really sure what switching development platforms for the tool > would buy me. Are you saying that the wire-protocol used by vb.net > and C# etc. is substantially faster than what is available in VB? > Regarding ASP.net, I don't really see how a web app would help here, > as it has to run on hundreds of client machines...Are you suggesting > that I install a webserver on them? > > I would say it probably is a bit faster, but the nice thing is there is no client libraries required, hence a thinner deployment. With a web application you would have the application run at a single web server and the clients would access through a browser. That way you would not have to deploy anything. -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
I think I may not have described my problem clearly enough...I *already* have a server-side app written in PHP with a Postgres backend...This is the ultimate destination of the data. The problem is that I am being forced by my client to deploy a 3rd party app on all of my field guys' laptops...This app (that I have NO ability to modify) is set in stone, I cannot use a different one or modify it. I am trying to write an app client side that exports the data from the .mdb backend of this 3rd party program and into my Postgres backend. This needs to work over the Internet, as my guys are not necessarily on my LAN. Believe me, you don't have to sell me on the benefits of web programming. What I am trying to do is work around the limitations of someone else's app. Everything that I have written on this project (with the eception of these "glue" apps) is PHP/Postgres. Tony Caduto wrote: > John McCawley wrote: > >> I'm not really sure what switching development platforms for the tool >> would buy me. Are you saying that the wire-protocol used by vb.net >> and C# etc. is substantially faster than what is available in VB? >> Regarding ASP.net, I don't really see how a web app would help here, >> as it has to run on hundreds of client machines...Are you suggesting >> that I install a webserver on them? >> >> > I would say it probably is a bit faster, but the nice thing is there > is no client libraries required, hence a thinner deployment. > > With a web application you would have the application run at a single > web server and the clients would access through a browser. That way > you would not have to deploy anything. >
Merlin Moncure wrote: > > no, the wire protocols are the same (dictated by the server). also, > c# and vb.net are almost functionally the same language. > > Hi, From experience NPGSQL seems faster than ODBC, though they have the same wire protocol. Must have something to do with the overhead of ODBC. All .net languages are basically the same but C# has less baggage than VB.net because VB.net requires special assemblies for VB features. I suggested .net as the npgsql .net data provider is pretty thin compared to libpq.dll which has a ton of dependencies these days that must also be deployed. Of course you would need .net on all the client PCs, but more than likely it's already installed. If the client side app is pretty simple then it would be a good idea to migrate away from VB 6 as it really is a dead language now. Food for thought :-) Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
John McCawley wrote: > I think I may not have described my problem clearly enough...I > *already* have a server-side app written in PHP with a Postgres > backend...This is the ultimate destination of the data. The problem > is that I am being forced by my client to deploy a 3rd party app on > all of my field guys' laptops...This app (that I have NO ability to > modify) is set in stone, I cannot use a different one or modify it. I > am trying to write an app client side that exports the data from the > .mdb backend of this 3rd party program and into my Postgres backend. > This needs to work over the Internet, as my guys are not necessarily > on my LAN. > > Believe me, you don't have to sell me on the benefits of web > programming. What I am trying to do is work around the limitations of > someone else's app. Everything that I have written on this project > (with the eception of these "glue" apps) is PHP/Postgres. > > Ok, then scratch the asp.net idea. The other stuff is still good advice With C# or VB.net you could use the ado.net driver to access the .mdb files,loop through the rows, then use the NPGSQL .net provider to do the inserts into the PG backend server for each row iteration. You could even use Perl to do this pretty easily. Later, -- Tony Caduto AM Software Design http://www.amsoftwaredesign.com Home of PG Lightning Admin for Postgresql Your best bet for Postgresql Administration
> John McCawley wrote: > >I think I may not have described my problem clearly enough...I > >*already* have a server-side app written in PHP with a Postgres > >backend...This is the ultimate destination of the data. The problem > >is that I am being forced by my client to deploy a 3rd party app on > >all of my field guys' laptops...This app (that I have NO ability to > >modify) is set in stone, I cannot use a different one or modify it. I > >am trying to write an app client side that exports the data from the > >.mdb backend of this 3rd party program and into my Postgres backend. If that's really all you need you might just write a Python script. Or do you need a frontend for the PostgreSQL data as well ? If so it might still be useful to separate it from the ex-/importer. > >This needs to work over the Internet, as my guys are not necessarily > >on my LAN. No problem. I'd just make sure it works over an encrypted pipe (as you said) - be it ssh or ssl. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346