JDBC connection (Hopping)
Vikas wanted a JDBC URL that connects to INFORMIXSERVER_1 on HOST_1 but reaches database test_2 on INFORMIXSERVER_2 (HOST_2), i.e. a hop via the local server; direct connections and dbaccess test_2@INFORMIXSERVER_2 worked fine. Art Kagel suggested a URL of the form .../test_2@INFORMIXSERVER_2:INFORMIXSERVER=INFORMIXSERVER_1, but the driver treated the whole string as the database name; SQLH_TYPE/SQLH_FILE dynamic sqlhosts lookup didn't help either. Marcus Haarmann said the hop probably can't be expressed in the JDBC URL and advised connecting to the local server, then issuing a DATABASE db@server statement or qualifying tables as db@server:table (XA if one transaction is needed). No confirmed resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all,
My last post was not very clear so I try again.
I have :
INFORMIXSERVER_1 running on HOST_1 against port 1980
INFORMIXSERVER_2 running on HOST_2 against port 1990
Both HOST entires are present in /etc/hosts, sqlhosts and /etc/services and
both instances are online!
I can simply edit the env on HOST_1 to point to INFORMIXSERVER_2 then do a
dbaccess and connect the INFORMIXSERVER_2.
Now comes the JDBC part :(
I'm using a front end tool "SQLWorkbench" which uses the
"com.informix.jdbc.IfxDriver" & the connection string is as:
jdbc:informix-sqli://HOST_1:1980/test_1:INFORMIXSERVER=INFORMIXSERVER_1
Every thing works fine and I'm able to connect to test_1 db
But when i try the following
jdbc:informix-sqli://HOST_1:1990/test_2:INFORMIXSERVER=INFORMIXSERVER_2
Error:'Can't load driver java.lang.reflect.InvocationTargetException
Any comment If such connection is possible where I connect to HOST_1 but then
try to access a database test_2 on INFORMIXSERVER_2 via port 1990, if yes that
what should i check for?
May be not the best way to do this but....
Regards,
Vikas
You can certainly connect directly to the INFORMIXSERVER_2 on HOST_2 to
access the test_2 database there or you can connect to INFORMIXSERVER_1 on
HOST_1 and access the test_2 database remotely through the INFORMIXSERVER_1
server which will connect to INFORMIXSERVER_2 on your behalf (as long as the
remote server is defined in the sqlhosts file on the local machine). Your
connection string should look something like:
jdbc:informix-sqli://HOST_1:1980/test_2@INFORMIXSERVER_2
:INFORMIXSERVER=INFORMIXSERVER_1
You can test the remote connection between the two servers using dbaccess as
follows:
INFORMIXSERVER=INFORMIXSERVER_1dbaccess test_2@INFORMIXSERVER_2 -
If that works, the jdbc connection should work as well.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Mon, Nov 9, 2009 at 7:18 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote:
> Hi all,
>
> My last post was not very clear so I try again.
>
> I have :
> INFORMIXSERVER_1 running on HOST_1 against port 1980
> INFORMIXSERVER_2 running on HOST_2 against port 1990
>
> Both HOST entires are present in /etc/hosts, sqlhosts and /etc/services and
> both instances are online!
> I can simply edit the env on HOST_1 to point to INFORMIXSERVER_2 then do a
> dbaccess and connect the INFORMIXSERVER_2.
>
> Now comes the JDBC part :(
> I'm using a front end tool "SQLWorkbench" which uses the
> "com.informix.jdbc.IfxDriver" & the connection string is as:
>
> jdbc:informix-sqli://HOST_1:1980/test_1:INFORMIXSERVER=INFORMIXSERVER_1
> Every thing works fine and I'm able to connect to test_1 db
>
> But when i try the following
> jdbc:informix-sqli://HOST_1:1990/test_2:INFORMIXSERVER=INFORMIXSERVER_2
>
> Error:'Can't load driver java.lang.reflect.InvocationTargetException
>
> Any comment If such connection is possible where I connect to HOST_1 but
> then
> try to access a database test_2 on INFORMIXSERVER_2 via port 1990, if yes
> that
> what should i check for?
>
> May be not the best way to do this but....
>
> Regards,
> Vikas
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0023545bd4944787470477f11ce6
Hello Art,
--You can test the remote connection between the two servers using dbaccess as
follows
INFORMIXSERVER=INFORMIXSERVER_1dbaccess test_2@INFORMIXSERVER_2 -
The above test works in a flash and i am able to connect the test_2 database
on INFORMIXSERVER_2 instance from INFORMIXSERVER_1 on HOST_1 !
I tried
jdbc:informix-sqli://HOST_1:1980/test_2@INFORMIXSERVER_2
:INFORMIXSERVER=INFORMIXSERVER_1
But it interprets "test_2@INFORMIXSERVER_2" as the name of the database and
not as test_2 database on INFORMIXSERVER_2 instance.
As you mentioned that the JDBC connection should work I also checked for the
topic on "Dynamically Reading the Informix SQLHOSTS file" here i tried
jdbc:informix-sqli://HOST_1:1980/test_2:INFORMIXSERVER=INFORMIXSERVER_2;SQLH_TYP
E=FILE;SQLH_FILE=/opt/informix/ids/etc/sqlhosts
But no luck eighter.
*******************************************************************************
You can certainly connect directly to the INFORMIXSERVER_2 on HOST_2 to
access the test_2 database there or you can connect to INFORMIXSERVER_1 on
HOST_1 and access the test_2 database remotely through the INFORMIXSERVER_1
server which will connect to INFORMIXSERVER_2 on your behalf (as long as the
remote server is defined in the sqlhosts file on the local machine). Your
connection string should look something like:
jdbc:informix-sqli://HOST_1:1980/test_2@INFORMIXSERVER_2
:INFORMIXSERVER=INFORMIXSERVER_1
You can test the remote connection between the two servers using dbaccess as
follows:
INFORMIXSERVER=INFORMIXSERVER_1dbaccess test_2@INFORMIXSERVER_2 -
If that works, the jdbc connection should work as well.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Mon, Nov 9, 2009 at 7:18 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote:
> Hi all,
>
> My last post was not very clear so I try again.
>
> I have :
> INFORMIXSERVER_1 running on HOST_1 against port 1980
> INFORMIXSERVER_2 running on HOST_2 against port 1990
>
> Both HOST entires are present in /etc/hosts, sqlhosts and /etc/services and
> both instances are online!
> I can simply edit the env on HOST_1 to point to INFORMIXSERVER_2 then do a
> dbaccess and connect the INFORMIXSERVER_2.
>
> Now comes the JDBC part :(
> I'm using a front end tool "SQLWorkbench" which uses the
> "com.informix.jdbc.IfxDriver" & the connection string is as:
>
> jdbc:informix-sqli://HOST_1:1980/test_1:INFORMIXSERVER=INFORMIXSERVER_1
> Every thing works fine and I'm able to connect to test_1 db
>
> But when i try the following
> jdbc:informix-sqli://HOST_1:1990/test_2:INFORMIXSERVER=INFORMIXSERVER_2
>
> Error:'Can't load driver java.lang.reflect.InvocationTargetException
>
> Any comment If such connection is possible where I connect to HOST_1 but
> then
> try to access a database test_2 on INFORMIXSERVER_2 via port 1990, if yes
> that
> what should i check for?
>
> May be not the best way to do this but....
>
> Regards,
> Vikas
Don't know more. I don't do JDBC at all myself. Note that direct remote
connections to a remote server tend to be faster than a forwarded connection
through the local server to the remote.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Mon, Nov 9, 2009 at 10:21 AM, VIKAS HIVARKAR
<vikas.hivarkar@gmail.com>wrote:
> Hello Art,
>
> --You can test the remote connection between the two servers using dbaccess
> as
> follows
> INFORMIXSERVER=INFORMIXSERVER_1> dbaccess test_2@INFORMIXSERVER_2 -
>
> The above test works in a flash and i am able to connect the test_2
> database
> on INFORMIXSERVER_2 instance from INFORMIXSERVER_1 on HOST_1 !
>
> I tried
> jdbc:informix-sqli://HOST_1:1980/test_2@INFORMIXSERVER_2
> :INFORMIXSERVER=INFORMIXSERVER_1
>
> But it interprets "test_2@INFORMIXSERVER_2" as the name of the database
> and
> not as test_2 database on INFORMIXSERVER_2 instance.
>
> As you mentioned that the JDBC connection should work I also checked for
> the
> topic on "Dynamically Reading the Informix SQLHOSTS file" here i tried
>
>
>
>
jdbc:informix-sqli://HOST_1:1980/test_2:INFORMIXSERVER=INFORMIXSERVER_2;SQLH_TYP
E=FILE;SQLH_FILE=/opt/informix/ids/etc/sqlhosts
>
> But no luck eighter.
>
>
>
>
*******************************************************************************
>
> You can certainly connect directly to the INFORMIXSERVER_2 on HOST_2 to
> access the test_2 database there or you can connect to INFORMIXSERVER_1 on
> HOST_1 and access the test_2 database remotely through the INFORMIXSERVER_1
> server which will connect to INFORMIXSERVER_2 on your behalf (as long as
> the
> remote server is defined in the sqlhosts file on the local machine). Your
> connection string should look something like:
>
> jdbc:informix-sqli://HOST_1:1980/test_2@INFORMIXSERVER_2
> :INFORMIXSERVER=INFORMIXSERVER_1
>
> You can test the remote connection between the two servers using dbaccess
> as
> follows:
>
> INFORMIXSERVER=INFORMIXSERVER_1> dbaccess test_2@INFORMIXSERVER_2 -
>
> If that works, the jdbc connection should work as well.
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity
> with which I am affiliated nor those of the entities themselves.
>
> On Mon, Nov 9, 2009 at 7:18 AM, VIKAS HIVARKAR
> <vikas.hivarkar@gmail.com>wrote:
>
> > Hi all,
> >
> > My last post was not very clear so I try again.
> >
> > I have :
> > INFORMIXSERVER_1 running on HOST_1 against port 1980
> > INFORMIXSERVER_2 running on HOST_2 against port 1990
> >
> > Both HOST entires are present in /etc/hosts, sqlhosts and /etc/services
> and
> > both instances are online!
> > I can simply edit the env on HOST_1 to point to INFORMIXSERVER_2 then do
> a
> > dbaccess and connect the INFORMIXSERVER_2.
> >
> > Now comes the JDBC part :(
> > I'm using a front end tool "SQLWorkbench" which uses the
> > "com.informix.jdbc.IfxDriver" & the connection string is as:
> >
> > jdbc:informix-sqli://HOST_1:1980/test_1:INFORMIXSERVER=INFORMIXSERVER_1
> > Every thing works fine and I'm able to connect to test_1 db
> >
> > But when i try the following
> > jdbc:informix-sqli://HOST_1:1990/test_2:INFORMIXSERVER=INFORMIXSERVER_2
> >
> > Error:'Can't load driver java.lang.reflect.InvocationTargetException
> >
> > Any comment If such connection is possible where I connect to HOST_1 but
> > then
> > try to access a database test_2 on INFORMIXSERVER_2 via port 1990, if yes
> > that
> > what should i check for?
> >
> > May be not the best way to do this but....
> >
> > Regards,
> > Vikas
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0023545bd6581c11390477f33c54
Hi,
yes, direct connection is fast and working but for some reasons i have to get
this via via connection working and that too with in the connection string.
Even I'm hoping for some hint, I'm having a look at dynamic sqlhosts lookup
but nothing working yet.
Regards,
Vikas
*******************************************************************************
Don't know more. I don't do JDBC at all myself. Note that direct remote
connections to a remote server tend to be faster than a forwarded connection
through the local server to the remote.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Mon, Nov 9, 2009 at 10:21 AM, VIKAS HIVARKAR
<vikas.hivarkar@gmail.com>wrote:
> Hello Art,
>
> --You can test the remote connection between the two servers using dbaccess
> as
> follows
> INFORMIXSERVER=INFORMIXSERVER_1> dbaccess test_2@INFORMIXSERVER_2 -
>
> The above test works in a flash and i am able to connect the test_2
> database
> on INFORMIXSERVER_2 instance from INFORMIXSERVER_1 on HOST_1 !
>
> I tried
> jdbc:informix-sqli://HOST_1:1980/test_2@INFORMIXSERVER_2
> :INFORMIXSERVER=INFORMIXSERVER_1
>
> But it interprets "test_2@INFORMIXSERVER_2" as the name of the database
> and
> not as test_2 database on INFORMIXSERVER_2 instance.
>
> As you mentioned that the JDBC connection should work I also checked for
> the
> topic on "Dynamically Reading the Informix SQLHOSTS file" here i tried
>
>
>
>
jdbc:informix-sqli://HOST_1:1980/test_2:INFORMIXSERVER=INFORMIXSERVER_2;SQLH_TYP
E=FILE;SQLH_FILE=/opt/informix/ids/etc/sqlhosts
>
> But no luck eighter.
>
>
>
>
*******************************************************************************
>
> You can certainly connect directly to the INFORMIXSERVER_2 on HOST_2 to
> access the test_2 database there or you can connect to INFORMIXSERVER_1 on
> HOST_1 and access the test_2 database remotely through the INFORMIXSERVER_1
> server which will connect to INFORMIXSERVER_2 on your behalf (as long as
> the
> remote server is defined in the sqlhosts file on the local machine). Your
> connection string should look something like:
>
> jdbc:informix-sqli://HOST_1:1980/test_2@INFORMIXSERVER_2
> :INFORMIXSERVER=INFORMIXSERVER_1
>
> You can test the remote connection between the two servers using dbaccess
> as
> follows:
>
> INFORMIXSERVER=INFORMIXSERVER_1> dbaccess test_2@INFORMIXSERVER_2 -
>
> If that works, the jdbc connection should work as well.
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Oninit, the IIUG, nor any other organization
> with which I am associated either explicitly or implicitly. Neither do
> those opinions reflect those of other individuals affiliated with any
> entity
> with which I am affiliated nor those of the entities themselves.
>
> On Mon, Nov 9, 2009 at 7:18 AM, VIKAS HIVARKAR
> <vikas.hivarkar@gmail.com>wrote:
>
> > Hi all,
> >
> > My last post was not very clear so I try again.
> >
> > I have :
> > INFORMIXSERVER_1 running on HOST_1 against port 1980
> > INFORMIXSERVER_2 running on HOST_2 against port 1990
> >
> > Both HOST entires are present in /etc/hosts, sqlhosts and /etc/services
> and
> > both instances are online!
> > I can simply edit the env on HOST_1 to point to INFORMIXSERVER_2 then do
> a
> > dbaccess and connect the INFORMIXSERVER_2.
> >
> > Now comes the JDBC part :(
> > I'm using a front end tool "SQLWorkbench" which uses the
> > "com.informix.jdbc.IfxDriver" & the connection string is as:
> >
> > jdbc:informix-sqli://HOST_1:1980/test_1:INFORMIXSERVER=INFORMIXSERVER_1
> > Every thing works fine and I'm able to connect to test_1 db
> >
> > But when i try the following
> > jdbc:informix-sqli://HOST_1:1990/test_2:INFORMIXSERVER=INFORMIXSERVER_2
> >
> > Error:'Can't load driver java.lang.reflect.InvocationTargetException
> >
> > Any comment If such connection is possible where I connect to HOST_1 but
> > then
> > try to access a database test_2 on INFORMIXSERVER_2 via port 1990, if yes
> > that
> > what should i check for?
> >
> > May be not the best way to do this but....
> >
> > Regards,
> > Vikas
Hi,
I remember we once did something like this.
We connected to the primary server as usual
and then made queries with databasename@database_instance:tablename
Alternatively we might have executed "database
databasename@database_instance"
statement on the connection and then use normal statements, I cannot
remember and
I have no hands on the sources right now, cause it was in an old
company.
I do not think it is possible to connect indirectly to the second
instance via a JDBC String.
Both is worth a try, if you need to connect to both databases within the
same connection.
In case you need both databases in one transaction, maybe you should use
XA,
but this is more complex because you need a TransactionManager.
Marcus
-----Original Message-----
From: VIKAS HIVARKAR [mailto:vikas.hivarkar@gmail.com]
Sent: Tuesday, November 10, 2009 10:21 AM
To: ids@iiug.org
Subject: Re: JDBC connection (Hopping) [18025]
Hi,
yes, direct connection is fast and working but for some reasons i have
to get this via via connection working and that too with in the
connection string.
Even I'm hoping for some hint, I'm having a look at dynamic sqlhosts
lookup but nothing working yet.
Regards,
Vikas
************************************************************************
*******
Don't know more. I don't do JDBC at all myself. Note that direct remote
connections to a remote server tend to be faster than a forwarded
connection through the local server to the remote.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Oninit, the IIUG, nor any other
organization with which I am associated either explicitly or implicitly.
Neither do those opinions reflect those of other individuals affiliated
with any entity with which I am affiliated nor those of the entities
themselves.
On Mon, Nov 9, 2009 at 10:21 AM, VIKAS HIVARKAR
<vikas.hivarkar@gmail.com>wrote:
> Hello Art,
>
> --You can test the remote connection between the two servers using
> dbaccess as follows
> INFORMIXSERVER=INFORMIXSERVER_1> dbaccess test_2@INFORMIXSERVER_2 -
>
> The above test works in a flash and i am able to connect the test_2
> database on INFORMIXSERVER_2 instance from INFORMIXSERVER_1 on HOST_1
> !
>
> I tried
> jdbc:informix-sqli://HOST_1:1980/test_2@INFORMIXSERVER_2
> :INFORMIXSERVER=INFORMIXSERVER_1
>
> But it interprets "test_2@INFORMIXSERVER_2" as the name of the
> database and not as test_2 database on INFORMIXSERVER_2 instance.
>
> As you mentioned that the JDBC connection should work I also checked
> for the topic on "Dynamically Reading the Informix SQLHOSTS file" here
> i tried
>
>
>
>
jdbc:informix-sqli://HOST_1:1980/test_2:INFORMIXSERVER=INFORMIXSERVER_2;
SQLH_TYPE=FILE;SQLH_FILE=/opt/informix/ids/etc/sqlhosts
>
> But no luck eighter.
>
>
>
>
************************************************************************
*******
>
> You can certainly connect directly to the INFORMIXSERVER_2 on HOST_2
> to access the test_2 database there or you can connect to
> INFORMIXSERVER_1 on
> HOST_1 and access the test_2 database remotely through the
> INFORMIXSERVER_1 server which will connect to INFORMIXSERVER_2 on your
> behalf (as long as the remote server is defined in the sqlhosts file
> on the local machine). Your connection string should look something
> like:
>
> jdbc:informix-sqli://HOST_1:1980/test_2@INFORMIXSERVER_2
> :INFORMIXSERVER=INFORMIXSERVER_1
>
> You can test the remote connection between the two servers using
> dbaccess as
> follows:
>
> INFORMIXSERVER=INFORMIXSERVER_1> dbaccess test_2@INFORMIXSERVER_2 -
>
> If that works, the jdbc connection should work as well.
>
> Art
>
> Art S. Kagel
> Oninit (www.oninit.com)
> IIUG Board of Directors (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own
> opinions and do not reflect on my employer, Oninit, the IIUG, nor any
> other organization with which I am associated either explicitly or
> implicitly. Neither do those opinions reflect those of other
> individuals affiliated with any entity with which I am affiliated nor
> those of the entities themselves.
>
> On Mon, Nov 9, 2009 at 7:18 AM, VIKAS HIVARKAR
> <vikas.hivarkar@gmail.com>wrote:
>
> > Hi all,
> >
> > My last post was not very clear so I try again.
> >
> > I have :
> > INFORMIXSERVER_1 running on HOST_1 against port 1980
> > INFORMIXSERVER_2 running on HOST_2 against port 1990
> >
> > Both HOST entires are present in /etc/hosts, sqlhosts and
> > /etc/services
> and
> > both instances are online!
> > I can simply edit the env on HOST_1 to point to INFORMIXSERVER_2
> > then do
> a
> > dbaccess and connect the INFORMIXSERVER_2.
> >
> > Now comes the JDBC part :(
> > I'm using a front end tool "SQLWorkbench" which uses the
> > "com.informix.jdbc.IfxDriver" & the connection string is as:
> >
> > jdbc:informix-sqli://HOST_1:1980/test_1:INFORMIXSERVER=INFORMIXSERVE
> > R_1 Every thing works fine and I'm able to connect to test_1 db
> >
> > But when i try the following
> > jdbc:informix-sqli://HOST_1:1990/test_2:INFORMIXSERVER=INFORMIXSERVE
> > R_2
> >
> > Error:'Can't load driver java.lang.reflect.InvocationTargetException
> >
> > Any comment If such connection is possible where I connect to HOST_1
> > but then try to access a database test_2 on INFORMIXSERVER_2 via
> > port 1990, if yes that what should i check for?
> >
> > May be not the best way to do this but....
> >
> > Regards,
> > Vikas
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.