Link to home
Start Free TrialLog in
Avatar of Wailland
Wailland

asked on

Stored Procedure

How can I write a storded procedure to an AS400 directly from Access or VB.
I'm familiar with using stored procedures via ADO objects.
Avatar of MFalcon
MFalcon

It might be easier to install client access so that you get an installation of ODBC for AS/400 if this is not already installed.  
Add a new connection in control panel/ODBC 32.

Link an AS/400 physical file via Access using the new ODBC link you created.

Use the new link as if its a table in Access.

If your AS/400 is not a fast server model you should use the MS Access Make Table to create a local copy of the data first, so as to prevent unnecessary overhead on the AS/400.

Create the Stored procedure on the AS/400 as follows

pace this in a source member and use
RUNSQLSTM CL command to execute this SQL statement.
 

CREATE PROCEDURE YOURLIB/GETNAMESET ( IN CSTNO INTEGER )

  RESULT SET 1

  LANGUAGE SQL

BEGIN

   DECLARE C1 CURSOR FOR
   SELECT * FROM DATALIB/CUSTFILE
     WHERE CUSTNUM = CSTNO;

   OPEN C1;

   SET RESULT SETS CURSOR C1;
END;

The next VB program consists of a form with two input boxes and a command button to call the procedure.
 
Text2 is the control that the user enters the number into.
 
Text1 is the control that the customer name is returned to.
 
Command1 is the button to pass the parameter and call the procedure - and handle the results.

VB Declarations:

Dim env1 As rdoEnvironment
Dim conn1 As rdoConnection
Dim strsql As String
Dim ps As rdoPreparedStatement
Dim rs As rdoResultset

'Form Load event code

rdoEngine.rdoDefaultCursorDriver = rdUseOdbc

Set env1 = rdoEngine.rdoCreateEnvironment(" ", " ", " ")

strsql = "DSN=xxxxx;UID=xxxxxx;PWD=xxxxxx;"

Set conn1 = env1.OpenConnection(" ", rdDriverNoPrompt, False, strsql)

conn1.QueryTimeout = 3600


The user enters a customer number into Text2, and the following code is run when the command button is clicked:


strsql = "call SPLIB.GETNAMESET(?)"

Set ps = conn1.CreatePreparedStatement("NAMETEST", strsql)

ps(0).Direction = rdParamInput

ps(0).Value = CInt(Text2.Text)

Set rs = ps.OpenResultset(rdOpenStatic)

If rs.RowCount = 0 Then

    Text1.Text = "Not Found"

Else

    Text1.Text = rs!CNAME

End If

Text2.SelStart = 0

Text2.SelLength = Len(Text2.Text)

ps.Close

 
Avatar of Wailland

ASKER

Can i Create the Procedure programmatically from VB? If yes, please help
Nope.

I have provided you with the answer for creating a DB2/400 stored procedure and you have rejected it. The AS/400 is NOT a PC based operating system. Its database and operating system are proprietry
Avatar of Asta Cu
This question is still open today and may just have been overlooked.  If you have been helped here, you may choose the comment which served you as the accepted answer to then grade and close so that others seeking help on this may find it in the PAQ database.  Or, due to the age of this question, if you have difficulty directly converting a comment to the accepted answer, as the expert to post as answer that best served your needs which will then return to you for acceptance.

If more information is required, please post details here.

Thank you,
Asta
Greetings.
 
This question is still open today, perhaps it was overlooked or just lost in the volumes.  Please return to this question to update it with comments if more information is needed to get your solution.  If you've been helped by the participating expert(s), you may just convert their comment to the accepted answer and then grade and close.  If an answer has ever been proposed you may not have this option to accept the comment as answer, if that is the case, ask the specific expert you wish to award to post an answer.     This benefits others who then search our PAQ for just this solution, and rewards the experts who have provided information.  A win/win scenario.  Please DO NOT accept this comment as an answer,  it is merely a reminder.
 
If you wish to award multiple participants, you can do so by creating a zero point question in the Community Support topic area, include this link and tell them which experts you'd like to award what amounts.  If you'd like to delete this question, use the same process as above, but explain why you think it should be deleted.  Here is the Community Support link:   https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
You can always click on your profile to see all your open questions, in the event you also have other open items to be resolved.   If your number of Questions Asked is not equal to the number of Answers Graded, choose to VIEW question history, and you'll quickly be able to navigate to your open items to close them as well.
 
I've had excellent help from experts-exchange through the years and find the real key to getting what I need is to remain active in all my questions, responding with results to suggestions until my solution is found, and recommend that highly.
 
Thank you very much for your responsiveness, it is very much appreciated.  
":0)  Asta
SECOND REQUEST, ADMINISTRATION ADVISED.
                                                     
Your options are:

1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you
wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with
details and we'll respond with the process.
4. Delete the question. Again, please comment to advise the other participants why you wish to do this.

For special handling needs, please post a zero point question in the link below and include the question
QID/link(s) that it regards.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt

Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer
process.  https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Please click you Member Profile to view your question history and keep them all current with updates
as the collaboration effort continues, to track all your open and locked questions at this site.  If
you are an EE Pro user, use the Power Search option to find them.

PLEASE DO NOT AWARD THE POINTS TO ME.  

------------>  EXPERTS:  Please leave any comments regarding your closing recommendations if this item
remains inactive another seven (7) days.  Also, if you are interested in the cleanup effort, please
click this link https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643

Thank you everyone.

Moondancer
Moderator @ Experts Exchange

P.S.  For any year 2000 questions, special attention is needed to ensure the first correct response
is awarded, since they are not in the comment date order, but rather in Member ID order.  
ASKER CERTIFIED SOLUTION
Avatar of Moondancer
Moondancer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial