DBI/ODBC question: how to create DB programmatically?

[Dave Halliday]

Hi,

I've been trying to figure out how to use ODBC with DBI. So far, I have
created a DSN for an existing DB and managed to read tables, update them,
etc, but I'd like to be able to create a new DB from scratch
programmatically (i.e. without any manual steps to create a DSN and add a
database).

Is there a way to do this from the DBI interface? I've found a few VB
examples and dug around in the ODBC source code, but I can't quite make the
leap from the VB code to the DBI API.

I'm using Windows XP and Ruby 1.8.0.

Thanks,

Dave Halliday

[Michael Neumann]

On Thu, Sep 11, 2003 at 11:51:08AM +0900, Dave Halliday wrote:

> Hi,
>
> I've been trying to figure out how to use ODBC with DBI. So far, I have
> created a DSN for an existing DB and managed to read tables, update them,
> etc, but I'd like to be able to create a new DB from scratch
> programmatically (i.e. without any manual steps to create a DSN and add a
> database).

There's no special command to create databases with DBI/ODBC. Usually,
you can create a database by issuing the "CREATE DATABASE ..." SQL
statement, but this will probably not create a DSN for you.

> Is there a way to do this from the DBI interface? I've found a few VB
> examples and dug around in the ODBC source code, but I can't quite make the
> leap from the VB code to the DBI API.

Everything what you can do in VB should be possible in Ruby by using the
win32ole module (this is how the ADO database driver is implemented).

> I'm using Windows XP and Ruby 1.8.0.

Have a look at the ADO database driver (part of DBI in directory
lib/dbd_ado). Look how it's using the ADODB.Connection COM object, and
try to translate the VB example into Ruby.

Hope this helps.


Regards,

Michael

[Chris Morris]

Dave Halliday wrote:

>Hi,
>
>I've been trying to figure out how to use ODBC with DBI. So far, I have
>created a DSN for an existing DB and managed to read tables, update them,
>etc, but I'd like to be able to create a new DB from scratch
>programmatically (i.e. without any manual steps to create a DSN and add a
>database).
>

Access:

cat = WIN32OLE.new('ADOX.Catalog')
cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=#{@out_mdb_fn}"

SQL Server:

db = WIN32OLE.new("ADODB.Connection")
db.Open "Provider=SQLOLEDB;Data Source=myserver;Database=master;..."
db.execute("CREATE DATABASE mydb")

--

Chris
[url]http://clabs.org/blogki[/url]

[Dave Halliday]

Thanks Chris. That's what I was looking for. I never thought of using
win32ole.

In the meantime, I did manage to create an Access database using the
undocumented add_dsn() method in the ODBC driver:

drv = ODBC::Driver.new
drv.name = "Microsoft Access Driver (*.mdb)"
drv.attrs = {"CREATE_DB" => ".\\test.mdb General"}
ODBC.add_dsn(drv)

Cheers,

Dave

"Chris Morris" <chrismo@clabs.org> wrote in message
news:3F60CFF0.8000805@clabs.org...

> Dave Halliday wrote:
>

> >Hi,
> >
> >I've been trying to figure out how to use ODBC with DBI. So far, I have
> >created a DSN for an existing DB and managed to read tables, update them,
> >etc, but I'd like to be able to create a new DB from scratch
> >programmatically (i.e. without any manual steps to create a DSN and add a
> >database).
> >

> Access:
>
> cat = WIN32OLE.new('ADOX.Catalog')
> cat.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=#{@out_mdb_fn}"
>
> SQL Server:
>
> db = WIN32OLE.new("ADODB.Connection")
> db.Open "Provider=SQLOLEDB;Data Source=myserver;Database=master;..."
> db.execute("CREATE DATABASE mydb")
>
> --
>
> Chris
> [url]http://clabs.org/blogki[/url]
>
>
>