• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1196
  • Last Modified:

Connecting to a jdbc:odbc database on a remote machine ...

The normal way to get a connection if you run the built-in ODBC database driver for Microsoft Access that Access installs in the control panel is this java line.

DriverManager.getConnection("jdbc:odbc:mydb");

But how do I connect to a database on the LAN/WAN or just using the IP for my own machine

This does not work!
DriverManager.getConnection("jdbc:odbc://127.0.0.1/mydb");

Do I have to export the database?

/Christer
0
ChristerH
Asked:
ChristerH
1 Solution
 
admiralCommented:
Your code DriverManager.getConnection("jdbc:odbc:mydb"); should work. Are you sure you registered your data base
as a data source in the windows control panel.
0
 
ChristerHAuthor Commented:
Hi!

DriverManager.getConnection("jdbc:odbc:mydb");

works just fine on my machine. I just would like to run on my friends machine and just tried out the syntax:

DriverManager.getConnection("jdbc:odbc://127.0.0.1/mydb");

before entering his IP-address. But I just get an SQL exception.
0
 
fmaritatoCommented:
Try using the following syntax:

<protocol>:@<hostname>:<port>

For example,

jdbc:oracle:thin:@hostname.domain.com:1234
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
ChristerHAuthor Commented:
For me, would that be
jdbc:odbc:mydb@myhost.mydomain.com:1234
What is the portnumber? How do I export a DB?

0
 
ChristerHAuthor Commented:
Should it be like:
"jdbc:odbc://hostname:port/mydb"
But I'm still confused about the portnumber ... is it possible to export a Microsoft Access database this way?
0
 
Ravindra76Commented:

Hi ChristerH,

 Jdbc-Odbc bridge don't support networking.

So you can't do like that.

One solution is

Note: Here server means the system which is storing database.Not your LAN server.

1. Map a driver ( assume f: ) on each cllient so that it will map to server directory.

 Eg: net use f: \\yourserver\c.

2.Create dsn in each client in LAN who need database of serverwith path of database in the server now.

Eg: data source name: xyz.

select path: f:\\yourdirctoryContaingdDdatanbase.

Now run the program as usual.

3.THe dsn in all clients should be same name mapping to server database path.

Best of luck.

If you are using type 3 or type 4 drivers, you can connet to database in LAN.

For more info:

visit this URL:

http://java.sun.com/products/jdbc/drivers.html         

Best of luck.
0
 
Ravindra76Commented:
Hi ChristerH,

I will explaint in detail.

1.Create a database (Assume name is chris.mdb) in a directory (Assume xyz) in a system (Assume Christerh )

2.In each client in LAN/WAN

  a. type

          net use f: \\Christerh\c

  b.Create a dsn (Assume abc) in

  start->setting->controlpanel->odbc->systemdsn->mdb->

 with path

    f:\xyz\chris.mdb

3.Use dsn name abc in java program
as jdbc:odbc:abc

That's all

Best of luck.
0
 
vladi21Commented:
My answer:
---
URL syntax

The "odbc" subprotocol
The "odbc" subprotocol has been reserved for URLs that specify ODBC style Data Source Names. For this subprotocol we specify a URL syntax that allows arbitrary attribute values to be specified after the data source name.
The full odbc subprotocol URL syntax is:


jdbc:odbc:<data-source-name>[;<attribute-name>=<attribute-value>]*

Thus valid jdbc:odbc names include:

jdbc:odbc:qeor7

jdbc:odbc:wombat

jdbc:odbc:wombat;CacheSize=20;ExtensionCase=LOWER

jdbc:odbc:qeora;UID=kgh;PWD=fooey

---
if u cannot map drive (its not easy in WAN ) use this solution
:

http://java.sun.com/products/jdbc/faq.html

How can I use the JDBC API to access a desktop database like Microsoft Access over the network?

Most desktop databases currently require a JDBC solution that uses ODBC underneath. This is because the vendors of these database products haven't implemented all-Java JDBC drivers.


