Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Executing arbitrary SQL on a DB2 database?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Executing arbitrary SQL on a DB2 database?
 Login/Join
 
Wep5622
Virtuoso
posted
We're working with DB2 on an IBM iseries server here, and it appears there is no proper tool to execute arbitrary SQL queries on that. Yes, you can execute strsql from a terminal session, but my god, how inconvenient can IBM make this?!? You get 25 lines of space to paste a query in, it hardly has any inline editing capabilities, the thing is a disaster!

So I was wondering, how hard can it be to create a report where I put my SQL query in a large TEXTAREA and send it to the WebFOCUS client to execute?

Creating the launch page is easy. A client-side procedure that executes an arbitrary SQL command isn't hard either, but retrieving the results is what I get stuck on...

Say, I upload this procedure to the client:
APP HOLD SQLREPORTS

-DEFAULT &ARBITRARYSQL = 'SELECT 1 FROM some_table WHERE 1 = 0';

ENGINE DB2 SET DEFAULT_CONNECTION SQL400

SQL DB2 PREPARE SQLOUT FOR

&ARBITRARYSQL.EVAL

END
-RUN

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS ARBITRARYSQLOUTPUT FORMAT FOCUS
END
-RUN


That code creates a master-file (ARBITRARYSQLOUTPUT.mas) that defines the format of the results. However, that result differs per query that I send to this fex! How to handle that?

Perhaps there are better suggestions for tools to send arbitrary SQL to this database server?

This message has been edited. Last edited by: Wep5622,


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Waz
Expert
posted Hide Post
Should be quite easy.

You will need to build a launch page for entering the SQL and running the procedure, but it should work.

The only issue I can think of is passing multiple line out of a text area.

Will the carriage returns cause any issues with the assignment of the & variable.

