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