× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Bruce,

If you look at the second level text for the message in the job log, you'll see that you have to run the CRTSQLPKG command to fix this. You need to run it on the system your program is running on and it will create an SQL package in the library the program was compiled into on the remote system.

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Bruce Collins
Sent: Tuesday, April 08, 2008 10:42 AM
To: RPG programming on the AS400 / iSeries
Subject: Embedded SQL Problem

Ok you SQL Guru's I am having an issue I hope can be fixed. I am trying to pull data from another i5/OS partition. I have created the RDBDIRE as AAALAWPI. Below is some test code I am using. When I connect my SQL State is '00000' but when I do the prepare I get '51002 - The package corresponding to an SQL statement execution request was not found.'

Here is the actual SQL command create by the RPG program : 'Select DVENVNDGRP,DVENVENDOR,DVENVNDVNM From LAWAPP8DB.DBAPVEN Where dvenvndvnm like 'RING%' Order by dvenvndvnm

If go into STRSQL, Connect to the Remote Database and execute it I get the following results:

DVENVNDGRP DVENVENDOR DVENVNDVNM
---------- ---------- ------------------------------
000001 ACT 1835 RING POWER CORP
000002 ACT 12 RING POWER CORPORATION
000003 ACT 5170 RING POWER CORPORATION
000004 ACT 9110 RING POWER CORPORATION
000005 ACT 9111 RING POWER CORPORATION

If I run it from an RPG program I get SQL Errors.


Here is the code:


H dftactgrp(*no) actgrp(*caller)
H Option(*NoDebugIO : *SrcStmt : *ShowCpy)


D SqlResult ds
D DVENVNDGRP 4a
D DVENVENDOR 9a
D DVENVNDVNM 30a


d FetchNxt pr
d CloseCur pr

D custname s 30 inz('RING')
D custnbr s 9
d Sql s 1000a
D UserName s 10a inz('USERID')
D PWD s 10a inz('PASSWORD')

D
d Select c const('Select DVENVNDGRP,-
d DVENVENDOR,DVENVNDVNM -
d From LAWAPP8DB.DBAPVEN ')
d
d
d Where c const('Where dvenvndvnm like ')
d Where1 c const('Where dvenvendor = ')
d Order c const('Order by dvenvndvnm')
d rQuote c const(X'7D')
d pcent c const('%')
D
/free


exsr sqlbld;
exsr runsql;
*inlr = *on;



//-------------------------------------------------------------------------------------------
//-------------------------------------------------------------------------------------------
begsr sqlbld;
sql = *blank;
sql = %trim(sql) + Select;

sql = %trim(Sql) + ' ' + where + rQuote + %trim(CustName) +
pcent + rQuote;

sql = %trim(sql) + ' ' + Order;
endsr;




begsr runsql;

/end-free
c/Exec Sql
c+ set option naming = *sql , commit = *None, closqlcsr = *endmod
c/End-Exec
C
C/EXEC SQL
C+ RELEASE ALL
C/END-EXEC
C
C/EXEC SQL
C+ CONNECT TO AAALAWPI USER :USERNAME USING :PWD
C/END-EXEC
C

c/Exec Sql
c+ Prepare S1 From :Sql
c/End-Exec

c/Exec Sql
c+ Declare C1 Cursor For S1
c/End-Exec

c/Exec Sql
c+ Open C1
c/End-Exec SQL
/free


dow 1 = 1;
FetchNxt();

if sqlstate <> '00000';
CloseCur();
leave;
endif;

enddo;



endsr;

/end-free
pFetchNxt b
dFetchNxt pi
c/Exec Sql
c+ Fetch Next From C1 Into : SqlResult
c/End-Exec
p e

pCloseCur b
dCloseCur pi
c/Exec Sql
c+ Close C1
c/End-Exec
p e


Thanks in advance

Bruce "Hoss" Collins
Project Leader/System i Administration
AAA Cooper Transportation
(334) 671-3106
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.