Home

RPG2SQL Integrator - RJS Support Center

1. The PAGESETUPPRINTAREA XLS_Command function is used to set the range to be printed in A1 format Example to print cells A1 to C5 e Eval Rtn XLS Command SQL Socket PAGESETUPPRINTAREA e SAS1 C 5 P1 Range to be printed in A1 format Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPLEFTHEADER The PAGESETUPLEFTHEADER XLS_Command function is used to set the left part of the header Example to set the left part of the header to the date e Eval Rtn XLS Command SQL Socket e PAGESETUPLEFTHEADER D P1 Text to use for the left part of the header See Excel documentation for formatting codes that can be used in the header Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPCENTERHEADER The PAGESETUPCENTERHEADER XLS_Command function is used to set the center part of the header Example to set the center part of the header to the page number with hyphens around the number 102 RPG2SQL Integrator Cc Eval Rtn XLS Command SQL Socket C PAGESETUPCENTERHEADER amp P P1 Text to use for the center part of the header See Excel documentation for formatting codes that can be used in the header Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API ca
2. 2 FromPCFile PC file name to rename from Can be a standard file path name C TEST TXT or UNC based file name SERVER SHARE TEST TXT 3 ToPCFile PC file name to rename to Can be a standard file path name C TEST TXT or UNC based file name SERVER SHARE TEST TXT xample Usage TA ss ss a i aS a a a a a a a a a a a E a a a a a a a a a a a a a me a a a a E q a a at a et a a ms a a mt a a Rename selected PC file AAA AAA A A A A A A A A A A A A E be A A A A A A A A A A A A A A A A A A E A A A be E E E be e e re e re e Eval RtnErr SOL PCFileRen SOL Socket d RE e er test2 txt SQL_PCFileCopy The SQL_PCFileCopy API allows a selected PC file to be copied on the server PC running the RPG2SQL Integrator server software Returns 44 RPG2SQL Integrator 0 operation completed normally Not 0 Errors occurred while deleting file Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL PCFileCopy Pr Like TInt D Socket Like TInt Value D FromPCFile Like TString Value D ToPCFile Like TString Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 FromPCFile PC file name to copy Can be a standard file path name C TEST TXT or UNC based file name SERVER SHARE TEST TXT 3 ToPCFile PC file name to copy to Can be a standard file path name C TEST TXT or UNC
3. Error messages for SQLXLSEXP ESCAPE Messages RPS9986 Warnings issued while exporting file 81 82 83 DSPJOB amp 6 amp 5 amp 4 for diagnostic messages RPS9989 Error occurred while exporting file amp 1 amp 2 amp 3 DSPJOB amp 6 amp 5 amp 4 for diagnostic messages Top Upgrade Settings SQLUPG Where allowed to run All environments ALL Parameters Threadsafe No Examples Error messages The Upgrade Settings SQLUPG command upgrades RPG2SQL Integrator software Run this command to copy settings from an old library after upgrading to a new version of the RJSRPGSQL library Restrictions e You must have RISRPGSQL added to your library list or qualify the command with the library name e This command is not threadsafe 148 RPG2SQL Integrator Top Parameters OLDLIB Old library Character value Required Positional 1 NEWLIB New library Character value RJSRPGSQL Optional Positional 2 Top Old library OLDLIB Specifies the re named version of the RJSRPGSQL library This is a required parameter character value Specify the the re named library For example RISRPGSQLV Top New library NEWLIB Specifies the name of the new or upgraded RISRPGSAQL library RJSRPGSQL Specifies the library name RUSRPGSQL character value Specify the the new or upgraded libarary name Top Example for SQLUPG Note This example assumes you have added RUSRPGSAQL to your library list Otherwise you must qualify the command with the library
4. Exit program if no IP connection made IF SQL SOCKET lt 999 EXIT PROGRAM AND CONTINUE RUN UNIT END IF SQLDisconnect This function is the COBOL version of the SQL_Disconnect procedure COBOL example usage 122 RPG2SQL Integrator CALL PROCEDURE SOLDISCONNECT USING SQL SOCKET RETURNING SQL BIN SQLTimeout This function is the COBOL version of the SQL_Timeout procedure MOVE 60 to SQL TIMEOUTVAL CALL PROCEDURE SQLTIMEOUT USING SQL SOCKET SQL TIMEOUTVAL RETURNING SQL BIN SQLDBOpenConn This function is the COBOL version of the SQL_DBOpenConn procedure COBOL example usage E SE S Set ADO Data Source for Access Database o Sih Sa ih ik a Sk SSS EE E hk a EE i EE E E MOVE Driver Microsoft Access Driver mdb DBQ C Progra m files rpgsqlsv irpgsql mdb Uid Admin Pwd to SQL DATASRC a a a a i a a a si as a a a a a a a a i a a a is a a a i il Open Database Connection to selected ADO ODBC database ai Sem SiS Set ja a ia a ae as ii e i mS Ee ia be Si CALL PROCEDURE SQLDBOPENCONN USING SQL SOCKET SQL DATASRC RETURNING SQL_RTN SQLDBCloseConn This function is the COBOL version of the SQL_DBCloseConn procedure COBOL example usage 123 RPG2SQL Integrator A RA el i si i i a i a Sp il slp a lp i i ia i Si ti ye ci a Close Database Connection to selected ADO ODBC database a as is as i as i cs as esa as a cs a a ia CALL PROCEDURE
5. Not 0 Errors occurred while adding stored procedure parameter Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL DBCloseRS Pr Like TInt D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection RPG2SQL ILE DDS Service Program Procedures RPG2SQL ILE DDS Service Program Procedures Introduction to DDS Handler Service Program The RPG2SQL ILE DDS Service Program has been added to the RPG2SQL Integrator product to allow data returned from a SQL record operation to be quickly parsed into individual fields and placed into a DDS defined record structure or record format with a single API call 60 RPG2SQL Integrator Prior to the addition of this new service program returning fields from an SQL query used to be a process of reading each individual field from the record buffer and manually moving the resulting data to a DDS or program defined field Using the DDS Handler Service Program eases the task by returning multiple fields with a single ILE procedure call The SQLExtDDS and SQLExtRecDDS functions can be used from ILE RPG or COBOL programs to parse the record data returned by the SQL MoveFirsBuf SQL MoveLastBuf SQL MoveNextBuf SQL MovePrevBuf procedures RPG Sample Program SQTEST25 can be found in source file SOURCE in library RJSRPGSQL COBOL Sample Program SCTESTO5 can be
6. Returns 0 Excel script run was successful 119 RPG2SQL Integrator 1 Excel script run was not successful 2 Unknown error 3 Unknown communications failure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DXLS_ RunScript Pr Like TInt D Socket Like TInt Value D Scriptrile 255 Value D Varying D FunctionName 100 Value D Varying D ParmString 3700 Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Script file Location of the PC script file to run 3 Function name The name of the VBScript function to call within the selected script file Functions can have all kind of names depending on your scripting requirements 4 Parm string This parameter can be used to pass delimited data strings to the selected VBScript if parameters need to be passed to the script for processing within the script Example Usage 1 Type the following VBScript code in a text editor on the PC where RPG2SQL Integrator is running and save the code as C temp helloexcel vbs Function TestExcell sCommand sExcelObject sObject2 sObject3 Function TestExcell Purpose Write a few cells of data into the current Excel sheet Parms Single parameter separated by semicolons Break down parameters Parms should be delimited by semicolons if passed Parms are all zero baed Parml
7. Software Systems User Guide RPG2SQL Integrator 2011 RJS Software Systems Document Version 1 50 5 RJS Software Systems 2970 Judicial Road Suite 100 Burnsville MN 55337 Phone 952 736 5800 Fax 952 736 5801 Sales e mail sales rjssoftware com Support e mail support rjssoftware com Web site http www rjssoftware com 2011 RJS Software Systems All rights reserved No parts of this work may be reproduced in any form or by any means graphic electronic or mechanical including photocopying recording taping or information storage and retrieval systems without the written permission of RUS Software Systems RPG2SQL Integrator is a trademark of RUS Software Systems Products that are referred to in this document may be either trademarks and or registered trademarks of the respective owners The publisher and the author make no claim to these trademarks While every precaution has been taken in the preparation of this document RJS Software Systems assumes no responsibility for errors or omissions or for damages resulting from the use of information contained in this document or from the use of programs and source code that may accompany it The information contained in this document is subject to change without notice In no event shall the publisher and the author be liable for any loss of profit or any other commercial damage caused or alleged to have been caused directly or indirectly by this document Printed
8. kee e e e e A A A o CS Send Testl Test2 Test3 data strings Ce to cells Al Bl C1 AAA A A A e a e a a a A A A be E bb E be E E E E E E bn be e re e Eval Rtn XLS SetDelimRec SQL Socket E 020 AL Testl Test2 Testate AAA A A A A A A A A A o ce Kill Excel AAA AAA A e e e A A A E be E A A o Eval Rtn XLS Quit SOL Socket AAA A A A A A A E E E bn be e re c Disconnect from RPGSQL server AAA AA A e a e a a E E a E a a A E E E E E E E E E E E E bn be e re C callp SQL Disconnect SQL Socket XLS_GetDelimRec The XLS_GetDelimRec API is used to retrieve a delimited iSeries data record buffer from the active Excel worksheet The amount of data that can be received is up to 4096 characters total length The XLS_GetDelimRec API can be called multiple times as needed Returns This function call returns a delimited record buffer string or ERROR if an error occurs Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype 115 RPG2SQL Integrator DXLS GetDelimRec Pr Like T4096String D Socket Like TInt Value D StartRowNum 7P 0 Value D StartColNum 7P 0 Value D EndColNum 7P 0 Value D CellRange 20A Value D Varying D DelimParms 4000A Value D Varying D DelimChar 6A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 StartRowNum This parameter is used to speci
9. 97 RPG2SQL Integrator Border operations allow users to outline a single sell or multiple cells The following sections list the special constant values that can be used to define the look and feel for border operations Note Special constants for the line styles weights and colors are also defined in the RPGSQLH include source member Border Line Type This constant value helps define the type of outline that will be used within a cell range An outline can be made around the entire cell or cell range across the top bottom right of left sides EdgeTop 8 EdgeLeft 7 EdgeBottom 9 EdgeRight 10 DiagonalUp 6 DiagonalDown 5 InsideHorizontal 12 InsideVertical 11 Border Line Styles This constant value helps define the style of line used when outlining a range of cells None 4142 Continuous 1 Dash 4115 DashDot 4 DashDotDot 5 Dot 4118 Double 4119 SlantDashDot 13 Border Weight This constant value helps define the thickness or weight of the line used when outlining a range of cells Hairline 1 Medium 4138 Thick 4 98 RPG2SQL Integrator Thin 2 Border Color This constant value helps define the color of line used when outlining a range of cells Black 0 Blue 16711680 Cyan 16776960 Green 65280 Magenta 16711935 Red 255 White 16777215 Yellow 65535 XLREFRESHPIVOTTABLE The XLREFRESHPIVOTTABLE XLS_Command function is used to refresh data in the selected pivot table SCHER to refresh
10. Example to disable italics Cc Eval Rtn XLS Command SQL Socket C SETACTIVECELLFONTITALIC 0 Example to set italics 76 RPG2SQL Integrator Cc Eval Rtn XLS Command SQL Socket C SETACTIVECELLFONTITALIC 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLFORECOLOR The SETACTIVECELLFORECOLOR XLS_Command function is used to set the foreground color for the active cell Example to set the font color to red e Eval Rtn XLS Command SQL Socket E SETACTIVECELLFORECOLOR 255 Parameters P1 Numeric color value Black 0 Blue 16711680 Cyan 16776960 Green 65280 Magenta 16711935 Red 255 White 16777215 Yellow 65535 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLBACKCOLOR The SETACTIVECELLBACKCOLOR XLS_Command function is used to set the background color for the active cell Example to set the font color to red C Eval Rtn XLS Command SQL Socket E SETACTIVECELLBACKCOLOR 255 Parameters P1 Numeric color value Black 0 Blue 16711680 Cyan 16776960 Green 65280 Magenta 16711935 77 RPG2SQL Integrator Red 255 White 16777215 Yellow 65535 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is return
11. S limitation Eval Rtn SQL DBCloseRS SQL Socket Retrieve SP Return Value to return record SP Return Value is always converted to string Eval RtnParm SQL GetParm SQL Socket Return Retrieve Integer Return Value to return record SP Return Value is always converted to string Eval RtnParml SQL GetParm SQL Socket Returnl Retrieve VarChar Return Value to return record SP Return Value is always converted to string Eval RtnParm2 SQL GetParm SQL Socket e Return2 59 RPG2SQL Integrator SQL_DBCloseRS The SQL_DBCloseRS API is used to close a recordset opened by running a SQL record select statement or stored procedure that creates or returns a recordset As good coding practice the SQL_DBCloseRS function call should be called after you have completed working with a recordset however the SQL_DBCloseConn function will also close all open tables as it closes the database connection if being used Just make sure to call one of these functions before your program exits Note When SQL_RunSPSelect is used to return a recordset from a stored procedure all records must first be read and processed After all records have been processed SQL_DBCloseRS should be called and then the stored procedure return code and return parameters will be available via the SQL_GetParm function call Returns A general return code indicating success or failure is returned 0 Parameter was added normally
12. SQL_Quote function RPG Prototype DSQL Quote Pr 4096A Varying D iaInput 4096A Const D Varying Parameters 1 Variable length string value of up to 4096 characters Check string for single quotes and pad if needed AH ea SSS eee eee eee meee ee eee erm emer mee ae am DWorkString S 512A iC Eval WorkString SQL Quote WorkString SQL_RunSPBasic The SQL_RunSPBasic API will run a SQL Server or other database stored procedure with input parameters allowed The user must build the stored procedure call string before passing the single call string to call the stored procedure 48 RPG2SQL Integrator This function call is used for very simple stored procedure calls that do not return a recordset or parameters No parameter values are returned however a return code indicating success or failure is returned Returns A return code indicating whether the stored procedure ran correctly 0 Stored procedure call completed normally Not 0 Errors occurred while running stored procedure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL RunSPBasic Pr Like Tint D Socket Like TInt Value D SPString 4096A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Variable length stored procedure execution string value of up to 4096 characters Call the rjssamplel stored proce
13. This setting specifies the data type that will be used when passing the parameter to the selected stored procedure Use any of the following special constants to determine the data type adArray Data is an array adBigint Data is a large integer adBinary Data contains binary data adBoolean Data is a boolean true false field adChar Data is character adCurrency Data is a currency field adDate Data is a date field adDBDate Data is a database data field adDBTime Data is a database time field adDBTimeStamp Data is a database timestamp field adDecimal Data is a decimal field adDouble Data is a double precision field adGUID Data contains a GUID string adinteger Data is in integer format adNumeric Data is in numeric format adSingle Data is in single precision format adSmallint Data is a small integer adVarChar Data is a variable character string adVariant Data is a variant field type 4 Parameter direction This setting determines if the selected stored procedure parameter will be used for input output or both Use any of the following special constants to determine the parameter direction ParmInput Input only parameter ParmOutput Output only parameter 55 RPG2SQL Integrator ParminputOutput Input and Output Parameter ParmReturnValue Return code from stored procedure If you will be expecting the stored procedure to return a return code the first parameter passed to t
14. XLS Command SQL Socket C SETSELECTIONFONTSUBSCRIPT 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONFONTOUTLINEFONT The SETSELECTIONFONTOUTLINEFONT XLS_Command function is used to set the outline font value for the selected cell range Example to disable outline fonts Cc Eval Rtn XLS Command SQL Socket Cc SETSELECTIONFONTOUTLINEFONT 0 Example to set outline fonts C Eval Rtn XLS Command SQL Socket C SETSELECTIONFONTOUTLINEFONT 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned 86 RPG2SQL Integrator SETSELECTIONFONTSHADOW The SETSELECTIONFONTSHADOW XLS_Command function is used to set the font shadow value for the selected cell range SCHER to disable font shadowing Eval Rtn XLS Command SQL Socket e SETSELECTIONFONTSHADOW 0 SCHER to set font shadowing Eval Rtn XLS Command SQL Socket a SETSELECTIONFONTSHADOW 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONFONTBOLD The SETSELECTIONFONTBOLD XLS_Command function is used to set the font bold value for the selected cell range SCHER to disable font bolding Eval Rtn XLS Command SQL Socket o SETSELECTIONFONTBOLD 0
15. arrparms 0 Parm2 arrparms 1 etc sExcelObject ActiveSheet Range B3 value arrparms 0 sExcelObject ActiveSheet Range C3 value arrparms 1 TestExcell 0 End Function 2 The following code runs the script you created in step 1 120 RPG2SQL Integrator Launch MS Excel and make it visible AAA A A A A A A A A A A A A A A A A A A A A A A E re E be re e e re e re Cc Eval Rtn XLS Launch SQL Socket e Eval Rtn XLS Visible SQL Socket 1 AAA AA A A A A A A o Create new spreadsheet file AAA A A a A A E E E A A A A o e Eval Rtn XLS Command SQL Socket e FILENEW ken e e be e e e E E be A A A bb E E bb E be E re E bb re e re e re Run Excel VBScript Macro to set data in MS Excel spreadsheet cells Eval Rtn XLS RunScript SQL Socket c temp helloexcel vbs TestExcell Call Test Data for B3 Call Test Data for C3 CH CC EE Problem Handling Problem Handling Common Errors If errors occur while running the iSeries RPG2SQL Integrator APIs always make sure to check the AS 400 job log by using the DSPJOBLOG command from the AS 400 command line Many times the job log will give some idea as to what is failing You can also run the RPG2SQL Integrator PC Server component in debug mode if you want to see what errors are occurring If you need to see what s happening you can also turn on logging in the settings for the RPG2SQL Integrator PC component Other Erro
16. 10 Eval Rtn XLS Command SQL Socket g SETRANGEROWCOL 1 1 1 10 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELL The SETACTIVECELL XLS_Command function is used to set the active cell in the active worksheet using A1 format Example to set the active cell to A10 Once selected the active cell operations can be used to set data or formatting for the cell 73 RPG2SQL Integrator e Eval Rtn XLS Command SQL Socket e SETACTIVECELL A10 P1 Cell in A1 format Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLROWCOL The SETACTIVECELLROWCOL XLS_Command function is used to set the active cell in active worksheet via Row Col Example to set the active cell to A1 Row 1 Column 1 Once selected the active cell operations can be used to set data or formatting for the cell e Eval Rtn XLS Command SQL Socket C SETACTIVECELLROWCOL 1 1 P1 Row Number P2 Column Number Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLVALUE The SETACTIVECELLVALUE XLS_Command function is used to set the active cell value Example to set the active cell to This is a test e Eval Rtn XLS Command SQL S
17. 2 PCFile PC file name to create Can be a standard file path name C TEST TXT or UNC based file name SERVER SHARE TEST TXT SQL_PCFileExist The SQL_PCFileExist API checks to see if a selected PC file exists on the server PC running the RPG2SQL Integrator server software Returns 0 operation completed normally Not 0 Errors occurred while deleting file Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL PCFileExist Pr Like TInt D Socket Like TInt Value D PCFile Like TString Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 PCFile PC file name to check Can be a standard file path name C TEST TXT or UNC based file name SERVER SHARE TEST TXT Example Usage 43 RPG2SQL Integrator SQL_PCFileRen The SQL_PCFileRen API allows a selected PC file to be renamed on the server PC running the RPG2SQL Integrator server software Returns 0 operation completed normally Not 0 Errors occurred while deleting file Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL PCFileRen PY Like TInt D Socket Like TInt Value D FromPCFile Like TString Value D ToPCFile Like TString Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection
18. A A A A A A A A A A A A A A A A A A A A A A A A A A A A A E re E be re e e re e re IG Eval RtnNum309 SQL GetFldNumB E RtrRecord 21 SQL_GetFldDatB The SQL_GetFldDatB API is used to retrieve a date field value from the most recent SQL_MoveFirstBuf SQL_MoveLastBuf SQL_MoveNextBuf or SQL_MovePrevBuf call Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns The field data value is returned in ISO date format as a timestamp value RPG date functions can be used to reformat the ISO date as needed for display or other calculations Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype 37 RPG2SQL Integrator DSQL GetFldDatB Pr Z D RecordBuffer 4096A Value D Varying D Delimiter 4096A Value D Varying D FldNumber 10I 0 Value D Delimiter Parameters 1 RecordBuffer The delimited data record returned from the last SQL_MoveFirstBuf SQL_MoveLastBuf SQL_MoveNextBuf or SQL_MovePrevBuf call 2 Delimiter Record delimiter for returned record Always Use tilde when using SQL_MoveFirstBuf SQL_MoveLastBuf SQL_MoveNextBuf or SQL_MovePrevBuf record operations 3 FldNumber Enter the 1 based relative recordset field number w Get first field value in 150 date format AAA A A A A A a E E E E E a E A ee e Eval RtnDate SQL GetFldDatB e REnkecord Te SE SQL_GetStrRow
19. ADO or ODBC data sources on the PC to talk to the various PC databases Listed below are some example strings for passing to the SQL_DBOpenConn function More example strings can be found in member ADODSNSAMP in RISRPGSQL SOURCE RPG2SQL Integrator Use a Predefined ODBC Data Source DSN iC Eval DataSrc DSN myDSN E Uid myUser ic Pwd myPassword The example listed above opens an existing ODBC data source named myDSN defined via the Windows ODBC Administrator program To start the ODBC Administrator program select Start Run type ODBCAD32 EXE and click OK Microsoft Access Table DSNless Connection String Eval DataSrc Driver Microsoft Access Driver mdb jp T Dbq c program files epgsaqlev irpgsql mdb Uid admin Pwd GAO ao The example listed above opens an Access database without the need to define an ODBC data source via the Windows ODDBC Administrator program This is known as a DSNless ADO connection string Microsoft Excel DSNless Connection String Eval DataSrc Driver Microsoft Excel Driver tie ls T DeiverIiD 790 Dbq c temp test xls DefaultDir c temp Pwd CACAO O The example listed above opens an Excel workbook without the need to define an ODBC data source via the Windows ODDBC Administrator program This is known as a DSNless ADO connection string Microsoft Dbase Driver DSNless Connection String E Eval DataSrc Driver Microsoft dBA
20. BE to set font bolding Eval Rtn XLS Command SQL Socket E SETSELECTIONFONTBOLD 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONFONTITALIC The SETSELECTIONFONTITALIC XLS_Command function is used to set the font italic value for the selected cell range IDE to disable font italics Eval Rtn XLS Command SQL Socket SETSELECTIONFONTITALIC 0 87 RPG2SQL Integrator Example to set font italics Cc Eval Rtn XLS Command SQL Socket C SETSELEC TIONFONTITALIC 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONFONTUNDERLINE The SETSELECTIONFONTUNDERLINE XLS_Command function is used to set the font underline value for the selected cell range Example to disable font underlining C Eval Rtn XLS Command SQL Socket Cc SETSELECTIONFONTUNDERLINE 0 Example to set font underlining to have a double underline C Eval Rtn XLS Command SQL Socket C SETSELECTIONFONTUNDERLINE 4119 Parameters P1 Numeric Font underline style Single Underline 2 Double Underline 4119 Single Accounting 4 Double Accounting 5 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONBACKCOLOR The SE
21. Bis ooo deefe Ree dE ee dor ataa aar Taa dE 93 SETINSERTPAGEBREAKG tege a ee a ENE a A E O 93 SETSELECTEDBORDEROUTLINE arise oann agat a aani a a a AEE A aE ER 94 SETSELECTEDBORDERLINE aiana n aa aat aaa a T EEEE eL EDA 94 SETSELECTEDBORDERNONE seeing orn aat aaa eaaa Eana Aa NEA EA aE Ea 94 DUSEL sve en a E A 95 WORKSHEETACTIVATE cocoa A E a 95 WORKSHEETADD nie o aaa 95 WORKSHEETADDOLEOBJECT 0 cccceeececeeeeeceeeeeeeaeeeeeeeceaeeeeaaeeseneeseaeeesaaeeesaaeseeeeeseaeeesaeeeeaeeeeees 96 WORKSHEET RTE RE 96 WORKSHEET GORY EE 96 EXCELDATEFORMATON sscicciszcsceccnscetdccevaachecs enn deans dads asthe gustan esa a A E ESN 97 EXCELDA TEFORMATOF E eet itiiti nl vee avast ES caer ieee 97 Line Styles for Border Operations oooonccccnccnnoniconoconnncccnonncnnorn non nnnccrrrcnnrrrn rr rnrnr 97 Line Styles for Border Operations oooonocccinccccncccononanonccnnoncccnonn nono n cnn cnn rca nr 97 Border Line Type iii DE S dada 98 Border Line EE 98 Border Weight iii aetna ea Aare ae vag eaves tee devas Aaa See 98 leie TT TEE 99 XEREFRESHPIVOTTABLE eege ged 99 XESETGELEDATA 0000 A tee ta nee ENEE 99 XESETCHECKBOX 0 AA E E a GA aA 100 ALSSUBTOTA iii AA A eee 100 PAGESETUPPRINTTITLEROWS AANEREN 101 PAGESETUPPRINTTITEEGOLUMNS aaraa aat aT aeaa aa ardido 101 PAGESE TUP PRIN TA REA a a a r a a aaa aa a e aa llei inaita 102 PAGESETUPLEETHEADER coito ii rallies 102 PAGESETUPGENTERHEADER eaaa a aaa me araa aa naea at alice ps 102 PAGESETUPRIG
22. Codes Value Type This is a character value with a length of 1 A sample entry would be 1 RJSRPGSQL Stores the RPG2SQL Integrator product authorization license key The authorization code can be set by using the PRDSEC command Possible Values License key provided by RJS Software This is a character value with a length of 50 SQLASCII Translates from ASCII to EBCDIC in your national language Possible Value This is a character value with a length of 10 Default Value Q037337850 SQLEBCDIC Translates from EBCDIC to ASCII in your national language Possible Value This is a character value with a length of 10 Default Value Q850337037 Contains the current PRG2SQL Integrator library version information RJS VERSION Software support staff can use the software version to diagnose problems Value Type This is a character value with a length of 50 A valid entry would be RPG2SQL Integrator Version V1 50 131 RPG2SQL Integrator Appendix C AS 400 Commands Appendix C AS 400 Commands This appendix contains detailed descriptions of and command syntax for each RPG2SQL Integrator AS 400 command Check for IFS File Existence CHKOBJIFS Where allowed to run All environments ALL Parameters Threadsafe No Examples Error messages The Check for IFS File Existence CHKOBJIFS command checks for the existence of an IFS directory or file The command returns a CPF9898 message if the IFS file is not found The command
23. D Socket Like TInt Value D Delimiter Like TString Value D Quotes Like TString Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Delimiter Record delimiter for returned record Can be comma tilde or any other character you choose 3 Quotes Use double quote values to surround text fields with O No quotes 1 Use double quotes xample Usage e as acom si Sg a as a Ft o SA A A A A A A E A A VE be EE be a S Get Delimited Field List Record data from SQL Table E a a a a ss as ss ss es ss a ss ss ss ss ms ie ss a ss a a ss as e a a Cc Eval RtnData SQL GetDelimFld SQL Socket E E GI SQL_GetDelimFlt The SQL_GetrDelimFlt API is used to retrieve a delimited field type list after recordset has been opened Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns This function call returns a delimited field name record buffer string or ERROR if an error occurs or if no record was found Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL GetDelimFlt Pr Like T4096String D Socket Like TInt Value D Delimiter Like TString Value D Quotes Like TString Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Delimiter Record delimiter for returned record Can be comma tilde or any other character you c
24. D Socket Like TInt Value D StartRowNum 7P 0 Value D StartColNum 7P 0 Value D StartCell 6A Value D Varying D DelimRecord 4000A Value D Varying D DelimChar 6A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 StartRowNum This parameter is used to specify the row of the starting cell to which the delimited data record will be written Note If this parameter is not used the StartCell value must be specified and the StartRowNum and StartColNum values must be set to 0 3 StartColNum This parameter is used to specify the column of the starting cell to which the delimited data record will be written Note If this parameter is not used the StartCell value must be specified and the StartRowNum and StartColNum values must be set to 0 4 StartCell This parameter is used to specify the starting cell to which the delimited data record will be written Note If this parameter is not used the StartRowNum and StartColNum values must be greater than 0 and this value must be blanks 5 DelimRecord This parameter is used to specify a delimited string value to be placed into the Excel worksheet and parsed into cells based on the selected delimiter value Ex Test1 Test 2 Test3 would be placed into cells A1 B1 and C1 if A1 was passed as the StartCell value 6 DelimChar This character string is the delimiter used to parse the DelimRecord value into fields If blanks are passed
25. D Timeout Like TInt Value Parameters 1 OnOff Use nonblocking socket connections O blocking 1 nonblocking with timeout 2 Timeout Set nonblocking timeout in seconds If set to 0 timeout defaults to 60 seconds Set connection to nonblocking with 30 second timeout VW e AAA EE A i i EE EE E EE EE a i EE EE EE EE EE EEN C Call SQL_NonBlocking 1 30 TCP_GetIPFromHost The TCP_GetIPFromHost API is used to convert a fully qualified domain name into its dotted IP address All other functions that use an IP address use the dotted IP format Note A dotted IP address will be left as is Returns 0 Domain name was converted successfully 11 Domain name could not be found 12 4 byte IP address could not be converted to dotted IP format RPG Prototype DICP_GetIPFromHost D Pr like TInt D IP_Addr 255 Value D READOTIP 15 Parameters 1 IP_Addr Fully qualified domain name 2 RtnDotIP Dotted IP address Example Usage RPG2SQL Integrator S Convert youri company com to ll dotted IP format Xi a E E SE E SE be E SE EE E Se E a EE ge Ge SE E SE SE E E E E e eg e Calp TCP_GetIPFromHost youri company com C RtnDotIP SQL_TCPTrace The SQL_TCPTrace API is used to enable or disable TCP IP logging to the RPGSQLLOG file Note TCP IP logging is off by default Returns 0 TCP IP logging was set successfully 2 Unknown error RPG Prototype DSQL_TCPTrace Pr Like T
26. DSQL MoveNext Pr Like TInt D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection xample Usage Seege OAS SS SSS SSS A SS SS SS SS SSS SS SSS AAA i Move to next record kee e re e e A A A A A A A A A A A A A A A E re E be re e e re e re GC Eval Rtn SQL MoveNext SQL Socket c Exit with Error Return TCP Server Conne e LE Rtn lt gt 0 C Exsr SExit e Endif SQL_MovePrev The SQL_MovePrev API is used to move to the previous record in the currently opened ADO recordset Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns 0 ADO SQL database operation completed successfully 1 ADO SQL database operation failed Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL MovePrev Pr Like TInt D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection Example Usage 29 RPG2SQL Integrator x Move to previous record AAA AAA A A A A A A E bb A A A A A A A A A A A A A be E A A o Q Eval Rtn SQL MovePrev SQL Socket CS Exit with Error Return TCP Server Conne C If Rtn lt gt 0 Exsr SExit d Endif SQL_GetField The SQL_GetField API is used to access the data values in any field from the current record in the currently opened ADO recordset Note SQL_RunSQLSel must be used to open
27. E E bb E re e be re e e re e re Get row and column count from worksheet S Extract the row and column counts and the used range AA A A A A A A a E E E E E a E E E A ee re e Eval Rtnrecord XLS RowColCount SQL Socket e Eval rows SQL GetFldNum RtnRecord 1 C Eval columns SQL GetFldNum RtnRecord 2 C Eval usedRange SOL GetFldChr RtnRecord e Feste AAA AAA A e e A A A A o S S Ball Excel Kee A A A A A a E E E re E E E a a E a a E ee Eval Rtn XLS_Quit SQL Socket kee e e e e e re be E be e be bb E be E E be E be e be E bb E bb E bb E bb be E E e be E re E be re e e re e re Disconnect from RPGSQL server AAA AA A A A A A o C calip SQL Disconnect SQL Socket XLS_SetDelimRec RPG2SQL Integrator The XLS_SetDelimRec API is used to send a delimited iSeries data record buffer to the active Excel worksheet and then parse the data into multiple cells based on the delimiter passed to the API Up to 256 values can be passed in the DelimRecord buffer with a maximum total length of 4000 The XLS_SetDelimRec API can be called multiple times as needed Returns 0 Cells were set successfully 1 Cells were not set successfully 2 Unknown error 3 Unknown communications failure 111 RPG2SQL Integrator Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DXLS_SetDelimRec Pr Like TInt
28. How to Use RPG2SQL Integrator Sample Code ooooncccccccconociconocanonoccnancnnnoncnn no nn nano c cnn rca nan nn nnnn nn nancccna 8 lee e Ee ai ita 8 Locating Source for Menu eme eae nn arrancaron rra 8 Required Items for Creating an RPG2SQL Application 0 ccccceceeeeeceeeeeeeeeeeeeseeeeeseeeesaeeneneeeeaes 9 Basic Structure of an RPG2SQL Integrator Appltcaton narra nanncnancccna 9 RPG2SQL Integrator Copy Member 9 Error e ule ln EE 9 Program Flow when Reading Selected Records from SQL table oooooooocccccccocccnccococonononononocnnnnnnconnnnos 10 Program Flow when inserting new records into SQL Gotle cnn 10 Starting iSeries RPG2SQL Integrator Server on PC 11 First Steps Running Sample Programs cccccccceeesceceeeeeceeeeeeaeeeeneeceeeeeeeaeeesaaeseeneeseaeeesnaeeseaeseneees 12 What s Next After Getting Started 0 0 eeeceeeeeeceeeeeeeeeeeeee eee ee arrancan carne rra nan 13 Main Men cir daba 13 Main Menta tata 13 Introduction to Main Men 13 CO cccceceeeceeeeeeeeeeeceeeeeceaeeeeaaeeeeeeeceaeeecaaesdeaaeseaeeeseaeeesaaeseeeeeseaeeesiaeeeeaeeeeaes 14 Available Options on the Main Men 14 RPG2SQL ILE RPG Service Program Procedures ooococcccccccconccononcnonccconcnconnn nano nn nana cn narra can rra rn 15 RPG2SQL ILE RPG Service Program Procedures c oconccccnccconociconocnnnncccnonccnnnnnnn arrancan 15 8 SQL NonBlockiNd NEE EE 15 e ET Wee ue cia aia 16 ek e ANE 17 lte NET 17 DOL CONNEC iii eege Hades
29. Integrator c 6 bill 60 To group by the vaules in the first column and make totals for columns 2 and 4 the example is E Eval Rtn XLS Command SQL Socket XLSSUBTOTAL 1 2 4 The result will be the like the following b Total Grand Total Parameters P1 Group by column P2 P11 Columns to be subtotaled Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPPRINTTITLEROWS The PAGESETUPPRINTTITLEROWS XLS_Command function is used to set the rows that contain the cells to be repeated at the top of each page in A1 format Example to set the first 3 rows C Eval Rtn XLS Command SQL Socket C PAGESETUPPRINTTITLEROWS 1 3 P1 Header rows in A1 format Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPPRINTTITLECOLUMNS 101 RPG2SQL Integrator The PAGESETUPPRINTTITLECOLUMNS XLS_Command function is used to set the columns that contain the cells to be repeated on the left side of each page in A1 format Example to set the first 3 columns e Eval Rtn XLS Command SQL Socket e PAGESETUPPRINTTITLECOLUMNS SA C P1 Header columns in A1 format Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPPRINTAREA
30. March 201 1 Table of Contents RPG2SQLINtegr ee 1 RPG2SQL tte EE 1 DA Ee 1 EE 1 Introduction to RPG2SQL Inteorator cesses eeeeeeseeeeeceaeeeeaeeseeeesaeeesaaeseeaeeseeeesaeeseaeeeeneeees 1 About RJS Software Systems Inc 2 Licensing le en le TEE EE 2 Contacting RUS Software Systems Inc 2 NEIE e IN EE 3 Installation si hie A A A EE 3 Introduction to Installation cccccccceeeeseeceeeeeceeeeeeeaeeeeaaeceeeeesaeeesaaeeeeaaeseeeeecaeeeeaaesdeaeeseeeescaeessaeeseneeees 3 AS 400 Prerequisites 0 ceccccecsceeeseeeeseeeeecceceeeeeeeaeeeeaaeseaaessaaeesaaeseaaessaaaesnaeeseaaesaaaeseeeeesaeseeaaesseaaenags 3 PG dE HEET 3 AS 400 Installation EE 4 AS 400 User Authorization Code Entry 0 cecccccecceeeseeeeeneeceeeeecaeeeeaaeeseeeeeeeeeesaaesseaaeseeeeeseeeesiaeeneeeeeaes 4 Installing iSeries RPG2SQL Integrator PC component cee cece eeeeee cece eect eeeeaeeeeaeeseaeeesaaeeesaaeseeeeeees 4 Deinstallation of iSeries RPGSQL Integorator ronca anna nan rca rana 5 Getting Started EE 5 Getting Started iii nin A edt i ied ee 5 Introduction to Getting Gtarted cece eeeeeeceeeeecaeeeeeaeseeaeeseeeeeseaeeesaaesdeaeeseaeeeeaeeseaeeeeeeeees 5 Installing the iSeries RPG2SQL Integrator Software eee eeeeaeeeeaeeseeeeetaeeesaeeeeneeee 6 Overview of RPGSQL Integrator AP 6 ADO ODBC Data Source Examples A 6 How to Use RPG2SQL Integrator Sample Code ooooocinoccconocococcccnonanononnnancccnancnn anna nana cnn rca ran rana nnnnccns 8
31. Rtn XLS Command SQL Socket C SETSELECTIONORIENTATION 90 P1 Text orientation option in degrees 90 to 90 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTEDCOLDELETE The SETSELECTEDCOLDELETE XLS_Command function is used to delete currently selected columns Example to delete all currently selected coumns e Eval Rtn XLS Command SQL Socket C SETSELECTEDCOLDELETE Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTEDROWDELETE The SETSELECTEDROWDELETE XLS_Command function is used to delete currently selected rows Example to delete all currently selected rows e Eval Rtn XLS Command SQL Socket C SETSELECTEDROWDELETE Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETINSERTPAGEBREAK The SETINSERTPAGEBREAK XLS_Command function is used to insert page break in the worksheet at current cell location Example to insert page break iG Eval Rtn XLS Command SQL Socket SETINSERTPAGEBREAK Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned 93 RPG2SQL Integrator SETSELECTEDBORDEROUTLINE The SETSELECTEDBORDEROU
32. SQL SQLEXEC RETURNING SQL RIN SQLMoveFirst This function is the COBOL version of the SQL_MoveFirst procedure CALL PROCEDURE SQLMOVEFIRST USING SQL SOCKET RETURNING SQL RIN SQLMoveLast This function is the COBOL version of the SQL_MoveLast procedure COBOL example usage 125 RPG2SQL Integrator CALL PROCEDURE SQOLMOVELAST USING SQL SOCKET RETURNING SQL RIN SQLMoveNext This function is the COBOL version of the SQL_MoveNext procedure CALL PROCEDURE SQLMOVENEXT USING SQL SOCKET RETURNING SQL RIN SQLMovePrev This function is the COBOL version of the SQL_MovePrev procedure CALL PROCEDURE SQLMOVEPREV USING SQL SOCKET RETURNING SQL RIN SQLGetField This function is the COBOL version of the SQL_GetField procedure SQLGetFldChr This function is the COBOL version of the SQL_GetFldChr procedure COBOL example usage 126 RPG2SQL Integrator Get value for selected field in recordset Return to 256 character work field MOVE lastname to SQL FLDNAME 4 amp CALL PROCEDURE SQLGETFLDCHR USING SQL RECORD SQL FLDNAME RETURNING WORKCHAR SQLGetFidNum This function is the COBOL version of the SQL_GetFldNum procedure SQLGetFldDat This function is the COBOL version of the SQL_GetFldDat procedure SQLMoveFirsBuf This function is the COBOL version of the SQL_MoveFirstBuf procedure SQLMoveLastBuf This function is the COBOL version of the SQL_MoveLastBuf procedure
33. SQLExtRecDDS for use 5 FidCount A pointer to a field where the DDS field count will be returned to This parameter is passed to SQLExtRecDDS for use Example Usage from RPG The sample code listed below retrieves the DDS layout for the QOUSTCDT file in library QIWS The resulting return values are placed into the FldDefArray FldPtrArray and FldDefCount return structures The results of this call can later be passed to the SQL_ExtRecDDS API 62 RPG2SQL Integrator S Get DDS Layout for selected AS 400 File or externally defined data structure Eval INFILE QCUSTCDT Eval INLIB QIWS Eval Rtn SQL ExtDDS INFILE INLIB SADDR FldDefArray SADDR FldPtrArray SADDR FldDefCount 000040400 The SQLExtRecDDS API is used to read a DDS defined file layout into memory for use with the SQLExtRecDDS function SQLExtRecDDS will parse the selected record buffer into multiple individual fields based on the DDS field definitions Return codes and output parameters offer a nice way for SQL Server stored procedures to communicate with a calling program As good programming practice make sure to first read the stored procedure return code and then retrieve all other return parameters as needed via the SQL_GetParm function Returns A 4096 character text buffer that contains the returned stored procedure value or return code lf the returned value was a numeric value you must convert the data into numeric format for use in y
34. SQLMoveNextBuf This function is the COBOL version of the SQL_MoveNextBuf procedure SQLMovePrevBuf 127 RPG2SQL Integrator This function is the COBOL version of the SQL_MovePrevBuf procedure SQLGetFldChrB This function is the COBOL version of the SQL_GetFldChrB procedure SQLGetFidNumB This function is the COBOL version of the SQL_GetFldNumB procedure SQLGetFldDatB This function is the COBOL version of the SQL_GetFldDatB procedure SQLGetStrRow This function is the COBOL version of the SQL_GetStrRow procedure SQLGetFldDat2B This function is the COBOL version of the SQL_GetFldDat2B procedure SQLGetDelimRcd This function is the COBOL version of the SQL_GetDelimRcd procedure 128 RPG2SQL Integrator SQLGetDelimFld This function is the COBOL version of the SQL_GetDelimFld procedure SQLGetDelimFIt This function is the COBOL version of the SQL_GetDelimFlt procedure SQLGetFidCount This function is the COBOL version of the SQL_GetFldCount procedure SQLPCFileEmpty This function is the COBOL version of the SQL_PCFileEmpty procedure SQLPCFileExist This function is the COBOL version of the SQL_PCFileExist procedure SQLPCFileRen This function is the COBOL version of the SQL_PCFileRen procedure SQLPCFileCopy This function is the COBOL version of the SQL_PCFileCopy procedure 129 RPG2SQL Integrator SQLPCFileDel This function is the COBOL version of the SQL_PCFileDel proced
35. Socket Like TInt Value D SPString 4096A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Variable length stored procedure execution string value of up to 4096 characters Parameter values are set via SQL_AddParm function Sample SQL Server stored procedure RJSSAMPLE1 This very simple stored procedure sets the stored procedure return code to 999 and sets the integer return value to 555 and the varchar value to This is a test Note The RISSAMPLE1 stored procedure must be created on your SQL Server You should create this sample stored procedure in the sample pubs database provided by Microsoft CREATE PROCEDURE rjssamplel Returnl Int output Return2 varchar 30 output as S95 This is a test set Returnl set Return2 return 999 Example Usage from RPG The sample code listed below creates the return parameters for the stored procedure call calls the RJSSAMPLE1 stored procedure and then retrieves the return parameter values 50 RPG2SQL Integrator Make sure stored procedure parameter list is empty Eval Rtn SQL ClearParms SQL Socket This parm will hold the stored procedure return code Must always be defined first if the SP return code will be used See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Return adInteger ParmReturnValue oe wy This parm will hold the stored procedure integer return f
36. a pivottable named PIVOT1 Eval Rtn XLS Command SQL Socket XLREFRESHPIVOTTABLE l PIVOT1 Parameters P1 Pivot table name Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned XLSETCELLDATA The XLSETCELLDATA XLS_Command function is used to set values in selected cells SCHER to set the values in cells A1 and D3 Eval Rtn XLS a bia Socket j XLSETCELLDATA A1 VALUE1 D3 VALUE2 Parameters 99 RPG2SQL Integrator P1 Cell location 1 P2 Value 1 through P37 Cell location 19 P38 Value 19 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned XLSETCHECKBOX The XLSETCHECKBOX XLS_Command function is used to set values in selected checkboxes Example to select checkbox 1 and clear checkbox 3 e Eval Rtn XLS Command SQL Socket XLSETCHECKBOX 1 1 3 0 Parameters P1 Checkbox 1 P2 Value 1 through P37 Checkbox 19 P38 Value 19 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned XLSSUBTOTAL The XLSSUBTOTAL XLS_Command function is used to apply auto subtotals Example to apply auto subtotals to the selection There must be a column heading row in the selection Here is an example of such a selection 100 RPG2SQL
37. a recordset before this API can be used Returns The field data value is always returned in text format The ILE programmer is required to convert any numeric values to the appropriate format in the ILE program by converting the text data to the appropriate format if required If an error occurs when retrieving a field the text value ERROR is returned Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed RPG Prototype DSQL_GetField Pr Like TString D Socket Like TInt Value D FieldName 256A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 FieldName Pass the field name for a field from the current recordset xample Usage VA a a A A EELER EELER EE EEN A EE EE E Get First Name field from current recordset An A HA i i i i lh it i yt Si ih i i i i leh tis iG E iC Eval RtnField SQL GetField SQL Socket a Fist SQL_GetFldChr 30 RPG2SQL Integrator The SQL_GetFieldChr API is used to a text field value from the most recent SQL_MoveFirst SQL_MoveLast SQL_MoveNext or SQL_MovePrev call This API has to go out to the RPG2SQL server to retrieve the field value Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns The field data value is returned in text format If an error occurs when retrieving a field the text value ERROR is returned Note You can use the SQL_Las
38. based file name GERVER SHARE TEST TXT Copy selected PC file TT e Eval RtnErr SQL PCFileCopy SQL Socket a Les eset tats a e testdest txt SQL_PCFileDel The SQL_PCFileDel API allows a selected PC file to be deleted on the server PC running the RPG2SQL Integrator server software Returns 0 operation completed normally Not 0 Errors occurred while deleting file Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL PCFileDel BY Like TInt D Socket Like TInt Value D PCFile Like TString Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 PCFile PC file name to delete Can be a standard file path name CATEST TXT or UNC based file name SERVER SHARE TEST TXT Example Usage 45 RPG2SQL Integrator SQL_LastErrNum The SQL_LastErrNum API returns the last SQL error number that occurred on the RPG2SQL server in text format Note SQL_LastErrNum SQL_LastErrNum2 SQL_LastErrMsg and SQL_LastFullErr can be called as desired to retrieveinformation about the last error that occurred on the server There is no rule as to which of these APIs must be used Returns Text string value containing last error number If an error occurs when retrieving the last error info the text value ERROR is returned RPG Prototype DSQL_ LastErrNum Pr Like TString D Socket Lik
39. blank rows DFT 142 RPG2SQL Integrator Import blank rows using default values Numeric fields are set to 0 All other fields are set to blanks Top Examples for SQLXLSIMP Note These examples assume you have added RISRPGSQL to your library list Otherwise you must qualify the command with the library RJSRPGSQL Example 1 Simple Command Example SQLXLSIMP INXLS C EXCEL FILES SAMPLE XLS OUTFILE RJSLIBRARY SAMPLEPF This command imports the active worksheet of workbook C Excel Files Sample xls to the first member of physical file SAMPLEPF in library RJSLIBRARY using the following default settings e Use the first member of SAMPLEPF e Replace existing data e There are no header rows e Log warnings and errors e Use the IP address of your 5250 session e Skip blank rows Example 2 More Complex Command Example SQLXLSIMP INXLS C EXCEL FILES SAMPLE XLS UTFILE RJSLIBRARY SAMPLEPF UTMBR QUARTER2 ADD I O O HDRROWS 3 L H B OGMSG YES OST CL 1 1 1 LKROW DFT This command imports the active worksheet of workbook C Excel Files Sample xls to member QUARTERZ2 of physical file SAMPLEPF in library RJSLIBRARY using the following settings e Use member QUARTER2 e Add new records after any existing records e There are three header rows e Log warnings and errors e Use IP address 1 1 1 1 e Use default values for blank rows
40. code Top Enter data area name DTAARA Specifies the data area that contains the security code RJS Software provides this information with the license key This is a required parameter character value Specify the name of the data area Top Enter data area library name DTALIB Specifies the data area library RJS Software provides this information with the license key This is a required parameter character value Specify the data area library name Top Example for PRDSEC Note This example assumes you have added RUSRPGSAQL to your library list Otherwise you must qualify the command with the library RJSRPGSQL PRDSEC SECURITY XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX DTAARA RJSRPGSQL DTALIB RJSRPGSQL This command enters a 50 character security access code in the data area RJSRPGSQL in the data area library RJSRPGSQL Top Error messages for PRDSEC ESCAPE Messages CPF9898 AT Top 135 RPG2SQL Integrator Test ADO Connection String RSQCONN Where allowed to run All environments ALL Parameters Threadsafe No Examples Error messages The Test ADO Connection String RSQCONN command tests connection strings tests if the server is running and tests if you can connect successfully A successful connection displays a message that tells you the database connection was successful If the connection fails you will recieve an error message This command is an example of how to use our error
41. eg dest Eegen feast agandaeds ais 119 Problem Hand W E 121 PrODIGM A EO EEEE EEA Ea TEE R N 121 GOMMONEMONRS EE 121 OMS Ae E EEEE A E a a aia 121 Appendix A RPG2SQL ILE COBOL Service Program Procedures c ccccceceeseeeeeceeteteeeeteeeeneeeeaes 121 Appendix A RPG2SQL ILE COBOL Service Program Procedures cceceeeeseeceeeeeseeeeeneeeeenees 121 COBOL Working Storage Variables cccccceeececeeeeeeneeeeeeeecaeeeeaaeeeeneeseeeeeseaeseeaaeseeeeeseaeeesaeeneaeeee 122 SOLCOMeC AEA ETE E A dee 122 el RIETS 122 SQL Dee eegene deene EE EE 123 SOLED BOPSMC eege gees 123 SQLDBCIOSS COMM EE 123 SGEBOGINT TAN iy catenin Alia ava atlas Se aN i nan nae Mea ae 124 SOL COMMIT aN a A ts ee E ide A 124 SQEROMD ACK teen A ee Gadde eaten sad eee Eet 124 SQERUNS GES eiii A a a Dee 124 SQLRUNSQLEXEC i etic I ava A a ie a wa aie ORT ala he Rite A ES cds 125 AA A ee eN 125 SQUMOVe AS diia Dit E A a E EE ARES A AE EA aer 125 SOEMOVEN eii e A A E EE AE A AEREA 126 SQUEMOVEP Vinil EA a E ALE A EEE a tudes E a SEA ab 126 SOLE El a EE ae 126 SOLO A EA A Ea 126 ele ONEI EE EEN 127 Eelst ebe tence SEET ege Lee a AE abia 127 elle le EE 127 OR oN ET 127 el Be Al GE 127 SQEMOVEPreV BUE idiota abatir dada iia sad A aan Ae e Age ies dee 127 SQEGetRld Chr E 128 SQLEGEtFIGNUMB E 128 SQLGetFId DatB sti tess ege cheese teed Legend Seed ee ees 128 SOLGetStROW EE 128 SOLGetEldDat2B ct asia 128 Sa EE 128 SOLEGESitDe lim Elsen dee 129 SOUG SIDS lin
42. following individual steps Make sure the RJSRPGSAQL library is not being used Delete the RJSRPGSQL library from the AS 400 by entering the following command DLTLIB LIB RUSRPGSQL Removing iSeries RPG2SQL Integrator from a PC Remove the C Program Files RPGSQLSV directory from the PC Getting Started Getting Started Introduction to Getting Started The purpose of this section is to describe how to quickly get started using the iSeries RPG2SQL Integrator to start developing RPG and COBOL programs that can access SQL Server and other ADO Microsoft Active Data Objects or ODBC databases RPG2SQL Integrator Installing the iSeries RPG2SQL Integrator Software If you haven t installed the software yet go back to the installation section and install the software before continuing If you re just reading ahead to find out what to do then please continue Overview of RPGSQL Integrator API The iSeries RPG2SQL Integrator is a native OS 400 API product used to allow ILE RPG ILE Cobol and programmers to directly access any network attached database such as Oracle Microsoft SQL Server Access dBase Foxpro Excel CSV MySQL and more right from an ILE RPG or ILE Cobol program Note While RPG2SQL Integrator works with products like Oracle Microsoft SQL Server Access dBase Foxpro Excel CSV MySQL and more RPG2SQL Integrator does not provide the ODBC drivers or OLE DB providers for these products Contact the database vendors dire
43. found in source file SOURCE in library RISRPGSOL SQLExtDDS The SQLExtDDS API is used to read a DDS defined file layout into memory for use with the SQLExtRecDDS function SQLExtRecDDS will parse the selected record buffer into multiple individual fields based on the DDS field definitions Returns 0 Completed successfully 9999 Unknown error Nonzero Some other error RPG Prototype DSQOLEXtDDS PR 102 0 D DDSFile 4096A Value D DDSLib Value D FldDefs Value D FldPtrs ZS Value D FldCount a Value Parameters 1 DDSFile The name of the AS 400 file layout to retrieve The DDS layout can be used to retrieve a DDS layout for use as an externally defined data structure or to format data for writing to a file 2 DDSLib The name of the AS 400 library for the file layout to retrieve 3 FldDefs A pointer to an array that will hold the field definitions after SQLExtDDS has been called successfully This parameter is passed to SQLExtRecDDS for use 4 FidPtrs A pointer to a field pointer array that will hold the field definition pointers after SQLExtDDS has been called successfully This parameter is passed to SQLExtRecDDS for use 5 FidCount A pointer to a field where the DDS field count will be returned to This parameter is passed to SQLExtRecDDS for use Example Usage from RPG 61 RPG2SQL Integrator The sample code listed below retrieves the DDS layout for the QOUSTCDT file in library QIWS The re
44. ge dnde altares 48 SQL RUNS Primos os Steichen a lr iaa sites Leger ode 49 SOL RunSPSelect scsi Gerke conc ai adela ale dh 51 SOL ClearParms EE 54 SOL io o ate i a triede it dle ta ao 54 ele ies a dada ita ai 58 A EE 60 RPG2SQL ILE DDS Service Program Procedures occococcccccccconccononanonococancccnnnno nono naar cnn canaria rra 60 RPG2SQL ILE DDS Service Program Procedures cc ccccecceeeeeeeeeeceeeeeeaeeesaaeseeeeeseaeeeseaeeseeeeeeeees 60 Introduction to DDS Handler Service Program 60 MS IA ENEE 61 El Se RRE 62 RPG2Excel ILE RPG Service Program Procedures occccccccccconicononcnonoccconcnnonnnno nono nann nn nn cn narran ranma cnn 65 RPG2Excel ILE RPG Service Program Procedures oooccccncccononiconocnnonoccnoncnono nana nn nn nan cc ran nnnncnnnnc 65 RPG2EXxcel Introduccion tic vies ae eta ate So E ac EES 65 MES PAU EE 65 LES AVIS IIS ee EE ee Ee EE Eege 66 DRL OU ee dl deng 67 XLS COMMANC O 68 XLS Commande 68 Table of Contents GLEARRANGEAEL meter getest o Late chee ein ae d GE 69 FILE CLOSE EE 69 le Ee EE 69 FILENEW E Eeer ee iron EE 70 FILEOREN E 70 FEE SAM EBs ccs Se sachpesee se nos Sapp ne econ A epea ta ody daa tia 70 FILESAVEAS RRE ENEE rola 70 A NEE geed Eegen BNG Et EE A 72 FIFEPRINT PREVIEW cocinar aaa 73 SETRANGE ROW TEE 73 SETAC TINE CEL EE 73 SETAC TIVECELL ROW GOEL icon aida 74 SETAGTIVECERE MAL HUE ou aa a rai 74 SETAGTIVECELEFROMEIEE cion 74 SETACTIVEGELLFORMULA ini iia 75 SETACTIVECELLFORMULARI
45. handling functions Restrictions e You must have RISRPGSQL added to your library list or qualify the command with the library name e This command is not threadsafe Top Parameters HOST RPG2SQL Server IP Character value Required Address Positional 1 ADODSN ADO Data Source Character value Optional String Login Driver Server Database UID PWD Positional 2 Top RPG2SQL Server IP Address HOST Specifies the IP address or host name where the RPG2SQL Integrator PC component is running This is a required parameter character value Specify the IP address or host name Top ADO Data Source String Login ADODSN Specifies the OLE DB ODBC connection string that you want to test Driver Server Database UID PWD This entry is a skeleton or a template of an SQL Server ODBC connection string character value Specify the connection string Note As of the printing of this manual two websites where you can find information on building connection strings for many database management systems are e http Awww connectionstrings com e http www carlprothman net then click connection strings 136 RPG2SQL Integrator Top Examples for RSQCONN Note These examples assume you have added RISRPGSQL to your library list Otherwise you must qualify the command with the library RJSRPGSQL Example 1 Connecting to Microsoft Access RSOCONN HOST 125 1 2 49 ADODSN Driver Microsoft Access Driver mdb Dbgq c program
46. returned RPG Prototype DSQL LastErrMsg Pr Like TString D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection SQL _LastFullErr The SQL_LastFullErr API returns a combination of the last SQL error message number and text from the RPG2SQL server Note SQL_LastErrNum SQL_LastErrNum2 SQL_LastErrMsg and SQL_LastFullErr can be called as desired to retrieveinformation about the last error that occurred on the server There is no rule as to which of these APIs must be used Returns Text string value containing last error number If an error occurs when retrieving the last error info the text value ERROR is returned RPG Prototype 47 RPG2SQL Integrator DSQL LastFullErr Pr Like TString D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection SQL_Quote The SQL_Quote API accepts a string as an input value If single quotes are found in the field value they are padded with an additional single quote This will allow the data value to get passed correctly to an SQL INSERT or UPDATE statement without needing to perform additional special editing on the string to check for single quotes in the data string The function returns the newly quoted string value Returns A string value where single quotes are padded with an additional single quote Example The People s Republic will be returned as The People s Republic after being passed into the
47. returns a CPF9897 message if the IFS file is found Restrictions e You must have RJSRPGSQL added to your library list or qualify the command with the library name e This command is not threadsafe Top Parameters FILNAM IFS file name Character value Required Positional 1 Top IFS file name FILNAM Specifies the IFS file or directory whose existence you want to check This is a required parameter character value Specify the name of the IFS file path This path can be absolute or relative Top Example for CHKOBJIFS Note This example assumes you have added RISRPGSQL to your library list Otherwise you must qualify the command with the library RJSRPGSQL CHKOBJIFS FILNAM RISRPGSOL INVOICE PDF 132 RPG2SQL Integrator This command checks for the existence of the file RUSRPGSQL INVOICE PDF Top Error messages for CHKOBJIFS ESCAPE Messages CPF9898 AT Top Display OS 400 Level PRDINFO Where allowed to run All environments ALL Parameters Threadsafe No Examples Error messages The Display OS 400 Level PRDINFO command displays the following information about your System i e The operating system level e The serial number e The model number e The logical partition LPAR number Restrictions e You must have RJSRPGSQL added to your library list or qualify the command with the library name e This command is not threadsafe There are no parameters for this command Top Parameters None
48. stored procedure to return recordset Eval Rtn SQL RunSPSelect SQL Socket rjssample2 Retrieve First Record from Recordset For this test we will only read one record from the authors table in the pubs database Eval RtnRecord SQL MoveFirsBuf SQL Socket Close recordset if we re done reading from the recordset The return parameters are available for reading only after the recordset is closed This is a ADO command object limitation not a RPG2SQL limitation Eval Rtn SQL DBCloseRS SQL Socket Retrieve SP Return Value to return record SP Return Value is always converted to string Eval RtnParm SQL GetParm SQL Socket Return Retrieve Integer Return Value to return record SP Return Value is always converted to string Eval RtnParml SQL GetParm SQL Socket Returnl Retrieve VarChar Return Value to return record SP Return Value is always converted to string Eval RtnParm2 SQL GetParm SQL Socket Return2 53 RPG2SQL Integrator SQL_ClearParms The SQL_ClearParms API will reset the stored procedure parameter list After clearing the parameter list the SQL_AddParm function must be called to create a parameter list before a stored procedure can be called with parameters As good practice SQL_ClearParms should be called prior to each stored procedure call to reset any input or return parameters that still exist from previous stored procedure calls After
49. to enable text wrapping 91 RPG2SQL Integrator E Eval Rtn XLS Command SQL Socket E SETSELECTIONWRAPTEXT 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONSHRINKTOFIT The SETSELECTIONSHRINKTOFIT XLS_Command function is used to set shrink to fit option for cell range selection E UP to disable shrink to fit option Eval Rtn XLS Command SQL Socket gt SETSELECTIONSHRINKTOFIT 0 SCHER to enable shrink to fit option Eval Rtn XLS Command SQL Socket e SETSELECTIONSHRINKTOFIT 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONMERGECELLS The SETSELECTIONMERGECELLS XLS_Command function is used to set merge cells option for cell range selection SCHER to not merge cells Eval Rtn XLS Command SQL Socket E SETSELECTIONMERGECELLS 0 ampie to merge cells Eval Rtn XLS Command SQL Socket d SETSELECTIONMERGECELLS 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONORIENTATION The SETSELECTIONORIENTATION XLS_Command function is used to set text orientation for cell range selection 90 to 90 degrees Example to set text rotation to 90 degrees 92 RPG2SQL Integrator e Eval
50. with the library RJSRPGSQL RSQIP DEVD This command returns the 15 character IP address for the current 5250 session Top Error messages for RSQIP ESCAPE Messages CPF9898 AT Top Ping Remote Host SQLPING 138 RPG2SQL Integrator Where allowed to run All environments ALL Parameters Threadsafe No Examples Error messages The Ping Remote Host SQLPING command tests the connectivity between the System i and the remote system specified by the Remote System RMTSYS parameter Restrictions e You must have RISRPGSQL added to your library list or qualify the command with the library name e This command is not threadsafe Top Parameters RMTSYS Remote System Character value INTNETADR Required Positional 1 INTNETADR Remote internet Character value Optional address Positional 2 Top Remote System RMTSYS Specifies the remote system name of the host with which the PING operation takes place To be successful the name must be valid and the remote system must be able to communicate with the local system You can assign names to an internet address by using either of the following e Work with Host Table menu which is an option on the Configure TCP IP CFGTCP menu e Remote name server to map a remote system name to an internet address This is a required parameter INTNETADR The remote system is identified by the value specified for the Remote internet address INTNETADR parameter character value Sp
51. 00 file CUSTOMER Note The difference between this option and option 20 is that this sample program illustrates the use of committment control and SQL Server transactions Option 22 Read All Records from NameAddr Table and Display This menu option reads all records from the NameAddr table in the pubs database on the selected SQL Server database server writes the data to a temporary AS 400 file and displays all the record data RPG2SQL ILE RPG Service Program Procedures RPG2SQL ILE RPG Service Program Procedures 8 SQL_NonBlocking The SQL_NonBlocking API is used to set socket connections to nonblocking You can use this API to eliminate time waits on the Series Turning on nonblocking causes connections to the RPG2SQL Integrator server to timeout after a specified number of seconds when the socket is unusable for any reason Functions that return a numeric value will return unknown communications failure 3 Functions that return a buffer will return ERROR Note The SQL_NonBlocking API must be called before the SQL_Connect API if nonblocking is desired 15 RPG2SQL Integrator Note Blocking connections are the default Note You will not be able to use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why an API call failed due to timeout Returns 0 Blocking nonblocking was set successfully 2 Unknown error RPG Prototype DSQL NonBlocking Pr Like TInt D Onoff Like TInt Value
52. 143 RPG2SQL Integrator Error messages for SQLXLSIMP ESCAPE Messages RPS9995 Warnings issued while importing worksheet amp 1 amp 2 DSPJOB amp 5 amp 4 amp 3 for diagnostic messages RPS9999 Error occured while importing worksheet amp 1 amp 2 DSPJOB amp 5 amp 4 amp 3 for diagnostic messages Top Export PF to Worksheet SQLXLSEXP Note As of the printing of this guide this command SQLXLSEXP is under development It is not available for RPG2SQL users Where allowed to run All environments ALL Parameters Threadsafe No Examples Error messages The Export PF to Worksheet SQLXLSEXP command exports a physical file member to a Microsoft Excel worksheet Restrictions e You must have RISRPGSQL added to your library list or qualify the command with the library name e This command is not threadsafe Top Parameters INFILE From file Qualified object name Required 8 Positional 1 Qualifier 1 From file Name Qualifier 2 Library Name LIBL CURLIB INMBR From member Name FIRST Optional Positional 3 OUTXLS To workbook Path name Required Positional 2 OUTSHEET To worksheet Character value ACTIVE Optional Positional 4 HEADER Header COLHDG TEXT NAME NONE Optional Positional 5 LOGMSG Log messages YES NO Optional Positional 6 HOST RPG2SQL Integrator Character value CURRENT Optional host name Positional 7 144 RPG2SQL Integrator RCDFIRST First record to export Integer FIRS
53. ATREADE NEE 103 PAGESETUPLEFTEOOTER EE 103 PAGESETUPGENTERFOOTER astucia relataba asias 103 PAGESETUPRIGHATEOO TE NEE 104 PAGESETUPLEETMARGIN turcas ctricos 104 PAGESETWPRIGHTEMARGIN E 104 PAGESETUPTOPMARGIN EE 105 PAGESETUPBOTTOMMARGIN coccocccccccoccconcnononanonononnncccnnnn anar crac 105 PAGESETUPHEADERMARGI anna nc 105 DAGEGETUPBFOOTERMAROIN cnn rca 105 PAGESETUPPRINTHEADINGS irena ainai nan ncnnnnccrn cnn 106 PAGESETUPPRINTGRIDLINES cooooccccinccococccononcnononcnoncccnoncnn arca cc ronca rr 106 PAGESETUPPRINTCOMMENTS cocccccccccconcnonononononnnnncncnnnn cnn nan nc 106 PAGESETUPPRINTQUALITA 0 oia rr aie 107 PAGESETUPCENTERHORIZONTALL cccccceseseceeeeceeeeeseaeeeeaeeseeeeecaaeeesaaesseaeeseaeeesaeessaaeenenees 107 PAGESETUPCENTERVERTICALLY cnicccinnccononcnonncnoncncnoncnn anna nnnn cnn nc 107 PAGESETUPORIENTATON oca ege EE ASSOSS Ze 108 PAGESETURDRAF T vwersstevecascaideseaiecsetii Eege EES EES 108 PAGESETUPPAPERSIZE ek eege leede dee eeh ee EE 108 PAGESETUPFIRSTPAGENUMBER o ccoocccccocccoconcnononcnoncncnonnnn nn nn nano cc rn nrn narran rra 109 PAGESE TUP OR DE EE 109 Table of Contents PAGESETUPBLACKANDWHIT E aae aeaee airar aaa iE a a aa Te ae riae Eaa ainia aA 109 PAGESETUPRZOO Mini Kate EEA Ee e Ae de 110 XES ROWGOIC OUN iento ie Ee a sa dee Eed ER 110 ESO a An 111 RE ue 113 ALS GEtDelimRSc s 2e e fe ege A aves sweets Eed Eege ees EEN AE 115 ALSEGEtDSlIM RO ee ee ee er ee t Esseg eg N 117 XES RUNSCIIPE
54. C Server is running before attempting to run any of these menu options Otherwise the samples will not work correctly Type GO RJSRPGSQL and press Enter to get to the main menu Listed below are the sample program menu options and a description of what will happen when the option is run Option 10 Work with Sample Source Code This menu option is used to display all the included sample programs in the SOURCE source file in the RJSRPGSAL library Note This option assumes that you have the IBM Application Development software tools on your AS 400 system and are familiar with using them Option 11 Test ADO ODBC Database Connection SQLTEST02C This menu option is used to see if the RPG2SQL Integrator PC Server is running Option 12 Write records to Access database IRPGSQL MDB This menu option deletes all existing records in the NameAddress MS Access table in the C Program Files RPGSQLSV IRPGSQL MDB access database and then inserts ten duplicate records Option 13 Read all records from Access Database IRPGSQL MDB RPG2SQL Integrator This menu option reads all records from the NameAddress Access Table in file C Program Files RPGSQLSV IRPGSQL MDB Option 14 Read all records from Sample Excel Workbook NameAddr XLS This menu option reads all records from the NameAddress workbook in file C Program Files RPGSQLSV NAMEADDR XLS Option 15 Read all records from Sample dBase Table NameAddr DBF This menu option reads all record
55. CA cnminccocononinonnnannnnnnnnn nani 75 SETACTIVECGELLFONTNAMEs 0 comia a A cones daunteasivedes 75 SETACTIVEGELEFONTSIZE cion A a 76 SETAGIIVECELLEONT BOLD consi ar ee 76 SETACTIVECELLFONTITALIC cnccinconinncnonononinonnnnrn narran rre 76 SETACTIVECELLFOREGOLOR cucuta ata 77 GE TACTIWECELLBACKCOL OR 77 SETACTIVECELLMERGEANDCEN TER cocococococccncconcnnnnnonanonnn cnn nonnncnnncnnnrnnrnrorrorrrrrr 78 SETACTIVECELLFONTSTRIKETHROUGH coccccicccnnccnncnonaconnncnnn nono nonn corno rro nono rr 78 GE TACTIWECELLFONTGUPDERGCHIDT cana cc arcano rro 79 SETACTIVECELLFONTSUBSCRIPT 00 narco cri 79 SETACTIVECELLFONTSHADOW A 79 SETACTIVECELLFONTOUTLINEFONT ooccccccccccnnccnnnnonnonanonnn cano ronnnonrnrnnnrnrrrrrrrrrrr 80 SETACTIVECELLFONTUNDERLINE oococncccocccoccnnccnnccnnnnnnnno rancio ancora nc nn cnn rro rro 80 SETACTIVECELLS TY LE iia A i g idi i edidi ts 80 SETACTIVECELLNUMBERFORMAT 0 eeceeeeeeeeeeeeeeeeeeeeeeeeaeesaeesaaecaaecaaeseaeseaeeeaesseeseeeseeeseneeegs 81 SETAUTOFITAL Lurton tirar selec lara lord irritada 81 SETRANGEALE EE 81 SETRANGEFORMULA coito toser eta pia 82 SETRANGESELEC Divisional taa ln lr tirada 82 SETRANGEVALUE shrann aei reeta ae et Sra eta pia 82 SETCOLSELECTA Turina adela do aprieta tia 83 SETROWSELECTA diciones tdo iia 83 SETCOLSELECGTROWGCOL muito lidad tia 83 SETROWSELEGCTROWGOL sintieran arce li da bid 84 SETSCREENUPDATANG cional lino pira 84 SETSELECTIONEONTSIZE cuina tddi a DEEE aea a de 85 SETSELECTIO
56. G2SQL Integrator Eval Rtn XLS Command SQL Socket WORKSHEETADD Example to insert a new worksheet and name it TEST1 a Eval Rtn XLS Command SQL Socket WORKSHEETADD TEST1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned WORKSHEETADDOLEOBJECT The WORKSHEETADDOLEOBJECT XLS_Command function is used to insert embedded OLE documents into the selected worksheet based on a saved file name Example to insert a Word document into the selected worksheet No special coordinates are used for sizing so they are all set to zero C Eval Rtn XLS Command SQL Socket C WORKSHEETADDOLEOBJECT C TEST DOC 0 0 0 0 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned WORKSHEETDELETE The WORKSHEETDELETE XLS_Command function is used to delete a worksheet by name Example to delete a worksheet named TEST1 e Eval Rtn XLS Command SQL Socket e WORKSHEETDELETE TEST1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned WORKSHEETCOPY The WORKSHEETCOPY XLS_Command function is used to copy the active worksheet You can specify its position relative to another worksheet or the beginning or end of the worksheets Example to copy active worksheet to t
57. G2SQL Integrator applications is member RPGSQLH in file RJSRPGSQL SOURCE This copy member contains e Prototypes for all the procedures in RPG2SQL Integrator e Several data types that you can use in your programs including TChar for a 1 character variable or TString for a 256 character variable or Tint for a 4 byte integer 101 0 e Several stored procedure and field type constants e Several Excel specific constants Error Handling Error handling is kept simple in all the examples in order to keep the examples clean For a basic example of error handling see source member RSQCONNR which shows how to use the SQL_LastFullErr procedure RPG2SQL Integrator Program Flow when Reading Selected Records from SQL table The following pseudocode shows how easy it is to write an RPG program to read all records from any PC database file SQL_Connect Connect to RPG2SQL PC server component SQL_DBOpenConn Open connection to selected ADO data source SQL_RunSQLSel Run a SQL record selection statement to create a recordset containing the selected records SQL_MoveFirstBuf Move to first record in file Begin of Loop Do until EOF Loop through all records and retrieve all required fields from current record Extract fields from record using the following Use SQL_GetFldCharB SQL_GetFldNumB or SQL_GetFldDatB function calls to retrieve selected field values Write extracted fields to AS 400 table Write Record SQL
58. GEFORMULA The SETRANGEFORMULA XLS_Command function is used to set the formula for the selected cell in A1 format Example to set cell formula to summarize cells A2 to A10 Eval Rtn XLS Command SQL Socket E e SETRANGEFORMULA SUM A2 A10 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETRANGESELECT The SETRANGESELECT XLS_Command function is used to select a cell range in the active worksheet Example to select cell A5 e Eval Rtn XLS Command SQL Socket e SETRANGESELECT A5 Example to select cells A1 B10 C Eval Rtn XLS Command SQL Socket e SETRANGESELECT A1 B10 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETRANGEVALUE 82 RPG2SQL Integrator The SETRANGEVALUE XLS_Command function is used to set the data value for an individual cell selection or range Example to set text data into the active cell or range e Eval Rtn XLS Command SQL Socket C SETRANGEVALUE This is a test Example to set numeric data into the active cell or range e Eval Rtn XLS Command SQL Socket C SETRANGEVALUE 200 00 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETCOLSELECTA1 The SETCOLSELECTA1 XLS_Com
59. I call failed if an error is returned SETACTIVECELLFONTUNDERLINE The SETACTIVECELLFONTUNDERLINE XLS_Command function is used to set the font underline value for the active cell Example to disable font underlining C Eval Rtn XLS Command SQL Socket Cc SETACTIVECELLFONTUNDERLINE 0 Example to set font underlining to have a double underline C Eval Rtn XLS Command SQL Socket C SETACTIVECELLFONTUNDERLINE 4119 Parameters P1 Numeric Font underline style Single Underline 2 Double Underline 4119 Single Accounting 4 Double Accounting 5 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLSTYLE The SETACTIVECELLSTYLE XLS_Command function is used to set selection style formatting information for the active cell A style contains a predefined list of formatting attributes See the Excel documentation or the Format Style menu for more detail on defining styles 80 RPG2SQL Integrator Example to set the style to Normal for the active cell Cc Eval Rtn XLS Command SQL Socket E SETACTIVECELLSTYLE Normal P1 Style name See Excel docs for definition Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLNUMBERFORMAT The SETACTIVECELLNUMBERFORMAT XLS_Command function is used to set numeri
60. Int D Onoff Like TInt Value Parameters 1 OnOff Set TCP IP logging O disable logging 1 enable logging C CallP SOL TCPIrace 1 SQL_TCPMultBuff The SQL_TCPMultBuff API is used to enable or disable TCP IP multiple buffering You can use this API to overcome packet truncation issues When disabled the socket will be read 3 times When enabled the socket will be read until an end of data marker is received Note TCP IP multiple buffering is off by default It is strongly recommended that you always enable multiple buffering Note This requires at least V1 0 42 of the RPG2SQL Integrator server software Returns 0 TCP IP multiple buffering was set successfully 2 Unknown error RPG Prototype 17 RPG2SQL Integrator DSQL TCPMultBuff Pr Like TInt D OnQEt Like TInt Value Parameters 1 OnOff Set TCP IP multiple buffering O disable multiple buffering 1 enable multiple buffering C Callp SQL TCPMultBuff 1 SQL_Connect The SQL_Connect API is the first procedure used in an ILE program that will be accessing SQL data from a remote ODBC ADO database The SQL_Connect API connects to the RPG2SQL Integrator PC Server component via TCP IP sockets The RPG2SQL Integrator uses TCP IP port 22001 to communicate with the PC server component If the SQL_Connect API completes successfully a socket ID number is returned from the PC server for the current connection This socket ID is used until a SQL_Disconnec
61. LS Command SQL Stee Cc SETSELECTEDBORDERNONE 94 RPG2SQL Integrator Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned DISPLAYALERTS The DISPLAYALERTS XLS_Command function is used to set the display Excel alerts option Alerts are messages that pop up if errors occur or if more information is required on a file open or save option etc It is a good idea to disable alerts when creating workbooks on a server PC Example to disable alerts e Eval Rtn XLS_Command SQL Socket DISPLAYALERTS 0 Example to enable alerts C Eval Rtn XLS Command SQL Socket DISPLAYALERTS 1 W P1 Alert option O no excel alerts 1 display excel alerts Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned WORKSHEETACTIVATE The WORKSHEETACTIVATE XLS_Command function is used to activate a worksheet by name Example to activate a worksheet named TEST1 e Eval Rtn XLS Command SQL Socket C WORKSHEETACTIVATE TEST1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned WORKSHEETADD The WORKSHEETADD XLS_Command function is used to add a new empty worksheet and optionally name the worksheet Example to insert a new worksheet without a special name 95 RP
62. L_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned 104 RPG2SQL Integrator PAGESETUPTOPMARGIN The PAGESETUPTOPMARGIN XLS_Command function is used to set the top margin in inches Example to set the top the margin to 1 5 inches e Eval Rtn XLS Command SQL Socket e PAGESETUPTOPMARGIN 1 5 P1 Size of the top margin in inches Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPBOTTOMMARGIN The PAGESETUPBOTTOMMARGIN XLS_Command function is used to set the bottom margin in inches Example to set the bottom the margin to 1 5 inches C Eval Rtn XLS Command SQL Socket c PAGESETUPBOTTOMMARGIN 1 5 P1 Size of the bottom margin in inches Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPHEADERMARGIN The PAGESETUPHEADERMARGIN XLS_Command function is used to set the header margin in inches Example to set the header the margin to 75 inches di Eval Rtn XLS Command SQL Socket C PAGESETUPHEADERMARGIN 75 P1 Size of the header margin in inches Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPFOOTERMARGIN The PAGESETUPFOOTERMARGIN XLS_Comma
63. NFONTNAME AAA 85 SETSELECTIONFONTSUPERSCRIPT conccinccncccncccnncnnncnnnanonnn oran conan nro rre 85 GE TGEIL ECTIONFONTGTRIKETHROUIOGH conocio nn norris 86 SETSELECTIONFONTSUBSGRIPT 00 cee ceeceeeeee eee eeeeeseeeeeeeeeaeecaeecaaecaaecaaeseaeseaeseaesseeeseeeseeeseneeaas 86 GE TGEIL ECTIONFONTOUTUINEFONT ccc eeeeeeceeeeeeeeeeeeeaeesaeesaaecaaecaaeseaeseaeseeseeeeseeeseeeseneesas 86 SETSELECTIONFONTSHADOW A 87 SETSELECTIONFONT BOLS aaa aeaa Tei a e rA aa TAa A Aa a Ad 87 SETSELECTIONFONTITALIC icii eeeeeeseceneeeneeeeeeeeeeeeeeseeeseaeeeaeecaeesaaesaaecaaeeaeseaeeeaeseaeeseeeseeeeeneeeas 87 SETSELECTIONFONTUNDERLINE 0 0 00 eee eeeeee eee eeeeeeeeeeeeeeeaeesaeesaeecaaecaeseaeseaeeeaeseeeseeeseneeeneesas 88 SETSELECTIONBACKCOLOR 1 ceececcese eens cene eter eter rr ivks radieri aieka ractania adii aidat 88 SETSELECTIONFORECOLOR 6 ois scceccccscesnetcnstevecessceisieessevedesdansedicedetesedieasutocehessnutenseeddehsesestesantiene 89 SETSELECTIONMERGEANDCENTER cococococccoccnncconcnonnnonnonnn cnn corn craneo rr 89 HR RECETTE 90 SETSELECTIONNUMBERFORMA T A 90 vil RPG2SQL Integrator viii SETSELECTIONHORIZALION coo t 90 SETSELEGTIONVERTALION uso ii rias 91 SETSELECTIONWRAPTE ius iii dE Ee 91 SETSELECTIONSHRIN KTO co rnncn narrar 92 SETSELECTIONMERGECELLS concccioccconoccnoncnononcnnnnnnnnnccrnn cnn rn 92 SETSELECTIONORIENTATION concccococccononccnnnccnnnnnnnnnnnnnn cnn ncn nn rra 92 SETSELECTEDGOLDE RE TE coin ii dret 93 SETSELECTEDROWDELET
64. P Address 192 168 100 102 Connections End Server iSeries RPG2SQL Integrator Main Window The server automatically starts listening on TCP IP port 22001 so you can simply minimize the main window and the program will hide in the system tray a 11 RPG2SQL Integrator The PC is now ready to receive SQL database requests The iSeries RPG2SQL Integrator program only needs to be started once It will remain running until the PC is shut down or the program is closed Note Only run the program in Debug Mode during initial testing or troubleshooting Debug Mode greatly impacts performance Logging Activity To capture all activity in a log file go to File Settings and check the Enable Logging check box Then you must exit RPG2SQL Integrator and restart it Note You can limit the size of the log file using the Maximum Log File Size Bytes text box Sending Debug info to RJS for Analysis If you re having problems with a particular SQL query you can use the File Export Debug Info to Clipboard menu option to send your debug log to RJS for analysis Ending the RPG2SQL Integrator PC Server program To end the program simply bring up the main window and click the End button First Steps Running Sample Programs Each of the included program samples are compiled and ready to run from the main menu Try out these samples to quickly see how the iSeries RPG2SQL Integrator works Note Make sure the RPG2SQL Integrator P
65. QL Integrator PC Server component Once Excel has been launched on the PC Server several Excel automation operations can be run including custom user defined VBScript code scripts if special functionality is needed If the XLS_Launch API completes successfully a return code of 0 is returned from the PC server 65 RPG2SQL Integrator Returns 0 Excel launch was successful 1 Excel launch was not successful Make sure Microsoft Excel is loaded on the the RPG2SQL Integrator server 2 Unknown error 3 Unknown communications error Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DXLS_ Launch Pr Like Tint D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection xample Usage aa aa ee se e ss E E a as a a ss a a SES ss ss i i ss a a ms a se as SE e SE a Connect to RPG SQL Server and Launch Excel AAA AA A A A E E be E E be E re E be re e e re e re e Eval SOL Socket SUL Connect 10 1 1 1 Ge Exit with Error Return TCP Server Connect C LE SOL Socket 999 C Eval INLR On e Return e Endif CS Exit if Excel did not launch d Eval Rtn XLS Launch SOL Socket C LE Rtn lt gt 0 C callp SQL Disconnect SQL Socket e Eval INLR On C Return C Endif XLS _ Visible The XLS_Visible API is used to make Excel visible or to hide it This API if used at
66. QL Integrator main menu by typing GO RJSRPGSQL and pressing Enter Take option 1 and enter the appropriate access codes listed on the access code sheet provided by RJS Software Systems If you have questions or problems with entering the access codes contact RJS Software Systems for assistance Installing iSeries RPG2SQL Integrator PC component RPG2SQL Integrator The following steps should be used to install iSeries RPG2SQL Integrator on a PC Start your PC and make sure Windows is running Insert the RJS installation CD ROM into drive D CD DRIVE 1 Click the Windows Start button and select the Run menu When the Run dialog is shown type D RPGSQLSV SETUP EXE and click the OK button to start the RUS iSeries RPG2SQL Integrator installation process When the installation program has started follow the on screen instructions to complete the PC installation Most of the time you should be able to simply use the default settings After copying files the Setup program will create a RJS iSeries RPG2SQL Integrator program group and notify you when it s done The RJS iSeries RPG2SQL Integrator can now be launched from the RJS iSeries RPG2SQL Integrator program group Deinstallation of iSeries RPGSQL Integrator The following sections describe how to remove the iSeries RPG2SQL Integrator from an AS 400 system Removing iSeries RPG2SQL Integrator from an AS 400 To remove iSeries RPG2SQL Integrator from an AS 400 system perform the
67. QL MoveFirst SQL Socket ES Exit with Error Return TCP Server Conne K If Rtn lt gt 0 Exsr SExit E Endif SQL_MoveLast The SQL_MoveLast API is used to move to the last record in the currently opened ADO recordset Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns 0 ADO SQL database operation completed successfully 1 ADO SQL database operation failed Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL MoveLast Pr Like TInt D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection Move to last record kee e e e e re be E be E E be E br E E be e E be E be e be E bb E be E be E bb E E bb E E bb E re E be be e e re e re C Eval Rtn SQL MoveLast SQL Socket CS Exit with Error Return TCP Server Conne Cc I Rtn lt gt 0 di Exsr SExit C Endif SQL_MoveNext The SQL_MoveNext API is used to move to the next record in the currently opened ADO recordset Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns 28 RPG2SQL Integrator 0 ADO SQL database operation completed successfully 1 ADO SQL database operation failed Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype
68. QL Server You should create this sample stored procedure in the sample pubs database provided by Microsoft CREATE PROCEDURE rjssample2 Returnl Int output Return2 varchar 30 output as select from authors set Returnl 555 set Return2 This is a test return 999 Example Usage from RPG The sample code listed below creates the return parameters for the stored procedure call calls the RJSSAMPLE2 stored procedure reads only the first returned record closes the recordset and then retrieves the return parameter values 52 GC cx cx ir Ce GC cx Ex Gs C3 GC cx Gs CS Cr RPG2SQL Integrator Make sure stored procedure parameter list is empty Eval Rtn SQL ClearParms SQL Socket This parm will hold the stored procedure return code Must always be defined first if the SP return code will be used See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Return adInteger ParmReturnValue get This parm will hold the stored procedure integer return field Returnl See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Returnl adInteger ParmOutput 0 y This parm will hold the stored procedure VarChar return field Return2 See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Return2 adVarChar ParmOutput sus Ti Call the
69. RJSRPGSQL SQLUPG OLDLIB RJSRPGSQLV This command copies settings from library RJSRPGSQLYV to library RJSRPGSQL Top Error messages for SQLUPG ESCAPE Messages CPF9898 AT Top 149 RPG2SQL Integrator 150
70. SE Driver E tie bt DriverID 277 e Doq c temp The example listed above sets a dBase work directory without the need to define an ODBC data source via the Windows ODDBC Administrator program This is known as a DSNless ADO connection string When selecting records from a table in this directory the following SQL example would be used select from NameAddress dbf Microsoft SQL Server DSNless Connection String Eval DataSrc Driver SQL Server Server 1 1 1 1 Database pubs Uid sa Pwd CA CH CH CH CH The example listed above connects to a SQL Server database without the need to define an ODBC data source via the Windows ODDBC Administrator program This is known as a DSNless ADO connection string SQL Server needs a valid SQL user ID and password passed to it to log in Check with your SQL Server administrator for a valid user id and password for your SQL Server RPG2SQL Integrator If you re not sure whether you have a particular ADO ODBC driver available on the PC running the RPG2SQL Integrator contact RJS for assistance How to Use RPG2SQL Integrator Sample Code How to Use RPG2SQL Integrator Sample Code RPG2SQL Integrator has several examples to help the developer use this tool kit Items 12 and up on the RJSRPGSQL RJSRPGSQL main menu have source code in the file RISRPGSQL SOURCE There are many more examples in that source file than are on the menu The RPG2SQL Integrator pro
71. SQLDBCLOSECONN USING SQL SOCKET RETURNING SQL_RTN SQLBeginTran This function is the COBOL version of the SQL_BeginTran procedure CALL PROCEDURE SQLBEGINTRAN USING SQL SOCKET RETURNING SQL RTN SQLCommitTran This function is the COBOL version of the SQL_CommitTran procedure CALL PROCEDURE SQLCOMMITTRAN USING SQL SOCKET RETURNING SQL RTN SQLRollback This function is the COBOL version of the SQL_Rollback procedure CALL PROCEDURE SQLROLLBACK USING SQL SOCKET RETURNING SQL RTN SQLRunSQLSel This function is the COBOL version of the SQL_RunSQLSel procedure 124 RPG2SQL Integrator Build SQL Record Select String i a a a a a a ee E a EE a EE EE E EE E E e e CALL PROCEDURE SQLRUNSQLSEL USING SQL SOCKET SQL SQLSEL RETURNING SQL BIN SQLRunSQLExec This function is the COBOL version of the SQL_RunSQLExec procedure gd Build SQL record insert string a i a ds ms a a a i a cm as a a a a a a es en a a an il es as MOVE INSERT INTO NameAddress First Last Addressl1 Address2 Ci ty State Zip Phone Fax Email Datel Dollars Values James Johnson 111 Maan Street Apt 5 Mpls MN 55555 y 111 111 1111 222 222 2222 james johnson com 12 25 2002 123456 18 to SQL SQLEXEC a a a ee et a a E ee E Run SOL Query to Insert record to NameAddress NameAddress Access Table i a a a a e e es e as a a cs a as CALL PROCEDURE SQLRUNSQLEXEC USING SQL SOCKET
72. T Optional Positional 8 RCDLAST Last record to export Integer LAST Optional Positional 9 FLDFIRST First field to export 1 8000 FIRST Optional Positional 10 FLDLAST Last field to export 1 8000 LAST Optional Positional 11 Top From file INFILE Specifies the physical file on the System i whose member is to be exported to the worksheet This is a required parameter Qualifier 1 From file name Specify the name of the physical file Qualifier 2 Library LIBL Searches all libraries in the current library list until the first match is found CURLIB Specifies the current library name Specify the name of the library Top From member INMBR Specifies the name of the physical file member that is to be exported to the worksheet FIRST The first member of the specified file is used name Specify the name of the file member Top To workbook OUTXLS Specifies the file path of the workbook to which the physical file member will be exported This file path must be available to the PC where the RPG2SQL Integrator PC component is running It can also be located on a network file share This is a required parameter path name Specify the path name of the workbook To worksheet OUTSHEET Specifies the name of the worksheet to which the data will be exported ACTIVE Specifies the worksheet that was active when you saved the workbook character value 145 RPG2SQL Integrator Specify the name of the worksheet If the workshee
73. TLINE XLS_Command function is used to add an outline style border around all of the selected cells Example to add a solid thick red line around the selected cells e Eval Rtn XLS Command SQL Socket C SETSELECTEDBORDEROUTLINE 1 4 255 P1 Line style P2 Line weight P3 Line color Note See Line Styles for Border Operations for descriptions of the style weight and color values Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTEDBORDERLINE The SETSELECTEDBORDERLINE XLS_Command function is used to add a border to the selected cells The border can surround the outer edge of all cells the top edge bottom edge left edge right edge or can be a diagonal line ampie to add a solid thick red line to the top edge of the selected cells Eval Rtn XLS Command SQL Socket E SETSELECTEDBORDERLINE 8 1 4 255 P1 Border line type P2 Line style P3 Line weight P4 Line color Note See Line Styles for Border Operations for descriptions of the style weight and color values Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTEDBORDERNONE The SETSELECTEDBORDERNONE XLS_Command function is used to remove any cell borders for cell range selection Example to remove all cell borders for selected cells E Eval Rtn X
74. TSELECTIONBACKCOLOR XLS_Command function is used to set the background color for the selected cell range Example to set the font color to red Cc Eval Rtn XLS Command SQL Socket C SETSELECTIONBACKCOLOR 255 Parameters P1 Numeric color value Black 0 Blue 16711680 Cyan 16776960 Green 65280 Magenta 16711935 Red 255 White 16777215 88 RPG2SQL Integrator Yellow 65535 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONFORECOLOR The SETSELECTIONFORECOLOR XLS_Command function is used to set the foreground color for the selected cell range Example to set the font color to red e Eval Rtn XLS Command SQL Socket C SETSELECTIONFORECOLOR 255 Parameters P1 Numeric color value Black 0 Blue 16711680 Cyan 16776960 Green 65280 Magenta 16711935 Red 255 White 16777215 Yellow 65535 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONMERGEANDCENTER The SETSELECTIONMERGEANDCENTER XLS_Command function is used to set merge and center for cell range This will merge all selected cells and center any text within the merged cells Example to merge and center selected cells C Eval Rtn XLS Command SQL Socket c SETSELECTIONMERGEANDCENTER Note Use this function last after format and other c
75. TUPORDER vey P1 Page order Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPBLACKANDWHITE The PAGESETUPBLACKANDWHITE XLS_Command function is used to set whether to print in black and white 109 RPG2SQL Integrator Example to print in black and white e Eval Rtn XLS Command SQL Socket e PAGESETUPBLACKANDWHITE 1 P1 Whether to print in black and white 1 print in black and white O print in color if supported Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPZOOM The PAGESETUPZOOM XLS_Command function is used to set the percentage between 10 and 400 percent by which Microsoft Excel will scale the selected worksheets for printing Example to scale the selected worksheets by 90 percent when printed C Eval Rtn XLS Command SQL Socket e PAGESETUPZOOM 90 P1 Zoom percentage Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned XLS_RowColCount The XLS_RowColCount API is used to retrieve the number of rows and columns and the used range from the current Excel worksheet This API is most useful to determine the limits of existing data when you are adding to a worksheet Note Using this API requires at least V1 0 52
76. The SQL_GetrStrRow API is used to retrieve the current record buffer in delimited format This API performs an implicit SQL_MoveNext after each call Use SQL_GetFIdChrB SQL_GetNumB or SQL_GetDat2B with a tilde delimiter to parse the returned data record Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns This function call returns a delimited record buffer string or ERROR if an error occurs or if no record was found Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL GetStrRow Pr Like T4096String D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection Example Usage 38 RPG2SQL Integrator Get Delimited Record data from SQL Table current record S and perform implicit SQL MoveNext ss ss aah samt a mt aS a a as ss e sas ss ss ss ss ss as sas a ss sa ss sm a a a M e Eval RtnData SQL GetStrRow SQL Socket SQL_GetFldDat2B The SQL_GetFldDat2B API is used to retrieve a date field value from the most recent SQL_GetStrRow call This version should be used when SQL_GetStrRow is used to retrieve a record because date values are returned from the server MM DD YYYY format instead of ISO format Note This API should be used instead of SQL_GetFldDat for retrieving date values after the SQL_GetStrRow API call Note SQL_RunSQLSel must
77. Top Example for PRDINFO Note This example assumes you have added RUSRPGSAQL to your library list Otherwise you must qualify the command with the library RJSRPGSQL PRDINFO 133 RPG2SQL Integrator This command displays the following information about your System i on the status line of your 5250 session e The operating system level e The serial number e The model number e The LPAR number For example OS 400 Level 530 Model 520 Serial 106 02E LPAR 1 Top Error messages for PRDINFO ESCAPE Messages CPF9898 AT Top Enter Access Codes PRDSEC Where allowed to run All environments ALL Parameters Threadsafe No Examples Error messages The Enter Access Codes PRDSEC command enters access codes for RPG2SQL Integrator Restrictions e You must have RJSRPGSQL added to your library list or qualify the command with the library name e You must have the license key that RJS Software provides upon purchasing RPG2SQL Integrator e This command is not threadsafe Top Parameters SECURITY Enter security access Character value Required code Positional 1 DTAARA Enter data area name Character value Required Positional 2 DTALIB Enter data area library Character value Required 134 RPG2SQL Integrator name Positional 3 Top Enter security access code SECURITY Specifies the 50 character security code that RJS Software provides This is a required parameter character value Specify the security
78. Unknown error RPG Prototype DSQL Disconnect Pr Like Tint D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection SQL_Timeout The SQL_Timeout API is used to set ADO query timeouts Note This should always be run before the SQL_DBOpenConn or SQL_RunSQLSEel API Returns 0 Unlimited seconds query timeout Set number of seconds for query timeout 1 ADO connection error 2 Unknown error occurred 3 Unknown communications failure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL TimeOut Pr Like TInt D Socket Like TInt Value D Timeout Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection 19 RPG2SQL Integrator 2 Timeout Set ADO query timeout in seconds Note Not all databases require all parameters SQL_DBOpenConn The SQL_DBOpenConn API is the second procedure used in an ILE program that will be access SQL data from a remote database The SQL_DBOpenConn API connects the ILE program to the specified SQL database using ADO Microsoft Active Data Obects Note A valid ADO data source must be passed to the SQL_DBOpenConn API call or the database connection will not work correctly If the SQL_DBOpenConn API completes successfully a socket ID number is returned from the PC server for the current connection Thi
79. _LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONHORIZALIGN The SETSELECTIONHORIZALIGN XLS_Command function is used to set horizontal alignment for cell range selection 90 RPG2SQL Integrator Example to set the horizontal alignment to Center for all currently selected cells C Eval Rtn XLS Command SQL Socket C SETSELECTIONHORIZALIGN 4108 P1 Numeric alignment value General 1 Left 4131 Right 4152 Center 4108 Justify 4130 Fill 5 Center across selection 7 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONVERTALIGN The SETSELECTIONVERTALIGN XLS_Command function is used to set vertical alignment for cell range selection Example to set the vertical alignment to Top for all currently selected cells e Eval Rtn XLS Command SQL Socket Cc SETSELECTIONVERTALIGN 4160 P1 Numeric alignment value Top 4160 Center 4108 Bottom 4107 Justify 4130 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONWRAPTEXT The SETSELECTIONWRAPTEXT XLS_Command function is used to set wrap text option for cell range selection Example to disable text wrapping e Eval Rtn XLS Command SQL Socket C SETSELECTIONWRAPTEXT 0 Example
80. _MoveNextBuf Move to next record in file End of Loop SQL_DBCloseConn Close connection to selected ADO data source SQL_Disconnect Disconnect from RPG2SQL PC server component Program Flow when inserting new records into SQL table The following pseudocode shows how easy it is to write an RPG program to write all records from any AS 400 file to a PC database file SQL_Connect Connect to RPG2SQL PC server component SQL_DBOpenConn Open connection to selected ADO data source Read AS400FILE Read first record from AS 400 data file Loop to read entire AS 400 data file Begin of Loop Do until EOF SQL_RunSQLExec Build and run a SQL record INSERT statement from AS 400 file fields to create a new record in SQL database RPG2SQL Integrator Read AS400FILE Read next record from AS 400 data file End of Loop SQL_DBCloseConn Close connection to selected ADO data source SQL_Disconnect Disconnect from RPG2SQL PC server component Starting iSeries RPG2SQL Integrator Server on PC In order for RPG programs to talk to a PC database the RPG2SQL Integrator PC server must be started and running on at least one PC Note Make sure the RJS iSeries RPG2SQL Integrator has been installed on the PC before continuing Start the RJS iSeries RPG2SQL Integrator Server Debug Mode program from the RJS iSeries RPG2SQL Integrator program group z O x e RJS Series RPG2SQL Integrator Server File Help Host rjs ibmt21 I
81. a comma is assumed Example Usage 112 RPG2SQL Integrator A Connect to RPG SQL Server and Launch Excel AAA AAA A A A A A A A A A A A A A A E bb be e be E be E bb be E be be e e re e re G Eval SQL Socket SQL Connect 10 1 1 1 GC Exit with Error Return TCP Server Connect e TE SQL Socket 999 e Eval INLR On e Return C Endif AAA AAA A A E be A o CS Exit if Excel did not launch kee e e e e A A A o e Eval Rtn XLS Launch SQL Socket e if Rtn lt gt 0 e callp SQL Disconnect SQL Socket e Eval INLR On e Return C Endif AAA AAA A A A A A A A A be E bb A A A A A A A A A A A A A A A A A A A A E re E be re e re e re or Start new worksheet AAA A A A A A A E E bb E E br E E E be E E be E be E E be E br bb E be E be E E E E be E E E E be E E E E be E E E E be E be E E be E re e Eval Rtn XLS Command SQL Socket e FILENEW kee e e e e A A A o CS Send Testl Test2 Test3 data strings Ce to cells Al Bl C1 AAA A A A e a e a a a A A A be E bb E be E E E E E E bn be e re e Eval Rtn XLS SetDelimRec SQL_ Socket E 020 A1L Testl Testz2 Test3ter t AAA A A A A A A A A A o ce Kill Excel AAA AAA A e e e A A A E be E A A o Eval Rtn XLS Quit SOL Socket AAA A A A A A A E E E bn be e re c Disconnect from RPGSQL server AAA AA A e a e a a E E a E a a A E E E E E E E E E E E E bn be e re C callp SQL Disconnect SQL Socket XLS_SetDelimRc2 The XLS_SetDelimRc2 API is used to send a del
82. above from the the listed Microsoft or RUS web sites Microsoft Data Access Components web link RPG2SQL Integrator http www microsoft com data RJS Microsoft Data Access Components V2 6 web link http www rissoftware com FILES mdac_typ exe After downloading run the MDAC installation process and follow all defaults Note The MDAC controls are Microsoft components and not written or packaged by RJS Any issues caused by the installation of MDAC updates must be addressed by Microsoft AS 400 Installation This section includes instructions for restoring the Series RPG2SQL Integrator AS 400 library AS 400 Installation Using RSTLIB command Insert the RJS CD into the AS 400 CD Drive and sign on to the AS 400 with a user ID that has security officer authority Run the following AS 400 command to restore the Series RPG2SQL Integrator library RSTLIB SAVLIB RJSRPGSQL DEV OPT01 OPTFILE RISRPGSO AS 400 Installation Using FTP This method of installation is described in the RJS Product Catalog and also on the RJS Software Systems installation screen on the RJS CD ROM Note All web download packages contain an automated FTP upload utility AS 400 User Authorization Code Entry Sign on to the AS 400 with a user ID that has security officer authority Entering Series RPG2SQL Integrator Access Code Add the RISRPGSOL library to the user library list by typing ADDLIBLE RJSRPGSQL and pressing Enter Go to the iSeries RPG2S
83. all should be called after XLS_Launch Note Excel visibility is off by default Returns 0 Excel visibility was set successfully 1 Excel visibility was not set successfully Make sure Microsoft Excel is loaded on the the RPG2SQL Integrator server 2 Unknown error 3 Unknown communications failure 66 RPG2SQL Integrator Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DXLS Visible Pr Like TInt D Socket Like TInt Value D Option Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Option Set Excel visibility O hide Excel 1 make Excel visible xample Usage Seege EEO SS SS SS SS A SSS SSS SSS SS SS AAA e Calip XLS Visible SQL Socket 1 XLS_ Quit The XLS_Quit API should be called when you are done working with Excel to generate spreadsheet data This will shut down the current instance of Excel on the RPG2SQL Integrator server PC If the XLS_Quit API completes successfully a return code of 0 is returned from the PC server Returns 0 Excel shutdown was successful 1 Excel shutdown was not successful Make sure Microsoft Excel is loaded on the the RPG2SQL Integrator server 2 Unknown error 3 Unknown communications failure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call faile
84. ame Pass the field name for a field from the current recordset xample Usage Bb eebe E bm be Em EE E ee E a a a EE EE Poo EE EE E EE EE E E E E E E E d e e 3 Get numeric field from current recordset SD m Segen eat kk tS i a i iSite i iii S S i is ie ie a e is i ge CS Numerics are always returned as packed 30 9 CS If you need a smaller field such as 6 2 simply ES evaluate the 6 2 field to the returned number e Eval Dollars SQL GetFldNum SQL Socket E Rollers SQL_GetFldDat The SQL_GetFieldDat API is used to retrieve a date field value from the most recent SQL_MoveFirst SQL_MoveLast SQL_MoveNext or SQL_MovePrev call This API has to go out to the RPG2SQL server to retrieve the field value Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns The field data value is returned in ISO date format as a timestamp value RPG date functions can be used to reformat the ISO date as needed for display or other calculations Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL GetFldDat Pr Z D Socket Like TInt Value D FieldName 256A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 FieldName Pass the field name for a field from the current recordset Get date field from current recordset Has a a sa es a a a a a
85. and ADO data types Eval Rtn SQL AddParm SQL Socket Returnl adInteger ParmOutput Gee T This parm will hold the stored procedure VarChar return field Return2 See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Return2 adVarChar ParmOutput suet T Call the stored procedure to return recordset Eval Rtn SQL RunSPSelect SQL Socket rjssample2 Retrieve First Record from Recordset For this test we will only read one record from the authors table in the pubs database Eval RtnRecord SQL MoveFirsBuf SQL Socket Close recordset if we re done reading from the recordset The return parameters are available for reading only after the recordset is closed This is a ADO command object limitation not a RPG2SQL limitation Eval Rtn SQL _DBCloseRS SQL Socket Retrieve SP Return Value to return record SP Return Value is always converted to string Eval RtnParm SQL GetParm SQL Socket Return Retrieve Integer Return Value to return record SP Return Value is always converted to string Eval RtnParml SQL GetParm SQL Socket Return1 Retrieve VarChar Return Value to return record SP Return Value is always converted to string Eval RtnParm2 SQL GetParm SQL Socket Return2 57 RPG2SQL Integrator SQL_GetParm The SQL_GetParm API is used to return a stored procedure return code value or t
86. and product names are trademarks or registered trademarks of their respective companies Licensing Information iSeries RPG2SQL Integrator is licensed on a per AS 400 system partition basis The software can also be loaded on up to 2 network servers without incurring additional License charges Contacting RJS Software Systems Inc For technical support please review the following information before contacting RJS Software Systems For Technical Support with iSeries RPG2SQL Integrator Because of the complexity of dealing with the various AS 400 connectivity environments please gather and organize as much information as possible on the problem prior to contacting RUS Software Systems for support If you have a question about an iSeries RPG2SQL Integrator operation first browse through the Online Help to try to find your answer prior to calling RUS Software Systems Contact Information Telephone support is available on normal business days from 9 00 am to 5 00 pm central time 952 736 5800 Voice 952 736 5801 Fax Support is available via Email at support rjssoftware com You may also find the answer to your question on our web site www rjssoftware com RPG2SQL Integrator Installation Installation Introduction to Installation This section covers the installation of the iSeries RPG2SQL Integrator software on the AS 400 iSeries system and PC server AS 400 Prerequisites e ARISC AS 400 system running V4R2 V5R2 o
87. armReturnValue 02 et This parm will hold the stored procedure integer return field Returnl See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Returnl adInteger ParmOutput Dr Eh This parm will hold the stored procedure VarChar return field Return2 See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Return2 adVarChar ParmOutput soe 3 Call the stored procedure to return recordset Eval Rtn SQL RunSPSelect SQL Socket rjssample2 Retrieve First Record from Recordset For this test we will only read one record from the authors table in the pubs database Eval RtnRecord SQL MoveFirsBuf SQL Socket Close recordset if we re done reading from the recordset The return parameters are available for reading only after the recordset is closed This is a ADO command object limitation not a RPG2SQL S limitation Eval Rtn SQL DBCloseRS SQL Socket Retrieve SP Return Value to return record SP Return Value is always converted to string Eval RtnParm SQL GetParm SQL Socket Return Retrieve Integer Return Value to return record SP Return Value is always converted to string Eval RtnParml SQL GetParm SQL Socket Returnl Retrieve VarChar Return Value to return record SP Return Value is always converted to string Eval RtnParm2 SQL GetParm SQL S
88. base e Imagine directly reading any record in a SQL Server database e Imagine updating information into a SQL database right from an AS 400 program e Imaging performing a direct daily update to a network based spreadsheet file right from an AS 400 program e Imagine creating a Access database for a salesperson to receive their daily sales information Integration between the AS 400 and other databases has never been so easy The uses for the iSeries RPG2SQL API as a SQL database integrator are endless Native Ability to generate Microsoft Excel worksheets Make sure to tell us how you re going to use our software Your ideas are what keep our products growing RPG2SQL Integrator About RJS Software Systems Inc RJS Software is a privately held software and hardware company dedicated to providing high quality AS 400 iSeries Client Server and web based products and customer services Customer Service is central to the company s objective Read more about us on our web site Copyright 2010 by RJS Software Systems Inc All rights reserved This manual and the software described in it are copyrighted with all rights reserved No part of this publication may be reproduced transmitted transcribed stored in a retrieval system or translated into any language in any form by any means without the written permission of RUS Software Systems Trademarks iSeries RPG2SQL Integrator is a trademark of RUS Software Systems Brand names
89. be re e re e re bi Open existing workbook AAA A A A A A A A A A A A A A A A E E E bn be E re e Eval Rtn XLS Command SQL Socket Cc FILEOPEN C 1ATest xls AAA AAA A A A A o Read second row from worksheet columns tx 1 te 13 Use a semicolon as the delimiter Use row and column values AAA A A A A A a E E E E E E E A A o c Eval Rtnrecord XLS_GetDelimRc2 SQL_ Socket e ETE AAA AA A A A A A A A A A A A A E be E be be e e be e re Read third row from worksheet columns ia 1 to 13 Use 2 asterisks as the delimiter Use Al format AAA A A e a e a a a a a a a a E a be E be E E be E a E ee re e Eval Rtnrecord XLS _GetDelimRc2 SQL Socket e a E ee AAA AAA A A A A A o S Kill Excel AAA AA A A A A A A E bb E E be E be E bb E be E be be e e re e re Eval Rtn XLS_Quit SQL Socket AAA AA A A e e A A E A E Er E E E E E E E E E E E E E bn be e re j Disconnect from RPGSQL server AAA AA a e a A A A A E be E E E E E E E E E E E E E bn be e re G callp SQL Disconnect SQL Socket XLS_RunScript RPG2SQL Integrator The XLS_RunScript API is useful for users who have customized scripting needs when creating workbooks Users can write scripts using VBScript that can talk to the Microsoft Excel OLE object model to automate selected Excel workbook creation processes not covered by the general RPG2Excel API functionality If the XLS_RunScript API completes successfully a return code of 0 is returned from the PC server
90. be used to open a recordset before this API can be used Returns The field data value is returned in ISO date format as a timestamp value RPG date functions can be used to reformat the ISO date as needed for display or other calculations Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL GetFldDat2B Pr Z D RecordBuffer 4096A Value D Varying D Delimiter 4096A Value D Varying D FldNumber 10I 0 Value D Delimiter Parameters 1 RecordBuffer The delimited data record returned from the last SQL_GetStrRow call 2 Delimiter Record delimiter for returned record Always Use tilde 3 FldNumber Enter the 1 based relative recordset field number Get first field value in ISO date format Bh eebe a a i EE EE EE E EE a EE EE EE EE a EE E a EE EE EE E EE E a E E a E a a a Eval RtnDate SQL GetFldDat2B C RtnRecord s1 SQL_GetDelimRcd The SQL_GetrDelimRcd API is used to retrieve the current record buffer in delimited format after a SQL_MoveFirst SQL_MoveNext SQL_MovePrev or SQL_MoveLast database operation 39 RPG2SQL Integrator Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns This function call returns a delimited record buffer string or ERROR if an error occurs or if no record was found Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_La
91. book SaveAs Filelame _ C Documents and Setcings DICK My Documenta Booki CreateBackup Faise Active orkbook SaveAs FileName _ C Documents and Settings DICK My Documenta Booki CreateBackup False Activelorkbook Saveks FileName _ C Documents and Sectings DICK My Documents Booki CreateBackup Ffaise ActiveVorkbook Savels FileName _ C Documents and Settings DICK My Documents Booki CreateBackup False End Sub Microsoft Excel VB Code Window Alt F11 from Excel FileFormat x1 KS Fileformat xi ol _ YileFormat xiDBF4 _ FileFormat xiDBF3 FileFormat xiDBF2 _ FileFormati xiDIF _ 71 RPG2SQL Integrator Example The value for xIDIF was displayed above by typing xIDIF in the immediate window and pressing enter The xIDIF value is 9 Available constants Microsoft Excel Output Format Constants D Microsoft Excel Constants DxlNormal E 4143 DxlSylk e 2 DxlText Cc 4158 DxlTemplate C 17 DxlTextPrinter 36 Dx1Excel5 E 39 Dx1Excel9795 a 43 Dx1CSV E 6 Dx1CSVMac G 22 Dx1CSVMSDOS CG 24 Dx1CSVWindows E ao Dx1Excel 4 e ES Dx1Excel3 E 29 Dx1Excel2 iC 16 Dx 1WK3 CG 15 Dx 1WK3FM3 ic 32 Dx1WK4 E 38 Dx1WK1FMT E 30 Dx1WK1ALL e 31 Dx1WK1 E 5 Dx1WKS e 4 Dx1WQ1 C 34 Dx1DBF4 ic 11 Dx1DBF3 E 8 Dx1DBF2 E F Dx1DIF E 9 Dx1HTML E 44 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error i
92. brary list until the first match is found CURLIB Specifies the current library name 141 RPG2SQL Integrator Specify the name of the library Top Output member options OUTMBR Specifies the name of the database file member to which the output is directed Element 1 FIRST The first member of the specified file is used name Specify the name of the file member If the member does not exist it will be created Element 2 Replace or add records REPLACE The file is cleared before new records are inserted ADD New records are added after any existing records Top Header rows HDRROWS Specifies the number of rows that comprise the header These rows are not included in the data that is imported NONE None of the rows comprise the header decimal number Specify the number of rows that comprise the header Top Log messages LOGMSG Specifies whether to log warnings and errors that occur when importing the worksheet to the physical file YES Log warnings and errors NO Do not log warnings or errors RPG2SQL Integrator host name HOST Specifies the IP address of the PC on which the RPG2SQL Integrator PC component is running CURRENT Specifies to use the IP address of your 5250 session character value Specify the IP address of the PC on which the RPG2SQL Integrator PC component is running Top Blank row option BLKROW Specifies whether to import blank rows from a worksheet SKIP Do not import
93. c formatting information for active cell See the Excel documentation or the Format Cells Number menu for more detail on defining numeric formats Excel has a number of formatting options such as Currency Accounting General etc Example to set the number format to Currency for all currently selected cells C Eval Rtn XLS Command SQL Socket C SETACTIVECELLNUMBERFORMAT 0 00_ 0 00 P1 Selection number format See Excel docs for definition Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETAUTOFITALL The SETAUTOFITALL XLS_Command function is used to select all cells that are defined in the range and automatically resize the columns to fit the data You can define the cell range by using SETRANGESELECT or SETRANGEALL Example to select all cells and resize E Eval Rtn XLS Command SQL Socket SETAUTOFITALL Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETRANGEALL The SETRANGEALL XLS_Command function is used to select all cells in the active worksheet 81 RPG2SQL Integrator Example to select all cells iG Eval Rtn XLS Command SQL Socket SETRANGEALL Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETRAN
94. call and return parameters can be queried via the SQL_GetParm function after the SQL_DBCloseRS function has been called What this means is that you must read all records in the returned recordset before trying to read any return parameters This is a Microsoft ADO ODBC limitation SQL_ClearParms should be called to remove all store procedure parameters before calling SQL_AddParm to define a new parameter Returns A general return code indicating success or failure is returned however the return parameters can also be queried for the stored procedure return code as well as any return parameters 0 Stored procedure call completed normally Not 0 Errors occurred while running stored procedure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL RunSPSelect Pr Like Tint D Socket Like TInt Value D SPString 4096A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Variable length stored procedure execution string value of up to 4096 characters Parameter values are set via SQL_AddParm function Sample SQL Server stored procedure RISSAMPLE2 This stored procedure runs a SQL record selection statement sets the stored procedure return code to 999 and sets the integer value to 555 and the varchar value to This is a test Note The RJSSAMPLE2 stored procedure must be created on your S
95. calling SQL_ClearParms SQL_AddParm should be used to set up any new parameters for the next stored procedure call Note If SQL_ClearParms is not called before setting up a new parameters list an incorrect number of parameters may exists for calling the next stored procedure so always make sure to call SQL_ClearParms before adding new parameters and calling a stored procedure Returns A general return code indicating success or failure is returned 0 Call completed normally Not 0 Errors occurred while clearing parameters Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL ClearParms Pr Like TInt D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection Example Usage Make sure stored procedure parameter list is empty prior to adding parameters e Eval Rtn SQL ClearParms SQL Socket SQL_AddParm The SQL_AddParm API is used to add a parameter to the stored procedure parameter list prior to making a stored procedure call This function can also set the input parameter value if the parameter will be used as input to a SQL stored procedure This function should be used when setting up a list of parameters to be sent to a stored procedure If multiple parameters will be sent to a stored procedure the SQL_AddParm should first be called to create the Return value param
96. come to the RPG2SQL User Guide This guide is document version 1 50 5 Overview Overview Introduction to RPG2SQL Integrator The iSeries RPG2SQL Integrator is a native OS 400 API product used to allow ILE RPG ILE Cobol and programmers to directly access any network attached database such as Oracle Microsoft SQL Server Access dBase Foxpro Excel CSV MySQL and more right from an ILE RPG or ILE Cobol program Note While RPG2SQL Integrator works with products like Oracle Microsoft SQL Server Access dBase Foxpro Excel CSV MySQL and more RPG2SQL Integrator does not provide the ODBC drivers or OLE DB providers for these products Contact the database vendors directly for ODBC drivers or OLE DB providers Since the RPG2SQL API uses ADO Microsoft Active Data Objects for database connectivity any database type that can be opened via ADO or ODBC can be accessed directly from an RPG program Prior to the RPG2SQL integrator AS 400 RPG developers had to resort to manually uploading and downloading data files between the AS 400 and other platforms implementing file replication software or other means of sharing data between the AS 400 and other databases With the RPG2SQL Integrator those days are over because the RPG2SQL API allows RPG and Cobol programs to directly read write update and delete records from remote SQL databases e Imagine no more file replication for an AS 400 application to access information in a SQL Server data
97. ctly for ODBC drivers or OLE DB providers Since the RPG2SQL API uses ADO Microsoft Active Data Objects for database connectivity any database type that can be opened via ADO or ODBC can be accessed directly from an RPG program Prior to the RPG2SQL Integrator AS 400 RPG developers has to resort to manually uploading and downloading data files between the AS 400 and other platforms implementing file replication software or other means of sharing data between the AS 400 and other databases With the RPG2SQL Integrator those days are over because the RPG2SQL API allows RPG and Cobol programs to directly read write update and delete records from remote SQL databases e Imagine no more file replication for an AS 400 application to access information in a SQL Server database e Imagine directly reading any record in a SQL Server database e Imagine updating information into a SQL database right from an AS 400 program e Imaging performing a direct daily update to a network based spreadsheet file right from an AS 400 program e Imagine creating a Access database for a salesperson to receive their daily sales information Integration between the AS 400 and other databases has never been so easy The uses for the iSeries RPG2SQL API as an SQL database integrator are endless Make sure to tell us how you re going to use our software Your ideas are what keep our products growing ADO ODBC Data Source Examples The RPG2SQL Integrator uses
98. d if an error is returned RPG Prototype DXLS Quit Pr Like TInt D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection Example Usage 67 RPG2SQL Integrator ha Connect to RPG SQL Server and Launch Excel and then quit right away AAA AAA e e e A E be E be E be A A bb be E bb be E bb bb re e re e Eval SUL Socket SUL Connect 10 1 1 1 H Exit with Error Return TCP Server Connect e EE SOL Socket 999 Cc Eval INLR On e Return Cc Endif ce Exit if Excel did not launch e Eval Rtn XLS _Launch SQL Socket C If Rtn lt gt 0 e callp SQL Disconnect SQL Socket Cc Eval INLR On e Return C Endif oe Quit Excel after launching it CG Eval Rtn XLS Quit SQL Socket e callp SQL Disconnect SQL Socket C Endif ES Bail out we re done E Eval INLR On e Return XLS_Command XLS_Command The XLS_Command API is used to run several Excel functions Functions not listed below can be run using VBScript code scripts For an example of using VBScript in the XLS_Command API refer tohttp wiki rissoftware com wiki index php Main_Page and then search for XLS_RunScript The XLS_Command API if used at all should be called after XLS_Launch Returns 0 The Excel command ran successfully 1 The Excel command did not run successfully Make sure Microsoft Excel is loaded on the the RPG2SQL Integrator server 2 Unknown error 3 Unkn
99. duct consists of the following service programs that contain the procedures you can use to work with remote databases e RPGSQLO1R This service program contains all the procedures and can be used with both ILE RPG and ILE COBOL e RPGDDSO1R This service program contains the procedures for DDS based data retrieval and can be used with both ILE RPG and ILE COBOL e RPGSQLO2R deprecated This service program was created for use with ILE COBOL only it is no longer needed is not being updated and does not contain any new procedures see the COBOL examples SCTEST as well as copy books RPGSQL1CB and RPGSQL2CB for details on the use of RPGSQLO1R with ILE COBOL programs Structure of the Examples All examples comprise 4 or 5 items e Acommand such as SQTESTO1 e Acommand processing program such as SQTESTOIC e An RPGLE program that has the real sample code such as SQTESTO1R e A program to build the example such as SQTESTO1B no longer needed since a header specification on the RPGLE source lets you use PDM option 14 to create it e Some examples might include a printer file or display file All examples have a command front end The reason for this structure is to simplify running the examples and is not intended to show typical usage Locating Source for Menu Items In order to tell which example corresponds to a menu item simply type the item number and press Enter The sample command name is part of the header text of the comma
100. dure with no additional parameters E sks se Hk ek O ME Gl lit A S SI I ei ik A ik ta kf ha ita a ik hi i tak A fia sk it ik ih es i ie Eval Rtn SQL RunSPBasic SQL Socket E rjssamplel SQL_RunSP The SQL_RunSP API will run a SQL Server or other database stored procedure with parameters as defined via the SQL_AddParm function Multiple parameters can be added for each stored procedure call if needed This function call is used to call simple or complex stored procedures that can take input parameters as well as return output parameters if desired Note No recordset will be returned from the SQL_RunSP call however return parameters can be queried via the SQL_GetParm function Use SQL_RunSPSelect if you will be returning parameters as well as a recordset from a stored procedure call SQL_ClearParms should be called to remove all stored procedure parameters before calling SQL_AddParm to define a new parameter Returns A general return code indicating success or failure is returned however the return parameters can also be queried for the stored procedure return code as well as any return parameters 49 RPG2SQL Integrator 0 Stored procedure call completed normally Not 0 Errors occurred while running stored procedure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL_ RunSP Pr Like Tint D
101. e SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONFONTSUPERSCRIPT The SETSELECTIONFONTSUPERSCRIPT XLS_Command function is used to set the superscript value for the selected cell range Example to disable superscript C Eval Rtn XLS Command SQL Socket D SETSELECTIONFONTSUPERSCRIPT 0 Example to set superscript C Eval Rtn XLS Command SQL Socket SETSELECTIONFONTSUPERSCRIPT 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned 85 RPG2SQL Integrator SETSELECTIONFONTSTRIKETHROUGH The SETSELECTIONFONTSTRIKETHROUGH XLS_Command function is used to set the strikethrough value for the selected cell range Example to disable strikethrough e Eval Rtn XLS Command SQL Socket C SETSELECTIONFONTSTRIKETHROUGH 0 Example to set strikethrough C Eval Rtn XLS Command SQL Socket G SETSELECTIONFONTSTRIKETHROUGH 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONFONTSUBSCRIPT The SETSELECTIONFONTSUBSCRIPT XLS_Command function is used to set the subscript value for the selected cell range Example to disable subscript C Eval Rtn XLS Command SQL Socket C SETSELECTIONFONTSUBSCRIPT 0 Example to set subscript C Eval Rtn
102. e TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection SQL_LastErrNum2 The SQL_LastErrNum2 API returns the last SQL error number that occurred on the RPG2SQL server in packed numeric format Note SQL_LastErrNum SQL_LastErrNum2 SQL_LastErrMsg and SQL_LastFullErr can be called as desired to retrieveinformation about the last error that occurred on the server There is no rule as to which of these APIs must be used Returns A 15 0 numeric value containing the last error number RPG Prototype DSQL LastErrNum2 Pr ISP 0 D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection 46 RPG2SQL Integrator Get last SQL error number in packed format TOA ss a aS a a a i ys a ca a ys a a ee ee a es a a a ee ee a ee DRtnErr S ISP 0 e Eval RtnErr SQL LastErrNum2 SQL Socket SQL_LastErrMsg The SQL_LastErrMsg API returns the last SQL error message text from the last error that occurred on the RPG2SQL server This text corresponds to the last error number which can be retrieved via the SPL_LastErrNum API Note SQL_LastErrNum SQL_LastErrNum2 SQL_LastErrMsg and SQL_LastFullErr can be called as desired to retrieveinformation about the last error that occurred on the server There is no rule as to which of these APIs must be used Returns Text string value containing last error number H an error occurs when retrieving the last error info the text value ERROR is
103. e currently opened ADO recordset and return a delimited record buffer containing the record data This command is used when high performance recordset reads are required Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns This function call returns a delimited record buffer string or ERROR if an error occurs or if no record was found 34 RPG2SQL Integrator Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL MoveNextBuf Pr Like T4096String D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection Go to next record in recordset and get the record buffer data in return Requires only a single server pass for fast performance amp SQL_MovePrevBuf The SQL_MovePrevBuf API is used to move to the previous record in the currently opened ADO recordset and return a delimited record buffer containing the record data This command is used when high performance recordset reads are required Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns This function call returns a delimited record buffer string or ERROR if an error occurs or if no record was found Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is retu
104. eadsafe Top Parameters INXLS From workbook Path name Required Positional 1 INSHEET From worksheet Character value ACTIVE Optional Positional 8 140 RPG2SQL Integrator OUTFILE To file Qualified object name Required Positional 2 Qualifier 1 To file Name ositiona Qualifier 2 Library Name LIBL CURLIB OUTMBR To member options Element list Optional Positional Element 1 Member Name FIRST SCHER Element 2 Replace or REPLACE ADD add records HDRROWS Header rows Decimal number NONE Optional Positional 4 LOGMSG Log messages YES NO Optional Positional 5 HOST RPG2SQL Integrator Character value CURRENT Optional host name Positional 6 BLKROW Blank row option SKIP DFT Optional Positional 7 Top From workbook INXLS Specifies the file path of the workbook that you want to import from the PC where the RPG2SQL Integrator PC component is running This is a required parameter path name Specify the path name of the workbook Top From worksheet INSHEET Specifies the name of the worksheet from which the data will be imported ACTIVE Specifies the worksheet that was active when you saved the workbook character value Specify the name of the worksheet Top To file OUTFILE Specifies the physical file to which to import the worksheet This is a required parameter Qualifier 1 To file name Specify the name of the physical file Qualifier 2 Library LIBL Searches all libraries in the current li
105. ecify the remote system name to be verified Remote internet address INTNETADR Specifies the remote internet address Either a valid IP Version 4 or IP Version 6 address will be accepted An IP Version 4 internet address is not valid if it has a value of all binary ones or all binary zeros for the network identifier 1D portion or the host ID portion of the address character value Specify the internet address of the remote system If you enter the internet address from a command line you must enclose the address in apostrophes 139 RPG2SQL Integrator Top Example for SQLPING Note This example assumes you have added RUSRPGSAQL to your library list Otherwise you must qualify the command with the library RJSRPGSQL SQLPING RMTSYS www ibm com This command tests if you can connect to the specified address www ibm com Top Error messages for SQLPING ESCAPE Messages CPF9898 AT Top Import Worksheet to PF SQLXLSIMP Where allowed to run All environments ALL Parameters Threadsafe No Examples Error messages The Import Worksheet to PF SQLXLSIMP command imports a Microsoft Excel worksheet to a physical file member Data is imported according to the definitions of the fields in the physical file Warnings can be issued for invalid data Restrictions e You must have RJSRPGSQL added to your library list or qualify the command with the library name e The physical file must already exist e This command is not thr
106. ect from RPGSQL server AAA AA a e a e A A E E E E E E be E ee be e re G callp SQL Disconnect SQL Socket XLS_GetDelimRc2 The XLS_GetDelimRc2 API is used to retrieve a delimited iSeries data record buffer from the active Excel worksheet The amount of data that can be received is up to 30 000 characters total length The XLS_GetDelimRc2 API can be called multiple times as needed Returns This function call returns a delimited record buffer string or ERROR if an error occurs 117 RPG2SQL Integrator Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DXLS_GetDelimRc2 Pr Like T30000String D Socket Like TInt Value D StartRowNum 7P 0 Value D StartColNum 7P 0 Value D EndColNum 7P 0 Value D CellRange 20A Value D Varying D DelimParms 30000A Value D Varying D DelimChar 6A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 StartRowNum This parameter is used to specify the starting worksheet row from which the data will be read Note If this parameter is not used the CellRange value must be specified and the StartRowNum StartColNum and EndColNum values must be set to 0 3 StartColNum This parameter is used to specify the starting worksheet column of the range from which the data will be read Note If this parameter is not used the CellRange value must be speci
107. ed SETACTIVECELLMERGEANDCENTER The SETACTIVECELLMERGEANDCENTER XLS_Command function is used to set merge and center for the active cell This will center any text within the active cell Example to merge and center the active cell C Eval Rtn XLS Command SQL Socket C SETACTIVECELLMERGEANDCENTER Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLFONTSTRIKETHROUGH The SETACTIVECELLFONTSTRIKETHROUGH XLS_Command function is used to set the strikethrough value for the active cell Example to disable strikethrough C Eval Rtn XLS Command SQL Socket E SETACTIVECELLFONTSTRIKETHROUGH 0 Example to set strikethrough C Eval Rtn XLS Command SQL Socket G SETACTIVECELLFONTSTRIKETHROUGH 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned 78 RPG2SQL Integrator SETACTIVECELLFONTSUPERSCRIPT The SETACTIVECELLFONTSUPERSCRIPT XLS_Command function is used to set the superscript value for the active cell Sane to disable superscript Eval Rtn XLS Command SQL Socket SETACTIVECELLFONTSUPERSCRIPT 0 Os to set superscript Eval Rtn XLS Command SQL Socket i SETACTIVECELLFONTSUPERSCRIPT 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed
108. ed to start a new database transaction if required Using database transactions is an optional feature of Microsoft SQL Server and some other database types to buffer changes made via INSERT UPDATE and DELETE statements until all changes have been made If any updates fail database changes can be removed in a single rollback operation Once all changes are made a commit operation is performed to save all changes to the database Note There is a lot of detail in the Microsoft SQL Server Online Help regarding using database transactions and when they should be used Note Using transactions is not required and only works with Microsoft SQL Server or other ADO ODBC databases that support database transaction control The available RPG2SQL Integrator transaction processing functions are SQL_BeginTran Start a database transaction SQL_CommitTran Commit all changes to the selected database SQL_Rollback Rollback changes made during a database transaction All INSERT UPDATE and DELETE statements that are run after the SQL_BeginTran procedure will be treated as being part of the same database transaction Once you have run all selected SQL statements make sure to issue the SQL_CommitTran to commit all changes to the SQL Server database Note lf SQL_CommitTran is not run all changes made to the database since running SQL_BeginTran can potentially be lost If a transaction is started with SQL_BeginTran all INSERT UPDATE or DELETE state
109. edure will be treated as being part of the same database transaction Once you have run all selected SQL statements make sure to issue the SQL_CommitTran to commit all changes to the SQL Server database Note If SQL_CommitTran is not run all changes made to the database since running SQL_BeginTran can potentially be lost If a transaction is started with SQL_BeginTran all INSERT UPDATE or DELETE statements can be rolled back prior to ending the transaction by using a single call to the SQL_Rollback procedure Once a rollback has been called all changes made after the SQL_BeginTran are removed from the SQL Server database tables After issuing a SQL_CommitTran or SQL_Rollback call you will need to issue the SQL_BeginTran call to start a new SQL Server database transaction Returns 0 ADO SQL begin transaction completed successfully 1 ADO SQL begin transaction failed 2 Unknown error occurred 3 Unknown communications failure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL BeginTran Pr Like Tint D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection SQL_CommitTran 22 RPG2SQL Integrator The SQL_CommitTran API permanently saves all changes made during a database transaction After issuing a commit command to save changes the SQL_BeginTran procedure must be us
110. een made If any updates fail database changes can be removed in a single rollback operation Once all changes are made a commit operation is performed to save all changes to the database Note There is a lot of detail in the Microsoft SQL Server Online Help regarding using database transactions and when they should be used Note Using transactions is not required and only works with Microsoft SQL Server or other ADO ODBC databases that support database transaction control The available RPG2SQL Integrator transaction processing functions are SQL_BeginTran Start a database transaction SQL_CommitTran Commit all changes to the selected database SQL_Rollback Rollback changes made during a database transaction All INSERT UPDATE and DELETE statements that are run after the SQL_BeginTran procedure will be treated as being part of the same database transaction Once you have run all selected SQL statements make sure to issue the SQL_CommitTran to commit all changes to the SQL Server database Note If SQL_CommitTran is not run all changes made to the database since running SQL_BeginTran can potentially be lost If a transaction is started with SQL_BeginTran all INSERT UPDATE or DELETE statements can be rolled back prior to ending the transaction by using a single call to the SQL_Rollback procedure Once a rollback has been called all changes made after the SQL_BeginTran are removed from the SQL Server database tables After issui
111. egrator main menu and the available options 13 RPG2SQL Integrator AE Fie Edt Transfer Appearance Communicaton Aen Window Help BID GIS E m a blb a ef D em Pom Copy Paste Send Recy Display Coke Moo Record Sko Puy Gut Cloted Seet index RISRPGSOL iSeries RPG2SQL Integrator Main Menu Select one of the following Bot tom F3 Exit F4 Prompt F9 Retrieve F12 Cancel 3 Information Assistant F16 A5 400 Main menu C RJS Software Systems Inc 1994 2002 MA a Connected lo remote toive toa 125 1 1 40 uneg por 23 iSeries RPG2SQL Integrator Main Menu 21 001 Getting to the Main Menu Sign on to the AS 400 system and add the RISRPGSOL library to your library list using the following AS 400 command ADDLIBLE RJSRPGSQL Next type GO RJSRPGSQL and press Enter This will take you to the iSeries RPG2SQL Integrator main menu Available Options on the Main Menu Option 1 Enter iSeries RPG2SQL Integrator License Code This menu option is used to enter the AS 400 access code for the iSeries RPG2SQL Integrator software An access code will be provided to you by RJS Software Systems Inc Option 10 Work with Sample Source Code This menu option is used to display all the included sample programs in the SOURCE source file in the RJSRPGSAL library Option 11 Test ADO ODBC Database Connection SQLTEST02C This menu option is used
112. entation for formatting codes that can be used in the footer Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPRIGHTFOOTER The PAGESETUPRIGHTFOOTER XLS_Command function is used to set the right part of the footer Example to set the right part of the footer to the file name e Eval Rtn XLS Command SQL Socket C PAGESETUPRIGHTFOOTER 4F P1 Text to use for the right part of the footer See Excel documentation for formatting codes that can be used in the footer Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPLEFTMARGIN The PAGESETUPLEFTMARGIN XLS_Command function is used to set the left margin in inches Example to set the left the margin to 1 5 inches e Eval Rtn XLS Command SQL Socket e PAGESETUPLEFTMARGIN 1 5 P1 Size of the left margin in inches Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPRIGHTMARGIN The PAGESETUPRIGHTMARGIN XLS_Command function is used to set the right margin in inches Example to set the right the margin to 1 5 inches e Eval Rtn XLS Command SQL Socket e PAGESETUPRIGHTMARGIN 1 5 P1 Size of the right margin in inches Note You can use the SQL_LastErrNum SQ
113. ermine why the API call failed if an error is returned 69 RPG2SQL Integrator FILENEW The FILENEW XLS_Command function is used to generate a new empty workbook The API can also generate a new workbook based on a template workbook as well Example to create a new empty workbook E Eval Rtn XLS Command SOL Socket FILENEW ample to create a new workbook based on the template name passed Eval Rtn XLS E iar Socket E FILENEW C XEXCELTEMPLATE XLS Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned FILEOPEN The FILEOPEN XLS_Command function is used to open an existing workbook PS to open an existing workbook Eval Rtn XLS Command SQL Socket S FILEOPEN C TEST XLS Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned FILESAVE The FILESAVE XLS_Command function is used to save the currently open workbook If the file has never been saved beforem use the FILESAVEAS function Example to save open workbook to same file name C Eval Rtn XLS Command SQL Socket FILESAVE Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned FILESAVEAS 70 RPG2SQL Integrator The FILESAVEAS XLS_Command function is used to save the currently ope
114. eter Each additional call to SQL_AddParm should be made in the order which the selected parameter will be passed to or returned from the stored procedure call Note As good practice SQL_ClearParms should be called prior to each stored procedure call to reset any input or return parameters If SQL_ClearParms is not called before setting up a new parameters list an incorrect number of parameters may exists for calling the next stored procedure so always make sure to call SQL_ClearParms before adding new parameters and calling a stored procedure 54 RPG2SQL Integrator Returns A general return code indicating success or failure is returned 0 Parameter was added normally Not 0 Errors occurred while adding stored procedure parameter Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LasiFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL AddParm ES Like TInt D Socket Like TInt Value D ParmName 256 Value D Varying D ParmDataType 11 0 Const D ParmDirection LIP U Const D ParmLength TIP 0 Const D ParmValue 4096 Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Parameter name Enter a text name for the SQL Server parameter The parameter name will be used when returning values from the stored procedure call if an output parameter is used This value is required for both input and output parameters 3 Parameter data type
115. fied and the StartRowNum StartColNum and EndColNum values must be set to 0 4 EndColNum This parameter is used to specify the ending worksheet column of the range from which the data will be read Note If this parameter is not used the CellRange value must be specified and the StartRowNum StartColNum and EndColNum values must be set to 0 5 CellRange This parameter is used to specify the single row worksheet range from which the data will be read Note If this parameter is not used the StartRowNum StartColNum and EndColNum values must be greater than 0 and this value must be blanks 6 DelimParms This parameter is not used Leave it blank 7 DelimChar This parameter is the character string used to delimit the values in the record buffer If blanks are passed a tilde is assumed Example Usage 118 A Connect to RPG SQL Server and Launch Excel AAA AAA A A A A A A A A A A e be E be E bb be E be be e e re e re G Eval SQL Socket SQL Connect 10 1 1 1 S Exit with Error Return TCP Server Connect e TE SQL Socket 999 e Eval INLR On e Return e Endif AAA A A A A A E E be E be E be re e e rn re Exit if Excel did not launch kee e e e e e e E E E be E be E bb E E E E E E E be E bb E E E be E bb E E be E be E re E E be be e re re e Eval Rtn XLS Launch SQL Socket e if Rtn lt gt 0 a callp SQL Disconnect SQL Socket e Eval FINLE On e Return e Endif AAA AA A A a e E be E be E re E
116. files rpgsqlsv irpgsql mdb Uid admin Pwd This command connects to Microsoft Access Example 2 Connecting to SQL Server RSQCONN HOST 125 1 2 49 ADODSN Driver Microsoft Access Driver mdb Dbq c program files rpgsqlsv irpgsql mdb Uid admin Pwd This command connects to SQL Server Top Error messages for RSQCONN ESCAPE Messages CPF9898 amp 1 Top Return device IP address RSQIP Where allowed to run Parameters Examples e Batch program BPGM Error messages e Interactive program IPGM Threadsafe No The Return device IP address RSQIP command returns the TCP IP address for the current 5250 session Restrictions e You must have RISRPGSQL added to your library list or qualify the command with the library name e You must call this command from a CL program e This command is not threadsafe 137 RPG2SQL Integrator Top Parameters DEVD Device Name Optional Positional 1 IPADDR IP address 15 Character value Optional Positional 2 Top Device DEVD Specifies the System i display device name Use this entry to retrieve the IP address of the the current 5250 session name Specify the name of the System i display device name Top IP address 15 IPADDR This parameter returns a 15 character IP address for the selected device or current 5250 session Top Example for RSQIP Note This example assumes you have added RUSRPGSAQL to your library list Otherwise you must qualify the command
117. for PRDSEC ocooncocinoccnnncccnonncononcnononnnnn cc cnn 135 Test ADO Connection String DHGOCONN cc rn cn ran rana 136 PAM A a ae a ey ee 136 RPG2SQL Server IP Address HOST cccccccesseecseceeceeeeesaeeeeaeeseeeeceaeeeeaaesseneeseeeeesaaeeneaaeennees 136 ADO Data Source String Login ADODSN ooococinnccnnnccononccononanononnnancccnnnn canon nan c nan cnrnn rana nn nn nnnnns 136 Examples for RSQCONN via A Aaa taa aga Eds 137 Error messages for RSQCONN cccsceeeeececeeeeeesaeeeeeeeceaeeecaaeeseaeeseaeeesaaeeesaaeeseaeeseaeeesaeeeeaeeeenees 137 Return device IP address DHGOIP cnn anna 137 PM A A a ey ee 138 Device DEN DY iii A a a Sat E E a ca ead as wade eden aga Id 138 Praddress 1 5 PADDR 0 ii a A E DE EA dai dE 138 Exampletor RSQ Puri A a Edi 138 Error messages tor RSOIP iii iiaii iiia a a ave cela eae dee 138 Ping Remote Host SQEPING ictericia raro Aa aa aaan aeaa Aaa aan 138 NEE 139 Remote System RMI SYS rrea aain era aaa tele fave aeaa a aae eaaa ahaaa a iaaa dine aatan 139 Remote internet address NTNET ADR 139 Example Tor SQEPING tocarla rd llas tado eegen 140 Error messages for SQLPING oari iaaa aeaa e naa aeaa aere aaa aeara dat Ed pas 140 Import Worksheet to PF SQLXLSIMP oooconcconnccconoccconcccconccononann arc cnnnn cnn rca rca rra 140 Eu 140 From Workbook INKL EE 141 From worksheet INSHEET eucariotas eisitdde oras 141 Tome QUT S E to abia a add tl 141 Output member options OUTTMDP non
118. fy the starting worksheet row from which the data will be read Note If this parameter is not used the CellRange value must be specified and the StartRowNum StartColNum and EndColNum values must be set to 0 3 StartColNum This parameter is used to specify the starting worksheet column of the range from which the data will be read Note If this parameter is not used the CellRange value must be specified and the StartTRowNum StartColNum and EndColNum values must be set to 0 4 EndColNum This parameter is used to specify the ending worksheet column of the range from which the data will be read Note If this parameter is not used the CellRange value must be specified and the StartRowNum StartColNum and EndColNum values must be set to 0 5 CellRange This parameter is used to specify the single row worksheet range from which the data will be read Note If this parameter is not used the StartRowNum StartColNum and EndColNum values must be greater than 0 and this value must be blanks 6 DelimParms This parameter is not used Leave it blank 7 DelimChar This parameter is the character string used to delimit the values in the record buffer If blanks are passed a tilde is assumed Example Usage 116 RPG2SQL Integrator kee A e e e A A A A A A A A A A A A A A A be E E bb E be E be re e e re e re A Connect to RPG SQL Server and Launch Excel AAA AAA A A A A A A A A A A e be E be E bb be E be be e e re e
119. hanges otherwise Excel may give unexpected results Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned 89 RPG2SQL Integrator SETSELECTIONSTYLE The SETSELECTIONSTYLE XLS_Command function is used to set selection style formatting information for cell range A style contains a predefined list of formatting attributes See the Excel documentation or the Format Style menu for more detail on defining styles Example to set the style to Normal for all currently selected cells E Eval Rtn XLS Command SQL Socket SETSELECTIONSTYLE E Normal P1 Style name See Excel docs for definition Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONNUMBERFORMAT The SETSELECTIONNUMBERFORMAT XLS_Command function is used to set numeric formatting information for cell range See the Excel documentation or the Format Cells Number menu for more detail on defining numeric formats Excel has a number of formatting options such as Currency Accounting General etc Example to set the number format to Currency for all currently selected cells e Eval Rtn XLS Command SQL Socket e SETSELECTIONNUMBERFORMAT 0 00_ 40 00 P1 Selection number format See Excel docs for definition Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL
120. he PAGESETUPPAPERSIZE XLS_Command function is used to set the paper size Example to set paper size to letter e Eval Rtn XLS Command SQL Socket e PAGESETUPPAPERSIZE 1 Example to set paper size to legal 108 RPG2SQL Integrator e Eval Rtn XLS Command SQL Socket e PAGESETUPPAPERSIZE 5 P1 Paper size See Excel documentation for other values that you can use Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPFIRSTPAGENUMBER The PAGESETUPFIRSTPAGENUMBER XLS_Command function is used to set the first page number of a sheet Example to set the first page number of a sheet to 3 e Eval Rtn XLS Command SQL Socket C PAGESETUPFIRSTPAGENUMBER 3 Example to have Excel determine the starting page of a sheet e Eval Rtn XLS Command SQL Socket C PAGESETUPFIRSTPAGENUMBER 4105 P1 First page number See Excel documentation for further details Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPORDER The PAGESETUPORDER XLS_Command function is used to set the order of how pages are numbered and printed Example to set the order to down then over iC Eval Rtn XLS Command SQL Socket PAGESETUPORDER 1 Example to set the order to over then down E Eval Rtn XLS_Command SQL Socket PAGESE
121. he beginning 96 RPG2SQL Integrator e Eval Rtn XLS Command SQL Socket WORKSHEETCOPY e BEFORE SEN to copy active worksheet after worksheet Accounts and renamed nger Eval Rtn XLS Command SQL Socket WORKSHEETCOPY r AFTER Accounts Ledger P1 Copy position AFTER either after P2 or at the end BEFORE either before P2 or at the beginning P2 Worksheet name to position from P3 New worksheet name Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned EXCELDATEFORMATON The EXCELDATEFORMATON XLS_Command function is used to turn on Excel date formatting Example to turn on Excel date formatting This will return the Excel date time formatted as an ISO timestamp C Eval Rtn XLS Command SQL Socket EXCELDATEFORMATON Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned EXCELDATEFORMATOFF The EXCELDATEFORMATOFF XLS_Command function is used to turn off Excel date formatting Example to turn off Excel date formatting This will return the Excel date time as text G Eval Rtn XLS Command SQL Socket EXCELDATEFORMATOFF Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned Line Styles for Border Operations Line Styles for Border Operations
122. he stored procedure should be the return value 5 Parameter length When sending to a text or character data type set the parameter length to match the selected parameter field length Set to 0 if the parameter will contain numeric data 6 Variable length parameter string value of up to 4096 characters Set this value when sending an input value to the stored procedure for this parameter Otherwise send the parameter out as blanks If you will be sending numeric data to a stored procedure it must be converted into a string buffer in your RPG program prior to sending the data to the SQL_AddParm function call Example Usage from RPG The sample code listed below creates the return parameters for the stored procedure call calls the RJSSAMPLE2 stored procedure reads only the first returned record closes the recordset and then retrieves the return parameter values 56 GC Ce Ex E Gs Ge GC cx Ce Gs Ex Gr ts cx RPG2SQL Integrator Make sure stored procedure parameter list is empty Eval Rtn SQL ClearParms SQL Socket This parm will hold the stored procedure return code Must always be defined first if the SP return code will be used See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Return adInteger ParmReturnValue Oe t This parm will hold the stored procedure integer return field Returni See RPGSQLH include for parm types
123. hoose 3 Quotes Use double quote values to surround text fields with O No quotes 1 Use double quotes Example Usage 41 RPG2SQL Integrator e Get Delimited Field Type List data from SQL Table E d Eval RtnData SQL GetDelimF1t SQL Socket CC K E ae SQL_GetFldCount The SQL_GetFldCount API is used to retrieve the number of fields in the current recordset Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns Count This function call returns the number of fields found in the current recordset 0 No fields 2 Unknown error Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL GetFldCount Pr Like TInt D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection SQL_PCFileEmpty The SQL_PCFileEmpty API creates an empty PC file on the server PC running the RPG2SQL Integrator server software Returns 0 operation completed normally Not 0 Errors occurred while deleting file Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype 42 RPG2SQL Integrator DSQL PCFileEmpty Pr Like TInt D Socket Like TInt Value D PCFile Like TString Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection
124. ield Returnl See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Returnl adInteger ParmOutput ST TI This parm will hold the stored procedure VarChar return field Return2 See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Return2 adVarChar ParmOutput 30t Call the stored procedure Eval Rtn SQL RunSP SQL Socket rjssamplel Retrieve SP Return Value to return record SP Return Value is always converted to string Eval RtnParm SQL GetParm SQL Socket Return Retrieve Integer Return Value to return record SP Return Value is always converted to string Eval RtnParml SQL GetParm SQL Socket Returnl1 Retrieve VarChar Return Value to return record SP Return Value is always converted to string Eval RtnParm2 SQL GetParm SQL Socket Return2 SQL_RunSPSelect The SQL_RunSPSelect API will run a SQL Server or other database stored procedure with parameters as defined via the SQL_AddParm function Multiple parameters can be added for each stored procedure call if needed This stored procedure call function also expects the stored procedure to return a recordset for processing This function should be used when the called stored procedure will be returning a recordset and potentially return parameters as well 51 RPG2SQL Integrator Note A recordset will be returned from this
125. ield number S S Got first field value in text format AAA A A A A A A A A A o E Eval RtnChar SQL GetFldChrB iC RtnRecord 1 SQL_GetFidNumB The SQL_GetFldNumB API is used to retrieve a numeric field value from the most recent SQL_MoveFirstBuf SQL _MoveLastBuf SQL_MoveNextBuf or SQL_MovePrevBuf call 36 RPG2SQL Integrator Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns The field data value is returned in numeric format The return value is returned in 30 9 digit format Thirty positions with nine decimals The RPG EVAL MOVE or Z ADD operations can be used to move the value to a smaller more focused RPG field size such as 7 2 or 15 2 or 9 0 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL GetF1ldNumB ES 30P 9 D RecordBuffer 4096A Value D Varying D Delimiter 4096A Value D Varying D FldNumber 101 0 Value Parameters 1 RecordBuffer The delimited data record returned from the last SQL_MoveFirstBuf SQL_MoveLastBuf SQL _MoveNextBuf or SQL_MovePrevBuf call 2 Delimiter Record delimiter for returned record Always Use tilde when using SQL_MoveFirstBuf SQL_MoveLastBuf SQL_MoveNextBuf or SQL_MovePrevBuf record operations 3 FldNumber Enter the 1 based relative recordset field number Got first field value in numeric format AAA AAA A A A
126. if an error is returned SETACTIVECELLFONTSUBSCRIPT The SETACTIVECELLFONTSUBSCRIPT XLS_Command function is used to set the subscript value for the active cell SCHER to disable subscript Eval Rtn XLS Command SQL Socket S SETACTIVECELLFONTSUBSCRIPT 0 SE to set subscript Eval Rtn XLS Command SQL Socket E SETACTIVECELLFONTSUBSCRIPT 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLFONTSHADOW The SETACTIVECELLFONTSHADOW XLS_Command function is used to set the shadow value for the active cell SCHER to disable shadow Eval Rtn XLS Command SQL Socket S SETACTIVECELLFONTSHADOW 0 Example to set shadow E Eval Rtn XLS Command SQL Socket g SETACTIVECELLFONTSHADOW 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned 79 RPG2SQL Integrator SETACTIVECELLFONTOUTLINEFONT The SETACTIVECELLFONTOUTLINEFONT XLS_ Command function is used to set the outline font value for the active cell Example to disable outline fonts C Eval Rtn XLS Command SQL Socket ei SETACTIVECELLFONTOUTLINEFONT 0 Example to set outline fonts e Eval Rtn XLS Command SQL Socket SETACTIVECELLFONTOUTLINEFONT 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the AP
127. imited iSeries data record buffer to the active Excel worksheet and then parse the data into multiple cells based on the delimiter passed to the API Up to 256 values can be passed in the DelimRecord buffer with a maximum total length of 30 000 The XLS_SetDelimRc2 API can be called multiple times as needed Returns 0 Cells were set successfully 1 Cells were not set successfully 2 Unknown error 3 Unknown communications failure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned 113 RPG2SQL Integrator RPG Prototype DXLS SetDelimRc2 Pr Like TInt D Socket Like TInt Value D StartRowNum 7P 0 Value D StartColNum 7P 0 Value D Starttell 6A Value D Varying D DelimRecord 30000A Value D Varying D DelimChar 6A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 StartRowNum This parameter is used to specify the row of the starting cell to which the delimited data record will be written Note If this parameter is not used the StartCell value must be specified and the StartRowNum and StartColNum values must be set to 0 3 StartColNum This parameter is used to specify the column of the starting cell to which the delimited data record will be written Note If this parameter is not used the StartCell value must be specified and the StartRowNum and StartColNum values must be set
128. ll failed if an error is returned PAGESETUPRIGHTHEADER The PAGESETUPRIGHTHEADER XLS_Command function is used to set the right part of the header Example to set the right part of the header to the file name e Eval Rtn XLS Command SQL Socket e PAGESETUPRIGHTHEADER 4F P1 Text to use for the right part of the header See Excel documentation for formatting codes that can be used in the header Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPLEFTFOOTER The PAGESETUPLEFTFOOTER XLS_Command function is used to set the left part of the footer Example to set the left part of the footer to the date e Eval Rtn XLS Command SQL Socket C PAGESETUPLEFTFOOTER amp D P1 Text to use for the left part of the footer See Excel documentation for formatting codes that can be used in the footer Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPCENTERFOOTER The PAGESETUPCENTERFOOTER XLS_Command function is used to set the center part of the footer Example to set the center part of the footer to the page number with hyphens around the number d Eval Rtn XLS Command SQL Socket C PAGESETUPCENTERFOOTER amp P 103 RPG2SQL Integrator P1 Text to use for the center part of the footer See Excel docum
129. ll failed if an error is returned RPG Prototype DSQL DBCloseConn Pr Like Tint D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection SQL_BeginTran The SQL_BeginTran API starts an SQL Server database transaction After issuing a begin transaction the SQL_CommitTran or SQL_Rollback procedure must be used to complete a transaction or roll back remove changes from the database Using database transactions is an optional feature of Microsoft SQL Server and some other database types to buffer changes made via INSERT UPDATE and DELETE statements until all changes have been made If any updates fail database changes can be removed in a single rollback operation Once all changes are made a commit operation is performed to save all changes to the database 21 RPG2SQL Integrator Note There is a lot of detail in the Microsoft SQL Server Online Help regarding using database transactions and when they should be used Note Using transactions is not required and only works with Microsoft SQL Server or other ADO ODBC databases that support database transaction control The available RPG2SQL Integrator transaction processing functions are SQL_BeginTran Start a database transaction SQL_CommitTran Commit all changes to the selected database SQL_Rollback Rollback changes made during a database transaction All INSERT UPDATE and DELETE statements that are run after the SQL_BeginTran proc
130. ll failed if an error is returned RPG Prototype DSQL MoveFirsBuf Pr Like T4096String D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection Example Usage 33 RPG2SQL Integrator Go to first record in recordset and get the record buffer data in return Requires only a single server pass for fast performance A SQL_MoveLastBuf The SQL_MoveLastBuf API is used to move to the last record in the currently opened ADO recordset and return a delimited record buffer containing the record data This command is used when high performance recordset reads are required Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns This function call returns a delimited record buffer string or ERROR if an error occurs or if no record was found Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL MoveLastBuf Pr Like T4096String D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection xample Usage A a a eee eee Fe bg E a E E EE EE E E FA EN FE FE E FE Go to last record in recordset and get the record buffer data in return Requires only a single server pass for fast performance SQL_MoveNextBuf The SQL_MoveNextBuf API is used to move to the next record in th
131. mand function is used to select column range using A1 format Example to select columns A D e Eval Rtn XLS Command SQL Socket e SETCOLSELECTA1 A D P1 Start Column Letter A B C D etc P2 End Column Letter A B C D etc Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETROWSELECTA1 The SETROWSELECTA1 XLS_Command function is used to select row range using A1 format Example to select rows 1 3 d Eval Rtn XLS Command SQL Socket E SETROWSELECTA1 1 3 P1 Start Row Number 1 2 3 etc P2 End Row Number 1 2 3 etc Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETCOLSELECTROWCOL 83 RPG2SQL Integrator The SETCOLSELECTROWCOL XLS_Command function is used to select column range using numeric column format Example to select columns A D using relative numeric column position C Eval Rtn XLS Command SQL Socket C SETCOLSELECTROWCOL 1 4 P1 Start Column Number P2 End Column Number Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETROWSELECTROWCOL The SETROWSELECTROWCOL XLS_Command function is used to select row range using numeric format Kampie to select rows 5 10 using relative numeric column
132. ments can be rolled back prior to ending the transaction by using a single call to the SQL_Rollback procedure Once a rollback has been called all changes made after the SQL_BeginTran are removed from the SQL Server database tables After issuing a SQL_CommitTran or SQL_Rollback call you will need to issue the SQL_BeginTran call to start a new SQL Server database transaction Returns 0 ADO SQL commit transaction completed successfully 1 ADO SQL commit transaction failed 2 Unknown error occurred 3 Unknown communications failure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL CommitTran Pr Like Tint D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection Example Usage 23 RPG2SQL Integrator Commit database changes made during R SQL Server database transaction A MM e Eval Rtn SQL CommitTran SQL Socket SQL_Rollback The SQL_Rollback API rolls back removes all changes made since SQL_BeginTran was used to start a database transaction After issuing a rollback command the SQL_BeginTran procedure must be used to start a new database transaction if required Using database transactions is an optional feature of Microsoft SQL Server and some other database types to buffer changes made via INSERT UPDATE and DELETE statements until all changes have b
133. mple to print comments in place d Eval Rtn XLS Command SQL Socket Cc PAGESETUPPRINTCOMMENTS 16 Example to print comments as end notes 106 RPG2SQL Integrator e Eval Rtn XLS Command SQL Socket C PAGESETUPPRINTCOMMENTS 1 Example not to print comments C Eval Rtn XLS Command SQL Socket C PAGESETUPPRINTCOMMENTS 4142 P1 Whether or how to print comments Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPPRINTQUALITY The PAGESETUPPRINTQUALITY XLS_Command function is used to set the print quality Example to set print quality to 1200 dpi on an HP printer C Eval Rtn XLS Command SQL Socket C PAGESETUPPRINTQUALITY 1200 P1 Print quality the value of which may depend on the printer Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPCENTERHORIZONTALLY The PAGESETUPCENTERHORIZONTALLY XLS_Command function is used to set whether to center the data on the page horizontally Example to center the data on the page horizontally d Eval Rtn XLS Command SQL Socket c PAGESETUPCENTERHORIZONTALLY 1 P1 Whether to center data horizontally 1 center 0 do not center Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an er
134. n Fe EEN 129 SQEGStRIAG OWI EE 129 SOLPCRICEMPLY E 129 SOLPG TEE 129 SOUPG FIRE KEE 129 SOLPCRIC CODY E 129 SOLPE FEDELE SA rpa ra AT AA EATA EEA EAE AEEA AEREA E AAT AE ENE AIFA AENA 130 E a E EN UTA EE 130 SQLLASIENMMSO WEE 130 S Olea Toi AO ll ld ali A ee 130 O Eager ee ee A EAEE A E N 130 SEI ter ARATE dais 130 RPG2SQL Integrator Appendix B List of Data Areas oo eee cece enneceneeene cee eeeeeeeeeseeeesaeesaeesaeecaaesaaeeaeseaeseaeseaeeseeeseeeseneesas 131 Appendix C AS 400 Commande sense caaeeesaaeegeeeeceaeeesaaeessaaeseeeeeseeeesaeeneeeenaes 132 Appendix C AS 400 Commande cccccococonocononoccnoncnononananonn conc cr nor 132 Check for IFS File Existence CHRORBIIESS cnn nc nn nono cnn nc ran rana 132 ee EC 132 IFS file name EILNAM oococonnccococccnncccnonoconorccr nn rr 132 Example for CHKOBUIFS ooccoccccnnncconocinococononcnnnnncnn non nnn cnn rr 132 Error messages for CHKOBUIFS coocinccccicccnoccconoccnonancnoncc cnn nana 133 Display OS 400 Level PDRDINFON nano nn nnnccrnn rca rana 133 Eu 133 Example ele PRDINFO coco a Ra aii 133 Error messages for PRDINFO ooocccinncccnccccconccononnnonannnnncc cnn cnn 134 Enter Access Codes DPRUDGEC corn cn nana narran cra rn 134 Parametros 134 Enter security access code GECUDITW cc nnncn narrar 135 Enter data area name DTAARA c0oooncicnncccnonccononinonnnnanc cnn narran rr 135 Enter data area library name D TALID nana nc crac rn 135 Example for D RER 135 Error messages
135. n workbook to a new file name Example to save open workbook to c test2 xls using default Excel format e e Eval Rtn XLS Command SQL Socket FILESAVEAS c test2 xls Example to save open workbook to c test2 dbf using Dbase IV format constant E Eval Rtn XLS _Command SQL Socket G FILESAVEAS c CG TEST DEF a Strim SEDITC x1DBF4 P Example to save open workbook to c test2 dbf using Dbase IV format numeric value e Eval Rtn XLS Command SQL Socket C FILESAVEAS e Cr RBS DER C FELZ Listed below are some of the available Excel formats for saving a file You can determine new output format value numbers as they are available in new versions of Excel by launching Excel recording a new macro and then saving the workbook to the selected file type After recording the macro review the macro code from Excel by pressing Alt F11 to enter VB editing mode Once you determine the new Excel content name you can paste it into the Excel immediate window to see its value 7 ea Vizual Basic Book1 dil Module Code Activelorkbook Savels FileName _ C Documents and Settings DICK My Documents Booki CreateBackup False ActiveWorkbook Saveis FileName _ C Documents and Settings DICK My Documents Booki CeeateBackup Falise ActiveCell FormulaRicl dst Range B6 Select Activecell FormulaRici fds Range B1 Select detivelel1l FormulaRiC1 lda ActiveVork
136. nd function is used to set the footer margin in inches Example to set the footer the margin to 75 inches 105 RPG2SQL Integrator C Eval Rtn XLS Command SQL Socket C PAGESETUPFOOTERMARGIN 75 P1 Size of the footer margin in inches Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPPRINTHEADINGS The PAGESETUPPRINTHEADINGS XLS_Command function is used to set whether to print row and column headings Example to print row and column headings e Eval Rtn XLS Command SQL Socket C PAGESETUPPRINTHEADINGS 1 P1 Whether to print row and column headings 1 print headings O do not print headings Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPPRINTGRIDLINES The PAGESETUPPRINTGRIDLINES XLS_Command function is used to set whether to print cell gridlines Example to print cell gridlines C Eval Rtn XLS Command SQL Socket C PAGESETUPPRINTGRIDLINES 1 P1 Whether to print cell gridlines 1 print gridlines O do not print gridlines Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPPRINTCOMMENTS The PAGESETUPPRINTCOMMENTS XLS_Command function is used to set whether or how to print comments Exa
137. nd prompt For example option 14 brings up the following Read from Excel SQTESTO4 All the source members for this example will start with SQTEST04 RPG2SQL Integrator Required Items for Creating an RPG2SQL Application When using RPG2SQL Integrator you must 1 Be sure RISRPGSAL is in your library list when creating the program or service program 2 Include an H specification that includes the RJSRPGSQL binding directory as well as QC2LE the binding directory for the C runtime library functions as follows BNDDIR RISRPGSQL OC2LE 3 Include the RPG2SQL Integrator copy member at the beginning of your D specifications as follows COPY SOURCE RPGSQLH 4 Convert the IP address of the RPG2SQL Integrator PC component to its dotted IP form as follows E Eval Rtn TCP GetIPEromHost IPAddr DotIPAddr 5 5 Set the multiple buffer flag just before executing SQL_Connect to ensure that all data is returned to your program as follows Cc Calle SQL TCPMultBuff 1 Basic Structure of an RPG2SQL Integrator Application Processing steps Connect to the RPG2SQL Integrator PC component SQL_Connect Connect to the remote database SQL_DBOpenConn Do the work such as reading iSeries tables and sending data to the remote database Disconnect from the remote database SQL_DBCloseConn Disconnect from the RPG2SQL Integrator PC component SQL_Disconnect RPG2SQL Integrator Copy Member The copy member for all RP
138. ng SQL record selection query string Example to select all records from NameAddress table SELECT FROM NameAddress Example Usage 25 RPG2SQL Integrator Run SQL Query to Open ADO Recordset Na AAA E Eval Rtn SQL RunSQLSel SQL Socket E SELECT FROM NameAddress SQL_RunSQLExec The SQL_RunSQLExec API is used to run SQL record INSERT UPDATE and DELETE operations to add data to an ADO ODBC SOL table or modify existing records in a table Returns 0 ADO SQL database operation completed successfully 1 ADO SQL database operation failed 2 Unknown error occurred 3 Unknown communications failure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL RunSQLExec Pr Like Tint D Socket Like TInt Value D SQLString 4096A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 SQLString SQL Insert Update or Delete string Example SQL to insert record INSERT into NameAddress First Last VALUES James Jones Example SQL to update Address field in record UPDATE NameAddress SET Address1 222 Main St where Last Jones and First James Example SQL to delete record DELETE from NameAddress where Last Jones and First James Example SQL to delete all records DELETE from NameAddress Example Usage in RPG program 26 RPG2SQL Integrat
139. ng a SQL_CommitTran or SQL_Rollback call you will need to issue the SQL_BeginTran call to start a new SQL Server database transaction Returns 0 ADO SQL commit transaction completed successfully 1 ADO SQL commit transaction failed 2 Unknown error occurred 3 Unknown communications failure 24 RPG2SQL Integrator Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL_ Rollback Pr Like Tint D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection S Rollback remove database changes made during SQL Server database transaction A a a a a a a a ns a a ae a a a E EEE AE E E e e Eval Rtn SQL Rollback SQL Socket SQL_RunSQLSel The SQL_RunSQLSel API is used to run a SQL record selection query and open an ADO recordset for reading records from a ADO SQL database Returns 0 ADO SQL database operation completed successfully 1 ADO SQL database operation failed 2 Unknown error occurred 3 Unknown communications failure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL RunSQLSel Pr Like Tint D Socket Like TInt Value D SOLString 4096A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 SQLStri
140. nnn nn cnn cn ran nana 142 Header rows HDRROWS A 142 Log messages LOOGMSO ae 142 RPG2SQL Integrator host name HOS Dlicocoicconiciconicoconcccnonnnnnonannno coran cc narran narran 142 Blank row option DBLKPDOW cnn ronca nn 142 Examples for SQLXESIMP oi ma 143 Error messages for SQLXLSIMP ccceesceceeececeeeeeeeeeeeeeeeceaeeeeaaeeeeaeeseeeeesaaeeseaaesseaeeseaeeesaeeeeaeeeenees 144 Export PF to Worksheet GO XL GEN 144 PAPAIN CTS u anaa a Aa E ENEA evades scan bd ace TAEA eE VAT aa AAS AAAA eset ed ATAATA 144 From file INFILE EA A ra 145 From member INMBR ccoo aA NE TAAS ETENE ARI AAT OAA EATA ENEE 145 To workbook OUTXLS a a a n a AA a aaa Aaa E aE AA nat 145 To worksheet OUTSHEE TI 145 Header HEADER ee ee aa 146 Table of Contents Log messages LOOMSOG nn cc nan c cnc cnn nn 146 RPG2SQL Integrator host name HOS Dicoooncconciconicacancccnonnno nana na nono rancn narran rr 146 First record to export RODFIRST coccccnicccnoccconocinonocanancncnoncnn anna cnn cnn rn 146 Last record to export HCDL AGT 146 First field to export FL DEIDRGSTI cnn cnn rana 147 Last field to export FLDLAGTI nn 147 Examples for GOL XL GENP A 147 Error messages for GOL XL GEN 148 Upgrade Settings GO UDO 148 lu CEET 149 Old library OLDEIB EE 149 New library NEW ID 149 Example for SQLUPG coin oe ege Seed Sege eebe Ri 149 Error messages for GOL UDO 149 xi RPG2SQL Integrator RPG2SQL Integrator Wel
141. o retrieve the value of a stored procedure output parameter Output parameters allow stored procedures to return parameters other than the return code to a calling program Return codes and output parameters offer a nice way for SQL Server stored procedures to communicate with a calling program As good programming practice make sure to first read the stored procedure return code and then retrieve all other return parameters as needed via the SQL_GetParm function Returns A 4096 character text buffer that contains the returned stored procedure value or return code If the returned value was a numeric value you must convert the data into numeric format for use in your program or process the returned text value ILE RPG has several built in functions for converting data from text to numeric RPG Prototype DSQL GetParm Pr Like T4096String D Socket Like TInt Value D ParmName 256 Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Parameter name Enter a text name for the SQL Server parameter The parameter name will be used when returning values from the stored procedure call if an output parameter is used This value is required for both input and output parameters Example Usage from RPG The sample code listed below creates the return parameters for the stored procedure call calls the RJSSAMPLE2 stored procedure reads only the first returned record closes the recordset and then retrieves
142. ocket Return2 RPG2SQL Integrator RPG2Excel ILE RPG Service Program Procedures RPG2Excel ILE RPG Service Program Procedures RPG2Excel Introduction In response to several of our RPG2SQL Integrator customers using the RPG2SQL Integrator in conjunction with Microsoft Excel RJS has added the ability to natively generate Excel workbooks from an RPG program Previously customers had to rely on using the outdated Microsoft Jet ODBC and Jet database drivers to write to Excel worksheets While this works for creating simple data worksheets using a database driver does not allow users to take full advantage of native Excel functionality The native RPG2Excel functionality allows RPG2SQL Integrator users to easily generate a brand new workbook create a workbook from a template file add data to a worksheet select a range of cells and apply formatting insert OLE objects into a worksheet or run specialized VBScript scripts when desired Excel functions such as those listed below Cell text formatting Set cell data Set fonts Set underline Set bold Set italics Setting calculation formulas Inserting OLE Objects Insert an org chart Insert a Word document VBScript Macro Capability Running User Defined VBScript Macros XLS_Launch The XLS_Launch API is the second procedure to call after the SQL Connect API is called to connect to the RPG2SQL Integrator PC component This API launches Microsoft Excel on the PC running the RPG2S
143. ocket c SETACTIVECELLVALUE This is a test P1 Cellvalue Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLFROMFILE The SETACTIVECELLFROMFILE XLS_Command function is used to set the active cell value from an ASCII text file 74 RPG2SQL Integrator Example to set value of active cell to the contents of a text file e Eval Rtn XLS Command SQL Socket E SETACTIVECELLFROMFILE Cz TEST TRT P1 Text file name Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLFORMULA The SETACTIVECELLFORMULA XLS_Command function is used to set the formula for the currently selected cell in A1 format Example to set cell formula to summarize cells A2 to A10 e Eval Rtn XLS_Command SQL Socket C SETACTIVECELLFORMULA SUM A2 A10 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLFORMULAR1C1 The SETACTIVECELLFORMULAR1C1 XLS_Command function is used to set the formula for the selected cell in R1C1 format See Excel documentation for details on the R1C1 format No example currently Normally you should use the SETACTIVECELLFORMULA function Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullEr
144. of the RPG2SQL Integrator server software Returns This function call returns a delimited record buffer string or ERROR if an error occurs The delimiter is a tilde The values in the buffer are first the number of rows then the number of columns and finally the used range in A1 format Ex If the worksheet has 4 rows and 10 columns the value returned will be 4 10 A1 J4 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DXLS_ RowColCount Pr Like T4096String D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection Example Usage 110 G Eval SQL Socket SQL Connect 10 1 1 1 S Exit with Error Return TCP Server Connect e TE SQL Socket 999 e Eval INLR On e Return C Endif AAA AAA e e e A A A A E E be E be E be re e e rn re Exit if Excel did not launch kee e e e e e e E E E be E be E bb E E E E E E E be E bb E E E be E bb E E be E be E re E E be be e re re e Eval Rtn XLS Launch SQL Socket e if Rtn lt gt 0 a callp SQL Disconnect SQL Socket e Eval FINLE On e Return Cc Endif kee A A A A A A E be e be E bb E be e be E bb E bb E bb E e E be E E be E re E be re e re e re bi Open existing workbook AAA A A A A A A A A A A A A A A A be E E E bn be E re e Eval Rtn XLS Command SQL Socket Cc FILEOPEN C Test xls AAA AAA A e e e e A E e E be
145. or WI23456 78 e Eval Rtn SOL RunSQLExec SQL Socket E insert into NameAddress E First E Last CG Addressl E Address2 G City E State G dae E Phone E Fax Email E Datel Dollars VALUES E quot James guot E quot Johnson quot G quot 111 Main Skreet guot Cc quot Apt 5 quot Ts i quot Mpls quot Trt quot MN quot E quot S5555 Qquet G quot TITESITISsIIII que Nt A E quot 1222 272 2222 quot G quot janes johnson com quot TT E quot 12 25 2002 quot a E SQL _MoveFirst T The SQL_MoveFirst API is used to move to the first record in the currently opened ADO recordset Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns 0 ADO SQL database operation completed successfully 1 ADO SQL database operation failed Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL MoveFirst Pi D Socket Parameters Like TInt Like TInt Value 1 Socket SQL socket ID for RPG2SQL server connection Example Usage 27 RPG2SQL Integrator x Move to first record Me a a aa a ai a a yy a my a ss se a a a es ma a me mm me e a a G Eval Rtn S
146. orted to the worksheet integer Specify the RRN of the first record to be exported to the worksheet Last record to export RCDLAST Specifies the RRN of the last record of the physical file member to be exported to the worksheet LAST The last record of the member is the last to be exported to the worksheet 146 RPG2SQL Integrator integer Specify the RRN of the last record to be exported to the worksheet First field to export FLDFIRST Specifies the number of the first field of each record of the physical file member to be exported to the worksheet FIRST The first field of each record is the first to be exported to the worksheet 1 8000 Specify the number from 1 8000 of the first field to be exported to the worksheet Last field to export FLDLAST Specifies the number of the last field of each record of the physical file member to be exported to the worksheet LAST The last field of each record is the last to be exported to the worksheet 1 8000 Specify the number from 1 8000 of the last field to be exported to the worksheet a Examples for SQLXLSEXP Note These examples assume you have added RISRPGSQL to your library list Otherwise you must qualify the command with the library RJSRPGSQL Example 1 Simple Command Example SOLXLSEXP INFILE RJSLIBRARY SAMPLEPF OUTXLS C EXCEL FILES SAMPLE XLS This command exports the first member of physical file SAMPLEPF in libra
147. our program or process the returned text value ILE RPG has several built in functions for converting data from text to numeric RPG Prototype DSQLEXtDDS PR 10P 0 D DDSFile 10A Const D DDSLib 10A Const D FldDefs SS Value D FldPtrs Value D FldCount Value DSQLExtRecDDS PR 10P Q D RecordBuffer 4096A Value D RecordPtr Value D ecordSize 10P 0 Value D FldDefs E Value D FldPtrs Value D FldCount ll Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Parameter name Enter a text name for the SQL Server parameter The parameter name will be used when returning values from the stored procedure call if an output parameter is used This value is required for both input and output parameters Example Usage from RPG The sample code listed below creates the return parameters for the stored procedure call calls the RJSSAMPLE2 stored procedure reads only the first returned record closes the recordset and then retrieves the return parameter values 63 RPG2SQL Integrator c Ex Ge EX cx Gs e CS CZ cx Ce Ex cx cee 64 Make sure stored procedure parameter list is empty Eval Rtn SQL ClearParms SQL Socket This parm will hold the stored procedure return code Must always be defined first if the SP return code will be used See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Return adInteger P
148. own communications failure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype 68 RPG2SQL Integrator DXLS_Command Pr Like TInt D Socket Like TInt Value D XLCommand 50A Value D Varying D ParmString 4000A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Excel command operation 3 Excel command parameters For specific example usage see each command below CLEARRANGEALL The CLEARRANGEALL XLS_Command function is used to clear all the cells in the active worksheet Example to clear all cells C Eval Rtn XLS Command SQL Socket CLEARRANGEALL Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned FILECLOSE The FILECLOSE XLS_Command function is used to close the currently selected workbook Close the currently selected workbook E Eval Rtn XLS Command SQL Socket FILECLOSE Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned FILECLOSEALL The FILECLOSEALL XLS_Command function is used to close all open workbooks Close all workbooks Bs Eval Rtn XLS Command SQL Socket FILECLOSEALL Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to det
149. position Eval Rtn XLS B hag Socket S SETROWSELECTROWCOL 5 10 P1 Start Row Number P2 End Row Number Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSCREENUPDATING The SETSCREENUPDATING XLS_Command function is used to set the Excel screen refresh option Disabling screen refreshing can speed up the process when running worksheet operations SCHER to disable screen refreshing Eval Rtn XLS O Socket E SETSCREENUPDATING 0 SE to enable screen refreshing Eval Rtn XLS ees Socket SETSCREENUPDATING 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned 84 RPG2SQL Integrator SETSELECTIONFONTSIZE The SETSELECTIONFONTSIZE XLS_Command function is used to set the Windows font size for the currently selected cell range Example to set font to 15pt e Eval Rtn XLS Command SQL Socket C SETSELECTIONFONTSIZE 15 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETSELECTIONFONTNAME The SETSELECTIONFONTNAME XLS_Command function is used to set the Windows font name for the currently selected cell range Example to set font to Arial C Eval Rtn XLS Command SQL Socket G SETSELECTIONFONTNAME Arial Note You can use th
150. r APIs to determine why the API call failed if an error is returned SETACTIVECELLFONTNAME The SETACTIVECELLFONTNAME XLS_Command function is used to set the active cell font name Example to set the active cell to Aal Cc Eval Rtn XLS Command SQL Socket Cc SETACTIVECELLFONTNAME Arial P1 Font name 75 RPG2SQL Integrator Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLFONTSIZE The SETACTIVECELLFONTSIZE XLS_Command function is used to set the active cell font size Example to set the active cell to 12pt e Eval Rtn XLS Command SQL Socket e SETACTIVECELLFONTSIZE 12 P1 Font size Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLFONTBOLD The SETACTIVECELLFONTBOLD XLS_Command function is used to set the bold value for the active cell Example to disable bold C Eval Rtn XLS Command SQL Socket C SETACTIVECELLFONTBOLD 0 Example to set bold e Eval Rtn XLS Command SQL Socket d SETACTIVECELLFONTBOLD 1 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETACTIVECELLFONTITALIC The SETACTIVECELLFONTITALIC XLS_Command function is used to set the italic value for the active cell
151. r above is required to run the software e TCP IP connectivity must be enabled If TCP IP is not active the software will not work PC Prerequisites e Atleast one PC or network server running Windows NT 2000 XP is required to run the network server component for the iSeries RPG2SQL Integrator Note The iSeries RPG2SQL Integrator Server PC component can be installed on any PC running Windows 95 98 Me NT 2000 XP however Windows NT 2000 XP is recommended for its stability and performance e deally the PC running the iSeries RPG2SQL server PC component should be as fast as possible and should also have a high speed 100 megabit or above network connection available to maximize performance Note A high speed server PC is not required however a high speed PC will help improve overall SQL database access performance e TCP IP must be running on the PC and the iSeries RPG2SQL Integrator Server component must also be installed and running on the PC The RPG2SQL Integrator PC server uses TCP IP port 22001 Client Access 400 IS NOT required e The Microsoft Data Access Components contain all the current ADO ODBC drivers for use by the RPG2SQL Integrator PC server software This software only needs to be installed on the RPG2SQL Integrator Server PC Ideally Version 2 6 or above of the MDAC components should be installed on the Server PC If you re not sure what version of the MDAC components you have loaded download MDAC version 2 6 or
152. re G Eval SQL Socket SQL Connect 10 1 1 1 S Exit with Error Return TCP Server Connect e TE SQL Socket 999 e Eval INLR On e Return e Endif AAA A A A A A E E be E be E be re e e rn re Exit if Excel did not launch kee e e e e e e E E E be E be E bb E E E E E E E be E bb E E E be E bb E E be E be E re E E be be e re re Cc Eval Rtn XLS Launch SQL Socket e if Rtn lt gt 0 e callp SQL Disconnect SQL Socket e Eval FINLE On e Return e Endif AAA AA A A a e E be E be E re E be re e re e re bi Open existing workbook AAA A A A A A A A A A A A A A A A E E E bn be E re e Eval Rtn XLS Command SQL Socket Cc FILEOPEN C 1ATest xls AAA AAA A A A A o Read second row from worksheet columns 1 te 13 Use a semicolon as the delimiter Use row and column values AAA A a A a a a E E E E E a E A A o e Eval Rtnrecord XLS GetDelimRec SQL Socket e ETE AAA AAA A A A A A A be E be e be E be be E bb E bb E bb E E be E be E be E be E be be e e be e re Read third row from worksheet columns ia 1 to 13 Use 2 asterisks as the delimiter Use Al format AAA A A A A A A be E be E be E be E E be E be E bb E be E bb E be E bn be e re e Eval Rtnrecord XLS _GetDelimRec SQL Socket e E E E te eh kee e e e e e re be E be e be E br E E be E E be E bb be E bb E bb E bb E re E bb E bb E re E be re e e re e re id tt Kill Excel k Eval Rtn XLS_Quit SQL Socket K A A e a a A A A S Disconn
153. rned RPG Prototype DSQL MovePrevBuf Pr Like T4096String D Socket Like TInt Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection Example Usage 35 RPG2SQL Integrator Go to previous record in recordset and get the record buffer data in return Requires only a single server pass for fast performance A SQL_GetFldChrB The SQL_GetFieldChrB API is used to retrieve a text field value from the most recent SQL_MoveFirstBuf SQL_MoveLastBuf SQL_MoveNextBuf or SQL_MovePrevBuf call Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns The field data value is returned in text format If an error occurs when retrieving a field the text value ERROR is returned Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL GetFldChrB Pr Like T4096String D RecordBuffer 4096A Value D Varying D Delimiter 4096A Value D Varying D FldNumber 10I 0 Value D Delimiter Parameters 1 RecordBuffer The delimited data record returned from the last SQL_MoveFirstBuf SQL_MoveLastBuf SQL_MoveNextBuf or SQL_MovePrevBuf call 2 Delimiter Record delimiter for returned record Always Use tilde when using SQL_MoveFirstBuf SQL_MoveLastBuf SQL_MoveNextBuf or SQL_MovePrevBuf record operations 3 FldNumber Enter the 1 based relative recordset f
154. ror is returned PAGESETUPCENTERVERTICALLY The PAGESETUPCENTERVERTICALLY XLS_Command function is used to set whether to center the data on the page vertically Example to center the data on the page vertically 107 RPG2SQL Integrator c Eval Rtn XLS Command SQL Socket C PAGESETUPCENTERVERTICALLY 1 P1 Whether to center data vertically 1 center 0 do not center Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPORIENTATION The PAGESETUPORIENTATION XLS_Command function is used to set the page orientation Example to set page orientation to portrait e Eval Rtn XLS Command SQL Socket e PAGESETUPORIENTATION 1 Example to set page orientation to landscape e Eval Rtn XLS Command SQL Socket C PAGESETUPORIENTATION 2 P1 Page orientation Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPDRAFT The PAGESETUPDRAFT XLS_Command function is used to set whether to print graphics Example to print graphics Cc Eval Rtn XLS Command SQL Socket PAGESETUPDRAFT 1 P1 Whether to print graphics 1 print graphics O do not print graphics Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned PAGESETUPPAPERSIZE T
155. rs If you cannot resolve the problem you re experiencing by using the previous troubleshooting methods you should contact RJS Software Systems for advice on solving the problems you re experiencing Appendix A RPG2SQL ILE COBOL Service Program Procedures Appendix A RPG2SQL ILE COBOL Service Program Procedures 121 RPG2SQL Integrator COBOL Working Storage Variables The following variables should be included in all COBOL programs you create that use the RPG2SQL Integrator You can add these variables to a COBOL copy book is desired to avoid redundancy COBOL example usage TO A o AS RPG2SQL Work variables SOL SOCKET SOL IPADDR SOL DATASRC SOL SOLSEL SQL SQLEXEC 0 E 9999999999 COMP 3 0 P 0 E 0 P 0 P 01 SQL RECORD P 0 P 0 iS 0 P 0 E 0 P CG C ACIDO C X 256 C xX 4096 C X 4096 C X 4096 SOL_RTN E SOL_INSTR Cc SQL ADDSTR g SQL_OUTSTR SOL FLDNAME 9999999999 COMP 3 X 4096 X 4096 01 WORKCHAR PIC X 256 5 01 WORKNUM PIC S9 21 V9 9 COMP 3 01 WORKDATE FORMAT TIMESTAMP SQLConnect This function is the COBOL version of the SQL_Connect procedure COBOL example usage kb e be bn be E be E E be E E E E be E E E E Fe E E E E be E E E E be E rn pl S Connect to RPG2SQL PC Server Returne IP socket ID for RPG2SQL BC Server A AAA A A A A be be E e e be e e e CALL PROCEDURE SQLCONNECT USING SQL IPADDR RETURNING SQL SOCKET
156. ry RJSLIBRARY to the active worksheet of workbook C Excel Files Sample xls using the following default settings e Use the first member of SAMPLEPF e Use column headings for the header text e Log warnings and errors e Use the IP address of your 5250 session e Use blanks if the fields are null e Use the first record as the first one to be exported e Use the last record as the last one to be exported e Use the first field as the first one to be exported e Use the last field as the last one to be exported Example 2 More Complex Command Example SQLXLSEXP INFILE SAMPLEPF RJSLIBRARY 147 RPG2SQL Integrator NMBR QUARTER2 UTXLS C EXCEL FILESISAMPLE XLS UTSHEET Sheet2 EADER NAME OGMSG NO OST CL EEN ULLOPT DFT RCDFIRST 6 RCDLAST 15 FLDFIRST 4 FLDLAST 12 GD OO This command exports member QUARTER2 of physical file SAMPLEPF in library RJSLIBRARY to worksheet Sheet2 of workbook C Excel Files Sample xls using the following settings e Use member QUARTER2 e Use field names for the header text e Do not log warnings and errors e Use IP address 1 1 1 1 e Use default values for fields that are null e Use the sixth record as the first one to be exported e Use the 15th record as the last one to be exported e Use the fourth field as the first one to be exported e Use the 12th field as the last one to be exported
157. s from the NameAddr dBase table in file C Program Files RPGSQLSV NAMEADDR DBF Option 16 Read all records from Sample CSV Table NameAddr CSV This menu option reads all records from the NameAddr CSV table in file C Program Files RPGSQLSV NAMEADDR CSV Option 20 Create SQL Server Table NameAdadr and Insert Records This menu option creates a table named NameAddr in the pubs database on the selected SQL Server database server and inserts records from the AS 400 file CUSTOMER Option 21 Illustrate use of Commit Rollback SQL Transaction This menu option creates a table named NameAdar in the pubs database on the selected SQL Server database server and inserts records from the AS 400 file CUSTOMER Note The difference between this option and option 20 is that this sample program illustrates the use of committment control and SQL Server transactions Option 22 Read All Records from NameAddr Table and Display This menu option reads all records from the NameAdar table in the pubs database on the selected SQL Server database server writes the data to a temporary AS 400 file and displays all the record data What s Next After Getting Started You should now have the basics down for using the RPG2SQL Integrator API The next few sections are a reference for each of the iSeries RPG2SQL Integrator commands and available parameters Main Menu Main Menu Introduction to Main Menu This section gives an overview of the Series RPG2SQL Int
158. s returned FILEPRINT The FILEPRINT XLS_Command function is used to print selected worksheets Example to print selected worksheets e Eval Rtn XLS Command SQL Socket C HP LaserJet 2200 Series PCL Example to print two copies of selected worksheets d Eval Rtn XLS Command SQL Socket e HP LaserJet 2200 Series PCL Example to print two copies collated of selected worksheets e Eval Rtn XLS Command SQL Socket e HP LaserJet 2200 Series PCL 72 FILEPRINT lt 6 FILEPRINT 6 2 FILEPRINT 6 2 1 RPG2SQL Integrator P1 Printer name P2 Number of copies P3 Collate 0 do not collate 1 collate Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned FILEPRINTPREVIEW The FILEPRINTPREVIEW XLS_ Command function is used to preview the printed view of selected worksheets Example to preview the printed view of selected worksheets E Eval Rtn XLS Command SQL Socket E FILEPRINTPREVIEW Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned SETRANGEROWCOL The SETRANGEROWCOL XLS_Command function is used to select a cell range based on row column in the worksheet ane to select cell A1 Eval Rtn XLS Command SQL Socket o SETRANGEROWCOL 1 1 aoe to select cells A1 to J1 Row 1 Col1 to Row 1 Col
159. s socket ID is used until a SQL_Disconnect is called to end the connection with the PC server Returns 0 ADO SQL database connection completed successfully 1 ADO SQL database connection failed 2 Unknown error occurred 3 Unknown communications failure 999 Invalid license Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL_DBOpenConn Pr Like Tint D Socket Like TInt Value D DataSource Like TString Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Data Source Formatted ADO data source containing driver type server database userid and password for SQL database Note Not all databases require all parameters 20 RPG2SQL Integrator S Open ADO SQL Server Database Connection to the SQL Server Pubs database e Eval Rtn SQL DBOpenConn SQL Socket Driver SQL Server Server 10 1 1 1 Cc Database Pubs Uid sa C Pwd admin SQL_DBCloseConn The SQL_DBCloseConn API closes a currently opened database connection to the specified ADO SQL database on the RPG2SQL server Returns 0 ADO SQL database connection completed successfully 1 ADO SQL database connection failed 2 Unknown error occurred 3 Unknown communications failure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API ca
160. stFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL GetDelimRcd Pr Like T4096String D Socket Like TInt Value D Delimiter Like TString Value D Quotes Like TString Value Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Delimiter Record delimiter for returned record Can be comma tilde or any other character you choose 3 Quotes Use double quote values to surround text fields with O No quotes 1 Use double quotes ia Get Delimited Record data from SQL Table current record Use comma delimiter and do not surround text fields id with double quotes AAA A A A A A A o e Eval RtnData SQL GetDelimRcd SQL Socket GC tetas Ge Tf errors assume EOF reached or some ce other error occurred We re done reading recs e 1 RtnRecord ERROR C Z add I EOF1 LS C Endif SQL_GetDelimFld The SQL_GeirDelimFld API is used to retrieve a delimited field name list after recordset has been opened Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns This function call returns a delimited field name record buffer string or ERROR if an error occurs or if no record was found Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype 40 RPG2SQL Integrator DSQL GetDelimFld Pr Like T4096String
161. sulting return values are placed into the FldDefArray FldPtrArray and FldDefCount return structures i Get DDS Layout for selected AS 400 File or externally defined data structure Eval INFILE QCUSTCDT Cc Eval INLIB QIWS Eval Rtn SOL ExtDDS INFILE INLIB e a SADDR FldDefArray E SADDR FldPtrArray ic ADDR FldDefCount SQLExtRecDDS The SQLExtDDS API is used to read a DDS defined file layout into memory for use with the SQLExtRecDDS function SQLExtRecDDS will parse the selected record buffer into multiple individual fields based on the DDS field definitions Returns 0 Completed successfully 9999 Unknown error Nonzero Some other error RPG Prototype DSQOLEXtDDS PR 10P 0 D DDSFile 10A Const D DDSLib 10A Const D FldDefs Value D FldPtrs Value D FldCount a Value Parameters 1 DDSFile The name of the AS 400 file layout to retrieve The DDS layout can be used to retrieve a DDS layout for use as an externally defined data structure or to format data for writing to a file 2 DDSLib The name of the AS 400 library for the file layout to retrieve 3 FldDefs A pointer to an array that will hold the field definitions after SQLExtDDS has been called successfully This parameter is passed to SQLExtRecDDS for use 4 FldPtrs A pointer to a field pointer array that will hold the field definition pointers after SQLExtDDS has been called successfully This parameter is passed to
162. t is called to end the connection with the PC server Returns TCP IP Socket ID to use 999 RPG2SQL Server connection was not successful Potentially the PC server component may not be running or the PC server may have a firewall protecting port 22001 RPG Prototype DSQL Connect Py Like TInt D IP Addr Like TDotIP Value Parameters 1 IP_Addr TCP IP Address for RPG2SQL Server PC component Connect to RPG SOL Server a a is a a a a a a Sa a i E a E ss ss a a a i a E a sa ss e E e e c Eval SQL Socket SQL Connect 10 1 1 1 ES Exit with Error Return TCP Server Connect E If SQL Socket 999 Cc Eval INLR On C Return C Endif SQL_Disconnect The SQL_Disconnect API is the last procedure used in an ILE program that will be access SQL data from a remote database The API disconnects the current AS 400 job from the RPG2SQL Integrator PC Server component thus freeing up the socket connection for use by another program RPG2SQL Integrator The disconnect should always be called before a program is ended Note If the SQL_Disconnect procedure is not run a connection to the PC Server will be maintained until the AS 400 job which was accessing the PC server has ended While this is not usually problematic in nature it can be if the SQL_Connect function is called several times throughout an RPG program and the connections are not closed after each connection has completed Returns 0 Completed successfully 2
163. t you specify does not exist it will be created if possible Top Header HEADER Specifies the data that appears in the worksheet s header row COLHDG Specifies that the header text comes from the column headings of the fields of the physical file If the column heading for a field does not exist the text description will be used If the text description does not exist the field name will be used TEXT Specifies that the header text comes from the text descriptions of the fields of the physical file If the text description for a field does not exist the field name will be used NAME Specifies that the header text comes from the names of the fields of the physical file NONE Specifies that there is no header Top Log messages LOGMSG Specifies whether to log warnings and errors that occur when exporting the physical file to the worksheet ES Log warnings and errors NO Do not log warnings or errors RPG2SQL Integrator host name HOST Specifies the IP address of the PC on which the RPG2SQL Integrator PC component is running CURRENT Specifies to use the IP address of your 5250 session character value Specify the IP address of the PC on which the RPG2SQL Integrator PC component is running Top First record to export RCDFIRST Specifies the Relative Record Number RRN of the first record of the physical file member to be exported to the worksheet FIRST The first record of the member is the first to be exp
164. tErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL GetFldChr Pr Like T4096String D Socket Like TInt Value D FieldName 256A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 FieldName Pass the field name for a field from the current recordset ia Get First Name field from current recordset AAA A A A A A A A o e Eval RtnField SQL GetFldChr SQL Socket C First SQL_GetFldNum The SQL_GetFieldNum API is used to a numeric field value from the most recent SQL_MoveFirst SQL_MoveLast SQL_MoveNext or SQL_MovePrev call This API has to go out to the RPG2SQL server to retrieve the field value Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns The field data value is returned in numeric format The return value is returned in 30 9 digit format Thirty positions with nine decimals The RPG EVAL MOVE or Z ADD operations can be used to move the value to a smaller more focused RPG field size such as 7 2 or 15 2 or 9 0 Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL GetFldNum Pr SOP 9 D Socket Like TInt Value D FieldName 256A Value D Varying Parameters 31 RPG2SQL Integrator 1 Socket SQL socket ID for RPG2SQL server connection 2 FieldN
165. teh ees eer edd Eegeregie SE 18 DOL Disconnect titan this ts Glee eege eech de Eege 18 SOL TiMeQut cias lin is 19 SOL DBOPENCON M mitra ada os 20 SOL DBClose Coca a hee hie 21 Cl E e e HN E EE 21 RPG2SQL Integrator vi SL GO MM ea E 22 SOL rell Ee 24 SOL RUNS OES EE EE 25 SQL RUNAS QUE ee EE EE 26 SOL MOVERIRSE ee geed raid BEE 27 SC MOVE EE 28 SO MOVENEX EEN 28 SOL IMOVERICV EES 29 SOL GPF O EEN 30 SOEC aA E eessen A A Eege 30 SOL ek sucia A EAA 31 SE SEET ease eege edel 32 SOL SetS QED Sl IM ii edd ens seed ates BEES 32 SOLU MOVE FIRS Bliss shes gege deeg ege eege ees 33 SOL MoveLastBUtiuisiicia ig EES eene edel Seed 34 SOL MOVENEXMBUfisic seesscdiedeecsibesesbacecd etd A a Ios EE 34 SOL MovePre vB ui A a See EES 35 SOC ul si od 36 SOLE Se O 1 ceesistsccsede sizes A A Edge ees Eed 36 SOL G tEldDatB EEN 37 ET E le EE 38 SOLE G tFldDat2B EE 39 SOL GetDelim RCA rA 5 ans se Ned A Reeg AER 39 SQL Geelen A A A 40 SQL GEMMA A edi nea A Nee eels 41 ET ele EE 42 SQL PCOFIGEMPLY secede ities A ened ea ee Gan ae At aeons 42 SE POFO ES a E E EE Ee TAS 43 SCH REENEN A A E tne a O AS 44 SOL PORO COPY n adie eng aA EEN ee At 44 SOL PCEFIEDEl is creer els hela tiie WIS eG adele EE 45 SOL EastErN OI cut ld ees 46 SQL Fer ug Zeeche iecdgeh a a cite alas 46 el Oe DEE 47 SOL s astRullE a gies cca ugeet ile lite alles 47 SOL QUOC eessen ege geheie a gege eels E eege Ghar be gebiet eege arse Merk see 48 SOL RUNS Reese d e ugeet
166. the return parameter values 58 Ga cx cx C c Ca Ga cx cx cx C Ca Ga G cx cx E cx c E RPG2SQL Integrator Make sure stored procedure parameter list is empty Eval Rtn SQL ClearParms SQL Socket This parm will hold the stored procedure return code Must always be defined first if the SP return code will be used See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Return adInteger ParmReturnValue 02 et This parm will hold the stored procedure integer return field Returnl See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Returnl adInteger ParmOutput Dr Eh This parm will hold the stored procedure VarChar return field Return2 See RPGSQLH include for parm types and ADO data types Eval Rtn SQL AddParm SQL Socket Return2 adVarChar ParmOutput soe 3 Call the stored procedure to return recordset Eval Rtn SQL RunSPSelect SQL Socket rjssample2 Retrieve First Record from Recordset For this test we will only read one record from the authors table in the pubs database Eval RtnRecord SQL MoveFirsBuf SQL Socket Close recordset if we re done reading from the recordset The return parameters are available for reading only after the recordset is closed This is a ADO command object limitation not a RPG2SQL
167. to 0 4 StartCell This parameter is used to specify the starting cell to which the delimited data record will be written Note If this parameter is not used the StartRowNum and StartColNum values must be greater than 0 and this value must be blanks 5 DelimRecord This parameter is used to specify a delimited string value to be placed into the Excel worksheet and parsed into cells based on the selected delimiter value Ex Test1 Test 2 Test3 would be placed into cells A1 B1 and C1 if A1 was passed as the StartCell value 6 DelimChar This character string is the delimiter used to parse the DelimRecord value into fields If blanks are passed a comma is assumed Example Usage 114 RPG2SQL Integrator A Connect to RPG SQL Server and Launch Excel AAA AAA A A A A A A A A bb be E be E be E bb E bb be e be E be E bb be E be be e e re e re G Eval SQL Socket SQL Connect 10 1 1 1 c Exit with Error Return TCP Server Connect e TE SQL Socket 999 e Eval INLR On e Return C Endif AAA AAA A A E be A o CS Exit if Excel did not launch kee e e e e A A A o e Eval Rtn XLS Launch SQL Socket e if Rtn lt gt 0 e callp SQL Disconnect SQL Socket e Eval INLR On e Return C Endif AAA AAA A A A A A A A A A A A A A A A A A A A A A A A E re E E re e re e re or Start new workbook AAA AA A A A A A A A A A A A E E be E E E E be E be E bn be rn re e Eval Rtn XLS Command SQL Socket e FILENEW
168. to see if the RPG2SQL Integrator PC Server is running RPG2SQL Integrator Option 12 Write records to Access database IRPGSQL MDB This menu option deletes all existing records in the NameAddress Access table in the C Program Files RPGSQLSV IRPGSQL MDB access database and then inserts ten duplicate records Option 13 Read all records from Access Database IRPGSQL MDB This menu option reads all records from the NameAddress Access Table in file C Program Files RPGSQLSV IRPGSQL MDB Option 14 Read all records from Sample Excel Workbook NameAddr XLS This menu option reads all records from the NameAddress workbook in file C Program Files RPGSQLSV NAMEADDR XLS Option 15 Read all records from Sample dBase Table NameAddr DBF This menu option reads all records from the NameAddr dBase table in file C Program Files RPGSQLSV NAMEADDR DBF Option 16 Read all records from Sample CSV Table NameAddr CSV This menu option reads all records from the NameAddr CSV table in file C Program Files RPGSQLSV NAMEADDR CSV Option 20 Create SQL Server Table NameAddr and Insert Records This menu option creates a table named NameAdar in the pubs database on the selected SQL Server database server and inserts records from the AS 400 file CUSTOMER Option 21 Illustrate use of Commit Rollback SQL Transaction This menu option creates a table named NameAdar in the pubs database on the selected SQL Server database server and inserts records from the AS 4
169. ts as as sa a a a i a a at at a as a mi e a ce Dates are always returned in ISO timestamp format e Eval Datel SOL GetFlaDat SOL Socket C Datei SQL_SetSQLDelim 32 RPG2SQL Integrator The SQL_SetSQLDelim API is used to set the delimiter that the RPG2SQL Integrator server software should use when returning delimited records Returns 0 Delimiter was set successfully 1 Error occurred while setting delimiter 2 Unknown error 3 Unknown communications failure Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API call failed if an error is returned RPG Prototype DSQL SetSQLDelim Pr Like Tint D Socket Like TInt Value D Delim 5A Value D Varying Parameters 1 Socket SQL socket ID for RPG2SQL server connection 2 Delim Delimiter e Eval Rtn SQL SetSQLDelim SQL Socket C CEFET SQL_MoveFirsBuf The SQL_MoveFirsBuf API is used to move to the first record in the currently opened ADO recordset and return a delimited record buffer containing the record data This command is used when high performance recordset reads are required Note SQL_RunSQLSel must be used to open a recordset before this API can be used Returns This function call returns a delimited record buffer string or ERROR if an error occurs or if no record was found Note You can use the SQL_LastErrNum SQL_LastErrMsg and SQL_LastFullErr APIs to determine why the API ca
170. ure SQLLastErrNum This function is the COBOL version of the SQL_LasrErrNum procedure SQLLastErrMsg This function is the COBOL version of the SQL_LasrErrMsg procedure SQLLastFullErr This function is the COBOL version of the SQL_LasrFullErr procedure SQLConcat The SQLConcat API allows COBOL users to quickly concatenate two variables together and return the resulting string Returns Text string value containing concatenated values SQLConcatFld The SQLConcatFld API allows COBOL users to quickly concatenate a field variable together with field delimiters and record delimiters for use when generating a SQL INSERT UPDATE or DELETE string to add a record to an SQL database 130 RPG2SQL Integrator Returns Text string value containing concatenated values Appendix B List of Data Areas This appendix lists the RPG2SQL Integrator data areas You can use these data areas to control functions Data areas are in the RJSRPGSQL library RJS Software recommends that you avoid modifying data areas that are not in the list below Use these AS 400 commands to modify the data areas e WRKDTAARA The Work with Data Areas command allows you to show a list of data areas from one or more libraries e CHGDTAARA The Change Data Area command allows you to change a data area These are some of the values contained in the RPG2SQL Integrator data areas that you may need to modify Data Area Description LPAR Stores the LPAR Access

RPG2SQL Integrator - RJS Support Center

Contents

Download Pdf Manuals

Related Search

Related Contents