The page you requested does not exist. A search for style agstyle css resulted in this page.

Compile RPG programs using SQL without SQL license on your iSeries

Anonymous's picture
Submitted by Anonymous on Sat, 07/08/2006 - 23:00

Posted in forum

How do I compile RPG programs using SQL without SQL license on my iSeries?

‹ The "Form" function Using embedded SQL with IceBreak ›
Bent Rønne's picture

Re: Compile RPG programs using SQL without SQL license on your i

Submitted by Bent Rønne on Sun, 07/09/2006 - 23:00.
Technical Director/CIO. System & Metode A/S. Denmark.

You can use the Structured Query Language - SQL in several ways in IceBreak.

The classic way is to incorporate embedded-SQL into your code with Embedded SQL

But IceBreak also lets you use SQL-result sets directly in your application. IceBreak has two build-in functions that return either a HTML-table or a complete XML document directly into your response object.

SQL to a HTML-table

If you just want to list the content of a SQL select statement (the result set) , then the SQL_Execute_HTML build-in function is the easiest way to incorporate database information into your application. This function formats the data values in respect to the data types in the result set.

The result is placed directly in the response-object just where the function is executed: 

 	<%
	D*Name++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++++++++++++++++++++++++++
	D Error           s               N
	D SqlCmd          s           1024    varying
	D MaxRows         s             10i 0
	/free
	%>
	<html>
	 <head>
	 <meta http-equiv=Content-Type content="text/html; charset=windows-1252">
	 <link rel="stylesheet" type="text/css" href="/System/Styles/IceBreak.css"/>
	</head>
	<body><%

	  *inlr   = *on;
	  SqlCmd  = 'Select * from product';
	  MaxRows = 1000;
	  Error   = SQL_Execute_HTML(sqlcmd : maxrows); 

	  if (Error); %>
	     <% = getLastError('*MSGTXT') %><br>
	     <% = getLastError('*HELP')   %><%
	  endif;
	%>
	</body>
	</html> 

Try the sample yourself - click here

The SQL_Execute_HTML build-in function returns logical *ON if an error occurs, which sets the LastError property. You can then retrieve the last error with GetLastError('*MSGTXT | *HELP | *MSGID') which returns the last error in a string.

The SqlCmd parameter can be any SQL select statement up to 32760 bytes long.

The MaxRows parameter determines the maximum number of rows allowed in the result set.

SQL to a XML-document

The SQL_Execute_XML build-in function returns the root element of an XML-document called <resultset>. Each row of the result set is called <row> and has an attribute named after the SQL column name.


However, you have to fill in the XML header with the version 1.0 and the encoding type of your choice - i.e. combine this function with SetContentType() . And ofcause you can append a formatting style sheet that reformats the XML into a XHTML document or what ever.

The result is placed directly in the response-object just where the function is executed: 

 	<%
	D*Name++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++++++++++++++++++++++++++
	D Error           s               N
	D SqlCmd          s           1024    varying
	D MaxRows         s             10i 0
	/free
	  SetContentType ('application/xml; charset=windows-1252');
	  %><?xml version="1.0" encoding="windows-1252" ?>
	  <%
	  *inlr   = *on;
	  SqlCmd  = 'Select * from product';
	  MaxRows = 10000;
	  Error   = SQL_Execute_XML(sqlcmd : maxrows);

	  if (Error);
	     %><error msg="<% = getLastError('*MSGTXT') %>"
	              help="<% = getLastError('*HELP') %>"/><%
	  endif; 


	%> 

Try the sample yourself - click here

You can read more at http://icebreak.org

—