Help with oracle adapter

Rails
1

I have been snooping around this forum for a while so hopefully i have
posted this the correct place. I need help getting oracle to work. I
have read a lot of posts and search google but i believe my situation
may be unique

I use aptana to program in ruby on rails. I am trying to connect to a
remote oracle database on a different network that i do not control.
Although i cannot see the point of it, i have setup the instant client
package from oracle.com. I have not setup a tnsnames.ora file.
Ofcourse, I setup a tunnel to the remote network, so i access the
database through localhost.

Now please note, very important here, I can clearly see all of my
oracle databases, in aptana dataview, which means that my database.yml
file as well as my tunnel, should be configured correctly. I can send
sql queries to the database successfully

The problem occurs when i try to innitiate a connection to the
database through a model. I have tried the following code in my
model :

  1. ActiveRecord::Base::establish_connection(
    :adapter => “oracle”, #same file as oci_adapter just renamed
    correct?
    :host => “ip/sid”,
    :user => "user,
    :password =>“pass”
    )
    …

establish_connection(“name setup in the database.yml file”)

etc etc etc… Just about every possible combiniation of the above. I
have also gotten all of the errors one could expect like ORA-12560
etc.

How do i rectify this situation? The yml file seems setup correctly
and i can communicate with the database, just not when i actually need
to interact with it through the model.

2

On 2/26/08, nobility [email protected] wrote:

  1. ActiveRecord::Base::establish_connection(
    :adapter => “oracle”, #same file as oci_adapter just renamed
    correct?

It may need to be the older ‘oci8’ depending on your driver version.

–
Greg D.
http://destiney.com/

3

“Greg D.”

It may need to be the older ‘oci8’ depending on your driver version.

thank you very much for your suggestion but i have already attempted
the following, directly in the controller

require 'oci8'
OCI8.new 'user', 'pass', '//localhost/sid'

but no success. At this point i am willing to download a totally new
driver if it would help. Please, anymore ideas?

4

On 2/27/08, nobility [email protected] wrote:

tried using an older oci8 driver with no success. Has anyone
successfully connected to a remote oracle database?

You mean like, ever? Yes, no problems here.

–
Greg D.
http://destiney.com/

5

tried using an older oci8 driver with no success. Has anyone
successfully connected to a remote oracle database?

6

On Feb 27, 1:56 pm, “Greg D.” [email protected] wrote:

Greg D.http://destiney.com/
Yeah thats exactly what i was asking, “ever”.

Maybe you can help me with some specifics then.

  1. With the fact that the database is remote, do i really need the
    instant client?
  2. Did you setup a tsanames.ora file and if so where would do you put
    it?
  3. the only thing thats needed for this adapter to work, is the 3
    files oci8, two files from instantclient put into the ruby/bin folder,
    the oracle adapter renamed to oci_adapter, and the database.yml file
    configured to look for the database?
  4. Is there something special that has to be included in the model so
    that i can call declarations in the database.yml file successfully?
    5 ) even if i ever get this working, where can i find info on setting
    up migrations etc for these extra databases, since i still have mysql
    designated in the yml file as development
7

tried using an older oci8 driver with no success. Has anyone
successfully connected to a remote oracle database?

8

nobility wrote:

  1. With the fact that the database is remote, do i really need the
    instant client?

Yes. The ruby-oci library is just an interface to the Oracle library.
Since you are getting Oracle errors reported, I can only assume that
Ruby is finding this library.

  1. Did you setup a tsanames.ora file and if so where would do you put
    it?

You don’t need tnsnames.ora if you are using the ‘//host:port/sid’ form
of database naming.

If your tunnel on your local machine is not listening on the standard
Oracle listener port (1521), then you must place this port number after
the host in the connect string. So, for example, if your tunnel is
connecting port 4444 on your local machine to port 1521 on the database
server, use ‘//localhost:4444/sid’ in your database.yml file.

Also, remember that the sid in the string must be the sid of the
database running on the server, not a local connection alias…

  1. the only thing thats needed for this adapter to work, is the 3
    files oci8, two files from instantclient put into the ruby/bin folder,
    the oracle adapter renamed to oci_adapter, and the database.yml file
    configured to look for the database?

Although the old oci adapter should work, if you are using Rails 2 you
should probably mode to the separate adapter available as the gem
activerecord-oracle-adapter and use “oracle” as the adapter name in your
database.yml file.

The files you actually need from the Oracle instant client are the
libraries. These would normally either go somewhere standard where your
system looks for libraries (eg /usr/lib or /usr/local/lib) or else you
would need to set, say, a LD_LIBRARY_PATH environment variable to
include the location where they are. It does seem, however, that you
are accessing the libraries, so I think this is ok.

  1. Is there something special that has to be included in the model so
    that i can call declarations in the database.yml file successfully?

If there is a database connection defined with the same name as the
application run mode (normally “test”, “development” or “production”)
then it will get picked up automatically and used. Nothing special to
do. Again, since you are getting Oracle errors from your application it
would seem that this part is working ok.

5 ) even if i ever get this working, where can i find info on setting
up migrations etc for these extra databases, since i still have mysql
designated in the yml file as development

If this remote database is your production database, you can run:

rake db:schema:load RAILS_ENV=production

to load the schema into it.

If this doesn’t help, then perhaps you could start the application
console (script/console), execute something which accesses a model
(something simple like “ModelName.count” will do to try to count the
number of rows in that model’s table) and then post the output produced,
and also the section of your log file from running that statement. It
won’t matter if the table is not in the database yet since you seem to
be suggesting that the application cannot even connect. This might help
to identify what exactly is failing…

9

On Feb 27, 3:01 pm, Mark B. [email protected]
wrote:

Posted viahttp://www.ruby-forum.com/.

Thank you very much, that definitely rules out a lot of things that i
was worrying about.

I guess its hard to relay how close i am to the solution, because i
doubt everyone uses aptana to program in rails. Aptana has a view
called dataview which is a visual description of the database with
options to edit it etc. When the database.yml file is setup correctly,
I can see the databases in this view as well as send queries to them.
I am totally confused how one aspect that depends on the yml file
works, but the activerecord part that depends on the same yml file
doesnt work. I even call that specific declaration from the
database.yml file, directly in the model. For example
self::establish_connection(:example1).

believe it or not, i have 2 declarations in my database.yml file and
they both work in the data view, connecting to the same database.
They are :

example1:
adapter: oci
host: localhost/database
username: usr
password: pass

example2:
adapter: oci
database: database
username: usr
password: pass
…

As for a simple query, i am calling ModelName.find(:all), althought i
just tried the ModelName.count thing you recommended.

To recap, the same declaration in the database.yml file that allows me
to interact with the databases in the dataview (example1, example2),
is the same one being called in the model, but i am getting the
following error : (ORA-12514: TNS:listener does not currently know of
service requested in connect descriptor ) in the view, when i try to
manipulate the database through activerecord.

facts: i am using rails 2.0.2 on vista, in Aptana, trying to connect
to a remote oracle database.

assumptions:

  1. since the dataview depends on the database.yml file, the yml file
    is setup correctly
  2. since the data view wont work when the tunnel is shut down, the
    tunnel works
  3. since purposly putting a fake name in the establish_connection
    string (e.g establish_connection(:example11222). throws an error of
    not declared, then i am correctly accessing the declarion in the
    database.yml file when i use establish_connection(:example1)
  4. Since i can manipulate the database through the data view, All
    necessary files for communication are in the correct place and there
    is no conflict with rails, the database, or Aptana

If anyone has any suggestions or can prove any of my assumptions
wrong, i will be much appreciative.