473,242 Members | 1,514 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 software developers and data experts.

Retrieving data from an AS/400 stored procedure

Hello to All,
I'm trying to retrieve records from AS/400 in an VBA application.
So, I've made an RPG program, then a stored procedure wchich calls that RPG
program, and finally some VBA code to call the stored procedure and retrieve
data from AS/400.
The problem is, that when I finally run my VB code, it just hangs.
But when I call the same stored procedure from "pure" SQL - it works
perfect. (I evaluate Aqua Data Studio 3.7)

What I find interesting, is that when I execute through my VBA any simple
SQL statement like "SELECT * FROM MYLIB.MYTABLE" it works and returns data.
It works also when I call an SQL-only stored procedure. Problems begin, when
I try to call a stored procedure wchich calls an external RPG program.
(Please see code below)

For now there are two things I suppose to be reasons for my problem:
1) I should declare a cursor in the stored procedure and leave it open for
the client application, but I just don't know how to do this together with
the "EXTERNAL NAME" clause;
2) I make a mistake in the connection string in VBA - here I also have no
idea what I do wrong

Well, the third case could be, that I do everything correct, but there are
new undocumented bugs in Microsoft Access ;-)

I hope I described my problem transparent enough.
Did anybody have such problems?

Below are the three sources: stored procedure, RPG program and VBA code.

Thanks for any help!

Greetings from Poland
WW

************************************************** ******************
******* STORED PROCEDURE *******
************************************************** ******************
CREATE PROCEDURE MYLIB.P3(
in SOC char(1),
in ENS char(1),
in CUST char(10)
)
RESULT SETS 1
LANGUAGE RPG
SPECIFIC MYLIB.P3
NOT DETERMINISTIC
CONTAINS SQL
EXTERNAL NAME bpl400.sp3
PARAMETER STYLE GENERAL ;

************************************************** ******************
******* RPG PROGRAM *******
************************************************** ******************
0001.00 FSGSELCJ1IF E K DISK
0002.00 *
0003.00 IV99999 E DSSGSELCJ1 1
0004.00 C Z-ADD1 ROW 10
0005.00 C *ENTRY PLIST
0006.00 C PARM SOC 1
0007.00 C PARM ENS 1
0008.00 C PARM CUST 10
0009.00 C MOVE CUST KBCUST 100
0010.00 C KEY1 KLIST
0011.00 C KFLD SOC
0012.00 C KFLD ENS
0013.00 C KFLD KBCUST
0014.00 C KEY1 CHAINSGSELCJ1 50
0015.00 C *IN50 IFEQ *OFF
0016.00 C 1 DO 1 I 30
0017.00 C I OCUR V99999
0018.00 C READ SGSELCJ1 99
0019.00 C END
0020.00 C*GOOD NUMBER
0021.00 C/EXEC SQL SET RESULT SETS ARRAY :V99999 FOR :ROW ROWS
0022.00 C/END-EXEC
0023.00 C ELSE
0024.00 C*BAD NUMBER
0025.00 C END
0026.00 C RETRN

************************************************** ******************
******* VBA PROCEDURE *******
************************************************** ******************

Public Sub ASquery()

Dim CNN As New ADODB.Connection
Dim CMD As New ADODB.Command
Dim RST As New ADODB.Recordset
Dim i, dl, ri As Integer
Dim sHeader, sRecord As String

'=======================
'Set and open connection
'=======================
CNN.Open "Provider=MSDASQL.1;USER=USR;" & _
"PASSWORD=PASS;Persist Security Info=False;" & _
"Data Source=MYSOURCE"

With CMD
.ActiveConnection = CNN
.CommandText = "call MYLIB.P3('1', '1', '0028351372')"
End With

'=======================
'Fetch data into Recordset
'=======================
Set RST = CMD.Execute

'=======================
'Retrieve column headers
'=======================
i = 0
sHeader = ""

For i = 0 To RST.Fields.Count - 1
sHeader = sHeader & RST.Fields.Item(i).Name & vbTab
Next i
Debug.Print sHeader

'=======================
'Retrieve data fields
'=======================
While RST.EOF = False
For ri = 0 To RST.Fields.Count - 1
sRecord = sRecord & RST.Fields.Item(ri) & vbTab
Next ri
Debug.Print sRecord
If RST.EOF = False Then RST.MoveNext
sRecord = ""
Wend

End Sub
Nov 13 '05 #1
6 6698
"Wojciech Wendrychowicz" <o.*********@spytaj.po.prostu> wrote in message
news:cb**********@atlantis.news.tpi.pl...
Hello to All,
I'm trying to retrieve records from AS/400 in an VBA application.
So, I've made an RPG program, then a stored procedure wchich calls that RPG program, and finally some VBA code to call the stored procedure and retrieve data from AS/400.
The problem is, that when I finally run my VB code, it just hangs.
But when I call the same stored procedure from "pure" SQL - it works
perfect. (I evaluate Aqua Data Studio 3.7)

What I find interesting, is that when I execute through my VBA any simple
SQL statement like "SELECT * FROM MYLIB.MYTABLE" it works and returns data. It works also when I call an SQL-only stored procedure. Problems begin, when I try to call a stored procedure wchich calls an external RPG program.
(Please see code below)

