none
Criar ODBC Oracle via VBS RRS feed

  • Pergunta

  • Olá, Pessoal ...

    É possível criar uma conexão ODBC Oracle 9i através de script VBS ?? Se possível gostaria de receber um modelo ... ja uso script´s VBS para outros fins, porém não tenho conhecimento em desenvolve-los.

    []´s

    segunda-feira, 25 de setembro de 2006 19:00

Respostas

  • Cara, nunca fiz, mas abaixo está um exemplo de como criar um para SQL.

    Retirei as informações do "The best of Hey, Scripting Guy!". Talvez não seja exatamente o que você quer, mas acho que da uma "clareada"

     

    Can I Create and Delete a DSN Using a Script?

     

    Hey, Scripting Guy! Is it possible to create an ODBC Data Sources Name (DSN) on the fly and then remove it when done?

    -- KC

      

    Hey, KC. Is it possible to create and then delete an ODBC DSN? To tell you the truth, we weren’t entirely sure. As it turns out, however the answer is this: as long as you’re willing to write a script that modifies the registry, then yes, you can programmatically create and delete DSNs.

     

    Note. If you aren’t sure what a DSN is, it’s a way for you to connect to a database merely by referencing the DSN name, and without having to specify the entire path to the database. That’s particularly handy if you have a bunch of scripts that access this database. If you hardcode the path into the scripts and then move the database from one server to another, you’ll have to edit the path on each and every script. If you use a DSN, however, you won’t have to edit the scripts at all; instead, you just need to modify the DSN. That’s one change versus hundreds of changes (depending on how many scripts you have).

     

    What we’re about to show you is a script that creates a DSN that uses SQL Server. Is that important? Yes. You can create DSNs that use other ODBC databases (Access, Oracle, FoxPro, whatever). However, the registry keys and values used when creating a DSN to access an Oracle database aren’t necessarily the same as those used to access a SQL Server database. If you’re using SQL Server, you can just copy our script and change the names of the database, the server, etc. If you’re using a different database, we’d recommend that you manually create a DSN. After you do that, fire up your favorite registry editor and look for the DSN here:

     

    HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

     

    And here:

     

    HKLM\SOFTWARE\ODBC\ODBC.INI\Name You Gave the DSN

     

    Take a look at the registry keys and values required for that type of database, and then plug that information into our script. If you aren’t sure how to do that, give us a week or two, and we’ll see what we can do about getting some sample scripts for different ODBC databases posted in the Script Center.

    At any rate, here’s a script that creates a DSN that uses SQL Server:

     

    Const HKEY_LOCAL_MACHINE = &H80000002

    strComputer = "."
     
    Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
        strComputer & "\root\default:StdRegProv")
     
    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
    strValueName = "Script Repository"
    strValue = "SQL Server"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
     
    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\Script Repository"

    objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath

    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\Script Repository"

    strValueName = "Database"
    strValue = "Script Center"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
     
    strValueName = "Driver"
    strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

    strValueName = "Server"
    strValue = "atl-sql-01"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

    strValueName = "Trusted_Connection"
    strValue = "Yes"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

     

    Yes, we know, it’s a bit long, but that’s because there are a number of registry keys and values that have to be created. For the most part, though, the script is pretty straightforward. We start by setting the constant HKEY_LOCAL_MACHINE to &H80000002, the value required to connect to the HKLM portion of the registry. We then bind to the WMI service, and to the StdRegProv class (which – as we always remind you – happens to be in the root\default namespace).

     

    From there we create a new registry value (HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\Script Repository), with Script Repository the name we’re giving to our new DSN.We set the value of this, well, value to SQL Server, because that’s the type of database we’re connecting to.

     

    Got that? After that we create a new registry key: HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\Script Repository (note that the key name is the same as our DSN Name). We then need to configure the following registry values:

     

    ·         Database -- Name of the actual database we want to connect to. In this example, the database is named ScriptCenter.

    ·         Driver -- Path to SQL Server ODBC drive. In this example, that path is C:\WINDOWS\System32\SQLSRV32.dll.

    ·         Server -- Name of the server where the database is found. In this case, the server name is atl-sql-01.

    ·         Trusted_Connection -- Tells SQL Server to use our logon credentials when we  access the database. This enables us to connect to the database without having to supply a user name and password.

     

    That’s it; run the script, and you’ll have yourself a fully-functioning DSN.

     

    And what about getting rid of that DSN once you’re finished with it? Well, that’s equally easy: after all, we just need to delete the registry keys and values our first script created. Here’s a script that will delete the DSN Script Repository :

     

    Const HKEY_LOCAL_MACHINE = &H80000002

    strComputer = "."
     
    Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
        strComputer & "\root\default:StdRegProv")
     
    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\Script Repository"
    objReg.DeleteKey HKEY_LOCAL_MACHINE, strKeyPath

    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
    strValueName = "Script Repository"
    objReg.DeleteValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName

     

    Note that what this script does is delete the first registry value we created (Script Repository), and then deletes the registry key we created. We don’t have to delete the individual registry values within that key, because deleting HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\Script Repository will delete all the values as well.

    segunda-feira, 25 de setembro de 2006 19:34

