Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     sql stored procedures
Go
New
Search
Notify
Tools
Reply
  
sql stored procedures
 Login/Join
 
Member
posted
Hi,I'm having trouble with what seems the simplest of tasks.

-SET &ECHO = ALL;
SET TEMPERASE = ON
SQL SQLMSS SET SERVER CHSPLUS
SET SQLENGINE = SQLMSS
SQL SQLMSS SET USER TESTSQLUSERS
-RUN
SQL SQLMSS EX SPQUERYPHONE '%&PHONENUMBER|%';
TABLE FILE SQLOUT
PRINT *
ON TABLE NOTOTAL
ON TABLE SET ONLINE-FMT &WFFMT
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *.............

Because of the way Excel formats data as it pleases (turns what you want alpha to numeric), I'm trying to create an interim file to add characters to the front of the data. I can't get anything to work. I've tried creating a hold file and defining the new field I want. I've tried to save the file so I can mess with it outside of Focus. I continually get errors that the field I'm trying to redefine doesn't exist or the file I'm trying to create can't be found.

Can someone actually adjust the above code for me to make it 1. save to hold or as file, redefine a field, and print fields; 2. simply save as c:\file.xls.

My problems seem to be a function of executing stored procedures (I think).

Thanks for any help!
 
Posts: 14 | Registered: September 02, 2004Reply With QuoteReport This Post
<Pietro De Santis>
posted
The problem may be that you're missing the ON TABLE HOLD statement. Try adding

TABLE ON TABLE HOLD AS SQLOUT
END

Extract from the document, "iWay Data Adapter Administration for UNIX,Windows, OpenVMS, OS/400, OS/390 and z/OS Version 5 Release 2.3"

http://documentation.informationbuilders.com/masterinde...tadapt_admin_523.pdf

or

http://tinyurl.com/4ctov

quote:

Calling a Microsoft SQL Server Stored Procedure Using SQL Passthru
Microsoft SQL Server stored procedures are supported via SQL Passthru. These procedures
need to be developed within Microsoft SQL Server using the CREATE PROCEDURE
command.
Example Calling a Stored Procedure
The supported syntax to call a stored procedure is shown below.
ENGINE SQLMSS
EX SAMPLE PARM1,PARM2,PARM3...;
TABLE ON TABLE PCHOLD
END
The server supports invocation of stored procedures written according to the following
rules:
� Only scalar input parameters are allowed, but not required.
� Output parameters are not allowed, except for optional cursor type parameters.
� A cursor used for output parameters must be defined with:
� The TYPE statement in a PROCEDURE.
� An associated record layout of the answer set to be returned.
� The cursor must be opened in the procedure.
� No fetching is allowed from the output parameter cursors in the stored procedure. The
Data Adapter for Microsoft SQL Server fetches the answer set.
� Any error messages must be issued using the RAISE APPLICATION ERROR method.
 
Reply With QuoteReport This Post
Member
posted Hide Post
Like so?
SQL SQLMSS SET SERVER CHSPLUS
SET SQLENGINE = SQLMSS
SQL SQLMSS SET USER TESTSQLUSERS
-RUN
SQL SQLMSS EX SPQUERYPHONE '%&PHONENUMBER|%';
TABLE FILE SQLOUT
PRINT *
TABLE ON TABLE HOLD AS SQLOUT2
END
DEFINE FILE SQLOUT2
CLAIMNO/A24= '#'|CLAIMNUMBER;
END
TABLE FILE SQLOUT2
PRINT CLAIMNO

Gets these errors:
0 ERROR AT OR NEAR LINE 3 IN PROCEDURE SQLOUT FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: TABLE
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
0 ERROR AT OR NEAR LINE 18 IN PROCEDURE __T00000FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: CLAIMNUMBER
0 ERROR AT OR NEAR LINE 21 IN PROCEDURE __T00000FOCEXEC *
(FOC003) THE FIELDNAME IS NOT RECOGNIZED: CLAIMNO
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT
 
Posts: 14 | Registered: September 02, 2004Reply With QuoteReport This Post
<Pietro De Santis>
posted
Maybe like so:

SQL SQLMSS SET SERVER CHSPLUS
SET SQLENGINE = SQLMSS
SQL SQLMSS SET USER TESTSQLUSERS
-RUN
SQL SQLMSS EX SPQUERYPHONE '%&PHONENUMBER|%';
TABLE ON TABLE HOLD AS SQLOUT
END

DEFINE FILE SQLOUT
CLAIMNO/A24= '#'|CLAIMNUMBER;
END
TABLE FILE SQLOUT
PRINT CLAIMNO
END

This actually creates the HOLD file for the SQL procedure:

TABLE ON TABLE HOLD AS SQLOUT
END
 
Reply With QuoteReport This Post
Member
posted Hide Post
I copied your code to avoid typos. I've deleted any other extraneous format stuff...just the pure code.

Errors received are:
0 ERROR AT OR NEAR LINE 1 IN PROCEDURE SQLOUT FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: @@@@0007
BYPASSING TO END OF COMMAND
0 ERROR AT OR NEAR LINE 12 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND
0 ERROR AT OR NEAR LINE 15 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

Thanks for your persistent help. I am new with WebFocus and feel jinxed at times.

Lisa
 
Posts: 14 | Registered: September 02, 2004Reply With QuoteReport This Post
<Vipul>
posted
use like this following your embedded sql - make sure you end the sql with ;(semi-colon)


TABLE FILE SQLOUT
PRINT TRNCODE
ON TABLE SET ASNAMES ON
ON TABLE HOLD FORMAT ALPHA AS DETBAICD
END

also the file name for hold file should not be more than 8 chars.

Vipul
 
Reply With QuoteReport This Post
Member
posted Hide Post
Thanks to you both. Still get pretty much the same errors. I've finally opened a case with Tech Support.
 
Posts: 14 | Registered: September 02, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Your problem may be that WF cannot find the stored procedure. If the connection_attributes do not identify a default database, then WF doesn't know what db to search. Try adding the database name to the prefix of the sp call. I'm doing as follows:

SQL SQLMSS
EX ESSDEMO.dbo.dumpEdbbas '&IN'
;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TMPOUT
END
-*
-RUN

TABLE FILE TMPOUT
PRINT *
END

And its all good. If I remove the 'ESSDEMO' (the database name) in the procedure call, then I get an error about table names not found.
 
Posts: 1100 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
Member
posted Hide Post
Thanks everyone! What it was is too simple. Upper/Lower case. I don't think SQL cares about case but the hold file produced in WF had ClaimNumber. I was using CLAIMNUMBER. There's a big DUH. The fex is working fine.

However, does anyone know how to set to mixed text. I am not within report painter..just editing .fex as text.
 
Posts: 14 | Registered: September 02, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Select "View/Options..." from the menu when editting. Check "Mixed Case".
 
Posts: 1100 | Location: Toronto, Ontario | Registered: May 26, 2004Reply With QuoteReport This Post
Member
posted Hide Post
And I thought I'd looked everywhere!!
Thanks much.
Lisa.
 
Posts: 14 | Registered: September 02, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     sql stored procedures

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.