|
Here is a sample program. the first describe initializes the SQLDA with the select information (i.e. number of variables,etc.) The second describe actually allocates the storage needed by setting the SQLN appropriately. Thanks, Tommy Holden -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Tyra Brodsky Sent: Friday, June 17, 2005 12:16 PM To: RPG programming on the AS400 / iSeries Subject: RE: SQLDA question I don't think so. How do I allocate storage? -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Holden Tommy Sent: Friday, June 17, 2005 10:14 AM To: RPG programming on the AS400 / iSeries Subject: RE: SQLDA question did you allocate storage??? Thanks, Tommy Holden -----Original Message----- From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Tyra Brodsky Sent: Friday, June 17, 2005 11:30 AM To: rpg400-l@xxxxxxxxxxxx Subject: SQLDA question I am trying to use the SQLDA to dynamically create SQL statements. I am getting the following error when running the program Message ID . . . . . . : SQL0822 Severity . . . . . . . : 30 Message type . . . . . : Diagnostic Date sent . . . . . . : 06/16/05 Time sent . . . . . . : 12:26:47 Message . . . . : Address in the SQLDA not valid. Cause . . . . . : The SQLDA contains an address, SQLDATA value, or SQLIND value in entry number 1 that is not valid. The incorrect address or value is type 1. -- Type 1 indicates that the SQLDATA address is not valid. -- Type 2 indicates that the SQLIND address is not valid. -- Type 3 indicates that the SQLDA address is not valid. -- Type 4 indicates that the row storage area is not large enough. -- Type 5 indicates that the indicator area for a blocked FETCH statement is not large enough. -- Type 6 indicates that the SQLDATA field was not initialized to a value in a REXX procedure -- Type 7 indicates that the SQLIND field was not initialized to a value in a REXX procedure. -- Type 8 indicates that the SQLDATAL address is not valid. Recovery . . . : For types 1, 2, 3, or 8, change the address in entry 1 to a valid address. For types 4 and 5, allocate enough area for all of the rows being requested. For types 6 and 7, initialize the SQLDATA or SQLIND fields to a valid value. Try the request again. Technical description . . . . . . . . : When an SQLDA is used on FETCH, OPEN, or EXECUTE, the address in each SQLDATA entry in the SQLDA must be set to a valid address. If the SQLTYPE value indicates that an indicator variable is also provided, the corresponding SQLIND entry must contain a valid address in addition to the SQLDA being set to a valid address. In the case of a REXX procedure, the SQLDATA and SQLIND fields are not pointers, but are buffers containing the variable and indicator values. When an SQLDA is used on an OPEN or EXECUTE, these variables must be initialized to a valid value. So, I can tell that I have a problem with the address in SQLDATA. I think the error is in reference to my open statement. I assume I need to have an eval statement for the SQLDATA, but I don't have a clue what to put there.... Here is a snippet of my code. Any ideas of what I'm doing wrong? I've also tried it with sqldabc = 1 * 80 + 16 and sqldabc = 2 * 80 + 16 for the second occurance. Same error C eval SQLN = 2 C eval SQLD = 2 C eval SQLTYPE = 452 C eval SQLLEN = 1 C eval SQLNAME = 'SAMPLE' C eval sqldabc = SQLN*80+16 C movea sqlvar sql_var(1) C eval SQLTYPE = 452 C eval SQLLEN = 1 C eval SQLNAME = 'SAMPLE1' C eval sqldabc = SQLN*80+16 C movea sqlvar sql_var(2) C/Exec SQL C+ PREPARE SQL_Stmt FROM :SQL_String C/End-Exec C/Exec SQL C+ DECLARE SQL_Read SCROLL CURSOR for SQL_Stmt C/End-Exec C/Exec SQL C+ OPEN SQL_Read Using DESCRIPTOR :SQLDA C/End-Exec
midrange.com
H DFTACTGRP(*NO)
H OPTION(*SRCSTMT: *NODEBUGIO: *NOSHOWCPY)
/copy prodkr/qcopysrc,clcmdexec
/copy prodkr/qcopysrc,getcurdb
?* SQLDA Descriptor Area
D SQLDA DS
D SQLDAID 1 8A
D SQLDABC 9 12B 0
D SQLN 13 14B 0
D SQLD 15 16B 0
D SQL_VAR 80A DIM(400)
D SQL_Data_Type 17 18B 0
D SQL_Var_Len 19 20B 0
D SQL_Resrvd 21 32A
D SQL_Data_Pntr 33 48*
D SQL_Null_Ind 49 64*
D SQL_Name_Len 65 66B 0
D SQL_Col_Name 67 96A
?* SQLDA Variable Data
D SQLVAR DS
D SQLTYPE 1 2B 0
D SQLLEN 3 4B 0
D SQLRES 5 16A
D SQLDATA 17 32*
D SQLIND 33 48*
D SQLNAMELEN 49 50B 0
D SQLNAME 51 80A
?* SQLDA Variable Data (Extended)
D SQLVAR2 DS
D SQLLONGL 1 4B 0
D SQLRSVDL 5 32A
D SQLDATAL 33 48*
D SQLTNAMELN 49 50B 0
D SQLTNAME 51 80A
?* Work Variables
D File s 10a
D Lib s 10a
D TempFile s 10a
D TempLib s 10a
Drowcountx s 10I 0
DOrdinal s 10I 0
DColName s 10a
DDataType s 8a
DLength s 10i 0
DScale s 10i 0
Dn1 s 4b 0
DPuNo s 6a
DDatabaseName s 100a
D XLSPath s 100a
D SQLSelect s 5000a Varying
DColumnNumber s 10i 0
D RecordData s 32766a
DElement s 10i 0
?* Parameter Entry
C *Entry PList
C Parm FileName 20
C Parm TemplateFile 20
C Parm XLSPath
?* SQL Error Trapping
c/exec sql
c+ Whenever SQLError Continue
c/end-exec
/free
//?* Extract The Input File Name
//?* & Load The Output Path If Not Specified
If XLSPath=*Blanks;
XLSPath='/tmp/xl.xls';
EndIf;
File=%Subst(FileName:1:10);
Lib=%Subst(FileName:11:10);
TempFile=%Subst(TemplateFile:1:10);
TempLib=%Subst(TemplateFile:11:10);
//?* Override The Database File Being Processed
CmdStr='OVRDBF '+%Trim(File)+' '+
%Trim(Lib)+'/'+%Trim(File)+
' Share(*Yes)';
CmdStrSize=%Len(%Trim(CmdStr));
monitor;
Run(CmdStr:CmdStrSize);
on-error;
endmon;
//?* Build The Dynamic Excel Generation Program
ExSR SQLSelectBuild;
/end-free
c/exec sql
c+ Prepare SQLExtract From :SQLSelect
c/end-exec
c Eval SqlN=1
C/exec sql
c+ Describe SQLExtract Into :SQLDA
c/end-exec
c Eval SqlN=SqlD
c Eval SQLABC=SqlN*%Len(SqlVar)+16
c/exec sql
c+ Describe SQLExtract Into :SQLDA
c/end-exec
c/exec sql
c+ Declare SQL_Extract Cursor For SQLExtract
c/end-exec
c/exec sql
c+ open SQL_Extract Using Descriptor :SQLDA
c/end-exec
c DoU SqlCod=100
c/exec sql
c+ Fetch SQL_Extract Into Descriptor :SQLDA
c/end-exec
c If SqlCod=100
c/exec sql
c+ Close SQL_Extract
c/end-exec
c Leave
c EndIf
c ExSR ExtractData
c EndDo
/free
//?* Terminate
*inlr = *on;
BegSR ExtractData;
For Element = 1 By 1 To SqlD;
SQLVAR=SQL_VAR(Element);
EndFor;
EndSR;
/end-free
?*
?* Build SQL Select Statement From Template File
C SQLSelectBuildBegSR
C Eval SQLSelect='Select '
?* Declare The SQL Cursor
c/exec sql
c+ Declare StringBuild Cursor
c+ For Select Column_Name,Data_Type,Length,Numeric_Scale,
c+ Ordinal_Position From SYSCOLUMNS
c+ Where System_Table_Name= :TempFile And
c+ System_Table_Schema = :TempLib
c+ Order By Ordinal_Position
c/end-exec
?* Open The SQL Cursor
c/exec sql
c+ Open StringBuild
c/end-exec
?* Error Occurred On Open
c If SQLCod<0
c LeaveSR
c EndIf
?* Load The Source Code For All Fields In the File
c Eval rowcountx=4
c DoU SQLCod=100
c/exec sql
c+ Fetch StringBuild Into :ColName,:DataType,:Length,
c+ :Scale:n1,:Ordinal
c/end-exec
?* End Of Records
c If SQLCod=100
c Leave
c EndIf
?* Error On Fetch
c If SQLCod<0
c Iter
c EndIf
c Eval SQLSelect=%Trim(SQLSelect)+' '+
c %Trim(ColName)+','
c EndDo
c Eval Length=%Len(%Trim(SQLSelect))
c Eval SQLSelect=%Subst(SQLSelect:1:Length-1)
c Eval SQLSelect=%Trim(SQLSelect)+
c ' From '+%Trim(Lib)+'/'
c +%Trim(File)
c EndSR
As an Amazon Associate we earn from qualifying purchases.
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.