For now there are two things I suppose to be reasons for my problem:
1) I should declare a cursor in the stored procedure and leave it open for the client application, but I just don't know how to do this together with the "EXTERNAL NAME" clause;


My experience with the AS400 or ISeries and stored procedures is that you
must open a cursor if you want the SP to return records.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #2
> My experience with the AS400 or ISeries and stored procedures is that you
must open a cursor if you want the SP to return records.


So that's what I thought... Could you give me some hints about the syntax
and how to do this? I know how to leave an open cursor in an SQL-only
procedure, but have no idea when I use the "EXTERNAL NAME" clause.
Could you give me some examples?
--
WW
Nov 13 '05 #3
"Wojciech Wendrychowicz" <o.*********@spytaj.po.prostu> wrote in message
news:cb**********@nemesis.news.tpi.pl...
My experience with the AS400 or ISeries and stored procedures is that you must open a cursor if you want the SP to return records.


So that's what I thought... Could you give me some hints about the syntax
and how to do this? I know how to leave an open cursor in an SQL-only
procedure, but have no idea when I use the "EXTERNAL NAME" clause.
Could you give me some examples?


Sorry, I am not familiar with the EXTERNAL NAME clause. All of the SPs I
have used on the AS400 have been SQL procedures. I'll ask around though as
we have a few RPG guys. I just don't know if they have ever created an RPG
stored procedure.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #4
> Sorry, I am not familiar with the EXTERNAL NAME clause. All of the SPs I
have used on the AS400 have been SQL procedures. I'll ask around though as we have a few RPG guys. I just don't know if they have ever created an RPG stored procedure.


Yes that would be great! Thanks in advance!
If I only could I would use an SQL procedure. But the table I have to
retrieve data from counts over 10 million records, so an SQL selection with
one simple condition takes about 3-5 minutes. RPG needs only 600milliseconds
:-)

--
?Chr(119)& Chr(095)& Chr(119)& Chr(101)& Chr(110)& _
Chr(100)& Chr(114)& Chr(121)& Chr(099)& Chr(104)& _
Chr(111)& Chr(119)& Chr(105)& Chr(099)& Chr(122)& _
Chr(064)& Chr(112)& Chr(111)& Chr(099)& Chr(122)& _
Chr(116)& Chr(097)& Chr(046)& Chr(102)& Chr(109)
Nov 13 '05 #5
Hi,

Now that's what I call a "munged" e-mail address!
If spammers want to reach you, there definitely going to have to work for
it! :-)

Brilliant!
Don
"Wojciech Wendrychowicz" <o.*********@spytaj.po.prostu> wrote in message
news:cb**********@nemesis.news.tpi.pl...
--
?Chr(119)& Chr(095)& Chr(119)& Chr(101)& Chr(110)& _
Chr(100)& Chr(114)& Chr(121)& Chr(099)& Chr(104)& _
Chr(111)& Chr(119)& Chr(105)& Chr(099)& Chr(122)& _
Chr(064)& Chr(112)& Chr(111)& Chr(099)& Chr(122)& _
Chr(116)& Chr(097)& Chr(046)& Chr(102)& Chr(109)

Nov 13 '05 #6
Well, hmm... sometimes I just have stupid ideas ;-)

Now that's what I call a "munged" e-mail address!
If spammers want to reach you, there definitely going to have to work for
it! :-)

Nov 13 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: PinkGuava | last post by:
Hi, I have a T-SQL stored procedure that returns both output parameters and a recordset. How do I retrieve them in my ASP script? As far as I know, the ADO Command object can be used to retrieve...
0
by: Mark Oueis | last post by:
Is there any way I can retrieve the result set of a Stored Procedure in a function. ALTER FUNCTION dbo.fn_GroupDeviceLink ( @groupID numeric ) RETURNS @groupDeviceLink TABLE (GroupID numeric,...
7
by: luna_s | last post by:
i've been playing with this code to return an ID on an insert - it seems a little dirty/hacky to me stored proc goes something like this (ive stripped out uneccesary sql code - the proc works...
1
by: stjulian | last post by:
If inside a stored procedure, there a SELECT statement to return a recordset and another SELECT to set the value of an output parameter (as in SELECT @OutValue = Name FROM table WHERE pkid=5),...
3
by: Susanne Klemm | last post by:
Hello! I use a procedure to insert a new row into a table with an identity column. The procedure has an output parameter which gives me the inserted identity value. This worked well for a long...
11
by: Nemisis | last post by:
Hi everyone, sorry if this post gets really long, i just wanna make sure i fully explain what i am trying to do. I am new to OOP and .net 2.0, so if this is obvious, i am sorry. I have wrote a...
5
by: Sanjay Pais | last post by:
I have a table with over 1.3 million rows. I am retrieving only 20 at a time using the with - over clauses In query analyser, the data is retrieved in under a second. When retrieving using the...
9
by: vertigo262 | last post by:
Hello All, I am trying to create an insert stored procedure with an @@identity output. need help This didn't seem dificult when I attempted it, but I can't for the life of me get the identity...
4
by: smartin | last post by:
Hi, I'm having problem retrieving data from an SQL stored procedure. I tried debugging but it wont give a the reason for the error. it just throws an exception after executing cmd.ExecuteNonQuery...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.