Re: CREATE PROCEDURE

[Ken.Slaugh@xxxxxxxxxx]

The issue actually involves a RPG/LE program. This program is accessed
through the Stored Procedure. I gave up trying to get Crystal Reports to
return the information for me. I was simply using CR to execute the RPG
program. I'm assuming that the RPG program would have to return a
'recordset' to be compatible with CR.

I was however able to get VB to execute the proc, thus my RPG program....
Here's the code:

Dim sConn As String
Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRS As ADODB.Recordset
  sConn = "DSN=TEST_SQL"
  Set oConn = CreateObject("ADODB.Connection")
  oConn.Open sConn
  Set oCmd = CreateObject("ADODB.Command")
  With oCmd
    .ActiveConnection = oConn
    .CommandText = "KEN.TESTSQL"
    .CommandType = adCmdStoredProc
    .Properties.Refresh
    .Prepared = True
    .Parameters.Append oCmd.CreateParameter("PBUFFER", adChar,
adParamInputOutput, 32000)
    End With
  oCmd("PBUFFER") = ""
  Set oRS = oCmd.Execute
  Set oRS = Nothing
  DHTML.DocumentHTML = oCmd("PBUFFER")
  oConn.Close
  Set oConn = Nothing

My library 'KEN' contains a stored procedure named 'TESTSQL'. It invokes an
RPG program and passes a single In/Out parameter of 32,000 bytes named
PBUFFER

You should see RPG create the HTML, it's impressive. :)

Ken Slaugh (707) 795-1512 x118
Chouinard & Myhre, Inc.
CA/400 Certified Specialist
iSeries Network/MSE Administrator
http://www.cm-inc.com/


                                                                                
                                    
                    Vern Hamberg                                                
                                    
                    <vhamberg@xxxxxxxxxxxxxxxx        To:     Midrange Systems 
Technical Discussion                 
                    ology.com>                        <midrange-l@xxxxxxxxxxxx> 
                                    
                    Sent by:                          cc:                       
                                    
                    midrange-l-bounces@xxxxxxx        Subject:     Re: CREATE 
PROCEDURE                             
                    e.com                                                       
                                    
                                                                                
                                    
                                                                                
                                    
                    02/21/2003 10:17 AM                                         
                                    
                    Please respond to Midrange                                  
                                    
                    Systems Technical                                           
                                    
                    Discussion                                                  
                                    
                                                                                
                                    
                                                                                
                                    




What you assume is not what I'm seeing. We use Crystal 8.5 for our products

(I said 7 below - was incorrect.)

As I described, when I want to create a new report, a Data Explorer
appears.
This has a list of connection types, including ODBC.
Opening that item (click the "+") displays a list of ODBC data sources
defined on your machine.
They all have a "+", so they can be opened, as in Win Explorer.
When opened, what is shown are 2 categories - Tables and Stored Procedures,

each with another "+" sign.
Opening the Stored Procedures item gives you a list of all stored
procedures available to you, probably according to library list.
Selecting a stored procedure and clicking on the Add button puts that
stored procedure into the report.
Bringing up the Field Explorer, the stored procedure appears under the
Database Fields section.
Opening up the stored procedure displays a list of the fields available
from the result set (or sets) that are defined in that stored procedure.

So Crystal treats stored procedures just like tables, since each produce
result sets with identifiable fields. Crystal does the low-level stuff
needed to get this information. And it makes the appropriate call, as
needed. All in all, very handy and easy.

Cheers

Vern

At 11:46 AM 2/21/2003 -0600, you wrote:
>It does use *SQL naming. I'm not aware of a way to change that, but that
>doesn't mean there isn't one. Afaik, result set columns are not published
>anywhere. The definitions need to be retrieved by the client when the
>result set is active. Return parameter values however, are an entirely
>different matter. They are part of the procedure's call template, and
>would be available to Crystal at design time. I suspect that in order to
>use a stored procedure with Crystal, you'll need to base the report on a
>crystal query, and use that to call the procedure. I haven't battled with
>Crystal for several years though.
>
>At 19:04 02/20/2003, Vern Hamberg wrote:
>>Just for fun, this looks like *SQL naming syntax. Is it possible your
>>ODBC data source wants *SYS syntax?
>>
>>As far as the message goes, it does look like a file-related matter. But
>>Crystal might not care - it displays the fields from one of my stored
>>procedures, in the "Database fields" section of the field browser. Go
figure.
>>
>>Vern


_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.