Fixed misspelling with SETACTIVERECELLFORECOLOR and SETACTIVERECELLBACKCOLOR and changed to SETACTIVECELLBACKCOLOR and SETACTIVECELLBACKCOLOR.
Added SETSELECTEDROWINSERT function to insert blank row in spreadsheet using .Selection.EntireRow.Insert.
Added adDBDate data type to the date handler. Customer was having odd errors with Informix database where they got the following error message Multiple-step OLE DB operation generated errors. Now if we get errors reading a date from an Informix database, we return a date of: 1900-01-01. The problem ultimately started because they had some bad date/time fields in their Informix database table.
Added trace point code to excel command function for tracking errors.
Updated PC component to correctly handle the situation where the trailing @@END statement in the transferred data may be split because of the TCP/IP packet breaking up somewhere in the middle of the @@END statement. This was causing periodic lockups for some customers because the PC component would go in to an endless looping condition.
Added code to automatically kill Excel if it's been launched and the socket connection closes and RPG2SQL still thinks Excel is active. The user will lose the spreadsheet data they were working on, but this should clean up straggler Excel instances that can be left opn on abnormal disconnects from an RPG program without shutting down Excel.
Added output of "199 EOF" for the SQL_GetParm function in RPG service program. Was causing a lockup when retrieving a parameter after a stored procedure call.
- Fixed Excel get delimited record function to always get the correct ending column.
- Added command timeout for mvarADOCMD. Some customers having timeout issues on long running stored procedures. This sets the timeout for the mvarAdoCmd object variable. The RPG function SQL_SetTimeout needs to be run to set any timeout values prior to running any queries after the SQL_Connect operation is run.
- Added trace point info to RunSQLSPExecParmRecordset function. This should show in the debug area which lines are failing with a trace id. We have a customer where this routine fails when running a stored procedure to get a recordset. We're thinking if the timeout value gets set higher this may work. They are getting an Object Error 91 on a single PC.
By default we no longer try to guess and format dates identified by IsDate() into an ISO date field when reading a delimited record from an Excel spreadsheet. We simply bring the text values across. XLS Date formatting can be enabled and disabled with the following RPG function calls. Once enabled or disabled this is set globally in the PC component until the program is started or ENABLE/DISABLE is called again.
RPG Sample to Enable Date Formatting:
*-------------------------------------------------------------------- * ** Enable Excel Date Formatting
*-------------------------------------------------------------------- C Eval Rtn = XLS_Command(SQL_Socket: C 'EXCELDATEFORMATON': C ' ')
*-------------------------------------------------------------------- * ** Disable Excel Date Formatting
*-------------------------------------------------------------------- C Eval Rtn = XLS_Command(SQL_Socket: C 'EXCELDATEFORMATOFF': C ' ')
Note: NO service program or library updates were required on the iSeries.
Recompiled program code and error when running SQTEST29 went away. Not sure why :-)
Fixed problem with ExcelGetDelimRecord that some numbers are being returned as timestamps. E.g., 22.5 in General format returns 1899-12-31-22.05.00.000000 because it thinks it is 22 hours 5 minutes. 22.5 in Numeric with 2 decimals returns 1899-12-31-22.50.00.000000 because it looks like 22.50 in the text, therefore is considered to be 22 hours 50 minutes.
Fixed problem with ExcelRowCount that it sometimes returns a row count that is too small. This can happen when there are blank rows at the top of the worksheet, especially after the user has edited the sheet.
Added ability to create a backup of the log file before we create a new one once the max log file size has been reached. Once the max log file size has been reached we now rename it to a backup log and then create a new one, this way there will be one current log and one old log.
Added ability to clear log file after it has reached the specified Max Log File Size stated in File/Settings.
Fixed problem with converting date fields to ISO format in the ExcelGetDelimRecord function. Excel Time fields were not being converted to ISO properly.
We now convert date fields to ISO format in the ExcelGetDelimRecord function.
Added XLS Row/Col/Used Range Retrieval. This way programmer knows how many rows/cols in a spreadsheet for reading. Note: Requires RJSRPGSQL library V1.46 and above with new function XLS_RowColCount
Changed code to return the absolute value of a boolean SQL field as either a positive 1 or a 0 instead of a -1 or a 0.
Added terminator data "199 EOF" at the end of each EXCELGETDELIMREC record function so that the RPG2SQL library V1.44 and above can use the SQL_TCPTrace and SQL_TCPMultBuff functions to attempt to read multiple data buffers if packets are split during receiving.
Note: Requires RJSRPGSQL library V1.44 and above.
We now release ADO connection when DBCloseConnection is called. This prevents SQL connections from staying open inadvertently because we previously were not releasing the ADO connection until the socket connection from the 400 was closed.
Added terminator data "199 EOF" at the end of each MOVEDELIMITED record function so that the RPG2SQL library V1.44 and above can use the SQL_TCPTrace and SQL_TCPMultBuff functions to attempt to read multiple data buffers if packets are split during receiving. Note: Requires RJSRPGSQL library V1.44 and above.
Fixed problem when getting SQL delimiter via GETSQLDELIM function. Apparently this was causing any error messages created during the time the GetSQLDelim function is called to get eaten so the program would not return errors that might happen during the GetDelimRecordBuffer function. Basically no errors would ever get returned from GetDelimRecordBuffer under this scenario if a Error 13 "Type Mismatch" or Error 94 "Invalid Use of Null" error occurred when parsing data.
If a bit field was NULL, this was causing Error 94 "Invalid Use of Null" to occur when parsing the bit/Boolean field from the record data stream. We now set Null bit fields to 0 automatically, so no Invalid Null errors should ever occur.
Changed project properties for threading model to "Thread Per Object" from "Thread Pool - 1". This will effectively force the component to create a thread for each CRPGSQLServer object created by a new connection. Had to remove the system tray component because the component was not multi-threaded so the program would not compile.
Added WORKSHEETCOPY option for XLS. This option has the following parameters:
- XLS_Command(SQL_Socket:WORKSHEETCOPY':'before~Sheet3~NewSheetName') This will place copy the active sheet before Sheet3 and name it NewSheetName.
- XLS_Command(SQL_Socket:WORKSHEETCOPY':'after~Sheet3~NewSheetName') This will place copy the active sheet after Sheet3 and name it NewSheetName.
If Sheet3 is not passed then it will place the new sheet at the very beginning of all the sheets when using the BEFORE parameter or at the very end of all the sheets when using the AFTER parameter. If the NewSheetName parameter is not passed then it will use the default Excel name.
Resolved following odd problem:
Fixed ExcelGetDelimRec function to return excel row as a delimited record buffer without getting any errors if a date field is Null.
Added ExcelGetDelimRec function to return excel row as a delimited record buffer.
Fixed problem so that if date conversion to ISO fails, we still return a valid ISO date value to the user. Also added a spec after a date field gets returned to the record buffer to prevent buffer parsing problems.
Added SETSQLDELIM function to soft code delimiter that can be used
Added fix to FILESAVEAS to kill existing file if selected file we're trying to Save already exists and is not the current file opened. Users were getting odd OLE errors when trying to do a saveas to an existing Excel spreadsheet file.
Added return code to RunSQLSelect method so that if an error occurs a on-zero return code is sent back to the user
Added binary file retrieval capability. Requires V1.33 or above of RJSRPGSQL library.
New RPG2SQLSV functions in RPG service program: SQL_PCFileSize, SQL_PCFileOpen, SQL_PCFileClose,SQL_PCFileGetB
Added XLSSUBTOTAL Excel operation. The RPG program can subtotal a total of 10 columns.
Example RPG call: This example will subtotal columns 2 and 4 everytime column 1 changes:
Eval Rtn = XLS_Command(SQL_Socket: 'XLSSUBTOTAL':'1~2~4')
Removed PPP License checking.
Updated the ExcelSetDelimRecord so it will add data starting at the column specified by the RPG program instead of over writting.
Added PCMKDIR method for creating directories. Requires RPG2SQL V1.28
Added AutoFit Excel Function to autosize all column widths to data.
Converted to ActiveX EXE so we can launch this program as a service.
When running RUNSQLEXEC statements, an Unknown Request message was showing when running in DEBUG mode.
When minimzed, main window now auto-hides in system tray. Must click system try to redisplay window. Updated system tray icon and regular icon to be a globe. We now display text descriptions for opcodes in debug mode. From the file menu, debug data can be exported to clipboard when in debug mode. Debug list box can be cleared from file menu. Can now scroll horizontally when in debug mode. If STARTMIN is passed on inital command line, the program minimzes to system tray right away.
Added ADO connection timeout property.
Added Create Empty PC File.
Added PC File Rename.
Added GetStringRow to see if it is faster than returning a record via
Added buffer MoveNext, MovePrev, MoveFirst and MoveLast code.
Added PC file copy, delete and exists routines.
Only show list box if running in debug mode
Added date retrieval in ISO format. Function name: GetFieldDate
Added screen resizing to resize all controls.
Fixed error logic returned when unhandled errors occur. This was causing situations for the ILE Service program on the AS/400 where no value was getting returned because the 100 SQL entry was not being correctly returned on unhandled errors in this program. The ILE service program would error out.
On GetFieldData, convert null numerics to 0 and null character fields to blanks to prevent errors.