I have an occassional WebFOCUS user who uses a variety of other applications and languages to access data. He recently has seen some benefits with using WebFOCUS. However, he would like to use Visual Basic to call WebFOCUS to collect an 'answer set' that will be used in his VP application. He stated he would be fine creating a function/subroutine to call WebFOCUS.

Seeing how I know nothing of VB and have never heard of anyone doing this, I thought I would post this.

So, has anyone ever done this or know whether such a thing is possible?

Many thanks in advance.

Harry
cleveland@temple.edu

Harry,

If you want an 'answer set' to be returned to a VB app, then what you need is an iWay ODBC connector talking to an iWay adapter/server. You minimally need to license the connector and, depending on how your WebFOCUS Reporting Server is licensed, you may need an iWay adapter license.

Please call your local branch for more information and pricing.

Also, please post your product suite, release, and platform in your profile signature.

Thank you Ginny for your quick reply. The only problem I see with your suggestion is - MONEY. Who has it these days?

I am trying to hold on to FOCUS and WebFOCUS at the University. Management has recently purchased COGNOS in the hope of replacing all we have done in last 15 years.

Using a 'free' VB technique to call WebFOCUS would have been another opportunity to show the value of keeping IBI products.

If anyone has any free ideas, I would really appreciate it.

Thanks again,

Harry
cleveland@temple.edu

Harry, I feel so badly for you. What can your management be thinking? WF is far and away much better than Cognos.

Please contact your local branch and tell them the situation. I am sure that they would want to save the account and the maintenance dollars that it generates. They could possibly set up a trial for you. There is also a .net interface. Can't hurt to try. And if your company has money to spend on Cognos ...

Best of luck to you.

Yes Harry...how sad. I recently converted Cognos reports to WebFocus since WF is our choice now...Users were so excited to see the new reports that were generated. Yep call IBI.

Harry,

Can you call a URL with the VB you are trying to use? If so the process is a straight forward URL of the form -

http[s]://[yourserver]/ibi_apps/WFServlet?IBIAPP_app=[your app folder]&IBIF_ex=[the focexec]&Parm1=[variable value]&.... etc.

Where &Parm1 is any variable(s) you need to pass to the called fex.

T

Harry could do that but then how would he retrieve the answer set? He needs to do fetches to get the rows.

I was going to make the same comment as Ginny but she beat me to it. Using a URL would be a good way around the problem, but it would require a two step process. First, executing the URL with an ON TABLE PCHOLD FORMAT databasetype/flatfile which would extract the data to a local "table" and second, use Access or whatever local query tool to retrieve the records as an answer set.

Although money is always the issue especially in higher ed, the license that would be required (iWay ODBC connector)for this single user would be minimal. And if he has a license for DevStudio, he already has what he needs. The big HOWEVER - if he is creating an app that is distributed, each vopy of the distributed app would need its own license of the connector.

Any here's my two cents - having working in higher ed and having seen Cognos vs. WF in my previous IBI life, IMHO, Cognos is a poor fit for a higher ed environment. Both for distribution/licensing, end-user use, and functionality. Of course that's a generalization because not all environments are equal, but I would definitely get your local IBI rep on this to help "the cause."

Missed that bit Ginny

T

If it is possible to make a URL call from VB to WF then it probably is also possible for VB to digest whatever is returned from the URL call directly. Perhaps looking into PCHOLD FORMAT XML or WP or even COMMA would be a good fit. The key here is returning the results of the URL call with the PCHOLD command and then VB can digest what is returned. Of course the whole answer set is returned all at the same time.

I know vb can consume webservices.

Does setting up a .fex as a webservice require any new licensing? I don't know as have never played with them in wf although I can see a good few applications for them!

Setting up the fex as a webservice does require additional licensing, but as you mention, there are some very good applications for this.

And Mickey's suggestion to go the XML route could be an interesting solution.

If I understand what you are wanting to do correctly this is pretty straight foward.
You can send your request to WF using the Server.CreateObject("Microsoft.XMLHTTP") VB method and retreive your response using VB's xmlHttp.ResponseText

Here is a sample.

	Server.ScriptTimeout=3600
	formContents = "IBIF_adhocfex="
	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 & "LIST *" & vbcrlf
                  formContents = formContents & "ON TABLE PCHOLD FORMAT " &output & vbcrlf
	'formContents = formContents & "ON TABLE PCHOLD FORMAT HTMTABLE" & vbcrlf
	formContents = formContents & "END" & vbcrlf

	Set xmlHttp = Server.CreateObject("Microsoft.XMLHTTP")

	call xmlHttp.Open("POST", "http://corpweba04/cgi-bin/ibi_cgi/webapi.dll", false)
	call xmlHttp.setRequestHeader("Content-Type", "application/x-www-form-urlencoded; charset=UTF-8")
	xmlHttp.Send(formContents)

	Response.Write(xmlHttp.ResponseText)
	Response.End()

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

Thank you all for your replies.

I have passed this information on to the VB coder to see if any of your comments inspire him to greatness.

Thanks again.

Harry

Okay, in my original message, I stated that I didn't know anything about VB. I still don't. It's just one of many things I do not know anything about ... and at my age, I am okay with that. By the time I could become an expert, I will be retired, dead or the language will not exist anymore.

The VB coder liked your responses, however, he was not delighted with having to pass user ids and passwords to access WebFOCUS.

He asked me to consider granting him (i.e., VB) permission by IP address. To be honest with you all, this is just one more thing I know nothing about. Up until the moment the VB'r asked me, I was okay with that.

