How can I connect to existing database

Hello community,

This is my first post and first time getting into ruby on rails. I am a novice and ran into rails when experimenting with Spiceworks. I decided to learn rails since Spiceworks was built off this technology and I wanted to learn more about how Spiceworks runs in the background. Well things spead up pretty fast on getting features to work since certain things do not run out the box. I know to do something big you need to start small but I do not have the luxury because of time constrants. I bought a book Agile Web Development with Rails and got to chapter 6 in one day because it has been that addictive. I never liked command line until this language.

I can pick up the syntax soon enough but I want to at least get one skill down because I will need to demonstrate the advantage of using this technology in my enviroment versus something I already created in .net.

Is there some step by step tutorial to connect to an external database that runs off of SQLite. Spiceworks runs off SQLite and we have data I want to pull into my site. If anyone can help I would appreciate it. Remember I am a novice =)

A SQLite "database" is a single file. Put the location of that file (or, better, a copy) in your config/database.yml and you're ready to go.

If you generate a new Rails app, it will use SQLite by default, so do `rails new example`, cd to that directory, run `rake db:create` and take a look at the contents of your config/database.yml and what's in the db directory.

HTH,

A SQLite "database" is a single file. Put the location of that file (or, better, a copy) in your config/database.yml and you're ready to go.

If you generate a new Rails app, it will use SQLite by default, so do `rails new example`, cd to that directory, run `rake db:create` and take a look at the contents of your config/database.yml and what's in the db directory.

To make sure I understand I will need to do the following:

1. rails new example 2. cd example 3. rake db:create 4. yml file    development:    adapter: sqlite3    database: db/C:\database\testdatabase.db    pool: 5    timeout: 5000

If I have a SQLite3 database file in my C:\database folder for example, does the above code look right? If so, what would be the next steps to use it? Is there a scaffold I can use or do I manually create my model, views, and controllers with rails generate?

   database: db/C:\database\testdatabase.db

> If I have a SQLite3 database file in my C:\database folder for example, does the above code look right?

No. The 'db' there is the directory in the Rails app; if you want to use the file where it is

   database: C:\database\testdatabase.db

That should work, but I don't use Windows, so try it and see what happens. :slight_smile:

If so, what would be the next steps to use it? Is there a scaffold I can use or do I manually create my model, views, and controllers with rails generate?

If it's a database not originally designed with Rails in mind, it may not be all that easy to use scaffold-generated models, but again - give it a shot. I would pick a table and try to hand-create a model that works with it first, myself.

HTH,

I also use Linux and Windows. My project is on Windows and the path you gave me was correct thanks.

I deleted my file and tried again from start just to drill in the steps. I did modifications in the below order before running into an error:

1. rails new example 2. cd example 3. rails generate controller Customer index 4. yml file    development:    adapter: sqlite3    database: db/C:\database\testdatabase.db    pool: 5    timeout: 5000 5. rake db:create 6. rails generate model Customer firstName:string middleName:string lastName:string email:string 7. rails server

I receive the below error:

Started GET "/assets/rails.png" for 127.0.0.1 at 2012-07-15 17:25:00 -0500 Served asset /rails.png - 304 Not Modified (0ms) [2012-07-15 17:25:00] WARN Could not determine content-length of response body. Set content-length of the response or set Response#chunked = true

My SQLite sample database consist of a (CustomerID(Text), firstName(Text), middleName(Text), lastName(Text), email(Text))

I do not know what he error means but sounds like it does not know where my strings end.

My project is on Windows and the path you gave me was correct thanks.

   database: db/C:\database\testdatabase.db

but you're not using it :slight_smile: As I said the 'db/' refers to the directory 'db' in your rails app. The above is unlikely to work.

6. rails generate model Customer firstName:string middleName:string lastName:string email:string

I receive the below error:

17:25:00] WARN Could not determine content-length of response body.

Not an error, it's a warning. I wouldn't worry about it now.

My SQLite sample database consist of a (CustomerID(Text), firstName(Text), middleName(Text), lastName(Text), email(Text))

Your "CustomerID" will be the first obstacle, since it doesn't conform to Rails conventions for a primary key. You can find a fix for that if you can't change it.

But forget about running the server, open up a console and try to access your table. If you've created a model and run the migrations, you should be able to use e.g. `Customer.first` to retrieve a record and examine it.

Good luck and have fun!

Serveral typos: database: C:\database\testdatabase.db CustomerID(Integer)

That is the way I had it when I set it up. Sorry about the typos.

If at all possible, you would be much better off to change the existing database column names to match Ruby/Rails conventions:

  id, first_name, middle_name, last_name etc.

I've been down this legacy database road more than once, and the work to restructure it via migrations is well worthwhile. :slight_smile:

Good luck.

I am going to take some time and go over the steps again. I am going to use that convention you described over the next couple of day and see if I can get this going. I will respond with what I come out with.

If you have a choice then I strongly advise that you use Linux for RoR development rather than windows.

Colin

Are you trying to use the external database as the only database in your rails app? If so then the suggestions others have already offered is the way to go it, but in addition I would google for rails legacy database for help on how to access an existing db from rails. I would say, however, that this can be tricky as the db schema presumably does not follow the rails conventions. I suggest that this could be a difficult task for a rails beginner.

If instead you are trying to pull in data from the legacy db into your app, but your app has additionally its own db then this is an entirely different problem.

Either way I suggest working right through some tutorials such as railstutorial.org (which is free to use online) to get a grasp of the basics of rails. Make sure that you are using rails 3 and that the tutorial or book you use uses the same version of rails that you have (at least the first two levels of the version, eg 3.2)

Colin

Hi Colin,

I agree with this being a difficult task for a beginner but with the time I have I do not have a choice. I am using the latest version of ruby and rails. If I get this project to work I will be going back to the basics because I usually start off small. I like to know all the reasons of why things are done the way they are from the basics to get a good foundation. I have already when through the only ruby tutorial basics.

I agree with this being a difficult task for a beginner but with the time I have I do not have a choice. I am using the latest version of ruby and rails. If I get this project to work I will be going back to the basics because I usually start off small. I like to know all the reasons of why things are done the way they are from the basics to get a good foundation. I have already when through the only ruby tutorial basics.

I forgot to mention that the good news with the database I am trying to connect too was created using a RoR app that uses the latest version of ruby and rails and SQLite3. The database uses the rails naming conventions.

Then just pick up the existing application and work with that. There is no point writing a new app to access the same database.

Colin

You can't. If I could I would but that is not an option. We talked to the developers and the only way to achieve this is through another interface. I will just stick with asp.net since I have it developed. I was trying to avoid having to set up IIS.

Try talking to some new developers, because the ones you’ve got should be FIRED. Running SQLite in production is bad enough, but expecting it to handle access from multiple applications? Surely there’s a real DB someplace in the shop…

–Matt Jones

Try talking to some new developers, because the ones you've got should be FIRED.

I understand completely what you mean; however, this is one of those situations we do not have a choice for the database for this product. If I went in complete detail of why and hows then it would make sense. MySQL would be great for this but its not an option.

Pick up the code of the other application and strip out what you do not need, then put in what you do want for your interface.

Colin

mysql = Mysql.init()
mysql.connect('localhost')
mysql.select_db('test')

mysql.query("DROP TABLE IF EXISTS rocks")

mysql.query("CREATE TABLE rocks (id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY (id), rockname CHAR(20) NOT NULL);")

mysql.query("INSERT INTO rocks values('Granite');")
mysql.query("INSERT INTO rocks values('Coal');")

mysql.query("INSERT INTO rocks values('Quartz');")

mysql.close()

Try this. I hope this will be helpful