Re: SQL5011 - Why am I getting this? -- RPG400-L

Host structure array &1 not defined or not usable? Seems like an odd
error message to get for that. Here's the second level:

Message . . . . : Host structure array &1 not defined or not usable.
Cause . . . . . : Host structure array &1 was specified in a blocked FETCH,
a blocked INSERT, or a SET RESULT SETS statement. The host structure array
is either not defined or is not usable for one of the following reasons:...

Something else is going on. I just put that statement in a source
member and compiled it...no problem.

You do have the SET OPTION before any SQL statement, right?

On Thu, May 12, 2011 at 12:20 PM, Siegrist, Dawn (Penske)
<dawn.siegrist@xxxxxxxxxx> wrote:

I tried adding the set option at the beginning of my program.
Exec SQL
SET OPTION DATFMT = *ISO;

But when I tried to compile, it still failed.

The only thing that will work is if I prompt the compile command and
change the date format parameter to *ISO. I prefer to do that inside of
the program when I can because I might forget it the next time I have to
compile the program, but apparently that is the only way the SQL
precompiler will recognize it.

Dawn Siegrist

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Morgan, Paul
Sent: Wednesday, May 11, 2011 5:23 PM
To: RPG programming on the IBM i / System i
Subject: RE: SQL5011 - Why am I getting this?

From the Embedded SQL programming manual:

If the date and time format and separator of date and time subfields
within the host structure are not the same as the DATFMT, DATSEP,
TIMFMT, and TIMSEP parameters on the CRTSQLRPGI command (or in the SET
OPTION statement), then the host structure array is not usable.

You are using two date fields in the data structure. Could this be the
problem?

Paul Morgan

Principal Programmer Analyst
IT Supply Chain/Replenishment

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Siegrist, Dawn
(Penske)
Sent: Wednesday, May 11, 2011 5:00 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL5011 - Why am I getting this?

I am trying to run an SQL statement to load 4 records into a multiple
occurrence data structure. According to what I see in the manuals, I
have everything set up correctly, but it is giving me an SQL5011 - Host
structure array HVWEEKDS not defined or not usable. I can't seem to
figure out how to fix this. Does anyone have any ideas?

Here is my code.

//----------------------------------

// Define Entry ProtoType

//----------------------------------

D EntryList PR ExtPgm('PMSBCUR2R')

D 4

D 2

D #PmWeekRcds Likeds(PmWeekDS) Dim(4)

//----------------------------------

// Main Procedure Interface

//----------------------------------

D EntryList PI

D PiDist 4

D PiLoc 2

D PmWeekRcds Likeds(PmWeekDS) Dim(4)

//---------------------------------------------------------

// Stand Alone Fields

//---------------------------------------------------------

D $$Corp S 4

D $$Flag S 1

D $$Dnam S 24

D $$Admn S 4

D $$Regn S 4

D $$Area S 4

D $$Cdis S 4

D NumRows S 3P 0

D RowCount S 3P 0

//---------------------------------------------------------

// Data Structures

//---------------------------------------------------------

D PmWeekDs DS Inz

D pwWeekFrom 10

D pwOverDays 7P 0

D pwOverMtrs 7P 0

D pwIncpOver 7P 0

D pwTotFleet 7P 0

D pwTotDue 7P 0

D pwDefectPct 3P 0

D pwCurrPct 3P 0

D pw4weekPct 3P 0

D hvWeekDs DS Occurs(4) Inz

D hvWeekFrom D

D hvWeekTo D

D hvOverDays 7P 0

D hvOverMtrs 7P 0

D hvIncpOver 7P 0

D hvTotFleet 7P 0

D hvTotDue 7P 0

D hvDefectPct 3P 0

D hvCurrPct 3P 0

//---------------------------------------------------------

// Prototypes

//---------------------------------------------------------

D SPV1LOCN PR ExtPgm('SPV1LOCN')

D $$Corp 4

D PiDist 4

D PiLoc 2

D $$Flag 1

D $$Dnam 24

D $$Admn 4

D $$Regn 4

D $$Area 4

D $$Cdis 4

//---------------------------------------------------------

// M A I N P R O G R A M

//---------------------------------------------------------

/FREE

// Get district information to use in query

$$Corp = '*ANY';

SPV1LOCN ($$Corp :PiDist :PiLoc :$$Flag :$$Dnam :$$Admn

:$$Regn :$$Area :$$Cdis);

// Get last 4 weeks of records for location from weekly

// PM defect summary file

Exec SQL

Declare SummCsr Cursor For

Select START_DATE,

END_DATE,

OVR_DAYS_CNT,

OVR_MTRS_CNT,

INCOMPLETE_CNT,

TOT_FLEET,

TOT_DUE,

TOT_DEF_PCT,

PCT_FLEET

from PMSSUMMWF

where CORP = :$$Corp and

AREA = :$$Area and

CTRL_DIST = :$$Cdis and

DIST = :PiDist and

LOCN = :PiLoc

order by END_DATE desc ;

Exec SQL

Open SummCsr;

Exec SQL

Fetch SummCsr For 4 Rows Into :hvWeekDs;

NumRows = 0;

For RowCount = 4 downto 1;

%Occur(hvWeekDs) = RowCount;

If ( hvWeekFrom <> *Loval );

NumRows += 1;

//Load data to data structure to send to caller

pwWeekFrom = %Char(hvWeekFrom : *USA);

pwOverDays = hvOverDays;

pwOverMtrs = hvOverMtrs;

pwIncpOver = hvIncpOver;

pwTotFleet = hvTotFleet;

pwTotDue = hvTotDue;

pwDefectPct = 100 - hvDefectPct;

pwCurrPct = 100 - hvCurrPct;

// Get 4-week rolling currency percentage

Exec SQL

Select PCT_FLEET

Into :pw4weekPct

From PMSSUMM4WF

Where CORP = :$$Corp and

AREA = :$$Area and

CTRL_DIST = :$$Cdis and

DIST = :PiDist and

LOCN = :PiLoc and

END_DATE = :hvWeekTo;

pw4weekPct = 100 - pw4weekPct;

PmWeekRcds(RowCount) = PmWeekDs;

EndIf;

EndFor;

// Send results back - used if called from stored procedure

Exec SQL

SET RESULT SETS ARRAY :PmWeekRcds FOR :NumRows ROWS;

Return;

Thank you.

Dawn Siegrist

Penske

Systems Analyst

T 610-775-6243

F 610-603-8402

E Dawn.Siegrist@xxxxxxxxxx <mailto:Dawn.Siegrist@xxxxxxxxxx>

GoPenske.com

PO Box 563

Reading PA 19603-0563 USA

Check out our blog for news and event info:

Blog.GoPenske.com

--
This is the RPG programming on the IBM i / System i (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.

--
This is the RPG programming on the IBM i / System i (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.

--
This is the RPG programming on the IBM i / System i (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.