embedded ESQL CONNECT with password

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.