November 14, 2009: On DAO, ODBC and Oracle

Here are a few notes on DAO and ODBC with Oracle.

I have conducted the experiment on a freshly installed Windows XP Machine. In order to run the VBA code below, I also installed Excel in order to have a VBA environment where I can dim variables; that is, I could also have used Visual Basic Script, but its disadvantage is that I have to create «objects» with CreateObject instead of set obj_var = new obj_type. Also, Excel's VBA environment is aware of the methods and constants (aka intellisense) for dao and the like which was a bit beneficial to me since I usually don't do any DAO.

So, first I downloaded Oracle's Instant Client for Microsoft Windows (32-bit) from Oracle Technology Network Instant Client page. Actually, to be more precise, I downloaded the following two zip files: instantclient-basic-win32-11.1.0.7.0.zip and instantclient-odbc-win32-11.1.0.7.0.zip. The former is needed for anything related to the Instant Client (as it contains for example oci.dll) while the latter has the ODBC related stuff.

Both zip files were extracted into C:\oracle\instantclient_11_1.

After the extraction, I installed ODBC for Oracle:

C:\>cd /d c:\oracle\instantclient_11_1

C:\oracle\instantclient_11_1>odbc_install.exe
Oracle ODBC Driver is installed successfully.

In order to be able to connect to an Oracle Instance, I needed a net service name which is configured in the tnsnames.ora file. Here, I chose ora11 as my net service name:

c:\oracle\instantclient_11_1>notepad c:\oracle\tnsnames.ora

tnsnames.ora

ora11 = (
  DESCRIPTION = (
    ADDRESS_LIST = (
      ADDRESS = (PROTOCOL=TCP)
                (HOST=xxx.xx.x.xx)
                (PORT=1521)
      )
   )(
      CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=newora11)
  )
)

Setting the required environment variables

In order to make the ODBC installation complete, I need to add the TNS_ADMIN environment variable and to point the PATH variable to the directory where the oci.dll is located. This is done by the following VBA script:

add_env_vars.bas

' See http://support.microsoft.com/kb/q184608/
' ---
' Must be run with administrator privileges on Vista
'

option explicit

private const REG_SZ             =          1
private const REG_EXPAND_SZ      =          2
private const HKEY_LOCAL_MACHINE = &H80000002

' Needed for SendMessageTimeout
private const HWND_BROADCAST   = &HFFFF
private const WM_SETTINGCHANGE = &H1A
private const SMTO_ABORTIFHUNG = &H2

private declare function RegCreateKey Lib "advapi32.dll" Alias _
     "RegCreateKeyA"                                           _
    (byVal hKey       as long                                , _
     byVal lpSubKey   as string                              , _
           phkResult  as long                                  _
) as long

private declare function RegSetValueEx Lib "advapi32.dll" Alias _
   "RegSetValueExA"                                             _
  (byVal hKey         as long                                 , _ 
   byVal lpValueName  as string                               , _
   byVal Reserved     as long                                 , _
   byVal dwType       as long                                 , _
         lpData       as Any                                  , _
   byVal cbData       as long                                   _
)  as long

private declare function RegQueryValueEx Lib "advapi32.dll" alias _
  "RegQueryValueExA"                                                _
 (byVal  hKey          as   long , _
  ByVal  lpszValueName as string, _
                   ByVal lpdwRes as long, _
                   lpdwType as long, _
                   ByVal lpDataBuff as string, _
                   nSize as long) as long

private declare function RegCloseKey Lib "advapi32.dll"         _
       (byVal hKey as long                                      _
)  as long


private declare function SendMessageTimeout lib "User32.dll" alias _
   "SendMessageTimeoutA"                                           _
  (byVal hWnd         as long                                 ,    _
   byVal msg          as long                                 ,    _
         wParam       as any                                  ,    _
   byVal lParam       as string                               ,    _
   byVal fuFlags      as long                                 ,    _
   byVal uTimeout     as long                                 ,    _
         lpdwResult   as long                                      _
) as long