Todas as Respostas

  • Cara, nunca fiz, mas abaixo está um exemplo de como criar um para SQL.

    Retirei as informações do "The best of Hey, Scripting Guy!". Talvez não seja exatamente o que você quer, mas acho que da uma "clareada"

     

    Can I Create and Delete a DSN Using a Script?

     

    Hey, Scripting Guy! Is it possible to create an ODBC Data Sources Name (DSN) on the fly and then remove it when done?

    -- KC

      

    Hey, KC. Is it possible to create and then delete an ODBC DSN? To tell you the truth, we weren’t entirely sure. As it turns out, however the answer is this: as long as you’re willing to write a script that modifies the registry, then yes, you can programmatically create and delete DSNs.

     

    Note. If you aren’t sure what a DSN is, it’s a way for you to connect to a database merely by referencing the DSN name, and without having to specify the entire path to the database. That’s particularly handy if you have a bunch of scripts that access this database. If you hardcode the path into the scripts and then move the database from one server to another, you’ll have to edit the path on each and every script. If you use a DSN, however, you won’t have to edit the scripts at all; instead, you just need to modify the DSN. That’s one change versus hundreds of changes (depending on how many scripts you have).

     

    What we’re about to show you is a script that creates a DSN that uses SQL Server. Is that important? Yes. You can create DSNs that use other ODBC databases (Access, Oracle, FoxPro, whatever). However, the registry keys and values used when creating a DSN to access an Oracle database aren’t necessarily the same as those used to access a SQL Server database. If you’re using SQL Server, you can just copy our script and change the names of the database, the server, etc. If you’re using a different database, we’d recommend that you manually create a DSN. After you do that, fire up your favorite registry editor and look for the DSN here:

     

    HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources

     

    And here:

     

    HKLM\SOFTWARE\ODBC\ODBC.INI\Name You Gave the DSN

     

    Take a look at the registry keys and values required for that type of database, and then plug that information into our script. If you aren’t sure how to do that, give us a week or two, and we’ll see what we can do about getting some sample scripts for different ODBC databases posted in the Script Center.

    At any rate, here’s a script that creates a DSN that uses SQL Server:

     

    Const HKEY_LOCAL_MACHINE = &H80000002

    strComputer = "."
     
    Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
        strComputer & "\root\default:StdRegProv")
     
    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
    strValueName = "Script Repository"
    strValue = "SQL Server"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
     
    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\Script Repository"

    objReg.CreateKey HKEY_LOCAL_MACHINE,strKeyPath

    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\Script Repository"

    strValueName = "Database"
    strValue = "Script Center"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue
     
    strValueName = "Driver"
    strValue = "C:\WINDOWS\System32\SQLSRV32.dll"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

    strValueName = "Server"
    strValue = "atl-sql-01"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

    strValueName = "Trusted_Connection"
    strValue = "Yes"
    objReg.SetStringValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName,strValue

     

    Yes, we know, it’s a bit long, but that’s because there are a number of registry keys and values that have to be created. For the most part, though, the script is pretty straightforward. We start by setting the constant HKEY_LOCAL_MACHINE to &H80000002, the value required to connect to the HKLM portion of the registry. We then bind to the WMI service, and to the StdRegProv class (which – as we always remind you – happens to be in the root\default namespace).

     

    From there we create a new registry value (HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\Script Repository), with Script Repository the name we’re giving to our new DSN.We set the value of this, well, value to SQL Server, because that’s the type of database we’re connecting to.

     

    Got that? After that we create a new registry key: HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\Script Repository (note that the key name is the same as our DSN Name). We then need to configure the following registry values:

     

    ·         Database -- Name of the actual database we want to connect to. In this example, the database is named ScriptCenter.

    ·         Driver -- Path to SQL Server ODBC drive. In this example, that path is C:\WINDOWS\System32\SQLSRV32.dll.

    ·         Server -- Name of the server where the database is found. In this case, the server name is atl-sql-01.

    ·         Trusted_Connection -- Tells SQL Server to use our logon credentials when we  access the database. This enables us to connect to the database without having to supply a user name and password.

     

    That’s it; run the script, and you’ll have yourself a fully-functioning DSN.

     

    And what about getting rid of that DSN once you’re finished with it? Well, that’s equally easy: after all, we just need to delete the registry keys and values our first script created. Here’s a script that will delete the DSN Script Repository :

     

    Const HKEY_LOCAL_MACHINE = &H80000002

    strComputer = "."
     
    Set objReg=GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & _
        strComputer & "\root\default:StdRegProv")
     
    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\Script Repository"
    objReg.DeleteKey HKEY_LOCAL_MACHINE, strKeyPath

    strKeyPath = "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"
    strValueName = "Script Repository"
    objReg.DeleteValue HKEY_LOCAL_MACHINE,strKeyPath,strValueName

     

    Note that what this script does is delete the first registry value we created (Script Repository), and then deletes the registry key we created. We don’t have to delete the individual registry values within that key, because deleting HKLM\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources\Script Repository will delete all the values as well.

    segunda-feira, 25 de setembro de 2006 19:34
  • Obrigado, Bruno !

    Vou analisar a possibilidade de usar este modelo com o Oracle9i.

    []´s

    terça-feira, 26 de setembro de 2006 20:28