Hi

I've written an MS Access VBA module to open and parse a tnsnames.ora file. The operation works, but I need to tweak my regular expression to handle an unanticipated exception.

I have 2 questions

Question 1

Is there a 'single' regular expression that I can use to extract all the components of all tnsnames entries.

Example:

DB11T.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = risc13)(PORT = 1523))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = DB11T)
    )
  )

DB2T.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (COMMUNITY = tcpcom.world)(PROTOCOL = TCP)(Host = risc13)(Port = 1527))
    )
    (CONNECT_DATA =
      (SID = DB2T)
    )
  )

DB3P.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = grid.info.ca)(Port = 1521))
    )
    (CONNECT_DATA =
      (SID = DB3P)
    )
  )
 
DB4U.WORLD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db2.info.ca)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB4U.WORLD)
    )
  )

Return:
DB,COMMUNITY,PROTOCOL,HOST,PORT,SERVER,SERVICE_NAME,SID
(DB11T,null,TCP,risc13,1523,null,DB11T,null)
(DB2T,tcpcom.world,TCP,risc13,1527,null,null,DB2T)
(DB3P,null,TCP,grid.info.ca,1521,null,null,DB3P)
(DB4U,null,TCP,db2.info.ca,1521,DEDICATED,DB4U.WORLD,null)

I should mention that I read the tnsnames file in and scan it line by line.

Question 2

My second question regards how to parse a single line with two alternatives.

(ADDRESS = (PROTOCOL = TCP)(HOST = risc13)(PORT = 1523))
(ADDRESS = (COMMUNITY = tcpcom.world)(PROTOCOL = TCP)(Host = risc13)(Port = 1527))

Is there a 'single' regular expression that would account for the 'optional' COMMUNITY =? I can query one form or the other, but I need a single expression

\s*\(ADDRESS\s*=\s*\(COMMUNITY\s*=\s*(\w+)\)\(PROTOCOL\s*=\s*(\w+)\)\(HOST\s*=\s*(\S+)\)\(PORT\s*=\s*(\d+)\)
or
\s*\(ADDRESS\s*=\s*\\(PROTOCOL\s*=\s*(\w+)\)\(HOST\s*=\s*(\S+)\)\(PORT\s*=\s*(\d+)\)

Return
COMMUNITY,PROTOCOL,HOST,PORT
(null,TCP,risc13,1523)
(tcpcom.world,TCP,risc13,1527)

Thanks and take care,
Shayne

You mention using Acess: can we assume that the regex engine you are using is VBScript V5.5?

For question #2, if the items can appear in any order then a pattern such as:

\(address\s*=\s*(\((protocol\s=\s*(\w+)|host\s*=\s*(\w+)|port\s*=\s*(\d+)|community\s*=\s*([\w.]*))\))*\)

with the "ignore case" option set, will find the last occurrence of each protocol or host or whatever. The corresponding value will be is the appropriate match group (the protocol value will be in match group #3, the host value in #4 etc).

If the order is always the same and only the "community" item and value are optional, then make your first pattern something like:

\s*\(ADDRESS\s*=\s*(\(COMMUNITY\s*=\s*(\w+)\))?\(PROTOCOL\s*=\s*(\w+)\)\(HOST\s*=\s*(\S+)\)\(PORT\s*=\s*(\d+)\)

Susan