public sub add_env_vars()

   dim TNS_Admin_path as string
   dim OCI_DLL_path   as string

   dim lResult        as long
   dim hKeyHandle     as long

   TNS_Admin_path = "c:\Oracle"
   OCI_DLL_path   = "c:\oracle\instantclient_11_1"


   lResult = RegCreateKey(                                                   _
             HKEY_LOCAL_MACHINE                                            , _
            "System\CurrentControlSet\Control\Session Manager\Environment" , _
             hKeyHandle                                                      _
   )


   lResult = RegSetValueEx(      _
             hKeyHandle        , _ 
            "TNS_ADMIN"        , _
             0&                , _
             REG_SZ            , _
       byVal TNS_Admin_path    , _ 
         Len(TNS_Admin_path)     _
   )


   ' get current value for %PATH%

   ' First, length of current %PATH%

   dim lenPath as long

   lResult = RegQueryValueEx(        _
             hKeyHandle       , _
            "PATH"            , _
             0                , _
             REG_EXPAND_SZ    , _
             0                , _
             lenPath )
             
    dim path as string
        path = string(lenPath, chr(0))

   ' then: actual value of PATH
   lResult = RegQueryValueEx(   _
             hKeyHandle       , _
            "PATH"            , _
             0                , _
             REG_EXPAND_SZ    , _
             path             , _
             lenPath )

   ' chop trailing chr(0) from path
   path = left$(path, lenPath - 1)

   ' append path to oci.dll
   path = path & ";" & OCI_DLL_path

   lResult = RegSetValueEx(     _
             hKeyHandle       , _ 
            "PATH"            , _
             0&               , _
             REG_EXPAND_SZ    , _
       byVal path             , _ 
         Len(path)              _
   )

   lResult = RegCloseKey(hKeyHandle)

       dim dwReturnValue as long

   ' Propagate changes in environment variables
   ' http://support.microsoft.com/kb/104011
   lResult = SendMessageTimeout(     _
               HWND_BROADCAST    ,   _
               WM_SETTINGCHANGE  ,   _
               0                 ,   _
              "Environment"      ,   _
               SMTO_ABORTIFHUNG  ,   _
               5000              ,   _
               dwReturnValue         _
    )

    
end sub

In order to execute this script in Excels VBA environment, I created another (visual basic script) that basically opens excel and loads my desired script and executes it:

runVBAFilesInExcel.vbs

' In order to prevent a "Programmatic access to Visual Basic Project is not trusted":
'   Tools -> Macro -> Security... -> Trusted Publishers -> check "Trust access to Visual Basic Project"
'
' Or
'   /path/to/excel.exe /unregserver
'   /path/to/excel.exe /regserver

option explicit

dim excel
dim workbook
dim vb_editor
dim shell_obj
dim cur_dir
dim args
dim function_name_to_call
dim vbs_name_to_import

Set args = WScript.Arguments

if args.count = 0 then
  WScript.echo ("no argument specified")
  WScript.Quit
end if

function_name_to_call = args(0)
vbs_name_to_import    = args(0) & ".bas"

set shell_obj = CreateObject("WScript.Shell")

cur_dir = shell_obj.currentDirectory

set excel = CreateObject("Excel.Application")
excel.visible = true
set workbook = excel.Workbooks.Add

set vb_editor = excel.vbe
dim vb_comps
set vb_comps = vb_editor.ActiveVBProject.VBComponents

