Forum: Ruby on Rails How can I connect to existing database

04ee61ffa169eea4fe98749292245752?d=identicon&s=25 Dwayne Brown (lluvian)
on 2012-07-15 20:11
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 =)
Bee69cfed999cd13e3bff73d472a39ee?d=identicon&s=25 Hassan Schroeder (Guest)
on 2012-07-15 20:31
(Received via mailing list)
On Sun, Jul 15, 2012 at 11:11 AM, Dwayne Brown Dwayne
<lists@ruby-forum.com> wrote:

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

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,
--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
http://about.me/hassanschroeder
twitter: @hassan
04ee61ffa169eea4fe98749292245752?d=identicon&s=25 Dwayne Brown (lluvian)
on 2012-07-15 21:24
> 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?
Bee69cfed999cd13e3bff73d472a39ee?d=identicon&s=25 Hassan Schroeder (Guest)
on 2012-07-15 21:39
(Received via mailing list)
On Sun, Jul 15, 2012 at 12:24 PM, Dwayne Brown Dwayne
<lists@ruby-forum.com> wrote:

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

> 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,
--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
http://about.me/hassanschroeder
twitter: @hassan
04ee61ffa169eea4fe98749292245752?d=identicon&s=25 Dwayne Brown (lluvian)
on 2012-07-16 00:36
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.
Bee69cfed999cd13e3bff73d472a39ee?d=identicon&s=25 Hassan Schroeder (Guest)
on 2012-07-16 01:02
(Received via mailing list)
On Sun, Jul 15, 2012 at 3:36 PM, Dwayne Brown <lists@ruby-forum.com>
wrote:
>  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 :-)  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!
--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
http://about.me/hassanschroeder
twitter: @hassan
04ee61ffa169eea4fe98749292245752?d=identicon&s=25 Dwayne Brown (lluvian)
on 2012-07-16 01:08
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.
Bee69cfed999cd13e3bff73d472a39ee?d=identicon&s=25 Hassan Schroeder (Guest)
on 2012-07-16 01:20
(Received via mailing list)
On Sun, Jul 15, 2012 at 4:08 PM, Dwayne Brown <lists@ruby-forum.com>
wrote:

> CustomerID(Integer)

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

Good luck.
--
Hassan Schroeder ------------------------ hassan.schroeder@gmail.com
http://about.me/hassanschroeder
twitter: @hassan
04ee61ffa169eea4fe98749292245752?d=identicon&s=25 Dwayne Brown (lluvian)
on 2012-07-16 01:22
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.
5f94b9b346c2aa648a80bc259978e5bc?d=identicon&s=25 Colin Law (Guest)
on 2012-07-16 08:32
(Received via mailing list)
On 15 July 2012 23:36, Dwayne Brown <lists@ruby-forum.com> wrote:
> I also use Linux and Windows. My project is on Windows and the path you
> gave me was correct thanks.

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

Colin
5f94b9b346c2aa648a80bc259978e5bc?d=identicon&s=25 Colin Law (Guest)
on 2012-07-16 08:40
(Received via mailing list)
On 15 July 2012 19:11, Dwayne Brown Dwayne <lists@ruby-forum.com> wrote:
> one day because it has been that addictive. I never liked command line
> Remember I am a novice =)
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
04ee61ffa169eea4fe98749292245752?d=identicon&s=25 Dwayne Brown (lluvian)
on 2012-07-16 13:31
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.
04ee61ffa169eea4fe98749292245752?d=identicon&s=25 Dwayne Brown (lluvian)
on 2012-07-16 13:54
> 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.

I think what my problem is now is how to call the data in the controller
so I can display it in a view. Once I study this I can get the site
going.
5f94b9b346c2aa648a80bc259978e5bc?d=identicon&s=25 Colin Law (Guest)
on 2012-07-16 15:12
(Received via mailing list)
On 16 July 2012 12:54, Dwayne Brown <lists@ruby-forum.com> wrote:
> 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
04ee61ffa169eea4fe98749292245752?d=identicon&s=25 Dwayne Brown (lluvian)
on 2012-07-16 15:16
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.

Thanks for everything Hassan, I have actually picked up some useful
information on RoR and will revisit this at a later date when I get a
better foundation.
6883e5ef03484d4fcef507d7b4f1d243?d=identicon&s=25 Matt Jones (Guest)
on 2012-07-17 14:19
(Received via mailing list)
On Monday, 16 July 2012 09:16:32 UTC-4, Ruby-Forum.com User wrote:
>
> 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.
>

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
04ee61ffa169eea4fe98749292245752?d=identicon&s=25 Dwayne Brown (lluvian)
on 2012-07-17 14:40
> 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.
5f94b9b346c2aa648a80bc259978e5bc?d=identicon&s=25 Colin Law (Guest)
on 2012-07-17 14:51
(Received via mailing list)
On 16 July 2012 14:16, Dwayne Brown <lists@ruby-forum.com> wrote:
> 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.

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
84c8411c59ddca8d82e65932f10e08e9?d=identicon&s=25 praneeth thota (praneeththota)
on 2012-07-17 15:04
(Received via mailing list)
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
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.