help with oracle adapter

rubyonrails-talk
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"   ) ............. 2) 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

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

3

"Greg Donald"

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

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

5

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

6

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

7

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

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.

2) 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...

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?

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.

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?

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

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.