DBI/ODBC question: how to create DB programmatically?

Discussion in 'Ruby' started by Dave Halliday, Sep 11, 2003.

  1. 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
     
    Dave Halliday, Sep 11, 2003
    #1
    1. Advertising

  2. 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
     
    Michael Neumann, Sep 11, 2003
    #2
    1. Advertising

  3. Dave Halliday

    Chris Morris Guest

    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
    http://clabs.org/blogki
     
    Chris Morris, Sep 11, 2003
    #3
  4. 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" <> wrote in message
    news:...
    > 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
    > http://clabs.org/blogki
    >
    >
    >
     
    Dave Halliday, Sep 12, 2003
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Michael

    DBI ODBC

    Michael, Jan 6, 2004, in forum: Perl
    Replies:
    1
    Views:
    831
    Michael
    Jan 6, 2004
  2. Oliver Osterholz

    DBI:ODBC-Question

    Oliver Osterholz, Nov 18, 2003, in forum: Ruby
    Replies:
    0
    Views:
    102
    Oliver Osterholz
    Nov 18, 2003
  3. Oliver Osterholz

    DBI:ODBC-Question

    Oliver Osterholz, Nov 20, 2003, in forum: Ruby
    Replies:
    0
    Views:
    113
    Oliver Osterholz
    Nov 20, 2003
  4. Oliver Osterholz

    DBI:ODBC-Question

    Oliver Osterholz, Nov 20, 2003, in forum: Ruby
    Replies:
    0
    Views:
    113
    Oliver Osterholz
    Nov 20, 2003
  5. Wes Gamble
    Replies:
    1
    Views:
    181
    Gerardo Santana Gómez Garrido
    Apr 5, 2006
Loading...

Share This Page