Oracle uri format does not allow connecting by service name #22
Comments
|
URI::oracle does support SIDs if no host name or port is specified: Line 20 in 0c9e8e6 |
|
I agree that it supports connecting by SID, but not by session name. As I wrote earlier,
But, I should have written "Service Name" rather than "Session Name" earlier. I will correct that now. |
vectro
changed the title
|
What would an interface for supporting a service name look like? How would one distinguish it from a SID? Also note that you can take the URI parts and do what you want with them, you don't have to rely exclusively on URI::db's interpretation of what to pass to |
DBD::Oracle has many different ways of specifying the connection properties (see documentation); however, the one used by URI::db does not support specifying the service name.
That is true if you control the caller, but not if URI::db is called from another tool (such as Sqitch). |
|
Ah, perhaps URI::oracle should not prepend |
I think this will do the right thing, but of course that is not compatible with also specifying the hostname or port, which you might want to do sometimes. I don't have a strong opinion about this but it seems like maybe you want something like:
|
|
Okay here's what I think is worth trying:
|
|
Actually, that is how it works! Does that not allow the DBD::Oracle to interpret Oh, but if it ha a host name: So the question is: if there is a host or port, how do we also support a service name. We could use the EZCONNECT URL, which would be: In that case, does it know whether Should we perhaps use Hrm, looking at the EZCONNECT again, maybe we should just replace |
The goal is to get away from hard-coding support for a SID, since we can't actually tell whether the database name part of the URL should be a SID or a service name. The hope is that by just returning an EZCONNECT URL, instead, the DBI or Oracle client library it uses can make the appropriate determination. Resolves #22.
|
Seems like that should work. So let's try it. Check out #23 and see what you think. |
The goal is to get away from hard-coding support for a SID, since we can't actually tell whether the database name part of the URL should be a SID or a service name. The hope is that by just returning an EZCONNECT URL, instead, the DBI or Oracle client library it uses can make the appropriate determination. Resolves #22.
|
Okay, the solution is probably gonna be either 160af71, which simply returns an EZCONNECT URL, but then incorrectly returns nothing from I think that 9fd8bfa is the correct approach, but let me know if it doesn't work for you. |
|
Just to test that using |
|
Thanks David for looking at this so quickly. sqitchers/sqitch@a49d012 work for me. I didn't test either of the changes in this repo (but will, if you like). They both look fine to me, with some caveats:
|
|
Agreed about the documentation for
|
Confirmed, thanks! I also tried this with a port number but no host, and that resulted in the "Can't connect using this syntax" error from DBD::Oracle. |
|
Thank you. Presumably the result is the same when using an EZCONNECT URL with a port and no host name, yes? If so, I think we're good here, since the presence of a port with no host name doesn't appear to be supported by any DSN syntax. |
|
I did not double check the behavior when it is missing, but hostname is documented to be required with EZCONNECT. |
|
Yes, I saw that, too. I think we're good to go here, will merge and release. |
|
v0.21 now on CPAN. |
|
So, I'm very embarrassed to say this, but I think may have provided incorrect information earlier, and unfortunately I think this is still not working for all cases. Should I open a new issue? Or reopen this one? Earlier I wrote this:
However, I think that was actually wrong, and I'm not able to reproduce this now. Maybe it's possible that I tested the wrong code by mistake? Looking more closely at the source code for DBD::Oracle, it's not possible to use I think that probably brings us back to this comment? But I could be wrong. |
|
I don't know, and not being an Oracle user, it'd be very helpful to me if someone just explained what the correct behavior should be. If you're willing and able to try some experiments, you can use this script: #!/usr/bin/env perl -w
use strict;
use warnings;
use v5.20;
use DBI;
my ($dsn, $user, $pass) = @ARGV;
die "Usage $0 DSN [USERNAME [PASSWORD]]\n" unless $dsn;
my $dbh = DBI->connect($dsn, $user, $pass, {
RaiseError => 1,
PrintError => 0,
AutoCommit => 1,
});
say $dbh->selectcol_arrayref("SELECT 'connected' FROM DUAL")->[0];Save it and call it like this: try_connect 'dbi:Oracle:service_name:foo;host=bar'Be sure to quote the DSN to prevent the shell from interpreting characters like If it prints "connected" then it connected and successfully ran a query. Pass a username and password as the second and third args if you need them. Try different combinations and figure out what works and what doesn't, with different Thanks! |
|
I'm happy to do that. Results are below; let me know if there are other configurations which would be helpful to test. Use case: Connecting to a SID
|
|
Does the service name |
It does not, because in this use case Use case: Connecting to a database running on the local host, on port 1521, with the service name
|
|
Well I have no bloody idea what the right solution is for converting URI to the appropriate DBD::Oracle DSN. But am I right in thinking that a service name requires a host name to work, but a SID may not? Uf you have a SID, does |
I think that is the
One path forward may be something similar to (or the same as) what Oracle did for the JDBC URI. |
|
Also does this earlier suggestion not work?
|
|
Okay, please try this: diff --git a/lib/URI/oracle.pm b/lib/URI/oracle.pm
index 4520c20..481f7d2 100644
--- a/lib/URI/oracle.pm
+++ b/lib/URI/oracle.pm
@@ -5,13 +5,26 @@ our $VERSION = '0.22';
sub default_port { 1521 }
sub dbi_driver { 'Oracle' }
-sub _dbi_param_map {
+sub _dsn_params {
my $self = shift;
- return (
- [ host => scalar $self->host ],
- [ port => scalar $self->_port ],
- [ service_name => scalar $self->dbname ],
- );
+ my $name = $self->dbname || '';
+ my $dsn = $self->host || return $name;
+
+ if (my $p = $self->_port) {
+ $dsn .= ":$port";
+ }
+
+ $dsn .= "/$name";
+
+ if (my @p = $self->query_params) {
+ my @kvpairs;
+ while (@p) {
+ push @kvpairs => join '=', shift @p, shift @p;
+ }
+ $dsn .= '?' . join ';' => @kvpairs;
+ }
+
+ return "//$dsn";
}
1;It assumes that URL query parameters should be included in the EZCONNECT string. I don't know if that's actually a thin in Oracle, but if not then one is going to use URL parameters anyway, so should, be harmless. |
|
This patch works for me, at least. I'm able to connect to both (a) a SID and (b) a service name + host name.
Looking at the documentation for EZCONNECT, I see that it does support parameter values and also that there is a ton of other complexity there.
|
|
Looks like plain old URL query params, which URI::db an of course provide. |
Again. It now works as follows:
- If there is not a hostname or port, just use the raw SID or service
name as the database name: `dbi:Oracle:$name`. This is way 1 in the
DBD::Oracle documentation
- If there is a hostname or port, use the EZCONNECT syntax. This is
way 3 in the DBD::Oracle documentation.
Use of a port without a host name may not be valid, but it seems most
prudent to build an EZCONNECT that includes the port in this context and
to let Oracle or DBD::Oracle reject it if appropriate. Thanks again to
@vectro for the and diligence, testing, and patience with this issue
(#22).
Again. It now works as follows:
- If there is not a hostname or port, just use the raw SID or service
name as the database name: `dbi:Oracle:$name`. This is way 1 in the
DBD::Oracle documentation
- If there is a hostname or port, use the EZCONNECT syntax. This is
way 3 in the DBD::Oracle documentation.
Use of a port without a host name may not be valid, but it seems most
prudent to build an EZCONNECT that includes the port in this context and
to let Oracle or DBD::Oracle reject it if appropriate. Thanks again to
@vectro for the and diligence, testing, and patience with this issue
(#22).
|
Okay, see what you think of #25. |
Again. It now works as follows:
- If there is not a hostname or port, just use the raw SID or service
name as the database name: `dbi:Oracle:$name`. This is way 1 in the
DBD::Oracle documentation
- If there is a hostname or port, use the EZCONNECT syntax. This is
way 3 in the DBD::Oracle documentation.
- If there are query parameters, delimit them by `&` and not `;`.
Use of a port without a host name may not be valid, but it seems most
prudent to build an EZCONNECT that includes the port in this context and
to let Oracle or DBD::Oracle reject it if appropriate. Thanks again to
@vectro for the and diligence, testing, and patience with this issue
(#22).
The
DBD::Oraclemodule supports a few different connection string formats, all of which support specifying either the SID or the service name. However, the URI-db library only support specifying the SID. This unfortunately means that connecting to an Oracle PDB is not possible without enumerating the PDB in a TNSNAMES.ora file.The text was updated successfully, but these errors were encountered: