embedded ESQL CONNECT with password

Can I get an example of embeded ESQL CONNECT command that specifies a password for an password/encrypted XLS?  This would be in native COBOL.

Thanks, Ellen S.

  • I noticed that you had submitted support incident 3164332 for this question so we have responded to you through the incident.

    For the sake of completeness I will repeat the response here.

    There doesn't appear to be an option in the ODBC driver for Excel to specify a password to connect to an encrypted Excel file. We found from web searching that the ODBC driver should be able to connect to a DSN which points to an encrypted file if the program already has the file open using the password at the time the CONNECT is done.

    We wrote an example which opens the encrypted file first using the native COM support for Excel and leaves it open, and then the ODBC driver can connect successfully. The COM closes the file when the ODBC driver disconnects. This is the only way we could get it to work.

    This implies that you will need to have MS Excel installed on the machine in order for the native COM to open the Excel file.

    This demo will open a simple spreadsheet called C:\testxlsencrypt\testBook1.xlsx and encrypted with the password "password". It also requires a 32-bit System DSN to be created and called TESTEXCEL which points to the file.

    Example code:

     

           $set ooctrl( P)
           identification division.
           program-id. testxlsencrypt.
           environment division.
           configuration section.
           class-control.
              MSExcel is class "$OLE$Excel.Application"
    		  oleVariant is class "olevar".
           data division.
           working-storage section.
    	   copy "mfole".
    	   EXEC SQL INCLUDE SQLCA END-EXEC.
    	   EXEC SQL INCLUDE TABLE1 END-EXEC.
    
           01 WS-XLSFILE     pic x(32)
              value "C:\testxlsencrypt\testBook1.xlsx".
    	   01 v              VARIANT.
           01 vOpt           object reference value null.
           01 WS-PASSWORD    pic x(9) value z"password".
           01 ExcelObject    object reference.
           01 WorkBooks      object reference.
           01 Workbook       object reference.
           procedure division.
    		   perform 100-open-protected-xls-with-COM
    		   perform 105-process-with-odbc
    		   perform 110-cleanup-COM
    		   goback.
    
           100-open-protected-xls-with-COM.
    
           *> Create a null variant to pass to OLE as optional parameters
               move low-values to v
               move VT-ERROR to VARIANT-vartype of v
               move DISP-E-PARAMNOTFOUND to VARIANT-VT-SCODE of v
               invoke olevariant "newWithData" using v returning vOpt
               
           *> Create a new instance of Microsoft Excel
               invoke MSExcel "new" returning ExcelObject
           *> Make Excel visible
               invoke ExcelObject "setVisible" using by value 1
           *> Get the collection of WorkBooks
               invoke ExcelObject "getWorkBooks" returning WorkBooks
           *> Open File
               invoke WorkBooks "Open"
                 using by reference  WS-XLSFILE
    		                         vOpt
    		                         vOpt
    		                         vOpt
    		                         WS-PASSWORD
                 returning Workbook
    		   end-invoke.
    
           105-process-with-odbc.
    
               EXEC SQL
                  CONNECT TO 'TESTEXCEL' 
               END-EXEC 
    		   display sqlcode
    		   EXEC SQL 
                   DECLARE usercursor CURSOR FOR SELECT 
                      A.NAME
                     ,A.ADDRESSZ
                     ,A.CITY
                     FROM TABLE1 A
               END-EXEC 
               EXEC SQL 
                  OPEN usercursor
               END-EXEC 
               PERFORM UNTIL SQLCODE < 0 OR SQLCODE =  100 
                  EXEC SQL 
                     FETCH usercursor  INTO 
                        :TABLE1-NAME:TABLE1-NAME-NULL
                       ,:TABLE1-ADDRESSZ:TABLE1-ADDRESSZ-NULL
                       ,:TABLE1-CITY:TABLE1-CITY-NULL
                  END-EXEC 
                  IF SQLCODE = 0 
    			     DISPLAY table1-name 
                  END-IF 
                END-PERFORM 
                EXEC SQL 
                   CLOSE usercursor
                END-EXEC 
    
                exec sql disconnect all end-exec.
    
    	   110-cleanup-COM.
    
               invoke vOpt "finalize" returning vOpt
               invoke Workbook "Close" using by value 0
               invoke WorkBook "finalize" returning WorkBook
    	       invoke WorkBooks "finalize" returning WorkBooks
           *> end excel     
    	       invoke ExcelObject "Quit"
               invoke ExcelObject "finalize" returning ExcelObject.