I think I have done similar in the past (don't know where the code is), and at the time, had to use javascript to break up the textarea into multiple variables, then use a loop to put them in the code.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
K2R400
Member
posted Hide Post
quote:
.... Yes, you can execute strsql from a terminal session, but my god, how inconvenient can IBM make this?!? You get 25 lines of space to paste a query in, it hardly has any inline editing capabilities, the thing is a disaster!

So I was wondering, how hard can it be to create a report where I put my SQL query in a large TEXTAREA and send it to the WebFOCUS client to execute?


You can use iSeries Navigator or other tools like Squirrel using an ODBC/OLE DB or JDBC driver.
No need green screen.


WebFocus 7.7 (DB2 Web Query 1.1.2). IBM i
 
Posts: 11 | Location: France | Registered: January 13, 2012Report This Post
Wep5622
Virtuoso
posted Hide Post
Thanks for mentioning those tools. I had actually looked at both before, but concluded they weren't capable of connecting to a DB2 database.

Who would have thought that iSeries Navigator could send SQL queries to the DB? I just need to figure out how that's supposed to work, as it isn't obvious from the interface at all. Or perhaps our version 5 release 3 can't?

I think Squirrel is probably the better option, once I can figure out what JDBC connect-string I need to use. I did manage to beat IBM's attempts to hide the JDBC driver, let's hope they didn't put up more challenges...


@Waz:
I hadn't realised that newlines in the text area could cause problems in interpreting the variable's contents. Thanks for mentioning that.

The problem I'm facing though is not getting the query to the database, but getting the results back. You need to TABLE FILE SQLOUT to generate a result set on the database server, for which you need a local master file to query the result set from WebFOCUS. That master file would have different contents for each query though, it's never going to be up-to-date. How do you handle that?


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Lucas
Silver Member
posted Hide Post
This will do exactly what you are asking if you use an Windows IIS sever to send the request. 
<%@ LANGUAGE = VBScript%>
<html>
<head>
<%
  if len(request.form("code"))=0 then
    response.write("<title>Focus DB2 Query</title>")
  else
    response.write("<title>"  & time() & "</title>")
  end if
%>
<script type="text/javascript" src="http://yourASPWebSever/menu/clientsortauto.js"></script>

<script>
function loadTemplate()
{
  document.getElementById("code").value="Select * from proddb2.\nFetch First 20 Rows Only\nWith UR";
}
</script>
<style>

body
{
margin:0px;
padding:0px;
}
table
{
background-color:buttonface;
font:.5em Arial;
border-collapse:collapse;
margin:10px;
}

td
{
border:1px solid buttonshadow;
padding:3px;
white-space:nowrap;
}

th
{
background-color:highlight;
color:highlighttext;
border:1px solid #ffffff;
padding:5px;
}
td table
{
display:none;
}
</style>
<body>
<form method="post">
     DB2 SQL Code                        
<input type="button" value="Load Template" onclick="loadTemplate()"><br>
<textarea name="code" style="width:580px;height:600px;overflow:auto;"><%=request.form("code")%></textarea><p>
   File Ouput:<SELECT name=OUTPUT size=1>
                            
                            <OPTION VALUE="EXCEL">EXCEL
                            <OPTION VALUE="PDF">PDF
                            <OPTION VALUE="HTML" selected>HTML
                          
                           </SELECT><p>
<input type="submit" value="Refresh Data">
</form>
<%
  Server.ScriptTimeout=2
  if len(request.form("code"))=0 then
    response.end()
  end if
  output = request.form("OUTPUT")
  before = now()
	Server.ScriptTimeout=3600
	formContents = ""
	formContents = formContents & "SET PAGE = NOPAGE" & vbcrlf
	formContents = formContents & "SQL DB2" & vbcrlf
	formContents = formContents & request.form("code")
	formContents = formContents & ";" & vbcrlf
	formContents = formContents & "TABLE" & vbcrlf
	formContents = formContents & "ON TABLE HOLD AS SQLRESULT" & vbcrlf
	formContents = formContents & "END" & vbcrlf
	formContents = formContents & "TABLE FILE SQLRESULT" & vbcrlf
	formContents = formContents & "PRINT *" & vbcrlf
        formContents = formContents & "ON TABLE PCHOLD FORMAT " &output & vbcrlf
	'formContents = formContents & "ON TABLE PCHOLD FORMAT HTMTABLE" & vbcrlf
	formContents = formContents & "END" & vbcrlf
	formContents = Server.urlencode(formContents)
	
	'response.write(formContents)
	'response.end()
	Set xmlHttp = Server.CreateObject("Microsoft.XMLHTTP")
	call xmlHttp.Open("POST", "http://your_webFocus_sever/ibi_apps/WFServlet", false)
	call xmlHttp.setRequestHeader("Content-Type", "application/x-www-form-urlencoded; charset=UTF-8")
	xmlHttp.Send("IBIF_adhocfex=" & formContents)
  scount = datediff("s",before,now())
  Response.Write("Query Took: " & scount\60 & "m  " & (scount mod 60) & "s")
	Response.Write(xmlHttp.ResponseText)
	Response.End()

	Set xmlHttp = Nothing
  If scount>10 Then
    Response.Write("<script>alert('Results Returned');</script>")
  End If
%>
</body>
</html>


7.6.6 Mainframe
7.6.4 Web Focus
Windows

 
Posts: 45 | Location: Gaffney SC | Registered: March 30, 2007Report This Post
Wep5622
Virtuoso
posted Hide Post
Aha, you're calling the adhoc_fex on the remote machine! Yeah, I suppose that would work. Thanks for the suggestion.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
Jason K.
Master
posted Hide Post
I use iSeries navigator to run sql against an iseries server every day of the week. There's a little trick to bring up a window that allows you to type in sql statements. Just expand your scheme (called library in iseries), expand "tables" then at the bottom pane of that window in blue letters is a link to "Run an SQL Script" click on that and it brings up a window.


Prod: Single Windows 2008 Server running Webfocus 7.7.03 Reporting server Web server IIS6/Tomcat, AS400 DB2 database.
 
Posts: 611 | Registered: January 04, 2007Report This Post
Wep5622
Virtuoso
posted Hide Post
Hmm... We must have a different version: On each "Connection" I only get "Basic Operations" and "File Systems" - no "Library".


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
cgiuda
Silver Member
posted Hide Post
You should be able to install the "Database" portion of the iSeries Navigator throught the File->Install Options->Selected Setup menu item.

It's been around since V4R4.


WebFocus 8104, IBMi (server), Windows 7 (client), iWay 6.1
 
Posts: 32 | Registered: January 27, 2011Report This Post
Waz
Expert
posted Hide Post
One thing about SQLOUT is that its produced from the result set, therefore its dynamic.

As long as you have
TABLE FILE SQLOUT
PRINT *
.
.
END

It should work.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Waz
Expert
posted Hide Post
This is a cutdown versionn of something that was created a long time ago.

It takes an SQL statement, and passes it to a FEX, executed it, and displays the out put in PDF format.

It is set up for Oracle.

The fex is fp_sqlcall, and its set up to reside in an app folder called focalpoint.

-* In EditPlus, place the cursor on the link below and press F8 to run
-* http://localhost:8080/ibi_apps..._ex=FP_SQLCALL&Rnd=1

-DEFAULTS &Step = 'Launch' ;

-GOTO Step_&Step

-Step_Launch

-HTMLFORM BEGIN
<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML//EN">
<html>

<head>
<meta http-equiv="Content-Type" content="text/html">
<title>SQL Direct Execute ....</title>
</head>

<body bgcolor="#FFFFFF" text="#000000" alink="#0000FF">

<form name=sql>
<script language="JavaScript">
function subform() {
  cgiUrl = "/ibi_apps/WFServlet?IBIF_ex=fp_sqlcall&|ECHO=OFF&|Step=Process"

  sqlcode = new String(document.sql.SQL_Code.value);

  var sql_array = sqlcode.split("\r\n");

  cgiUrl += "&|SQL0=" + sql_array.length ;

  for (var loop=0; loop < sql_array.length; loop++)
    {
     varidx  = loop + 1 ;
     escaped_value = escape(sql_array[loop]);
     sql_fixed     = escaped_value.replace(/\+/gi,'%2B') ;
     cgiUrl += "&|SQL" + varidx + "=" + sql_fixed ;
    }

  cgiUrl += "&|rndnum=" + Math.random() ;

  document.location = cgiUrl ;
}
</script>
<table border="0" width="100%">
<tr>
<td valign="top" colspan="2" width="67%"><font size="3" face="Arial">
<strong>Enter SQL commands below</strong></font>
<font size="3"><strong>:</strong></font>
<input type="button" name="submit" value="Run" onclick="subform()"></td>
</tr>
<tr>
<td colspan="2" width="67%"><font size="1" face="Arial">
<textarea name="SQL_Code" rows="20" cols="78">SQL Commands</textarea>
</font></td>
</tr>
</table>
</form>
</body>
</html>
-HTMLFORM END

-EXIT

-Step_Process

-SET &ECHO=ALL ;
-DEFAULTS &WL    = 'WHERE RECORDLIMIT EQ 100 ' 

-DEFAULTS &SHOWSQL = 'Y' 
-*
SET ASNAMES    = ON 
SET XRETRIEVAL = ON 
SET HOLDLIST = PRINTONLY
-*
SET TRACEOFF    = ALL
SET TRACEON     = SQLAGGR//CLIENT
SET TRACEON     = STMTRACE//CLIENT
SET TRACEON     = STMTRACE/2/CLIENT
SET TRACEUSER = ON
-*
-RUN

SQL SQLORA 
-REPEAT SQLEX FOR &Line FROM 1 TO &SQL0
&SQL.&Line
-SQLEX
;
TABLE FILE SQLOUT
PRINT *
-*&WL
ON TABLE HOLD AS HOLDSQLO
END
-*
-*
-RUN
-*
-IF &LINES EQ 0 AND &FOCERRNUM EQ 0 THEN GOTO NoSQLRows ;
-*
DEFINE FILE HOLDSQLO

-REPEAT SQLEX2 FOR &Line FROM 1 TO &SQL0
-SET &SQLTemp = &SQL.&Line ;
-SET &SQLLen  = &SQLTemp.LENGTH ;
-SET &SQLLine = CTRAN(&SQLLen, &SQLTemp, 39, 96, 'A&SQLLen.EVAL') ;
 SQL_Line&Line/A100 = 
 '&SQLLine' ;
-SQLEX2

END
-*
TABLE FILE HOLDSQLO
HEADING
" SQL Output <60> &DATEDMYY "
" "
PRINT *
-SET &REP_FOOT = IF &SHOWSQL EQ 'N' THEN 'ON TABLE SUBFOOT' ELSE 'FOOTING' ;
&REP_FOOT
" "
"Sql Entered: "
" "
-REPEAT SQLEX3 FOR &Line FROM 1 TO &SQL0
"<SQL_Line&Line"
-SQLEX3
" "
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, SQUEEZE=ON, ORIENTATION=LANDSCAPE,PAGESIZE=A4,$
TYPE=REPORT, FONT=ARIAL, SIZE=8, $
TYPE=DATA, STYLE=NORMAL, $
TYPE=TITLE, STYLE=BOLD, $
TYPE=FOOTING, STYLE=ITALIC, SIZE=10, COLOR=RED, $
TYPE=SUBFOOT, STYLE=ITALIC, SIZE=10, COLOR=RED, $
TYPE=TABFOOTING, STYLE=ITALIC, SIZE=10, COLOR=RED, $
ENDSTYLE
END
-RUN
-GOTO EndSQL
-*
-NoSQLRows
DEFINE FILE T139
 TEXT/A30 WITH T139_GENERIC_GROUP = 'No Data Returned' ;

-REPEAT SQLEX2 FOR &Line FROM 1 TO &SQL0
-SET &SQLTemp = &SQL.&Line ;
-SET &SQLLen  = &SQLTemp.LENGTH ;
-SET &SQLLine = CTRAN(&SQLLen, &SQLTemp, 39, 96, 'A&SQLLen.EVAL') ;
 SQL_Line&Line/A100 = 
 '&SQLLine' ;
-SQLEX2

END

TABLE FILE T139
HEADING
" SQL Output <60> &DATEDMYY "
PRINT TEXT
IF RECORDLIMIT EQ 1
FOOTING
" "
"Sql Entered: "
" "
-REPEAT SQLEX3 FOR &Line FROM 1 TO &SQL0
"<SQL_Line&Line"
-SQLEX3
" "
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, SQUEEZE=ON, ORIENTATION=LANDSCAPE,PAGESIZE=A4,$
TYPE=REPORT, FONT=ARIAL, SIZE=8, $
TYPE=DATA, STYLE=NORMAL, $
TYPE=TITLE, STYLE=BOLD, $
TYPE=FOOTING, STYLE=ITALIC, SIZE=10, COLOR=RED, $
TYPE=SUBFOOT, STYLE=ITALIC, SIZE=10, COLOR=RED, $
ENDSTYLE
END
-*
-EndSQL


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Wep5622
Virtuoso
posted Hide Post
It looks like the SQL plug-in is not available from our AS400 servers.

Fortunately, a colleague helped me set up a system ODBC connection, with which I succeeded in setting up Squirrel! Things like these are hard to get right when you're not very familiar with either Windows or AS400...

Thanks for the pointers!


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Executing arbitrary SQL on a DB2 database?

Copyright © 1996-2020 Information Builders