vb_comps.Import(cur_dir & "\" & vbs_name_to_import)

' Type Libs
' --------
'   Find them in the registry under HKEY_CLASSES_ROOT\TypeLib...
'
' Microsoft Access 11.0 Object Library
'call addReference(workbook, "{4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}")
' Microsoft DAO 3.6 Object Library 
call addReference(workbook, "{00025E01-0000-0000-C000-000000000046}")
' Microsoft Scripting Runtime
call addReference(workbook, "{420B2830-E718-11CF-893D-00A0C9054228}")

excel.Run(function_name_to_call)
set excel = nothing


sub addReference(workbook, guid)

  on error resume next

  dim refs
  set refs = workbook.VBProject.References
  refs.AddFromGuid guid, 0, 0

  if err.number <> 0 then

     if err.number <> 32813 then
          msgBox "Error: " & err.number & vbNewLine & err.description
     end if

  end if
end sub

This second script (runVBAFilesInExcel.vbs) takes one parameter which is the name of the function to call and the name of the file in which the function is found without the .bas suffix. Thus, I can execute add_env_vars with a:

c:\oracle\instantclient_11_1>runVBAFilesInExcel.vbs add_env_vars

Setting up the ODBC Datasource

Now, I am ready to set up my ODBC datasource. Again, I do that with a VBA script:

create_odbc_datasource.bas

' See http://support.microsoft.com/kb/q184608/
' ---
' Must be run with administrator privileges on Vista
'

option explicit

private const REG_SZ             =          1
private const HKEY_LOCAL_MACHINE = &H80000002

private Declare Function RegCreateKey Lib "advapi32.dll" Alias _
     "RegCreateKeyA"                                           _
    (byVal hKey       As long                                , _
     byVal lpSubKey   As string                              , _
           phkResult  As long                                  _
) as long

private Declare Function RegSetValueEx Lib "advapi32.dll" Alias _
   "RegSetValueExA"                                             _
  (byVal hKey         as long                                 , _ 
   byVal lpValueName  As string                               , _
   byVal Reserved     As long                                 , _
   byVal dwType       As long                                 , _
         lpData       As any                                  , _
   byVal cbData       As long                                   _
)  as long

private Declare Function RegCloseKey Lib "advapi32.dll"         _
       (byVal hKey As long                                      _
)  as long


public sub create_odbc_datasource()
     
   dim ODBCName       as string
   dim description    as string
   dim driverPath     as string
   dim driverName     as string
   dim lastUser       as string
   dim TNSName        as string

   dim lResult        as long
   dim hKeyHandle     as long


   ODBCName       = "DAOTest"
   TNSName        = "ora11"
   Description    = "Dynamically created datasource for Oracle"
   DriverPath     = "c:\oracle\instantclient_11_1\sqora32.dll"
   user_id        = "RENE"
   DriverName     = "Oracle in instantclient11_1" ' See HKLM\SOFTWARE\ODBC\ODBCINST.INI

   'Create the new DSN key.

   lResult = RegCreateKey(                                _
             HKEY_LOCAL_MACHINE                         , _
            "SOFTWARE\ODBC\ODBC.INI\"  & ODBCName       , _
             hKeyHandle                                   _
   )

   lResult = RegSetValueEx(                               _
             hKeyHandle                                 , _ 
            "ServerName"                                , _
             0&                                         , _
             REG_SZ                                     , _
       byVal TNSName                                    , _ 
         Len(TNSName)                                     _
   )

   lResult = RegSetValueEx(                               _
             hKeyHandle                                 , _
            "Description"                               , _
             0&                                         , _
             REG_SZ                                     , _
       byVal Description                                , _
         Len(Description)                                 _
   )

   lResult = RegSetValueEx(                               _
             hKeyHandle                                 , _
            "Driver"                                    , _
             0&                                         , _
             REG_SZ                                     , _
       byVal DriverPath                                 , _
         Len(DriverPath)                                  _
   )

   lResult = RegSetValueEx(                               _
             hKeyHandle                                 , _ 
            "UserID"                                    , _
             0&                                         , _
             REG_SZ                                     , _
       byVal user_id                                    , _
         Len(user_id)                                     _
   )

'  lResult = RegSetValueEx(                               _
'            hKeyHandle                                 , _ 
'           "Server"                                    , _
'            0&                                         , _
'            REG_SZ                                     , _
'      byVal Server                                     , _
'        Len(Server)                                      _
'  )

   lResult = RegCloseKey(hKeyHandle)

   lResult = RegCreateKey(                                _ 
            HKEY_LOCAL_MACHINE                          , _
           "SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources"   , _
            hKeyHandle                                    _
   )

   lResult = RegSetValueEx(                               _
             hKeyHandle                                 , _
             ODBCName                                   , _
             0&                                         , _
             REG_SZ                                     , _
       byVal DriverName                                 , _
         Len(DriverName)                                  _
   )

   lResult = RegCloseKey(hKeyHandle)
    
end sub

I can verify the success by opening the «ODBC control panel», and going to System DSN:

C:\oracle\instantclient_11_1>odbccp32.cpl

Running the test

dao_test.bas

option explicit

sub dao_test()

  dim ws            as DAO.workspace
  dim db            as DAO.database

  dim strConnection as string
  dim odbcName      as string
  dim userName      as string
  dim password      as string

  odbcName     = "DAOTest"
  UserName     = "rene"
  password     = "rene"

  ' you need to create a DSN in  Control Panel ODBC

      strConnection =    _
     "ODBC" &            _
    ";DSN=" & odbcName & _
    ";UID=" & UserName & _
    ";PWD=" & password

  set ws=DBEngine.Workspaces(0)
  set db=ws.OpenDatabase("", false, false, strConnection)

  msgBox("Database opened")

  call create_table    (db)
  call add_column      (db)
  call add_primary_key (db)
  call add_index       (db)
  call insert_records  (db)
  call autoincrement_01(db)
  call autoincrement_02(db)
  call transaction     (db)

' remove_table()

end sub

private sub create_table(db as DAO.Database)

  dim table_def   as dao.tableDef
  dim table_field as dao.Field

  set table_def = db.createTableDef("some_table")

  ' First column
  set table_field = table_def.createField("theAutonumberField", dbLong)
      table_field.attributes = dbAutoIncrField
  table_def.Fields.append table_field

  ' Second column
  set table_field = table_def.createField("aDateColumn"       , dbDate)
  table_def.Fields.append table_field

' ' Third column
' set table_field = table_def.createField("aCurrencyColumn"   , dbCurrency)
' table_def.Fields.append table_field
'
' Oracle seems not to support dbCurrency

  ' Fourth column
  set table_field = table_def.createField("aTextColumn"       , dbText, 20)
  table_def.Fields.append table_field
  
  ' 
  db.tableDefs.append table_def

  set table_field = nothing
  set table_def   = nothing

  msgBox ("Table created")

end sub

private sub add_column(db as DAO.Database)

  dim table_def   as dao.tableDef
  dim table_field as dao.Field

  set table_def = db.tableDefs("some_table")

  set table_field = table_def.createField("fifthColumn", dbText, 20)
  table_def.fields.append table_field

  set table_field = nothing
  set table_def   = nothing

  msgBox ("Column added")

end sub


private sub add_primary_key(db as DAO.Database)

  dim table_def   as dao.tableDef
' dim table_field as dao.field
  dim ix          as dao.index

  set table_def = db.tableDefs("some_table")

  set ix        = table_def.createIndex("pk_some_table")
  ix.fields.append ix.createField("theAutonumberField")
  ix.unique     = false ' ???

' can obviously not be set in Oracle
' ix.primary    = true
  ix.primary    = false

  table_def.indexes.append ix

  set ix          = nothing
  set table_def   = nothing

  msgBox("Primary key added")
end sub

private sub add_index(db as DAO.Database)

  dim table_def   as dao.tableDef
  dim ix          as dao.index

  set table_def = db.tableDefs("some_table")

  set ix        = table_def.createIndex("ix_unique")
  ix.fields.append ix.createField("aTextColumn")
  ix.unique     = true
  ix.primary    = false ' default ?

  table_def.indexes.append ix

  set ix          = nothing
  set table_def   = nothing

  msgBox ("Index added")
end sub

private sub insert_records(db as DAO.Database)

  dim rs as DAO.recordSet

' dbOpentable seems not to be supported in Oracle
' set rs = db.openRecordSet("some_table", dbOpentable)
  set rs = db.openRecordSet("some_table")
 
  call insert_a_record (rs, #08/28/1970#, 10.02, "some text", "more text")
  call insert_a_record (rs, #01/01/2001#, 11.11, "one"      , "Apple"    )
  call insert_a_record (rs, #02/02/2002#, 22.22, "two"      , "Pear"     )
  call insert_a_record (rs, #08/28/1970#, 10.02, "some text", "more text") ' Same record as first, won't be inserted.

end sub

private sub insert_a_record (              _
  rs           as dao.recordSet ,  _
  date_col     as date          ,  _
  curr_col     as currency      ,  _
  txt_col      as string        ,  _
  col_5        as string) 

  on error goto insert_fail

    rs.addNew
  
    rs("aDateColumn"    ) = date_col
'   rs("aCurrencyColumn") = curr_col
    rs("aTextColumn"    ) = txt_col
    rs("fifthColumn"    ) = col_5
  
    rs.update

    msgBox ("Record inserted")

    exit sub

  insert_fail:
    dim err_desc as string

    err_desc = "record" &              vbCrLf & _
               "  "     & date_col  &  vbCrLf & _
               "  "     & curr_col  &  vbCrLf & _
               "  "     & txt_col   &  vbCrLf & _
               "could not be inserted" 

    select case err.number
      case 3022
        err_desc = err_desc & vbCrLf & "because a unique index was validated"

      case else
        err_desc = err_desc & vbCrLf & "Error No: "   & err.number & vbCrLf & _
                                       "Error Text: " & err.description
    end select

    msgBox err_desc

end sub


private sub select_record()

  dim sqlText as string

  ' "  aCurrencyColumn, "     & _

  sqlText =                     _
    "select "                 & _
    "  theAutonumberField, "  & _
    "  aDateColumn, "         & _
    "  aTextColumn, "         & _
    "  fifthColumn "          & _
    "from "                   & _
    "  some_table"

' msgBox sqlText

  set rs = db.openRecordSet(sqlText, dbOpenDynaset)
   
  do until rs.eof

'           rs("aCurrencyColumn"   ) & vbCrLf & _
     
     msgBox rs("theAutonumberField") & vbCrLf & _
            rs("aDateColumn"       ) & vbCrLf & _
            rs("aTextColumn"       ) & vbCrLf & _
            rs("fifthColumn"       )
     
     rs.moveNext
  loop

end sub

private sub autoincrement_01(db as DAO.Database)

  ' insert a biiiig autoincrement (autonumber)
  ' into the table

  dim rs as DAO.RecordSet

' set rs = db.openRecordSet("some_table", dbOpentable)
  set rs = db.openRecordSet("some_table")

  rs.addNew
  
    rs("theAutonumberField") = 999999999
    rs("aDateColumn"       ) = #10/31/1517#
'   rs("aCurrencyColumn"   ) = 68.65
    rs("aTextColumn"       ) = "biiiig autonumber"
    rs("fifthColumn"       ) = "Chanel, no 5"
  
  rs.update

  set rs = nothing

end sub


public sub autoincrement_02(db as DAO.Database)

  ' insert a biiiig autoincrement (autonumber)
  ' into the table

  dim rs as dao.recordSet

' set rs = db.openRecordSet("some_table", dbOpentable)
  set rs = db.openRecordSet("some_table")

  rs.addNew
  
'   rs("theAutonumberField") = ??? (Deliberatly omitted)
    rs("aDateColumn"       ) = #11/01/1517#
'   rs("aCurrencyColumn"   ) = 100.01
    rs("aTextColumn"       ) = "autonumber, 2nd part"
    rs("fifthColumn"       ) = "still Chanel, no 5"

    msgBox "The autonumber field to be inserted is: " & rs!theAutonumberField

  rs.update

  set rs = nothing

end sub

private sub transaction(db as DAO.Database)

  call do_transaction(db, false)
  call do_transaction(db, true )

end sub

private sub do_transaction(db as dao.Database, commit as boolean)

  dim wrk    as Dao.Workspace
  set wrk =  DBEngine.Workspaces(0)

  call wrk.beginTrans()
  call select_count(db, "At begin of transaction")

  ' Note the [] instead of the "" in order to prevent a 3450 incomplete query clause.
  db.Execute("delete from [some_table] where [aTextColumn] in ('one', 'two')")
  call select_count(db, "After delete")

  if commit then
    call wrk.commitTrans()
    call select_count(db, "After Commit")
  else
    call wrk.rollback()
    call select_count(db, "After Rollback")
  end if

  set wrk = nothing

end sub

private sub select_count (db as Dao.Database, txt as string)

  dim rs as dao.recordSet

  set rs = db.OpenRecordSet("select count(*) as cnt from some_table")
  msgBox txt & vbCrLf & "Count: " & rs("cnt")

  set rs = nothing

end sub

Running the test, I noticed the following strange behaviour:

After opening the database, it takes several seconds to create the table, and I wonder if this is just my environment.
The field theAutonumberField is neither a primary key, nor not null nor does dao automatically insert any value into it.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.

Warning: require(): open_basedir restriction in effect. File(/var/www/virtual/adp-gmbh.ch/forum/comment.inc) is not within the allowed path(s): (/home/httpd/vhosts/renenyffenegger.ch/:/tmp/) in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2009/11/14.php on line 1019

Warning: require(/var/www/virtual/adp-gmbh.ch/forum/comment.inc): failed to open stream: Operation not permitted in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2009/11/14.php on line 1019

Fatal error: require(): Failed opening required '/var/www/virtual/adp-gmbh.ch/forum/comment.inc' (include_path='.:/home/httpd/vhosts/renenyffenegger.ch') in /home/httpd/vhosts/renenyffenegger.ch/adp-gmbh.ch/blog/2009/11/14.php on line 1019


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger