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.
RE: 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.
$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.