The best approach is to use a commercial JDBC driver that supports ODBC and the database you want to use. See the JDBC drivers page for a list of available JDBC drivers.

The JDBC-ODBC bridge from Sun's Java Software does not provide network access to desktop databases by itself. The JDBC-ODBC bridge loads ODBC as a local DLL, and typical ODBC drivers for desktop databases like Access aren't networked. The JDBC-ODBC bridge can be used together with the RMI-JDBC bridge , however, to access a desktop database like Access over the net. This RMI-JDBC-ODBC solution is free.
http://dyade.inrialpes.fr/mediation/download/
0
 
sytanCommented:
Christ,

> But how do I connect to a database on the LAN/WAN or just using the IP for my own machine

This should depend on the settings of your network and database. This is what i do :

Class.forName("com.informix.jdbc.IfxDriver");
{ I am using informix db so here, specify the drivers }

DriverManager.getConnection("jdbc:informix-sqli://127.0.0.1:1234/mydb:INFORMIXSERVER=dbenv; user=user; password=password");

127.0.0.1 = your db server ip
:1234 = the port number set. (how to get this? ask your db admin)
This is set in your db server (DBMS). Remember it is a db server, not just a database.

mydb = your db name
INFORMIXSERVER = enviroment set for your db. This is specific to informix only.
user , password = login information to your db. u must have access to the db in order to use it, again ask your db admin for it.

The connection string depends largely on the db server and the network. It will not work for you, but hopefully it would help you to figure out whats your conn- string should be.

But if your are not running a dbms, i would suggest you try ravindra76's method..

0
 
Ravindra76Commented:
Hi,

IF you want to go for RMI - JDBC solutioin, a sample code.



In sun site, you will get sample examples

//Remote Interface

import java.rmi.*;

public class RMIJdbc extends Remote
{
public String getDataFromServerDatabase() throws RemoteException;
}

// Implementation class (SERVER)

import java.rmi.*;
import java.rmi.server.*;
public class RMIJdbcImpl extends UnicastRemoteObject implements RMIJdbc
{
Connection con;
PreparedStatement pst;
ResultSet rs;
String hostname="localhost"; // where you run the rmi registry
int port = 1099; // the port of the rmiregistry (by default it takes 1099)
String objectname="server"; // your object name in the naming service(rmiregistry)
public RMIJdbcImpl()
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con = DriverManager.getConnection("jdbc:odbc:yourdsn");
}
catch ( Exception ee)
{
ee.printStackTrace();
}

public String getDataFromServerDatabase() throws RemoteException
{
String toReturn = "";
try
{
pst = con.prepareStatement("select ename from your table where empno=?)";
pst.setInt(1,100) //Assume yourtable contains like 100 as empno and //Rameshaa as ename
rs = pst.executeQuery();
if ( rs.next())
toReturn = rs.getString(1);
retrun toReturn;

}

public static void main(String args[])
{
RMIJdbcImpl server = new RMIJdbcImpl();
server.bindInNamingService();
}
public void bindInNamingService(){
try
{
Naming.rebind("rmi://"+hostname+":"+hostpor+"/"+objectname,this);
}
Catch ( Exception eee)
{
eee.printStackTrace();
}
}
}



// CLient class
import java.rmi.server.*;
import java.rmi.*;

public class RMIClient
{
public static void main(String args[])
{
try
{
RMIJdbc obj = Naming.lookup("rmi://"+args[0]+":"+args[1]+"/"+args[2]);
System.out.println(getDataFromServerDatabase());
}catch(Exception e){
e.printStackTrace();
}
}
}



Compiling:

javac RMIJdbc.java
javac RMIJdbcImpl.java

rmic RMIJdbcImpl

javac RMIClient.java

Running:

SERVER:

start rmiregistry ( you should keep the window open until you need your server)
java RMIJdbcImpl

CLIENT:

java RMIClient localhost 1099 server

( you can give the hostname where rmiregistry is running for your server the port is rmiregistry port
these params must be the same as you give in your server);


Best of luck
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now