# Connect to MySQL Database

**URL:** <https://www.ruby-forum.com/t/connect-to-mysql-database/79138>\
**Category:** Ruby\
**Created:** [December 12, 2006, 2:34pm UTC](https://www.ruby-forum.com/t/connect-to-mysql-database/79138 "2006-12-12T14:34:03Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![jawosis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f04885/32.png) [@jawosis](https://www.ruby-forum.com/u/jawosis)\
**Post date:** [December 12, 2006, 2:34pm UTC](https://www.ruby-forum.com/t/connect-to-mysql-database/79138/1 "2006-12-12T14:34:03Z")

</div>

Hello list,

I run Mac OS X Tiger, ruby 1.8.4 (from sources) and MySQL 5.0.27  
(from sources).

I wan’t to create a script which is called by pure-ftpd’s  
uploadscript functionality - clamav scans each uploaded file - and  
store the scan results directly into a MySQL database.

What’s the recommended way to do this?

Thanx

---

<div class="post-metadata">

**Author:** ![jawosis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f04885/32.png) [@jawosis](https://www.ruby-forum.com/u/jawosis)\
**Post date:** [December 12, 2006, 3:24pm UTC](https://www.ruby-forum.com/t/connect-to-mysql-database/79138/2 "2006-12-12T15:24:15Z")

</div>

Hi,

> I run Mac OS X Tiger, ruby 1.8.4 (from sources) and MySQL 5.0.27 (from  
> sources).
> 
> I wan’t to create a script which is called by pure-ftpd’s uploadscript  
> functionality - clamav scans each uploaded file - and store the scan  
> results directly into a MySQL database.
> 
> What’s the recommended way to do this?

I would do it along these lines (untested):

require ‘og’

# [http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/165163](http://blade.nagaokaut.ac.jp/cgi-bin/scat.rb/ruby/ruby-talk/165163)

require ‘clamscanner’

class ClamavScanResult  
is Timestamped  
property :path, String  
property :result, Result  
end

Og.start(:store =\> :mysql, :name =\> ‘clamavdb’, :user =\> ‘testuser’,  
:password =\> ‘private’, :classes =\> [ClamavScanResult])

# scan this file

file = ARGV[0] # first argument is the file/path ?  
clamav = ClamScanner.new  
res = clamav.scan( file )

ClamavScanResult.create\_with(:path =\> file, :result =\> res)

**END**

Of course, the ‘recommended’ way could way differ from my own opinion  
on how this should be done. 😛

This has the downside that using Og instead of a pure mysql lib that  
the (startup) speed is a little worse. The plus side is, you get to  
switch to postgresql very fast (if you ever get that idea :P) and that  
the script is quite small and easy to read.

Enjoy.

---

<div class="post-metadata">

**Author:** ![jawosis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f04885/32.png) [@jawosis](https://www.ruby-forum.com/u/jawosis)\
**Post date:** [December 12, 2006, 7:43pm UTC](https://www.ruby-forum.com/t/connect-to-mysql-database/79138/3 "2006-12-12T19:43:41Z")

</div>

Kashia B. schrieb:

> property :result, Result  
> ClamavScanResult.create\_with(:path =\> file, :result =\> res)
> 
> **END**

Great, I will give it a try. Seems to be exactly what I need.

But what do I need to connect to a MySQL database?

\<? mysql\_connect(...... mysql\_select\_database(..... mysql\_query(------- ....display results in a html-table... ?\>

Has ruby no native MySQL support. Do I need to install extra  
modules, libs ??

---

<div class="post-metadata">

**Author:** ![jawosis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f04885/32.png) [@jawosis](https://www.ruby-forum.com/u/jawosis)\
**Post date:** [December 12, 2006, 8:24pm UTC](https://www.ruby-forum.com/t/connect-to-mysql-database/79138/4 "2006-12-12T20:24:03Z")

</div>

Hi,

> > class ClamavScanResult  
> > is Timestamped  
> > property :path, String  
> > property :result, String  
> > end

^^ that should’ve said String there, sorry.

> > Og.start(:store =\> :mysql, :name =\> ‘clamavdb’, :user =\> ‘testuser’,  
> > :password =\> ‘private’, :classes =\> [ClamavScanResult])

> …display results in a html-table…  
> ?\>
> 
> Has ruby no native MySQL support. Do I need to install extra modules,  
> libs ??

For my ‘idea’ you only need ‘Og’ (gem install og) and it’s dependencies.  
It comes with a built in mysql lib, so for your case not even a mysql  
lib is required.  
Og just needs the username (:user), password (:password) and the table  
(:name) to connect to any of its supported database systems.

My idea also includes, that you have _nothing_ do with the ‘database’,  
which is handles by Og (silently). To use a database is merely a way  
to structure data, which Og does quite nicely.

I see you’re using erb/eruby/erubis/mod\_ruby (unsure, never used  
either)?  
Or are you using Rails (which I also never used and comment on either)?

This part would then just ‘display’ what is in the database. What I  
posted you, would basically be the creating the data in the database.

On to how to display it using erb:

# hypothetical display.erb
\<? require 'og' Og.create\_schema = false class ClamavScanResult is Timestamped property :path, String property :result, String end Og.start(:store =\> :mysql, :name =\> 'clamavdb', :user =\> 'testuser', :password =\> 'private', :classes =\> [ClamavScanResult]) # scans is just a array with ClamavScanResult instances scans = ClamavScanResult.all ?\> \<? scans.each do |scan| ?\> \<? end ?\>

| File | Status |
| --- | --- |
| \<?= scan.path ?\> | \<?= scan.result ?\> |

**END**

Well, or something along those lines.

Note that this is probably _not_ compatible with rails.

I can elaborate on I myself would do the display part if you want  
me to.

Hope that helps.

---

<div class="post-metadata">

**Author:** ![jawosis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f04885/32.png) [@jawosis](https://www.ruby-forum.com/u/jawosis)\
**Post date:** [December 13, 2006, 12:36pm UTC](https://www.ruby-forum.com/t/connect-to-mysql-database/79138/5 "2006-12-13T12:36:08Z")

</div>

Hi,

> > My idea also includes, that you have _nothing_ do with the ‘database’,  
> > which is handles by Og (silently). To use a database is merely a way  
> > to structure data, which Og does quite nicely.

> I feel a great disturbance in the Force, as if millions of DBAs voices  
> cried out in terror and then were silenced.

I wonder who silenced them. 😉

> But in the context, I’ll admit it’s a Good Enough ™ first approach.  
> SQL is one of the few programming languages I’m likely to routinely use  
> as part of my job that’s actually interesting, so I’m biased in favour  
> of overrating databases 😉

Well… I’m not sure if a database is the right way to ‘log’ virus  
occurances, but I try to be helpful. 😛  
But seeing as this data is used to present (as a table) to someone, this  
actually has it’s use. Not that I would want looking at 1000 entries of  
‘no virus’ found… But that’s another story. 😛

As for databases… well, I didn’t want to hurt any feelings. ;D  
I feel similar about SQL, but if it’s a simple task like the one in the  
original context… A simple task calls for a simple answer imho. 😛

Have a nice day, everyone 🙂

---

<div class="post-metadata">

**Author:** ![jawosis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f04885/32.png) [@jawosis](https://www.ruby-forum.com/u/jawosis)\
**Post date:** [December 12, 2006, 9:54pm UTC](https://www.ruby-forum.com/t/connect-to-mysql-database/79138/6 "2006-12-12T21:54:42Z")

</div>

Kashia B. wrote:

> My idea also includes, that you have _nothing_ do with the ‘database’,  
> which is handles by Og (silently). To use a database is merely a way  
> to structure data, which Og does quite nicely.

I feel a great disturbance in the Force, as if millions of DBAs voices  
cried out in terror and then were silenced.

But in the context, I’ll admit it’s a Good Enough ™ first approach.  
SQL is one of the few programming languages I’m likely to routinely use  
as part of my job that’s actually interesting, so I’m biased in favour  
of overrating databases 😉

David V.

---

<div class="post-metadata">

**Author:** ![jawosis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f04885/32.png) [@jawosis](https://www.ruby-forum.com/u/jawosis)\
**Post date:** [December 13, 2006, 12:50pm UTC](https://www.ruby-forum.com/t/connect-to-mysql-database/79138/7 "2006-12-13T12:50:09Z")

</div>

> Well… I’m not sure if a database is the right way to ‘log’ virus  
> occurances, but I try to be helpful. 😛

My customer is running a ftp-server with about 6.000 accounts. we  
need to log all activities concerning the ftp-server. uploads,  
downloads, virus occurences etc… there are about 10.000 new  
entries in the database each day.

they will be used for statistics.

each day my customer will get an email with some points:

Total logins/unique users  
Total uploads  
MB transfered  
found viruses

…

So I think it’s useful to use a database to store alle the  
information and run the statistic-queries.

My knowledga about databases is very poor.

Any other suggestions?

I was not sure how to handle this with ruby/ror because I’am (I  
was!!!) a php man and it’s the first project I want to do with ruby.  
These are my first lines od ruby code - and I want to make it good.

Thanx

---

<div class="post-metadata">

**Author:** ![jawosis](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/f04885/32.png) [@jawosis](https://www.ruby-forum.com/u/jawosis)\
**Post date:** [December 13, 2006, 5:14pm UTC](https://www.ruby-forum.com/t/connect-to-mysql-database/79138/8 "2006-12-13T17:14:47Z")

</div>

Hi,

> Total uploads  
> MB transfered  
> found viruses
> 
> …
> 
> So I think it’s useful to use a database to store alle the information  
> and run the statistic-queries.

Yes, I see your application better now.

> My knowledge about databases is very poor.
> 
> Any other suggestions?

Yes, I surely hope that you get some other opinions than mine!

> I was not sure how to handle this with ruby/ror because I’am (I was!!!)  
> a php man and it’s the first project I want to do with ruby.  
> These are my first lines od ruby code - and I want to make it good.

So, seeing you’re a php coder, and not used to ruby, I will try again.

You first have to chose between 2 options, do you want a real framwework  
like Ruby on Rails, Nitro, IOWA or something similar, or stay with the  
approach from php (erb/eruby/erubis).

My approach would best work within a framework (Og is in fact part of  
Nitro).

I researched a bit and coded a little. As it’s just scrap code and  
it’s a bit longish, I put it into a paste service. Feel free to copy  
small parts here if something is unclear.

## get data from pure-uploadscript

[http://pastie.caboo.se/27389](http://pastie.caboo.se/27389)

When called like `UploadedFile.parse_pure_uploadscript` it returns a  
UploadedFile instance with your data.

## setting up the database

[http://pastie.caboo.se/27391](http://pastie.caboo.se/27391)

This configures Og and creates a table to save your data. It can be  
extended by adding more `propery :symbol, Type` entries.

## setting up the clamav ‘parser’

[http://pastie.caboo.se/27393](http://pastie.caboo.se/27393)

I extended the original a bit, allowing for a local clamavscan.

## Putting the parts together

[http://pastie.caboo.se/27394](http://pastie.caboo.se/27394)

This, when run, expects the filename as the first argument, uses  
the uploaded\_file ‘parser’ to get all the data from the environment,  
feeds that to the clamav scanner and saves the result by using Og.

Must be a bit confusing for a newcomer, it’s not all ‘newbie’ code,  
but I hope that helps you a little to see how this _could_ be done.

Anyway, maybe somebody else can jump in and propose another solution.  
(What I didn’t get into here, is on how to present the data. Sorry.  
Tell me which road you want to go (framework or plain erb/eruby),  
and I’ll try to write something up for that.)

I apologize to everyone who sees this message in the future when the  
pastie links are all dead… just send me an email and I’ll try to  
dig it up again.

Enjoy