how to import and export data between local and remote server
Hi all,
right now i am in to my academic project, an intranet application using JSP & PostgreSQL. the work is for a univerity department. i hav provided all the internal management screens as an offline component. so all the pages wil reside in a local web server (Tomcat 5.5). but this department already have a web site. so i wud like my application to communicate with this web site. for example, the offline componenet can process the mark list details locally (no net connection needed for
that). and at the end of processing say let all the data be n "marklist_tbl" in the local database.
how could i provide an interface for the user to do an export operation (offcourse internet connection needed for that) by which this local "marklist_tbl" wil be copied to "marklist_tbl" on the remote web server(the remote server also has postgresql support, and i hav already created "marklist_tbl" table in the remote server). so what i need is a line by line copying of "marklist_tbl". so how can this be accomplished using JSP & Java. i cant use the import export screens provided by Postgresql, so the user feels that they r doing something by moving out of my application. similarly i wanna copy some tables in the remote server to the local database, say an import from remote server to my local machine.
i have the enitire privilages and paaswords (FTP, DB etc) with the web site and local server.
hope u became nutt with my lengthy mail. as i am totally new to Java, plz tell me whether this kind of an application (internet & intranet communication) has any technical issues? if not how can i do that.
drop in ur comments
thanks for ur time
regards
aleena
[1734 byte] By [
aleensa] at [2007-10-2 6:20:20]
NO replies!! so i am afraid that such kind of an application is not feasible.or else drop in ur comments ASAP plzzzzzzzz
What you could do is provide a screen in your intranet application. This would contain info on what is in the database locally and would contain an upload button.
An user with sufficient privileges can review the info (with drill down options) and choose to upload the info into the other system by clicking on the 'upload' button.
When the 'upload' is clicked, your local web application can make a connection to the remote db and insert the 'chosen' data there.
Hope this helps.
ram.
1] you can create a jsp page with 2connections one for local DB and the other for the remote DB. now select (sql querry) all the records that u want to upload. as u parse the records
( generally using while (rs.next()) ) create a sql insert command that uses second connection. in this command map each field(remote DB) with the data(local DB) and execute.
thus for each record it reads from the local DB and executes an insert command on the remoteDB.
this page may display some animation as this process may take time depending on the no. of records...
Good Luck.................
thanks a lot for ur reply.
but can u tell me how could i connect to a remote database server (say in www.university.org ) from my local machine using Java code.
ie, how cud i mention that remote server in the following code
cnxion2 = DriverManager.getConnection("jdbc:postgresql:"+_database,_username,_password);
plzz help me ASAP
aleena
yeah i tried but on my system postgress is not installed so it said class "org.postgresql.Driver" not found but may be in ur system it may run
use ("jdbc:postgresql://<ip number of ur server>:<port number of postgres>"/+_database,_username,_password);
if that does not wor u hav to put this page on the server and have to post the data from local DB to the page on the server (action="http://www.university.com/pagename.jsp")....which inserts the data and redirects to local website . the data ay be sent record by record(not efficient) or bulk(add all the records to a string in a way u could recognize it in the server page and post the string)
thanks a lot.
so let me check that option, moreover the second option u hav said seems a better way. i never thought of such a thing. in that case the connection issues wont affect much...right.
But i think, for getting some data from remote server to local that wont work. bcoz i dont think that we can identify a JSP page in a local machine from a remote server. or is there any way
anyways thanks a lot for ur contributions.
i hav tried to connect using the IP address:port number method. but locally i am working with Postgre SQL 8.0. but my remote web server is running with PostgreSQL 7.3.10.
so i hav downloaded the relevent driver from http://jdbc.postgresql.org/download.html
still i hav the same error, driver not found. so i am a bit confused to make a statemet. that is whether the driver is the problem, or postgresql doesn't allow remote login to the database?.so me really in a tough state now. so can anyone help me. plzzzzzzzzzzzzz
try the second option in the previous message.....
that solution seems gud. and i think its going to work fine for export. but for import (ie; for data transfer from remote server to local server) how can i use that.
and do u hav any info that postgresql doesnt support remote login. bcoz i think that will be the better solution if i can do that
aleena
thats just in the reverse process.....open a page on remote server submit the required querry to it that will return all the records - now submit this data to a servlet on the local server that will read the data and save it to local db..
I tried to connect to my remote server from my local server... for that i just need to place the driver (.jar) file in the WEB-INF/classes/ dir
but when i open the page it says no permision set for my ip ....
from this what i understood is that there is a file pg_hba.conf in the /usr/local/pgsql/data dir of postgres installation where u can set access right to a particular ip , user, etc...
find more info at http://www.faqs.org/docs/ppbook/c15679.htm
if u have the time to experiment then try this... it is more effeceint way to do...
good luck
thanks a lot for info on pg_hba.conf. so lemme try some research in that.
anyhow i hav to find a solution which allows a remote postgresql server to allow conxion from any IP (bcoz we i dont think that we can specify a fixed IP as far as a local server is concerned, or is there any way).
i been disturbing u for such a long time.so sorry for that
thanks a lot
aleena
no probs..buddy...even i learned a lot from this topic .... if possiable post the solution that u find out after your research so that i (and others) can know the exact method of doing it....good luck.......
