Обсуждение: Connect via odbc from Windows to Linux
I am trying to set up an odbc connection from Windows 2000 to Postgresql running on Linux (CentOS 4.7).
I have the appropriate Postgresql jdbc driver on the windows box as well as the hosts file with the linux host.
Postgresql.confhas listen_addresses = '*'. The port is the same on both ends.
Trying various configurations with the odbc datasource administrator, the most friendly response I can get from a test
isthat the connected party/host failed to respond ("properly after a period of time" in the case of the 'party').
I do have portsentry running on the linux host but the windows box is not blocked.
I'm afraid there must be a simple solution but I can't figure out what it is. Where should I look?
TIA,
Bruce Hyatt
Bruce Hyatt wrote:
> I am trying to set up an odbc connection from Windows 2000 to Postgresql running on Linux (CentOS 4.7).
>
> I have the appropriate Postgresql jdbc driver on the windows box as well as the hosts file with the linux host.
Postgresql.confhas listen_addresses = '*'. The port is the same on both ends.
>
> Trying various configurations with the odbc datasource administrator, the most friendly response I can get from a
testis that the connected party/host failed to respond ("properly after a period of time" in the case of the 'party').
>
> I do have portsentry running on the linux host but the windows box is not blocked.
>
> I'm afraid there must be a simple solution but I can't figure out what it is. Where should I look?
>
the postgres JDBC driver doesn't use ODBC at all. its a native Java
driver that speaks the Postgres protocol directly.
to use ODBC, you'd need the Windows ODBC Postgres stack, which is fairly
complex to get working right and has little to do with Postgres JDBC,
and would just serve to confuse things.
In your Java app, you specify jdbc:postgresql://host/database as the
connection string... I'd probably run tcpdump on hte linux side,
`tcpdump -n tcp port 5432` then try and connect, this will show you if
the windows/java app is attemping to connect and its packets are getting
through... ideally you should see a whole >SYN <ACK >... tcp
connection handshake.
it /should/ look something like...
20:56:01.930328 IP 10.0.0.100.34307 > 10.0.0.10.postgres: S
3335832814:3335832814(0) win 32792 <mss 16396,sackOK,timestamp 671638094
0,nop,wscale 7>
20:56:01.930393 IP 10.0.0.10.postgres > 10.0.0.100.34307: S
3338209742:3338209742(0) ack 3335832815 win 32768 <mss
16396,sackOK,timestamp 671638094 671638094,nop,wscale 7>
20:56:01.930439 IP 10.0.0.100.34307 > 10.0.0.10.postgres: . ack 1 win
257 <nop,nop,timestamp 671638094 671638094>
20:56:01.930556 IP 10.0.0.100.34307 > 10.0.0.100.postgres: P 1:9(8) ack
1 win 257 <nop,nop,timestamp 671638094 671638094
...... (many more packets should follow)
where 10.0.0.100 is the Windows PC's IP, 10.0.0.10 is the Postgres
server's IP, and 34307 is a random 'source' port (this can be almost
anything. if you're using a different port than 5432, then 'postgres'
would be replaced with this other port (and, of course, you should
specify this port on the tcpdump command instead of 5432...)
so... if you DO see this, the network is working and its some other
problem. if you don't see it, its a network problem. or the JDBC
client isn't specifying the host correctly, or somehting like that.
--- On Mon, 12/1/08, John R Pierce <pierce@hogranch.com> wrote:
> Bruce Hyatt wrote:
> > I am trying to set up an odbc connection from Windows
> 2000 to Postgresql running on Linux (CentOS 4.7).
> >
> > I have the appropriate Postgresql jdbc driver on the
> windows box as well as the hosts file with the linux host.
> Postgresql.conf has listen_addresses = '*'. The port
> is the same on both ends.
> >
> > Trying various configurations with the odbc datasource
> administrator, the most friendly response I can get from a
> test is that the connected party/host failed to respond
> ("properly after a period of time" in the case of
> the 'party').
> >
> > I do have portsentry running on the linux host but the
> windows box is not blocked.
> >
> > I'm afraid there must be a simple solution but I
> can't figure out what it is. Where should I look?
> >
>
<snip>
> In your Java app, you specify
> jdbc:postgresql://host/database as the connection string
This is what I tried initially and it returns:
SQL Error: java.sql.SQLException: No suitable driver found for jdbc:postgresql://host/database 0 08001
This is what led me to try the postgresql odbc.
> I'd probably run tcpdump on hte linux side,
> `tcpdump -n tcp port 5432` then try and connect, this will
> show you if the windows/java app is attemping to connect and
> its packets are getting through... ideally you should see a
> whole >SYN <ACK >... tcp connection handshake.
As you'd probably expect, tcpdump shows no activity.
<snip>...
> so... if you DO see this, the network is working and its
> some other problem. if you don't see it, its a network
> problem. or the JDBC client isn't specifying the host
> correctly, or somehting like that.
I doubt it's a network problem because I have an application server running on the Windows box and httpd running on the
Linuxbox and that works fine. I checked again and the linux box is in the Windows' hosts file and I tried using all 3
waysof addressing the host in the java application.
Also, the application is a modification of a simple test app that worked on the localhost.
Thanks for your help,
Bruce Hyatt
Bruce Hyatt wrote: > This is what I tried initially and it returns: > SQL Error: java.sql.SQLException: No suitable driver found for jdbc:postgresql://host/database 0 08001 Have you followed the instructions here? http://jdbc.postgresql.org/documentation/head/load.html -O
--- On Mon, 12/1/08, Oliver Jowett <oliver@opencloud.com> wrote: > Bruce Hyatt wrote: > > > This is what I tried initially and it returns: > > SQL Error: java.sql.SQLException: No suitable driver > found for jdbc:postgresql://host/database 0 08001 > > Have you followed the instructions here? > > http://jdbc.postgresql.org/documentation/head/load.html > > -O I had commented out that line because the Java SE 6 API documentation for class DriverManager says it's no longer necessarybut when I add it back in I get: Error: java.lang.ClassNotFoundException: org.postgresql.Driverorg.postgresql.Driver This lead me to look at why the driver name is repeated. I think it might be the connection syntax: [String data = "jdbc:postgresql://host/database";] // with the appropriate host and db and without the brackets ... Connection conn = DriverManager.getConnection( data, "", ""); Something wrong with that? Thanks again, Bruce
Bruce Hyatt wrote: > --- On Mon, 12/1/08, Oliver Jowett <oliver@opencloud.com> wrote: > >> Bruce Hyatt wrote: >> >>> This is what I tried initially and it returns: >>> SQL Error: java.sql.SQLException: No suitable driver >> found for jdbc:postgresql://host/database 0 08001 >> >> Have you followed the instructions here? >> >> http://jdbc.postgresql.org/documentation/head/load.html >> >> -O > > I had commented out that line because the Java SE 6 API documentation for class DriverManager says it's no longer necessarybut when I add it back in I get: You commented out what line? > Error: java.lang.ClassNotFoundException: org.postgresql.Driverorg.postgresql.Driver You have the wrong classname. -O
--- On Mon, 12/1/08, Oliver Jowett <oliver@opencloud.com> wrote: > Bruce Hyatt wrote: > > --- On Mon, 12/1/08, Oliver Jowett > <oliver@opencloud.com> wrote: > > > >> Bruce Hyatt wrote: > >> > >>> This is what I tried initially and it returns: > >>> SQL Error: java.sql.SQLException: No suitable > driver > >> found for jdbc:postgresql://host/database 0 08001 > >> > >> Have you followed the instructions here? > >> > >> > http://jdbc.postgresql.org/documentation/head/load.html > >> > >> -O > > > > I had commented out that line because the Java SE 6 > API documentation for class DriverManager says it's no > longer necessary but when I add it back in I get: > > You commented out what line? > > > Error: java.lang.ClassNotFoundException: > org.postgresql.Driverorg.postgresql.Driver > > You have the wrong classname. I had commented out: Class.forName("org.postgresql.Driver"); I see that the classname is wrong. It seems the application is appending the classname to itself for some reason. The applicationis simple - only 27 lines total and three lines apply to the connection. The relevant lines are: String data = "jdbc:postgresql://192.168.1.199/platingshop"; // I tried hostname/... and hostname.domain/platingshop too try { // call to Class.forName no longer necessary Class.forName("org.postgresql.Driver"); Connection conn = DriverManager.getConnection( data, "", ""); Bruce Hyatt
--- On Mon, 12/1/08, Oliver Jowett <oliver@opencloud.com> wrote: > Bruce Hyatt wrote: > > --- On Mon, 12/1/08, Oliver Jowett > <oliver@opencloud.com> wrote: > > > >> Bruce Hyatt wrote: > >> > >>> This is what I tried initially and it returns: > >>> SQL Error: java.sql.SQLException: No suitable > driver > >> found for jdbc:postgresql://host/database 0 08001 > >> > >> Have you followed the instructions here? > >> > >> > http://jdbc.postgresql.org/documentation/head/load.html > >> This page also mentions the error I'm getting saying it occurs if the driver is not available but it's not clear (and I can'tfigure out) why it wouldn't be available. Bruce Hyatt
Bruce Hyatt wrote: > I see that the classname is wrong. It seems the application is appending the classname to itself for some reason. The applicationis simple - only 27 lines total and three lines apply to the connection. The relevant lines are: I suggest you post your whole testcase to the list, it's too hard to guess at exactly what you're doing otherwise. -O
--- On Mon, 12/1/08, Oliver Jowett <oliver@opencloud.com> wrote:
> I suggest you post your whole testcase to the list,
> it's too hard to guess at exactly what you're doing
> otherwise.
import java.sql.*;
public class TestPlatingShop {
public static void main(String[] arguments) {
String data = "jdbc:postgresql://192.168.1.199/platingshop";
try {
// call to Class.forName no longer necessary
Class.forName("org.postgresql.Driver");
Connection conn = DriverManager.getConnection(
data, "", "");
Statement st = conn.createStatement();
ResultSet rec = st.executeQuery(
"SELECT custname " +
"FROM customers " +
"WHERE " +
"(acctnum = 'B20109')");
System.out.println("Customer Name");
while(rec.next()) {
System.out.println(rec.getString(1));
}
st.close();
} catch (SQLException s) {
System.out.println("SQL Error: " + s.toString() + " "
+ s.getErrorCode() + " " + s.getSQLState());
} catch (Exception e) {
System.out.println("Error: " + e.toString()
+ e.getMessage());
}
}
}
This returns "Error: java.lang.ClassNotFoundException: org.postgresql.Driverorg.postgresql.Driver" when run.
Bruce
Bruce Hyatt wrote:
> try {
> // call to Class.forName no longer necessary
> Class.forName("org.postgresql.Driver");
> } catch (Exception e) {
> System.out.println("Error: " + e.toString()
> + e.getMessage());
> }
> This returns "Error: java.lang.ClassNotFoundException: org.postgresql.Driverorg.postgresql.Driver" when run.
Ok then, it's just your error printing code that's doubling up the class
name then.
You don't have the driver in your classpath.
-O
--- On Tue, 12/2/08, Oliver Jowett <oliver@opencloud.com> wrote: > You don't have the driver in your classpath. For some reason I had assumed that Java EE 5 SDK included the driver but I couldn't locate postresql.jar on my computer.So I downloaded and ran various precompiled versions of the driver from jdbc.postgresql.org and always got the error"failed to load main-class manifest attribute." Googling this in various permutations at Google, jdbc.postgresql andjava.sun lists I didn't find anything promising. I tried to build the source with Ant but I always wind up with "Buildfile: build.xml does not exist." Sorry for the newbie questions and problems. I thought once I had all the components working it would be fairly simple toimplement jdbc to PostgreSQL. This list also seemed more appropriate than the novice list. Sincerely obliged, Bruce Hyatt
Bruce Hyatt wrote: > --- On Tue, 12/2/08, Oliver Jowett <oliver@opencloud.com> wrote: > >> You don't have the driver in your classpath. > > For some reason I had assumed that Java EE 5 SDK included the driver It doesn't as far as I know. > So I downloaded and ran various precompiled versions of the driver from jdbc.postgresql.org and always got the error "failedto load main-class manifest attribute." The driver is not a selfcontained Java program and does not provide a main class. Trying to run it with -jar will produce the above error. You want to run with the driver jar in the classpath (e.g. via a -classpath argument to your JVM) *in addition to* your own code, and specify your own main class as the entry point. > Sorry for the newbie questions and problems. I thought once I had all the components working it would be fairly simpleto implement jdbc to PostgreSQL. This list also seemed more appropriate than the novice list. Frankly, you may be better off looking for an introductory Java forum for help. Understanding how the JVM locates classes and how to modify your classpath is a prerequisite for using *any* sort of 3rd party library; it's not specific to JDBC or PostgreSQL. -O