RPG2SQL Integrator is ready for V6R1/V7R1 of i5/OS, starting with version 1.50
Added field delimiter parameter to SQLExtRecDDS API.
Added SQL_Verify API to check a connection
Added ability to handle Unicode/Graphic fields to SQLExtRecDDS API
Changed SQL_Quote to only trim trailing spaces not both leading and trailing spaces
Removed XLS examples that used ODBC. SQTEST04 & SQTEST09 were removed
Fixed problem with national language builds, that the source for the main service programs was being converted twice, so that the product would not work at all.
Changes to SQLXLSIMP command
Added SQLXLSEXP (Export PF to Worksheet) command. Parameters include:
Fixed problem with commas in row/column values in XLS functions. This was a problem for customers in countries that use the comma as the decimal point.
Updated SQL_GetParm to make sure that if there is a '199 EOF' present in the data buffer it gets stripped out automatically.
This update requires V1.0.57 or above of RPG2SQL Integrator PC Component.V1.0.57 of the PC component also fixes a lockup issue when calling SQL_GetParm.
Updated SQL_GetFldNum and SQL_GetFldNumB functions to handle rounding appropriately. There were some cases where numbers that ended in whole number decimal values were rounding into fractional values when converted to 30,9. The new numeric conversion routine should resolve this issue.
Example of rounding error:
NOTE: Minimum OS/400 release is V5R1.
Fixed problem with SQLXLSIMP that it truncated text in cells to 1024 characters. Maximum length is now around 4096 characters.
Fixed problem with SQLXLSIMP that it said a number in cell A1 had non-numeric data.
Finished SQLXLSIMP (Import Spreadsheet to PF) command. - message logging - if there are any errors or warnings, they are sent to the job log; a final message is also sent to QSYSOPR; messages can be found in message file RPSMSGF in RJSRPGSQL.
Diagnostic messages include:
Added blank row handling - options are *SKIP (default) and *DFT - message logged in either case that identifies the row in the worksheet that was empty - more data type supported - binary, date, time, timestamp, and floating point - numbers with more decimals than the PF field has are truncated without comment
Fixed SQL_TCPMultBuff() so that it gets the entire buffer from the socket. Added SQL_TCPMultBuff() to all samples.
Added XLS_SetDelimRc2 and XLS_GetDelimRc2, to get and set delimited buffer with 30,000-byte buffers.
Added XLS_RowColCount to retrieve row and column info from spreadsheet. Main use is to get number of rows in spreadsheet. Can also be used to get used range. Added SQLXLSIMP (Import Spreadsheet to PF) command. Parameters include: - workbook name - must be visible to the RPG2SQL PC component - output file - must exist and have field layout that matches spreadsheet - special values for library include *LIBL and *CURLIB - output member - mamber name and add/replace option - if member does not exist, it will be created - header rows - number of rows in header of spreadsheet - will not be included - error report - whether to report errors or warnings - not yet implemented - host - name or address of PC where RPG2SQL PC component is running - special value is *CURRENT, which will be converted to the IP address of the PC on which the terminal emulation is running (does not work in a batch job) At this time only character, zoned, and packed data types are supported. Empty cells will be converted to default values in the physical file. (Requires PC Component 1.0.47.)
Changed SQL_GetDelimFld, SQL_GetDelimFlt, and SQL_GetDelimRcd to return T4096String instead of TString. Added more field type constants to RPGSQLH for use with SQL_GetDelimFlt.
Added SQL_TCPTrace function for tracing TCP communications to file RPGSQLLOG.
SQL_TCPTrace(1) enables logging. SQL_TCPTrace(0) disables logging. (Default)
Added SQL_TCPMultBuff function for attempting to read multiple data buffers when using SQL_MoveFirsBuf, SQL_MoveNextBuf, SQL_MoveLastBuf, & SQL_MoveprevBuf.
SQL_TCPMultBuff(1) enables multibuffer.
SQL_TCPMultBuff(0) disables. (Default) Either function must be called before the SQL_Connect function. Note: Requires PC component V1.0.42 or above.
Added TCP_GetIPFromHost function, so that a fully-qualified domain name can be used, not just a dotted IP address. If a dotted IP address is passed in, it is left as is. The return values of TCP_GetIPFromHost are 0 if was successful, -11 if the fully-qualified domain name could not be found, and -12 if the 4-byte IP address could not be converted to dotted IP format.
The samples were changed to use TCP_GetIPFromHost in order to get the dotted IP address used to connect. COBOL samples were changed to use service program RPGSQL01R instead of RPGSQL02R, which does not have the newest features, including the Excel functions. The examples show how to use underscores in the procedure names, as well as variable-length character and binary parameters. COBOL copybooks RPGSQL1CB (for working storage) & RPGSQL2CB (some utility functions) were created. Build programs and COBOL programs were created in addition to the sample command and processing program.
Change service programs RPGSQL01R, RPGSQL02R, and RPGDDS01R so that they will have the same signature from one version of the product to the next. Customers who use these service programs will not need to recompile their programs after this version, unless they use new functions.
Added RPGDDS01R service program to binding directory so SQTEST25R and other samples will compile that use the DDS buffer service program. Also fixed SQTEST25R so that it will compile correctly. It was referring to SQL_ExtDDS* functions that have been renamed to SQLExtDDS.
Added XLS_GETDELIMREC function to retrieve the contents of a spreadsheet row into a delimited record buffer. See sample SQTEST29 for example usage. Requires RPG2SQL PC Server V1.0.36 or above.
Extended data buffer from 256 to 4096 for SQL_GetFldChrb and SQL_GetFldChr buffer retrieval functions.Resolved lockup issues on PC component when using SQL_GetDelimRcd function to retreive delimited values from a record buffer when tilde was used as a delimiter. Requires RPG2SQL PC Server V1.0.36 or above.
Added SQL_SetSQLDelim function for determining what value to use for delimiter. A tilde (~) is used as the default delimiter. A multiple character delimiter can be specified if desired. Requires RPG2SQL PC Server V1.0.34 or above.
Added SQLSetSQLDelim function to COBOL service program. This is the COBOL equivalent of SQL_SetSQLDelim. Added SQLNonBlocking function to COBOL service program. This is the COBOL equivalent of SQL_NonBlocking.
Insure that internal data buffer gets cleared before doing buffered read operations. This was causing problems when customers actually wanted to save and parse the returned record data in their own programs instead of using the RPG2SQL field parsing functions.
Major internal library enhancement. Added the ability to use non-blocking IP connections so that all SQL TCP/IP connections to the PC server can be set to be timed out after a selected number of seconds. Blocking connections are still the default so the SQL_NonBlocking function must be called before running the SQL_Connect function if non-blocking TCP/IP connections are desired. If 0 is passed for the timeout valuem the timeout value is defaulted to 60 seconds.
Fixed problem with SQL timeout truncating timeout values, thus causing SQL queries to prematurely timeout.
Added fully qualified library names to service programs bound to service programs RPGDDS01R and RPGSQL02R.
Added API'S for reading a binary PC file by buffer.
Added binding directory as well as H spec to header of all sample programs. This will eliminate the need to use the special B CL programs to generate an RPG2SQL RPG program. All samples can now simply be compiled with option 14. in PDM. Binding directory name: RJSRPGSQL in library RJSRPGSQL.
Added sample program to write records to Domino database via NotesSQL ODBC driver. Added sample program to read records from Domino database via NotesSQL ODBC driver.
Added sample program to create a new account, address, contact and opportunity record via SalesLogix.
Added sample program to write email recip record to WinSpool Email Report Server access database WSPESR32.MDB.
Consolidated includes into RPGSQLH to avoid AS/400 SQL precompiler messages because the AS/400 SQL precompiler does not support nested copy include statements.
Added SQL_MKDir function for creating directories on the PC server.
Changed SCTEST05L to have the following check IF SQL_RTN <> 0 instead of IF SQL_RTN > 0 after the call to SQLEXTDDS. This will insure that we bail out if DDS for selected file is not retrieved correctly.
Added functions so programs can now read an entire SQL record into a DDS defined data structure or record structure without having to return each field individually. This was added to a separate service program named: RPGDDS01R.
Ex: If customer number is the first field returned from the SQL query the customer field should be the first field defined in the DDS defined record structure.
Added COBOL sample to read SQL Server database and extract record data to DDS defined record via SQLExtRecDDS- SCTEST05.
Added initial RPG2Excel functionality.
Added Subfile Inquiry Interactive Samples. SQTEST23 - Query Records from Name/Address. Table with MS Access Driver. SQTEST24 - Query Records from Titles Table in Microsoft SQL Server PUBS database.
Added logic to service program and to the PC server component to handle multiple TCP/IP data packets being sent from the AS/400. Apparently the original data packet can potentially arrive as multiple packets to the PC server component, so the new logic will handle this scenario. Requires RPG2SQL PC Server V1.0.18 or above. Extended SQL_Quote buffer size from 2048 to 4096 bytes.
Added COBOL sample to read SQL Server database SCTEST04
Fixed COBOL service program to correctly process returned dates in MM/DD/YY format returned by SQLGetFldDat2B.
Added SQL_LastErrNum2 function to return last ADO error message as a numeric value. SQL_LastErrNum returns the error number as a string. Added support for calling stored procedures with parms and also allow the return of parms or recordsets from SP calls.
Added SQL_RunSPBasic to allow users to call SQL stored procedures and check for a successful return code. Soon we will add the ability to call stored procedures that can return parameter values as well as recordsets when a stored procedure is run.
Added SQL_Quote function to allow users to take a string with a single quote and make sure it gets padded to two single quotes before performing a data insert via SQL. Otherwise strings with single quotes will cause an insert or update failure.
Added RPGSQL02R service program wrapper for allowing COBOL programmers to use the RPG2SQL functionality. Added initial COBOL sample programs SCTEST01 - SCTEST03 to source file SOURCE. Also added NAMEADDR2 file definition.
Soft coded ASCII/EBCDIC translation tables so users can set the tables to match their own systems.
Added SQL Server transaction control functions.
Added SQL Server samples:
Created SQTEST09 sample to read PF and write all records to Excel spreadsheet.
Added DOCPING utility command so users can PING TCP/IP host printers and systems.
Added new PC file management routines. SQL_PCFileEmpty - Create empty PC file.
Added new PC file management routines. SQL_PCFileRen - Renames PC file.
Make sure service program adds the RJSRPGSQL library to the library list, so the access code checking does not fail on program PRDINFO4 not being found.
Re-added license check and status message.
Temporarily removed license check from RPG service program RPGSQL01R. V1.08 test version was not working correctly.
Re-added license check and status message.
Temporarily removed license check from RPG service program RPGSQL01R.
Created SQL socket test program SQLSOCKET.
Added SQLTEST10 sample to test reading a CSV file to a PF via SQL.
Added NEW buffered database functions to read back the entire record buffer. This enhances performance when doing bulk record reads from a SQL server or access database.
Added SQLTEST08 sample to read all records from the IRPGSQL.MDB table to file SQLTEST8PF in QTEMP. This tests the new buffered reading commands.
Added SQLTEST09 sample to test the new PC file management routines.
Created new buffered record retrieval function which uses the ADO GetString method to retrieve a delimited record.
Created function to convert MM/DD/YYYY dates into ISO format when a record is retrieved by the SQL_GetStrRow function.
Created SQLDATE1C to convert MM/DD/YYYY dates into ISO format.
Added SQLTEST06 sample to create a new spreadsheet via ADO.
Added SQL_PCFileExist function to check if a PC file exists.
Added SQL_PCFileDel function to delete a PC file if it exists.
Added SQL_PCFileCopy function to copy a PC file to a selected destination PC file.
Added AS/400 access code checking.
Added SQLUPG command to upgrade settings to new library if installed.
Added SQL_GetFldDat,SQL_GetFldNum and SQL_GetFldChr field retrieval function calls. Characters fields are returned the same as the SQL_GetField function. Dates are returned in *ISO timestamp format. Numeric fields are returned as packed 30,9.
Changed command SQLTEST01 to call SQLTEST01C instead of directly calling SQLTEST01R.Changed command SQLTEST03 to call SQLTEST03C instead of directly calling SQLTEST03R.
INITIAL DEVELOPMENT RELEASE
Still have questions? We can help. Submit a case to Technical Support.