I have WebFOCUS locked down with more security techniques than anyone would care to hear me explain. When I call IBI tech support to discuss security issues, they cringe and regret not having taken the day off.

Has anyone ever heard of granting access to WebFOCUS via an IP address instead of a user id and password. Can both techniques co-exist.

Thanks in advance ...

old and confused [but I am generally okay with that]

I am not an expect on this matter but I suggest setting up a "Production" account that all job's using VB, login in using those credintials.
You can send those using the xmlHttp.Send method...

If the VB application is going to be ran from a production web server then I would not see a problem allowing that server access via IP, domain name or something like that but only for a production server that has user access granted by approiate user credentials.

my .02

If your server is running with security on, the only way around the user/id password is to hard-code them into your configuration. Then that opens up the connection for others to use as well. My suggestion is to pass the appropriate credentials in the app, but additionally use IP filtering. You don't list platform/version information (I would suggest you update that in your profile as soon as possible) so I can't really get too specific. Search techsupport for IP restriction and you should get some good hits. Maybe add an additional service for use by your VB apps that are restricted to certain IP addresses.

We have a number of application where we have set up dummy accounts. In fact, the VB coder has access set up for him like this already in non WebFOCUS applications. I will probably recommend something to that effect to him.

I have (and will be) stuck in a PL SQL class for the week so I have not been able to discuss why the IP addres access is so important to him.

In between napping in class, I have been sharing your comments and suggestions with the VB'r.

Thanks for all of the input.

I have updated my personal profile... whether I have done so correctly is another story.

I'm a little confused about what your VB programmer needs from all this. There's a reporting tool included with VB if he just needs to create reports and it's not that shabby.

If he needs to get data, he can use ODBC to gather any data that webfocus can. Using ADO, a VB application can access any data source from a lotus spreadsheet to Oracle tables. Surely he's familiar with ADO over Oracle as you're taking a class in PL/SqL.

I've done quite a bit of work in vb, oracle 9i pl/sql and .net before I watched a self-help movie called Office Space. Now I spend most of my time "zoning out".

quote:

The only problem I see with your suggestion is - MONEY. Who has it these days?

Ask your president Bush for money....he just orders to print some if he needs it...LOL..

Reading all this I see the same discussion I have with some of our programmers. They all need the data and would like to build reports them self with .Net or VB. When I proof to them that building reports with WF (and graphs) they seem to be a bit unhappy, because this tool (WF) is not the language they understand.

Convincing them is not easy. I try to convince the end user.

I can use data from several sources (SQL, RMS, flatfiles etc) and combine it in on report within a time no .NET programmer can. VB and .Net are great tools, but not for the final reporting.

quote:

Originally posted by FrankDutch:
I can use data from several sources (SQL, RMS, flatfiles etc) and combine it in on report within a time no .NET programmer can. VB and .Net are great tools, but not for the final reporting.

Let's not lose sight that you could use Focus's ease of reporting and combine it with something like flash driven graphs which are light years ahead of any Focus graphing that I have seen. You could not combine these two technoligies without a tool like VB (C#, JSCRIPT, etc.) and have a final report that would be "sexier" than anything canned by either technology standing alone. I use Focus/SQL/JScript/VB/C#/PHP depending on the needs and server at the time and which one is faster or better to meeting a solution. Also I am not allowed to connect to databases for reporting using SQL directly here at work (will not give me a UserId and password) but they will let me read using Focus. Don't ask me why, so I use Focus mixed with VB or JScript to get my data present it in various appication be it web or desktop.

Tony was correct on how he stated how to pass the variables to webfocus


Public Const strPath As String = "URL;http://localhost/cgi-bin/ibi_cgi/" or /ibi_apps/WFServlet
Public Const Executable_Name As String = "ibiweb.exe?IBIF_ex=" or "WFServlet?IBIF_ex="
Public Const FocExec_Name As String = "test.fex"


Next you will have to figure out how you want to pass the variables from VB to WebFocus. He should already have the variables set so you can then pass them after the constants above. Hopefully this helps you out a little bit more.

Thank you all for your replies and comments. The money issues are too painful to talk about right now. I agree there are better ways to do what the user wants; however, I have a real need to bend over backwards for users right now. The more value that can be perceived for WebFOCUS, the longer I will have a job.

The user has made a lot of progress with VB submitting a request through WebFOCUS on our server, having it run on the mainframe and bring back data to his VB application.

The only problem is ... WebFOCUS surrounds the returned output with html tags which the user does not want. I have tried a lot of things to get rid of the tags; however, none of them are close to what the user wants.

My last suggestion was to put the following in his FOCUS code:

ON TABLE SET STYLE *
TYPE=REPORT, GRID=OFF, FONT=DEFAULT-FIXED,$

and I am still waiting to hear from him.

Out of desperation, I even tried ...

ON TABLE PCHOLD FORMAT ALPHA

and others.

This returned:

(FOC35804) BAD STATUS CODE (-32) RECEIVED FROM SERVER EDAUSER
(FOC1400) SQLCODE IS -32 (HEX: FFFFFFE0)
(FOC1405) SQL PREPARE ERROR.

Since my little magical bag of tricks is empty, can any of the experts tell me how I might return data without the html tags?

Thanks,

Harry

A -32 is a 'userid already in use' error. You have your WebFOCUS reporting server set up such that a single id can be logged on only once. Was there a previous process running that never finished?

BTW, I would remove all styling from the program and leave the FORMAT ALPHA in there. You could also try FORMAT XML.

on table pchold format comma


brings back a comma seperated file with no other formatting and what I use for such applications.